Replication Between SQL2000 And SQL2005
I have a distributor setup on SQL Server 2005 (9.0.3042) and am trying to create a publication on SQL Server 2000 (8.0.2040) which receives the following error in my production environment:
Msg 8526, Level 16, State 2, Procedure sp_addpublication, Line 802
Cannot go remote while the session is enlisted in a distributed transaction that has an active savepoint.
The interesting part of this equation is that I was able to get this to work without error in my DEV (development) environment and well as my QAT (test) environment. This end result was that my distributor was SQL 2005, my publisher was SQL 2000 and my pull subscriber was SQL 2005. I have been diligently comparing our production environment to my other environment and have yet to find differences.
Has anyone else seen an error similiar to this? Any insight would be appreciated.
Thanks in advance,
Max
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Replication From SQL2000 To SQL2005
I want to replicate from SQL2000 to SQL2005. I had configured distributor, publisher in SQL2000 and also subscriber in SQL2005. But so far no replication was done on SQL2005. I wonder SQL2000 (SP2) and SQL2005 replication will it work? Is it true that I should install Service Pack 3 and above in order to replicate my database to SQL2005. Thanks Wint
View Replies !
View Related
Upgraded SQL2000 To SQL2005, SQL2005 VERY Slow
I just upgraded my SQL 2000 server to SQL2005. I forked out all that money, and now it takes 4~5 seconds for a webpage to load. You can see for yourself. It's pathetic. When I ran SQL2000, i was getting instant results on any webpage. I can't find any tool to optimize the tables or databases. And when I used caused SQL Server to use 100% cpu and 500+MB of ram. I can't have this.Can anyone give me some tips as to why SQL 2005 is so slow?
View Replies !
View Related
Possible In SQL2000 - Not In SQL2005 :(
Hello all I have an odd problem with sql 2005. I'm a long time sql 2000 user trying to migrate to 2005. My company uses CSVs to import data from our clients db into our SQL db. In 2000 I just create a txt source and pick whichever CSV and transform to a temp sql table. This has always worked just fine with no problems. So we have a new server with sql 2005 installed. I go through the "migrate dts 2000" wizard and pull in all my DTSs from sql 2000. The first thing I see that needs to be fixed is "connection 1" which is my CSV connection manager along with the flat file source. I went through both the connection and the flat file source and tried to copy exactly what I do in sql 2000. When I preview the data or even execute this package I get bad data. Here is what my CSV looks like: "ACCT ","ACCOUNT DESCRIPTION ","ACCT TYPE","SCH NO","SC" "9999Z","Balance By Source Offset",9 "87B","UTILITIES(NOT PHONE)U-C",7 72,"ADM-LEGAL/AUDIT/COLL EXP",7 315,"SALES TAX - VEHICLES LOCAL 1",3 "90A","INTEREST(NON FPLAN/MORT)/N-C",7 73,"MEALS & ENTERTAINMENT-ALL",7 "210A","REBATES RECEIVABLE",2,17,4 "5A@","# OF EXT WARR SALES-UC",10 "51N","N/C-VEHICLE INV MAINTENANCE",7 "87D","UTILITIES(NOT PHONE)SVC",7 "90B","INTEREST(NON FPLAN/MORT)U-C",7 317,"ACCRUED OTHER",3,75,4 This is what it looks like when I preview it inside the flat file connection manager: ACCT ACCT DESC ACCT TYPE SCH NO SCTYPE 9999z blance by source... 9– – "87B" UTILITIES(NO... 7 So it is basically putting two rows of csv data into one row of SQL data. Now I realize my CSV isn't in the correct format because it doesn't have the extra commas at the end of a row IF the columns are null. It seems that 2005 doesn't recognize the carriage return/line feed. It puts the actual ascii characters in the preview. Once I execute the package it changes the ascii characters into blank spaces. My big issue is, why did this work just fine in 2000 but I can't get clean data in 2005? I'm completely clueless now, I have no idea where to go with this. Its impossible to change the output of the CSVs I get. There is literally no other options. I kinda get what I get with them. Why is this happening?
View Replies !
View Related
SQL2005 SP2 And SQL2000
I have just loaded SQL2005(server only)on my production box. This box also is running SQL2000. When I install the SQL2005 SP2 it says that if services are locked they will cause a reboot. I stop all the SQL 2005 services but it also want to stop SQL2000 Server for "backward compatiblilty". I thought installing a separate instance of SQL2005 would not have any effect on my SQL2000 instance. Thanks for your help R/P
View Replies !
View Related
Possible Between SQL2005 And SQL2000?
I have two SQL servers setup; A - SQL 2005, and B - SQL 2000. I would like to create an account on Server A which has access to the results from one view via a linked server on Server B. I don't want the user on Server A to be able to access any databases, tables or even columns on Server B with the exception of those contained within this view. Is this possible, and how would I go about doing it? (Permission-wise)
View Replies !
View Related
Using SQLNCLI With SQL2005 And SQL2000
Is it true that I cannot start a distributed transaction between a SQL Server 2005 instance (which starts the transaction) with an SQL Server 2000 instance with the SQL Native Client? That is, I have made a .NET 2 application that calls an stored procedure on a local server within a transaction, and in turn that server is supposed to update rows on a remote server. The aplication uses the sqlclient component. If it is not possible to use the SQL Native Client for a distributed transaction on a mixed server scenario as this, is there a workaround? Thanks a lot.
View Replies !
View Related
SQL2000 To SQL2005 MSTDC
I have a scenario in which i must run a distributed (transaction) query or a cursor, which updates tables on a SQL2005 from a SQL2000 using linked server on the SQL2000. For some reason, the query hangs, and the only way to stop/release it is to restart the MSDTC on the SQL2000. Clues anyone? Thanks, Bjarup
View Replies !
View Related
Install SQL2000 AFTER SQL2005
Hi, I need to install SQL2000 onto a machine (W2K3 R2, SP2) that already has SQL2005, SP1 running. I can't disturb the SQL2005 installation. Are there any problems or pitfalls to avoid? I've googled and looked on Technet but not yet found an answer. Has anyone done this? I know that if I install a separate instance of SQL2000 alongside a default installation of SQL2000 then I'll get a totally separate installation which won't affect the other one at all, so I reckon it could be done. But I have to be sure before I start! Thanks in advance, Steve
View Replies !
View Related
Restore Sql2005 Db In Sql2000
Hello, Restoring a DB from sql 2005 (which is located on my local computer) into sql 2000 (which is located on the server) and I am getting this type of message: TITLE: Microsoft SQL Server Enterprise Edition ------------------------------ An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ ADDITIONAL INFORMATION: Too many backup devices specified for backup or restore; only 64 are allowed. RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3205)
View Replies !
View Related
Why SQL2005 Is NOT Faster Than SQL2000?
Hello!! we use SQL2000(64bit) and we are considering our system to apply SQL2005 so i tested two SQL-Server on One Machine by installing each instance of them. The RESULT.. i can't find difference of performance between two instance. is there no impoving in Database engine? * i'm sorry, for my poor sentence...
View Replies !
View Related
Upgrade From SQL2000 To SQL2005
Hi, I'm running a VB6 app with MDAC 2.8 on Windows Server 2003 SP1. I create an ADO recordset with a SELECT statement which returns an empty recordset. I then add 2 new records to it with AddNew and later perform an UpdateBatch. Before the SQL upgrade it worked fine but now errors with: 'Cannot convert a char value to money. The char value has incorrect syntax.' I did a SQL Trace to find out what the Updatebatch was actualling sending to SQL (listed below). This works fine when I run it on Query Analyser using SQL 2000 but fails on SQL 2005. I worked out the problem is linked to the columns NewValue and OldValue both are varchar 70. The first insert has them defined varchar(7) and varchar(1). This insert succeeds, but the second insert has the columns defined as varchar(18) and varchar(12). This one fails (unless they only contains numbers!). I'm confused. I've read that sp_executesql has changed for SQL2005. Any ideas anyone? Thanks Howard Code Snippet exec sp_executesql N'INSERT INTO "pit1_lamda".."Planalt" ("ElNo","Item","MvmtDate","NewValue","OldValue","PlanAltId","PlanNo","Timestamp","User_Id","RBNo","CreatorId","CreatorTypeCode","CreatedDate","ExitNo","PBTxnTriggerId","PremHistNo","RegContractPremium","WaiverPrem","SinglePremTot","PlanMvtId","SingMvtID","PLFAID","TrancheId") VALUES (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13,@P14,@P15,@P16,@P17,@P18,@P19,@P20,@P21,@P22,@P23); INSERT INTO "pit1_lamda".."Planalt" ("ElNo","Item","MvmtDate","NewValue","OldValue","PlanAltId","PlanNo","Timestamp","User_Id","RBNo","CreatorId","CreatorTypeCode","CreatedDate","ExitNo","PBTxnTriggerId","PremHistNo","RegContractPremium","WaiverPrem","SinglePremTot","PlanMvtId","SingMvtID","PLFAID","TrancheId") VALUES (@P24,@P25,@P26,@P27,@P28,@P29,@P30,@P31,@P32,@P33,@P34,@P35,@P36,@P37,@P38,@P39,@P40,@P41,@P42,@P43,@P44,@P45,@P46)',N' @P1 varchar(6),@P2 int,@P3 datetime,@P4 varchar(7),@P5 varchar(1),@P6 int,@P7 varchar(12),@P8 int,@P9 varchar(8),@P10 varchar(4),@P11 varchar(12),@P12 tinyint,@P13 datetime,@P14 int,@P15 int,@P16 int,@P17 money,@P18 money,@P19 money,@P20 int,@P21 int,@P22 int,@P23 int, @P24 varchar(6),@P25 int,@P26 datetime,@P27 varchar(18),@P28 varchar(12),@P29 int,@P30 varchar(12),@P31 int,@P32 varchar(8),@P33 varchar(4),@P34 varchar(12),@P35 tinyint,@P36 datetime,@P37 int,@P38 int,@P39 int,@P40 money,@P41 money,@P42 money,@P43 int,@P44 int,@P45 int,@P46 int', '000000',80,'01-01-2008','1463286','0',27425002,'043902305X2 ',57153,'mtstdat4','0000', 'mtstdat4 ',1,'03-05-2008',0,0,0,$0.0000,$0.0000,$0.0000,4250207,0,0,3001752, '000000',11,'03-02-2008','10 In Force (V011)','3 AwaitIssue',27425003,'043902305X2 ',57155,'mtstdat4','0000', 'mtstdat4 ',1,'03-05-2008',0,0,0,$0.0000,$0.0000,$0.0000,4250207,0,0,3001752
View Replies !
View Related
SQL2000 Job Calling SQL2005 Job
Hi, We have two systems, one working on SQL2000 and other working on SQL2005. In SQL2000, there is JOB which needs to execute a JOB in SQL2005, get feedback based (success or failure) and proceed accordingly. I am not sure the best practice to call SQL2005 job from SQL2000 job. Both the server's run undersame domain account. please help, thanks atul
View Replies !
View Related
SQL2000 TO AND FROM SQL2005 TRANSFERING
I am a IT Manager.I have question: There is a database was created on SQL Server. I want to learn if I restore the database to SQL 2005 then I want to get backup then I want to restore to SQL 2000. shortly: SQL 2000 sever-> SQL 2005->SQL 2000 SERVER Can I do that? If I do that is there someting missed? http://www.narcv.com/
View Replies !
View Related
Sql2000 And Sql2005 On The Same Machine
I have installed MSDE2000 (name: mypcsql2000, port 1433) and sql2005 Express (mypcsql2005, port 1434) on my WinXP. I'm developing some application and I need to test both server. It has worked fine about 1 year, but last month i got error message. When i'm trying to connect to SQL2005 from my application or import data from Excel, i get error message: [DBNETLIB][ConnectionOpen (Invalid Connection()).]Invalid connection. I can access SQL2005 with Management Studio or cmd line (sqlcmd). But not with Excel (or with my application). MSDE2000 works fine, i haven't any problems. Last weekend I have reinstalled my WinXP and also MSDE2000, SQL2005 Express. Worked fine, but when turn on automatic updates ON and afterwards installed latest updates, the problem is here again... Any idea to solve this problem? (sorry for my english...)
View Replies !
View Related
Upgrading From Sql2000 32 Bit To Sql2005 64 Bit
Hi, Is there any difficulties I should be aware of? I'm not running DTS and I think my DB is relatively simple. I've googled and not found anything that sticks out as a problem. We are getting a new server because we want more RAM, and better upgrade options. The hosting company recommended 64bit SQL, so I am thinking of following their recommendations. I am upgrading from a 2 x p4 2.8ghz xeons to 1 x Intel 5130 dual core 64 bit chip. I also will be going from 2 gigs to 4 gigs of RAM. Any thoughts I would love to hear. Thanks!
View Replies !
View Related
Linked Server From SQL2005 To SQL2000
Linked server from SQL2005 to SQL2000 Hi I use the following SQL statements to create a link server. RemoteServerName is an SQL2000 and I am executing this in another machine which is SQL2005. Though the link server is created successfully, I am not able to se the tables under it so could not query anything. ---- USE [master] EXEC master.dbo.sp_addlinkedserver @server = N'RemoteServerNameInstanceName', @srvproduct=N'SQL Server' EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'collation compatible', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'data access', @optvalue=N'true' EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'rpc',@optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'rpc out', @optvalue=N'false' EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'connect timeout', @optvalue=N'0' EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'collation name', @optvalue=null EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'query timeout', @optvalue=N'0' EXEC master.dbo.sp_serveroption @server=N'RemoteServerNameInstanceName', @optname=N'use remote collation', @optvalue=N'true' EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'RemoteServerNameInstanceName', @locallogin = NULL , @useself = N'False', @rmtuser = N'remoteUser', @rmtpassword = N'remotePassword'
View Replies !
View Related
Parameter Query Asp.net 2.0 With Sql2000 Vs Sql2005
Hello, I am currently using vs2005 with sql2000 and have a parameter query getting records form my sql2000 db. 'Load existing data to textboxes for editingDim strSQL1 As String = "SELECT id, LogDate, LogTime, LogDD, StatDD, LogEvent FROM DEMO_DailyOccurrence WHERE completed = 0 AND RecordIdentify = @strOutdate ORDER BY id ASC "Dim scnnNW1 As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("SQLconnection"))Dim scmd1 As New SqlCommand(strSQL1, scnnNW1)With scmd1.Parameters.Add(New SqlParameter("@strOutdate", SqlDbType.DateTime)).Value = strOutdate1End WithDim sda1 As New SqlDataAdapter(scmd1)Dim ds1 As New Data.DataSetTrysda1.Fill(ds1)Catch ex As ExceptionEnd Try strOutdate1 is a date and has been tested and proven to be OK. When I run the same code against my sql2005 db, the query returns nothing. Do I have to handle something differently for sql2005. Thanks Peter
View Replies !
View Related
Accessing SQL2005 As A Second Instance To SQL2000
I'm having trouble accessing SQL2005 Standard Edition as a secondinstance of SQL Server where the first instance is SQL 2000 EnterpriseEdition. I installed SQL 2005 as a named instance "SQL2005". Theserver is running Windows 2000 SP4 ON A 32-Bit machine. When I look inServices I see the SQL Server (SQL2005), SQL Server Agent (SQL2005)services there. I went into SQL Server Configuration manager anddisabled the named pipes protocol and only have TCP/IP and by defaultShared Memory enabled to rule out that named pipes is causing theproblem. In SQL Server Configuration Manager I created an alias"computername$SQL2005" and was able to connect to database engine"computernameSQL2005" through SQL Server Management Studio WHILE ONTHAT BOX.But when trying to access the 2005 instance from another server(64-bit Windows 2003 SP1) I'm having trouble. On the 64-bit machine Idisabled the named pipes protocol for both "SQL Native ClientConfiguration (32-bit)" and "SQL Native Client Configuration". Under32-bit I created the alias "machinename$SQL2005" to point to the IPaddress of the 32-bit machine. In SQL Server Management Studio whentrying to connect to the database engine "machinename$SQL2005" itdefaults to the SQL 2000 instance. How do I access the SQL2005instance from a remote server?Any help would be appreciated.Mike
View Replies !
View Related
SQL2000 JOB Calling SQL2005 JOB - Best Practice?
Hi, We have two systems, one working on SQL2000 and other working on SQL2005. In SQL2000, there is JOB which needs to execute a JOB in SQL2005, get feedback based (success or failure) and proceed accordingly. I am not sure the best practice to call SQL2005 job from SQL2000 job. Both the server's run undersame domain account. please help, thanks atul
View Replies !
View Related
Distributed Transactions Between SQL2005 And SQL2000
Hi there, We have two servers, one (we'll call 'SERVERA') has SQL2005 running on it. The second (we'll call 'YELLOWSTEONE') is running both SQL2000 and SQL2005 on it. The SQL instances on YELLOWSTONE are 'YELLOWSTONESQL2000' and 'YELLOWSTONESQL2005'. As a linked server, I have an entry for YELLOWSTONE which then links to the SQL Server of YELLOWSTONESQL2000 on the server network name of YELLOWSTONE. By them selves they seem to run fine. However, if I have trigger that Runs on SERVERA to do a distributed transaction on 'YELLOWSTONESQL2000', I get the following error: OLE DB provider "SQLNCLI" for linked server "YELLOWSTONE" returned message "Login timeout expired". OLE DB provider "SQLNCLI" for linked server "YELLOWSTONE" returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.". Msg 2, Level 16, State 1, Line 0 Named Pipes Provider: Could not open a connection to SQL Server [2]. If You can provide me any assistance, I would greatly appreciate it. Thanks! - Eric -
View Replies !
View Related
Cast - Differences Between SQL2000 And SQL2005
I came across a problem when migrating from SQL2000 to SQL2005. Table1 - large table with multiple columns including columns (X21 varchar(50), Start datetime, Complete datetime) View1 - Only returns integer values into the dwpId column SELECT X21 AS dwpId, Start AS startDate, Complete AS endDate FROM Table1 WHERE (X21 IS NOT NULL) AND (X21 LIKE '[0-9]%') AND (ProjectID NOT LIKE '%_WI') View2 SELECT cast(dwpId as int) as dwpId, startDate, endDate FROM View1 In SQL2005, when selecting values from View2 an error is returned indicating that the statement failed when converting a varchar value to a data type int. In SQL2000, this same statement would return the appropriate rows from the view.
View Replies !
View Related
SQL2000 Distributer Move To SQL2005
Hi there, Need to plan moviing a SQL2000 distributor (which is also the publisher) to a SQL2005 distributor (64bit). Problem is this needs to be done without a snapshot as the databases are a couple of hundred Gb and there is about 15 subscribers (including remote sites on a slow link) What would be a recommended process for this, according to some searches it seems published DB goes to readonly/dbo mode, once log reader/distro agents have delivered all they are stopped. Replication is reconfigured on the pubication to point to the new SQL2005 boxes, and replication/subscriptios are readded/started again without snapshots. I read somewhere that there were problems with the SQL stored procs being called differently between 2000 and 2005 for the insert/del/update, something like the owner (dbo) becoming part of the procs being called in 2005 (ie sp_MSins_dbo etc instead of dbo.sp_MSins). Can someone confirm if this is the case? This will ofcourse be labbed before being moved, so just wanted to get some idea of what one is in for.
View Replies !
View Related
Returning A Databse From Sql2005 To Sql2000
Dear All, I had a database file running under sql 2000 perfectly, because of a crash in the server we provided the customer with a temp server 2003 with sql 2005 until we fix his server, the procedure we did: 1. Detach the database from sql2000. 2. Copy the database to the new server the (filename.mdf & filename.ldf) 3. Attached the database to sql 2005. 4. The application is working fine. Now we returned the original server with sql 2000 back to the customer, when we followed the same procedures above, meaning same steps but from sql2005 to sql2000 we got this error: [[Error 602: Could not find row in sysindexes for database ID 5, object ID 1, Index ID 1. Run DBCC CHECKTABLE on sysindexes]] We tried every thing export/import the database etc. nothing works. Any advice please, & thanks for the help.
View Replies !
View Related
Do We Get SQL2000 Media With A SQL2005 Purchase?
My company is planning to purchase a SQL2005 standard Processor license (unlimited users - about $5000) Does anyone know if we can obtain SQL2000 media along with the purchase. Because of the app we are running and its stage of development, we have to install and run SQL2000 for about 6 months before we can run Sql2005. We dont want to purchase SQL2000 for such a short term use. I asked a DELL rep to help me with this over a month ago and he still has no answers for me. Any help with this would be greatly appreciated.
View Replies !
View Related
Restoring Backup From SQL2000 To SQL2005
I am restoring a backup from SQL2000 Database to 2005 and I get this error : An exception occured while executing a Transact-SQL statement or batch (Microsoft.SqlServer.ConnectionInfo) Additional Information: Too many backup devices specified fro backup or restore; only 64 are allowed. RESTORE HEADEEONLY is terminationg abnormally. (Microsoft SQL Server, Error 3205) How do fix this? Please assist. Thanx
View Replies !
View Related
Link Server From SQL2005 To SQL2000
I have the follwoing scenario, where the query returns an error, when we tried to upgrade oru production SQL2000 enviroment to SQL2005. SQL2000 Env: Microsoft SQL Server Developer Edition Microsoft Windows NT 5.2 (3790) 8.00.2175 SP4 SQL2005 Env: Microsoft SQL Server Developer Edition Microsoft Windows NT 5.2 (3790) 9.00.3054.00 Notice the removal of the two RTRIMs. Any help welcome Gertus Current Connection was on the SQL2005 machine: Remote Link Server SQL2000 SP4 Working: SELECT * FROM Entity -- on SQL2005 WHERE (EntityRef) + Source NOT IN (SELECT (ENTITYID) + 'EU' FROM MRIEU.MRIOB.dbo.ENTITY) --this is on sql2000 AND Source = 'EU' Not-working: SELECT * FROM Entity-- on SQL2005 WHERE RTRIM(EntityRef) + Source NOT IN (SELECT RTRIM(ENTITYID) + 'EU' FROM MRIEU.MRIOB.dbo.ENTITY) AND Source = 'EU' Error :: >> OLE DB provider "SQLNCLI" for linked server "MRIEU" returned message "Cannot create new connection because in manual or distributed transaction mode.". Msg 7320, Level 16, State 2, Line 1 Cannot execute the query "SELECT TOP 1 1 FROM "MRIOB"."dbo"."ENTITY" "Tbl1005"" against OLE DB provider "SQLNCLI" for linked server "MRIEU".
View Replies !
View Related
Can SQL2000 && SQL2005 Co-exist On The One Server??
i realise you can have both SQL2000 & SQL2005 Client software co-existing on one (developement) machine. but i cant seem to find a definitive answer on whether i can install both complete databases on a server?? we are currently building a new development server and would like to install both version on it if possible?? Cheers, Craig
View Replies !
View Related
Moving Database From Sql2000 To Sql2005
I am planning to move the sql server 2000 database to the sql server 2005 database. I have to restore that by using the back up copy. Do I need to follow any special instructions or just like moving from one server to another. Could some body help with this. Thanks!
View Replies !
View Related
Inner Join Queries Between Sql2005 And Sql2000
We have the follwoing environment Sql2005 64 bit edition standard edition servers Sql2000 Sp3 enterprise edition servers when we try to access a table in sql2000 from sql2005 using linked server, the query also uses inner joins and max() it gives the follwoing error "Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. Msg 207, Level 16, State 3, Line 1 Invalid column name 'Col1017'." The query looks something like this select *FROM [X.X.X.X.].HRDE.dbo.PS_HX_LVE_FRM_SRCH A, [X.X.X.X.].HRDE.dbo.PS_NAMES B WHERE A.EMPLID = B.EMPLID AND A.HX_LEAVE_STATUS = 'PND' AND B.EFFDT IN (SELECT MAX(EFFDT) FROM [X.X.X.X.].HRDE.dbo.PS_NAMES WHERE EMPLID = A.EMPLID)
View Replies !
View Related
Diffrent Results In SQL2000 And SQL2005
Good day I'm having a problem where I get diffrent results between SQL2000 and 2005. I have have the following view: Code Block SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ALTER VIEW [dbo].[vUsrMsgInfo] AS SELECT dbo.vMsgInfo.Body, dbo.vMsgInfo.FinalBody, dbo.vMsgInfo.BodyType, dbo.vMsgInfo.MsgID, dbo.vMsgInfo.Status, dbo.vMsgInfo.Creator, dbo.vMsgInfo.Created, dbo.vMsgInfo.StatusTime, dbo.vMsgInfo.StatusReason, dbo.vMsgInfo.Via, dbo.vMsgInfo.RouteAt, dbo.vMsgInfo.Source, dbo.vMsgInfo.Priority, dbo.vMsgInfo.Subject, dbo.vMsgInfo.RouteRuleName, dbo.UsrMsgInfo.Usr, dbo.vMsgInfo.SequenceNo, dbo.vMsgInfo.SendEnv, dbo.vMsgInfo.EnvCount, dbo.vMsgInfo.OwnerOrg, dbo.vMsgInfo.OwnerPerson, dbo.vMsgInfo.ToWildAddr, dbo.vMsgInfo.BodySize, dbo.vMsgInfo.RoutedRoute, dbo.vMsgInfo.NoteBody, dbo.vMsgInfo.ToOrg, dbo.vMsgInfo.CreatorOrg, dbo.vMsgInfo.FaxMode FROM dbo.UsrMsgInfo RIGHT OUTER JOIN dbo.vMsgInfo ON dbo.UsrMsgInfo.Message = dbo.vMsgInfo.MsgID GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO I run the following query: Code Block Select * from vUsrMsgInfo where usr='cdk' In SQL2000 I get a resultset that includes nulls and cdk in the usr column. In SQL2005 however I only get results that has cdk in the usr column. Is there a way to replicate the 2000 results in 2005?? Thanks & Regards Corne.
View Replies !
View Related
SQL2000-SQL2005 Connection String
Hi all, I'm new to SQL and I am trying to move from SQL 2000 to SQL 2005 on another server. When I change my connection string from the old IP to the new one(with SQL 2005) my web app crashes. Here is the command string I need to change: <add key="ConnStr" value="data source=myoldIP;initial catalog=gt-test;uid=myuserid;pwd=mypassword;" /> I have copied the old database exactly the way it was to the new one. I need to change myoldIP to my new one but when I do it crashes... Any ideas? Thanks in advance for your help -Will
View Replies !
View Related
Install SQL2000 And SQL2005 In Production Environment?
Hi, Is it possible to deploy both SQL2000 and SQL2005 on the same server in the production environment? Is it recommended? Next year we will be deploying a SQL2005 solution and was wondering if we could get away with installing it on the same server as the SQL2000 server, or whether we should put it on another server. We have up to a maximum of 50 concurrent users. Thanks.
View Replies !
View Related
SQL2005 Outperforms SQL2000? Its Over 4 Times Slower...
I've found a very interesting case where SQL 2005 is more than 4 times slower than its predecessor, despite superior hardware. To compare, I have some logs from the ISA Server has stored into the database, which is then filtered against my blocklists. The result are the most popular sites I have not blocked or trusted. I have done some tuning on the query to utilize the data patterns that are in the database. This allows much better scaling towards large datasets. The engine is now capable to use hash-matches instead of nested loops over the entire datasets. It currently runs in 1 to 1.5 minutes on SQL 2000, which is powered by a VIA C3 Nehemiah at 1 GHz, with only 350 MB RAM too spare in optimistic conditions (The remainder of the 1 GB is used by many other applications). THe server is limited to 384 MB memory usage. The other box running SQL 2005 is a Athlon 800 MHz, which has superior processing power, larger cache and more memory bandwidth and has 1.25 GB RAM, where SQL server can use the needed 629 MB without any problem. Despite these facts, my query takes more than 4 times longer on the SQL 2005 box. 1.5 minutes compared to 7 to 8 minutes. SQL 2000 Database Schema: CREATE TABLE [dbo].[WebProxyLog]( [ClientIP] [bigint] NOT NULL, [ClientUserName] [nvarchar](514) NOT NULL, [ClientAgent] [varchar](128) NOT NULL, [ClientAuthenticate] [smallint] NOT NULL, [logTime] [datetime] NOT NULL, [service] [smallint] NOT NULL, [servername] [nvarchar](32) NOT NULL, [referredserver] [varchar](32) NOT NULL, [DestHost] [varchar](255) NOT NULL, [DestHostIP] [bigint] NOT NULL, [DestHostPort] [int] NOT NULL, [processingtime] [int] NOT NULL, [bytesrecvd] [bigint] NOT NULL, [bytessent] [bigint] NOT NULL, [protocol] [varchar](12) NOT NULL, [transport] [varchar](8) NOT NULL, [operation] [varchar](24) NOT NULL, [uri] [varchar](2048) NOT NULL, [mimetype] [varchar](32) NOT NULL, [objectsource] [smallint] NOT NULL, [resultcode] [int] NOT NULL, [CacheInfo] [int] NOT NULL, [rule] [nvarchar](128) NOT NULL, [FilterInfo] [nvarchar](128) NOT NULL, [SrcNetwork] [nvarchar](128) NOT NULL, [DstNetwork] [nvarchar](128) NOT NULL, [ErrorInfo] [int] NOT NULL, [Action] [varchar](32) NOT NULL, [GmtLogTime] [datetime] NOT NULL ) CREATE TABLE [dbo].[TrustedHosts]( [Hostname] [varchar](60) NOT NULL, [Comment] [varchar](500) NULL, CONSTRAINT [PK_TrustedHosts] PRIMARY KEY CLUSTERED ( [Hostname] ASC ) ON [PRIMARY] ) ON [PRIMARY] GO CREATE TABLE [dbo].[DeniedHosts]( [ReasonId] [smallint] NOT NULL, [Hostname] [varchar](80) NOT NULL, [Path] [varchar](50) NOT NULL CONSTRAINT [DF_DeniedHosts_Path] DEFAULT ('%'), [Comment] [varchar](500) NULL, CONSTRAINT [PK_DeniedHosts] PRIMARY KEY CLUSTERED ( [Hostname] ASC, [ReasonId] ASC, [Path] ASC ) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[DeniedHosts] WITH NOCHECK ADD CONSTRAINT [FK_DeniedHosts_DenyReason] FOREIGN KEY([ReasonId]) REFERENCES [dbo].[DenyReason] ([ReasonId]) GO ALTER TABLE [dbo].[DeniedHosts] CHECK CONSTRAINT [FK_DeniedHosts_DenyReason] GO ALTER TABLE [dbo].[DeniedHosts] WITH NOCHECK ADD CONSTRAINT [CK_DeniedHosts_HostName_NoTwoDots] CHECK ((((not([Hostname] like '%..%'))))) GO ALTER TABLE [dbo].[DeniedHosts] CHECK CONSTRAINT [CK_DeniedHosts_HostName_NoTwoDots] GO ALTER TABLE [dbo].[DeniedHosts] WITH NOCHECK ADD CONSTRAINT [CK_DeniedHosts_NoPercentDot] CHECK (([Hostname] <> '%.')) GO ALTER TABLE [dbo].[DeniedHosts] CHECK CONSTRAINT [CK_DeniedHosts_NoPercentDot] GO ALTER TABLE [dbo].[DeniedHosts] WITH NOCHECK ADD CONSTRAINT [CK_DeniedHosts_NoWildcardMiddle] CHECK ((((not([Hostname] like '_%[%]%'))))) GO ALTER TABLE [dbo].[DeniedHosts] CHECK CONSTRAINT [CK_DeniedHosts_NoWildcardMiddle] GO ALTER TABLE [dbo].[DeniedHosts] WITH NOCHECK ADD CONSTRAINT [CK_DeniedHosts_ValidPath] CHECK (([Path] is null or [Path] = '%' or [Path] like '/%' and [Path] <> '' and [Path] <> '/%')) GO ALTER TABLE [dbo].[DeniedHosts] CHECK CONSTRAINT [CK_DeniedHosts_ValidPath] GO ALTER TABLE [dbo].[DeniedHosts] WITH NOCHECK ADD CONSTRAINT [CK_DeniedHosts_WildcardStart] CHECK ((((not([Hostname] like '[%]%'))) or ([Hostname] like '[%].%' or [Hostname] = '%'))) GO ALTER TABLE [dbo].[DeniedHosts] CHECK CONSTRAINT [CK_DeniedHosts_WildcardStart] I'm not going to give you any data as: WebProxyLog contains 1 223 878 rows; 524 MB (few more on SQL 2000 database). DeniedHosts contains 52 338 rows; 3 MB TrustedHosts contains 2 183 rows; <1 MB The relevant query is: CREATE PROCEDURE [dbo].[GetTrustedHosts] AS SELECT Hosts, Requests --, DistinctRequests FROM(SELECT AA.Hosts, COUNT(*) AS Requests, COUNT(DISTINCT Path) AS DistinctRequests FROM(SELECTCASE WHEN CHARINDEX('':'', SUBSTRING(URI, 8, CHARINDEX(''/'', URI, 8)-8), 8) <> 0 THEN SUBSTRING(URI, 8, CHARINDEX('':'', URI, 8)-8) ELSE SUBSTRING(URI, 8, CHARINDEX(''/'', URI, 8)-8) END AS Hosts, SUBSTRING(URI, CHARINDEX(''/'', URI, 8), 50) AS Path FROM dbo.WebProxyLog wpl WHERE URI LIKE 'http://%/%' AND ResultCode BETWEEN 200 AND 399 AND (Service = 1) -- filter for only forward proxy ) AA WHERE NOT EXISTS (SELECT * FROM dbo.DeniedHosts dhp WHERE Path <> ''%'' AND LEFT(AA.Path,3) = LEFT(dhp.Path COLLATE SQL_Latin1_General_CP1_CI_AS, 3) AND AA.Hosts LIKE (dhp.Hostname COLLATE SQL_Latin1_General_CP1_CI_AS) AND ( AA.Path LIKE (dhp.Path COLLATE SQL_Latin1_General_CP1_CI_AS) OR AA.Path LIKE ((dhp.Path COLLATE SQL_Latin1_General_CP1_CI_AS) + ''[?]%'') ) ) AND NOT Hosts IS NULL-- this seems to give a speed advantage GROUP BY AA.Hosts HAVING COUNT(*) >= 25 ) A WHERE NOT Hosts IN (SELECT Hostname COLLATE SQL_Latin1_General_CP1_CI_AS FROM dbo.TrustedHosts thc --WHERE NOT thc.Hostname LIKE ''%[%]%'' ) AND NOT Hosts IN (SELECT Hostname COLLATE SQL_Latin1_General_CP1_CI_AS FROM dbo.DeniedHosts dhc WHERE dhc.Path = ''%'' ) AND NOT EXISTS (SELECT * FROM dbo.TrustedHosts thh WHERE A.Hosts LIKE (thh.Hostname COLLATE SQL_Latin1_General_CP1_CI_AS) AND thh.Hostname LIKE ''[%]%'' -- generates a hash join instead of a of nested loop AND RIGHT(A.Hosts,6) = (RIGHT(thh.Hostname,6) COLLATE SQL_Latin1_General_CP1_CI_AS) ) AND NOT EXISTS (SELECT * FROM dbo.DeniedHosts dhh WHERE dhh.Path = ''%'' -- this reduces the cost of the most expensive query AND dhh.Hostname LIKE ''[%]%'' AND A.Hosts LIKE (dhh.Hostname COLLATE SQL_Latin1_General_CP1_CI_AS) -- generates a hash join instead of a of nested loop -- performance difference is significant due to volume AND RIGHT(A.Hosts,6) = (RIGHT(dhh.Hostname,6) COLLATE SQL_Latin1_General_CP1_CI_AS) ) ORDER BY Requests DESC There is some mess with collations, but these don't seem to hurt performance. The query plans provided differ only slightly. SQL 2000 provides a Clustered Index Scan over WebProxyLog with predicate on resultcode. After this a filter for the LIKE operator. Cost is 78% for the scan and 10% for the filter. SQL 2005 combines both. With the scan costing 93%. However, the key does not seem to be in this data, as the plans are nearly equavent, with the SQL2005 executing plan looking slightly better (table scan is a larger part of the execution). How can these differences, espcially of this magnitude, be explained? And further, how can the query be optimized for decent performance on SQL2005? What am I doing wrong?
View Replies !
View Related
Schema Changes On Merge Publication On SQL2000 And SQL2005
Hello I've got the following problem: Our product is delivered with SQL2000 and SQL2005. Now, there are some schema changes, which I'd like to deploy with T-SQL on the publishers. With SQL2000 I do it with the sp_addmergecolumn etc and on SQL 2005 (if replication compatibility level is 90) with replication of DDL. So far so good. But how can my T-SQL Script determine, wether the replication of DDL is on? I know there exists sp_helpmergepublication, but how do I get the column replicate_ddl of the result-set? thanks for your advices. Aline
View Replies !
View Related
SQL2000 Publisher &&> SQL2005 Subscriber On Different Port
Hi,I'm trying to setup replication using SQL 2000 as the publisher and SQL 2005 as the subscriber.The problem I have is that the SQL 2000 instance is running on a non standard port 1083, so the name of the instance I'm connecting to is: TESTPC1SQL2000,1083SQL 2005 requires you to provide the "requires the actual server name" which would be "TESTPC1SQL2000" which unfortunately will not work as it's on a different port.The error message I'm getting when attempting to setup the subscription on SQL 2005 Management Studio is this:TITLE: Connect to Server------------------------------Cannot connect to TESTPC1SQL2000,1083.------------------------------ADDITIONAL INFORMATION:SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'TESTPC1SQL2000'. (Replication.Utilities)Does anyone know a workaround to this?Thanks,Andy
View Replies !
View Related
Problem Installing Either Sql2000 Or Sql2005 On Vista
i have a vista basic running on my lappy,i have beeen trying to install either sql2000 or sql2005 on it for the past one month i keep getting funny errors like installshield engine has stopped working.WHile on the sql2005 the installation will exclude the database mangament studio ,etc..I had disabled the norton antivirus running to no effect. I really need help here!
View Replies !
View Related
Unable To Tranfer Jobs From Sql2000 To Sql2005
Hi Im in the process of upgrading my sqlserver 2000 to 2005.I want to migrate all the jobs from sql2000 to 2005. I scripted the job (right click job--->all tasks--->generate sqlscript). I ran the same script on sql2005,i was getting an error like this "Msg 515, Level 16, State 2, Procedure sp_add_job, Line 137 Cannot insert the value NULL into column 'owner_sid', table 'msdb.dbo.sysjobs'; column does not allow nulls. INSERT fails. The statement has been terminated." What does the error mean? cant i run the same script generated in sql2000 on sql2005. Wht is the approach to transfer the jobs from sql2000 to sql2005 ? I know ther is one more way i can transfer.It is through sqlserver business intelligence development studio and using transfer job task.But i dont want to use that.I want to script the job and execute it on sql2005. Regards Arvind
View Replies !
View Related
Copying / Moving Data From SQL2005 To SQL2000
Hi I have a (possibly) common position where half of our IT department is SQL 2005 and the rest is SQL2000. For myself, having to work in a SQL2000 environment and needing data from a SQL2005 Cluster I came up with this solution. Also, to alert me when the process starts and completes since it is part of a scheduled process, I have it do a RAISERROR with logging to record entry and exit times. In addition, this runs out a series of PRINT statements that lets the operator know what table is currently being worked on. Of course, any suggestions for speeding this up would be helpful! What I have found that works for getting data (albeit slow) from SQL 2005 down to SQL 2000 is to script a fairly simple copy process - -- it is actually pretty easy to follow if you just read the code...... -- first, we find all the views which are present, so they can be ignored when copying the raw data CREATE TABLE #VIEWS (TABLE_NAME NVARCHAR (255) ) INSERT #VIEWS SELECT TABLE_NAME FROM OPENDATASOURCE( 'SQLOLEDB', Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' ) .SQL_SIS_SYSTEM.INFORMATION_SCHEMA.VIEWS -- now, we get the actual tables with data CREATE TABLE #TEMP (TBL_SCHEMA VARCHAR (12), TBL_NAME VARCHAR (255), RECCNT INT ) INSERT #TEMP SELECT TABLE_SCHEMA , TABLE_NAME FROM OPENDATASOURCE( 'SQLOLEDB', 'Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' ) .SQL_SIS_SYSTEM.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME NOT IN (SELECT TABLE_NAME FROM #VIEWS) -- then, we start copying tables over - now we tag the ones which are populated with at least 1 row. -- the first cursor loop gets all table names, the second will find row counts of source tables. -- this segment uses (ugh) cursor to loop through and gather all of the data. This cursor is at the table name level - not -- processing anything, and is used only to find tables which have a rowcount > 0 -- believe it or not, the cursors run pretty darn quickly since they arnet doing any calculations - just finding -- tables with row counts > 0 SET @QUOT = CHAR(39) SET @LBRAKT = '[' SET @RBRAKT = ']' SET @IUT = 'IsUserTable' SET @ODBC_CMD ='(' + @QUOT + 'SQLOLEDB' + @QUOT + ',' + @QUOT + 'Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' + @QUOT + ').SQL_SIS_SYSTEM.dbo.' PRINT 'BEGIN TABLE SCHEMA LOAD CURSOR. ' + CAST(GETDATE() AS VARCHAR (50) ) DECLARE GETEM CURSOR FOR SELECT TBL_SCHEMA, TBL_NAME FROM #TEMP OPEN GETEM FETCH NEXT FROM GETEM INTO @TBL_SCHEMA, @TBL_NAME WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = 'UPDATE #TEMP SET RECCNT = ' + '(SELECT COUNT(*) FROM OPENDATASOURCE(' + @QUOT + 'SQLOLEDB'+ @QUOT + ',' + @QUOT +''Data Source=SQL2005server;User ID=trust_me;Password=i_know_what_i_am_doing' + @QUOT + ').SQL_SIS_SYSTEM.' + @TBL_SCHEMA + '.' + @TBL_NAME +')' + ' WHERE TBL_NAME = ' + @QUOT + @TBL_NAME + @QUOT EXEC (@SQL) FETCH NEXT FROM GETEM INTO @TBL_SCHEMA, @TBL_NAME END CLOSE GETEM DEALLOCATE GETEM PRINT 'FINIS TABLE SCHEMA LOAD CURSOR. ' + CAST(GETDATE() AS VARCHAR (50) ) DECLARE @PLIN VARCHAR (80),@LFT_PLIN VARCHAR (20), @RT_PLIN VARCHAR (20), @TLIN INT PRINT 'BEGIN TABLE CHECK AND LOAD CURSOR.' DECLARE FETCHIT CURSOR FOR SELECT TBL_NAME, RECCNT FROM #TEMP WHERE RECCNT > 0 OPEN FETCHIT FETCH NEXT FROM FETCHIT INTO @TBL_NAME, @RECCNT WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'CHECKING ' + @LBRAKT+ @TBL_NAME + @RBRAKT IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id (@LBRAKT+ @TBL_NAME + @RBRAKT ) AND OBJECTPROPERTY(id,''+@IUT) = 1) BEGIN SET @LFT_PLIN = 'OBJECT' + SPACE(5) SET @RT_PLIN = SPACE(5) + 'NOT FOUND. BUILD ' + CAST(@RECCNT AS VARCHAR (6) ) + ' rowS.' SET @PLIN = @LFT_PLIN + @LBRAKT+ @TBL_NAME + @RBRAKT SET @TLIN = LEN(@PLIN) SET @PLIN = @PLIN + SPACE(50 - @TLIN) + @RT_PLIN PRINT 'ENTRY. ' + CAST(GETDATE() AS VARCHAR (50) ) PRINT @PLIN PRINT 'COPYING DATABASE TABLE.' SET @SQL = ' SELECT * INTO ' + @TBL_NAME + ' FROM OPENDATASOURCE' + @ODBC_CMD + @TBL_NAME EXEC (@SQL) PRINT 'COMPLETED. ' + CAST(GETDATE() AS VARCHAR (50) ) END IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id (@LBRAKT+ @TBL_NAME + @RBRAKT ) AND OBJECTPROPERTY(id,''+@IUT) = 1) -- #TEMP (RECCNT) CONTAINS ROW COUNT OF SOURCE SYSTEM. -- COMPARE TO ROW COUNT OF LOCAL TABLE BEGIN SELECT @DEST_RECCNT = RECCNT FROM #TEMP WHERE TBL_NAME = @TBL_NAME IF @DEST_RECCNT = @RECCNT BEGIN PRINT 'TABLE row COUNT MATCHES. BYPASSING REBUILD.' END IF @DEST_RECCNT <> @RECCNT BEGIN SET @SQL = 'DROP TABLE ' + @TBL_NAME EXEC (@SQL) SET @LFT_PLIN = 'OBJECT' + SPACE(5) SET @RT_PLIN = SPACE(5) + 'REBUILD.' + CAST(@RECCNT AS VARCHAR (6) ) + ' rowS.' SET @PLIN = @LFT_PLIN + @LBRAKT+ @TBL_NAME + @RBRAKT SET @TLIN = LEN(@PLIN) SET @PLIN = @PLIN + SPACE(50 - @TLIN) + @RT_PLIN PRINT 'ENTRY. ' + CAST(GETDATE() AS VARCHAR (50) ) PRINT @PLIN PRINT 'COPYING DATABASE TABLE.' SET @SQL = ' SELECT * INTO ' + @TBL_NAME + ' FROM OPENDATASOURCE' + @ODBC_CMD + @TBL_NAME EXEC (@SQL) PRINT 'COMPLETED. ' + CAST(GETDATE() AS VARCHAR (50) ) END END FETCH NEXT FROM FETCHIT INTO @TBL_NAME, @RECCNT END CLOSE FETCHIT DEALLOCATE FETCHIT PRINT 'FINIS TABLE CHECK AND LOAD CURSOR.' XIT: RAISERROR (50002,010,1) WITH LOG -- FLAG COMPLETION TIME IN SQL LOG SET QUOTED_IDENTIFIER OFF
View Replies !
View Related
Upgrading SQL2000 Enterprise To SQL2005 Standard
Hi , We are using the a 4 CPU SQL Server 2000 Enterprise edition for our application , We want to migrate to SQL2005 with the compatibility level downgraded to SQL2000. If we go for the SQL2005 Standard edition is there any problem? We are not using the Failover clustering in our server. thanks & Regards siva
View Replies !
View Related
|
TRACKER
