|
|
You're in the
sql server dts
group:Setting sql result to global variable?
sql server dts:
..The below code is included in the "Use ActiveX Script" area of an "Execute SQL Task" in DTS. I know it's a sloppy loop, but I'm a newbie, and it works, so... Anyways, the sqlstatement checks if a table exists, if it does, it appends an "a" to the tablename and creates a new table, if "a" table exists as well, then it creates a "b" table and so on... What I'm trying to do is set a dts global variable equal to whatever table is eventually produced. Any tips or links are greatly appreciated! Thanks! Function Main() Dim sqlstatement, objPkg, ExecSQL sqlstatement = "IF object_id('dbo.de_"& dtname() &"') IS NULL CREATE TABLE [de_"& dtname() &"] (tables,etc...) ON [PRIMARY] ELSE IF object_id('dbo.de_"& dtname() &"a') IS NULL CREATE TABLE [de_"& dtname() &"a] (tables,etc...) ON [PRIMARY] ELSE IF object_id('dbo.de_"& dtname() &"b') IS NULL CREATE TABLE [de_"& dtname() &"b] (tables,etc...) ON [PRIMARY] ELSE IF object_id('dbo.de_"& dtname() &"c') IS NULL CREATE TABLE [de_"& dtname() &"c] (tables,etc...) ON [PRIMARY] ELSE IF object_id('dbo.de_"& dtname() &"d') IS NULL CREATE TABLE [de_"& dtname() &"d] (tables,etc...) ON [PRIMARY] ELSE IF object_id('dbo.de_"& dtname() &"e') IS NULL CREATE TABLE [de_"& dtname() &"e] (tables,etc...) ON [PRIMARY] ELSE IF object_id('dbo.de_"& dtname() &"f') IS NULL CREATE TABLE [de_"& dtname() &"f] (tables,etc...) ON [PRIMARY] ELSE IF object_id('dbo.de_"& dtname() &"g') IS NULL CREATE TABLE [de_"& dtname() &"g] (tables,etc...) ON [PRIMARY]" ' Whatever table is eventually created above is what I wish to set the global variable Raw_Table to DTSGlobalVariables("Raw_Table").Value = ' Getting sql script to execute Set objPkg = DTSGlobalVariables.Parent Set ExecSQL = objPkg.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask ExecSQL.SQLStatement = sqlstatement ' Reset object variables Set ExecSQL = Nothing Set objPkg = Nothing Main = DTSTaskExecResult_Success End Function ' Creates date-based name for table, ex., "de_0101" Function dtname() Dim d d = date() d = Right("0" & Month(d), 2) & Right("0" & Day(d), 2) dtname = d End Function
No, I didn't know I could do it all in T-SQL... Basically, my task is to
take many different sql queries and combine them into one automated procedure of which the "execute sql task" below is a part (it's a process that takes an initial input file and processes it, saves results to various tables, then creates a final completed .txt file that another company uses to produce physical output). I assumed that a DTS Package was the easiest route. Is it? I'll check out the link. BTW, your site has already been extremely helpful to me, thanks! [quoted text, click to view] "Darren Green" <darren.green@reply-to-newsgroup-sqldts.com> wrote in message news:uB$SYhbaEHA.4032@TK2MSFTNGP11.phx.gbl... > Hmm, you do know that you could do all of that in T-SQL? Makes my eyes hurt > trying to look at it! Yes I know it works.... > > To get a value out of a task, then you need to use a resultset, so just use > SELECT 'TableNameXX' AS TableName as the last statement or thereabouts. > > I suspect the design-time validation will fail, so use the workaround, of > some dummy SQL to set-up the parameter mapping, then the real SQL will get > stuffed in at run-time by your script task. The technique is described in > the "Input and Output Parameters" section of this article. > > I also suggest you use SET NOCOUNT ON at the top of your script, also > described in the article. > > Global Variables and Stored Procedure Parameters > (http://www.sqldts.com/default.aspx?234) > > -- > Darren Green > http://www.sqldts.com > > "Sayonara" <me@msn.com> wrote in message > news:2ll0mpFe2sacU1@uni-berlin.de... > > Hey all, > > > > .The below code is included in the "Use ActiveX Script" area of an > "Execute > > SQL Task" in DTS. I know it's a sloppy loop, but I'm a newbie, and it > works, > > so... Anyways, the sqlstatement checks if a table exists, if it does, it > > appends an "a" to the tablename and creates a new table, if "a" table > exists > > as well, then it creates a "b" table and so on... > > > > What I'm trying to do is set a dts global variable equal to whatever table > > is eventually produced. Any tips or links are greatly appreciated! Thanks! > > > > > > Function Main() > > > > Dim sqlstatement, objPkg, ExecSQL > > > > sqlstatement = "IF object_id('dbo.de_"& dtname() &"') IS NULL CREATE > TABLE > > [de_"& dtname() &"] (tables,etc...) ON [PRIMARY] ELSE IF > > object_id('dbo.de_"& dtname() &"a') IS NULL CREATE TABLE [de_"& dtname() > > &"a] (tables,etc...) ON [PRIMARY] ELSE IF object_id('dbo.de_"& dtname() > > &"b') IS NULL CREATE TABLE [de_"& dtname() &"b] (tables,etc...) ON > [PRIMARY] > > ELSE IF object_id('dbo.de_"& dtname() &"c') IS NULL CREATE TABLE [de_"& > > dtname() &"c] (tables,etc...) ON [PRIMARY] ELSE IF object_id('dbo.de_"& > > dtname() &"d') IS NULL CREATE TABLE [de_"& dtname() &"d] (tables,etc...) > ON > > [PRIMARY] ELSE IF object_id('dbo.de_"& dtname() &"e') IS NULL CREATE TABLE > > [de_"& dtname() &"e] (tables,etc...) ON [PRIMARY] ELSE IF > > object_id('dbo.de_"& dtname() &"f') IS NULL CREATE TABLE [de_"& dtname() > > &"f] (tables,etc...) ON [PRIMARY] ELSE IF object_id('dbo.de_"& dtname() > > &"g') IS NULL CREATE TABLE [de_"& dtname() &"g] (tables,etc...) ON > > [PRIMARY]" > > > > > > ' Whatever table is eventually created above is what I wish to set the > > global variable Raw_Table to > > > > DTSGlobalVariables("Raw_Table").Value = > > > > > > ' Getting sql script to execute > > > > Set objPkg = DTSGlobalVariables.Parent > > Set ExecSQL = objPkg.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask > > ExecSQL.SQLStatement = sqlstatement > > > > ' Reset object variables > > > > Set ExecSQL = Nothing > > Set objPkg = Nothing > > > > Main = DTSTaskExecResult_Success > > > > End Function > > > > > > ' Creates date-based name for table, ex., "de_0101" > > Function dtname() > > Dim d > > d = date() > > d = Right("0" & Month(d), 2) & Right("0" & Day(d), 2) > > dtname = d > > End Function > > > > > >
The article you specified noted an "Intermediate Level" of knowledge is
required. Maybe I haven't hit that level yet, but I can't seem to grasp how to do what you say... Basically, you're saying to take the loop I have currently and convert it into a stored procedure, then somehow set the return value of that procedure equal to the global variable? How can I set the return value equal to said variable? Doesn't seem possible... Thanks! [quoted text, click to view] "Darren Green" <darren.green@reply-to-newsgroup-sqldts.com> wrote in message news:WEIrp+PsrW9AFwQl@sqldts.com... > Doing it in T-SQL, I only I meant the bit about checking if a table > exists and creating a different one, with date parts in the name, etc, > the long SQL you generate could just be done in a single T-SQL relieving > you of the need to generate the statement each time. Basically you could > loose that ActX script you posted below, and just have the Exec SQL > Task. > > > Darren > > In message <2ll315Fd49avU1@uni-berlin.de>, Sayonara <me@msn.com> writes > >No, I didn't know I could do it all in T-SQL... Basically, my task is to > >take many different sql queries and combine them into one automated > >procedure of which the "execute sql task" below is a part (it's a process > >that takes an initial input file and processes it, saves results to various > >tables, then creates a final completed .txt file that another company uses > >to produce physical output). I assumed that a DTS Package was the easiest > >route. Is it? > > > >I'll check out the link. BTW, your site has already been extremely helpful > >to me, thanks! > > > >"Darren Green" <darren.green@reply-to-newsgroup-sqldts.com> wrote in message > >news:uB$SYhbaEHA.4032@TK2MSFTNGP11.phx.gbl... > >> Hmm, you do know that you could do all of that in T-SQL? Makes my eyes > >hurt > >> trying to look at it! Yes I know it works.... > >> > >> To get a value out of a task, then you need to use a resultset, so just > >use > >> SELECT 'TableNameXX' AS TableName as the last statement or thereabouts. > >> > >> I suspect the design-time validation will fail, so use the workaround, of > >> some dummy SQL to set-up the parameter mapping, then the real SQL will get > >> stuffed in at run-time by your script task. The technique is described in > >> the "Input and Output Parameters" section of this article. > >> > >> I also suggest you use SET NOCOUNT ON at the top of your script, also > >> described in the article. > >> > >> Global Variables and Stored Procedure Parameters > >> (http://www.sqldts.com/default.aspx?234) > >> > >> -- > >> Darren Green > >> http://www.sqldts.com > >> > >> "Sayonara" <me@msn.com> wrote in message > >> news:2ll0mpFe2sacU1@uni-berlin.de... > >> > Hey all, > >> > > >> > .The below code is included in the "Use ActiveX Script" area of an > >> "Execute > >> > SQL Task" in DTS. I know it's a sloppy loop, but I'm a newbie, and it > >> works, > >> > so... Anyways, the sqlstatement checks if a table exists, if it does, it > >> > appends an "a" to the tablename and creates a new table, if "a" table > >> exists > >> > as well, then it creates a "b" table and so on... > >> > > >> > What I'm trying to do is set a dts global variable equal to whatever > >table > >> > is eventually produced. Any tips or links are greatly appreciated! > >Thanks! > >> > > >> > > >> > Function Main() > >> > > >> > Dim sqlstatement, objPkg, ExecSQL > >> > > >> > sqlstatement = "IF object_id('dbo.de_"& dtname() &"') IS NULL CREATE > >> TABLE > >> > [de_"& dtname() &"] (tables,etc...) ON [PRIMARY] ELSE IF > >> > object_id('dbo.de_"& dtname() &"a') IS NULL CREATE TABLE [de_"& dtname() > >> > &"a] (tables,etc...) ON [PRIMARY] ELSE IF object_id('dbo.de_"& dtname() > >> > &"b') IS NULL CREATE TABLE [de_"& dtname() &"b] (tables,etc...) ON > >> [PRIMARY] > >> > ELSE IF object_id('dbo.de_"& dtname() &"c') IS NULL CREATE TABLE [de_"& > >> > dtname() &"c] (tables,etc...) ON [PRIMARY] ELSE IF object_id('dbo.de_"& > >> > dtname() &"d') IS NULL CREATE TABLE [de_"& dtname() &"d] (tables,etc...) > >> ON > >> > [PRIMARY] ELSE IF object_id('dbo.de_"& dtname() &"e') IS NULL CREATE > >TABLE > >> > [de_"& dtname() &"e] (tables,etc...) ON [PRIMARY] ELSE IF > >> > object_id('dbo.de_"& dtname() &"f') IS NULL CREATE TABLE [de_"& dtname() > >> > &"f] (tables,etc...) ON [PRIMARY] ELSE IF object_id('dbo.de_"& dtname() > >> > &"g') IS NULL CREATE TABLE [de_"& dtname() &"g] (tables,etc...) ON > >> > [PRIMARY]" > >> > > >> > > >> > ' Whatever table is eventually created above is what I wish to set the > >> > global variable Raw_Table to > >> > > >> > DTSGlobalVariables("Raw_Table").Value = > >> > > >> > > >> > ' Getting sql script to execute > >> > > >> > Set objPkg = DTSGlobalVariables.Parent > >> > Set ExecSQL = objPkg.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask > >> > ExecSQL.SQLStatement = sqlstatement > >> > > >> > ' Reset object variables > >> > > >> > Set ExecSQL = Nothing > >> > Set objPkg = Nothing > >> > > >> > Main = DTSTaskExecResult_Success > >> > > >> > End Function > >> > > >> > > >> > ' Creates date-based name for table, ex., "de_0101" > >> > Function dtname() > >> > Dim d > >> > d = date() > >> > d = Right("0" & Month(d), 2) & Right("0" & Day(d), 2) > >> > dtname = d > >> > End Function > >> > > >> > > >> > >> > > > > > > -- > Darren Green (SQL Server MVP) > DTS - http://www.sqldts.com > > PASS - the definitive, global community for SQL Server professionals > http://www.sqlpass.org >
Hmm, you do know that you could do all of that in T-SQL? Makes my eyes hurt
trying to look at it! Yes I know it works.... To get a value out of a task, then you need to use a resultset, so just use SELECT 'TableNameXX' AS TableName as the last statement or thereabouts. I suspect the design-time validation will fail, so use the workaround, of some dummy SQL to set-up the parameter mapping, then the real SQL will get stuffed in at run-time by your script task. The technique is described in the "Input and Output Parameters" section of this article. I also suggest you use SET NOCOUNT ON at the top of your script, also described in the article. Global Variables and Stored Procedure Parameters (http://www.sqldts.com/default.aspx?234) -- Darren Green http://www.sqldts.com [quoted text, click to view] "Sayonara" <me@msn.com> wrote in message news:2ll0mpFe2sacU1@uni-berlin.de... > Hey all, > > .The below code is included in the "Use ActiveX Script" area of an "Execute > SQL Task" in DTS. I know it's a sloppy loop, but I'm a newbie, and it works, > so... Anyways, the sqlstatement checks if a table exists, if it does, it > appends an "a" to the tablename and creates a new table, if "a" table exists > as well, then it creates a "b" table and so on... > > What I'm trying to do is set a dts global variable equal to whatever table > is eventually produced. Any tips or links are greatly appreciated! Thanks! > > > Function Main() > > Dim sqlstatement, objPkg, ExecSQL > > sqlstatement = "IF object_id('dbo.de_"& dtname() &"') IS NULL CREATE TABLE > [de_"& dtname() &"] (tables,etc...) ON [PRIMARY] ELSE IF > object_id('dbo.de_"& dtname() &"a') IS NULL CREATE TABLE [de_"& dtname() > &"a] (tables,etc...) ON [PRIMARY] ELSE IF object_id('dbo.de_"& dtname() > &"b') IS NULL CREATE TABLE [de_"& dtname() &"b] (tables,etc...) ON [PRIMARY] > ELSE IF object_id('dbo.de_"& dtname() &"c') IS NULL CREATE TABLE [de_"& > dtname() &"c] (tables,etc...) ON [PRIMARY] ELSE IF object_id('dbo.de_"& > dtname() &"d') IS NULL CREATE TABLE [de_"& dtname() &"d] (tables,etc...) ON > [PRIMARY] ELSE IF object_id('dbo.de_"& dtname() &"e') IS NULL CREATE TABLE > [de_"& dtname() &"e] (tables,etc...) ON [PRIMARY] ELSE IF > object_id('dbo.de_"& dtname() &"f') IS NULL CREATE TABLE [de_"& dtname() > &"f] (tables,etc...) ON [PRIMARY] ELSE IF object_id('dbo.de_"& dtname() > &"g') IS NULL CREATE TABLE [de_"& dtname() &"g] (tables,etc...) ON > [PRIMARY]" > > > ' Whatever table is eventually created above is what I wish to set the > global variable Raw_Table to > > DTSGlobalVariables("Raw_Table").Value = > > > ' Getting sql script to execute > > Set objPkg = DTSGlobalVariables.Parent > Set ExecSQL = objPkg.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask > ExecSQL.SQLStatement = sqlstatement > > ' Reset object variables > > Set ExecSQL = Nothing > Set objPkg = Nothing > > Main = DTSTaskExecResult_Success > > End Function > > > ' Creates date-based name for table, ex., "de_0101" > Function dtname() > Dim d > d = date() > d = Right("0" & Month(d), 2) & Right("0" & Day(d), 2) > dtname = d > End Function > >
Thank you tremendously Darren, make no mistake, I deeply appreciate you
sharing your time and experience with me. Having said that, I suspect that while I'm not in over my head, I'm definitely treading water... You've already done so much, but is there anyway you could go through this and comment each section? I could just cut n paste in all this stuff, but I'm not truly understanding *why.* I don't want to be the atypical monkey-in-a-spaceshuttle if you know what I mean... I want to learn the what, why's, & hows so I become a code-giver, not a code-taker. Thanks! [quoted text, click to view] "Darren Green" <darren.green@reply-to-newsgroup-sqldts.com> wrote in message news:lf2XPtTVca9AFw2A@sqldts.com... > In message <2llbk4Fe7l6jU1@uni-berlin.de>, Sayonara <me@msn.com> writes > >The article you specified noted an "Intermediate Level" of knowledge is > >required. Maybe I haven't hit that level yet, but I can't seem to grasp how > >to do what you say... > > > >Basically, you're saying to take the loop I have currently and convert it > >into a stored procedure, then somehow set the return value of that procedure > >equal to the global variable? > > Add the following SQL to an Execute SQL Task, and map the Output > Parameter "TableName", using "Row Value" to your global variable. > > > SET NOCOUNT ON > DECLARE @TableNameBase nvarchar(128) > DECLARE @TableName nvarchar(128) > DECLARE @Suffix int > DECLARE @CreateTable nvarchar(4000) > > SET @Suffix = 97 > SET @TableNameBase = 'de_' + RIGHT(CONVERT(char(8), CURRENT_TIMESTAMP, > 112), 4) > SET @TableName = @TableNameBase > > WHILE EXISTS(SELECT 1 FROM dbo.sysobjects WHERE name = @TableName AND > type = 'U') > BEGIN > SET @TableName = @TableNameBase + CHAR(@Suffix) > SET @Suffix = @Suffix + 1 > END > > > SET @CreateTable = ' > CREATE TABLE ' + @TableName + ' > ( > Col1 int NOT NULL, > Col2 int NOT NULL > )' > > EXEC(@CreateTable) > > SELECT @TableName AS TableName > > -- > Darren Green (SQL Server MVP) > DTS - http://www.sqldts.com > > PASS - the definitive, global community for SQL Server professionals > http://www.sqlpass.org >
Doing it in T-SQL, I only I meant the bit about checking if a table
exists and creating a different one, with date parts in the name, etc, the long SQL you generate could just be done in a single T-SQL relieving you of the need to generate the statement each time. Basically you could loose that ActX script you posted below, and just have the Exec SQL Task. Darren In message <2ll315Fd49avU1@uni-berlin.de>, Sayonara <me@msn.com> writes [quoted text, click to view] >No, I didn't know I could do it all in T-SQL... Basically, my task is to >take many different sql queries and combine them into one automated >procedure of which the "execute sql task" below is a part (it's a process >that takes an initial input file and processes it, saves results to various >tables, then creates a final completed .txt file that another company uses >to produce physical output). I assumed that a DTS Package was the easiest >route. Is it? > >I'll check out the link. BTW, your site has already been extremely helpful >to me, thanks! > >"Darren Green" <darren.green@reply-to-newsgroup-sqldts.com> wrote in message >news:uB$SYhbaEHA.4032@TK2MSFTNGP11.phx.gbl... >> Hmm, you do know that you could do all of that in T-SQL? Makes my eyes >hurt >> trying to look at it! Yes I know it works.... >> >> To get a value out of a task, then you need to use a resultset, so just >use >> SELECT 'TableNameXX' AS TableName as the last statement or thereabouts. >> >> I suspect the design-time validation will fail, so use the workaround, of >> some dummy SQL to set-up the parameter mapping, then the real SQL will get >> stuffed in at run-time by your script task. The technique is described in >> the "Input and Output Parameters" section of this article. >> >> I also suggest you use SET NOCOUNT ON at the top of your script, also >> described in the article. >> >> Global Variables and Stored Procedure Parameters >> (http://www.sqldts.com/default.aspx?234) >> >> -- >> Darren Green >> http://www.sqldts.com >> >> "Sayonara" <me@msn.com> wrote in message >> news:2ll0mpFe2sacU1@uni-berlin.de... >> > Hey all, >> > >> > .The below code is included in the "Use ActiveX Script" area of an >> "Execute >> > SQL Task" in DTS. I know it's a sloppy loop, but I'm a newbie, and it >> works, >> > so... Anyways, the sqlstatement checks if a table exists, if it does, it >> > appends an "a" to the tablename and creates a new table, if "a" table >> exists >> > as well, then it creates a "b" table and so on... >> > >> > What I'm trying to do is set a dts global variable equal to whatever >table >> > is eventually produced. Any tips or links are greatly appreciated! >Thanks! >> > >> > >> > Function Main() >> > >> > Dim sqlstatement, objPkg, ExecSQL >> > >> > sqlstatement = "IF object_id('dbo.de_"& dtname() &"') IS NULL CREATE >> TABLE >> > [de_"& dtname() &"] (tables,etc...) ON [PRIMARY] ELSE IF >> > object_id('dbo.de_"& dtname() &"a') IS NULL CREATE TABLE [de_"& dtname() >> > &"a] (tables,etc...) ON [PRIMARY] ELSE IF object_id('dbo.de_"& dtname() >> > &"b') IS NULL CREATE TABLE [de_"& dtname() &"b] (tables,etc...) ON >> [PRIMARY] >> > ELSE IF object_id('dbo.de_"& dtname() &"c') IS NULL CREATE TABLE [de_"& >> > dtname() &"c] (tables,etc...) ON [PRIMARY] ELSE IF object_id('dbo.de_"& >> > dtname() &"d') IS NULL CREATE TABLE [de_"& dtname() &"d] (tables,etc...) >> ON >> > [PRIMARY] ELSE IF object_id('dbo.de_"& dtname() &"e') IS NULL CREATE >TABLE >> > [de_"& dtname() &"e] (tables,etc...) ON [PRIMARY] ELSE IF >> > object_id('dbo.de_"& dtname() &"f') IS NULL CREATE TABLE [de_"& dtname() >> > &"f] (tables,etc...) ON [PRIMARY] ELSE IF object_id('dbo.de_"& dtname() >> > &"g') IS NULL CREATE TABLE [de_"& dtname() &"g] (tables,etc...) ON >> > [PRIMARY]" >> > >> > >> > ' Whatever table is eventually created above is what I wish to set the >> > global variable Raw_Table to >> > >> > DTSGlobalVariables("Raw_Table").Value = >> > >> > >> > ' Getting sql script to execute >> > >> > Set objPkg = DTSGlobalVariables.Parent >> > Set ExecSQL = objPkg.Tasks("DTSTask_DTSExecuteSQLTask_1").CustomTask >> > ExecSQL.SQLStatement = sqlstatement >> > >> > ' Reset object variables >> > >> > Set ExecSQL = Nothing >> > Set objPkg = Nothing >> > >> > Main = DTSTaskExecResult_Success >> > >> > End Function >> > >> > >> > ' Creates date-based name for table, ex., "de_0101" >> > Function dtname() >> > Dim d >> > d = date() >> > d = Right("0" & Month(d), 2) & Right("0" & Day(d), 2) >> > dtname = d >> > End Function >> > >> > >> >> > > -- Darren Green (SQL Server MVP) DTS - http://www.sqldts.com PASS - the definitive, global community for SQL Server professionals http://www.sqlpass.org
In message <2llbk4Fe7l6jU1@uni-berlin.de>, Sayonara <me@msn.com> writes
[quoted text, click to view] >The article you specified noted an "Intermediate Level" of knowledge is >required. Maybe I haven't hit that level yet, but I can't seem to grasp how >to do what you say... > >Basically, you're saying to take the loop I have currently and convert it >into a stored procedure, then somehow set the return value of that procedure >equal to the global variable? Add the following SQL to an Execute SQL Task, and map the Output Parameter "TableName", using "Row Value" to your global variable. SET NOCOUNT ON DECLARE @TableNameBase nvarchar(128) DECLARE @TableName nvarchar(128) DECLARE @Suffix int DECLARE @CreateTable nvarchar(4000) SET @Suffix = 97 SET @TableNameBase = 'de_' + RIGHT(CONVERT(char(8), CURRENT_TIMESTAMP, 112), 4) SET @TableName = @TableNameBase WHILE EXISTS(SELECT 1 FROM dbo.sysobjects WHERE name = @TableName AND type = 'U') BEGIN SET @TableName = @TableNameBase + CHAR(@Suffix) SET @Suffix = @Suffix + 1 END SET @CreateTable = ' CREATE TABLE ' + @TableName + ' ( Col1 int NOT NULL, Col2 int NOT NULL )' EXEC(@CreateTable) SELECT @TableName AS TableName -- Darren Green (SQL Server MVP) DTS - http://www.sqldts.com PASS - the definitive, global community for SQL Server professionals http://www.sqlpass.org
Yes, it appears to work, though I'd have to swap out the incrementing
97, 98, etc... for a series of if statements (my boss really wants his tables in "a, b, c" format. Basically, this is where my confusion lies. [quoted text, click to view] > > > SELECT @TableName AS TableName Your comment concerning "Row Value" is confusing me (can't find any reference in books online). How do I get the SQL variable @TableName to talk to my DTS global variable? Thanks!
Firstly does it work, and do what you want?
What area don't you understand? [quoted text, click to view] "Sayonara" <me@msn.com> wrote in message news:2llpmsFeag3gU1@uni-berlin.de... > Thank you tremendously Darren, make no mistake, I deeply appreciate you > sharing your time and experience with me. > > Having said that, I suspect that while I'm not in over my head, I'm > definitely treading water... You've already done so much, but is there > anyway you could go through this and comment each section? I could just cut > n paste in all this stuff, but I'm not truly understanding *why.* I don't > want to be the atypical monkey-in-a-spaceshuttle if you know what I mean... > I want to learn the what, why's, & hows so I become a code-giver, not a > code-taker. > > Thanks! > > "Darren Green" <darren.green@reply-to-newsgroup-sqldts.com> wrote in message > news:lf2XPtTVca9AFw2A@sqldts.com... > > In message <2llbk4Fe7l6jU1@uni-berlin.de>, Sayonara <me@msn.com> writes > > >The article you specified noted an "Intermediate Level" of knowledge is > > >required. Maybe I haven't hit that level yet, but I can't seem to grasp > how > > >to do what you say... > > > > > >Basically, you're saying to take the loop I have currently and convert it > > >into a stored procedure, then somehow set the return value of that > procedure > > >equal to the global variable? > > > > Add the following SQL to an Execute SQL Task, and map the Output > > Parameter "TableName", using "Row Value" to your global variable. > > > > > > SET NOCOUNT ON > > DECLARE @TableNameBase nvarchar(128) > > DECLARE @TableName nvarchar(128) > > DECLARE @Suffix int > > DECLARE @CreateTable nvarchar(4000) > > > > SET @Suffix = 97 > > SET @TableNameBase = 'de_' + RIGHT(CONVERT(char(8), CURRENT_TIMESTAMP, > > 112), 4) > > SET @TableName = @TableNameBase > > > > WHILE EXISTS(SELECT 1 FROM dbo.sysobjects WHERE name = @TableName AND > > type = 'U') > > BEGIN > > SET @TableName = @TableNameBase + CHAR(@Suffix) > > SET @Suffix = @Suffix + 1 > > END > > > > > > SET @CreateTable = ' > > CREATE TABLE ' + @TableName + ' > > ( > > Col1 int NOT NULL, > > Col2 int NOT NULL > > )' > > > > EXEC(@CreateTable) > > > > SELECT @TableName AS TableName > > > > -- > > Darren Green (SQL Server MVP) > > DTS - http://www.sqldts.com > > > > PASS - the definitive, global community for SQL Server professionals > > http://www.sqlpass.org > > > >
[quoted text, click to view]
> Yes, it appears to work, though I'd have to swap out the incrementing > 97, 98, etc... for a series of if statements (my boss really wants his > tables in "a, b, c" format. Duh. 97 is the numeric reference for "a." I'm gettin there...
n/m Darren, I got it. Geeez! I knew what I was doing all along, I just
didn't know that I knew, ya know? :) Anyways, thanks for the tremendous help!
In message <2lnmu1Fenk77U1@uni-berlin.de>, Sayonara <me@msn.com> writes
[quoted text, click to view] >n/m Darren, I got it. Geeez! I knew what I was doing all along, I just >didn't know that I knew, ya know? :) > >Anyways, thanks for the tremendous help! > > Well done, glad you've got in cracked. I knew you'd get it if I left you for a bit :) -- Darren Green (SQL Server MVP) DTS - http://www.sqldts.com PASS - the definitive, global community for SQL Server professionals http://www.sqlpass.org
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |