Feeds:
Posts
Comments

Posts Tagged ‘SQL Server Tips and Tricks’

While I was migrating my database server from SQL 2000 to SQL Server 2005 I came across this error as the production and development environment were not quite identical.

Well the first thing to note in this error is non descriptive, preventing information disclosure to unauthenticated clients.

To determine the true reason for the failure, the administrator can look in the server’s error log where a corresponding entry will be written.  An example of an entry is:

2006-02-27 00:02:00.34 Logon     Error: 18456, Severity: 14, State: 8.

2006-02-27 00:02:00.34 Logon     Login failed for user ‘<user name>’. [CLIENT: <ip address>]

A great article with the complete description of this error and states is written by Il-Sung Lee here: http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx

Happy working with your linked servers.

Read Full Post »

Server: Msg 8180, Level 16

Server: Msg 102, Level 15

Server: Msg 1018, Level 15, State 1, Procedure SendReview, Line 12

Incorrect syntax near ‘FASTFIRSTROW’. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

Remote tables are not updatable. Updatable keyset-driven cursors on remote tables require a transaction with the REPEATABLE_READ or SERIALIZABLE isolation level spanning the cursor.

 

We were getting this error from the SQL server job that was running a store procedure inside which we were calling a table that was on a linked server running SQL Server 2005, while the major database was running on SQL Server 2000. We had the linked server setup and it was running fine. We could run queries to this table on the 2005 server without any difficulties.

One thing that I came to find out was the changes in the cursor. Please find details from the following link http://msdn.microsoft.com/en-us/library/ms143359.aspx.

 

Solution:

            1: Either you can have a temp table created out side the cursor to get all the values from across the linked server and then create the cursor over this table.

2: Secondly you can replace the Cursor with a simple loop.

3: Thirdly if possible and you can simply create the Cursor as “FOR Read Only”.

 

Hope this helps you people to migrate your databases happilyJ.

Well I found the solution from this URL:
http://www.eggheadcafe.com/software/aspnet/31278881/cannot-declare-a-cursor-t.aspx

Thought I should share it with everyone.

Read Full Post »