I had a one procedure in MS SQL 2000 which used to update the n number of records.
Recently we have upgraded App Server version and in DB we have upgraded to MS SQL 2005- SP1. Now same procedure is present in MS SQL 2005. And users are compaining that they are finding very less count of updated transaction.
For eg
Transaction count in MS SQL 2000 - 1400
Transaction count in MS SQL 2005 SP1 - 800 [Using Compatibility 80]
What will be the reason ? we have checked the procedure and both are same.
Please help me i wanna to come out this situation
Thanks in Advance
I'm using MS SQL Server Management Studio. How do copy my store procedures that are on the SQL Server 2000 to SQL Server 2005? What are my options? I don't have admin rights...only rights to my database.
I am writing a sales managing software using C# in .NET 2005. The program was used to work properly with sql server 2000. I decided to write a "FILE Version" of sofware using sql server express 2005 , and detached db from 2000 , attaching it to 2005 using AttachDBFileName clause in connection string.
But the problem is when program executes something like this: EXEC [AddOrder] ... two rows inserted instead of one.and when I use server explorer of VS2005 to launch SP, it works fine. I should mention again that same code works correctly when I change connection string and force it to use sqlserver 2000.
I have a rather complex sp that runs for 4 minutes in SQL2000. It computes some messy oil and gas revenue/cost transactions. It involves lots of calls to functions that return single values of all types and also returns recordsets. There are all kinds of joins with temp and memory tables, etc. Just a mess, but it works. However, in SQL2005, it runs and apparently hangs, as it never ends.
I have run the Upgrade Advisor and otherwise, have not found any information that tells me that there are issues with functions or SQL-specific functions, tables, etc. that might cause this. Does anyone on this forum have some pointers on where I might look for assistance on this matter? Surely someone knows something about things working differently in 2005.
We would like to install Sql 2005 Enterprise Edition (including database engine, reporting service, integration service and analysis service) as a sepearte instance on a server which already has Sql 2000 with reporting services and analysis services. We do not want to disturb the existing sql 2000 setup.
If we do that then what will happen to my earlier sql 2000 reporting service? Will it be upgraded to sql 2005 reporting service? I heard that reporting services are instance unaware application. Where will be the default reporting service database available?
I was wondering if someone can point out the error or the thing I shouldn't be doing in a stored procedure on SQL Server 2005. I want to switch from SQL Server 2000 to SQL Server 2005 which all seems to work just fine, but one stored procedure is causing me headache.
I could pin the problem down to this query:
DECLARE @Package_ID bigint
DECLARE @Email varchar(80)
DECLARE @Customer_ID bigint
DECLARE @Payment_Type tinyint
DECLARE @Payment_Status tinyint
DECLARE @Booking_Type tinyint
SELECT @Package_ID = NULL
SELECT @Email = NULL
SELECT @Customer_ID = NULL
SELECT @Payment_Type = NULL
SELECT @Payment_Status = NULL
SELECT @Booking_Type = NULL
CREATE TABLE #TempTable(
PACKAGE_ID bigint,
PRIMARY KEY (PACKAGE_ID))
INSERT INTO
#TempTable
SELECT
PACKAGE.PACKAGE_ID
FROM
PACKAGE (nolock) LEFT JOIN BOOKING ON PACKAGE.PACKAGE_ID = BOOKING.PACKAGE_ID
LEFT JOIN CUSTOMER (nolock) ON PACKAGE.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID
LEFT JOIN ADDRESS_LINK (nolock) ON ADDRESS_LINK.SOURCE_TYPE = 1 AND ADDRESS_LINK.SOURCE_ID = CUSTOMER.CUSTOMER_ID
LEFT JOIN ADDRESS (nolock) ON ADDRESS_LINK.ADDRESS_ID = ADDRESS.ADDRESS_ID
AND PACKAGE.CUSTOMER_ID = ISNULL(@Customer_ID,PACKAGE.CUSTOMER_ID)
AND PACKAGE.PAYMENT_TYPE = ISNULL(@Payment_Type,PACKAGE.PAYMENT_TYPE)
AND PACKAGE.PAYMENT_STATUS = ISNULL(@Payment_Status,PACKAGE.PAYMENT_STATUS)
AND BOOKING.BOOKING_TYPE = ISNULL(@Booking_Type,BOOKING.BOOKING_TYPE)
-- If this line below is included the request will take about 90 seconds whereas it takes 1 second if it is outcommented
--AND ADDRESS.EMAIl LIKE '%' + ISNULL(@Email, ADDRESS.EMAIL) + '%'
GROUP BY
PACKAGE.PACKAGE_ID
DROP TABLE #TempTable
The request is performing quite well on the SQL Server 2000 but on the SQL Server 2005 it takes much longer. I already installed the SP2 x64, I'm running the SQL Server 2005 on a x64 environment. As I stated in the comment in the query it takes 90 seconds to finish with the line included, but if I exclude the line it takes 1 second. I think there must be something wrong with the join's or something else which has maybe changed in SQL Server 2005. All the tables joined have a primary key. Maybe you folks can spot the error / mistake / wrong type of doing things easily. I would appreciate any help you can offer me to solve this problem.
On the web I saw that there is a Cumulative Update 4 for the SP2 which fixes the following:
942659 (http://support.microsoft.com/kb/942659/) FIX: The query performance is slower when you run the query in SQL Server 2005 than when you run the query in SQL Server 2000
Anyhow I think the problem is something else, I haven't tried out the cumulative update yet, as I think it is something different, more general why this query takes ages to process.
I had few stored procedures which were working in SQL server 2000. I upgraded SQL server to 2005 and one stored procedure does not work. It gives the Error Msg 102. "Syntax error near ',' " I already tried set quoted identifiers ON & OFFAny help would be appriciated.
I am attempting to move some SQL 2000 databases to SQL 2005. My main production database does not seem to want to move. When I use the SQL 2005 GUI the .bak backup file is marked 'Incomplete'. When I attempt to restore the backup file I get a 'RESTORE detected an error on page (0:0) in database' message. I saw a thread in the SQL Express forum suggesting trying to restore from the T-SQL level to get the GUI out of the picture and I get the same 'error on page (0:0)' message. However when I take the same file and use SQL 2000 Enterprise Manager it restores with no problems.
Hi, I am trying to edit some data from a SQL2000-datasource in ASP.NET 2.0 and have a problem with a column that has bit-data and is used for selection. SQL2005 works fine when declaring <SelectParameters> <asp:Parameter DefaultValue="TRUE" Name="APL" Type="boolean" /> </SelectParameters>When running this code with SQL2000, there are no error-msgs, but after editing a record the "APL"-column looses its value of 1 and is set to 0. Looks like an issue with type-conversion, we've hit incompatibilities between SQL200 and 2005 with bit/boolean several times before. So, how is this done correctly with SQL2000? (I've tried setting the Type to "int16" -> err. Also setting Defval="1" gave an err) ThanksMichael
Hi,i have SQL 2000 and 2005 on same machine(with different intance names,of course), my laptop - XP with SP2. The 2005 works fine but i can'tconnect on SQL 2000. All the the SQL services are started.Any idea? Have i to reinstall 2000?Tks,Lourival
I have to merge the data from two databases, one is in SQL Server 2005 format, one is in 2000. The merged data will then reside on a SQL Server 2000 platform. Is there an easy way to do this through Management Studio or Enterprise Manager? Or will we have to export the data from the 2005 database to a flat file and import it into a new 2000 database. And then do the merge?
I am in the process of migrating from Sql Server 2000 to 2005. Part of my plan is to move some database's to 2005, but use the 2000 compatibility mode for the short term. My issue is this, our DR boxes are still on SQL Server 2000, would I still be able to use our log shipping processes? Or would I be better off in starting with migrating the DR boxes to 2005 first?
I have several SQL 2000 servers I need to setup transactional (non updatable) replication with. The structure is:
SQL Server 2000 as Publisher/Distributor
SQL Server 2005 Standard as Subscriber
The connection is via the Internet with snapshots using FTP.
I setup the first set (2 databases at location A). They work wonderfully. I created the publication and then subscribed using MGMT Studio for 2K5.
II am setting up the same scenario for location B. Here is my problem:
In MGMT Studio I connect to the publisher (SANDRA). I right-click a publication and choose New Subscriptions..., the publication is already selected. I click next - Run each agent at its Subscriber is selected and the only option (this is desired), I click Next
HERE IS THE PROBLEM:
On the Subscriber's screen there are no Subscribers listed. When setting up location A the subscribing server was listed and I could choose a database. The Next button is greyed out and there is no way to create/add one.
I tried setting up the subscription by right-clicking the subcribing server's Replication folder in MGMT Studio but I get the same result (except that I have to authenticate with the publishing server which works fine).
WHAT'S DIFFERENT:
Location A is SQL Server Standard (SP3) running on SBS2K3. It is obviously on a domain and so SQL Server and the SQL Agent are running under domain accounts. Location B is a Windows XP SP2 machine running SQL Server Personal Edition (it actually says Development Edition in the properties window).
The databases are the same strucutre, different data. At location A the firewall is set to allow 1433->*any* and *any*->1433 where *any* is 1024 or higher. On the XP machine the firewall is set to allow port 1433. I don't think this is the issue because I've turned the firewall off on the XP machine and I get the same result.
Most of our sql servers ar still sql 2000. Our programmers created many sql 2000 database diagrams using EM. But they can not access them under sql 2005. (They now have only sql 2005 tools installed on their boxes.)
Question: can we reinstall the sql 2000 client tools on their boxes without affecting the current sql 2005 install on their boxes?
Question: is there any workaround negating the need to ihstall the sql 2000 client?
I'm trying to connect to Desktop SQL Server 2000 from Windows mobile PC Emulator (VS 2005). I need a direct connection using connection string to SQL Server 2000 through local wireless network without IIS.
Bellow is the code that I use. After executing this code I get an error on line Conn.Open(). Error says SQL Server does not exist or access denied. SQL is un and running, and I can log in using SA username from the desktop. Even if I chance IP for another SQL server in my connection string I still get the same error. There is no firewall of any kind running.
Dim connectionSTR As String = "Persist Security Info=False;Integrated Security=False;Server=192.168.0.202,1433;initial catalog=MyDB;user id=sa;password=;"
Dim Conn As SqlConnection
Conn = New SqlConnection(connectionSTR)
Conn.Open()
If Conn.State = ConnectionState.Open Then MessageBox.Show("Open") End If
About my environment: SQL Server 2000 is running on Desktop PC with Windows XP SP2. Application which I need to connect to SQL Server is in Visual Studio 2005. I execute the application in Windows Mobile PC Emulator and try to connect to SQL Server from emulator.
I would like to ask you if there is better to recreate database structure on 2005 from 2000 and move data or to just load 2000 backup.
Currently I loaded the backup, but I am wondering if there might be slightly better performance on 2005 when recreating structure on 2005 to loading 2000 backup?
Does loading 2000 backup create 2005 binary structure?
How do SQL 2000 service packs play a role in upgrading? That is, can SQL 2000 Standard with no Service Packs(SP) be upgraded to SQL 2005 Standard, or does SQL 2000 Standard have to have a certain service pack??
If we changed the sort order from BIN to BIN2 but kept everything else the same will it have any effect on replication? So in SQL 2005 if I were to change my default collation from Latin1_General_Bin to Latin1_General_Bin2, would that cause replication to break? I suspect that it will not be an issue since it is just sort order that is changing and the code page stays the same.
BTW, this is transactional replication. Sorry, I left that out of my original post.
I have a problem regarding forwarding 'n number of parameters' from Visual Studio 2005 using VB to SQL-Server 2005 stored procedure.I have to save N number of rows in my stored procedure as a transaction. If all rows are not saved successfully, I have to roll-back else update some other table also after that. I am unable to handle - How to send variable number of parameters from Visual Stduio to Sql - Server ? My requirement is to use the SQL-Stored Procedure to store all the rows in the base table and related tables and then update one another table based on the updations done. Please Help .....
Hi, I want to get data from Oracle 10g Stored procedure to Reporting Services 2005. I could pass a SQL text and get a record set, but I want to execute a store proc and get the record set.
1. Add New Data Source 2. Choose Type : Oracle and connection tested OK 3. { call Test_Package.Test_Procedure(?) } is it wrong... how to write??? There is an error in the query. ORA-00911: invalid character
Also make sure that you use the text-based generic query designer (2 panes !) instead of the visual query designer (4 panes) - you can switch between them through an icon on the toolbar in the data view of report designer. Question : I tried many methods but unable to solve it...
create or replace PACKAGE Test_Package AS TYPE Test_Type IS REF CURSOR RETURN Test_Table%ROWTYPE; END Test_Package;
create or replace PROCEDURE Test_Procedure ( Test_Cursor IN OUT Test_Package.Test_Type, Test_Parameter IN Test_Table.ID%TYPE ) AS BEGIN OPEN Test_Cursor FOR SELECT * FROM Test_Table WHERE Test_Table.ID >= Test_Parameter; END Test_Procedure;
The below site gave some example but i could not solve it... any suggestions greatly appreciated...
I executed them and got the following results in SSMSE: TopSixAnalytes Unit AnalyteName 1 222.10 ug/Kg Acetone 2 220.30 ug/Kg Acetone 3 211.90 ug/Kg Acetone 4 140.30 ug/L Acetone 5 120.70 ug/L Acetone 6 90.70 ug/L Acetone ///////////////////////////////////////////////////////////////////////////////////////////// Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming: //////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class ///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors: Error #1: Type 'SqlConnection' is not defined (in Form1.vb) Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb) Error #3: Array bounds cannot appear in type specifiers (in Form1.vb) Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance, Scott Chang
More Information for you to know: I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly. I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
HI,ALL: I'm new to store procedure in sql 2000,now I create a sp to generate Balance report data , but when I run my sp in sql 2000 Query analyzer,I can get the data and some of data is null.
Now I want to replace null with '0' in my sp result data, is it possible to do it in store procedure in design model. ? how to get the soluation ?
Hi ALl I need a quick solution for my sql server problem as follows.
Actually Earlier we have SQL Server 7, Every 3 hours we execute one Procedure it takes only 15 seconds. Now we Install SQL Server 2000 and Same Procedure Is taking 30 Minutes for execution what could be the reason I want to minimize the execution time. how can i ?
Hi,Sorry, this is a very easy question. I have to create a storedprocedure in a SQL SERVER Database.I have read lots on MSDN about the syntax of this stored procedure :ie.CREATE PROCEDURE spGetContctDetailsAS-- get everything out of contact tableSELECT * FROM tblContactbut I cannot find anywhere how to actually add this procedure, so thatit is accessable in my VB.NET program.What I am asking is : what screen do I type this into? Is it QueryAnalyzer, if so where?Its got me oh so confused. When I've got time i'll read all about SQLServer, but for this morning I need to know how to simply add thatStored Procedure to my database.Thanks LOADS for any help.
I have a MSSQL2000 box with a large database containing circa 150 SP's.I want to move the DB to another SQL machine, but many of the SP's havereferences to the name of the current machine. Is there any way tobatch edit the procedures to refer to the new server, disassociatingthe old machine completely?Tia,Graham
I am trying to get the DTS Execute SQL task to run a simple sql server 2000no-parameter stored procedure.The procedure runs without error when using SQL Analyzer but DTS Execute SQLreports "could not find stored procedure "[dbo].[test]"The stored procedure is as follows:create procedure test asdeclare tnames_cursor CURSORforselect database_name, tmp_table_name from[DW_Dimensions].[dbo].[vw_dimension_temporary_tables_active]open tnames_cursordeclare @tablename sysnamedeclare @databasename sysnamewhile (@@FETCH_STATUS <> -1)beginselect @databasename = rtrim(@databasename)select @tablename = rtrim(@tablename)exec ("delete from " + @databasename + ".dbo." + @tablename)fetch next from tnames_cursor into @databasename, @tablenameendclose tnames_cursordeallocate tnames_cursorGOAny ideas?
I have a stored procedure that builds a sql statement and executes it. When run in Query Analyzer it runs successfully and displays records as it should. When I open a recordset with the stored procedure from VBA it executes the codes but doesn't open the recordset.
The store procedure is: ALTER PROCEDURE dbo.spTest AS DECLARE @sColumns varchar(2000) DECLARE @sFrom varchar(2000) DECLARE @sWhere varchar(2000) DECLARE @sSqlString AS nvarchar(2000) SET @sColumns = 'dbo.tblDoctor.lastname ' SET @sFrom = 'FROM dbo.tbldoctor ' SET @sWhere = 'WHERE dbo.tbldoctor.doctorcode is not null ' SET @sSqlString = 'SELECT ' + @scolumns + ' ' + @sFrom + ' ' + @swhere PRINT '@scolumns ' + @scolumns
The VBA code is: Private Sub Command0_Click() Dim cn As Connection Dim rs As New ADODB.Recordset Dim strsql As String Set cn = CurrentProject.Connection strsql = "exec spTest" With rs
.Source = strsql .ActiveConnection = cn .CursorType = adOpenKeyset .LockType = adLockReadOnly .Open End With Debug.Print rs.RecordCount & " records found" End Sub When it hits the line with rs.recordcount it displays the following error: "Operation is not allowed when object is closed" referring to the rs recordset never being opened.
I'm using Java to connect to a SQL Server 2000 database. I connect using the Driver Manager with Sun's odbc driver ( sun.jdbc.odbc.JdbcOdbcDriver ) or I can use the jdbc driver provided by Microsoft (com.microsoft.jdbc.sqlserver.SQLServerDriver)
The Java application makes 1 Connection.
Within the database there exists a stored procedure that updates 2 Tables. The tables have a fixed number of rows that get updated continuously by calls to this stored procedure.
The Java application has a thread pool of 15 threads that create 15 CallableStatements (1 per thread) using the same instance of the Connection object.
According the the Microsoft JDBC driver docs, 1 Connection with multiple calls to the Callable statements is how it's supposed to be done. The following is an excerpt from Microsoft's "SQL Server 2000 Driver for JDBC User’s Guide and Reference" (page 86) regarding Connection Managment: Managing Connections
Connection management is important to application performance. Optimize your application by connecting once and using multiple statement objects, instead of performing multiple connections. Avoid connecting to a data source after establishing an initial connection. This is precisely what I'm doing, but I do not know if the the stored procedures are being run concurrently, the documentation does not tell me.
So my question: What is happening inside SQL Server 2000?
