Need Help With Raiserror Function In Ado.net Mssql 2000
Dec 7, 2007
I have a stored procedure that raises an error just fine, but the problem is when i want to catch that particular error in client code, how do i go able doing so, i want to display a message in a label when that particular error occurs.
right now all i know about the raiserror function is RAISERROR("Message", Severity, Stage) how do i give it number so i can catch it in code?
Does enabling/disabling Data Execution Prevention have a performanceimpact on SQL 2000 or SQL 2005?For SQL best performance - how should I configure for:Processor Scheduling:Programs or Background servicesMemory Usage:Programs or System Cache
I am a bit new to the MSSQL server. In our application, we use so many SQL queries. To imporve the performance, we used the Database enigine Tuning tool to create the indexes. The older version of the application supports MSSQL 2000 also. To re-create these new indexes, I have an issue in running these "CREATE INDEX" commands as the statements generated for index creation are done in MSSQL 2005. The statements include "INCLUDES" keyword which is supported in MSSQL 2005 but not in MSSQL 2000.
Ex:-
CREATE INDEX IND_001_PPM_PA ON PPM_PROCESS_ACTIVITY
Ben writes "I have a sql script that doesn't function very well when it's executed on a SQL 2000 server.
The scrpt looks like this:
--------------------------------------------------------------------------------------------------- USE [master] GO IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME') EXEC sp_addlogin N'SSDBUSERNAME', N'SSDBPASSWORD' GO GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME] GO GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME] GO GRANT CONNECT SQL TO [SSDBUSERNAME] GO GRANT CONTROL SERVER TO [SSDBUSERNAME] GO GRANT CREATE ANY DATABASE TO [SSDBUSERNAME] GO USE [master] GO If EXISTS (Select * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME') ALTER LOGIN [SSDBUSERNAME] WITH PASSWORD=N'SSDBPASSWORD' GO GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME] GO GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME] GO GRANT CONNECT SQL TO [SSDBUSERNAME] GO GRANT CONTROL SERVER TO [SSDBUSERNAME] GO GRANT CREATE ANY DATABASE TO [SSDBUSERNAME] GO USE [master] GO IF EXISTS (select * from dbo.sysdatabases where name = 'ISIZ') DROP DATABASE [ISIZ] GO USE [SurveyData] GO exec sp_adduser 'SSDBUSERNAME' GRANT INSERT, UPDATE, SELECT, DELETE TO SSDBUSERNAME GO USE [SurveyManagement] GO exec sp_adduser 'SSDBUSERNAME' GRANT INSERT, UPDATE, SELECT, DELETE TO SSDBUSERNAME ---------------------------------------------------------------
I need to be converted to a script that can be executed on both MSSQL 2000 and MSSQL 2005.
I was wondering if somebody there could help me with this problem?!
I've been tasked to move our production databases on MSSQL 2000 to 2005. I've supported MSSQL since version 6.5 and performed migrations to successor versions.
Current Environment is MSSQL 2000 32-bit with current Service Packs.
I've performed mock migrations on Test servers upgrading all Production instances simultaneously from MSSQL 2000 to 2005 32-bit. The Test environment is identical to Production minus server name, IP etc. Also I have a separate server with MSSQL 2005 installed where I use the DETACH / ATTACH and BACKUP / RESTORE method for migration / acceptance testing. There are approximately 30 databases totaling 70 GB. This has gone as expected and fairly successful. Vendors have been coordinated with to update code and staff for acceptance testing.
I'd prefer going directly to MSSQL 2005 64-bit instead if possible due to memory benefits etc. This is where I'd like some feedback prior to borrowing a 64-bit server for testing.
Upgrade options:
1. Is it better to migrate from MSSQL 2000 32-bit to 2005 64-bit via: a. DETACH / ATTACH b. BACKUP / RESTORE c. Is one method more advantageous relating to the end result? 2. Regarding XP clients, have issues been experienced with the default SQL Server driver or is an alternate recommended for XP clients to connect to a MSSQL 64-bit server databases? 3. If you have performed this migration and have relevant experience please pass them along.
Hi everybody,I was looking for an equivalent ORACLE INSTR Function in MSSQL but Idon´t found it and I don´t know if it exist so I must to write it andthis is the code. Maybe it will be helful to you:/************************************************** *************************Description:Looks for a string inside another string and returns an integerthat correspond to the position of first ocurrence.Parameters:Input:- strSource. Contains the string where the functions look for theother string- strToFind. Contains the string to look for inside strSourceSalida:- Integer value indicating the position of first occurrence ofstrToFind in strSource************************************************** *************************/CREATE FUNCTION posSubString(@strSource varchar(400),@strToFind varchar(200)) RETURNS intASBEGINDECLARE@position int,@maxPos int,@longSubStr int,@res int,@strSub varchar(200)SET @position = 0SET @res = 0SET @longSubStr = LEN(RTIRM(LTRIM(@strToFind)))SET @maxPos = LEN(@strSource) - @longSubStrWHILE (@position <= @strToFind)BEGINSET @strSub = SUBSTRING(@strSource, @position, @longSubStr)IF (@strToFind = @StrSub)BEGINSET @res = @position - 1RETURN @resENDELSESET @position = @position + 1ENDRETURN @resENDAlonso
HelloI watchet some posts but did not find answer to my question : how toimport data from xml into mssql 2000 using t-sql?i tried:USE Northwindif(object_id('dbo.test_xml') is not null)drop table dbo.test_xmlgocreate table dbo.test_xml (Id int identity(1,1), col1 text)goinsert into test_xml(col1) values('')godeclare @cmd varchar(512)set @cmd = 'D:Progra~1Micros~3MSSQLBinnTextCopy.exe /S /U[user] /P [password] /D Northwind /F c:pobierz.xml /Ttest_xml /C col1 /I /W "where Id = 1"'exec master..xp_cmdshell @cmd, 'no_output'godeclare@hdoc int, @doc varchar(1000)select @doc = col1 from dbo.test_xmlexec sp_xml_preparedocument @hdoc out, @docselect @hdoc as hdoc-- ...select * from openxml(@hdoc, ...--...exec sp_xml_removedocument @hdocBut my documet have more than 1000characters and more than max varchar. Sohow to do that?Best regardsAJA
I am using Visual Studio 2005 and MSSql-2005 Server. But i also want to add MSSql-2000 Server Database.
Both the servers are running but when i provide the connection string of MSSql-2000 in SqlConnection to access data base, i get an error. It says like MSSql-2005 is not allowing to access.
So, How can i add MSSql-2000 to Visual Studio-2005???
Hi,I have app with file selection field. Users can shoose xml file from their local hard disk and click Save. When they click save, content of xml file should be inserted into mssql 2000.What is the most efficient way to do this using C#2005 and mssql2000?Any good article or tutorial?
:confused: I have a single table "amount" which has only three columns such as memberid referenceid and AMOUNT with following data ........ memberid referenceid AMOUNT L001 L001 L004 L003 L003 L002 L002 L001 and so on my needed query willbe if i choose L003 from referenceid the query should select the column where it was a memberid and again it choose the same row refernceid of(L003) as L002 where L002 was a memberid and choose L002s referenceid which is L001 Finally i should REACH the memberid(L001) and the other one i need is i should return the count of no of rows crossed , and my biq question is everytime the query gets into new row i should add some data in amount (50,25,12.5,10,7.5,5,4,3,2,1)for (immediate row,next row,next row and so on) how can i do this in MSSQL2000?? I have crossed accross many forums pls help im stuck up :(
I am now trying to configure Log Shipping in my compeny but I have encountered some difficulties. The destination site and the source site are far from each other and the transaction logs are too big, this situation causes an huge overload on our private network (we use WAN which is complitly disconnected from the internet). Is there any way to compress the transacion logs with zip or rar and combine it in the log shipping mechanizem?
Is there another way for making this files smaller?
I have recently downloaded the MSSQL 2000 Evaluation package from microsoft website,but i can't get it to install.I run the autorun normally,but when i select the option install database server nothing happens.I am trying to install it on Windows XP Service Pack 2 Edition 5.1....As this would help,in 3-4 hours of computer inactivity,i see the setup loader but nothing happens,it says not responding.
Yao, Could anyone help me with, or point me to information regarding, scheduling jobs for MSSQL 2000? I need to execute a SQL Command, monthly, which sets a budget field to a fixed ammount, something like: UPDATE Programs SET Budget = 500. --Kyle Johnson
hi guys, are there any official or some sort of documents to talk about the advantages and disadvantages of storing image in the SQL's Image datatype field or storing the pointer to the image file? i have lots of static images (no more than 1MB each) that requires little updating and i need to retrieve them constantly. i am really struggled here on which method i should store the images: to store the images as Image datatype in SQL or save them as files and store the paths to the files. i would like to know, in my case, would there be any performance and efficient difference on using either method? naturally, i'd think the 1st method seems like a better solution. it's easier to back up and it protects integrity. But at the same time, i am not sure if the constant inserting and retrieving of images will affect the performance? also, i want to share the images and may create a ticker thing (using JavaScript) to display the images on the web page randomly and it seems like the 2nd method is easier to program. if you were me, which method would you use and why? thanks!
Hi, I am installing MQQSQL2000 onto NT4 SP6. Installation prompts me this msg "Setup failed to configure the server". I click on, it terminate.
Below is the sqlstp.log file for reference.
Any one got any idea ?? The error at the last few lines.
TQ
13:01:27 Begin Action BuildServer: 13:01:27 C:TEMPSqlSetupBinscm.exe -Silent 1 -Action 5 -ExePath "C:Program FilesMicrosoft SQL ServerMSSQL$MOMSERVERinnsqlservr.exe" -Service "MSSQL$MOMSERVER" 13:01:27 Process Exit Code: (0) 13:01:27 Begin Action: CreateRegistrySetSQL 13:01:27 End Action: CreateRegistrySetSQL 13:01:27 Begin Action: RegWriteSetupEntry 13:01:27 End Action: RegWriteSetupEntry 13:01:27 Begin Action: CreateSer 13:01:27 End Action: CreateSer 13:01:27 Begin Action: SkuIt 13:01:27 End Action: SkuIt 13:01:28 SetFileSecuritySQLAndAdmin for C:Program FilesMicrosoft SQL ServerMSSQL$MOMSERVER returned: 0, 0 13:01:28 SetRegSecuritySQLAndAdmin for SoftwareMicrosoftMicrosoft SQL ServerMOMSERVER returned: 0, 0 13:01:28 Begin Action: UpdateSystemPath 13:01:28 Path successfully updated. 13:01:28 C:PROGRA~1COMMON~1UeWI;C:PROGRA~1UeWISYS;C: PROGRA~1UeWICommon;C:PROGRA~1UeWI;%SystemRoot% system32;%SystemRoot%;C:Program FilesUeWIReconin;C:Program FilesUeWIRecondll;C:Program FilesMicrosoft SQL Server80ToolsBinn 13:01:28 End Action: UpdateSystemPath 13:01:29 Grant Right for MOMDEVAdministrator returned : 1, 0 13:01:29 Grant Right for MOMDEVAdministrator returned : 1, 0 13:01:29 Grant Right for MOMDEVAdministrator returned : 1, 0 13:01:29 Grant Right for MOMDEVAdministrator returned : 1, 0 13:01:30 C:Program FilesMicrosoft SQL Server80ToolsBinncnfgsvr.exe -F "C:WINNTsqlstp.log" -I MOMSERVER -V 1 -M 0 -Q "SQL_Latin1_General_CP1_CI_AS" -H 65908 -U sa -P ################################################## #############################
13:03:27 Process Exit Code: (-1) 14:06:27 Setup failed to configure the server. Refer to the server error logs and C:WINNTsqlstp.log for more information. 14:06:30 Action CleanUpInstall: 14:06:30 C:TEMPSqlSetupBinscm.exe -Silent 1 -Action 4 -Service SQLAgent$MOMSERVER 14:06:30 Process Exit Code: (1060) The specified service does not exist as an installed service.
I normally use Microsoft Access to retrieve data , via ODBC connection, from a table in a SQLserver database. One of the fields in the table has a value 1 but at times when the data is retrieved into MS Access it turns out to be 100 in Access. Moreover, when I try to change it in Access my computer hangs or it takes a long time before the change is effected. It does not matter from which client Pc this action takes place it remains the same. I am using ODBC SQLserver ODBC driver version 2000.81.9042.00.
I would be grateful if you could help me out of this strange situation.
Hi I'm an experienced programmer, but just don't have much experience with ASP or MSSQL. I inherited a website and now need to set up an automated backup system for a fairly large (40MB) MSSQL database hosted by a 3rd party provider of reasonable quality with ASP and SQL Server 2000. Let's avoid ASP.NET solutions since the website I inherited is written in "straight up" ASP (old school).
I have access to the database, login etc. I'm thinking I should combine an ASP script with the database connectivity and write daily/weekly backups to a dir on the hosting server.
I've got a nagging problem with the snapshot agent not being able to connect to the distributor.... I have a set up as follows.
I've set up the machine as a publisher, and as it's own distributor. Both the SQL Server and SQL Server Agent services are configured to run as the Administrator user (not ideal I know), and I've created a publication ready for replication. (the agent and distributor are on the same machine)
When I try and run the snapshot agent, Enterprise Manager just times out but a log in the event log says:
Event ID: 203 step 2 - The process could not connect to Distributor 'X'.
Thats about all the info I can find, nothing else is being logged.
Can anyone suggest anything I might have missed? - it's driving me up the wall!
I’ve got a big problem with my Database, Whenever I runs my query I’ve got error on the half way. I’m using MSSQL 2000. So I run DBCC CHECKDB and the follow errors result shown. I don’t know how to solve that problem.
Plz help me plz help me If that database crush I’m gonna be fu*ked up by my boss. :...( plz
DBCC results for 'Table_1'. Server: Msg 8928, Level 16, State 2, Line 1 Object ID 795149878, index ID 0: Page (1:352679) could not be processed. See other errors for details. Server: Msg 8944, Level 16, State 1, Line 1 Table error: Object ID 795149878, index ID 0, page (1:352679), row 44. Test (!(hdr->r_tagA & (VERSION_MASK | RECTAG_RESV_A | RECTAG_RESV_B))) failed. Values are 157 and 193. Server: Msg 8944, Level 16, State 1, Line 1 Table error: Object ID 795149878, index ID 0, page (1:352679), row 44. Test (ColumnOffsets <= (nextRec - pRec)) failed. Values are 18754 and 3160.
There are 0 rows in 1 pages for object 'Total_Profit_Loss'. Server: Msg 2511, Level 16, State 2, Line 1 Table error: Object ID 1211151360, Index ID 0. Keys out of order on page (1:161199), slots 25 and 26. Server: Msg 2511, Level 16, State 1, Line 1 Table error: Object ID 1211151360, Index ID 0. Keys out of order on page (1:173791), slots 4 and 5. Server: Msg 8928, Level 16, State 1, Line 1 Object ID 1211151360, index ID 0: Page (1:206443) could not be processed. See other errors for details. Server: Msg 8939, Level 16, State 1, Line 1 Table error: Object ID 1211151360, index ID 0, page (1:206443). Test (m_freeCnt == freeCnt) failed. Values are 660 and 692. Server: Msg 2511, Level 16, State 1, Line 1 Table error: Object ID 1211151360, Index ID 0. Keys out of order on page (1:217603), slots 18 and 19. Server: Msg 2511, Level 16, State 1, Line 1 Table error: Object ID 1211151360, Index ID 0. Keys out of order on page (1:266945), slots 26 and 27. Server: Msg 2511, Level 16, State 1, Line 1 Table error: Object ID 1211151360, Index ID 0. Keys out of order on page (1:268051), slots 28 and 29. Server: Msg 8928, Level 16, State 1, Line 1 Object ID 1211151360, index ID 0: Page (1:277634) could not be processed. See other errors for details.
DBCC results for 'Pin'. Server: Msg 8939, Level 16, State 106, Line 1 Table error: Object ID 1211151360, index ID 0, page (1:277634). Test (m_freeCnt == freeCnt) failed. Values are 2168 and 2164. Server: Msg 8928, Level 16, State 1, Line 1 Object ID 1211151360, index ID 0: Page (1:299697) could not be processed. See other errors for details. Server: Msg 8939, Level 16, State 1, Line 1 Table error: Object ID 1211151360, index ID 0, page (1:299697). Test (m_freeCnt == freeCnt) failed. Values are 3435 and 3431. Server: Msg 8928, Level 16, State 1, Line 1 Object ID 1211151360, index ID 0: Page (1:302290) could not be processed. See other errors for details. Server: Msg 8944, Level 16, State 1, Line 1 Table error: Object ID 1211151360, index ID 0, page (1:302290), row 12. Test (((UNALIGNED DataRecHdr*) m_pRec)->r_tagB == 0) failed. Values are 4 and 0. Server: Msg 2511, Level 16, State 1, Line 1 Table error: Object ID 1211151360, Index ID 0. Keys out of order on page (1:900223), slots 2 and 3. There are 5940869 rows in 210312 pages for object 'Pin'. CHECKDB found 0 allocation errors and 14 consistency errors in table 'Pin' (object ID 1211151360).
CHECKDB found 0 allocation errors and 23 consistency errors in database 'MY_SQL_DB'. repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (MY_SQL_DB ). DBCC execution completed. If DBCC printed error messages, contact your system administrator.
On one of our intranet SQL servers running under Windows 2000 SP 3, MSsql 2000 SP4 seems to gradually "eat" away all available memory (with no obvious reason for it) until a certain limit is reached, forcing the server to slow down substantially since the OS has to SWAP continuously.
I would appreciate any suggestions at this point on how to tackle this problem :)
Select order_NO, shiptoname, Shiptoaddress, Shiptocity,shiptostate, shiptozip, EMAILaddress FROM orders;
my question is: If the email exist in the EMAILaddress column then I need to have a Y show in another column called EMAILflag, if the EMAILaddress does not exist then I would need the EMAILflag to be a N.
Hi, I have one application in two different companies with MSSQL2000 running on Windows2000 Server and Windows2003 Server. It seems that memory needed for MSSQL2000 as displayed in task manager is increased every day. Does anybody knows anything regarding memory leaks in MSSQL2000?
How can I find what version of service pack have I installed in a MSSQL2000 server machine?
I am doing my final semester project. It is a migration tool that converts the DB2 PL dialect into MSSQL Server's T-SQL dialect. I am in need of sample DB2 PL files(procedures, UDFs, triggers) for testing the tool. Please point out me to some DB2 files repository links or if you have some files, please give me the same. I will use this only for testing purposes.
I am using MSSQL 2005 but we have to work in compatibility mode with MSSQL 2000 and also we are only allowed to use 2000-compatible features, now my question:
I need to programmatically copy a database within the same server (including all stored procs, triggers and tables, indices) ...
Can anybody give me a hint what is the best way for reaching this target??