Scripting in Microsoft SQL Server 2005 Web Cast Resources

This article contains extra information with regard to the web cast "Scripting in Microsoft SQL Server 2005 " that was held on 6/22/2005 9:30-11:00 PST.

Q&A Log

Question: Is sql server management studio part of standard edition ?
Answer: Yes. Every edition except for Express

Question: Is there an option to show the template scripts without the big, ugly graphics (i.e., in plain old Courier New)?
Answer: Yes. The presenter is using the large script just for the LiveMeeting. Any font can be used, as it was in Query Analyzer (2000).

Question: Management Studio (SSMS) replaces BOTH Enterprise Manager and Query Analyzer in all editions other than Express. For SQL Express there's a more lightweight tool called SQL Express Manager (XM). XM is ALREADY available for download on microsoft.com/sql and works to connect against any version of SQL Server 2000 and higher.
Answer: this was an answerr...sorry for the incorrect post.

Question: Can we migrate our existing ISQL and OSQL directly into SQLCMD -- any syntax changes? Perhaps this will be covered...
Answer: No syntax changes and even the default environment settings are the same as OSQL (not isql)

Question: Can you explain what file is needed and how to register it for SQL Native Client prerequisite?
Answer: SQL Native Client is installed when you install SQL Server 2005. As for what type of file is needed - it's nothing more than a Transact-SQL script with some additional new commands!

Question: Is Sql Server 2005 June CTP compatible to Visual Studio 2005 beta 2, if this is compatible is it ok to install sql server 2005 CTP first before installing visual studio beta 2
Answer: Yes and it worked for me. The build numbers of the .NET frame look to be compatible.

Question: You should always install SQL Server before Visual Studio. BOTH the April CTP and the June CTP of SQL Server are compatible with VS Beta 2. You should always install SQL Server before VS
Answer: .

Question: Does SQLCMD run on a client or does it have to run on the Server?
Answer: Either. It is just a client process that connects to a server.

Question: Is Visual Studio going to be required to write DTS packages in SQL 2005?
Answer: No, if you install SQL Server 2005, you will be able to design SQL Server Integration Service packages with the Business Intelligence Workbench tool.

Question: Does SQLCMD also allow you log all your SQL statements to a file like in Oracle TSSQL?
Answer: yes, there are multiple ways to do this as well. Michiel will cover this I think but you can have an output file or your can redirect - even to different outputs - in a single script.

Question: do we need to be in a specific folder to execute sqlcmd?
Answer: No, it will work in any folder.

Question: [folder question] Installing SQL Server should put the location in your path. The default location of sqlcmd.exe is C:\Program Files\Microsoft SQL Server\90\Tools\Binn
Answer: .

Question: can you change the editor for :ed
Answer: Yes, with the SQLCMDEDITOR environment variable

Question: where do the scripts get saved to (directory, etc)
Answer: Wherever you like,the idea is that you the path where SQLCMD lives in your OS PATH settings, then you can execute them from anywhere, as long as you are logged in as user than can connect to the database (when using integrated security) or by specifying a user name and password on the commandline

Question: Can you use SQLCMD with SQL2K? (and thanks to Michiel for the useful :ed tip)
Answer: yes

Question: Will there be a Unix or Java version of sqlcmd?
Answer: Not aware of any plan to port this unix/java, at least on MS's part.

Question: Michiel mentioned how SQLCMD commands are executed immediately. That can be risky, obviously. Is there an undo?
Private Answer: BEGIN TRANSACTION is your friend. :)

Question: I like to use the [-E] (trusted connection) command line argument with ISQL; does SQLCMD support this?
Answer: Yes. It is also the default connection type if no loginname (-U) and password (-P) are set.

Question: To elaborate further, will Microsoft have a SQL Client that runs on Unix ( Or a ODBC driver ) for environments that have a mix of Unix, Mac and PC workstations?
Answer: There's a a couple of solutions there. First is to use a support ODBC provider. Second. SQL Server supports the mapping of procedures and functions to SOAP (http/s) endpoints that detour around that issue.

Question: Can you get at (create/edit/read) registry values in the same way as ENVARs ?
Answer: there is no direct way of accessing registry values from Transact-SQL or SQLCMD but you can access environment variables. I haven't tried this but you should be able to create an environment variable which accesses a registry key and then use that. Can you elaborate more on what you'd like to access?

Question: Hi, I came a little bit late, how do we launch sqlcmd on the command line thanslk
Answer: Type SQLCMD

Question: Ok, so what was technet> on the dos screen
Answer: It was just the directory part of the command prompt. c:\directoryname> His just happened to be technet> without the drive

Question: Can call SQLCMD program from within VBScript, and how to pass value in?
Answer: You should be able to, just like executing any other command line program such as "DIR." Just make sure that whatever you and sending to the command shell is correct. :)

Question: What file we have to check if we have June release of SQL Server 2005 or if we have earlier version?
Private Answer: You can execute "select @@version" and if you see the number "9.0.1187," you've got the june CTP installed on the target server.

Question: [ELABORATION AS REQUESTED] Our app uses the registry to store a bunch of information; we don't use env variables. I has heard a rumor that SQLCMD would allow direct reg interaction in order to grab variable values from the registry. That was the root of my question; thanks..
Answer: (I have not heard this rumor.)

Question: How can you access an environment variable? Thanks
Answer: By using code like $(environment_variable) in the SQLCMD script.

Question: How to get query result from SQLCMD and pass it into VBScript, and, call a VBScript within SQLCMD and pass value to SQLCMD variable?
Answer: Well, you could use -e to have the output go to a file, then use the FileSystemObject to read and parse that file. You might find, however, that using ADO (Active Data Objects) would be easier.

Question: what is use of code page?
Answer: A code page is an encoding of characters beyond ASCII 127 to handle different alphabets. A sequence of bytes may not represent the same characters in different code pages.

Question: what was the code page that allowed to dispaly the c correctly
Answer: Code page 1252 (multilingual latin characters) will work (not sure which was shown)

Question: Will we be able to save the Q&A queue after the presentation?
Private Answer: They typically get posted online in a few days, too.

Question: What option we have to increase from 300 Milli seconds?
Answer: Can you clarify? 300 ms option for what?

Question: This may have been covered in the intro: Will the presentation slideset be made available to attendees? It would be a helpful reference.
Answer: You can go up to the File menu right now and choose Print to PDF, which will allow you to save the slides in PDF format. you'll also be sent an email with links to download the Powerpoint version of the slides in about 24 hours.

Question: Is it likely DBAs will use SQLCMD to do anything related to XML?
Answer: Interesting question. I can see some use cases, like querying an existing instance of XML in a DB to file, for example

Question: are the output control Michiel just showed, -Y and -W available on Management Console?
Answer: do you mean SQL Server Management Studio? And if so - the answer is yes BUT through the comparable SQLCMD variables SQLCMDMAXVARTYPEWIDTH and SQLCMDMAXFIXEDTYPEWIDTH

Question: can I just remove the hyphens under the headers? That would allow me to create a csv file that would import into Excel or Access, for example, with column headers.
Answer: You can use the option -h-1 when you invoke SQLCMD. However, you may find SSIS (Sql Server Integration Services) or SSMS (Management Studio, which has improved cut-and-paste from grid) better options.

Question: How to store image in a table and compress the column to save space?
Answer: Storing an image is possible with the new varbinary(max) type and it wouldn't be hard to write a SQLCLR function that compressed a byte stream, but note that compression on most modern image types is somewhat redundant since they are compressed by design

Question: Can you (or any of the attendees or Michiel) think of any "unusual" scenarios where one might use SQLCMD - perhaps in conjunction with other SQL-related services like SSRS or FTS?
Answer: One case I can think of is having a batch job that parses data for later transformation by a Unix system or if you already have a flat-file adapter into some integration process.

Question: SQLCMDMAXVARTYPEWIDTH and SQLCMDMAXFIXEDTYPEWIDTH seem to trim at a specified MAX value, how about the trim spaces he showed? or the output with comma delimitted?
Answer: -W is to remove trailing spaces, -y is MAXVARTYPE width and -Y is MAXFIXEDTYPE width. Will look to see if there is (and what it is) the -W is with a SQLCMD variable.

Question: is there an unlimited value on timeout?
Answer: Yes, specify 0 for the timeout

Question: Doesn't look like there's a comparable -W with SQLCMD variables in SSMS
Answer: .

Question: What was Michiel saying about the time-savings on a "match" script? (the 8-hour example) Also, what is his blog URL?
Answer: "Put as much as you can in a single batch script" as opposed to opening separate SQLCMD processes.

Question: How is using integrated security a best practice? It requires implementation of domain accounts into sql server, accounts which any domain admin will have access to. Wouldn't it be less secure to do that? Domain admins don't necessarily need access to data?
Answer: Domain admins do not always have access to the database server. The domain admin can always be removed from the database security groups. It is more secure because you only have to log in once, and passwords do not have to be passed around and even worse, coded into scripts for wandering eyes to see.

Question: blog for Michiel (where the scripts will be posted):
Answer: https://blogs.msdn.com/mwories/

Question: any webcast on SMO?
Answer: There's a bit of discussion on SMO at https://msevents.microsoft.com/cui/webcasteventdetails.aspx?eventid=1032271603&culture=en-us

Question: Thanks both to Michiel and the MVPS and whomever else was helping answer inline questions - *very* useful session!
Answer: We love to help :)

Question: thx so much, the presentation was fantastic! I'm ready for SQL 2005 :D
Answer: We are too. The week of November 7 is going to be exciting!

Question: Scheduling via DTS vs. batch scripting -- what is more efficient?
Answer: SSIS jobs maybe faster, overall, than running semi-interpreted scripts.

Demo Scripts

(scripts in order as demonstrated)

selectversion.sql

select @@version

processes_formatted.sql

set nocount on
go

print 'You are connected to ' +
 rtrim(CONVERT(char(20), SERVERPROPERTY('servername'))) +
 ' (' +
 rtrim(CONVERT(char(20), SERVERPROPERTY('productversion'))) +
 ')' + char(10)

:setvar SQLCMDMAXFIXEDTYPEWIDTH 20

:r e:\sqlcmd\processes.sql

set nocount off
go

:setvar SQLCMDMAXFIXEDTYPEWIDTH

backuptemplate.sql

use master
backup database [$(db)] to disk='$(file)'

backupsingle.sql

:setvar db msdb
:setvar file c:\temp\msdb.bak

:r e:\sqlcmd\backuptemplate.sql

backupmulti.sql

:setvar db msdb

:connect .\yukon
:setvar file c:\temp\msdb1.bak
:r e:\sqlcmd\backuptemplate.sql
go

:connect .\sqlexpress
:setvar file c:\temp\msdb2.bak
:r e:\sqlcmd\backuptemplate.sql
go

codepage.sql

select FirstName, Lastname from AdventureWorks.Person.Contact
where LastName = 'Ferrier'

badscript.sql

select batch_1_this_is_an_error
go
select 'batch #2'
go

goodscript.sql

select 'batch #1'
go
select 'batch #2'
go

returnvalue.sql

:exit(select 100)

Demo Script

This was the demo script that was used during this session. The demo script may have been diverted from here and there.

1.1 Script Generation

Share: Management Studio

1. Open new editor Window. Show SQLCMD mode.

2. Show Template Explorer and double click ‘create database’

3. Object Explorer: Right click on AdventureWorks and Script CREATE to new query editor window

4. Object Explorer:

a. Right click Databases node and select Create New Database.

b. Type ‘Test’ as name of database and click Script to Query Editor

5. Object Explorer:

a. Right click on AdventureWorks and select Tasks -> Generate Script.

b. Select only Schema’s to be scripted.

c. Select Purchasing/Production schema’s and click Finish.

6. Cleanup: Hide Object Explorer; close all editor windows.

1.2 Getting Started with SQLCMD

Share: SQLCMD

1. Interactively

a. Start SQLCMD.

b. Type select @@version

c. Issue a GO.

2. Non interactive.

a. Type SQLCMD -i selectversion.sql

 

1.3 Start-up Script

Share: Frame

1. SSMS: Open processes_formatted.sql from Solution Explorer

2. Switch on SQLCMD mode

3. Explain script

4. Run Script

5. Switch to SQLCMD

6. Type set sqlcmdini=e:\sqlcmd\processes_formatted.sql

7. Start SQLCMD

8. Type set sqlcmdini=

1.4 Getting Help and Editing Scripts

Share: SQLCMD

1. Start SQLCMD

2. Type :help

3. Type select a [sic]

4. Type GO

5. Type :ED [correct script]

6. Type :list

7. Type :reset

8. Exit SQLCMD

 

1.5 Scripting Variables

Share: Frame

1. Switch to SQLCMD window

2. Start SQLCMD

3. Type: :listvar -- explain that the built-ins that are set are shown

4. Type: :setvar db AdventureWorks

5. Type: :listvar -- highlight the variable that was just set

6. Type: use $(db)

7. Type: GO

8. Explain how the variable got replaced and context was changed to AdventureWorks

9. Switch to Management Studio

10. Open backuptemplate.sql -- explain how variables will be replaced

11. Switch to SQLCMD window

12. Type: sqlcmd -i backuptemplate.sql -v db=msdb file="c:\temp\msdb.bak"

13. Switch to Management Studio

14. Open backuptemplate.sql -- explain that variables are now contained in script

15. Switch to SQLCMD window

16. Type: sqlcmd -i backupsingle.sql

1.6 Creating Master Scripts

Share: Frame

1. Switch to Management Studio

2. Open backupmulti.sql -- explain use of :connect

3. Switch to SQLCMD window

4. Type: sqlcmd -i backupmulti.sql

 

1.7 Working with code pages

Share: Frame

1. Switch to Management Studio

2. Open codepage.sql in workbench

3. Switch to SQLCMD window

4. Type: chcp -- explain current code page is 437. Current code page is Ansi Latin 1.

5. Type: sqlcmd -i codepage.sql -o cp.out -f o:437

6. Open cp.out with notepad. Point out that Francois Ferrier is not correct in Notepad as Notepad is ANSI.

7. Type: sqlcmd -i codepage.sql -o cp.out

8. Open cp.out with notepad. Should be OK now.

9. Type: findstr Fran cp.out

More info :

If no code pages are specified then sqlcmd will use the current code page [CP_ACP] for input and output, unless the input file is a Unicode file, in which case no conversion is needed. Unicode input files will be automatically recognized (either little or big-endian Unicode). If the -u option has been specified, output will always be little-endian Unicode. If no output file has been specified with the -o option, the output code page will be the console code page [GetConsoleOutputCP()]. This allows the output to be correctly displayed on the console display.

Code pages:

1252	Ansi Latin 1
28591	ISO-8859/1
437	OEM

1.8 Formatting Options

Share: SQLCMD

1. Type: sqlcmd -i codepage.sql

2. Type: sqlcmd -i codepage.sql -W

3. Type: sqlcmd -i codepage.sql -W -s,**

4. Type: sqlcmd -i codepage.sql -W -s, -h-1

5. Type: sqlcmd -i codepage.sql -Y 20

1.9 Handling Errors

Share: Frame

1. Switch to Management Studio

2. Open badscript.sql

3. Switch to SQLCMD window

4. Type: sqlcmd -i badscript.sql -- explain that script execution does not stop

5. Type: sqlcmd -i badscript.sql -b -- explain that script execution now stops at first error

6. Switch to Management Studio

7. Open goodscript.sql

8. Open runscript.bat -- walk through script

9. Switch to SQLCMD window

10.  Type runscript.bat -- explain results