My technical skills
My picture album
My code library
Search my site
Sign my guestbook
SQL Server Articles New
Database encryption for SQL Server and MSDE:
Why are my insert, update statements failing with the following error?
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
What is the T-SQL equivalent of IIF (immediate if/ternary operator) function of other programming languages?
How to programmatically find out when the SQL Server service started?
How to get rid of the time part from the date returned by GETDATE function?
How to upload images or binary files into SQL Server tables?
How to run an SQL script file that is located on the disk, using T-SQL?
How to get the complete error message from T-SQL while error handling?
How to get the first day of the week, last day of the week and last day of the month using T-SQL date functions?
How to pass a table name, column name etc. to the stored procedure so that I can dynamically select from a table?
Error inside a stored procedure is not being raised to my front-end applications using ADO. But I get the error when I run the procedure from Query Analyzer
How to suppress error messages in stored procedures/triggers etc. using T-SQL?
How to save the output of a query/stored procedure to a text file?
How to join tables from different databases?
How to join tables from different servers?
How to convert timestamp data to date data (datetime datatype)?
Can I invoke/instantiate COM objects from within stored procedures or triggers using T-SQL?
Oracle has a rownum to access rows of a table using row number or row id. Is there any equivalent for that in SQL Server? Or how to generate output with row number in SQL Server?
How to specify a network library like TCP/IP using ADO connect string?
How to generate scripts for repetitive tasks like truncating all the tables in a database, changing owner of all the database objects, disabling constraints on all tables etc?
Is there a way to find out when a stored procedure was last updated?
How to find out all the IDENTITY columns of all the tables in a given database?
How to search the code of stored procedures?
How to retrieve the generated GUID value of a newly inserted row? Is there an @@GUID, just like @@IDENTITY ?
This error occurs, when
the length of the value entered by you into a char,
varchar, nchar, nvarchar column is longer than the
maximum length of the column. For example, inserting
'FAQ' into a char(2) column would result in this error.
the equivalent of IIF function. See SQL Server Books
Online for more information. Here's a quick example:
SQL Server starts, it recreates the tempdb database. So,
the creation date and time of the tempdb database tells
us the date and time at which SQL Server service started.
This information is stored in the crdate column of the
sysdatabases table in master database. Here's the query
to find that out:
to use the CONVERT function to strip the time off the
date. Any of the following commands will do this:
First of all, if possible, try not to stored images and other binary files in the SQL Server tables, as they slow things down. Instead, store a link (file path) to the file in the tables and let your applications directly access the files. But if you must store these files within SQL Server, use the text/ntext or image datatype columns and consider the following options:
no direct command to read a script file and execute it.
But the isql.exe and osql.exe come in handy when you have
to execute a script file from within T-SQL. Just call any
of these exes using xp_cmdshell and pass the script file
name as parameter to it. See SQL Server Books Online for
more information about the input parameters of these
exes. Here are some quick examples:
the error handling capabilities of SQL Server are
limited. When an error occurs, all you can get is the
error number, using the @@ERROR global variable. There is
no @@ERROR_MESSAGE global variable to get the error
SELECT and other commands like DROP TABLE won't let you
use a variable instead of a hardcoded table name. To
overcome this problem, you have to use dynamic sql. But
dynamic SQL has some disadvantages. It's slow, as the
dynamic SQL statement needs to be parsed everytime it's
executed. Further, the user who is executing the dynamic
SQL string needs direct permissions on the tables, which
defeats the purpose of having stored procedures to mask
the underlying tables. Having said that, here are some
examples of dynamic SQL: (Also see sp_executesql in SQL
Server Books Online)
typically happens when your stored procedure is returning
multiple resultsets and the offending SQL statement is
executed after returning one or more resultsets. ADO will
not return an error untill it processes all the
recordsets returned before the offending SQL statement
got executed. So, to get to the error message returned by
your procedure. You have to loop through all the
recordsets returned. ADO Recordset object has a method
called NextRecordset, which lets you loop through the
It's not possible to suppress error
messages from within T-SQL. Error messages are always
returned to the client. If you don't want your users to
see these raw error messages, you should handle them in
your front-end applications. For example, if you
are using ADO from ASP to connect to SQL Server, you
would do something like the following:
by itself has no support for saving the output of
queries/stored procedures to text files. But you could
achieve this using the command line utilities like
isql.exe and osql.exe. You could either invoke
these exe files directly from command prompt/batch files
or from T-SQL using the xp_cmdshell command. Here are the
just have to qualify the table names in your SELECT queries with
database name, followed by table owner name. In the
following example, Table1 from pubs database and Table2
from northwind database are being joined on the column i.
Both tables are owned by dbo.
able to join tables between two SQL Servers, first you
have to link them. After the linked servers are setup,
you just have to prefix your tables names with server
name, database name, table owner name in your SELECT
queries. The following example links SERVER_01 to
SERVER_02. Execute the following commands in SERVER_02:
The name timestamp is a little misleading. Timestamp data has nothing to do with dates and times and can not be converted to date data. A timestamp is a unique number within the database and is equivalent to a binary(8)/varbinary(8) datatype. A table can have only one timestamp column. Timestamp value of a row changes with every update of the row. To avoid the confusion, SQL Server 2000 introduced a synonym to timestamp, called rowversion.
Yes. SQL Server provides system stored procedures that let you instantiate COM objects using T-SQL from stored procedures, triggers and SQL batches. Search SQL Server Books Online for sp_OACreate and sp_OA* for documentation and examples. Also check out my code library for an example.
There is no direct equivalent to Oracle's rownum or row id in SQL Server. Strictly speaking, in a relational database, rows within a table are not ordered and a row id won't really make sense. But if you need that functionality, consider the following three alternatives:
For more ideas on this topic, click here to read an informative article from Microsoft Knowledgebase.
To specify TCP/IP net
library, append the following to your ADO connect string:
Simple answer is 'No'.
The crdate column in the sysobjects table always contains
the stored procedure create date, not the last updated
date. You can use Profiler to trace ALTER PROC calls to
the database, but you can't really afford to run a trace
for ever, as it's resource intensive. Here is a simple
idea! Whenever you have to alter your stored procedure,
first drop it, then recreate it with the updated code.
This resets the crdate column of sysobjects table. If you
can make sure your developers always follow this plan,
then the crdate column of sysobjects will always reflect
the last updated date of the stored procedure. For
example, if I have to modify a procedure named MyProc,
instead of doing "ALTER PROC MyProc", here's
what I would do: