Hi, I have a situation where I need to give a custom error message in an SP where I get a primary key violation. I tried this with the @@error variable as in the following code. CREATE PROCEDURE TESTspt1PROC AS insert into testspt1 values (1, 'spt') if @@error <> 0 PRINT 'it is a Pri...more >>

Could someone explain me the second answer of this query : select @@nestlevel go sp_executesql N'select @@nestlevel' go ----------- 0 ----------- 2 I expected 0 and 1 ... Thx. JN. ...more >>

how to handle sql errors with severity less then 10 in .NET i call to stored procedure which raise error with severity 10 for example however in c# i can't cach this error (with severiry >= 11 it going to the catch block in my c# app), also the @@ERROR is equal to zero when severity <= 10 -...more >>

Hello everybody. What mean if server is runninf in the default 80 mode What are 60, 65,70 mode. Thanks for all respond. ...more >>

I am trying to create a DTS package to send results from an extended stored procedure [master.dbo.xp_fixeddrives] to an Access table. What is the best Task to use in order to call an extended stored procedure in DTS? -- Message posted via http://www.sqlmonster.com...more >>

Currently this is purely hypothetical, though I'm semi-considering something like this for part of a current project. I'm wondering how to apply a pseudo-order to a set of data. To give an example, if you were storing Service Ranks, which were referenced by the service personnel you stored...more >>

Hi, I'm having trouble getting the Index Tuning Wizard to analyze my workload. I'm using SQL Server 2000. I've run the ITW against several different trace files, and keep getting an error that says, "The workload does not contain any events or queries that can be tuned against current datab...more >>

Hello, I have 300 groups. Each user can or can not be associated with certain group. I would like to represent group membership using corresponding flag (on/off). Using integer dattatype it will look like : G1- 1, G2 - 2, G3 - 4..... If user is member of group of 1,2 and 3 it will be re...more >>

Hello how do I insert to an other db using a insert-statement? INSERT INTO OtherDBsTable(Something) SELECT Something FROM ThisDBsTable TIA /Lasse ...more >>

Hi All: We encounter a distributed transaction error in a stored procedure that uses database links to do select and update queries over two databases. Two procedures were created with the same structures and query almost the same tables. One of them works well even now. But the other enco...more >>

I need to create an UPDATE statement that links 3 tables in order to update one of them (WorkerDeductions.DeductionBalance). Below is the code I have so far but am not sure how the UPDATE statement would be coded. Any help is appreciated. UPDATE dbo.WorkerDeductions SET dbo.WorkerDeducti...more >>

i have many buses that drive between many cities. some of the buses connect more than 2 cities in one line... ...more >>

First off, I know that only members of SA can execute xp_cmdshell... That said, is there a way to make this work: I have a stored procedure that utilizes xp_cmdshell to pull file information from a directory. The process gets the file names, renames, moves to archive and compresses. Th...more >>

Hi all, OK, I'm sure very simple question, what am I doing wrong. how do I get the percentage? of 68 / 1812 @result keeps showing 0.00 Declare @result varchar(20) set @result = cast(round(100 * (68 / 1812) , 2)as numeric(5,2)) print @result thanks GV ...more >>

what 's the maximum length of database objects in sqlserver 2000? Thanks ...more >>

Hello, I have 2 tables; I want to create a query that will show only records that DO NOT Exists in another table. Now I know I can do a select with an EXISTS but I wanted to know if this is the fastest way as I will be dealing will millions of records. Any ideas? ...more >>

I am trying to call a User Defined Function whilest feeding it a dynamic parameter instead of a literal. The UDF returns a table: CREATE FUNCTION fn_GetSubAgents(@agtpin decimal(7,0)) RETURNS @AgentList TABLE ( pin_sur decimal(7,0), sch_nam char(40) ) AS BEGIN INSERT ...more >>

Hi, I have the following query that I can't figure out why it won't work. I'm getting the following error in SQL Query Analyzer: Syntax error converting datetime from character string. If I change the sortBy value to any value other than 2 it works just fine and returns results. I can't...more >>

In trying to simplify an overly complex query [at one time, 1000+ views and 14 queries run in query analyzer!], I am trying to incorporate many of the different views/queries together but am having troubles with one thing; trying to divide one of the columns in a view. SELECT FIPS, [MTG...more >>

I have a SQL Server 2000 database set up on my local machine, set to mixed mode authentication. I also have an ASP.NET web service running on the same machine. When the web service is called, it connects to the db using a connection string from the web.config file. Whenever I try ...more >>

We have to allocate pseudo-random password to primary aged students. RedDog1 BlackCat3 you get the idea. I have the seed words stored in a table and I need to extract them at random...so I need to be able to gernate random INTEGERS between 1 and 10 to pick the seed word(s). I am very confu...more >>

I have a trigger on FOR INSERT, but even when I just change data in an existing row, the trigger fires!! Why would this happen? I have the trigger on a replicated table -- does that have anything to do with it? Please help, I'm going crazy. CREATE TRIGGER trigIncidentInsert ON [Incident...more >>

In light of SARBOX, ChoicePoint security breaches, identity theft etc, what are some best practices regarding encryption and security of this type of info? I know this is a pretty broad question, so links to other sites are great, but I'd like to hear some personal experiences and opinions. ...more >>

If I update a row using one connection and leave the transaction open is it true that another connection can't read any rows from the same table. That is what I'm seeing in query analyser - an IX lock at the table level which prevents any reads. Please help me.......more >>

DISCLAIMER: I didn't design this, can't change it, have to deal with it... If an application updates a primary key on a table that has a compund key, will I be able to access the old primary key values as well as the new in an INSTEAD OF TRIGGER...?...more >>

In the sysindexes table, what field stores the columns which an index is based on? TIA ...more >>

I am trying to get a sum of the dollar amount for each term for each customer. Unfortunately, the same value for sum is returned, so I thinkt hat I have my query wrong. My query looks like this: SELECT sales.name, sales.terms, t_order_term.order_term_description, SUM(sales.prodamt) AS TheSum...more >>

The following is what a customer is seeing: He writes CLR stored proces in SQL 2005 and then executes them using sqlcmd scripts, occasionally he gets the following error: Sqlcmd: Error: Microsoft SQL Native Client : Unknown token received from SQL Server Any help you can provide is grea...more >>

In vfp, I can set the table as free table. and just copy *.dbf to other PC, and then I can use it. In SQL, I try that I can only export the table into text , csv.... excel etc. If i want to import that excel, ALL the width is set to nvarchar 255. Does there is any simple way, I can copy th...more >>

I am writing a script that takes a database and will make all new indexes. I have about 150 identical databases but they all have slightly different indexes due to usage, profiler and tuning wizard set them all up separately. So I want to start from scratch. Is there a statement I can use to ...more >>

I have created 2 ASP pages that gives users the option of switching back and forth between French and English. They click on the word English and it converted the text to English and the French link converts the text to French. In the ASP page with the form on it there is an includes file pointi...more >>

Hello, I setup replication between 2 computers, but it keeps crashing because (I think) the database table names include spaces. I have the option to go through each individual replication stored procedure (3 per table) and edit them adding [] to each....but I'm replicating more than 100 tabl...more >>

Hello, Forgive me if I am being stupid, but I cannot get some syntax to work and I need it urgently. I want to drop a table if it exists and make a new table in its place. I though I could do, DROP TABLE IF EXISTS tablename; CREATE TABLE tablename ([ID] [int] NOT NULL) but I get the ...more >>

Greetings - I need to know how I can retrieve the Meta Data Information about a procedure. I know I can use SET FMTONLY ON to retrieve it but I need to retrieve this dynamically - I need a call that will return a resultset containing the information instead of just a header info. TIA...more >>

Hello. I had a table with identity column set to datatype [smallint]. I've reached near 32800 records. That's the limit for [smallint]. So, I've changed the identity datatype to [int], that SHOULD allow me to have more records. But the error continues. "Arithmetic overflow error convertin...more >>

Is there a way in SQL server 2000 to read a value into a stored procedure while it is running? for example using a PRINT 'Enter a Value' this puts the line in the results window. Is there a way to read in a value from the result window for use in the stored procedure? Thanks in adva...more >>

I need to get data from a table with <NULL> in a date field. My query looks something like this: select batch_id, visit, reg_date from daily_batch where cpi = 6800 and post_date = NULL This should bring back a record but it does not. cpi 6800 exists and the post_date field is <NULL>....more >>

I have a table with roughly 165,000,000 records. Within this table is a subset of roughly 6,000,000 records I need to retrieve and process sequentially with some VB code. These records are identified by a numeric code in 1 field. There is an index built on this field. When I run a sim...more >>

How to compress the output into single line if got the below output using case..else 9155 michael NULL NULL NULL 9155 NULL NULL narain NULL 9155 NULL NULL NULL karthik 9155 NULL shumaker NULL NULL Thanks krish...more >>

Dear all, Because of the large amount of live servers i am obligated to have in my desktop six or seven query analyzer sessions (linking servers is forbidden) against them. Sometimes and suddenly i lost my keyboard configuration and when I touch the * key appears another character (th...more >>

Hello experts, I think this is an easy question for you... I do an Insert in a "character type" column (nvarchar) and the value has enter chars. In detail, I build dynamically a string and use the char(13) character, the enter char is inserted as a "rectangle char" instead of pragmatic ent...more >>

Hello, I'm writing a function witch from an string (nvarchar) gets the characters and converts them. I have the problem with case sensitivity. I get the results in small letters but I need them to be in the exact case as the input values, and must not change the SQL Server's settings. I've t...more >>

Hi, I am getting problem when I use DBCC CHECKIDENT with in a Transaction. Please advice on this. -- Regards Sivakumar...more >>

I am trying to complete an insert statement for user logins in order to keep a record of all the times users login. Can someone help me with the statement below. Thanks very much for any help anyone can give me. CREATE PROCEDURE [LoginValidation] @p_UserName char(12) AS SELECT UserName, P...more >>