SQL Server Performance Forum – Threads Archive

3 basic T-SQL questions

Hi out there. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> As I am trying to get a bit more into T-SQL I’m coming across some questions I was not able to answer reading the BOL or the mighty Google hit list. As these questions are not performance related I hesitated posting them here, because I do not want to start off topic threads. If you don’t want this here, please close this thread. Thanks. [:I] Otherwise I’d appreciate hints that help me not to bite in my keyboard anymore. <ul> <li>Can I "connect to a server"? I mean, is there anything like <pre id="code">connect to MSSQLSERVER001 use MyDatabase …</pre id="code">? </li> <li>Why will this code not work? <pre id="code">DECLARE @DBName sysname SET @DBName = ‘MyDB' use @DBName</pre id="code"> It returns "Incorrect syntax near ‘@DBName’." I also tried to exec(‘use ‘ + @DBName) unsuccessfully. </li> <li>I’ve read often here that one shouldn’t query the system tables directly, as they might change in future releases. Trying to avoid this I am facing the problem to extract a single field of a single row of a sp’s result set, eg: put the "LogicalName" of the dat file in a variable: <pre id="code">RESTORE FILELISTONLY FROM DISK = @BackupFile</pre id="code"> My tries like <pre id="code">select @BkpLogicalDat = (select LogicalName from (RESTORE FILELISTONLY FROM DISK = @BackupFile) where Type = ‘D’ and FileGroupName = ‘PRIMARY’)</pre id="code"> return "Incorrect syntax near the keyword ‘RESTORE’." and "Incorrect syntax near ‘)’." Similar with "exec sp_helpdb [SD-c]" which even gives me two tables as a result, both have "name" as a column name. </li> </ul> Background: We are a group of around 25 people who’s job is to maintain a large heterogeneous (Windows/Unix, SQL-Server, DB/2, Oracle, WTS, Notes Domino, IBM Websphere and so on) site. Everyone has some "own" responsible topics and normally does not know much about the other ones. Hence, I want to make some simple scripts that allow anyone in my group to e.g. restore a database without having to know the system. This way the common requests of our customers can be answered quickly, and the more scripts I can provide the less they will call me at home. Thanks in advance for your help. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

Question 1: A connection is established with a specific SQL Server. If you create Linked Servers under Management/Security you can have commands that utilize the servers name: ServerName.DatabaseName.OwnerName.TableName etc. There are also ways to execute procedures on other servers, but to my knowledge there is no specific way to say "move my connection to another server and allow me to directly reference whatever I want on it." Question 2: You can’t issue the command in the manner that you’ve described, but you can do the following:
Exec (‘use ‘ + @DBName) The Exec command allows you to execute dynamic sql commands that you construct on the fly. Question 3: You may be able to execute the command you are trying inside the EXEC command. Generally you can capture results from stored procedures that return multiple rows by doing the following:
declare @tblResultsIWant (field datatype, field datatype) — Where the field and datatype’s define what will be returned insert into @tblResultsIWant
EXEC (‘spWhateverYouWant’) The stored procedure would be executed and the results would be pumped into the table variable that you created, which you can then do a select against into whatever variables you want
select @BackupFile = SomeField from @tblResultsIWant where SomeField = SomeCondition
PS – I’m a bit under the weather or I’d do some reasearch to guess what proc you are likely trying to run. Hope this helps you get started.

To add to the confusion: if you use USE in a script that you run with EXEC, it only has effect within that bit of script you’re executing with this EXEC – so after this EXEC is done, the rest of the script runs in the database environment where you started. A more structured way of returning values is to use dbo.sp_ExecuteSQL DECLARE @MyReturnInt INT
EXEC dbo.sp_ExecuteSQL N’SET @MyProcInt = 100′, N’@MyProcInt INT OUTPUT’, @MyReturnInt OUTPUT
SELECT @MyReturnInt

Hence, I want to make some simple scripts that allow anyone in my group to e.g. restore a database without having to know the system. This way the common requests of our customers can be answered quickly, and the more scripts I can provide the less they will call me at home.

Ensure the security is not compromised in this case, as too many SA privileges may lead to a disaster.
If you want to automate few tasks then using TSQL scripts is good enough and be specific about your tasks. Take time to read books online for more information on the topics you’re in doubt and this forum is well ahead of time to provide any information you may need. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

2 Why cant you use DBName.db :confused:

ject? Exec(‘Select …… ‘[email protected]+’.dbo.object’) Madhivanan Failing to plan is Planning to fail

Nobody will close such a thread here! Not everything is about performance. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] I think for question 2) + 3) you can find an explanation here<a target="_blank" href=http://www.insidesql.de/content/view/164/3/>http://www.insidesql.de/content/view/164/3/</a> That’s my translation of probably the most frequently referenced article about dynamic SQL on the web. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

]]>