GetProcedureColumns Behavior Changed From SQLServer 2000 To 2005?
Jun 20, 2007
A stored procedure takes an IN parameter, an INOUT parameter, and returns an OUT parameter. When this stored procedure is defined in SQL Server 2000, the JDBC DatabaseMetadata method getProcedureColumns() returns three rows in the resultset:
one for the IN parameter (COLUMN_TYPE=1)
one for the INOUT parameter (COLUMN_TYPE=2), and
one for OUT parameter (COLUMN_TYPE=5).However, when the same stored procedure is defined in SQL Server 2005 (SP2), the getProcedureColumns() method returns only two rows in the resultset:
one for the IN parameter (COLUMN_TYPE=1)
one for the INOUT parameter (COLUMN_TYPE=2).No row for the OUT parameter is returned.
jTDS JDBC driver was used for both of the above tests. When the Microsoft JDBC Driver for 2005 was used against SQL Server 2005, the same behavior (only two rows in the resultset) was observed.
Has someone else run into such a problem? Is this a bug in SQL Server 2005 because the same jTDS driver works as expected against SQL Server 2000 but not against SQL Server 2005? Any feedback will be appreciated.
We are in the process of upgrading from SQL Server 2000 to 2005. During testing we came across the following situation.
To reproduce the issue you can do the following
Create this structure in a 2000 and 2005 server instances
CREATE TABLE test (a int, b varchar(30))
INSERT INTO test (a, b) VALUES (1, '2')
INSERT INTO test (a, b) VALUES (3, '3a')
INSERT INTO test (a, b) VALUES (4, '4')
Then run the following statement in both servers:
UPDATE test SET a = ltrim(rtrim(b)) WHERE b NOT LIKE '%a%' AND ltrim(rtrim(b)) <> a
In 2000 this last statement will execute with no problem and it will update one row, whereas in 2005 the following error message is given:
Msg 245, Level 16, State 1, Line 20 Conversion failed when converting the varchar value '3a' to data type int.
By looking at the execution plan it seems that 2005 first tries to evaluate ltrim(rtrim(b)) <> a and then excludes those rows containing a whereas 2000 first excludes those rows containing a and then evaluates the different than condition.
I know fixing this instance itself is easy but I€™m more concerned about having to rewrite many more stored procedures where we find this same scenario; is there any setting that can be changed to avoid this?
I am trying to 'load' a copy of a SQLServer 2000 database to SQLServer 2005 Express (on another host). The copy was provided by someone else - it came to me as a MDF file only, no LDF file.
I have tried to Attach the database and it fails with a failure to load the LDF. Is there any way to bypass this issue without the LDF or do I have to have that?
The provider of the database says I can create a new database and just point to the MDF as the data source but I can't seem to find a way to do that? I am using SQL Server Management Studio Express.
I have an app that uses a sqlserver 2000 jdbc driver to connect to a sqlserver 2000.
Is it possible to do a direct replacement of sqlserver 2000 with sqlserver 2005 express just by reconfiguring the app to point to the express? The app would still be using the sqlserver 2000 jdbc driver to try and make the connection.
If that is a possibility, what can be some differences in the configuration? Previously with 2000 the config information I entered is:
server name: "machinename"( or ip). I've also tried "machiname/SQLEXPRESS"
DB name: name of db instance
port: 1433(default)
user and pass.
My attempts so far results in
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Error establishing socket."
and
"java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Unable to connect. Invalid URL."
Some time ago I saw a section in sql 2005 BOL describing the t-sql features that have changed from sql 2000 to sql 2005. For example, some NULL handling and that "*=" is no longer supported. Can anyone find that refernece for me?
Hello,I developed a win32 .exe CGI that connects to a clustered SQLServer toreport some data.The software is written with Borland C++Builder.This is the oledb string:Name=Provider=SQLOLEDB;Password=xxx;Persist Security Info=True;UserID=xxxx;Data Source=xxxxx;Initial Catalog=xxxxx;NetworkLibrary=dbmssocnIt suddendly stopped working on my customer network, so I made sometest and I verivied that the problem is on the connection withSQLServer: my test program just opens a connection, closes it andexits, reporting in a log file if the open was successful or failed.If I run the program locally, just launching it, no problem, it works.I can run it mutilple time continuosly and is connects every time.If I run the program through my webserver, as a CGI that's how it issupposed to work (http:\localhostscriptsconnect.exe), it connectsthe first time, and then I have to wait 40 seconds to connect againsuccessfully, or it fails.If I try against MY sqlserver on my laptop or on my network no 40 secproblems, but on my customer network, with THEIR SQLServer , if I tryto connect from their webserver, or from my laptop webserver, I havethis 40sec problem.I analyzed the network traffic, and I discovered that when I run mytest program locally it originates only TCP/IP packets, and SQLServeranswers only with TCP/IP.But when I use it from the webserver as a CGI, it originates an UDPpacket, then SQL answers with another UDP packet, and then theycommunicate over TCP/IP.This when it works: the second time my program continues to send theUDP packet, but it receives no answer, and fails the communication.I can only say that:- we haven't touched the program for months, and it really stoppedworking suddendly, so I suspect that something in my customer netowrkhas changed- I tried many different OLEDB strings, disabling connection poolingand all the services, calling the SQLServer by name or IP...- the problem can't be related to my program, because now is reallyjust an oledb connection testAnyone have an idea?Thank you very much,Mattia
We do our development in SQLServer 2005. We want to backup and restore our database into the client's side who use SQLServer 2000. Backup and restore cannot work. We try to script the database in 2005 and change the option script to 2000. This fails also. What other option I can use?
Hi, I am trying to transfer data from a sql server 2000 database to a Sql Server 2005 database... and i just want to transfer around 2 tables.. and my sql server 2000 database is located in one machine and the 2005 database is located in the other machine.. So i am trying to insert data into the sql server 2005 database. So can i do it like this.. Insert into SqlServer2005comp.Databasename..TableName ( Columns ) Select Columns From sqlserver2000comp.Databasename..tablenames Where UserId = @UserId. Is this the right way to do it.. and can i give this query in the sqlserver2005 db or the 2000 db any help or ideas will be appreciated. Regards... Karen
hello all.I have a database file in sqlserver 2005 mode, i set it's compatiblity to sqlserver 2000 (80) but i cant attach it in sqlserver 2000.My host only support sqlserver 2000!I need any help to convert or attach it!Thank all!
Hello, is it possible to have both sql server 2000 and sql server express 2005 installed at the same box?
I currently have sql2000 installed its used for an application. But I want to install visual studio 2005 and with this sql server express 2005 also is being installed ( if you want to) now I wonder if this is possible, that is is it possible to both run sql server 2000 and sql server express at the same time on the same box?
I heard from someone that there is a tool that can verify/check whether the database can be deported to SQL Server 2005 native (90) or not. Is this true?
My upgrade of SQL Server 2000 to SQL Server 2005 is being blocked as follows: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding FTCATALOGPATH DEPRECATEDSP FTCATALOGNAMERESTRICTION FTNONPERSISTEDCOMPCOL
I am trying to transfer data from a sql server 2000 database to a Sql Server 2005 database... and i just want to transfer around 2 tables.. and my sql server 2000 database is located in one machine and the 2005 database is located in the other machine.. So i am trying to insert data into the sql server 2005 database. So can i do it like this..
Insert into SqlServer2005comp.Databasename..TableName
(
Columns
)
Select
Columns
From
sqlserver2000comp.Databasename..tablenames
Where UserId = @UserId.
Is this the right way to do it.. and can i give this query in the sqlserver2005 db or the 2000 db
I have many sql 2000 DTS packages that I support from my development workstation running v2000 sp4. Packages are altered on the development machine and then go through a normal release mechanisms to production via testing servers etc.
I have recently installed the client tools for SQL Server 2005 on my desktop to evaluate the product. The 2005 DB instance is running on a seperate server.
So, I have dev edition of sql 2000 and 2005 client tools (including BI Dev studio etc) on my workstation.
I have recently had to make changes to a 2000 DTS package and used my 2000 enterprise manager to do so. No Problem- saved and tested fine on my workstation.
But when I try and release it to another server, or open the package using enterprise manager from another machine that does not have sql 2005 installed - I get an error message 'Unspecified error'. This I've seen before when trying to open packages created in v2000 , using v7 or where the service packs are different between machines.
Digging around my workstation and comparing some of the DLLs I know to be required to distribute DTS packages (from RDIST.txt) it seems that some of the SQL 2000 dll files have been updated by my 2005 installation.
E.g
DTSFFILE.DLL on my machine is 2000.85.1054.0 whilst on any 'clean' 2000 machine is at version v2000.80.760.0
Surely it cant be right that SQL 2005 has newer versions of components for SQL 2000 than is available with the latest SP for the actual product! Especially considering that the installation of 2005 does not even allow you to edit 2000 DTS packages through the management studio without a 'special' download' of the feature pack,(whihc by the way does not work very well either)
So am I to conclude that you can not run side by side installations of SQL 2000 and 2005 on a single machine and expect 2000 to run as it did previously !!!
Apparently you cannot go backwards. Once 2005 Express is installed, even removing it does not allow you to setup MSDE 2000 again. So there is no "trying it out" option.
Unfortunately, our software does not use 2005 Express. I tried it out, and now have a useless testing workstation that can't have MSDE 2000 installed again.
Any ideas on how to break the chain here are welcome.
Hi,anyone could explain this : I have installed the same asp.net application on 2 distinct hosts, each with its own SQL Server 2000 database.However, in some SQL queries in Stored Procedures, the results do not come out the same.For example, on one server the query "select @value = (select ....)-(select ...)" works and on the other one it returns a null valueAny hint ?Thanks for your helpJohann
I was able to successfully create a database maintenance plan for SQL Server 2000 Transaction Log Shipping for a few databases a few weeks ago. Yesterday, I created a few more but to my surprise, I can no longer do it. I can create a maintenance plan but the job it creates does not start even if I force the job to start. I did exactly the same thing as what I did (as I document everything I do) before but no luck.
Hi I've got a sql server 2000 database that when running is runnign fine. About 9 months ago I altered one of the stored procedures and ever since then when the machine is rebooted the stored procedure is "reverted" back to the old sproc... ??? is there any way I can recrete a sproc in a job that runs every day?? why would it be doing this?
Hi All,I'm a relatively newbie to SQL Server 2000, having come from a MySQLbackground.I'm creating my first Trigger statement on a table, and I'd like toknow how I go about performing an update on the row that was changedwhen the trigger was fired.To explain, I have 2 columns, one which contains a member number, theother which contains a flag that is supposed to indicate whether ornot the member number in the row has changed since the last time thetable was processed for updates.So, whenever the value in the member number field [memnum] is updated,I want to set the flag [igproc] to true.The best I've been able to do is:CREATE TRIGGER [updateignoreprocflag] ON [dbo].[dd_testtable]FOR UPDATEASdeclare @key as intIF UPDATE (memnum)select @key = recid from insertedUPDATE dd_testtable set igproc=1 where recid=@keyThis seems to work, but I'd like to know if there's a better way ofretrieving the recid value of the changed row to pass to the UPDATEstatement? Also, I read somewhere in passing that using SELECTstatements and variable assignments within triggers can cause problemswhen called from other applications; in this case it will either be aweb site using ASP.or an application developed in FOXPRO. I can't findwhere I read this originally, so it's entirely possible I imagined itor misunderstood it, but I'd very much appreciate it if someone couldconfirm whether or not this is the case?Many, many thanks in advance!Much warmth,Murray
We have an interesting problem. We are attempting to migrate from sql 2000 to sql 2005. the schema we have is exactly the same. the new 2005 box is more powerful than our 2000 box.
here is our schema:
tbl_Items ItemID int pk ReferenceID int sessionid varchar(255) StatusID int
tbl_ItemsStatus statusid int pk isinternalstatus bit
there is an index on (ReferenceID, SessionID, StatusID) and (SessionID, StatusID)
this is the query:
DECLARE @referenceid INTEGER SET @referenceid = 1019
SELECT MAX(i2.itemid) FROM tbl_Items i2 (NOLOCK) JOIN tbl_ItemsStatus s (NOLOCK) ON i2.StatusID = s.StatusID WHERE s.IsInternalStatus = 0 AND i2.referenceid = @referenceid AND i2.sessionid IN ( SELECT i3.sessionid FROM tbl_Items i3 (NOLOCK) WHERE i3.referenceid = @referenceid AND i3.status <> 7 AND i3.status <> 8 AND i3.status <> 10 AND i3.itemid IN ( SELECT max(i4.itemid) FROM tbl_Items i4 (NOLOCK) WHERE i4.referenceid = @referenceid GROUP BY i4.sessionid ) AND i3.itemid NOT IN ( SELECT MAX(i7.itemid ) FROM tbl_Items i7 (NOLOCK) WHERE i7.referenceid = @referenceid AND i7.SessionID IN ( SELECT i5.SessionID FROM tbl_Items i5 (NOLOCK) WHERE i5.status <> 11 AND i5.referenceid = @referenceid AND i5.itemid IN ( SELECT MAX(i6.itemid) FROM tbl_Items i6 (NOLOCK) WHERE i6.referenceid = @referenceid AND i6.status IN (7,11,8) GROUP BY i6.sessionid ) ) GROUP BY i7.SessionID ) )
GROUP BY i2.sessionid
we know this query is pretty bad and can be optimized. however, if we run this query as is on 2005 it takes about 2 hours to run...if we run the exact same query on 2000 it takes 9 seconds.
so this query on 2005 if run takes 2 hours..however, if we omit the s.IsInternalStatus = 0 or the i2.referenceid = @referenceid line it takes about 9 seconds.
why would this be? it makes no sense why omitting one of those where clauses would increase the performance of the query by 2 hours? we know its a bad query...but this doesnt make sense.
I have an SQLServer Mobile database, and I would like to know if there is a way to upgrade it to SQLServer 2005 (.mdf) database. My database has no records in it, just the structure (tables etc). What I am actually asking is if I can create automatically a new SQLServer 2005 Database with the same structure as my existin SQLSErver Mobile database
We are having some problems with the SQL Server 2005 Keep Alive mechanism causing dropped connections. The Keep Alive time and interval appear to work as documented. However the number of retries made before dropping the connection seems to be variable. When running Network Monitor I have seen this vary between 3 and 9 retries. The value for TcpMaxDataRetransmissions in the registry is set to 5. Does anyone know what the correct number of retries should be for SQL Server 2005 Keep Alive? Is this dynamically determined or is this a bug?
We moved a very tiny db (< 100 mb) from 2000 to 2005 (via attach/ detach) recently and now there are a couple sprocs which take forever to run from the app. Yes, the stats have since been updated as I reorg the indexes weekly. Now here is where it gets really strange, I can run this sproc in 0 seconds via Query Analyzer (QA). In other words I:
Have a developer run it from the app while monitoring in Profiler. There is a setting in the app for a 30 timeout, so after 30 seconds the app blows up and I see the exact code that the developer tried to run.
I can grab that code from Profiler, plop it into QA, and run it. Results are returned instantly. We have done this test a number of times, so I know it's not just a one time thing. How can this be? The only thing that got changed in the app was the connection string to the new 2005 server.
Thanks in advance. What is maximum SQL Server database (*.mdf) file size with SQL Server 2000 as part of Microsoft Small Business Server 2000? (Database files were limited to 10 GB in SBS 4.5 with SQLServer 7.0... has this changed?).
Hi, I wanted to see what are all the users in a windows nt group that has a group access to sql server 2000. I have a windows 2000 group access to sqlserver 2000 as "xxxsomegroup". How can I list all users that belongs to this windows 200 group? is there any stored procedure to find out this? any information could be greatly appreciated.
