HI all, In a client side envrionment I would retrieve a rowset, then create a loop do some processing, then skip to the next row, and then loop again How could I achieve this in a SP serverside Thanks Robert ...more >>

Hi all, I'm trying to pass some variables a custome error message I have created. But I'm not getting it write. How do I put place holder for the variable in the custom error message, and how do I pass it values. Thanks Robert ...more >>

I have three stored procedures that need to run nightly in SQL 7. The three procedures are not related; but to keep the procedures from running at the same time, I placed them as three steps of a single job. The first two steps are set to "Goto next step" on success; the last step is set to "Q...more >>

Hi I am having trouble writing/saving large text strings to a sql server 2000 table with 3 ntext columns. The command object (from adodb or ado.net) times out. I have looked through all the postings and help and it would seem that this should always be a problem and that using WRITETEXT or ...more >>

Hello, Please forgive the stupid question, but I'm a bit lost as to how to do this SQL. I have a table that creates an audit trail of access to a web site. Various actions are logged in the table, and I want to get some statistics out of them. The table looks like this... create table...more >>

Hi Is there is any way to run the stored procedure automatically from master database on daily at particular time. Thanks is advance Nizham ...more >>

Some facts: 1) I have an Access database (.mdb file) sitting on my harddrive. 2) I have Visual Studio 2005, Sql Server Express, and Sql Server Management Studio Express. 3) I do *not* have Microsoft Access. What I'm trying to do: I simply want to import the Access database into Sql Serve...more >>

Hi, I am using sp_oa for the first time. I have a stored procedure which uses a cursor and creates an object, for each record in the cursor I then call another stored procedure which has the sp_oamethod. Everything works as expected for the first 64 records. 65th record onward I get an erro...more >>

In my stored proc I was using SELECT count(*) on table for each user. This stored proc was finishing in 30 seconds. And customer was happy with the results. Now there is a change in my stored proc and i have to use SELECT count ( distinct startdate ) on that table where startdate is of type...more >>

I have two tables, each with about 50 columns. What's the easiest way to merge these tables together - I hope without having to manually enter all the columns. -- Michael Tissington http://www.oaklodge.com http://www.sqlview.net ...more >>

I am trying to use the new Sql 2005 feature Row_Number, and as a base point, used one of ScottGu's excellent blogs as a template. (http://weblogs.asp.net/scottgu/archive/2006/01/07/434787.aspx). I modified it for my purpose and as written below, works fine., HOWEVER .... There are a few fie...more >>

I have a datetime column named Timesheet.StartTime and a datetime column also named Employee.StartTime and I want to compare the time portion of each to see if the Timesheet.StartTime is later than the Employee.StartTime. My problem is that I only need to compare the time portion as the date ...more >>

I am trying to use the ALTER table Alter column command to set the default value of a column and to drop the default value of a column. can anyone pls give me the syntax.I tried the one given in sql help file but gives me a syntax error. thanks!...more >>

I keep getting a cursor operation conflict when calling the "Update" method of a recordsetup object using VB6 with a SQL Server 2000 DB. I have 0 "INSTEAD OF" triggers in the database, my user options are set to 0. I am running MDAC 2.81.117.0 on a Win XP SP2 machine against a Win 2003 Serve...more >>

hello, i'm making a sub-function (procedure actually) that will take in 7 parameters, 4 of which may be null. the 4 variable input parms if not null will be used to build a where clause. i want to use the sp_executesql to be more efficient in this process (instead of exec()) and also take adv...more >>

If I execute a function in SQL Server 2005 which does a simple query like SELECT * FROM TableA in an SQL Command object does it require that the person who executed the CLR stored procedure has select permission on that object? Or does the CLR proc execute as a DBO? or is it possible to make ...more >>

I am trying to convince the big boss (with the help of my manager) that SQL Server 2005 is the way to go. One thing he keeps harping on is the ability for Oracle to create packages, and overload within the package. Can SQL Server 2005 overload "packages"? If so how is this done? I really n...more >>

Two tables with a one-to-many relationship regarding trials or tests. In the many table there are three columns with attributes of each test. For the test to be 'valid' the three attributes must each meet certain criteria. Atribute1 must be greater than 4.0; attribute 2 must be less than 18...more >>

If I insert a record into a table, I would usually do an IF EXISTS ... first. But how do I do that if I am doing an INSERT/SELECT where I don't want duplicate Records (based on the UserID in my example) In the following tables and inserts, I have 5 records in my UserTable. I now want to...more >>

Hi What is the prefered practice to use when I have 2 or more related tables and I want to delete a row in the master table and I want the child tables to automatically delete their related rows, Should I use triggers in the database, enable cascade delete in the dataset or somthing else ? ...more >>

I have a stored procedure that inserts records into a table. Currently my code looks something like this: CREATE PROCEDURE sp_FinancialReport <init variables here> EXEC sp_myproc @lLocationID, @sFrom1Date, 'Visa', 'Visa', 'SALE' EXEC sp_myproc @lLocationID, @sFrom1Date, 'MC', 'MC', 'SALE'...more >>

Hi, I have read some articles on Unicode characters, I understand that they use as a "big international code page" that host thousands of characters. The thing I don't understand is why I get an error when I compare two nvarchar column having a different collation... as this: I know that...more >>

Hello, I have to import the data of a table from Sybase SQL Anywere to SQL server 2005. In Sybase I have a char field with xml data, in SQL server this field is a type XML. In Sybase I have two solutions to export the data 1) it is with the option ESCAPE ON, and in this case I have the fo...more >>

Hi - We have designed an Integration Architecture with SQL Linked Servers and the layout will be a single SQL Server(Publisher) connecting to multiple Linked Servers (Subsribers). We have a bottle-nect in the performance when we try to execute an update statement, which takes 30 mins fo...more >>

Hello, I have got a code snippet for a complex CTE. Here is the code **********************************************CTE******************************** With OrgCTE (EmployeeId, EmployeeName, ManagerId, Heirarchy) As ( Select emp.EmployeeId, con.FirstName + ' ' + con.LastName as Emplo...more >>

This might sound like a wierd question but I need to be able to do. Say I have a table called Addresses... then I had people, Businesses, and Contacts... all of which have an address... so Addresses would have the Primary Key and people, businesses and contacts would have a foreign key refre...more >>

I am trying to make the IN clause of a stored procedure return the rows in the order in which the IN clause values were specified. What I have is a table that can be sorted on a number of columns yet I want to pull back a subset of rows. I have the set of rows needed but I am unable to return ...more >>

I am trying to set up an Sql Statement that will run at various times of the day automatically. I have to use a Dynamic string and am getting an error: Server: Msg 103, Level 15, State 7, Line 3 The identifier that starts with 'Select distinct Rank=0,l.CompanyID,m.UserID, FullName= Case W...more >>

There any SQL command to do this? For some reason a lot of my text fields have empty white spaces at the end of the data, would like to get rid of these. Thanks ...more >>

I am unable to use print statements in SQl Server Functions. I can't even use INserts, how do I debug a function then please? ...more >>

I have a VERY simple stored procedure that I've used the Stored Procedure wizard in sql2000 to create. This is it: **************************** CREATE PROCEDURE [delete_PC] (@PC_1 [varchar]) AS DELETE [mtsys].[dbo].[PC] WHERE ( [PC] = @PC_1) GO ***************************** ...more >>

I have a table that contains two fields: effectiveFrom, effectiveTo. The time elapsed between these day could be greater than one year. I would like to develop a UDF that would convert these dates into a table of values. For example, if the dates are 7/1/03 and 4/1/06 the resulting table w...more >>

Hello, I am trying to create DDL trigger as below ALTER TRIGGER DDLTRIGGER ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS DECLARE @EventData XML SET @EventData = EVENTDATA() INSERT DDLEVENTLOG (EVENTTYPE /*POSTTIME, SPID, SERVERNAME, LOGINNAME, DATABASENAME, SCHEMANAME, OBJECTNAM...more >>

I am taking values entered by a user in a form and inserting them into a table. I then would like to obtain the record ID after I have added the record. However I do not understand why the following code does not work. Set conn = New ADODB.Connection conn.Open "Provider=SQLNCLI;Server=mys...more >>

I want to implement a "cloning" feature in my application and this involves cloning related data two. Having this structure (simplified but representative of my problem) CREATE TABLE Enterprise (enteid INTEGER IDENTITY PRIMARY KEY, entename VARCHAR(30) NOT NULL) CREATE TABLE Department (d...more >>

I've started work a company been asked to optimize some stored procedures. In several instances a temp table will be created in which a single row will be created. This temp table will then be used in three and four table joins to do work further along in the SP. I could rewrite this usin...more >>

Hi, I have a quite simple question. My stored procedure is the following one: ALTER PROCEDURE dbo.usp_Test @ENTRY_DATE varchar(8000) = Null, AS BEGIN SELECT * FROM TableTest WHERE (@ENTRY_DATE IS NULL OR ENTRY_DATE IN (@ENTRY_DATE)) END I just don't know what to pass the parameter w...more >>

I have a table that holds some 16,000 Lotus Notes records. The primary key is a clustered char(32), which always holds 32 hex digits. We will be moving away from Notes and using SQL Server instead. How concerned should I be about performance of this key (char(32)) versus say an integer key? I...more >>

I'm having a problem in getting a dynamic query in a sp working and the code is below so can someone please help me? It would be very much appreciated. Thanks Girogio -------------------------------------- set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[Search...more >>

Hi, I have some questions regarding query performances: Here is a commented set of queries against a Table called ADDRESS. I am using a daft sub query to highlight a point. Any help much appreciated! Tris sp_helpindex Address /* index_name index_descri...more >>

I have this recordset that has 3 fields, HOST_NAME, DISK and Samples. There can be multiple Disks in a server, but they can have different names. There are Unix machines that have quite some inventive namegiving. My question is, can I add a unique identifier, per DISK, per server? So that each d...more >>

When develpoing faily complex SQL statements possibly involving aggregate function across large amounts of data, what startegies can you use to test the results are correct? How do DB Administrators/Developers test that the data is accurate?...more >>

Dear All With the help of the group I have written a Trigger (more like Mark Williams wrote the Trigger for me) and now I get an error when the trigger is added to the table it was written for. This trigger deletes any previous entry from the AssetRegisterBAK where the CAPS_REF is previousl...more >>

If I created an alias type as followed, CREATE TYPE SSN FROM varchar(11) NOT NULL For some reasons, I would like to modify this alias type to varchar(20) not null, How to do it? ---Frank, using SQL2005dev ...more >>

Hi, I've got a view that union's 3 tables together on the same server. The base tables all have a constraint on a date field like: Table06 ([Date] >= '01/Jan/2006' and [Date] <= '31/Dec/2006') Table05 ([Date] >= '01/Jan/2005' and [Date] <= '31/Dec/2005') The view simply "union-all"'s...more >>

Hi look at the queries 1. Examination_timetable consists of rows like this ClassId ExamDate SubId Invigilation CLD00001 3/12/2006 SUB00001 STA00001,STA00002 select invigilation from Examination_timetable where examdate > '2/12/2006' and su...more >>

I execute 2 procedures in loop statement: first loop: sqlN = "exec dbo.e_NavIzd '20060212'" rsN.Open sqlN, cnENar, 1, 1 DO UNTIL rsN.EOF = True exec e_product insert 'test' LOOP second loop: sqlN1 = "exec dbo.e_NavSizd" rsN1.Open sqlN1, connection, 1, 1 DO UNTIL rsN1.EOF = True...more >>