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.
Profiler is handy in troubleshooting this error. If data truncation is okay with you and you don't want to see this error, then turn off ANSI WARNINGS by using the following SET command: SET ANSI_WARNINGS OFF.
Steps to reproduce the problem:
CREATE TABLE MyTable
(
Pkey int PRIMARY KEY,
Col1 char(10)
)
GO
INSERT INTO MyTable (Pkey, Col1) VALUES (1, 'SQL Server Clustering FAQ')
GO
Make sure, you restrict the length of input, in your front-end applications. For example, you could use the MAXLENGTH property of the text boxes in HTML forms. E.g:
Profiler is handy in troubleshooting this error. If data truncation is okay with you and you don't want to see this error, then turn off ANSI WARNINGS by using the following SET command: SET ANSI_WARNINGS OFF.
Steps to reproduce the problem:
CREATE TABLE MyTable
(
Pkey int PRIMARY KEY,
Col1 char(10)
)
GO
INSERT INTO MyTable (Pkey, Col1) VALUES (1, 'SQL Server Clustering FAQ')
GO
Make sure, you restrict the length of input, in your front-end applications. For example, you could use the MAXLENGTH property of the text boxes in HTML forms. E.g:
CASE
is the equivalent of IIF function. See SQL Server Books Online for more
information. Here's a quick example:
CREATE TABLE People
(
[ID] int PRIMARY KEY,
[Name] varchar(25) NOT NULL,
Sex bit NULL
)
INSERT INTO People ([ID],[Name], Sex) VALUES (1,'John Dykes', 1)
INSERT INTO People ([ID],[Name], Sex) VALUES (2,'Deborah Crook', 0)
INSERT INTO People ([ID],[Name], Sex) VALUES (3,'P S Subramanyam', NULL)
SELECT [ID], [Name],
CASE Sex
WHEN 1
THEN 'Male'
WHEN 0
THEN 'Female'
ELSE 'Not specified'
END AS Sex
FROM People
CREATE TABLE People
(
[ID] int PRIMARY KEY,
[Name] varchar(25) NOT NULL,
Sex bit NULL
)
INSERT INTO People ([ID],[Name], Sex) VALUES (1,'John Dykes', 1)
INSERT INTO People ([ID],[Name], Sex) VALUES (2,'Deborah Crook', 0)
INSERT INTO People ([ID],[Name], Sex) VALUES (3,'P S Subramanyam', NULL)
SELECT [ID], [Name],
CASE Sex
WHEN 1
THEN 'Male'
WHEN 0
THEN 'Female'
ELSE 'Not specified'
END AS Sex
FROM People
Everytime
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:
SELECT crdate AS 'SQL Server service started approximately at:'
FROM master.dbo.sysdatabases
WHERE name = 'tempdb'
SQL Server error log also has this information (This is more accurate) and the error log can be queried using xp_readerrorlog
SELECT crdate AS 'SQL Server service started approximately at:'
FROM master.dbo.sysdatabases
WHERE name = 'tempdb'
SQL Server error log also has this information (This is more accurate) and the error log can be queried using xp_readerrorlog
We
have to use the CONVERT function to strip the time off the date. Any of the
following commands will do this:
SELECT CONVERT(char,GETDATE(),101)
SELECT CONVERT(char,GETDATE(),102)
SELECT CONVERT(char,GETDATE(),103)
SELECT CONVERT(char,GETDATE(),1)
See SQL Server Books Online for more information on CONVERT function.
SELECT CONVERT(char,GETDATE(),101)
SELECT CONVERT(char,GETDATE(),102)
SELECT CONVERT(char,GETDATE(),103)
SELECT CONVERT(char,GETDATE(),1)
See SQL Server Books Online for more information on CONVERT function.
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:
- SQL Server 7.0 and 2000 come with a utility called textcopy.exe. You can locate this file in the Binn folder under your SQL Server installation folder. Run this file from command prompt, and it will prompt you for required input
- Use the GetChunk and AppendChunk methods of ADO Field object. MSDN has examples
- Use the ADO Stream object
- Use the Bulk Insert Image utility (BII)
that ships with SQL Server 2000 (Can be found at \Program Files\Microsoft
SQL
Server\80\Tools\Devtools\Samples\Utils)
There's
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:
EXEC master..xp_cmdshell 'osql -Svaio -Usa -Pzaassds1 -ic:\MySQl.sql -n'
EXEC master..xp_cmdshell 'isql -Svaio -Usa -Pzaassds1 -ic:\MySQl.sql -n'
See xp_cmdshell in SQL Server Books Online if you are having permissions problems in getting this technique to work.
EXEC master..xp_cmdshell 'osql -Svaio -Usa -Pzaassds1 -ic:\MySQl.sql -n'
EXEC master..xp_cmdshell 'isql -Svaio -Usa -Pzaassds1 -ic:\MySQl.sql -n'
See xp_cmdshell in SQL Server Books Online if you are having permissions problems in getting this technique to work.
Unfortunately,
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 description.
For a complete error message, you can always query the master..sysmessages table using the error number, but most of these messages have place holders (like %s, %l etc.), and hence we can't get the complete error message.
However, the client applications using an object model such as RDO, ADO have access to the complete error message.
For a complete error message, you can always query the master..sysmessages table using the error number, but most of these messages have place holders (like %s, %l etc.), and hence we can't get the complete error message.
However, the client applications using an object model such as RDO, ADO have access to the complete error message.
Here's
the code:
DECLARE @Date datetime
SET @Date = '2001/08/31'
SELECT DATEADD(dd,-(DATEPART(dw, @Date) - 1),@Date) AS 'First day of the week'
SELECT DATEADD(dd,-(DATEPART(dw, @Date) - 7),@Date) AS 'Last day of the week'
SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,@Date)),DATEADD(m,1,@Date))) AS 'Last day of the month'
DECLARE @Date datetime
SET @Date = '2001/08/31'
SELECT DATEADD(dd,-(DATEPART(dw, @Date) - 1),@Date) AS 'First day of the week'
SELECT DATEADD(dd,-(DATEPART(dw, @Date) - 7),@Date) AS 'Last day of the week'
SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,@Date)),DATEADD(m,1,@Date))) AS 'Last day of the month'
Basically,
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)
CREATE PROC DropTable
@Table sysname
AS
EXEC ('DROP TABLE ' + @Table)
GO
EXEC DropTable 'MyTable'
GO
CREATE PROC SelectTable
@Table sysname
AS
EXEC ('SELECT * FROM ' + @Table)
GO
EXEC SelectTable 'MyTable'
For a complete discussion on the pros and cons of dynamic SQL check out Erland's article:
The curse and blessings of dynamic SQL
CREATE PROC DropTable
@Table sysname
AS
EXEC ('DROP TABLE ' + @Table)
GO
EXEC DropTable 'MyTable'
GO
CREATE PROC SelectTable
@Table sysname
AS
EXEC ('SELECT * FROM ' + @Table)
GO
EXEC SelectTable 'MyTable'
For a complete discussion on the pros and cons of dynamic SQL check out Erland's article:
The curse and blessings of dynamic SQL
This
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 recordsets.
Having SET NOCOUNT ON at the beginning of the procedure also helps avoid this problem. SET NOCOUNT ON also helps in improving the stored procedure performance. Here's a sample procedure to simulate the problem:
CREATE PROC TestProc
AS
SELECT MAX(Col1) FROM TestTable
SELECT MIN(Col1) FROM TestTable
INSERT INTO TestTable (Col1, Col2) VALUES (1,'Oracle and SQL Server comparison')
INSERT INTO TestTable (Col1, Col2) VALUES (1,'How to configure SQL Server?') -- Dupplicate key error occurs
GO
Having SET NOCOUNT ON at the beginning of the procedure also helps avoid this problem. SET NOCOUNT ON also helps in improving the stored procedure performance. Here's a sample procedure to simulate the problem:
CREATE PROC TestProc
AS
SELECT MAX(Col1) FROM TestTable
SELECT MIN(Col1) FROM TestTable
INSERT INTO TestTable (Col1, Col2) VALUES (1,'Oracle and SQL Server comparison')
INSERT INTO TestTable (Col1, Col2) VALUES (1,'How to configure SQL Server?') -- Dupplicate key error occurs
GO
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:
On Error Resume Next
Set Rs = Conn.Execute ("INSERT INTO MyTable (1,'How to migrate from Oracle to SQL Server','Book'")
If Err.Number <> 0 Then Response.Write ("Error occurred while inserting new data")
On Error GoTo 0
On Error Resume Next
Set Rs = Conn.Execute ("INSERT INTO MyTable (1,'How to migrate from Oracle to SQL Server','Book'")
If Err.Number <> 0 Then Response.Write ("Error occurred while inserting new data")
On Error GoTo 0
T-SQL
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 examples:
From command prompt:
osql.exe -S YourServerName -U sa -P secretcode -Q "EXEC sp_who2" -o "E:\output.txt"
From T-SQL:
EXEC master..xp_cmdshell 'osql.exe -S YourServerName -U sa -P secretcode -Q "EXEC sp_who2" -o "E:\output.txt"'
Query Analyzer lets you save the query output to text files manually. The output of stored procedures that are run as a part of a scheduled job, can also be saved to a text file.
BCP and Data Transformation Services (DTS) let you export table data to text files.
From command prompt:
osql.exe -S YourServerName -U sa -P secretcode -Q "EXEC sp_who2" -o "E:\output.txt"
From T-SQL:
EXEC master..xp_cmdshell 'osql.exe -S YourServerName -U sa -P secretcode -Q "EXEC sp_who2" -o "E:\output.txt"'
Query Analyzer lets you save the query output to text files manually. The output of stored procedures that are run as a part of a scheduled job, can also be saved to a text file.
BCP and Data Transformation Services (DTS) let you export table data to text files.
You
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.
SELECT a.i, a.j
FROM pubs.dbo.Table1 a
INNER JOIN
northwind.dbo.Table2 b
ON a.i = b.i
GO
SELECT a.i, a.j
FROM pubs.dbo.Table1 a
INNER JOIN
northwind.dbo.Table2 b
ON a.i = b.i
GO
To
be 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:
EXEC sp_addlinkedserver SERVER_01
GO
/* The following command links 'sa' login on SERVER_02 with the 'sa' login of SERVER_01 */
EXEC sp_addlinkedsrvlogin @rmtsrvname = 'SERVER_01', @useself = 'false', @locallogin = 'sa', @rmtuser = 'sa', @rmtpassword = 'sa password of SERVER_01'
GO
SELECT a.title_id
FROM SERVER_01.pubs.dbo.titles a
INNER JOIN SERVER_02.pubs.dbo.titles b
ON a.title_id = b.title_id
GO
EXEC sp_addlinkedserver SERVER_01
GO
/* The following command links 'sa' login on SERVER_02 with the 'sa' login of SERVER_01 */
EXEC sp_addlinkedsrvlogin @rmtsrvname = 'SERVER_01', @useself = 'false', @locallogin = 'sa', @rmtuser = 'sa', @rmtpassword = 'sa password of SERVER_01'
GO
SELECT a.title_id
FROM SERVER_01.pubs.dbo.titles a
INNER JOIN SERVER_02.pubs.dbo.titles b
ON a.title_id = b.title_id
GO
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:
- Add an IDENTITY column to your table. See Books Online
for more information
- Use the following query to generate a row number for
each row. The following query generates a row number for each row in the
authors table of pubs database. For this query to work, the table must
have a unique key.
SELECT (SELECT COUNT(i.au_id)
FROM pubs..authors i
WHERE i.au_id >= o.au_id ) AS RowID,
au_fname + ' ' + au_lname AS 'Author name'
FROM pubs..authors o
ORDER BY RowID
- Use a temporary table approach, to store the entire resultset into a temporary table, along with a row id generated by the IDENTITY() function. Creating a temporary table will be costly, especially when you are working with large tables. Go for this approach, if you don't have a unique key in your table. Search for IDENTITY (Function) in SQL Server Books Online.
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:
Network=dbmssocn
For more information on specifying other net libraries in ADO connect strings, click here to read the article from Microsoft Knowledgebase.
Network=dbmssocn
For more information on specifying other net libraries in ADO connect strings, click here to read the article from Microsoft Knowledgebase.
- Use sp_helptext to get the current code of MyProc.
- Change the code as needed.
- Run the following code to drop the existing version of MyProc:
IF EXISTS(SELECT 1 FROM sysobjects WHERE name = 'MyProc' AND type = 'P' AND USER_NAME(uid) = 'dbo')
BEGIN
DROP PROC dbo.MyProc
END
- Run the updated code to recreate MyProc
There is a much more powerful way out, if you can use Visual Source Safe (VSS). VSS is a version control software, that lets you manage your code. With VSS in place, you will have to maintain all your object creation scripts as script files and check them into VSS. When you have to modify a particular stored procedure, check out that script from VSS, modify it, test it, create the stored procedure, and check the script back into VSS. VSS can show you when a script got modified, by who and a whole lot of other information.
Advantages of using VSS
- You can version control your software, as VSS maintains all your changes as different versions
- You can go back to a previous known good version of your stored procedure, if a developer makes a mistake
- Using the labelling feature, you can revert back to an entire set of scripts at a particular point in time
- You can control access to your source code by configuring permissions to your developers
- By maintaining backups of VSS database, you can secure all your code centrally, instead of worrying about individual script files
If you have any quarries/feed back catch me on - knowthetesting@gmail.com
Thank you
Ram
