August 26, 2009 at 10:26 am
Hi all,
I have a simple query:
SELECT @myVar = someValue
FROM [server].[database].schema.table
WHERE someName = @myName.
This runs perfectly well in my SP.
I need to make it dynamic:
DECLARE @SQLstr nvarchar(800),
@myVar int,
@server varchar(20),
@database varchar(20)
SET@SQLstr = 'SELECT @myVarOUT = someValue FROM ['
+ @server + '].[' + @database + '].[schema].
WHERE someName = ' + quotename(@myName);
EXEC sp_executesql @SQLstr, N'@myVarOUT int OUTPUT', @myVarOUT = @myVar OUTPUT;
This gives me an error:
The OLE DB provider "SQLNCLI" for linked server "server" does not contain the table ""database"."schema"."table"". ...
desperate for help :crying:
Thanks
August 26, 2009 at 10:46 am
Please, let us see the value you are building:
-- add this statement and rerun your code, then paste the printed results back here.
PRINT @SQLstr
EXEC sp_executesql @SQLstr, N'@myVarOUT int OUTPUT', @myVarOUT = @myVar OUTPUT;
Always try to give data and results with your code. It makes it easier for people to spot your problem and gets you a correct answer quicker. For examples, read this excellent article [/url] by Jeff Moden.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 26, 2009 at 10:50 am
In the meanwhile, here's a hint:
You are putting '[schema].
' in as a constant, instead of using two variables to supply the schema name and the name of the table.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 26, 2009 at 1:27 pm
thanks for taking a look at it 🙂
my problem is just that....
select @SQLstr
produces almost exact line:
SELECT @myVarOUT =someValue FROM [server].[database].[dbo].
WHERE someName = [@myName]
just @myName is [bracketed] insted of 'single quoted ' for whatever reason... but I don't think that is the problem... since I've done it both ways already.
the reason for schema and table not being variales is because in my case they will not be variables (each database have exactly same schemas and tables) ...
it is just that all of this is needed for easily switching from dev environment to production ....
Thanks.
August 26, 2009 at 3:18 pm
my problem is just that....
select @SQLstr
produces almost exact line:
SELECT @myVarOUT =someValue FROM [server].[database].[dbo].
WHERE someName = [@myName]
It does not. You did not do as I requested. You just typed in something, and that really makes me angry.
I just borrowed your code and made a slight modification, since you don't seem to understand what I asked to see, or can't be bothered to do it.
DECLARE @SQLstr nvarchar(800),
@myVar int,
@server varchar(20),
@database varchar(20),
@myName varchar(20)
set @server = 'MyLinkServer'
set @database = 'MyDB'
set @myName = 'nroudak'
SET @SQLstr = 'SELECT @myVarOUT = someValue FROM ['
+ @server + '].[' + @database + '].[schema].
WHERE someName = ' + quotename(@myName);
Print @SQLstr
The above code produces the following statement. What do you see that is wrong with it?
SELECT @myVarOUT = someValue FROM [MyLinkServer].[MyDB].[schema].
WHERE someName = [nroudak]
Between my rather blatant "hint" and the line above there is more than enough information for you to solve your problem. Good luck.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 26, 2009 at 3:46 pm
:unsure:
As I mentioned already, I see [brackets] instead of 'single quotes' in WHERE clause ... but I did try already:
DECLARE @SQLstr nvarchar(800),
@myVar int,
@server varchar(20),
@database varchar(20),
@myName varchar(20)
set @server = 'MyLinkServer'
set @database = 'MyDB'
set @myName = 'nroudak'
SET @SQLstr = 'SELECT @myVarOUT = someValue FROM ['
+ @server + '].[' + @database + '].[schema].
WHERE someName = ' + '''@myName''';
Print @SQLstr
which resalted in:
SELECT @myVarOUT = someValue FROM [MyLinkServer].[MyDB].[schema].
WHERE someName = '@myName'
but it produces same error :ermm:
by the way , thanks, I just finally notice how to insert the code 🙂
August 26, 2009 at 3:52 pm
[schema].
You have a table named this in every db on every server?
You have a table named this anywhere?
What you typed in was:
[server].[database].[dbo].
That wouldn't work either, but please tell me just how you think the table name you want is going to replace '[schema].
' or '[dbo].
'. Please think about it a minute for answering. The error you are getting has nothing to do with the WHERE clause.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 26, 2009 at 4:24 pm
yes, Sir ...
I have same schema, lets say [dbo], in each of the 25 databases that reside on 5 different servers...
The table name is identical too ... as well as table structure...
It was silly/stupid/... mistype ...
My sincere apologies for taking up you time ... at the same time your questioning prompted me finding that typo, which is greatly appreciated .. Thank you very much
August 26, 2009 at 4:28 pm
truly sorry for making you angry ... I just couldn't paste real servers and db names (sensitive), besides it wouldn't help anyway ..
August 26, 2009 at 4:33 pm
You're welcome.
If I may make a suggestion for the future: Whenever I am working with dynamic SQL, I never begin with trying to execute the string I've just generated. First I use PRINT to display it, then cut and paste it exactly into an SSMS session. I run it there, and debug it there. Then I go back to the code that generates the dynamic SQL string, and make corrections. Only after generating a string which can cut, paste, and then run without errors should you move forward to executing the string with sp_executeSQL, adding parameters, etc.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 26, 2009 at 4:51 pm
nroudak (8/26/2009)
truly sorry for making you angry ... I just couldn't paste real servers and db names (sensitive), besides it wouldn't help anyway ..
I'm no longer angry, but that's an unacceptable excuse.
Problems with the linkedserver name, database name, schema name, or table name are the only things that would trigger your error message. Even if your code were perfect, and you fed it an incorrect linked server name, it would fail. This is why I wanted you to post the string so we could discuss it.
If you have to ask for help, you should not attempt to decide what will and won't help.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply