sp_execute returns error

Question

sp_execute returns error

jsciii

SSCommitted

Points: 1536
More actions
October 17, 2008 at 8:12 am

#199656

With no more hair to pull, I ask the experts. What is wrong with my sp_excute statement that throws an error? The embedded SQL statement runs fine on the local box. the openquery runs fine if I change the variable to a constant. For some reason the sp_execute statement is not passing the variables. Any suggestions would be greatly appreciated.
DECLARE @passval datetime, @sql nvarchar(1000)
SET @passval = (SELECT dateadd(day, -1, getdate()))
select @sql = N'declare cursearch cursor for select * from openquery(dbssqts1,
''Select convert(varchar(100), name) as accname,
convert(datetime, loginproperty(name, ''''PasswordLastSetTime'''')) as passchgdate,
cast(getdate()-convert(datetime, loginproperty(name, ''''PasswordLastSetTime'''')) as int) as ctdays, is_disabled as accdisabled, is_expiration_checked as chkexpiration
from sys.sql_logins
where (convert(datetime, loginproperty(name, ''''PasswordLastSetTime'''')) + 35 < @dateval and is_expiration_checked = 1)
or (convert(datetime, loginproperty(name, ''''PasswordLastSetTime'''')) + 335 < @dateval and is_expiration_checked = 0) or is_disabled = 1
order by is_disabled desc, is_expiration_checked, accname'')'
execute sp_executesql @sql, N'@dateval datetime', @dateval = @passval
The error:
OLE DB provider "SQLNCLI" for linked server "dbssqts1" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 137, Level 15, State 2, Line 6
Must declare the scalar variable "@dateval".

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Run "EXEC sp_helpservers" and show us the results.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Garadin One Orange Chip Points: 29613 More actions · Answer 2

You are trying to execute a dynamic sql statement with the variable actually in the statement. Usually, when you use dynamic sql you'd do something like

SET @sql = 'Select * from table where id = ' + @MyID

Exec(@SQL)

You are doing

SET @sql = 'Select * from table where id = @MyID'

Exec(@SQL)

I believe this would require you to declare the variable within your dynamic sql statement, as it runs as its own process. Which coincides with your error about the variable being undeclared.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Try this...

[font="Courier New"]DECLARE @passval DATETIME, @sql NVARCHAR(1000)

SET @passval = (SELECT DATEADD(DAY, -1, GETDATE()))

SELECT @sql = N'declare cursearch cursor for select * from openquery(dbssqts1,

''Select convert(varchar(100), name) as accname,

convert(datetime, loginproperty(name, ''''PasswordLastSetTime'''')) as passchgdate,

cast(getdate()-convert(datetime, loginproperty(name, ''''PasswordLastSetTime'''')) as int) as ctdays, is_disabled as accdisabled, is_expiration_checked as chkexpiration

from sys.sql_logins

where (convert(datetime, loginproperty(name, ''''PasswordLastSetTime'''')) + 35 < ''''' + @dateval + ''''' and is_expiration_checked = 1)

or (convert(datetime, loginproperty(name, ''''PasswordLastSetTime'''')) + 335 < ''''' + @dateval + ''''' and is_expiration_checked = 0) or is_disabled = 1

order by is_disabled desc, is_expiration_checked, accname'')'

EXECUTE sp_executesql @sql, N'@dateval datetime', @dateval = @passval

[/font]

OPENQUERY doesn't accept variables in its arguments. The variables are evaluated in the mod shown.

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jsciii SSCommitted Points: 1536 More actions · Answer 4

jsciii

SSCommitted

Points: 1536

October 17, 2008 at 8:49 am

#886413

Thanks Chris, you nailed it.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Cool, many thanks for the feedback.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden