Dear Experts, I have posted this problem few minutes back i have added few more things in this so this is updated version of my last post so pls do not reffer my previous post. I am facing one problem on my client loactions Please advise me . At our client we have SQL 2000 and client app...more >>

Hi, I have a select that give me this result: EJ. SELECT Codigo + ' ' + 'CANT:' + ' ' + CAST(SUM(Quantity) AS VARCHAR)+ CHAR(9) + 'PEDIMENTO:' + ' ' + Pedimento + ' ' + 'FECHA:' + CAST((SELECT DAY(FIA)) AS VARCHAR) + '/' + CAST((SELECT MONTH(FIA)) AS VARCHAR) + '/' + CAST((SELECT YEAR(FI...more >>

When I run my backup job I am getting the following error message. Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed. I am running SQL Server 2000. Plase advice. Ankush ...more >>

Hola grupo, Tengo un SP en T-SQL que env=EDa Mail por medio de CDOSYS, ALTER PROCEDURE dbo.Send_Mail_Function--************* Create the = CDO.Message Object *******************EXEC @hr =3D sp_OACreate = 'CDO.Message', @iMsg OUT--***************Configuring the Message Object = **************...more >>

I posted this in ACCESS.Reports, but I think it belongs here: Using Access 2000 database project with Sql Server 2000 Back end. Issue: A drafting firm has 60 boxes divided into 36 - 2" squares - 6 across and 6 down for storing architectural / engineering plans. The Access Project tracks th...more >>

hi, does anyone know how to change a dts packeage's owner name on sql 2000? we have individuals that creates dts packages on production servers, and we would like to change the dts owner name from the individual accounts into server's own account name. many thanks! JJ...more >>

I am trying to import some data into a smalldatetime field type but receive the following error: Arithmetic overflow error converting expression to data type smalldatetime. It works fine if left as datetime, but smalldatetime causes the grief. Any suggestions as to how to find the offe...more >>

Hello, I am trying to use the sp_OAMethod on an ActiveX object property that returns an interface for an object. When I try to use a method on the object, I get the following "ODSOLE Extended Procedure" error: "The callee (server [not server application]) is not available and dis...more >>

Hi, I have noticed that my xion server is a hyper threaded server. Therefore in "Task manager" I see 2 CPU's when actually my server only has a single CPU. I think this is because the machine has registered two threads to handle the CPU. anyway, my question is does SQL server (if lic...more >>

I need to write a script that creates a bunch of indexes. Essentially it will do CREATE INDEX foo ON ... CREATE INDEX bar ON ... CREATE INDEX deedle ON ... CREATE INDEX dum ON .. until they are all created. The actual code used is not so linear, but it boils down to this. The probl...more >>

On compairing Insert vs Update, which statement causes more overhead to the DB. If i have a Table(col1 int primary key) And there are a million rows in it. Now if i do a insert and update which statement causes more overhead to the DB. ...more >>

I have a package....I right click, schedule it...and it fails - 'System cannot find file specified' Any ideas? ...more >>

i read that using the order by clause in sql could cause a performance hit because order by uses the tempdb. is this accurate?? i have an asp/sql server system - would it be better to do my ordering on the asp side (recordset object) or within sql server using the order by clause?? any tho...more >>

How to shrink the size of log file in SQL Server (I am talking of SQL Server log and not the database log). Currently my backup is failing and not sure why it is failing. Since the SQL Server Log file size is very huge and not possible to see in the enterprise manager. Is there any way I can shr...more >>

I have a situation where I have some scripts that are loaded via a Visual BASIC exe that's written by a 3rd party. These scripts contain INSERTs, UPDATEs, etc I had initially separated each DDL statement with a GO but the performance was horrendous. I removed the GOs and the 3rd party sta...more >>

Hello,I have a table (T)like the following: I Syn 1 AB 2 BC 3 DB 4 ACD 5 DBC What I want to do is replacing A with 1, B with 2, C with 3, D with 4, so I wrote like this: SELECT i as 'index', 'trans'= case when patinde...more >>

Using TSQL Declare @MyErrInt as int SELECT @MyErrInt = @@ERROR will capture the error number How do I convert the error number into the actual system error message for storage in a varchar ... WITH all proper % and * substutions having occurred? For example: Cannot update i...more >>

Is there a way to allow BCP to export to a file, and include the 'first row has column names' ? ...more >>

First a disclamer that I didn't design these tables and I can not change them (3rd party). Second the table info. Parcel table has one record for each parcel. Address table has many records per parcel. Address table looks like... Parcel RecordNumber Type (Mailing/Deed/Contract) Address...more >>

I've got an addin that generates vb6 code to create or update an sqlserver database. It relies on the sysdepends table to determine the order in which everything should be created. I've found out that sysdepends is an incredibly unreliable beast. To get around that I recompile each view, stored ...more >>

I have a stored procedure A which performs a few inserts and outputs an select @@identity as id I have a stored procedure B which calls stores procedure A However I want to suppress the ouput of stored procedure A inside stored procedure B. I tried using set nocount on but this only ...more >>

I need to check a date field for a consumer on insert and if it is the day is already entered for same consumer I need a alert to be given to the scheduler telling them that two appointments on the same day for a consumer is not allowed. This needs to be on a trigger after insert and also...more >>

One of the main use of Stored Procedures is, it is a precompiled code which is stored in cache memory and as a result faster query execution. My question is suppose if i have (say) 100 Procedures, are all these stored procedures stored in Cache memory? Thanks ...more >>

Hi: In a trigger, I'm updating a table in another database. I reference the table using correct syntax, but it appears to hang server- any ideas? Thanks, Charlie ...more >>

Given a certain date, how do I find the last date of that particular month? Also, is there a simpler way of getting the first day of the month besides this? select ltrim(datepart(mm,getdate()))+'/1/'+ltrim(datepart (yy,getdate())) Thanks so much!...more >>

I'm trying to store values like what I have listed below and am not sure what design I should use. I initial thought is to store then exactly as they are below by separating each value with some sort of delimiter, but I'm not sure if this is really best. RecordID SomeColumn 1 75 2 75/11...more >>

Hi I encountered problem, i want to select records with its row number ( or serial no) but i don't know sql server provides it or not as in oracle rowid and rownum is available. is in sql such thing is available or not. Thanks in advance ...more >>

Hi I encountered on problem, i want to select records with its row number ( or serial no) but i don't know sql server provides it or not as in oracle rowid and rownum is available. is in sql such thing is available or not. Thanks in advance ...more >>

Hi I encountered on problem, i want to select records with its row number ( or serial no) but i don't know sql server provides it or not as in oracle rowid and rownum is available. is in sql such thing is available or not. Thanks in advance ...more >>

Here's a DDL snippet of two tables that need to be kept in sync. i.e. Training and Live Training: CREATE TABLE Variances ( SessionGUID uniqueidentifier NOT NULL ) CREATE UNIQUE CLUSTERED INDEX [IX_Variances_SessionGUID] ON [dbo].[Variances]([SessionGUID]) Live: CREATE TABLE Var...more >>

We are using Great Plains to do our accounting. I'm creating a custom aging report and want to do the calculations as a stored proc. Every company is a separate database in SqlServer. Also, there are upgrade issues with GP involving custom stored proc's on that companies database so I'd like t...more >>

We are using Great Plains to do our accounting. I'm creating a custom aging report and want to do the calculations as a stored proc. Every company is a separate database in SqlServer. Also, there are upgrade issues with GP involving custom stored proc's on that companies database so I'd like t...more >>

I have one question? I can't decide what is better way, to store the word documents(or some other) into the SQL database or store them on the hard disc? If user in my application use search option, it should be able to search for the specific word in the documents. I know, I can search for ...more >>

I found this article on Microsoft newsgroups today. I have a problem accessing a user defined function on a linked server. I can run a query like "select * from LinkedServerName.DBName.dbo.TableName" which returns result, but when I try to run "select LinkedServerName.DBName.dbo.FunctionNam...more >>

I need to replace some underscores in descriptions with spaces, but am having issues because the field name is "data" and I am receiving errors. I cannot change the field name (this is a 3rd party product), so any suggestions on how to replace the underscores with spaces while working around ...more >>

Successfully ran DTS tool and imported spreadsheet of customers into SQL Server 2k database table called "sheet1$". Changed the type and length values of the columns on table "sheet1$" to match identical to type and length values of columns on table "customers". Executed the SQL Statement...more >>

Hi all, I have a problem accessing a user defined function on a linked server. I can run a query like "select * from LinkedServerName.DBName.dbo.TableName" which returns result, but when I try to run "select LinkedServerName.DBName.dbo.FunctionName(FieldNameOnLocalServer) from TableNameOnLo...more >>

I try to perform a single query in a single table (sqlserver 2000), but i need to know the line number. I've tried this, but it gives an error. Any idea ? Thanks in advance, Kind regards, Guy declare @i int select @i=isNull(@i,0)+1,c_nom from cli ...more >>

Hi How do I get a list of identities from a batch insert i.e. CREATE TABLE #maintable (ID int IDENTITY, Name) INSERT INTO #maintable (ID, Name) SELECT (Name) FROM #importtable 'Name' column may not be unique in the #maintable, there may already be entries so I only want the IDs creat...more >>

Hi, I am using MSSQL 2000 and table triggers to enforce business rules. I am having a strange error when the condition in the trigger is not met. I get the following error: The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION. In my trigger, I execute the followin...more >>

when i use the following it adds records that only have the username and dbuserstamp filled in. So I think the issue is the "insert into" but I do not know what to change that to. CREATE TRIGGER [auditOn] ON [dbo].[tblEmployee] FOR UPDATE AS insert into tblEmployee (dbuserstamp, UserN...more >>

String or binary data would be truncated. The statement has been terminated. How do I get this to run anyway around this. Insert Into Agent ([Agent], [AgentLoc], [AppointmentDate], [BusinessName], [BusinessPhone1], [BusinessPhone2], [BusinessType], [CommercialProp], [Co...more >>

I am using SQL Server 2000. I have a stored procedure that inserts data into a table. The table contains a unique index. Now the stored proc takes a comma delimited string of numbers and loops thru them to insert them into a table. I call this SP from VB 6.0. The string may contain values...more >>

I am trying to add auditing to each table in the db. I would like to add the suser_sname and a date stamp to each record that is updated and/or inserted. My trigger is not working and it leaves the table uneditable. Any assistance as to where I am going wrong is appreciated. trigger 1 ...more >>

Hi all I've only come accross this recently, but is it true that INTERSECT isn't supported in SQL Server? The Intersect function is doesn't seem to want to work with the 2 queries i'm trying to compare (if it is, indeed, even supposed to do this!) and i cant use techiniques using NOT IN or ...more >>

I have a query joining two tables: select a.* from a, b where a.col1 like b.col1 and b.col2 = 1 It returns nothing. If I remove the "and b.col2 = 1" then I get data returned. I cannot find any reference to this in docs or on web. Has anyone seen similar? Anyone have a workaround? ...more >>

I've been tasked with implementing a stored procedure that does a full-text search on a column called Part_Description. This column is user-entered, and as such there could be a BUNCH of different ways to represent the same thing. I need a stored procedure that will return a list of anything that...more >>

hello i need to fetch to cursor and return entire row to put it in variable not in multiple variable (like %rowtype in oracle) how can i do that in sql server thank you...more >>

Hello, I can disable and enable triggers and constraints with sql-statements shown below. ALTER TABLE mytable NOCHECK CONSTRAINT ALL ALTER TABLE mytable DISABLE TRIGGER ALL ALTER TABLE mytable CHECK CONSTRAINT ALL ALTER TABLE mytable ENABLE TRIGGER ALL How can I check if t...more >>

Hi I have a table that has three columns in it. What I want to do is write a stored procedure that will read this table and plug in the column values from it to another update query where clause. For example Table 1 has White blue red yellow green black I want to write a query that...more >>

SQL Server 2000 SP3a I have a query that is a little beyond my capabilities at this time. If someone could point me in the right direction I would greatly appreciate it. I have a table named supplements which has 7 columns. The two that I am working on are called "cr" int, and "suppId" char-...more >>

Hi, I am looking for a way to pick specific records from a view I have created. The data from my view looks like this: machineid serverdate kioskdate kiosktime ITG70000011 2004-09-30 01:59:52.000 2004-09-28 00:00:00.000 1900-01-01 02:02:28.000 ITG70000011 200...more >>

Hello everybody, Configuration: select @@version --------------------------------------------------------------------- Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 21...more >>

Users can enter different fields (or no fields) in the GUI and the query I use has LIKE clauses for each. The proc looks something like this. Is there a more effecient way to do this? The code runs slow sometimes. Thanks. IF @fname IS NULL SET @fname = '%' IF @lname IS NULL SET @lname = ...more >>

hello i have 1 table which have an image column Table1 (id1 int, imCol1 image) i want to update the contents of a record with the ones of another record this is what i do : EXEC sp_dboption 'dbName, 'select into/bulkcopy', 'true' DECLARE @ptrval binary(16),@ptrvalTarget binary(16) SELE...more >>

Hello Experts, I'm trying to find which objects are different between two DB's and the result is always "true" even when I use the equal or notequal operator for text column. above example query is for a specific object Select distinct d.id from [OTHERSERVER].MYDB.dbo.syscomments d ...more >>

Hi, Any tips on extracting all the records in a table from 19:00 the previous day. I have a DATETIME field in the table containing the creation dates, the format is 2004-09-30 07:23:59.840 (I posted this before but the results have been lost) much obliged...more >>

Dear All, Last night our test server had a crash, and now will be off line for a couple of days. We have backups of the server, however we have a problem. The temporary server we have been given is an old one and only has one drive, whereas the restore is expecting two drives, is there a ...more >>

Hello, Programming in asp & SQL Server 7, I often display the result of my select stored proc in graphic arrays. These arrays can often be sorted. Usually, I send, as a parameter to my stored proc the kind of sort : what column, ascending, descending etc... So, only the part "ORDER BY..." o...more >>

Hi, Window2000 server +SQL server 2000 I am trying to migrate my current SQL server application to a new setup, my setup procedure is as below procedure: 1.Install OS win2000 + SP4 2. Install SQL server 2000 +SP2 3. backup MASTER db and MyDB on the old server, then copy these backup to new...more >>

Hi, I'm new to sqlserver - I currently have sql server (query analyzer) in the office, though it seems to be used primarily for database work on our central main database system and requires a special network password. Is there some way to hook it up to a local application in my computer lik...more >>

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TestTbl]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[TestTbl] GO CREATE TABLE [dbo].[TestTbl] ( [Tcount] [int] NOT NULL , [Tdate] [datetime] NOT NULL ) ON [PRIMARY] GO SET NOCOUNT ON INSERT IN...more >>

Dear all: What is the Sql Server's equivilant to Oracle's instruction Execute immediate 'select FieldName from TableName where Condition' into variable Thank you ...more >>

Dear All: How to emulate Oracle's %rowtype datatype in Sql Server??? Thanks, ...more >>