I get the following error in my VB6 app when executing a stored proc: "Syntax error converting the varchar value of 'c:\email\attachments\' to a column of datatype int." Here is my stored proc: ==================================== CREATE Procedure [Proc_getSiteDir] ( @usrPK int, @usrSi...more >>

Hello, I created a userdefined datatype called IncrInt1 as bigint. I want to use this data type in a UDF as follows: CREATE FUNCTION [Incre] (@Inc bigint) RETURNS bigint AS BEGIN Declare @Incr bigint If @Inc = null IncrInt1 = IncrInt1 set @Incr = IncrInt1 return @Incr EN...more >>

I have a table'(catlist) with 3 columns, catid, parentid and categoryname. I have a stored proc and am passing in @catid but want to get the parent category name. The parentid and catid are foreign keys. this is what i've got but it doesn't work!!! CREATE PROCEDURE dbo.GetParentCategory ( ...more >>

Hi, Need help again. Can anyone give me advise on IF, THEN, ELSE statements from SYBASE to MSSQL. I am at the business end of finishing this project and got stuck with a few stored procedures...see below...the if then else statement is where I'm up to. alter procedure "DBA".SP_PR_O...more >>

First recall your favorite answer for: What is TABLOCK for? How let's look at something BOL says: A table can be loaded concurrently by multiple clients if the table has no indexes and TABLOCK is specified. Let's see what MS Word's Thesaurus says: concurrently -- at the same time as ...more >>

Hi. How can I modify this statement to work in sql server? MOD((TRUNC(a.sql_date) - TRUNC (d.dts_start_day_ref)),d.days_in_schedule)+1 Thanks!!! Alex Ivascu ...more >>

Hi! If I have a sp that consists of an update statement followed by a select statement, why does it not return anything? Anyone got a simple example? Thanks ...more >>

What is the best way to use dates for comparison? I have the dates stored as datetime types in SQL, and need to do a select via stored proc mp_GetData (@instartDate as datetime, @inEndDate as datetime) as select * from table where somedatefield between @instartDate and @inEndDate 'doesnt seem to...more >>

Would it be possible to find out which person has sold the most items? table: item colums: item_id, item_desc, saleperson_id (null means item has not been sold, not null means has been sold by the person with the id) This query made sense to me that it should work but I got this error "Can...more >>

Hi we tried to load a xml file which have multiple same named elements (SubProductID) into a table. But we can only get the first row for each product (we tried all possible ways with OPENXML). How can we get all SubProductID for each product? Thanks for any ideas/suggestion/codes. FYI: If ...more >>

Hi, First of all sorry for this looong question... I have two tables 1: CMENVI - with data changes 2: BAS_EMPRESA - data table On BAS_EMPRESA I have the following triggers on insert, update. CREATE TRIGGER PSNI_BAS_EMPRESA ON BAS_EMPRESA FOR INSERT, UPDATE AS DECLARE @SEQUE...more >>

Newbie Q- someone suggested I use drop constraint [DF__Products__Price__7C4F7684] to remove a constraint, but I got an 'incorrect syntax near Constraint' error. The reason I need to do this is because I tried ALTER TABLE Products ALTER COLUMN Price Money but got the constraint mentioned ab...more >>

Hello. I am trying to improve the speed & efficiency of some Transact-SQL code I'm writing, but I'm not sure if it's possible. I'm using SQL Server 7.0. Basically, I'm using a cursor to loop through a list of sorted records, then update another field in that record based on @TotalVal and the ...more >>

Changing the nullability on a new, nonkey column re-creates the table in the database when you save the table or database diagram using Database Diagrams within SQL Server Enterprise Manager. -- does the preceding statement from Books Online in SQLSrver 2K mean that the data within the table is ...more >>

Hi, Is there any system table in which the statistics for the columns of a table are stored? I have read that the statblob column of the sysindexes table contains statistical information, but what about the statistics stored for columns that do not have index? are those statistics also stored ...more >>

Hello, I need to write an incremental number to a large table. I would like to create a UDF with a static var the increments itself and write that to the table in an Update SP. I would be grateful for any suggestions how to declare a static var in my UDF. Thanks, Rich *** Sent via D...more >>

Is it possible to use math in the ORDER BY clause? I need the percent of views by divide the number of bannerviews to the sum of all bannerviews and sort by the less viewed banner SELECT * FROM banner WHERE (startdate <= '2003-07-14' AND stopdate >= '2003-07-14') AND ad_group = '2' ORDER B...more >>

I have a table that contains several million records. There are several entries with the same information, how can I delete all records are duplicate and still only have one entry?...more >>

PROBLEM: The "deleted" table is empty when a child table's delete trigger fires EXPECTED: The deleted table to contain all the rows from the DB that are being deleted EXPLANATION: If I have two tables (Orders and OrderItems) [Orders:] OrderID OrderDate [OrderItems:] OrderItemID ...more >>

I am trying to write a trigger that is called on row update. The problem is that I need to compare the old value of a certain column with the new value. Is this possible? I thought that if I selected from the table and compared the value with "inserted" I could accomplish what I want, but it se...more >>

I am running Sql Server 2000 and have an ASP page that queries some of the tables from Sql and returns their values to text boxes or text areas in a web form. This all seems to be working fine except for one case, if there is a carriage return/line feed stored in the field in the table. When tha...more >>

I'm working with MS SQL Server 2000. I have read-only rights and can't write anything on the server. I need the top X waiting times by Division. Order of the returned rows is not important since I can order the data in FoxPro once I've got it. All help is appreciated. CREATE Table Test (Pk I, ...more >>

Hi, We have a web server say "server1" and a database server say "server2". I have installed a DLL say "test.dll" as a COM+ component on server1. The test.dll has also been registered on server2. test.dll is a vb6 dll. How do I use sp_OACreate to instantiate test.dll from server1 from ...more >>

I want to create a SQL Server 2K stored procedure to return a recordset to my program, but am not sure what method to use, or how to determine which method is more efficient. I will pass the SP a single paramter (@ExID). I'm trying to determine what is more efficient: 1) Creating a join bet...more >>

Hi all, I have installed Windows 2000 and SQL Server 2000 (both fully updated) on a dual-CPU Xeon Dell 2650, but I am getting very strange behaviour from SQL Server. When SQL Server starts CPU utilization jumps straight to 100%, albeit SQL is under no load whatsoever -- if I stop SQL Se...more >>

Hi all, I would like to duplicate a table within the same database. I want to table structure, constraints, index, and the data to be copied over also. Is there any SQL statement to do it? I know I can duplicate a table structure and its data by using Select * into [New Table] From [Old Tab...more >>

How can I return a table when a stored proc calls another stored proc? I know the example below does not work, but it clarifies what I need. I will be able to use the returned table for use in an Update statement, so I wanted to aviod returning a cursor. IE:.... ----------------------------...more >>

my difficulty is to find the total value of the connections in the format hh:mm:ss. I get to find like this the time of the connections for each connection of the name: select convert(char, DHFimLigacao -DHInicioLigacao, 108) the TempoLigacao from controls. The one that I want is to add the tot...more >>

How can I catch a resultset (in T-SQL) that returns from a stored procedure? A local variable with the datatype 'table' doesnt solve the problem, because declare @tab table(table definition ...) insert into @tab exec master..xp_msver will throw the errormessage 'EXECUTE cannot be used a...more >>

Hi all, Quick question, I can't find my SQL book and its driving me mad. I have a StartDate field and a FinishDate field in my table and I need to know if Todays Date is between or equal to the above fields. I tried (GetDate() Between StartDate and FinishDate) but not working. What am I d...more >>

I'm having problems using ADO to return any schema information for a SQL Server database. Here's my code (VB6): Dim rs As ADODB.Recordset Dim con As ADODB.Connection Set con = New ADODB.Connection con.Open "Provider=SQLOLEDB.1;" & _ "Integrated Security=SSPI;" & _ ...more >>

Charindex should return INT or, if its not found, NULL I can´t figure it out on a stored procedure... select @tags = Inserted.Custom16 FROM inserted IF not(CharIndex('<L',@tags,1) > 0) select @mycar = '<LL>' This actually works IF the Custom16 is not null to beggin with, if c...more >>

Hi, I'm writing an SQL script to update the structure of a database. One of the tasks I need to do is insert a new field into a table, but before I try to insert I want to check if the field has already been added to the table. I've managed to do it by using sp_columns, storing its re...more >>

hie there. i've searched high n low for some sample coding on this matter, but i can't seem to get any. i have 3 views in sql server that i want to export it to excel using windows form. how can i do this? i need some guidelines, pls help as it is urgent. thanx! *** Sent via Developersde...more >>

I have some questions about this stored procedure. 1. This parameter thing you can send in to the sp_executeSql can only handle things in the WHERE statements i guess? I cant send in for example table names in there? 2. If i build the query with dynamic table names and dynamic where stateme...more >>

Hi, I have a stored procedure which compiles fine in Isql utility provided by Sql-Server 7.0 However if I compile the utility in Query Analyzer (sql- server 2000), I am getting the invalid column errors(few line's last characters and " characters). I did this by cut/paste from notepad ...more >>

Hello, I have been knocking myself out trying to find the answer to what I thought was a simple question: What is the maximum number of page locks for SQL Server 2000? I have searched on BOL, all over the web, and cannot seem to find a straight answer to this. If someone can answer this I...more >>

i have a after update trigger which perform upadte on a summary table. sometimes, the number of affected records is more than one. but the trigger only be excuted once. i want to implement 'FOR EACH ROW' trigger like Oracle does in SQL server. anyone got idea? instead of writing a cur...more >>