I'm trying execute some sql statements in a loop. The table I'm operating on is very large, so I'm updating the table in batches of 10000 rows at a time. I get an error after processing for a while: Arithmetic overflow error converting numeric to data type numeric.Query Analyzer points to th...more >>

CodEquipPerif CodEquip CodPerif ------------- ----------- ----------- 5 2 7 21 2 8 24 3 7 6 3 8 22 3 9 23 3 10 7 4 10 25 4 10 26 5 8 8 5 10 9 6 7 10 7 9 11 8 10 13 9 7 12 9 9 14 10 7 15 11 7 16 12 7 17 12 10 (19 row(s) affected) I need a select that shows me the registr...more >>

Hello All, I have two tables, parent and child. The parent table includes 5 primary keys and the child table contains a composite key. When I try to update some primary keys or foreign keys in both tables, I got following error massages: "UPDATE statement conflicted with TABLE F...more >>

I want to allow client backup the data to local disk, but the SQL Server backup file to Server disk, how to transfer the data from server to client ? thanks! ...more >>

I have two tables one with a list of localities (postcode, suburb, state) the other with a listing of user selections from the first table. I require a listing of localities excluding those already in the selections table. Thanks Create Table Localitles ( Postcode VarChar(4), Suburb Va...more >>

Can you tell me please, why this script gives me: "Server: Msg 170, Level 15, State 1, Line 23 Line 23: Incorrect syntax near '@strDataDbName'."? Thank you a lot! tuzo DECLARE @strDbName nvarchar(255) DECLARE @strDataDbName nvarchar(255) DECLARE @strDataFileName nvarchar(255) DECLARE...more >>

SQL2000 trigger code performs the update, for example.... update openrowset('microsoft.jet.oledb.4.0','excel 8.0;database=c:\sample.xls', [my_range]) set f1 = 100 As were in a trigger and therefore in a transaction, SQL decides to elevate the current transaction to a distributed transact...more >>

when trying to execute this: SET @SQL = "truncate table " + @vTable EXEC @SQL I get this: "Could not find stored procedure 'truncate table <tablename>" why is it not executing the query ? ...more >>

Dates are stored as yyyymmdd hh:mm:ss.000 I only want to retrieve the date part of the field back yyyymmdd How do I do this???? Cheers Mark ...more >>

Hi All, I use the following query to collect data from two of my DB tables SELECT distinct qh.Query_No, qh.Query_Date, qh.date_closed, qh.Managed_By, qc.Comment, qc.comment_Date FROM dbo.T_Query_Header qh left outer JOIN dbo.T_Query_Comments qc ON qh.Query_No = qc.Query_No WHERE (qh.Stat...more >>

Hi, I am writing a cursor, reading from one table and update the order value count into another table. After which I will using this summary table to generate a report using Crystal Report. But I encounter an error as below. Server: Msg 137, Level 15, State 2, Line 1 Must declare ...more >>

Hi, I want to run this within my sotred procedure: EXEC SP_FULLTEXT_TABLE @tabname='DocumentTable', @action='start_full' Such that an authorized user can connect to database, and run this stored procedure to initiate a rebuild of index. The problem is I get an exception in my C# code wh...more >>

hi, working on sql server 2000. Can you shrink tempdb's log and data file size at any time during the day on production servers? Will this impact other user's jobs on the server? many thanks. JJ...more >>

Hi ! I have a problem, but is hard to explain. I will try it. I have a table called Sales and it have 2 indexes. One day the query's over this table begins to be slowly without explanation. I have many installations of my system and this table works ok in all of them except one, and many of t...more >>

Hi, I'd like to know if using alot of Cursors and Temp Tables in TSQL can affect the performance. Does MS recommend that we do not use it that much? If so, can you please point me to a document on MS websites (preferred) or any other websites that don't recommend it. Thanks in advanced...more >>

if so, can you give me an example of syntax? right now, i am using a syntax like: SELECT ContractNum FROM contracts c INNER JOIN orgs o ON o.OrgNum = c.EmployerNum WHERE CONTAINS(o.OrgName, '"sports *"') and it doesn't return the org named 'Vitale Motorsports'... other than this behavio...more >>

Hello, why does this statement not work: .... ORDER BY CASE @sortby WHEN 'Datum' THEN p.Datum DESC END .... Is there any other way I could realize a descend order? Thanks in advance S. Willem ...more >>

Hello, I need some help with a query, and last time I posted to this newsgroup I received some great suggestions so I am trying again :) I have a table that I need to merge rows on based on a timeout on a datetime column and I can't figure out the sql statement to use. create table #sessi...more >>

G'day - I have a table that has one field in it - note, which is of type "TEXT". I want a trigger on it which does something with the last note entered. I'm getting an error saying that I can't access inserted.note, because it is of type text. What I want to know, is there any way that I ca...more >>

I am creating a complex view. I came to a point where I need to union the view with itself three times (with slight variations) to get the result set that I need. For example it's as if I have a view called "paragraph" that contains one record for each paragraph in a book. I need to cre...more >>

I have a stored procedure, in the name Proc_UpdateProductionShare. This procedure has got a select statement and a couple of update statements. I wish to debug this stored procedure. By debug i mean that i wish to step into the procedure code line by line and debug it. I also have used some loca...more >>

BlankHow many jobs can be supported at a time with sp_add_jobschedule ? --Amit...more >>

BlankHi I have one typical problem.... I am getting an XML string from which I am extracting n values. These = values I need to passed to another procedure for processing. As these = values are large in number so I was thinking to go store them in a = logical table and then pass them this tabl...more >>

Please help if you can. I have an isssue that don't know non-cursor answer. Let's say I have an order with order quantity of 9. I need to split this quantity into buckets of 2. So I need to have a a result set of 5 rows with 4 having quntity of 2 and one with 1. I can determine total splits b...more >>

Blank Hi Friends, Is there any thing called variant table data type. Or can we have our = own USER DEFINED DATA TYPE as TABLE? --Amit...more >>

Hello, I am trying to work through using the "bcp" utility. I am wanting to take the output of a query and write it to a file. My command looks like the following: master..xp_cmdshell 'bcp "exec master..sp_who2" queryout "H:\Temp\SQLTestOutput.txt" -c -U "Freddy" -P "Stuff" -S "\Fred\NetSDK" ...more >>

hi all! i've been running a stored procedure over and over again, and it's driving me insane! three out of four times, the proc runs in 1 second. the fourth time it will take 10 to 15 seconds. i've traced it, and the only difference i can see is that the long- running quer...more >>

Switching from Oracle to SQL*Server. I have several commands that use the (+) for outer joins, is there a similar operator in SQL*Server? ...more >>

OK ! After 2 days of trying to fathom out how to get xml rom a server I have amaged to do it. Next step is how do I get the xml into my table The member name releats ta a feilf name in my table , I assume have to translate into a field name and update value in table. Can someone offer a hint.....more >>

BlankHi Friends, I just want to know that is there any Tool available that can test our = PL/SQL scripts like we test our classes in Java using JUNIT. Also is = there any equivalent tool available for SQL SERVER like we have TOAD for = ORACLE. --Amit...more >>

Hello, I am running the following query. select * from northwind.dbo.orders where orderdate between '4/1/1996' and '5/1/1996' I need to run this query several time. For example: I need to know the entire month of April, breaking it down to each week of the month. select * from nort...more >>

Hello Folks, how can I get table information from an existing table in the SQL-Form "CREATE TABLE ...". I want to get this SQL String to install my table on another SQL-Server. I need it as an SQL-String. Only the structure of the table, not the data. Thanks! Andreas -- Andreas B...more >>

Hi, How to: SELECT X AS INFOX, 'bla bla bla text' AS SOMETHING, IF X > 0 THEN 'X is larger than 0' ELSE 'X ain't larger than 0' AS TEXTSTRING FROM .... WHERE... The main issue is to get one text-string (export to a kommaseparated file) if X is larger than 0 and another if not. Is t...more >>

Is there any way to apply the DISTINCT clause to just one field? EG, say I have data like this: Field1 Field2 aaa bbb aaa cdc bbb ppp ccc bbb How would I select a result set containing both fields but only applying the DISTINCT clause to Field1? 'SELECT DISTINCT Field1, ...more >>

In creating an SP and reporting on the output, I run a DTS package with xp_cmdshell to begin with. When this DTS package runs, I see all the steps of the package, as I normally would. I'd like to suppress this output, but can't think of a way to do it. Does anyone have a hint or better yet an ans...more >>

Hi Is it possible to do the following: SELECT Fieldname1,Fieldname2 FROM Tablename WHERE DISTINCT COUNT Fieldname1<4 I can't seem to write this, any ideas? -- Kind Regards Rikesh (W2K-SP3 / SQL2K) ...more >>

I'm writing an app to monitor various scheduled tasks using the xp_sqlagent_enum_jobs stored procedure to get the current status. I can't find a way of finding out whether the job errored out or not. Am I missing sometihng? Thanks ...more >>

Does SQL Server maintain an internal date/time stamp for each row in a table? I'm trying to figure out when a particular row was inserted into the database. Thanks </joel> ...more >>

I have written update trigggers for tables in my DB , but the problem is that I want to check if a real update is done on the table record. I mean , if there is no change on field values ( with the previous ones ) , I don't want to execute this Trigger ( or just to prevent Trigger functional cod...more >>

Hi, everybody! We are using NT 4.0 Server and MS SQL Server 6.5(sp5a) Today one of our developers said he suffered serious performance degradation when he tried to select more columns modifying existing query. He didn't modify FROM clause nor WHERE clause, just added some more columns. ...more >>

Date: Thu, 6 Nov 2003 12:46:09 +0100 Message-ID: <MPG.1a1452ee749514ab9896b4@news.algonet.se> X-Newsreader: MicroPlanet Gravity v2.10 Ok here goes, I'm trying to do quite easy operation here. I've inserted a number of values into @table1 (adminUserId int, thisString varchar(255)) and @tab...more >>

Whats wrong with query, trying to do multiple inserts. Insert into Applications (Server, Databases, Applications, ID, Services, Dependencies) Values ('Dataserver1','Eazyimage','NA','1','NA','NA') ('Dataserver1','fisdb','NA','1','NA','NA') ('Dataserver1','ITDepartment','NA','1','NA','NA') ('Dataser...more >>

Hello I have MS SQL Server 2000 version All databases with Collation Hebrew and i can write hebrew in all string columns i restored one database with different Collation ,so i can't write hebrew What can i do to resolve this problem ? how can i change Collation of this database ? Thank you...more >>

Hello: I'm creating a pretty complicated view, so it would take me too long to explain why do I need this, but I need to split the column name in the view like this: if the column name is CustNumber, I need to split it in two parts and present something like 'Cust' + 'Number'. I tried to do...more >>

I need to delete from lookup records where count(*)>1 and no records in table1. select NNumber, Pin, TNumber, SNumber from lookup group by NNumber, Pin, TNumber, SNumber having count(*) > 1 table1: num id lookup: NNumber Pin TNumber SNumber num id Both tables connected to each other ny...more >>

I have an event table structured as follows: Column Name Type ========== ==== Timestamp datetime SourceID nvarchar EventID nvarchar There is no primary key in the table. What I'm wan...more >>

when I test a query in the query analyzer it works fine when I run it in a stored procedure it fails to execute, no error messages. How can I determine why it's failing ?? sequence of events are: --tableB is populated then --tableA is populated then --the following query is run to remove u...more >>

I am trying to write a query that needs to have selected data excluded. Below is what I have, but not sure how to write it so that data is excluded. I'll try to explain. First the query. SELECT r1.StPSerial, stuff(stuff(r1.stpefdate, 3,0, '/'), 6,0,'/') as StPefdate, r1.StPTxType...more >>

Does anyone have any ideas how to modify the format file for importing "image" datatypes into sql server 2000 with "BULK INSERT"? The size of the imported data is varying (that's why i use "image"). thanks in advance ralf ...more >>

SQLServer(7.0): Hai everybody, I am new to programming . My doubts are 1. What is a Service Pack? 2. What is the purpose and usage of Service Packs 3. How can i know which Service Pack my SQL Server is using 4. If possible suggest me a web link on Basic of Service Packs ...more >>

Our application is returning these errors: --------------------------- SQL Server Message 10054 --------------------------- Possible network error: Write to SQL Server Failed. General network error. Check your documentation. Debugging info: MS_sqlfree - dbcursorclose Cursor(83) s...more >>

Hello I have MS SQL Server 2000 version All databases with Collation Hebrew and i can write hebrew in all string columns i restored one database with different Collation ,so i can't write hebrew What can i do to resolve this problem ? how can i change Collation of this database ? Thank you...more >>

Hello, Someone knows if is possible doing indexes over views ??? How ? Any help is greatly appreciated. ...more >>

I am writing a VB.NET program that runs an X-ray analyzer from a PC embedded in the analyzer. The program records results of the analysis every 2-3 minutes into a SQL Server database. In some cases, the SQL Server will be MSDE on the embedded PC, in other cases it may be another SQL Server on t...more >>

I have SQL database and I am trying to build an ACCESS front end for it to do some data editing, I have went in to the SQL Enteprise Manager and update 2 VARCHAR fields to 255 chatacters but my ACCESS field in my form will only allow 80 characters typed into it. I also decresed the size ...more >>

I am getting an error "The text, ntext, and image data types are invalid in this subquery or aggregate expression." I understand what this means, but I can't figure out a way around it. I have a number of columns that are ntext datatypes, below is an example of one of my current updates that is g...more >>

when will MSQL 2000 Server will run in other operating system , cause i got some people an clients that says that they will like that MSQL 2000 server could run in Linux. Will microsoft do this some day or is doing it? Carlos Gómez Collado ...more >>

I am using SQL 2000 as a backend to an Access 2000 application. Every once in a while, I get the "Error 2486: You can't carry out this action at the present time." and the screen form locks up. I can still close the form with the upper right X close button but I have to close the application an...more >>

I received the above error when attempting the (abbreviated) code below. I can't find anything about it in Books Online, nor in the Knowledge Base. I'm trying to find out what the specifics are, and what (if any) work arounds exist. I could conceivable convert dbo.procList2Table (which con...more >>

Folks, i have a SQL Statement with in a Cursor containing few variables, its giving me few errors. I am having an issue with the following statement EXEC('Select '+ @COUNT + ' = Count(*) from' + @tablename ) The error i get is :- Line 1: Incorrect syntax near '='. 0 ANy POinter would ...more >>

A text field in SQL 2000 Server that has lines of data such as: - Line 1 - Line 2 - Line 3 Shows up in the browser as - Line 1- Line 2- Line 3 How do we retain the formatting? TIA...more >>

My job is cleaning up data for the U.S. State Department. This involves writing complex T-SQL scripts (because that is the only tool my employer will allow, and each script must be self-contained, not calling other scripts). In order to get around the fact that you can't functionalize a ...more >>

Is it possible to return a varchar value from a stored procedure? I only know how to use the RETURN command and this returns only integer values....more >>

Can someone explain this behaviour of the REPLACE fucntion. using SQL2K SP2 create database a collate SQL_Latin1_General_CP1_CI_AS go use a go declare @c binary(100) set @c =cast( 'simon' + replicate (95, 0x00) as varbinary (100)) select @c select replace(@c ,0x00,0x20) select cast(@c...more >>

I have 8 fields in my table: century1,year1,month1,day1,century2,year2,month2,day2 20 01 12 01 Null Null Null Null 19 98 01 01 Null Null Null Null I need to set century2,century2,year2,month2,day2 to the next day after century1,year1,month1,day1, ...more >>

I am having problems writing a query that will return a distinct ServerName, with the lowest Weighted and Nominal values, ordered in that manner. Here is the DDL: CREATE TABLE Servers (ServerID int, ServerName varchar(50), Site int, Nominal float, Cost float, Weighted float) INSER...more >>

I have made a script in the query analyser. This works fine. Now I want to run the script from a job. So I put the script in a stored procedure. But the result is totaly different. Can anybody tell me what the difference is between a stored procedure and the query analyser? Thanks...more >>

I have two servers both running SQL Server 2000 and both with an identical database loaded. Both servers have 2.5 Gb memory, one with twin 1.4 Ghz P4 processors and one with twin 933Mhz P3 processors. The same query run against each server gives results back in 10 secs on the slower machine and su...more >>

I keep getting this EXCEPTION_ACCESS_VIOLATION and desparately need help! First, an overview of what I'm doing. This is a C app, using ODBC SqlXXX functions (if that makes a difference). This app opens a cursor on a database, then prepares querys for these two SQL statements: Query1: SELE...more >>

When bcp in from a text file, the order of records inserted seems to be of different order every execution even thought the input text file does not change. E.g input text file below, 1record1 2record2 3record3 4record4 5record5 When bcp in, the order in the table is 2,3,5,4,1 the first execut...more >>

Hi, what am I doing wrong? Brain dead for two days.... Table A0 = multicolumn 1000 rows column X is being searched and has identifier to Table A1 Table A1 = 3 column 100 rows SELECT A0.[X] FROM TableA0 as A0 where A0.[Y] = 1 group by A0.[X] order by A0.[X] works fine and returns...more >>

Hi there, Is it possible to initate a trigger with a check? Overall, I'm trying to make a duplicate of a "Product" record to a different database, whenever that product record in the original table has a special flag. So, I'm actually looking into something like: "whenever update occures, ...more >>

Datasets/tables can be created programmatically with C# or VB.NET and written to an existing database... But is there any way to create the database itself within a C# program (without embedding lots of SQL commands)....more >>

Hi All, I have an MFC application which has a spreadsheet on a CFormView. Currently teh spreadsheet load and saves it's data from a file. I want to store and retrieve the information for the spreadsheet in a SQLServer database. Can anyone give me some pointers as to how this can be done? Ar...more >>

Hi Dejan Sarka, thanx for the previous reply, but I cannot set the cell format in Excel. because i am directly saving the results from SQL query analyzer to .rpt extension file and opening it in XL sheet file. in this mean time i cannot set the cell format to text. it is true that by defa...more >>

Hello, We have implemented some code to capture error codes and put them in a database. We would now like to put in a view which will show the error message, and the error text to go with it. Does anyone know how to retrieve associated message that goes with an error code ? Thanks ...more >>

How do I return an ntext value from a stored procedure? I have a table which has an ntext column (among others). I am writing a stored procedure to return a single row's ntext value. I pass in the equivalent of the row ID for the where clause but I cannot return the ntext. Should it be a...more >>

Is there another way to execute a dynamic sql statement length more than 4000 avoiding sp_executesql limitation?...more >>

Hi all, How do i change a value in the inserted record using trigger. when i try using update it does not allow me !! Is there something equivalent in sql ? new.last_update_date = getdate() or set inserted .last_update_date = getdate() where last_update_date is a a column in ...more >>

Using: MS SQL Server 2000 (MSDE), VS.NET C++/C# Problem: I need to add an additional column to several databases on one SQL Server computer using Transact. I can add column using following command: ALTER TABLE myExistingTable ADD myNewColumn VARCHAR(250) NULL Is it possible to loop thro...more >>