ERROR: A variable may only be added once to either the read lock list or the write lock list.

Hi,
I have set of 2 DTS packages, one of which calls the other by forming a command-line (dtexec) using a Execute Process task.

From the parent package-> Execute Process Task->
dtsexec /F etc... /<pkg variable> = "servername"

Each of the parent and the called package have a variable: "User::DWServerSQLInstance" which is mapped to the SQL server connection manager server name property using an expression. The outer package has the above variable and so does the inner called package (which gets assigned through the command line from the outerpackage call to inner)

I "sometimes" get the following error:

OnError,I4,TESTDOM\Administrator,ACDWAggregation,{A1F8E43F-15F1-4685-8C18-6866AB31E62B},{77B2F3C7-6756-46EB-8C01-D880598FB4B3},5/22/2006 5:10:28 PM,5/22/2006 5:10:28 PM,-1073659822,0x,The variable "User::DWServerSQLInstance" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

Help would be appreciated!

I have seen other posts on this but, not able to relate the solution to my scenario.

Do you have multiple tasks (in the same package) executing in parallel, and using same variable, e.g. in property mappings?

If yes, I think this problem is fixed by this post-SP1 fixes collection:
http://support.microsoft.com/Default.aspx?id=918222

Alternatively, you may change the package so the tasks are executed sequentially by creating precedence constraints.|||

Hi Brian,
The child Package uses the User Variable: DWSQLInstanceName mentioned above to initialize 4 SQL Server connections (2 OLEDB & 2 ADO .NET) -- Only <<Server Name>> property using expressions.

I also checked that this error seems to be only on Multi-proc machine. Don't get the same on my laptop.

I cannot seralize the tasks since, the error is coming @. package level while evaluating the package expressions (i.e. for the connections) when the package starts.

What is the best way to still use expressions for connection server names but, get around this error?
Is this what is fixed in SP1?

Also, it seems that the packages does not fail when it encounters the error which I am getting. I have MaxErrorCount = 1 for Package. The package goes ahead.

|||I believe the issue with connections sharing the variable is fixed in the commulative hotfix package I've mentioned above. It should be installed on top of SP1: first, install SP1 then install all the component packages from the hotfix package in the order they are listed in the article.

http://www.microsoft.com/downloads/details.aspx?FamilyId=CB6C71EA-D649-47FF-9176-E7CAC58FD4BC
and then
http://support.microsoft.com/Default.aspx?id=918222|||This was indeed a bug and the post SP1 hotfix "fixes" it.

However, you can still create the bug. It's harder but stil there...|||

Thanks Michael and Crispin for the help on this!

I think we will need to upgrade to SP1 and install the Hotfix bundle for SP1. I think this should fix the issue which I am facing i.e. use of same variable for setting connection "Server Name" - Property in the expressions.

Thanks,
Gaurav

Error with for..next loop variable

I'm setting a variable called ConnectionString_vc in a for..next loop. The connectionstring property of one of my connections is then set to @.[User::ConnectionString_vc] via an expression. When running the package in a SQL agent job I'm getting the following error in my logs. I've received this sporadically when running it in debug mode in BIS, but it happens consistently in Agent.

The variable "User::ConnectionString_vc" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

Are you using that variable anywhere else? Perhaps in a script task?

It sounds as tho you've got the variable in the ReadOnlyvariables or ReadWriteVariables property of a script task and you're also calling LockOneForRead or LockOneForWrite within the script task.

-Jamie|||Not using the variable in a script task in this package. Control flow looks like this...

1. Execute SQL task queries list of connection strings that are stored in destination database. (Package basically loads data from multiple (identical) sources into one single destination. Just different instances of the same legacy system.) Execute SQL task maps Result set to variable called User::ADORecords.

2. Next step: For loop. Enumerator is "Foreach ADO Enumerator", ADO object source variable is "User::ADORecords", Enumeration mode is "Rows in the first table". Variable mapping is set for User::ConnectionString_vc

3. Within for loop there are about a dozen dataflow tasks that all reference a connection which has its connectionstring property equal to
@.[User::ConnectionString_vc] via an expression.

It runs successfully in debug mode or even when running without debugging from BI Studio. But when run from SQL Server agent I get the error.|||The issue doesn't seem to be related to the setting of the variable's value. It seems to be related to the Connection's connectionstring property being defined in the expression. I removed the expression so that as the For Loop ran it would set the variable but the connection would always be based off the initial default value of the connection instead of the expression. It ran through successfully.

Can anyone think of a better way to loop through a resultset of connection strings and modify the connection to the source system?

Thanks.|||That's bizarre. It sounds as though you've done it exactly as best practice dictates. Can you share the package?

This is annoying. It SHOULD work.

-Jamie|||

I'm having a similar problem. I'm using variables in expressions to set properties and also adding to the ReadOnlyList. I sometimes get that message even though I'm not doing any manual locking from the SQL agent but never get it in debug mode.

It also seems to happen when the expression is evaluated. It definately happens before any tasks even have run!

Anyways did you ever solve this?

|||

Kris, I'm doing this in a few packages as well and I experienced the problem when the variable that I passed in through the expression didn't exist. I changed over to a VariableDispenser and used the contains property in the script to do a check to make sure it existed before I did anything with it.

Brian Knight
Blog: http://www.whiteknighttechnology.com

|||

Are you using a script to build the value of the variables instead of using variables set by expressions?

Or are you using the VariableDispenser to just ensure it exists before continuing to other tasks?

|||

I wanted to have a general repository for variables (package configuration with steroids) so we had all the variables in a table. Then there's a stored procedure to retreive the variables for a group of package and inside a ForEach loop the Script task dynamically assigned the variables (so the variable name and value were dynamic from the table). I started much like you by using an expression to dynamically push the variable name into the ReadWriteVariables option in the script task based on the record in the ForEach collection. After seeing this bomb due to variables not matching (my recordset had a variable that didn't exist in the package), I switched to using the VariableDispenser. That way I could do a contains check to see if the value existed in the package and try catch it.

I'm sure you know this already, but here is how I set the variables using the VariableDispenser minus the error handling I added later:

http://whiteknighttechnology.com/cs/blogs/brian_knight/archive/2005/12/30/57.aspx

I hope I'm reading your question correctly though! Pardon if I went in a different direction than your question.

-- Brian

Error with for..next loop variable

I'm setting a variable called ConnectionString_vc in a for..next loop. The connectionstring property of one of my connections is then set to @.[User::ConnectionString_vc] via an expression. When running the package in a SQL agent job I'm getting the following error in my logs. I've received this sporadically when running it in debug mode in BIS, but it happens consistently in Agent.

The variable "User::ConnectionString_vc" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.

Are you using that variable anywhere else? Perhaps in a script task?

It sounds as tho you've got the variable in the ReadOnlyvariables or ReadWriteVariables property of a script task and you're also calling LockOneForRead or LockOneForWrite within the script task.

-Jamie|||Not using the variable in a script task in this package. Control flow looks like this...

1. Execute SQL task queries list of connection strings that are stored in destination database. (Package basically loads data from multiple (identical) sources into one single destination. Just different instances of the same legacy system.) Execute SQL task maps Result set to variable called User::ADORecords.

2. Next step: For loop. Enumerator is "Foreach ADO Enumerator", ADO object source variable is "User::ADORecords", Enumeration mode is "Rows in the first table". Variable mapping is set for User::ConnectionString_vc

3. Within for loop there are about a dozen dataflow tasks that all reference a connection which has its connectionstring property equal to
@.[User::ConnectionString_vc] via an expression.

It runs successfully in debug mode or even when running without debugging from BI Studio. But when run from SQL Server agent I get the error.

|||The issue doesn't seem to be related to the setting of the variable's value. It seems to be related to the Connection's connectionstring property being defined in the expression. I removed the expression so that as the For Loop ran it would set the variable but the connection would always be based off the initial default value of the connection instead of the expression. It ran through successfully.

Can anyone think of a better way to loop through a resultset of connection strings and modify the connection to the source system?

Thanks.

|||That's bizarre. It sounds as though you've done it exactly as best practice dictates. Can you share the package?

This is annoying. It SHOULD work.

-Jamie|||

I'm having a similar problem. I'm using variables in expressions to set properties and also adding to the ReadOnlyList. I sometimes get that message even though I'm not doing any manual locking from the SQL agent but never get it in debug mode.

It also seems to happen when the expression is evaluated. It definately happens before any tasks even have run!

Anyways did you ever solve this?

|||

Kris, I'm doing this in a few packages as well and I experienced the problem when the variable that I passed in through the expression didn't exist. I changed over to a VariableDispenser and used the contains property in the script to do a check to make sure it existed before I did anything with it.

Brian Knight
Blog: http://www.whiteknighttechnology.com

|||

Are you using a script to build the value of the variables instead of using variables set by expressions?

Or are you using the VariableDispenser to just ensure it exists before continuing to other tasks?

|||

I wanted to have a general repository for variables (package configuration with steroids) so we had all the variables in a table. Then there's a stored procedure to retreive the variables for a group of package and inside a ForEach loop the Script task dynamically assigned the variables (so the variable name and value were dynamic from the table). I started much like you by using an expression to dynamically push the variable name into the ReadWriteVariables option in the script task based on the record in the ForEach collection. After seeing this bomb due to variables not matching (my recordset had a variable that didn't exist in the package), I switched to using the VariableDispenser. That way I could do a contains check to see if the value existed in the package and try catch it.

I'm sure you know this already, but here is how I set the variables using the VariableDispenser minus the error handling I added later:

http://whiteknighttechnology.com/cs/blogs/brian_knight/archive/2005/12/30/57.aspx

I hope I'm reading your question correctly though! Pardon if I went in a different direction than your question.

-- Brian

Error with default database collation

Hello
I am using SQL Server 2000 SP4
In my stored procedure I create table variable that contains one varchar
field and join that table variable with one of the database tables. I keep
getting error "Cannot resolve collation conflict for equal to operation.".
Both fields should have same collation. Table's field due to the fact that
it's collation is set to <database default> and <database default> is
Latin1_General_CI_AS. And according to Books online table variable also has
a
collation taken from <database default> if not explicitly set to other. So
what is the problem? If table variable field explicitly assign collation
Latin1_General_CI_AS - everything works fine. Why? My server's default
collation is Cyrillic_General_CI_AS. But how that can affect? I am not
allowed to use explicit collation in my SP.
Probably it is a known bug in SQL Server... Are then any fixes for that?
Thanks in advance.Seems like SQL2K doesn't pick up the current database for the connection whe
n you create a table
variable:
CREATE DATABASE x COLLATE Cyrillic_General_CI_AS
GO
USE x
CREATE TABLE t(c1 varchar(10))
INSERT INTO t VALUES('asd')
GO
--Error on 2000 sp3, fine on 2005
DECLARE @.t table(c1 varchar(10))
INSERT INTO @.t VALUES('asd')
SELECT * FROM t INNER JOIN @.t AS t2 ON t.c1 = t2.c1
GO
--Fine on both 2000 sp3 and 2005, as expected
DECLARE @.t table(c1 varchar(10) COLLATE database_Default)
INSERT INTO @.t VALUES('asd')
SELECT * FROM t INNER JOIN @.t AS t2 ON t.c1 = t2.c1
GO
--Error as expected on both 2000 sp3 and 2005
CREATE TABLE #t (c1 varchar(10))
INSERT INTO #t VALUES('asd')
SELECT * FROM t INNER JOIN #t AS t2 ON t.c1 = t2.c1
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Alexander Korol" <AlexanderKorol@.discussions.microsoft.com> wrote in messag
e
news:C5DD7198-4122-483E-BFD6-E83AF28002F7@.microsoft.com...
> Hello
> I am using SQL Server 2000 SP4
> In my stored procedure I create table variable that contains one varchar
> field and join that table variable with one of the database tables. I keep
> getting error "Cannot resolve collation conflict for equal to operation.".
> Both fields should have same collation. Table's field due to the fact that
> it's collation is set to <database default> and <database default> is
> Latin1_General_CI_AS. And according to Books online table variable also ha
s a
> collation taken from <database default> if not explicitly set to other. So
> what is the problem? If table variable field explicitly assign collation
> Latin1_General_CI_AS - everything works fine. Why? My server's default
> collation is Cyrillic_General_CI_AS. But how that can affect? I am not
> allowed to use explicit collation in my SP.
> Probably it is a known bug in SQL Server... Are then any fixes for that?
> Thanks in advance.

error with calling stored proc with exec

Ok, I previously had a thread about "Column name as variable".
Here's a proc I wrote, and it compiles ok:
alter PROCEDURE rptRRTP_By_Date_Range
-- Add the parameters for the stored procedure here
@.Update_Field as sysname,
@.start_date as datetime,
@.end_date as datetime,
@.contr_Stat as varchar(2),
@.seq as int
AS
declare @.sql_stat as varchar(255)
BEGIN
set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' =
(select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
',' + @.end_date + ')
where contract_status = ' + @.contr_stat + ' group by sequence_no)
where sequence = ' + @.seq
Execute (@.sql_stat)
END
GO
And when I try to run it:
exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
'00',1
I get this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Looks like it doesn't like that getdate() call.
Any feedback on this?You can't pass a function in as a parameter. Try:
DECLARE @.dt DATETIME;
SET @.dt = GETDATE();
EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178047866.417792.32680@.h2g2000hsg.googlegroups.com...
> Ok, I previously had a thread about "Column name as variable".
> Here's a proc I wrote, and it compiles ok:
>
> alter PROCEDURE rptRRTP_By_Date_Range
> -- Add the parameters for the stored procedure here
> @.Update_Field as sysname,
> @.start_date as datetime,
> @.end_date as datetime,
> @.contr_Stat as varchar(2),
> @.seq as int
> AS
> declare @.sql_stat as varchar(255)
> BEGIN
> set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' =
> (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
> ',' + @.end_date + ')
> where contract_status = ' + @.contr_stat + ' group by sequence_no)
> where sequence = ' + @.seq
> Execute (@.sql_stat)
> END
> GO
>
> And when I try to run it:
>
> exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
> '00',1
>
> I get this error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near ')'.
> Looks like it doesn't like that getdate() call.
> Any feedback on this?
>|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
>
Great. Many thanks.|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
Ok, did that, but see error below.
[vbcol=seagreen]
> --
> Aaron Bertrand
> SQL Server MVPhttp://www.sqlblog.com/http://www.aspfaq.com/5006
> "d.s." <nodamnspa...@.yahoo.com> wrote in message
> news:1178047866.417792.32680@.h2g2000hsg.googlegroups.com...
>
>
>
>
>
>
>
>
Hmmm...I'm getting this error:
Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
Conversion failed when converting datetime from character string.
which I'm assuming is for the line of code immediately above,
specifically the @.end_date part.
[vbcol=seagreen]
>
>|||Hi d.s.
Concatenation requires string values (char, varchar, etc.). You get the
error when concatenating your datetime parameter into the table name string.
You could try declaring your parameters as type varchar, but you have to be
aware that the actual string that results will depend on your regional
settings for displaying dates. If you already have these tables created,
expecting particular date formats for the table names, you'll need to be
really careful about how your dates are converted. Look up the CONVERT
function to see all the possibilities.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178049900.305992.123560@.h2g2000hsg.googlegroups.com...
> On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
>
> Ok, did that, but see error below.
>
>
>
> Hmmm...I'm getting this error:
> Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
> Conversion failed when converting datetime from character string.
> which I'm assuming is for the line of code immediately above,
> specifically the @.end_date part.
>
>
>
>|||On May 1, 1:30 pm, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Hi d.s.
> Concatenation requires string values (char, varchar, etc.). You get the
> error when concatenating your datetime parameter into the table name strin
g.
> You could try declaring your parameters as type varchar, but you have to b
e
> aware that the actual string that results will depend on your regional
> settings for displaying dates. If you already have these tables created,
> expecting particular date formats for the table names, you'll need to be
> really careful about how your dates are converted. Look up the CONVERT
> function to see all the possibilities.
>
This is actually what I ended up doing, converting to varchar before
sending it on down the line. Thanks for your feedback.

error with calling stored proc with exec

Ok, I previously had a thread about "Column name as variable".
Here's a proc I wrote, and it compiles ok:
alter PROCEDURE rptRRTP_By_Date_Range
-- Add the parameters for the stored procedure here
@.Update_Field as sysname,
@.start_date as datetime,
@.end_date as datetime,
@.contr_Stat as varchar(2),
@.seq as int
AS
declare @.sql_stat as varchar(255)
BEGIN
set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' = (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
',' + @.end_date + ')
where contract_status = ' + @.contr_stat + ' group by sequence_no)
where sequence = ' + @.seq
Execute (@.sql_stat)
END
GO
And when I try to run it:
exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
'00',1
I get this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Looks like it doesn't like that getdate() call.
Any feedback on this?You can't pass a function in as a parameter. Try:
DECLARE @.dt DATETIME;
SET @.dt = GETDATE();
EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178047866.417792.32680@.h2g2000hsg.googlegroups.com...
> Ok, I previously had a thread about "Column name as variable".
> Here's a proc I wrote, and it compiles ok:
>
> alter PROCEDURE rptRRTP_By_Date_Range
> -- Add the parameters for the stored procedure here
> @.Update_Field as sysname,
> @.start_date as datetime,
> @.end_date as datetime,
> @.contr_Stat as varchar(2),
> @.seq as int
> AS
> declare @.sql_stat as varchar(255)
> BEGIN
> set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' => (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
> ',' + @.end_date + ')
> where contract_status = ' + @.contr_stat + ' group by sequence_no)
> where sequence = ' + @.seq
> Execute (@.sql_stat)
> END
> GO
>
> And when I try to run it:
>
> exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
> '00',1
>
> I get this error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near ')'.
> Looks like it doesn't like that getdate() call.
> Any feedback on this?
>|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
>
Great. Many thanks.|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
Ok, did that, but see error below.
> --
> Aaron Bertrand
> SQL Server MVPhttp://www.sqlblog.com/http://www.aspfaq.com/5006
> "d.s." <nodamnspa...@.yahoo.com> wrote in message
> news:1178047866.417792.32680@.h2g2000hsg.googlegroups.com...
>
> > Ok, I previously had a thread about "Column name as variable".
> > Here's a proc I wrote, and it compiles ok:
> > alter PROCEDURE rptRRTP_By_Date_Range
> > -- Add the parameters for the stored procedure here
> > @.Update_Field as sysname,
> > @.start_date as datetime,
> > @.end_date as datetime,
> > @.contr_Stat as varchar(2),
> > @.seq as int
> > AS
> > declare @.sql_stat as varchar(255)
> > BEGIN
> > set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' => > (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
> > ',' + @.end_date + ')
Hmmm...I'm getting this error:
Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
Conversion failed when converting datetime from character string.
which I'm assuming is for the line of code immediately above,
specifically the @.end_date part.
> > where contract_status = ' + @.contr_stat + ' group by sequence_no)
> > where sequence = ' + @.seq
> > Execute (@.sql_stat)
> > END
> > GO|||Hi d.s.
Concatenation requires string values (char, varchar, etc.). You get the
error when concatenating your datetime parameter into the table name string.
You could try declaring your parameters as type varchar, but you have to be
aware that the actual string that results will depend on your regional
settings for displaying dates. If you already have these tables created,
expecting particular date formats for the table names, you'll need to be
really careful about how your dates are converted. Look up the CONVERT
function to see all the possibilities.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178049900.305992.123560@.h2g2000hsg.googlegroups.com...
> On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
>> You can't pass a function in as a parameter. Try:
>> DECLARE @.dt DATETIME;
>> SET @.dt = GETDATE();
>> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
>
> Ok, did that, but see error below.
>
>
>> --
>> Aaron Bertrand
>> SQL Server MVPhttp://www.sqlblog.com/http://www.aspfaq.com/5006
>> "d.s." <nodamnspa...@.yahoo.com> wrote in message
>> news:1178047866.417792.32680@.h2g2000hsg.googlegroups.com...
>>
>> > Ok, I previously had a thread about "Column name as variable".
>> > Here's a proc I wrote, and it compiles ok:
>> > alter PROCEDURE rptRRTP_By_Date_Range
>> > -- Add the parameters for the stored procedure here
>> > @.Update_Field as sysname,
>> > @.start_date as datetime,
>> > @.end_date as datetime,
>> > @.contr_Stat as varchar(2),
>> > @.seq as int
>> > AS
>> > declare @.sql_stat as varchar(255)
>> > BEGIN
>> > set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' =>> > (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
>> > ',' + @.end_date + ')
>
> Hmmm...I'm getting this error:
> Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
> Conversion failed when converting datetime from character string.
> which I'm assuming is for the line of code immediately above,
> specifically the @.end_date part.
>
>
>> > where contract_status = ' + @.contr_stat + ' group by sequence_no)
>> > where sequence = ' + @.seq
>> > Execute (@.sql_stat)
>> > END
>> > GO
>|||On May 1, 1:30 pm, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Hi d.s.
> Concatenation requires string values (char, varchar, etc.). You get the
> error when concatenating your datetime parameter into the table name string.
> You could try declaring your parameters as type varchar, but you have to be
> aware that the actual string that results will depend on your regional
> settings for displaying dates. If you already have these tables created,
> expecting particular date formats for the table names, you'll need to be
> really careful about how your dates are converted. Look up the CONVERT
> function to see all the possibilities.
>
This is actually what I ended up doing, converting to varchar before
sending it on down the line. Thanks for your feedback.

error with calling stored proc with exec

Ok, I previously had a thread about "Column name as variable".
Here's a proc I wrote, and it compiles ok:
alter PROCEDURE rptRRTP_By_Date_Range
-- Add the parameters for the stored procedure here
@.Update_Field as sysname,
@.start_date as datetime,
@.end_date as datetime,
@.contr_Stat as varchar(2),
@.seq as int
AS
declare @.sql_stat as varchar(255)
BEGIN
set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' =
(select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
',' + @.end_date + ')
where contract_status = ' + @.contr_stat + ' group by sequence_no)
where sequence = ' + @.seq
Execute (@.sql_stat)
END
GO
And when I try to run it:
exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
'00',1
I get this error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Looks like it doesn't like that getdate() call.
Any feedback on this?
You can't pass a function in as a parameter. Try:
DECLARE @.dt DATETIME;
SET @.dt = GETDATE();
EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178047866.417792.32680@.h2g2000hsg.googlegrou ps.com...
> Ok, I previously had a thread about "Column name as variable".
> Here's a proc I wrote, and it compiles ok:
>
> alter PROCEDURE rptRRTP_By_Date_Range
> -- Add the parameters for the stored procedure here
> @.Update_Field as sysname,
> @.start_date as datetime,
> @.end_date as datetime,
> @.contr_Stat as varchar(2),
> @.seq as int
> AS
> declare @.sql_stat as varchar(255)
> BEGIN
> set @.sql_stat = 'update RRTP_Scorecard set ' + @.update_field + ' =
> (select count(sequence_no) from fnStatusPerDatesTbl(' + @.start_date +
> ',' + @.end_date + ')
> where contract_status = ' + @.contr_stat + ' group by sequence_no)
> where sequence = ' + @.seq
> Execute (@.sql_stat)
> END
> GO
>
> And when I try to run it:
>
> exec rptRRTP_By_Date_Range 'current_status', '1/1/2000', getdate(),
> '00',1
>
> I get this error:
> Msg 102, Level 15, State 1, Line 1
> Incorrect syntax near ')'.
> Looks like it doesn't like that getdate() call.
> Any feedback on this?
>
|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
>
Great. Many thanks.
|||On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
<ten...@.dnartreb.noraa> wrote:
> You can't pass a function in as a parameter. Try:
> DECLARE @.dt DATETIME;
> SET @.dt = GETDATE();
> EXEC rptRRTP_By_Date_Range 'current_status', '1/1/2000', @.dt, '00',1;
Ok, did that, but see error below.
[vbcol=seagreen]
> --
> Aaron Bertrand
> SQL Server MVPhttp://www.sqlblog.com/http://www.aspfaq.com/5006
> "d.s." <nodamnspa...@.yahoo.com> wrote in message
> news:1178047866.417792.32680@.h2g2000hsg.googlegrou ps.com...
>
>
>
>
Hmmm...I'm getting this error:
Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
Conversion failed when converting datetime from character string.
which I'm assuming is for the line of code immediately above,
specifically the @.end_date part.
[vbcol=seagreen]
>
|||Hi d.s.
Concatenation requires string values (char, varchar, etc.). You get the
error when concatenating your datetime parameter into the table name string.
You could try declaring your parameters as type varchar, but you have to be
aware that the actual string that results will depend on your regional
settings for displaying dates. If you already have these tables created,
expecting particular date formats for the table names, you'll need to be
really careful about how your dates are converted. Look up the CONVERT
function to see all the possibilities.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"d.s." <nodamnspamok@.yahoo.com> wrote in message
news:1178049900.305992.123560@.h2g2000hsg.googlegro ups.com...
> On May 1, 12:35 pm, "Aaron Bertrand [SQL Server MVP]"
> <ten...@.dnartreb.noraa> wrote:
>
> Ok, did that, but see error below.
>
>
>
> Hmmm...I'm getting this error:
> Msg 241, Level 16, State 1, Procedure rptRRTP_By_Date_Range, Line 20
> Conversion failed when converting datetime from character string.
> which I'm assuming is for the line of code immediately above,
> specifically the @.end_date part.
>
>
>
|||On May 1, 1:30 pm, "Kalen Delaney" <replies@.public_newsgroups.com>
wrote:
> Hi d.s.
> Concatenation requires string values (char, varchar, etc.). You get the
> error when concatenating your datetime parameter into the table name string.
> You could try declaring your parameters as type varchar, but you have to be
> aware that the actual string that results will depend on your regional
> settings for displaying dates. If you already have these tables created,
> expecting particular date formats for the table names, you'll need to be
> really careful about how your dates are converted. Look up the CONVERT
> function to see all the possibilities.
>
This is actually what I ended up doing, converting to varchar before
sending it on down the line. Thanks for your feedback.