Need help changing database dynamically

I have the following code. I basically want to dynamically empty all data
from every user table in every user database. I have the code that processe
s
all the databases I want and then gets all of the tables I want. My problem
is using the GO. I can't get this to run by writing USE @.dbname GO or
USE @.dbname
GO
Does anyone have a recommendation? I'm sure I have just been looking at it
to long at this point and it is easy. Thanks in advance.
-- Sydem
begin tran ProcessDataClean
declare @.dbname varchar(255)
declare @.dbtablename varchar(255)
declare @.sql varchar(255)
DECLARE ProcessDataClean_CURSOR CURSOR FOR
select name
from master.dbo.sysdatabases
where dbid > 6 and
name not in ('pubs', 'Northwinds')
order by name
OPEN ProcessDataClean_CURSOR
FETCH NEXT FROM ProcessDataClean_CURSOR INTO @.dbname
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
select @.sql = 'USE ' + @.dbname
exec sp_sqlexec @.sql
--i want to use GO right here to switch the database
--for now i'm just printing what i want to do
PRINT ''
PRINT @.sql
PRINT 'GO'
PRINT ''
DECLARE GetDBTableName_CURSOR CURSOR FOR
select name
from dbo.sysobjects
where OBJECTPROPERTY(id, N'IsUserTable') = 1
order by name
OPEN GetDBTableName_CURSOR
FETCH NEXT FROM GetDBTableName_CURSOR INTO @.dbtablename
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT 'DELETE ' + @.dbtablename
END
FETCH NEXT FROM GetDBTableName_CURSOR INTO @.dbtablename
END
CLOSE GetDBTableName_CURSOR
DEALLOCATE GetDBTableName_CURSOR
END
FETCH NEXT FROM ProcessDataClean_CURSOR INTO @.dbname
END
CLOSE ProcessDataClean_CURSOR
DEALLOCATE ProcessDataClean_CURSOR
commit tran ProcessDataClean[USE] does not accept a parameter / variable. You will have to execute the
rest of the code inside the context where you are suing "use database_name".
Example:
exec sp_msforeachdb '
if ''?'' not in (
''master'',
''msdb'',
''model'',
''tempdb'',
''pubs'',
''northwind''
)
begin
use [?]
select
table_catalog,
table_name
from
information_schema.tables
end
'
This is an example (may be a bad one), i am not recommending to use the
undocumented and unsupported sp sp_msforeachdb.
Anyway if you try to delete from tables being referenced by other tables,
you will get an error. I think it will be better if you drop the databases
and recreate them.
AMB
"Sydem" wrote:

> I have the following code. I basically want to dynamically empty all data
> from every user table in every user database. I have the code that proces
ses
> all the databases I want and then gets all of the tables I want. My probl
em
> is using the GO. I can't get this to run by writing USE @.dbname GO or
> USE @.dbname
> GO
> Does anyone have a recommendation? I'm sure I have just been looking at i
t
> to long at this point and it is easy. Thanks in advance.
> -- Sydem
>
> begin tran ProcessDataClean
> declare @.dbname varchar(255)
> declare @.dbtablename varchar(255)
> declare @.sql varchar(255)
> DECLARE ProcessDataClean_CURSOR CURSOR FOR
> select name
> from master.dbo.sysdatabases
> where dbid > 6 and
> name not in ('pubs', 'Northwinds')
> order by name
> OPEN ProcessDataClean_CURSOR
> FETCH NEXT FROM ProcessDataClean_CURSOR INTO @.dbname
> WHILE (@.@.FETCH_STATUS <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> select @.sql = 'USE ' + @.dbname
> exec sp_sqlexec @.sql
> --i want to use GO right here to switch the database
> --for now i'm just printing what i want to do
> PRINT ''
> PRINT @.sql
> PRINT 'GO'
> PRINT ''
> DECLARE GetDBTableName_CURSOR CURSOR FOR
> select name
> from dbo.sysobjects
> where OBJECTPROPERTY(id, N'IsUserTable') = 1
> order by name
> OPEN GetDBTableName_CURSOR
> FETCH NEXT FROM GetDBTableName_CURSOR INTO @.dbtablename
> WHILE (@.@.FETCH_STATUS <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> PRINT 'DELETE ' + @.dbtablename
> END
> FETCH NEXT FROM GetDBTableName_CURSOR INTO @.dbtablename
> END
> CLOSE GetDBTableName_CURSOR
> DEALLOCATE GetDBTableName_CURSOR
> END
> FETCH NEXT FROM ProcessDataClean_CURSOR INTO @.dbname
> END
> CLOSE ProcessDataClean_CURSOR
> DEALLOCATE ProcessDataClean_CURSOR
> commit tran ProcessDataClean|||Correction,

> Anyway if you try to delete from tables being referenced by other tables,
> you will get an error.
Anyway, if you try to delete from tables being referenced by other tables
and there are rows related in the child table, you will get an error.
Example:
exec ('use northwind delete [dbo].[orders]')
go
AMB
"Alejandro Mesa" wrote:
> [USE] does not accept a parameter / variable. You will have to execute the
> rest of the code inside the context where you are suing "use database_name
".
> Example:
> exec sp_msforeachdb '
> if ''?'' not in (
> ''master'',
> ''msdb'',
> ''model'',
> ''tempdb'',
> ''pubs'',
> ''northwind''
> )
> begin
> use [?]
> select
> table_catalog,
> table_name
> from
> information_schema.tables
> end
> '
> This is an example (may be a bad one), i am not recommending to use the
> undocumented and unsupported sp sp_msforeachdb.
> Anyway if you try to delete from tables being referenced by other tables,
> you will get an error. I think it will be better if you drop the databases
> and recreate them.
>
> AMB
> "Sydem" wrote:
>|||Great Alejandro. I will take a look at that.
"Alejandro Mesa" wrote:
> Correction,
>
> Anyway, if you try to delete from tables being referenced by other tables
> and there are rows related in the child table, you will get an error.
> Example:
> exec ('use northwind delete [dbo].[orders]')
> go
>
> AMB
> "Alejandro Mesa" wrote:
>

Need desperate help on a storedProc

Hi,

I'm a PHP Programmer but I need help making a storedProc.

basically, if I have orders and want to output the intials of the order taker I need help on that storedProc.

So I got the IDClientID
select idRessCreatedBy from TBL_quotation where idClient = IDclient

now i got the IDresscreatedby and I need to do a

substring(firstname,1,1) + substring(lastname,1,1) from

tbl_ress where IDresscreatedby = IDresscreatedby

Can anyone help me with this storedProc?

I would very much apreciate it

Creek:

I am guessing at a good bit of this, but maybe you have in mind something like this?

set nocount on

--
-- I am assuming that the "quotation" table is the orders table; if
-- not how is the order related to the quotation?
--
-- The request is for the "initials of the order"; therefore, it seems
-- that the "order" must be part of the information returned (Unless
-- the "order" is an input parameter!)
--
-- This is really guessing at too much.
--
declare @.quotation table
( IDquote integer,
IDClient integer,
idRessCreatedBy integer
)
insert into @.quotation values (1, 1, 1)
insert into @.quotation values (2, 1, 1)
insert into @.quotation values (3, 2, 2)

declare @.ress table
( idRessCreatedBy integer,
firstName varchar (20),
lastName varchar (20)
)
insert into @.ress values (1, 'Bob', 'Einstein')
insert into @.ress values (2, 'Dave', 'Osborne')

select a.idQuote as IDOrder,
left (b.firstName, 1) + left (b.lastName, 1) as orderInitials
from @.quotation a
inner join @.ress b
on a.idRessCreatedBy = b.idRessCreatedBy

-- Sample Output:

-- IDOrder orderInitials
-- -- -
-- 1 BE
-- 2 BE
-- 3 DO

--
-- If the query is to be based say on a given client ID you can add
-- this in
--
declare @.inputClientID integer
set @.inputClientID = 2

select a.idQuote as IDOrder,
left (b.firstName, 1) + left (b.lastName, 1) as orderInitials
from @.quotation a
inner join @.ress b
on a.idRessCreatedBy = b.idRessCreatedBy
and a.IDClient = @.inputClientID

-- Sample Output:

-- IDOrder orderInitials
-- -- -
-- 3 DO

|||

Hi,

your storedProc is a little confusing for me, as i'm just a PHP programmer.

there's no order as such, it's all quotations but the DB uses the words interchangeably. I know I didn't make the DB, lol.

what's the difference between the two you gave and what's nocount on and where does that go?

I have a clientID array that will be sent from my PHP web app to a storedProc

|||

CREATE PROCEDURE Initials_sp

-- Add the parameters for the stored procedure here

@.IDclient int ,

@.Initials NCHAR(2) OUTPUT

AS

SET @.Initials=(SELECT (substring(firstname,1,1) + substring(lastname,1,1)) as initials from tbl_ress where IDresscreatedby = (select idRessCreatedBy from TBL_quotation where idClient = @.IDclient))

Return @.Initials

|||what's

@.Initials NCHAR(2) OUTPUT?

Thanks alot man

I very much apreciate your help

|||

The difference is that one of the two assumes that you are giving a "client ID" as an argument to the procedure. The other assumes that you want a list of many orders with the initials of the order taker. The "set nocount on" is so that I do not get "count" informational messages returned when I run the script. It is DEFINITELY not a necessary part of the script -- strictly optional!

I used "Client ID" as the input to this; it is also possible that you might want the "Order ID" as an argument to this procedure instead of the "Client ID" Limno's version of a version of the stored procedure is a good example.

The potential weakness of his version of the stored procedure is that it uses an "OUTPUT PARAMETER". This has both advantages and disadvantages. In the sample data I put together I had two orders for customer ID #1. If it is possible that a customer has more than one pending orders the output parameter cannot reflect this because it is only single valued. But there are times in which a programmer prefers to receive an output parameter rather than a query result. What I returned were query results.

Dave

|||

The code "@.INITIALS NCHAR(@.) OUTPUT

is the output parameter.

|||what do I put where it says add parameters here?|||

NCHAR(2) to hold your returned initial for the output parameter. I don't know whether this link will help you to use it in code.

http://aspnet.4guysfromrolla.com/demos/printPage.aspx?path=/articles/062905-1.aspx

|||

Got it!

and what do i put for add parameters here?

I'm just a php programmer

|||

Creek:

You probably don't need to add anything. If (1) the @.IDClient parm is what you want to use for filtering and (2) you expect your output to be single valued and (3) receiving the output as an output parameter is acceptable, the procedure ought to be fine as is. In this case, just create the procedure and test it. It is reading data and should not cause any harm as written.

Dave

|||

Knob:

I was visualizing this stored procedure this morning while preparing for work and I realized that the line

Return @.Initials

was going to cause an execution error. So I compiled the stored procedure and ran it and received this error:

Server: Msg 245, Level 16, State 1, Procedure Initials_sp, Line 13
Syntax error converting the nvarchar value 'DO' to a column of data type int.

Therefore, you need to change this line

Return @.Initials

into this:

Return

And this particular execution error will be eliminated. In addition, I am still not confident that the results of the query will be singular. When I ran the stored procedure like this in query analyzer:

declare @.return_initials nchar(2)

exec initials_sp 1, @.initials = @.return_initials output

select @.return_initials as [@.return_initials]

I received this output:

Server: Msg 512, Level 16, State 1, Procedure Initials_sp, Line 11
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The 'Initials_sp' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.
@.return_initials
-
NULL

(1 row(s) affected)

HOWEVER! This error will not be received if the assumptions of my test data were incorrect. To verify whether or not we need to correct the procedure to fix this potential problem, try running this query:

if
( select count(*)
from ( select idClient,
count(*) as clientOrderCount
from tbl_quotation
group by idClient
having count(*) > 1
) x
) > 0
select 'Multiple Results ' as Results
else
select 'All Singular Results' as Results

If you receive output that looks like this:

Results
--
All Singular Results

then no additional modifications to the procedure are necessary; however, if you receive results that look like this:

Results
--
Multiple Results

then an additional modification is necessary. If you get "Multiple Results" you will need to change the procedure to something like this:

alter PROCEDURE dbo.Initials_sp

-- Add the parameters for the stored procedure here

@.IDclient int

AS

select a.idQuote,
left (b.firstName, 1) + left (b.lastName, 1) as orderInitials
from tbl_quotation a
inner join tbl_ress b
on a.idRessCreatedBy = b.idRessCreatedBy
and a.IDClient = @.IDClient

Return

|||

I ran the query and received multiple results in query analyzer.

I apreciate your help btw

if I run the alter storedproc, what do I put where it says add parameters here?

I'm just a php programmer.

Thasnk

|||

Sorry. That line was from template. I changed the end of this sp for the Return part.

You can call this stored procedure from your code(php page) with the value for your @.IDclient parameter.

--start of this sp

CREATE PROCEDURE Initials_sp

@.IDclient int ,

@.Initials NCHAR(2) OUTPUT

AS

SET @.Initials=(SELECT (substring(firstname,1,1) + substring(lastname,1,1)) as initials from tbl_ress where IDresscreatedby = (select idRessCreatedBy from TBL_quotation where idClient = @.IDclient))

Return

--end of this sp

|||

Ok, so I tried the SP and I get this error:

Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'rsGetClientIDbyRessInitials' expects parameter '@.Initials', which was not supplied., SQL state 37000 in SQLExecDirect in c:\src\db\DB_Interface.php on line 102
rsGetClientIDbyRessInitials 8

Could not execute Statement! MySQL-Error: [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'rsGetClientIDbyRessInitials' expects parameter '@.Initials', which was not supplied.

But i'm not trying to send the parameter initials, i'm only trying to send clientID and then return initials.

This is my function:

function getInitials($quotId){
$conn = new Connection();
$initialsArr = $conn->select("rsGetClientIDbyRessInitials ".$quotId);
if($result[0]['IDQuotation'] > 0){
$quotId = $this->getOriginalId($result[0]['IDQuotation']);
}
return $initialsArr;
}//end function

Can you help me out, i've been pulling my hair over this

Need desperate help on a storedProc

Hi,

I'm a PHP Programmer but I need help making a storedProc.

basically, if I have orders and want to output the intials of the order taker I need help on that storedProc.

So I got the IDClientID
select idRessCreatedBy from TBL_quotation where idClient = IDclient

now i got the IDresscreatedby and I need to do a

substring(firstname,1,1) + substring(lastname,1,1) from

tbl_ress where IDresscreatedby = IDresscreatedby

Can anyone help me with this storedProc?

I would very much apreciate it

Creek:

I am guessing at a good bit of this, but maybe you have in mind something like this?

set nocount on

--
-- I am assuming that the "quotation" table is the orders table; if
-- not how is the order related to the quotation?
--
-- The request is for the "initials of the order"; therefore, it seems
-- that the "order" must be part of the information returned (Unless
-- the "order" is an input parameter!)
--
-- This is really guessing at too much.
--
declare @.quotation table
( IDquote integer,
IDClient integer,
idRessCreatedBy integer
)
insert into @.quotation values (1, 1, 1)
insert into @.quotation values (2, 1, 1)
insert into @.quotation values (3, 2, 2)

declare @.ress table
( idRessCreatedBy integer,
firstName varchar (20),
lastName varchar (20)
)
insert into @.ress values (1, 'Bob', 'Einstein')
insert into @.ress values (2, 'Dave', 'Osborne')

select a.idQuote as IDOrder,
left (b.firstName, 1) + left (b.lastName, 1) as orderInitials
from @.quotation a
inner join @.ress b
on a.idRessCreatedBy = b.idRessCreatedBy

-- Sample Output:

-- IDOrder orderInitials
-- -- -
-- 1 BE
-- 2 BE
-- 3 DO

--
-- If the query is to be based say on a given client ID you can add
-- this in
--
declare @.inputClientID integer
set @.inputClientID = 2

select a.idQuote as IDOrder,
left (b.firstName, 1) + left (b.lastName, 1) as orderInitials
from @.quotation a
inner join @.ress b
on a.idRessCreatedBy = b.idRessCreatedBy
and a.IDClient = @.inputClientID

-- Sample Output:

-- IDOrder orderInitials
-- -- -
-- 3 DO

|||

Hi,

your storedProc is a little confusing for me, as i'm just a PHP programmer.

there's no order as such, it's all quotations but the DB uses the words interchangeably. I know I didn't make the DB, lol.

what's the difference between the two you gave and what's nocount on and where does that go?

I have a clientID array that will be sent from my PHP web app to a storedProc

|||

CREATE PROCEDURE Initials_sp

-- Add the parameters for the stored procedure here

@.IDclient int ,

@.Initials NCHAR(2) OUTPUT

AS

SET @.Initials=(SELECT (substring(firstname,1,1) + substring(lastname,1,1)) as initials from tbl_ress where IDresscreatedby = (select idRessCreatedBy from TBL_quotation where idClient = @.IDclient))

Return @.Initials

|||what's

@.Initials NCHAR(2) OUTPUT?

Thanks alot man

I very much apreciate your help

|||

The difference is that one of the two assumes that you are giving a "client ID" as an argument to the procedure. The other assumes that you want a list of many orders with the initials of the order taker. The "set nocount on" is so that I do not get "count" informational messages returned when I run the script. It is DEFINITELY not a necessary part of the script -- strictly optional!

I used "Client ID" as the input to this; it is also possible that you might want the "Order ID" as an argument to this procedure instead of the "Client ID" Limno's version of a version of the stored procedure is a good example.

The potential weakness of his version of the stored procedure is that it uses an "OUTPUT PARAMETER". This has both advantages and disadvantages. In the sample data I put together I had two orders for customer ID #1. If it is possible that a customer has more than one pending orders the output parameter cannot reflect this because it is only single valued. But there are times in which a programmer prefers to receive an output parameter rather than a query result. What I returned were query results.

Dave

|||

The code "@.INITIALS NCHAR(@.) OUTPUT

is the output parameter.

|||what do I put where it says add parameters here?|||

NCHAR(2) to hold your returned initial for the output parameter. I don't know whether this link will help you to use it in code.

http://aspnet.4guysfromrolla.com/demos/printPage.aspx?path=/articles/062905-1.aspx

|||

Got it!

and what do i put for add parameters here?

I'm just a php programmer

|||

Creek:

You probably don't need to add anything. If (1) the @.IDClient parm is what you want to use for filtering and (2) you expect your output to be single valued and (3) receiving the output as an output parameter is acceptable, the procedure ought to be fine as is. In this case, just create the procedure and test it. It is reading data and should not cause any harm as written.

Dave

|||

Knob:

I was visualizing this stored procedure this morning while preparing for work and I realized that the line

Return @.Initials

was going to cause an execution error. So I compiled the stored procedure and ran it and received this error:

Server: Msg 245, Level 16, State 1, Procedure Initials_sp, Line 13
Syntax error converting the nvarchar value 'DO' to a column of data type int.

Therefore, you need to change this line

Return @.Initials

into this:

Return

And this particular execution error will be eliminated. In addition, I am still not confident that the results of the query will be singular. When I ran the stored procedure like this in query analyzer:

declare @.return_initials nchar(2)

exec initials_sp 1, @.initials = @.return_initials output

select @.return_initials as [@.return_initials]

I received this output:

Server: Msg 512, Level 16, State 1, Procedure Initials_sp, Line 11
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The 'Initials_sp' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.
@.return_initials
-
NULL

(1 row(s) affected)

HOWEVER! This error will not be received if the assumptions of my test data were incorrect. To verify whether or not we need to correct the procedure to fix this potential problem, try running this query:

if
( select count(*)
from ( select idClient,
count(*) as clientOrderCount
from tbl_quotation
group by idClient
having count(*) > 1
) x
) > 0
select 'Multiple Results ' as Results
else
select 'All Singular Results' as Results

If you receive output that looks like this:

Results
--
All Singular Results

then no additional modifications to the procedure are necessary; however, if you receive results that look like this:

Results
--
Multiple Results

then an additional modification is necessary. If you get "Multiple Results" you will need to change the procedure to something like this:

alter PROCEDURE dbo.Initials_sp

-- Add the parameters for the stored procedure here

@.IDclient int

AS

select a.idQuote,
left (b.firstName, 1) + left (b.lastName, 1) as orderInitials
from tbl_quotation a
inner join tbl_ress b
on a.idRessCreatedBy = b.idRessCreatedBy
and a.IDClient = @.IDClient

Return

|||

I ran the query and received multiple results in query analyzer.

I apreciate your help btw

if I run the alter storedproc, what do I put where it says add parameters here?

I'm just a php programmer.

Thasnk

|||

Sorry. That line was from template. I changed the end of this sp for the Return part.

You can call this stored procedure from your code(php page) with the value for your @.IDclient parameter.

--start of this sp

CREATE PROCEDURE Initials_sp

@.IDclient int ,

@.Initials NCHAR(2) OUTPUT

AS

SET @.Initials=(SELECT (substring(firstname,1,1) + substring(lastname,1,1)) as initials from tbl_ress where IDresscreatedby = (select idRessCreatedBy from TBL_quotation where idClient = @.IDclient))

Return

--end of this sp

|||

Ok, so I tried the SP and I get this error:

Warning: odbc_exec() [function.odbc-exec]: SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'rsGetClientIDbyRessInitials' expects parameter '@.Initials', which was not supplied., SQL state 37000 in SQLExecDirect in c:\src\db\DB_Interface.php on line 102
rsGetClientIDbyRessInitials 8

Could not execute Statement! MySQL-Error: [Microsoft][ODBC SQL Server Driver][SQL Server]Procedure 'rsGetClientIDbyRessInitials' expects parameter '@.Initials', which was not supplied.

But i'm not trying to send the parameter initials, i'm only trying to send clientID and then return initials.

This is my function:

function getInitials($quotId){
$conn = new Connection();
$initialsArr = $conn->select("rsGetClientIDbyRessInitials ".$quotId);
if($result[0]['IDQuotation'] > 0){
$quotId = $this->getOriginalId($result[0]['IDQuotation']);
}
return $initialsArr;
}//end function

Can you help me out, i've been pulling my hair over this

Need a way to switch specific data from columns

Basically I have 635k records in a table with a person's first name, and date of birth (other stuff but it's not relavent). I imported all the data from excel files, but somehow a bunch of records got the first name and date of birth mixed up, so I'm trying to write a stored procedure that would switch the first name with the date of birth wherever the firstname is purely numeric, or something of the sort. Now records are in fact repeated so another possible but more time taking solution is to write a stored procedure that I give the date of birth and it does the switching around for the respective date of birth when it's found inside the First name. Any suggestions? All the code I've written has proved useless :/

so I'm trying to write a stored procedure that would switch the first name with the date of birth wherever the firstname is purely numeric, or something of the sort.

If you have an ID column all you would need to do is check if there are multiple records (count(*)> 1). if so keep the first one (min(id) or whichever you choose), delete the rest, get the two values into local variables and update the record in hand.
if you already made an attempt post some code and we can help you out.