dynamic sql que

[habesha]

I want my dynamic sql to return one result set on a variable

declare @string varchar(400), @result varchar(400), @columnName varchar(400)

set @string = 'select ' + @result + '= ' + @columnName + 'from ' + @tableName

exec(@string)

select @result

This query is not working, any suggestion?

 

[SQLDenis]

Deja-vu,

I answered something very similar yesterday
thread183-1318473


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[habesha]

Thank you,

here is onother question,
I want to transfer data into a table tmpTable from a table tmpTable2 which is found on another server

How can I do that
The database of tmpTable2 is db1
server is server1
owner of the table is dbo

Thanks

 

[Catadmin]

Write a normal Insert statement, but use the Server.Database.Owner.Table naming convention every time Table2 is mentioned in the query.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLDenis]

you have to set up a linked server and use 4 part notation

or use OPENROWSET

lookup OPENROWSET and Linked Server in BOL

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[habesha]

The server name is server1\serv
when I try to run this query

select * from server1\serv.databasename.dbo.tablename

it is complaining saying that

Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '\'.

what should I do

 

[Catadmin]

Put [] around the ServerName\InstanceName part of your code.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLDenis]

dot not slash

select * from server1.databasename.dbo.tablename


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[habesha]

That is the way it is, I can't omit the slash

the server name id server1\serv
Thanks

 

[SQLDenis]

use [server1\serv] instead of server1\serv



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[habesha]

Thank you

I am facing this error

Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

thanks

 

[Catadmin]

Is your login (or the login running the code) given permissions to access the database that holds table2? How big or granular are those permissions?

Have you added the linked server connection yet? (Sp_LinkedServers)



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[habesha]

Yes I have sa permission on both servers and they are also linked servers

thanks

 

[Catadmin]

Run the sp_UpdateUserLogins on both DBs for that login. Maybe something is broken.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[habesha]

Is this undocumented proc 'sp_UpdateUserLogins'

I couldn't find any where.
How can I use it

Thanks

 

[Catadmin]

Sorry, I keep getting the name of that proc wrong. It's sp_change_users_login.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"