I'm would like to import records I select with a SELECT from one database to another but maintain the identity column values. I've read where I can do this by setting IDENTITY_INSERT ON. From the Import wizard on the enterprise manager, I am selecting 'import using a query', then specifying: ...more >>

hello, is there a way to reset @@rowcount to zero if I have multiple inserts one after another I can't rely on @@rowcount because it does not always reflect the last insert if one of inserts fails then the @@rowcount keeps its value from the previous Insert Am I wrong?? SAM ...more >>

Hello, How do I know that an Insert has succeeded? Should I check both @@rowcount and @@Error like if @@rowcount <>0 and @@Error <>0 or what Does an If statement affect the @@RowCount or the @Error during the check. I know it could effect afterwards? because set @error = @@Error a...more >>

Which are the correct service packs for SQL Server 2000 Developer Edition? ...more >>

Hello, I have on SP that calls another ChildSP What is your recommendation on: having a Tran in ChildSP or just send back an error and rollback in Parent SP What happens if I have a Tran in ChildSP and I roll it back does Trans in ChildSP Bubble up to Parent SPs and then what?? Al...more >>

Hello, I have one table (TableC) that has one row and one column to supply couple of tables an Id Table 1 1 3 4 5 8 9 Table2 2 6 7 10 etc... What happens when multiple users are using the system What happens if one transaction is rolledback so by the time I get the ne...more >>

I have a table that contains about 450K records that represents sales leads. Within this table there are a set of columns (26 to be exact) that indicate the industry the lead is in. For eaxample, lets say: A = Transportation B = Telecommunications C = Utility .. .. .. Z = Other This ...more >>

I have a computer setup next to a receipt printer. I want the query results to print directly to the receipt printer. i have asp pages that take the input from the users, but at the end of their transaction, i want to print a report (receipt). Thanks...more >>

Dear All, Having just had this little gotcha seared into my memory, ( within 5 minutes of causing a $10k/minute shutdown ), I thought I'd post a refresher, as it is not recently in any archives that I can see. isnumeric('.') = 1 yet cast( '.' as int ) errors isnumeric(',') = 1 ...more >>

I am designing some tables for an application and I have an instance where I have a many to many relationship between two tables. I have an EMPLOYEE table and a BENEFIT table. Each EMPLOYEE can have more than 1 benefit and each BENEFIT can be held by more than one EMPLOYEE. I know I need to...more >>

Hi there. I have a problem with my sql statement. The data refers to an electronic answering form. People are asked questions "KANT_KRIT_ID" and their answers are stored in "KANT_ANTWORT". Now I would like to sort out those people that answered 1 for question KANT_KRIT_ID=1 and at the same tim...more >>

Hi Everybody, I'm joining four tables in a Stored Procedure and returning a result based on an input parameter passed in from ADO.NET's OLEDB provider. My question is.... Would it be more efficient to join the tables ahead of time in a View instead and then call the View from the Stored P...more >>

I have a column in the following select startement called displayvalue. Displayvalue is a varchar column. Primary everything entered into this column is numeric, but there are times when NR is entered. In the select query if it sees NR, I would like to have NR changed in the resultset t...more >>

Hai all, I'm working on a project that has around 25 tables of which around 10 tables contains 4 million records. All are culstered indexed. When we issue a select query that joins these tables (at a time 4 0r 5 joins),it creates locks in the database or sometimes it will take too long a ti...more >>

Hi...we recently ran a simple query "select * from tableA where columnA is null". ColumnA was not null, but instead a byte (''). But the query still returned records where ColumnA = ''. The ANSI NULL option for the database is not checked. Am I missing something in comparisons of null valu...more >>

I'm trying to remember how I use to place ' in my strings so they were picked up set @WhereClause = ' where ap_basicmodel = '823' '. I've tried the''' and the " ' " but ??? TIA -- Stephen Russell S.R. & Associates Memphis TN 901.246-0159 Steve says get rid of the notat_ to send hi...more >>

Just getting dirty w/ relations for the first time. I hit a rad block already. Consider these tables Prod_Main [ ProdId(pk) ] Prod_Accessories [ ProdId(fk) ][ AccId(fk) ] so, a basic 1-many relationship, right? Is this what is called a "self referencing table"? Now conside...more >>

I have column name called SendToQIP/ERS, and I'm trying to ADD DEFAULT to column like this ALTER TABLE tblQuality /* sets default to 0 (zero) * ADD CONSTRAINT Def_SendToQIP/ER DEFAULT '0 FOR SendToQIP/ERS This was an ACCESS Database file once before and naming conventions are not good. My e...more >>

I have a few questions I donnot know exact answer.Can someone help explain ? (1) What are some common causes of Unavailability for a SQL database ? (2) What is Perfmon ? Where can I find more info about it? (3) In a multi-user or multi-threaded SQL environment what issues may arise? Than...more >>

I'm trying to write a vbscript that connects to an oracle stored procedure and then sends two variables to the stored procedure. I'm connecting to the oracle database via ADO. Can anyone share sample code that would demonstrate connecting to the SP and then sending it two variables from th...more >>

Hi all, I have a table which resembles the following: Dept VP Director Manager Marketing Smith Smith Jones HR Thompson Haskins Packard IT Johns Peterson Jones And I need to somehow combine VP, Director, and Manager to get one un...more >>

Is there an SP that will show you table size as far as allocated and data similar to the sp_spaceused for dbs? Thanks in advance Offey...more >>

I have a VB application that I do a select statement to return a table (it is actually only one row that is being returned). This process takes about 30 seconds. Would it be faster to create a function that returns a table that does the same thing as the select statement? Aren't functions "com...more >>

How can extract only records from a table where the value of a field equals to its default? I mean something like select field1, field2 from table where field1 = defaultValue(field1) I have used user defined functions as default value, so if I query information_schema.columns ...more >>

I am begining to look at learning more about analyzing our servers performance. Understanding the profiler is a little overwhelming. For now I want to find poor performing queries which are all views now. Can I somehow set up profiler to track performance on all views ran from a datab...more >>

Hi, is exists some feature in SQL Server se rownum in Oracle.? I do not want identity, I need select XX, YYY, rownum from dual transform to SQL server want to do it in one select not procedure ... T. ...more >>

Here's the procedure: CREATE PROCEDURE proc @id varchar(50),@acct_num varchar(10) OUTPUT,@no_go int OUTPUT AS SET NOCOUNT ON SET @acct_num = '00' + @id SELECT first_name,last_name FROM $table WHERE account_number = @acct_num IF @@ROWCOUNT = 0 BEGIN SET @no_go = 1 RETURN END ELSE BEGI...more >>

I have a series of about 30 queries that need to be run. The where criteria contains many warehouse locations and status codes. Rather that repeat the code 30 times, I would like to place it in two vaiables and reference the variables in the where clause. I have tried using double and singl...more >>

I have some values that I need to convert a varring number of spaces between words and letters to one space. Is there a way to do this other than running an update similar to the following until no more rows are updated? Update tblX Set colX = replace(colX, ' ', ' ') Thanks much Carl ...more >>

I have an Access 2002 database running off SQL 2K advance server back end. Within this database I need to have column A (example column or field A) upon selection of a specific criteria have column B or field B become a automatically filled column with whatever criteria I specify. Basically making...more >>

Hello everyone, I have a field value like this: "RQ1234LN1". It comes from my receipts table and makes reference to the requisition on which that part appears and the line number. Is there a way to extract the numbers between "RQ" and "LN" and to extract the numbers following "LN"? The al...more >>

I have a webpage used to update parts. I have a complex part # that I'm using computed columns with. How do I use a passed in where clause that the webpage creates? Code below: CREATE PROCEDURE dbo.Test1 @whereClause varchar(150) AS select * from depappl where @whereClause I err...more >>

Hi everyone, I need to look for non-English characters in a SQL statement. Accent marks, ascii values above 128, etc. Is there a way to run a select statement from the query analyzer, where instead of the actual character I can substitute an ascii or hexadecimal value? For instance: ...more >>

For example: SELECT * FROM NO_TABLE; Server: Msg 208, Level 16, State 1, Line 1 Invalid object name 'NO_TABLE'. I need to capture this message and to put into logtable. And probably reraise after that. I know that @@ERROR shows a number of error, and sysmessages table has templates o...more >>

I know you have access within an insert trigger... But my question is in regards to the inserted data when an insert is performed within a stored procedure... i.e. From within a stored procedure I insert data from table1 into table2 that contains an Identity field. Is it possible to add the ...more >>

Hello: When I export a table in comma delimited format, it works OK, but there is one problem. If the field is varchar (or char) and has the size, for instance, 50, there are empty spaces in the text file, so it looks like this: "CBAS ","Bass Gambling Supplies ...more >>

Hi All, I have inherited a large database without any scope to change the db structure. Becuase these tables are downloaded daily from IBM DB2 (remote location) to SQL Server. This has to be done in the back end as it has nothing to do with reporting. It has to do with populating another tabl...more >>

Hi all quick question: I have a dimensional staging table and a dimension table. I am trying to query out the combination of child and parent that exist in the (newly extracted staging table) that do not exist in the dimensional table i.e. select item_cd, parent_cd from stage_org_Frcst ...more >>

Hi - this might be a basic question, but after some investigation i cant find an answer. I've got a stock recommendations screen which displays various money columns, however when the value is input in to MS SQL 2000, the value is truncated if it ends with 0. I.e. $9.90 becomes 9.9. Therefo...more >>

Why does the first query take 19 ms and the second/third takes 4000 ms? The time result difference seems to say joining a big table to a small table is BAD/slow AND joining a big table to variables from small table is very fast. The 4 sec query sometimes takes 30 secs when the server is busy. ...more >>

Hello SQL Gurus, I had a strange Requirement The end users of our system want to store as much data as they can i.e., from getting text from the word and other documents etc ... The reason they want to store it huge data.. If they store in a word file or other document means every o...more >>

What would be the quickest and easiest way to change a column datatype without dropping the column? Also, will all queries written for a varchar column work on an nchar or nvarchar column?...more >>

SQL Server 2000 I want to count the number of records created each day in a table, and display the result in date order. I am using something like this: SELECT COUNT(TestID) as NumCreated,CONVERT (VARCHAR(10), DateCreated, 103 ) as DateCreated FROM tblTest GROUP BY CONVERT (VARCHAR(10),...more >>

I think it is possible using sp_start_job, but I cannot get it to work. But, how can I get a job to start another job on a different server, when it finishes? Can that be done?...more >>

Hi All, can anybody please tell me what is the problem is with this statement. I am trying to update datetime field in my table. update test set date = '13/10/1999' where sno=1 I am getting Erro as... Server: Msg 242, Level 16, State 3, Line 1 The conversion of a char data type to a dat...more >>

Hi, I have a SQL Server 2000 DB which stores the data for my VB6 web app. Recently the VB6 web app was converted to VB.NET. As a result some of the stored procedures changed. The end result of these SPs does not change the final data that gets put in the DB, just in some cases it is done in...more >>

If I have a SELECT statement such as: SELECT ContractID, LTRIM(RTRIM(SitusStreetNo)) + ' ' + LTRIM(RTRIM(SitusFractionSuite)) + ' ' + LTRIM(RTRIM(SitusStreetDir)) + ' ' + LTRIM(RTRIM(SitusStreetNm)) + ' ' + LTRIM(RTRIM(SitusStreetType)) + ' ' + LTRIM(RTRIM(SitusStreetSuffix)) AS...more >>

Hi, How would you get information on priviledges for a specific user on a specific table. For example, I would like to find out if user 'johndoe' has 'write' access to table 'master.' Now I found that the sp_helpuser sproc gives me somewhat the information I desire, however not refine...more >>

I am tryoing to get a count of how meny transaction are fro wich accoun Table Licence AccID Transaction 111111 001 1 111111 002 222222 001 222222 ...more >>

I'm having some precision problems in a SQL2000 Function Here's my calculations in Excel: @mf_cu 0.231 @crt_cu 0.031 @ccoc_cu 28.7 (@mf_cu - @crt_cu) 0.2000000000000000000 (@ccoc_cu - @crt_cu) 28.6690000000000000000 (@ccoc_cu / @mf_cu) 124.2424242424240000000 ...more >>

hi, i wanted to know if there is a way with which we can grant a user, permission for querying,updating and deleting a database through a web page (using ASP) and restricting him any kind of access to the database through QUERY ANALYZER or ENTERPRISE MANAGER tools of SQL SERVER 2000. i.e. The...more >>