Modern Data Insight

Reserved Keywords are part of the grammar of the Transact-SQL language used by SQL Server to parse and understand Transact-SQL statements and batches. Although it is syntactically possible to use SQL Server reserved keywords as identifiers and object names in Transact-SQL scripts, this can be done only using delimited identifiers. It is recommended to not use the reserved keywords as identifiers or object names. more

SQLCMD is DOS command prompt utility introduced in 2005 that supersedes isql.exe and osql.exe. Like isql and osql, SQLCMD.exe can be used to execute T-SQL statements and scripts. Unlike isql and osql, SQLCMD has extensive support for scripting and variables. SQLCMD.exe uses OLE DB to connect and execute the T-SQL batches. more Another example

Time to move to new SQL server . You can either download Yukon or order from microsoft. Some good links about sql 2005 online books and additional tools . Another good article on Yukon, SQLOS .

I use this tool to connect to sql server. It works without any kind of install on the client computer. Its similar to ms sql querry analyzer. Attached image shows how to connect to the server etc. Download Toad. More USB Tools Links .

This query shows any process thats running more than 30 secs. SELECT spid, cmd, status, loginame, open_tran, datediff(s, last_batch, getdate ()) AS [WaitTime(s)] FROM master..sysprocesses p WHERE open_tran > 0 AND spid > 50 AND datediff (s, last_batch, getdate ()) > 30 ANd EXISTS (SELECT * FROM master..syslockinfo l WHERE req_spid = p.spid AND rsc_type 2) This query shows if a process is running more that 10 secs. SELECT spid, waittime, lastwaittype, waitresource FROM master..sysprocesses WHERE waittime > 10000 -- The wait time is measured in milliseconds AND spid > 50 you can kill the process with command - kill [spid]

isqlw -S [server name] -d [database name] -U [user name] -P [password] C:\>isqlw -S [server name] -d [database name] -U [user name] -P [password] -i "c:\project\sql\emp.sql" -o "c:\project\sql\test.csv" With this command line sql example you can run a sql command from dos prompt and send output to a file.

Following article shows how to use external text file in SQL server. But you have to remember the speed issue in using a text file compared to indexed table. Connect to Text Files with the Microsoft Jet ODBC Text Driver

SQL bcp Exprot to CSV: Following process can be used to create csv from sql server with bcp command. First of all we need a format file and then bcp command in stored proc or run directly from query analyzer. Sample Format File 7.0 [sql bcp version] 305 [number of data fields] 1 SQLCHAR 0 9 "," 1 MyField Exec master..xp_cmdshell bcp "SELECT * FROM ##WrkData_LBW_Exp_2" queryout ' + [export file].csv + ' -f\\[format file].fmt -U [user name] -P [pasword] reference: bcp

BCP Basics : bcp sql command can be used to import data. This process is faster than dts. bcp can be used with format file or data can directly imported to a pre-defined table. Format file is the key in this process. Code : BULK INSERT tempdb..U_LNBASE_IMPORT_LNKEYS FROM '\\dfw-iapp-fsnt51\wiredata\loans.txt' WITH ( FIELDTERMINATOR = '"', ROWTERMINATOR = '\n' )

SQL Table Definition : This sql query is used to find table definition without using enterprise manager table design. Code : SELECT a.[name] as 'Table', b.[name] as 'Column', c.[name] as 'Datatype', b.[length] as 'Length', CASE WHEN b.[cdefault] > 0 THEN d.[text] ELSE NULL END as 'Default', CASE WHEN b.[isnullable] = 0 THEN 'No' ELSE 'Yes' END as 'Nullable' FROM sysobjects a INNER JOIN syscolumns b ON a.[id] = b.[id] INNER JOIN systypes c ON b.[xtype] = c.[xtype] LEFT JOIN syscomments d ON b.[cdefault] = d.[id] WHERE a.[xtype] = 'u' -- 'u' for user tables, 'v' for views. and a.[name]='table name' AND a.[name] 'dtproperties' ORDER BY a.[name],b.[colorder]

SQL Table Space Script : This sql query uses "sp_mstablespace" stored procedure to get table space for all the user table in the server. Code : SELECT a.[name] as 'Table', (select convert(int, rowcnt) FROM dbo.sysindexes WHERE indid (((SELECT sum(dpages) FROM dbo.sysindexes WHERE indid + (SELECT isnull(sum(used), 0) FROM dbo.sysindexes WHERE indid = 255 and id = a.id))*8) as DataSpaceUsed, (((SELECT sum(used) FROM dbo.sysindexes WHERE indid in (0, 1, 255) and id = a.id) - ((SELECT sum(dpages) FROM dbo.sysindexes WHERE indid + (SELECT isnull(sum(used), 0) FROM dbo.sysindexes WHERE indid = 255 and id = a.id)) )*8) as IndexSpaceUsed FROM sysobjects a WHERE a.[xtype] = 'u' AND a.[name] 'dtproperties' ORDER BY a.[name]

How to find all the user tables in a database : select * from sysobjects a where a.[xtype] = 'u' and a.[name] 'dtproperties' . This will give you list of all your tables.