Hi Experts,
I had tried to remove report authentication (on a report-by-report
basis), select
a report in the Report Manager, then select the Properties tab and
select the very bottom radio button that says 'Credentials are not
required' and then 'Apply.'
I have tried above things but after doing like that report is not
rendring it is showng error that
credential is not set.
So please tell what to do... This i done for a single report only
suppoese if i want to remove authentication from my report server it
self then what to do, how i can implement that.
Or can any body tell me how to remove authentication from my report
server so that any body can run the report and it should not ask any
user name or pass word.
Regards
DineshI presume that you are using Shared Data source,then open your project and go
to shared data source. click properties and there you select "Credentials not
required", and before doing that go to the "Edit" data source and click on
Advance and click on security "UserID" give the admin user id or an Id
equivalent to admin.
Amarnath, MCTS.
"Dinesh" wrote:
> Hi Experts,
> I had tried to remove report authentication (on a report-by-report
> basis), select
> a report in the Report Manager, then select the Properties tab and
> select the very bottom radio button that says 'Credentials are not
> required' and then 'Apply.'
> I have tried above things but after doing like that report is not
> rendring it is showng error that
> credential is not set.
> So please tell what to do... This i done for a single report only
> suppoese if i want to remove authentication from my report server it
> self then what to do, how i can implement that.
> Or can any body tell me how to remove authentication from my report
> server so that any body can run the report and it should not ask any
> user name or pass word.
> Regards
> Dinesh
>
Showing posts with label user. Show all posts
Showing posts with label user. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
How to Remove Carriage Returns from database field?
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help
Hi Peter !
See if the values are related to the value of which is a carriage
return CHAR(13)
http://www.asciitable.com, then REPLACE it with the REPLACE function to
whatever you want to display.
HTH, Jens Suessmeyer.
|||Try:
update MyTable
set
MyCol = replace (MyCol, char (13), '')
where
MyCol like '%' + char (13)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<peter.rietmann@.swisscom.com> wrote in message
news:1129721023.545769.275420@.o13g2000cwo.googlegr oups.com...
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help
|||Just to be a nut picker ;-).
You don=B4t need that:
where=20
MyCol like '%' + char (13)=20
Jens Suessmeyer.
|||that's great, it has solved my problem. thanks
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help
Hi Peter !
See if the values are related to the value of which is a carriage
return CHAR(13)
http://www.asciitable.com, then REPLACE it with the REPLACE function to
whatever you want to display.
HTH, Jens Suessmeyer.
|||Try:
update MyTable
set
MyCol = replace (MyCol, char (13), '')
where
MyCol like '%' + char (13)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
<peter.rietmann@.swisscom.com> wrote in message
news:1129721023.545769.275420@.o13g2000cwo.googlegr oups.com...
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help
|||Just to be a nut picker ;-).
You don=B4t need that:
where=20
MyCol like '%' + char (13)=20
Jens Suessmeyer.
|||that's great, it has solved my problem. thanks
How to Remove Carriage Returns from database field?
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the helpHi Peter !
See if the values are related to the value of which is a carriage
return CHAR(13)
http://www.asciitable.com, then REPLACE it with the REPLACE function to
whatever you want to display.
HTH, Jens Suessmeyer.|||Try:
update MyTable
set
MyCol = replace (MyCol, char (13), '')
where
MyCol like '%' + char (13)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<peter.rietmann@.swisscom.com> wrote in message
news:1129721023.545769.275420@.o13g2000cwo.googlegroups.com...
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help|||Just to be a nut picker ;-).
You don=B4t need that:
where=20
MyCol like '%' + char (13)=20
Jens Suessmeyer.|||that's great, it has solved my problem. thanks
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the helpHi Peter !
See if the values are related to the value of which is a carriage
return CHAR(13)
http://www.asciitable.com, then REPLACE it with the REPLACE function to
whatever you want to display.
HTH, Jens Suessmeyer.|||Try:
update MyTable
set
MyCol = replace (MyCol, char (13), '')
where
MyCol like '%' + char (13)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<peter.rietmann@.swisscom.com> wrote in message
news:1129721023.545769.275420@.o13g2000cwo.googlegroups.com...
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help|||Just to be a nut picker ;-).
You don=B4t need that:
where=20
MyCol like '%' + char (13)=20
Jens Suessmeyer.|||that's great, it has solved my problem. thanks
How to Remove Carriage Returns from database field?
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the helpHi Peter !
See if the values are related to the value of which is a carriage
return CHAR(13)
http://www.asciitable.com, then REPLACE it with the REPLACE function to
whatever you want to display.
HTH, Jens Suessmeyer.|||Try:
update MyTable
set
MyCol = replace (MyCol, char (13), '')
where
MyCol like '%' + char (13)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<peter.rietmann@.swisscom.com> wrote in message
news:1129721023.545769.275420@.o13g2000cwo.googlegroups.com...
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help|||Just to be a nut picker ;-).
You don=B4t need that:
where MyCol like '%' + char (13)
Jens Suessmeyer.|||that's great, it has solved my problem. thanks
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the helpHi Peter !
See if the values are related to the value of which is a carriage
return CHAR(13)
http://www.asciitable.com, then REPLACE it with the REPLACE function to
whatever you want to display.
HTH, Jens Suessmeyer.|||Try:
update MyTable
set
MyCol = replace (MyCol, char (13), '')
where
MyCol like '%' + char (13)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<peter.rietmann@.swisscom.com> wrote in message
news:1129721023.545769.275420@.o13g2000cwo.googlegroups.com...
I have saved user input from an html text area into a MS SQL 2000
database in the process replacing the \n character with a <br> tag.
Unfortunately I did not replace the \r at the same time so now have
many fields that have the following
Line 1
Line 2
in SQL table it looks like Line 1x<br>Line 2x<br> ... etc where the x
appears as a little square box.
I then present this data in an html table which works fine. If the user
copy pastes into Excel then each of the \r characters cause Excel to
create a new cell. This is not fine.
Can someone tell me how I can access the \r characters in the table so
that I can replace them.
ie Select * From tab where col like '%\r'
does not contain any records
thanks for the help|||Just to be a nut picker ;-).
You don=B4t need that:
where MyCol like '%' + char (13)
Jens Suessmeyer.|||that's great, it has solved my problem. thanks
How to remove all non-system service broker objects
I'm looking for a tool or script that can wipe a database clean of any and all user configured service broker objects.
I've got two environments, Development and QA, that I need to have parallel service broker configurations. Each environment is hosted on a different set of servers. I need to make sure the Development environment looks exactly like the QA environment from a Service Broker perspective (other than the specific service broker instance references in the installed routes). I've got a script to build the objects that I want, but the Development environment is full of artifacts of abandoned experiments. Before running the creation script in Development, I'd like to start with a clean slate. Recreating the Development databases is not an option.
If a tool or script is not available, I know I need to adress at least the following object types, but I'm not sure if this list covers everything or in which order I need to drop which objects: routes, remote service bindings, certificates, database principals, tcp endpoints, services, queues, contracts, and message types
Thanks in advance
Lee
I would suggest to create a script that drops all types of objects (services, contracts, message types, queues, routes, remote service bindings). One easy criteria to choose wheter is a 'system' object or not is to look if a similar object (same type and name) exists in [master]. If it does, then is 'system'. Each of these types has a coresponding metadata view: sys.services, sys.service_contracts, sys.service_message_types, sys.service_queues, sys.routes, sys.re:mote_service_bindings.
Attempting to drop a true 'system' object would fail. But there is one object that is implicitly created, but is not trully system: the 'AutoCreatedLocal' route in each database. this route can be altered and dropped, it is not a system object.
HTH,
~ Remus
How to remove a user
I'm trying to remove a user who is no longer in the Active Directory. When I
go to Site Settings > Configure site-wide security > check the box for the
user to be deleted and click "Delete" I get an error message "The user or
group name 'domain\username' is not recognized. (rsUnknownUserName).
The user doesn't get deleted. How do I remove this user?I just figured out. There were several users that do not exist in the Active
Directory & I had to check them all & then "Delete" them. By just checking
one, the process was raising an error (rsUnknownUserName) for the other non
existing users; therefore, not allowing the "Delete" to complete.
Thanks.
"Sal Young" wrote:
> I'm trying to remove a user who is no longer in the Active Directory. When I
> go to Site Settings > Configure site-wide security > check the box for the
> user to be deleted and click "Delete" I get an error message "The user or
> group name 'domain\username' is not recognized. (rsUnknownUserName).
> The user doesn't get deleted. How do I remove this user?
go to Site Settings > Configure site-wide security > check the box for the
user to be deleted and click "Delete" I get an error message "The user or
group name 'domain\username' is not recognized. (rsUnknownUserName).
The user doesn't get deleted. How do I remove this user?I just figured out. There were several users that do not exist in the Active
Directory & I had to check them all & then "Delete" them. By just checking
one, the process was raising an error (rsUnknownUserName) for the other non
existing users; therefore, not allowing the "Delete" to complete.
Thanks.
"Sal Young" wrote:
> I'm trying to remove a user who is no longer in the Active Directory. When I
> go to Site Settings > Configure site-wide security > check the box for the
> user to be deleted and click "Delete" I get an error message "The user or
> group name 'domain\username' is not recognized. (rsUnknownUserName).
> The user doesn't get deleted. How do I remove this user?
Monday, March 26, 2012
How to remote processing reports?
Hi everyone!
I have my reports published in a report server. What do I need to do so that the user can processing reports from a windows application?
There are many ways to do that. Within VIsual Studio 2005 there is a reportviewer control which you can use within your application to contact and display report. If you only want to get a report from the Server and present the user a byte stream to save to a file (something like a button which says Save the report to PDF...) THen you can just use the Report Server Services interface. WHat do you actually want to do ? (Meanwhile you can take a look on: http://www.gotreportviewer.com/)
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
How to release user connections?
I'm in the process of moving our database server to new hardware, but when I
try to use the Copy Database Wizard it finds to concurrent users and won't
allow for the copy. How can I find out who is on and then disconnect them?exec sp_who2 to find them
KILL spid to close them
You only need to kill the spid's where the spid is greater than 50
http://sqlservercode.blogspot.com/|||EXEC sp_who for discovering who is connected
KILL spid for killing a connection
Both are documented in Books On Line
--
Med Bouchenafa
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>|||Another method for forcibly terminate database connections is:
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and then back to normal:
ALTER DATABASE MyDatabase
SET MULTI_USER
See the Books Online for more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>|||I find that stopping and starting the SQL services usually does the
trick. Nice and easy too.|||Thats a hard one if you have more than one userdatabase one your
server, because ALL serverusers will be wiped out of the system, so
better altering the database like DAN stated would be the best (it is
also my perferable solution, for a hard kick-off)
HTH, jens Suessmeyer.
try to use the Copy Database Wizard it finds to concurrent users and won't
allow for the copy. How can I find out who is on and then disconnect them?exec sp_who2 to find them
KILL spid to close them
You only need to kill the spid's where the spid is greater than 50
http://sqlservercode.blogspot.com/|||EXEC sp_who for discovering who is connected
KILL spid for killing a connection
Both are documented in Books On Line
--
Med Bouchenafa
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>|||Another method for forcibly terminate database connections is:
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and then back to normal:
ALTER DATABASE MyDatabase
SET MULTI_USER
See the Books Online for more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>|||I find that stopping and starting the SQL services usually does the
trick. Nice and easy too.|||Thats a hard one if you have more than one userdatabase one your
server, because ALL serverusers will be wiped out of the system, so
better altering the database like DAN stated would be the best (it is
also my perferable solution, for a hard kick-off)
HTH, jens Suessmeyer.
How to release user connections?
I'm in the process of moving our database server to new hardware, but when I
try to use the Copy Database Wizard it finds to concurrent users and won't
allow for the copy. How can I find out who is on and then disconnect them?
exec sp_who2 to find them
KILL spid to close them
You only need to kill the spid's where the spid is greater than 50
http://sqlservercode.blogspot.com/
|||EXEC sp_who for discovering who is connected
KILL spid for killing a connection
Both are documented in Books On Line
Med Bouchenafa
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>
|||Another method for forcibly terminate database connections is:
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and then back to normal:
ALTER DATABASE MyDatabase
SET MULTI_USER
See the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>
|||I find that stopping and starting the SQL services usually does the
trick. Nice and easy too.
|||Thats a hard one if you have more than one userdatabase one your
server, because ALL serverusers will be wiped out of the system, so
better altering the database like DAN stated would be the best (it is
also my perferable solution, for a hard kick-off)
HTH, jens Suessmeyer.
try to use the Copy Database Wizard it finds to concurrent users and won't
allow for the copy. How can I find out who is on and then disconnect them?
exec sp_who2 to find them
KILL spid to close them
You only need to kill the spid's where the spid is greater than 50
http://sqlservercode.blogspot.com/
|||EXEC sp_who for discovering who is connected
KILL spid for killing a connection
Both are documented in Books On Line
Med Bouchenafa
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>
|||Another method for forcibly terminate database connections is:
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and then back to normal:
ALTER DATABASE MyDatabase
SET MULTI_USER
See the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>
|||I find that stopping and starting the SQL services usually does the
trick. Nice and easy too.
|||Thats a hard one if you have more than one userdatabase one your
server, because ALL serverusers will be wiped out of the system, so
better altering the database like DAN stated would be the best (it is
also my perferable solution, for a hard kick-off)
HTH, jens Suessmeyer.
How to release user connections?
I'm in the process of moving our database server to new hardware, but when I
try to use the Copy Database Wizard it finds to concurrent users and won't
allow for the copy. How can I find out who is on and then disconnect them?exec sp_who2 to find them
KILL spid to close them
You only need to kill the spid's where the spid is greater than 50
http://sqlservercode.blogspot.com/|||EXEC sp_who for discovering who is connected
KILL spid for killing a connection
Both are documented in Books On Line
Med Bouchenafa
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>|||Another method for forcibly terminate database connections is:
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and then back to normal:
ALTER DATABASE MyDatabase
SET MULTI_USER
See the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>|||I find that stopping and starting the SQL services usually does the
trick. Nice and easy too.|||Thats a hard one if you have more than one userdatabase one your
server, because ALL serverusers will be wiped out of the system, so
better altering the database like DAN stated would be the best (it is
also my perferable solution, for a hard kick-off)
HTH, jens Suessmeyer.
try to use the Copy Database Wizard it finds to concurrent users and won't
allow for the copy. How can I find out who is on and then disconnect them?exec sp_who2 to find them
KILL spid to close them
You only need to kill the spid's where the spid is greater than 50
http://sqlservercode.blogspot.com/|||EXEC sp_who for discovering who is connected
KILL spid for killing a connection
Both are documented in Books On Line
Med Bouchenafa
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>|||Another method for forcibly terminate database connections is:
ALTER DATABASE MyDatabase
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
and then back to normal:
ALTER DATABASE MyDatabase
SET MULTI_USER
See the Books Online for more information.
Hope this helps.
Dan Guzman
SQL Server MVP
"Cwhitmore" <Cwhitmore@.discussions.microsoft.com> wrote in message
news:2D491056-49C8-4061-BE1E-5E1F6F77D722@.microsoft.com...
> I'm in the process of moving our database server to new hardware, but when
> I
> try to use the Copy Database Wizard it finds to concurrent users and won't
> allow for the copy. How can I find out who is on and then disconnect them?
>|||I find that stopping and starting the SQL services usually does the
trick. Nice and easy too.|||Thats a hard one if you have more than one userdatabase one your
server, because ALL serverusers will be wiped out of the system, so
better altering the database like DAN stated would be the best (it is
also my perferable solution, for a hard kick-off)
HTH, jens Suessmeyer.
Friday, March 23, 2012
How to regenerate a stored proc (drop & create or alter procedure) ??
Hi all.
Cf my other news: How to change or alter a user defined data type?
I need to regenerate (not only "sp_recompile") a list of stored procedures.
With the "SQL Server Enterprise Manager" it's very easy: just open each
Stored proc and save it. But how to do that by transact SQL script '
Thanks.
Lilian.What purpose does "regenerating" these serve? There are 2 ways to do this
depending on what your after. One way is to use the DROP PROCEDURE xxx
command and then issue the CREATE PROCEDURE xxx ... command. The other is
to use the ALTER PROCEDURE xxx.... command. Drop will not keep any
existing permissions and ALTER will.
--
Andrew J. Kelly
SQL Server MVP
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:u7Os%23CHQDHA.2228@.tk2msftngp13.phx.gbl...
> Hi all.
> Cf my other news: How to change or alter a user defined data type?
> I need to regenerate (not only "sp_recompile") a list of stored
procedures.
> With the "SQL Server Enterprise Manager" it's very easy: just open each
> Stored proc and save it. But how to do that by transact SQL script '
> Thanks.
>
> Lilian.
>sql
Cf my other news: How to change or alter a user defined data type?
I need to regenerate (not only "sp_recompile") a list of stored procedures.
With the "SQL Server Enterprise Manager" it's very easy: just open each
Stored proc and save it. But how to do that by transact SQL script '
Thanks.
Lilian.What purpose does "regenerating" these serve? There are 2 ways to do this
depending on what your after. One way is to use the DROP PROCEDURE xxx
command and then issue the CREATE PROCEDURE xxx ... command. The other is
to use the ALTER PROCEDURE xxx.... command. Drop will not keep any
existing permissions and ALTER will.
--
Andrew J. Kelly
SQL Server MVP
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:u7Os%23CHQDHA.2228@.tk2msftngp13.phx.gbl...
> Hi all.
> Cf my other news: How to change or alter a user defined data type?
> I need to regenerate (not only "sp_recompile") a list of stored
procedures.
> With the "SQL Server Enterprise Manager" it's very easy: just open each
> Stored proc and save it. But how to do that by transact SQL script '
> Thanks.
>
> Lilian.
>sql
Monday, March 19, 2012
How to recover Master database table data
Hi all, I am working with SQL Server2000 and I have done a horrible thing here.
We here have an script that delete all data from all user tables of a database, and I run it in the master DATABASE.
As we don't made backups of this database, now somethings of the database aren't working.
We here have an script that delete all data from all user tables of a database, and I run it in the master DATABASE.
As we don't made backups of this database, now somethings of the database aren't working.
Here is the script:
declare @.table_name sysname
declare @.alter_table_statement varchar(256)
declare @.delete_statement varchar(256)
-- definindo o cursor...
declare table_name_cursor cursor local fast_forward for
select
name
from
sysobjects
where
xtype = 'U'
and
name <> 'dtproperties'
-- desligando os vÃnculos...
open table_name_cursor
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' nocheck constraint all'
exec(@.alter_table_statement)
while @.@.Fetch_Status = 0
begin
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' nocheck constraint all'
exec(@.alter_table_statement)
end
close table_name_cursor
open table_name_cursor
fetch next from table_name_cursor into @.table_name
select @.delete_statement = 'delete from ' + ltrim(rtrim(@.table_name))
exec(@.delete_statement)
while @.@.Fetch_Status = 0
begin
fetch next from table_name_cursor into @.table_name
select @.delete_statement = 'delete from ' + ltrim(rtrim(@.table_name))
exec(@.delete_statement)
end
close table_name_cursor
open table_name_cursor
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' check constraint all'
exec(@.alter_table_statement)
while @.@.Fetch_Status = 0
begin
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' check constraint all'
exec(@.alter_table_statement)
end
close table_name_cursor
deallocate table_name_cursor
I have tried to restore master table with the restore function, but it doesn't work. When I try to do this I received a message informing that it can't copy the data because one file was in use. The server was in a single user mode.
Is there anyway to recover the data that I have lost?
Help me understand this.
At the beginning, you say that you don't have backups of the master database.
At the end you say that you tried to restore the master database, but had problems.
You should be able to restore the master database if you have a backup of it, and this would be the best method to use.
Can you clarify, and also let us know what method (T-SQL commands etc.) you used to attempt the restore?
|||Kevin don't have any backup of this database, I tried to rebuild it, not restore it, I used the wrong word for it.|||There isn't any easy answer to this. If you have already rebuilt master then you should have SQL Server up and running with a clean master. Realize that by rebuilding the master database all your jobs and maintenance plans in MSDB have been lost, unless you have a backup of msdb or scripts to recreate the jobs.
The datafiles for your user databases should still be on the disk, you will need to attach those using sp_attach_db.
Once you have the databases attached you still won't have the logins. You will need to recreate the logins, then remap those new logins using the procedure outlined in http://support.microsoft.com/kb/274188/en-us
|||Ok thanks... one more explanation
In fact my real problem isn't on the databases and user logins, becaus this I doesn't lost. I lost the access to table metadata (via jdbc driver) and the permission to create diagrams. Probably this can be done by reconfiguring the server, but I doesn't know how to do it.
How to recover deleted data from master database
Hi all,
I made a horrible thing this week. I have an script that delete all data from all user tables, and I run it in the master database.
After this I couldn't access the metadata from the tables of my databases using a JDBC connection.
My script runs over all sysobject that are different from dtproperties.
I don't have a back up of master table. I have tried to copy the data from another master database (from another machine) but I did not work. I tried to copy from a xls, but it did'nt work too. I try do recover the master database (as it is explained in the online books) but it did not work either.
I made a horrible thing this week. I have an script that delete all data from all user tables, and I run it in the master database.
After this I couldn't access the metadata from the tables of my databases using a JDBC connection.
My script runs over all sysobject that are different from dtproperties.
I don't have a back up of master table. I have tried to copy the data from another master database (from another machine) but I did not work. I tried to copy from a xls, but it did'nt work too. I try do recover the master database (as it is explained in the online books) but it did not work either.
Someone know how can I recover this data?Here is the script:
declare @.table_name sysname
declare @.alter_table_statement varchar(256)
declare @.delete_statement varchar(256)
declare table_name_cursor cursor local fast_forward for
select
name
from
sysobjects
where
xtype = 'U'
and
name <> 'dtproperties'
open table_name_cursor
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' nocheck constraint all'
exec(@.alter_table_statement)
while @.@.Fetch_Status = 0
begin
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' nocheck constraint all'
exec(@.alter_table_statement)
end
close table_name_cursor
open table_name_cursor
fetch next from table_name_cursor into @.table_name
select @.delete_statement = 'delete from ' + ltrim(rtrim(@.table_name))
exec(@.delete_statement)
while @.@.Fetch_Status = 0
begin
fetch next from table_name_cursor into @.table_name
select @.delete_statement = 'delete from ' + ltrim(rtrim(@.table_name))
exec(@.delete_statement)
end
close table_name_cursor
-- ligando os vnculos...
open table_name_cursor
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' check constraint all'
exec(@.alter_table_statement)
while @.@.Fetch_Status = 0
begin
fetch next from table_name_cursor into @.table_name
select @.alter_table_statement = 'alter table ' + ltrim(rtrim(@.table_name)) + ' check constraint all'
exec(@.alter_table_statement)
end
close table_name_cursor
deallocate table_name_cursor
And here is the names of the tables of master database that this script had deleted all the containing data:
spt_monitor
spt_values
spt_fallback_db
spt_fallback_dev
spt_fallback_usg
spt_provider_types
spt_datatype_info_ext
MSreplication_options
spt_datatype_info
spt_server_info
spt_server_info
Wednesday, March 7, 2012
How to read IIS log files to get the hit count and distinct users information?
Hi All,
I am trying to read IIS log file to find out the hit count of the
application and also to find the user base (number of different users
who access our application).
Log files are created on a daily basis.
I am using DTS package to import IIS log files into the database but I
am finding difficulty in analyzing the data. My objective is to get the
users information and hit count on a daily basis so that I can then
consolidate it to find the hit count for the w
/month and users who
accessed it during that period.
Thanks,
NareshNaresh,
Can you post the destination table DDL+sample rows+desired output?
Thanks
Jerry
"Naresh" <thatsnaresh@.gmail.com> wrote in message
news:1130169058.111355.64750@.g43g2000cwa.googlegroups.com...
> Hi All,
> I am trying to read IIS log file to find out the hit count of the
> application and also to find the user base (number of different users
> who access our application).
> Log files are created on a daily basis.
> I am using DTS package to import IIS log files into the database but I
> am finding difficulty in analyzing the data. My objective is to get the
> users information and hit count on a daily basis so that I can then
> consolidate it to find the hit count for the w/month and users who
> accessed it during that period.
> Thanks,
> Naresh
>|||
Hi Jerry,
I imported the IIS log file, Sql server automatically creates the table
during the import process.
Following is the extract from IIS log file
#Software: Microsoft Internet Information Services 6.0
#Version: 1.0
#Date: 2005-10-24 07:00:26
#Fields: date time s-sitename s-ip cs-method cs-uri-stem cs-uri-query
s-port cs-username c-ip cs(User-Agent) cs(Referer) cs-host sc-status
sc-win32-status sc-bytes cs-bytes time-taken
It contains the columns information that is being logged.
Just curious to know whether each entry in IIS log file a HIT to the
application? i.e., can I treat the number of rows in the log file as the
hit count of the application?
As already requested I also wanted to find out different users who had
accessed the application?
Thanks much in advance.
Regards,
Naresh
*** Sent via Developersdex http://www.examnotes.net ***
I am trying to read IIS log file to find out the hit count of the
application and also to find the user base (number of different users
who access our application).
Log files are created on a daily basis.
I am using DTS package to import IIS log files into the database but I
am finding difficulty in analyzing the data. My objective is to get the
users information and hit count on a daily basis so that I can then
consolidate it to find the hit count for the w
/month and users whoaccessed it during that period.
Thanks,
NareshNaresh,
Can you post the destination table DDL+sample rows+desired output?
Thanks
Jerry
"Naresh" <thatsnaresh@.gmail.com> wrote in message
news:1130169058.111355.64750@.g43g2000cwa.googlegroups.com...
> Hi All,
> I am trying to read IIS log file to find out the hit count of the
> application and also to find the user base (number of different users
> who access our application).
> Log files are created on a daily basis.
> I am using DTS package to import IIS log files into the database but I
> am finding difficulty in analyzing the data. My objective is to get the
> users information and hit count on a daily basis so that I can then
> consolidate it to find the hit count for the w
/month and users who> accessed it during that period.
> Thanks,
> Naresh
>|||
Hi Jerry,
I imported the IIS log file, Sql server automatically creates the table
during the import process.
Following is the extract from IIS log file
#Software: Microsoft Internet Information Services 6.0
#Version: 1.0
#Date: 2005-10-24 07:00:26
#Fields: date time s-sitename s-ip cs-method cs-uri-stem cs-uri-query
s-port cs-username c-ip cs(User-Agent) cs(Referer) cs-host sc-status
sc-win32-status sc-bytes cs-bytes time-taken
It contains the columns information that is being logged.
Just curious to know whether each entry in IIS log file a HIT to the
application? i.e., can I treat the number of rows in the log file as the
hit count of the application?
As already requested I also wanted to find out different users who had
accessed the application?
Thanks much in advance.
Regards,
Naresh
*** Sent via Developersdex http://www.examnotes.net ***
how to read a resultset from a procedure calling a procedure
Hi all,
I'm trying to user proc1 to call proc2.
proc2 is a search proc which returns a resultset, but how can i have proc1
read that resultset without having that resultset being sent to the client?
i need to read that resultset in proc1 and then make another resultset out
of that one that will return to the client.
i was thinking temporary tables but they dont go across procedures and
##global tables dont do the trick cause they arent unique
any ideas?
thanksPedro,
Check out Erland's article at:
http://www.sommarskog.se/share_data.html
HTH
Jerry
"Pedro C Valenzuela" <peter4jc@.hotmail.com> wrote in message
news:%23P8b4c4wFHA.904@.tk2msftngp13.phx.gbl...
> Hi all,
> I'm trying to user proc1 to call proc2.
> proc2 is a search proc which returns a resultset, but how can i have proc1
> read that resultset without having that resultset being sent to the
> client?
> i need to read that resultset in proc1 and then make another resultset out
> of that one that will return to the client.
> i was thinking temporary tables but they dont go across procedures and
> ##global tables dont do the trick cause they arent unique
> any ideas?
>
> thanks
>|||You could replace Proc2 with a VIEW ?
Look up CREATE VIEW in BOL - this will give you all the information you
should need regarding Views.
HTH
Barry|||CREATE TABLE #<temptablename>
(
<colspec>
)
INSERT #<temptablename>
EXEC <storedprocedurename>|||The scope of temporary tables does extend to called stored procedures. If
you create a temp table within one stored procedure, it is visible in any
stored procedure called by that stored procedure, provided the CREATE TABLE
occurs before the EXEC. You can also create a temp table and issue
INSERT...EXEC to populate it with the result set of another stored
procedure.
"Pedro C Valenzuela" <peter4jc@.hotmail.com> wrote in message
news:%23P8b4c4wFHA.904@.tk2msftngp13.phx.gbl...
> Hi all,
> I'm trying to user proc1 to call proc2.
> proc2 is a search proc which returns a resultset, but how can i have proc1
> read that resultset without having that resultset being sent to the
> client?
> i need to read that resultset in proc1 and then make another resultset out
> of that one that will return to the client.
> i was thinking temporary tables but they dont go across procedures and
> ##global tables dont do the trick cause they arent unique
> any ideas?
>
> thanks
>|||Have you thought of using a table variable returned to proc1 from Proc2
Lookup OUTPUT in BOL
GW
"Pedro C Valenzuela" wrote:
> Hi all,
> I'm trying to user proc1 to call proc2.
> proc2 is a search proc which returns a resultset, but how can i have proc1
> read that resultset without having that resultset being sent to the client
?
> i need to read that resultset in proc1 and then make another resultset out
> of that one that will return to the client.
> i was thinking temporary tables but they dont go across procedures and
> ##global tables dont do the trick cause they arent unique
> any ideas?
>
> thanks
>
>|||as far as i can recall, you cant use table variables for output parameters
and i havent found it in BOL either, am i wrong?
"GWilliy" <GWilliy@.discussions.microsoft.com> wrote in message
news:DB2AC26B-FCB9-45D9-ABEE-57CF97079878@.microsoft.com...
> Have you thought of using a table variable returned to proc1 from Proc2
> Lookup OUTPUT in BOL
> GW
> "Pedro C Valenzuela" wrote:
>|||On Tue, 27 Sep 2005 18:53:13 -0700, Pedro C Valenzuela wrote:
>as far as i can recall, you cant use table variables for output parameters
>and i havent found it in BOL either, am i wrong?
Hi Pedro,
No, you're right. Table variables can't be passed as input parameter to
stored procedures, nor returned as output parameter from stored
procedures. That's what table-valued user-defined functions are for.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
I'm trying to user proc1 to call proc2.
proc2 is a search proc which returns a resultset, but how can i have proc1
read that resultset without having that resultset being sent to the client?
i need to read that resultset in proc1 and then make another resultset out
of that one that will return to the client.
i was thinking temporary tables but they dont go across procedures and
##global tables dont do the trick cause they arent unique
any ideas?
thanksPedro,
Check out Erland's article at:
http://www.sommarskog.se/share_data.html
HTH
Jerry
"Pedro C Valenzuela" <peter4jc@.hotmail.com> wrote in message
news:%23P8b4c4wFHA.904@.tk2msftngp13.phx.gbl...
> Hi all,
> I'm trying to user proc1 to call proc2.
> proc2 is a search proc which returns a resultset, but how can i have proc1
> read that resultset without having that resultset being sent to the
> client?
> i need to read that resultset in proc1 and then make another resultset out
> of that one that will return to the client.
> i was thinking temporary tables but they dont go across procedures and
> ##global tables dont do the trick cause they arent unique
> any ideas?
>
> thanks
>|||You could replace Proc2 with a VIEW ?
Look up CREATE VIEW in BOL - this will give you all the information you
should need regarding Views.
HTH
Barry|||CREATE TABLE #<temptablename>
(
<colspec>
)
INSERT #<temptablename>
EXEC <storedprocedurename>|||The scope of temporary tables does extend to called stored procedures. If
you create a temp table within one stored procedure, it is visible in any
stored procedure called by that stored procedure, provided the CREATE TABLE
occurs before the EXEC. You can also create a temp table and issue
INSERT...EXEC to populate it with the result set of another stored
procedure.
"Pedro C Valenzuela" <peter4jc@.hotmail.com> wrote in message
news:%23P8b4c4wFHA.904@.tk2msftngp13.phx.gbl...
> Hi all,
> I'm trying to user proc1 to call proc2.
> proc2 is a search proc which returns a resultset, but how can i have proc1
> read that resultset without having that resultset being sent to the
> client?
> i need to read that resultset in proc1 and then make another resultset out
> of that one that will return to the client.
> i was thinking temporary tables but they dont go across procedures and
> ##global tables dont do the trick cause they arent unique
> any ideas?
>
> thanks
>|||Have you thought of using a table variable returned to proc1 from Proc2
Lookup OUTPUT in BOL
GW
"Pedro C Valenzuela" wrote:
> Hi all,
> I'm trying to user proc1 to call proc2.
> proc2 is a search proc which returns a resultset, but how can i have proc1
> read that resultset without having that resultset being sent to the client
?
> i need to read that resultset in proc1 and then make another resultset out
> of that one that will return to the client.
> i was thinking temporary tables but they dont go across procedures and
> ##global tables dont do the trick cause they arent unique
> any ideas?
>
> thanks
>
>|||as far as i can recall, you cant use table variables for output parameters
and i havent found it in BOL either, am i wrong?
"GWilliy" <GWilliy@.discussions.microsoft.com> wrote in message
news:DB2AC26B-FCB9-45D9-ABEE-57CF97079878@.microsoft.com...
> Have you thought of using a table variable returned to proc1 from Proc2
> Lookup OUTPUT in BOL
> GW
> "Pedro C Valenzuela" wrote:
>|||On Tue, 27 Sep 2005 18:53:13 -0700, Pedro C Valenzuela wrote:
>as far as i can recall, you cant use table variables for output parameters
>and i havent found it in BOL either, am i wrong?
Hi Pedro,
No, you're right. Table variables can't be passed as input parameter to
stored procedures, nor returned as output parameter from stored
procedures. That's what table-valued user-defined functions are for.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Friday, February 24, 2012
How to query?
In the following query, @.CusVenRefLike is a string entered by user, it is by default equal to '%' so that the query return all string when user enter nothing.
SELECT Voucher.VoucherID, Voucher.VoucherRef, Customer.CustomerRef
FROM Voucher
LEFT Join Customer on Voucher.CustomerID=Customer.CustomerID
WHERE Customer.CustomerRef LIKE @.CusVenRefLike
I find that the resulting dataset contain ONLY rows that Voucher.CustomerID<>-1. The reason is that when Voucher.CustomerID= -1, Customer.CustomerRef=NULL which is not included in Customer.CustomerRef LIKE '%' filter.
How can I include those row with Voucher.CustomerID=-1?
Thanks
You need to add additional constraints to your query, depending on exactly how you want it to work.
A simple approach is to use Coalesce(CustomerRef, '' ) to force the null into an empty string. This allows the match to procede. In fact, if there is no special meaning to the CustomerRef being NULL you might want to add a default to the column.
If you want to include nulls in any search, then add Or CustomerRef Is Null to the Where clause. You could also add Or (CustomerRef Is Null and @.CusVenRefLike = '%').
||||||SELECT Voucher.VoucherID, Voucher.VoucherRef, Customer.CustomerRef
FROM Voucher
LEFT Join Customer on Voucher.CustomerID=Customer.CustomerID
WHERE Customer.CustomerRef LIKE @.CusVenRefLikeor Customer.CustomerRef is null
Can do it as this as well..
SELECT Voucher.VoucherID, Voucher.VoucherRef, Customer.CustomerRef
FROM Voucher
LEFT Join Customer on Voucher.CustomerID=Customer.CustomerID
WHERE isnull(Customer.CustomerRef ,'') LIKE @.CusVenRefLike
How to query the list of table (DD) of a user DB
Hello there
I want to know if there is a way in sql server to query the list of
objects in a user DB. especially tables.
thanks
SELECT *
FROM information_schema.tables
WHERE TABLE_TYPE = 'BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMSShipped') = 0
Keith
"Simo Sentissi" <simo@.sentissiweb.com> wrote in message
news:e3ZU196rEHA.3608@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I want to know if there is a way in sql server to query the list of
> objects in a user DB. especially tables.
> thanks
|||Use information_schema views.
Example:
select * from information_schema.tables where
objectproperty(object_id(table_name), 'IsUserTable') = 1
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsqlref.chm::/ts_ia-iz_4pbn.htm
AMB
"Simo Sentissi" wrote:
> Hello there
> I want to know if there is a way in sql server to query the list of
> objects in a user DB. especially tables.
> thanks
>
|||http://www.aspfaq.com/search.asp?q=schema:&type=ALL
http://www.aspfaq.com/
(Reverse address to reply.)
"Simo Sentissi" <simo@.sentissiweb.com> wrote in message
news:e3ZU196rEHA.3608@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I want to know if there is a way in sql server to query the list of
> objects in a user DB. especially tables.
> thanks
I want to know if there is a way in sql server to query the list of
objects in a user DB. especially tables.
thanks
SELECT *
FROM information_schema.tables
WHERE TABLE_TYPE = 'BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMSShipped') = 0
Keith
"Simo Sentissi" <simo@.sentissiweb.com> wrote in message
news:e3ZU196rEHA.3608@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I want to know if there is a way in sql server to query the list of
> objects in a user DB. especially tables.
> thanks
|||Use information_schema views.
Example:
select * from information_schema.tables where
objectproperty(object_id(table_name), 'IsUserTable') = 1
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%2 0Server\80\Tools\Books\tsqlref.chm::/ts_ia-iz_4pbn.htm
AMB
"Simo Sentissi" wrote:
> Hello there
> I want to know if there is a way in sql server to query the list of
> objects in a user DB. especially tables.
> thanks
>
|||http://www.aspfaq.com/search.asp?q=schema:&type=ALL
http://www.aspfaq.com/
(Reverse address to reply.)
"Simo Sentissi" <simo@.sentissiweb.com> wrote in message
news:e3ZU196rEHA.3608@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I want to know if there is a way in sql server to query the list of
> objects in a user DB. especially tables.
> thanks
How to query the list of table (DD) of a user DB
Hello there
I want to know if there is a way in sql server to query the list of
objects in a user DB. especially tables.
thanksSELECT *
FROM information_schema.tables
WHERE TABLE_TYPE = 'BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMSShipped') = 0
--
Keith
"Simo Sentissi" <simo@.sentissiweb.com> wrote in message
news:e3ZU196rEHA.3608@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I want to know if there is a way in sql server to query the list of
> objects in a user DB. especially tables.
> thanks|||Use information_schema views.
Example:
select * from information_schema.tables where
objectproperty(object_id(table_name), 'IsUserTable') = 1
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsqlref.chm::/ts_ia-iz_4pbn.htm
AMB
"Simo Sentissi" wrote:
> Hello there
> I want to know if there is a way in sql server to query the list of
> objects in a user DB. especially tables.
> thanks
>|||http://www.aspfaq.com/search.asp?q=schema:&type=ALL
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Simo Sentissi" <simo@.sentissiweb.com> wrote in message
news:e3ZU196rEHA.3608@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I want to know if there is a way in sql server to query the list of
> objects in a user DB. especially tables.
> thanks
I want to know if there is a way in sql server to query the list of
objects in a user DB. especially tables.
thanksSELECT *
FROM information_schema.tables
WHERE TABLE_TYPE = 'BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMSShipped') = 0
--
Keith
"Simo Sentissi" <simo@.sentissiweb.com> wrote in message
news:e3ZU196rEHA.3608@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I want to know if there is a way in sql server to query the list of
> objects in a user DB. especially tables.
> thanks|||Use information_schema views.
Example:
select * from information_schema.tables where
objectproperty(object_id(table_name), 'IsUserTable') = 1
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsqlref.chm::/ts_ia-iz_4pbn.htm
AMB
"Simo Sentissi" wrote:
> Hello there
> I want to know if there is a way in sql server to query the list of
> objects in a user DB. especially tables.
> thanks
>|||http://www.aspfaq.com/search.asp?q=schema:&type=ALL
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Simo Sentissi" <simo@.sentissiweb.com> wrote in message
news:e3ZU196rEHA.3608@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I want to know if there is a way in sql server to query the list of
> objects in a user DB. especially tables.
> thanks
How to query the list of table (DD) of a user DB
Hello there
I want to know if there is a way in sql server to query the list of
objects in a user DB. especially tables.
thanksSELECT *
FROM information_schema.tables
WHERE TABLE_TYPE = 'BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMSShipped') = 0
Keith
"Simo Sentissi" <simo@.sentissiweb.com> wrote in message
news:e3ZU196rEHA.3608@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I want to know if there is a way in sql server to query the list of
> objects in a user DB. especially tables.
> thanks|||Use information_schema views.
Example:
select * from information_schema.tables where
objectproperty(object_id(table_name), 'IsUserTable') = 1
mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\tsq
lref.chm::/ts_ia-iz_4pbn.htm
AMB
"Simo Sentissi" wrote:
> Hello there
> I want to know if there is a way in sql server to query the list of
> objects in a user DB. especially tables.
> thanks
>|||http://www.aspfaq.com/search.asp?q=schema:&type=ALL
http://www.aspfaq.com/
(Reverse address to reply.)
"Simo Sentissi" <simo@.sentissiweb.com> wrote in message
news:e3ZU196rEHA.3608@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I want to know if there is a way in sql server to query the list of
> objects in a user DB. especially tables.
> thanks
I want to know if there is a way in sql server to query the list of
objects in a user DB. especially tables.
thanksSELECT *
FROM information_schema.tables
WHERE TABLE_TYPE = 'BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsMSShipped') = 0
Keith
"Simo Sentissi" <simo@.sentissiweb.com> wrote in message
news:e3ZU196rEHA.3608@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I want to know if there is a way in sql server to query the list of
> objects in a user DB. especially tables.
> thanks|||Use information_schema views.
Example:
select * from information_schema.tables where
objectproperty(object_id(table_name), 'IsUserTable') = 1
mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\tsq
lref.chm::/ts_ia-iz_4pbn.htm
AMB
"Simo Sentissi" wrote:
> Hello there
> I want to know if there is a way in sql server to query the list of
> objects in a user DB. especially tables.
> thanks
>|||http://www.aspfaq.com/search.asp?q=schema:&type=ALL
http://www.aspfaq.com/
(Reverse address to reply.)
"Simo Sentissi" <simo@.sentissiweb.com> wrote in message
news:e3ZU196rEHA.3608@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I want to know if there is a way in sql server to query the list of
> objects in a user DB. especially tables.
> thanks
How to query on a date range, display matching records but also display companies with no matchi
OK, hopefully someone can help me on this one. I have a report that allows a user to select a date range and returns the applicable data. Hypothetically, this returns to the user 3 rows of data for 3 different companies\sites. The problem is that the client wants to see the other 7 companies\sites that don't have data for the date range specified by the user, in the same report.
I am having issues with this as my query returns the results that exist within the date range. If now rows of data exist for the date range, how can I possibly display the other 7 companies? I looked at embedding a report inside of another report, but I can't think of a query I could write that says, if these companies are not in the result set generated by the user, display them anyway. I also looked at using a different dataset then the one I included below.
Maybe I am missing something, I don't know. I am pretty knew to SQL but don't know how to go about capturing this data. I included my query for your review. It relies on views and whatnot, but maybe someone has an idea I could look into.
SELECT TOP (100) PERCENT vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, SUM(vTTAttRollup.HC) AS HC, SUM(vTTAttRollup.ProgDays)
AS ProgDays, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays, DailyFTE.Offer_Hired
FROM vTTAttRollup LEFT OUTER JOIN
DailyFTE ON vTTAttRollup.Site = DailyFTE.Site
WHERE (vTTAttRollup.AttendanceDate >= @.StartDate) AND (vTTAttRollup.AttendanceDate < @.EndDate + 1) AND (vTTAttRollup.ProgramStartDate < @.StartDate)
GROUP BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays,
DailyFTE.Offer_Hired
ORDER BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site
Jambi,
you can basically write an Union Sql query
SELECT TOP (100) PERCENT vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, SUM(vTTAttRollup.HC) AS HC, SUM(vTTAttRollup.ProgDays)
AS ProgDays, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays, DailyFTE.Offer_Hired
FROM vTTAttRollup LEFT OUTER JOIN
DailyFTE ON vTTAttRollup.Site = DailyFTE.Site
WHERE (vTTAttRollup.AttendanceDate >= @.StartDate) AND (vTTAttRollup.AttendanceDate < @.EndDate + 1) AND (vTTAttRollup.ProgramStartDate < @.StartDate)
UNION ALL
SELECT TOP (100) PERCENT vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, SUM(vTTAttRollup.HC) AS HC, SUM(vTTAttRollup.ProgDays)
AS ProgDays, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays, DailyFTE.Offer_Hired
FROM vTTAttRollup LEFT OUTER JOIN
DailyFTE ON vTTAttRollup.Site = DailyFTE.Site
WHERE some 7 other company exist
GROUP BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays,
DailyFTE.Offer_Hired
ORDER BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site
The union statement will return your date range and the other 7 company data if they exist.
Ham
Subscribe to:
Posts (Atom)