I am trying to programically recover from a SQL 2000 critical error while using cursors to traverse a recordset returned by a select statement. I believe the select statement by default simply returns what is in my table from top to bottom (as I have no order clause in my statement). During my...more >>

Hi, I wonder how to pass to procedure something that could be used in the IN clause, for example: I wan't to call EXEC test @tmp where @tmp = '01', '02', '03', '04' then i wan't to do (in procedure test): Select * from something where value IN @tmp but it didn't work - when i write it...more >>

Hi One and All, Have a interesting query I am trying to sort out, I have one table that looks something like this. NAME PAPER Buggs Bunny Mirror Buggs Bunny Daily Mail Buggs Bunny The Star Taz The Observer Taz ...more >>

Hi, I am getting following error Microsoft OLE DB Provider for SQL Server error '80040e07' Operand type clash: nchar is incompatible with image When using .... Set ThumbParam = Server.CreateObject("adodb.parameter") ThumbParam.Name = "image_thumb" ThumbParam.Type = 128 ' adBinary Thum...more >>

Hi SQL Gurus, I want to implement Logical Delete in Transaction tables by UPDATE a column 'DelStatus' into 1, Default = 0. e.g : OrderHeader (Parent) & OrderDetail (Child) I plan to use INSTEAD OF TRIGGER to do the UPDATE. But at the same Time I want to use CASCADE DELETE on OrderDetail. So...more >>

Hello New for SQL, I am using the "select lastname + firstname + mi as Fullname" context in a select query. My problem is, if the middle init is NULL, my whole name field is NULL. How can I test for the mi being null, and alter the "FullName" column? like: if (mi) IS NULL FullName...more >>

Just a couple of questions about Procedures and using the in VB.NET... 1. If you have a procedure which uses a CURSOR and returns multiple rows.... are all the values returned as Parameters. 2. In VB.NET, when calling a stored procedure which uses a CURSOR and returns multipe rows, are you a...more >>

Hi All Win 2kpro SQL 2k Dev ed I got 2 tables and I want to know how to do the FK references between them as I keep getting errors when I try to create the second key. create table MAIN ( ID int identify(1,1) primary key , AnotherField varchar(50) , andanother varchar(50) , ) ...more >>

In the following sample I think I have been able to duplicate a production situation as an example of the issue. We have two key tables involved. The problem is trying to identify the Appropriate Tax Schedule to assign to a transaction that contains only the pieces of the Tax Schedule, which...more >>

Hi all, how can I do this? 2 instances of the same program will select one row from a table and update it. One instance shouldn't select the row that the other one has already selected. I'm using an ado recordset with something like this: connection.BeginTrans recordset.open (se...more >>

Hello there Is there a way to duplicate items(Tables, views etc...) on another names on the same database? lile copy and paste with diffrent name ...more >>

Hi NG, How can I access values/records which are returned by EXEC('Select_Statement') within a transaction within a stored procedure??? My sp looks like this (without create procedure, declarations, etc.): BEGIN TRANSACTION GetDataSet CREATE TABLE #TemporaryTable ( ...more >>

When I run the follow query that uses a linked server inside the view, the response time is abnormally high. UPDATE vwPrevVendas_Orcados SET DbOrcadoVendedor1 = 0, DbOrcadoVendedor2 = 0, DbOrcadoVendedorR1 = 0, DbOrcadoVendedorR2 = 0 WHERE InAnoMes = '200307' AND TxCdAr...more >>

Hello there I have some trigger for table that act on delete. The trigger should do diffrent things in two cases Is there a way on the trigger to know which Store Procedure run the action that activate the trigger? If not is there a way to tell the trigger how to act outside? any hel...more >>

Hi, I've come across a problem in MSSQL2K when using large 'in' statements of the form: select * from MyTable where MyTablePrimaryKeyField in (123, 4325, 32143, 2312, 234.... ) This query runs quite fast as long as the number of entries in the 'in' clause is small, but as soon as it b...more >>

Below is a summary of the steps I've taken to create and access a linked server. The error is at the bottom of the page. First, I create the linked server like this Dim _createsrv As New OleDbCommand("sp_addlinkedserver", _conn) _createsrv.Parameters.Add("@server", OleDbType.VarChar) _crea...more >>

Microsoft User this is the latest version of security update, the "September 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to ...more >>

I have a serious problem with one of my ASP/SQL Server applications. In the last year, we've discovered that on at least two occasions, thousands of records have been overwritten by valid but incorrect data. I've found the likely suspect in my code. In one dialog the user, can make a batch o...more >>

I have small question I have SQL Table and I am looking a net send message to my compouter for any insert on this table, how can I wrtie a insert trigger on this table to send me a net send message thanks ...more >>

I have a phone number field, where the phone numbers are in the following format: xxx-xxx-xxx. I need to query the phone numbers for possible area codes . The user will either input one or several area codes, separated by a comma, to get a list of those phone numbers for the desired area code...more >>

Hi all I need to create a procedure that output month number depend on my inputs let's say @p1 is current month number. @p2 is # of quarters going backward (3 month per quarter) if I input @p1 =12 , @p2=3 (it means 12 - 3 quarters so it equal to 3 ) proc output will be 3 if I...more >>

Dear All I have a stored procedure which query a table in a link server. I want to check the availability of the link server. If the link is broken, the SP should do something else. How can I do it? Thanks in advance Ken ...more >>

We would like to view a blob as a table. We have tried several approaches... We have written XP which can output table... but we want to make it transparent for a user so he will be able not only call XP but uses select statement to view data. We tried UDF ( but XP cannot return res...more >>

Microsoft Customer this is the latest version of security update, the "September 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to h...more >>

If I cannot use Copy DB Wizard or DTS, what is the best way to copy logins from one S2K server to another? BCP out & in? Will passwords be intact? Bill ...more >>

what is a readonly cursor and what is not a readonly cursor? ...more >>

i run the query on a linked server: SELECT * FROM MyLinkedServer.MyDatabase.dbo.Users WHERE Username = 'Ian' and i see in query analyzier the remote query issued contains the where clause, and the one matching row is returned. But if i run SELECT * FROM MyLinkedServer.MyDatabase.dbo....more >>

Hi all - I have a data set with a bunch of serial numbers in it. The serial numbers represent the build up in a manufacturing process. L0_SN represents the Level 0 serial number. L1_SN represents the Level 1 serial number and so on. Level 0 things are "inside" Level 1 which are "inside" Leve...more >>

I have a table which contains a datetime field. Via ASP, I am trying to (unsuccessfully) get a list of rows where the datetime field is within the past 48 hours... the logic looks something like this: SELECT * FROM table WHERE time >= DateTime.Now.AddDays(-2) How can I accomplish this from ...more >>

I have written a short procedure that incorporates the xp_sendmail command. I have the result set in a file attachment. I want to forward this email to a fax server that does attachment rendering. However, it won't render the attached .txt file because it is in Unicode character set. If I ...more >>

Hi Freinds, SQL 2000 CREATE PROCEDURE test AS SELECT te.ntimecardid , vl.cbranchsystemid, vl.cbranchcorpid, a.cpayrollid, app.cssn, convert(char(10),te.dweekend,101) as dweekend , sum(te.nregular) as nsumregular, te.npayrate, te.nbillrate ,' ' as NBCODE1, sum(te.novertime) as nsumoverti...more >>

I have the following Create Table procedure: CREATE TABLE PersonalDays (PersonalDayID INT NOT NULL PRIMARY KEY, EmployeeID INT NOT NULL, DateEarned DATETIME NOT NULL, AmountEarned DECIMAL(2,1) NOT NULL, ReasonEarned VARCHAR(30) NOT NULL, DateTakenOne DATETIME, AmountUsedOne DECIMA...more >>

slq-svr-2k query analyzer connected to remote instance of sql-svr-2k. credentials = "sa" in a (user-created) db on this instance, use qa to attempt to delete a sp owned by "dbo" ... why would qa state that "user does not permission to perform this operation on procedure xxxxx" ? ...more >>

Hi Freinds, SQL 2000 I have a SP where I am doing: select a.f1 , b.f2 , c.f3 into #tmp1 from a ineer join b on a.a = b.b inner join c on a.a = c.c select * from #tmp1 --------------> is ok and I am getting all fileds and can see field name too select f1 , f2 from #tmp1 ----------> is ok...more >>

I use this statement to retrieve student's most recent exam date if taken within last 20 days: select Name, max(DateExam) as DT from StudentExam where DT > getdate()-20 group by Name But it says "Invalid column name 'DT' ". How do I achieve it?...more >>

I'm running a job that executes a DTS package in SQLDMO and having a hard time finding where the error messages are sent. I have looked a little at the Alert object but its not making sense, anyone know how to find out where the DTS package error messages are sent so I can get them in SQL...more >>

I'm sure I have the syntax wrong, but is the below possible? What I'm trying to do is... If @Category is submitted - the run this code. But if @Category AND @Manuf are both submitted - then run this code. Can anyone give me boost here? thanks! CREATE PROCEDURE sp_ShowCategory @Category varch...more >>

Sorry for my bad English but I'm Italian... I have a question for you... I delete all record of table but I don't set autonumber field to start with value 1.... What do you do to set a values of autonumber colomns with Query Analizer or Enterprise Manager ??? ...more >>

Thanks for your help. I've used openrowset to open local access db in .net, but i have multi-user used themselfs access db and they all want update the data from their access db to sqlserver. How to do it and i cant use sa account? please help me. ...more >>

I have the following Create Table procedure: CREATE TABLE PersonalDays (PersonalDayID INT PRIMARY KEY, DateEarned DATETIME, AmountEarned DECIMAL(2,1), DateTakenOne DATETIME, AmountTakenOne DECIMAL(2,1), DateTakenTwo DATETIME, AmountTakenTwo ...more >>

Hi All & Greetings. I am hoping someone can quickly show me a stored proc to INSERT rescords in the following scenario. IN SQL2K, we have the Northwind DB, with 2 tables: Products and Categories. I want to enter a New record in Products table. But, I need toenter the CategoryID based on the...more >>

Objective: Create a stored procedure which will return the selected records and a record count of all of the records and use that stored procedure in a ASP.Net application using a datagrid. I created a simple Proc for returning the rows and I successfully loaded those records into a dataset an...more >>

Hello, Can someone assist me with some code that will change a date from: 1:43:31PM to 1:40:00PM 1:53:31PM to 1:50:00PM I will be putting this into a trigger that corrects the date into the proper table. I appreciate the time. Ken ...more >>

How can I SELECT the old (but still valid value) of a row which another connection is still updating? Example. 1. Connection begin transaction update Countries set CountryShort='USA' where CountryID=1 2. Connection select * from Countries ===> blocking, beca...more >>

What do you say to a person who believes that putting logic into stored procedures is not ultimately scaleable? eg He believes the database is the bottleneck and therefore all interaction with it should be kept simple so as not to tax the server, with any kind of data manipulation logic implem...more >>

if I need to order by multiple columns within a case statement, is there a way? Ex: order by case id when 1 then col1, col2 else col3 end ...more >>

I have a script (someone sent it to me, though I think it is Oracle) for loading a flat file into an existing table. I don't know much about SQL Server but have spent a year on this project now in SQL Server learning as I go along. All my prior experience w/ data imports is from a define...more >>

Hi, I have an integer field called id which has values from 1 to 1000. I want to convert it to char(4) and zero fill the blank spaces for e.g. 1 should be converted to 0001. Can u let me know how to do it using sql? An early reply would be appreciated. Regards, Venugopal ...more >>

Hi, When I use DTS to Import/Export the data the triggers are not getting fired. Is there any way to make them fired while using DTS. Thanks and Regards, Atiq Rahman Database Administrator Zuhair Fayez Partnership *** Sent via Developersdex http://www.developersdex.com *** Don't...more >>

Hi all, Jacco, you've helped me on this the most, it's pretty much the last question I have for you, and thanks for all your help, and Drebin as well. SELECT CASE WHEN $winners > 25 THEN 'No prize :'(' WHEN (entrantID -10) % 6 = 0 THEN 'Prize A!!' WHEN (entrantID -10) % 3...more >>

I've got this great php/mysql application I need to port to asp.net/mssql2000 I've noticed mssql text field does not store anywhere what mysql's text field stores. Some of my html textarea entries are many records/pages long - mainly used to store technical docs and code. Somebody suggestio...more >>

In a select statement, i am using a user defined function in the where clause. The function has no arguments and returns a string. The function is having two select statements. The function will always returns same value. But while executing select statement having this function in the wh...more >>

To All: I have this query running okay for some months now and then suddenly this error: Intra-query parallelism caused your server command (process ID #126) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1). This is the query: s...more >>

Hi Sql Gurus, (This is a repost because I am still confused) This is a best prectice question : If a transaction is DELETED from the system (e.g : Order, Voucher etc), While the Accounting department insist that 'deleted number' must be 'explainable' what is your best practice : (1) If...more >>

Dear all, I have an asp web application that submits SQL commands to our SQL 7 database. When trying to run one of the users queries with a date selection where the days > 12 I get the following error: [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a da...more >>

Hi, Is there any way to get a unique to a column for each row inserted to a table ? Insert INTO dbo.Table1 (UniqueKey, -- USERID, STATUS, etc.. select ...more >>

There are two tables, table A has PK on ID column, let's say the value is 10 and 20, table B have Unique Constraint on ID column, with same value, I defined a update trigger on table A so when I update table A's ID column's value, the trigger update table B's ID's value too. It works when I upd...more >>