have brought this up in a previous post, and have much appreciated the input. I have a table containing financial instrument quotes. Create table MyTable ( instrumentName varchar(32) not null, stamp smalldatetime not null, price decimal(28,10) ) it has a unique clust...more >>

I know this is a well-trodden road, but I've come up with a SQL paging solution that I can't seem to match up with the many approaches I've seen online. Feedback on "my" approach would be appreciated. The following statement to get the TOP x rows, and we add a criteria which will exclude the r...more >>

I made an AFTER DELETE T-SQL trigger that sends e-mail. Worked like a charm. I then made a similar CLR trigger and deployed it to the server. The T-SQL trigger seemed to disappear from the Database Triggers folder for that database. However, when I delete a row from the table I get e-mail from b...more >>

Hi all, I have the following query: SELECT parent FROM myTable INNER JOIN myTable2 ON myTable.ID = myTable2.ID WHERE myTable.ID = 2 AND myTable.ID NOT IN (1,2,3) AND myTable.AnotherColumn = 1 I have brought this up in a previous post, and have much ...more >>

The names for naming objects in SQL Server include the following as per BOL: "Letters as defined in the Unicode Standard 2.0." Does anyone know how to determine which characters are valid Unicode letters ? Thanks in advance, Thomas...more >>

Hi If we use connection pooling, then a sp 'sp_reset_connection' will be executed to clean the connection. Does this sp clears the context_info for the connection or not? Our initial tests shows that it is not clearing the context_info. How to clear context_info in connection pooling environm...more >>

Hi all, I have a SELECT statement like follows: @myVariable //parameter ; consisting of values like: '1,13,53' SELECT myTableColumn FROM myTable WHERE myTableColumnID IN (@myVariable) I get the following error: Syntax error converting the varchar value '1, 13, 53' to a column of data ty...more >>

I haven't written SQL in some time and I'm extremely rusty, so help would be appreciated and thanks in advance. I have a table (main) that has multiple fields that contain a lookup id to a common lookup table (lookup). How do I write the query to pull back one result set that has the form belo...more >>

GRANT EXEC ON dbo.MyFunction TO PUBLIC GO >> Incorrect syntax near the keyword 'GRANT'. Apparently, I have to GRANT something other than EXEC? It's a scalar function so I shouldn't have to GRANT SELECT. -- Peace & happy computing, Mike Labosh, MCSD "When you kill a man, you're a...more >>

How would I use the datediff function without counting weekends? declare @StartDate SmallDateTime declare @EndDate SmallDateTime set @StartDate = '10/01/05' set @EndDate = getdate() select datediff(d,@StartDate,@EndDate) --except for the weekends This value returns 18, but I want it t...more >>

Hey everyone, I am working woth SQL2000. DDL: Appointments ============ ApptID (pk) Integer Identity ApptDate DateTime PhoneCalls ============ CallID (pk) Integer Identity CallDate DateTime Leters ============ LetterID (pk) Integer Identity LetterDate DateTime Peopl...more >>

Hi Can anyone direct me to a site which has a good overview of the ability to code C# (or VB.NET) directly in SQL Server 2005?? Does this allow .NET languages to replace / supplement existing Stored Procs?? Is there any advantage (other than not having to learn T-SQL), to writing SP's in C...more >>

hi all, is there sql function that i can use to check a variable and if it is = 0 then return null? thanks, ari...more >>

Hello, I have a simple select statement where I would like the username to come from another select statement. Unfortunately the sub select statement has more than one fields that makes it impossible for me to get the data I want. Here is my script. select count(usern),usern from contac...more >>

Hi This can probably be solved in thousands of ways, but I need a hint in the right direction. I have a stored procedure that copies data from one database to another database. I have this stored proedure scheduled in SQLServerAgent. I have scheduled it four times - at every minut on the s...more >>

SQL 2000 + SP3 Using Query Analyzer -> Object Browser -> Right-click SP -> Debug I hit the execute button and get this error: Server: Msg 504, Level 16, State 1, Procedure sp_sdidebug, Line 1 [Microsoft][ODBC SQL Server Driver][SQL Server]Unable to connect to debugger on HORSQL01 (Error ...more >>

Hi I have this setup: I have two databases, NavisionDB and migaDB. In NavisionDB, I have written a stored procedure (Lets call it SPa) that copies some data from NavisionDB to migaDB. After copying the data from NavisionDB, the data in NavisionDB is updated so it wont get copied the next...more >>

I have a few tables that I am joining together and one of the joins is on a field that almost always has a code in it. Sometimes it is a blank (not a null) if the clerk didn't type anything into it. The table that I am joining to this one, has many cells in the join field thhat are blank and t...more >>

Given the two timestamp Record Timestamp 2005-10-19 15:33:11.800 Current Timestamp 2005-10-19 16:33:11.800 how can I tell if my record timestamp has not reached a 6:00pm cut off time. when passed a record id I need to check the record to see if it has passed 6:00pm of it's recorded date...more >>

Hello! I am trying to capture all the infromation that DOS dir command generates and stores it into a table called #DirectoryInfo. See below: create table #DirectoryInfo ( dir varchar(255), date datetime NULL, size int, name varchar(255), type char(9)) insert #DirectoryInfo exec x...more >>

Here is my situation: I have a table of items and a table of links. the table of links links an item to its next gerneration item. It is setup something like this. Table: Item ID desc ... 1 a widget 2 a different widget 3 yet another widget 4 widget again .. .....more >>

I know you can't use an alias in a group by, but is there any way to group by a constant in the select list? See the following northwind sample of my problem. use NORTHWIND select city, 'blah' from customers group BY ciy, 'blah' having count(*) > 5...more >>

Hello, If I have a field that contains varchar type data which looks like '00312' How can I parse the field to just display '312' ? Thanks! Patrice...more >>

using SQL SERVER 2K I have registered a server that sits on TestServer\TestInstance and I have a bunch of Database and table creation scripts... but I didn't think it was possible to run something like the following unless it was run from that actual machine (while at the machine or TermSer...more >>

Is there a SQL command that will display the tables in a SQL 2000 database or describe the layout of a table in the DB?...more >>

Can somebody help me with a query. carLogin is the id of the trader. carReference is unique for the trader Exemple: data in my database 099 - A 099 - B 100 - A 100 - B 100 - C 100 - D 100 - E 100 - F 100 - G 100 - H 100 - I 108 - A 108 - B Now i want delete for 1 trader...more >>

Hello! Is there any way in SQL to convert records in fields. Something like creating a a record with 3 fields from a table with 3 records? Thanks in advance, Hugo Madureira...more >>

I've got a bit of an odd situation here. I have a SELECT statement that feeds an INSERT operation. The select executes in 11 seconds. The INSERT takes more than 18 minutes. I'm only inserting 14000 rows, and it takes the same amount of time even if I clear the target table first (TRUNCATE). Simi...more >>

What is going on here? CREATE TABLE HWW ( IDD int, Par1 varchar(5) NULL, Par2 varchar(5) NULL, Par3 varchar(5) NULL, PRIMARY KEY (IDD) ) INSERT INTO HWW VALUES (1,'1','2','3') INSERT INTO HWW VALUES (2,'1','2','') INSERT INTO HWW VALUES (3,'1','5','6') INSERT INTO HWW VALUES...more >>

I used Select Into... to duplicate a table, but lost the IDENTITY and PK constraints in the process. How do I get those back? Thanks. Here are the details: Original Table: TABLE A ( [ID] INT IDENTITY PRIMARY KEY , ColA smallint NOT NULL , ColB va...more >>

Hi I have a table containing financial instrument quotes. Create table MyTable ( instrumentName varchar(32) not null, stamp smalldatetime not null, price decimal(28,10) ) it has a unique clustered index on instrumentName and stamp it has a nonclustered index on stamp ...more >>

Consider the following hypothetical queries: QUERY A Select ... >From TableA TA Join TableB TB ON TA.Field1 = TB.Field1 AND TA.Field2 = TB.Field2 AND TA.Field3 = TB.Field3 Where TA.Field4 = 'Some Value' QUERY B Select ... >From TableA TA Join TableB TB ON T...more >>

Hi, I'd like to schedule job to "Start whenever the CPU(s) become idle". But I also want to STOP job whenever the CPU(s) become busy. How can I achieve that? I use SS2000, SP4, Win 2000 Advance, SP4. Thanks in advance Nikola Milic ...more >>

Hi with you all, I need to automatically increase the value of a column by one. Except of course for the first value of my column, in that case the value must be 1. It's like a counter. In the table this counter must be initialized by 1 for each new value of "InheritedKey". The following li...more >>

Hi I am using ADO (msado.dll) to create MS ACCESS database in version 2000. It was working fine. when I tried to create database in version 2002 I got error Code[80004005] (Selected collating sequence not supported by the operating system) i.e. unspecified error. I don't know where is the pro...more >>

Hi, Did Microsoft ever release something called "SQL SERVER 2003"? The reason why I am asking is because I keep hearing people say they use SQL Server 2003 all the time. From what I know, the latest version is still 2000... Any idea? Ed...more >>

I have two databases--A and B. Database B, a database contain zipcode and county code, has one table zipcounty that includes two fields zipcode, county. Database A has one table, address, which contain the address for the client, which has clientid, clientzip, clientcounty. I need to script...more >>

Hey all, does sql server have a date.parse function? take 20050101 and convert 1/1/2005 thanks, rodchar...more >>

hey all, how do i get a report of all the fields and their types for a table in my db? thanks, rodchar...more >>

I am new to SQL programming and needed some help. I have two statements that produces to tables that are correct. I need SQL query to only return 1 table with the two results. How do I do that? My code is as follows: Select Count(*) TotalInterviewCount From CreelInterview Where (CSDayID ...more >>

I need to get the row count on a select distinct on more fields (but not all) of my table. Example: myfld1 myfld2 myfld3 myfld4 myfld5 1 2 3 4 A 10 2 3 4 A 10 2 30 4 ...more >>

Hello, Just wondering if anyone can tell me the best way to rewrite the below code without a cursor. It's just passing each Id to a stored procedure. Let me know if you need any more info. Thanks & go easy on me, I know cursors tend to rile everyone up. Declare cur_DeleteStuff Cursor Scrol...more >>

Hi, we are optimizing our SP's and have 2 pretty similar SP's: Slow: SELECT * FROM Healthform WHERE healthformid = (SELECT TOP 1 healthformid FROM Healthform WHERE t.customerid = 1201 AND t.treatementid = h.treatementid ORDER BY treatementdate DESC, row DESC, colcol DESC, healthformid DE...more >>

Dear *, I need help with a query! I've set up a Database for managing employees and their contracts in our institution. I have two tables (stripped-down): tbl_persons: PersonID, smallint(2) Primary Key Name, varchar(50) tbl_contracts: ContractID, smallint(2) Primary Key Person_ID, ...more >>

pls in am just working on a web site, i am confused as to what programm to use... 1.i know macromedia dreamwaver,but some one said v.b.net is better 2.what is the differnce between web designing,appliocation and development..pls let me know. 3.how do i introduce webpages on dreamwaver to tsq...more >>

Is it possible to return an array of values from a stored procedure? I have a situation where a stored procedure is going to insert any number of records into a table. I need to grab the identity field from each of these added records and insert these into another table. Does anybody know how...more >>

It seems that RTRIM, LTRIM, and TRIM are not defined for these fields. Has anyone thought of a way to use PATINDEX or SUBSTRING to effectively TRIM a text/ntext field of trailing blanks?? thanks adam ...more >>

Hi I'm just populating a couple of tables whilst I learn about SQL. The primary keys in both tables NICKNAMES AND PEOPLE are autoupdate or whatever i.e. you don't set them. The database does when you write a record. Below, is there a better way to insert my nicknames? It seems silly to d...more >>

Hi, I have declared a variable of type varchar(3000) in my Stored Procedure (SQL Server 2000). The purpose of this varibale is to hold the query string that i form during runtime. Surprisingly, the value of the variable gets truncated beyond 256 characters. Can anyone help me to identify...more >>

When i execute a SQL Statement and trace it through the profiler.. why there is a differnce in counters for SQL:BatchCompleted and SQL:StmtCompleted for a single SQL Statement? For example the statement select * from office SQL:StmtCompleted : CPU->0 , Read -> 10 , Write->0 , Duratio...more >>

/* I have posted DDL below for the issue i have. When you look at the output from marc1, I would like to retrieve the minimum and maximum vehicle_id's based on Vh_make_id so as my output would look something like: min max -------- ----------- 3040501 3131887 3040443 3049312 How c...more >>

Hello, I am wondering where to do certain processing of data, I receive data of about 5000 rows in the sql server (2005 Beta) Now after I got the data I need to go trough the records and if that record matches some criteria than I add a new record in an other table, than the next records crit...more >>

Hi, Yesterday's answers by ML & Tibor were helpful. I am having a database with 30 tables. One of the tables contains around 600 thousand records. It takes long time to load these records on the frontend application. Also the machine on which SQL server is installed slows down(the ...more >>

Hi All I am looking for a bit of advice. I am in the process of creating a database in which it has been decided that all primary keys are going to varchar(40). Not my decision, but anyway. When inserting into each table it will be possible to specify a value for the primary, but if not...more >>

Hi, I have a query here that I would like to make cheaper in terms of query cost. Here's the query: __________________________________________________________________ Select distinct vw.CustomerID, vw.BranchID, vw.SourceOfBusiness , vw.SpkCustNo, vw.Surname, vw.Initials, vw.Inco...more >>

We are having a total session rollback problem, but cannot manage to find what is causing it. It is happening very intermittently. To put you in the picture, our program connects to SQL as soon as the application is open and then disconnects from SQL only when the application is closed. ...more >>

Is it possible for SQL Server to throw raise an error when a procedure is called and a varchar value is passed in, which is too long. Let me explain. A procedure is created with a varchar(5) parameter, but field is passed in which is a varchar(50). SQL Server automatically truncates the valu...more >>

I have been trying for the last few days to execute a DTS from a Stored procedure and failed misrabely. I cut some of the code into a SQL Query window to try and track down where the problem lies .... After executing the sp_OACreate i get a 0 return value, but when executing the sp_OAMethod ...more >>

hi friends, declare @primary varchar(100) select @primary = 'vanitha'+','+isnull(null,'')+','+isnull(null,'') select @primary so the result will be vanitha,, i want the result vanitha. thanks vanitha ...more >>

Hi all, I am pretty much going insane. I have tried all sorts of things and gotten nowhere, and I'm fairly sure there exists a simple solution to my problem. If you help me, I will be eternally in your debt. Here is the scenario. I have a stored procedure which conains a fairly heavy UNI...more >>

Hi all, I have the following query: The two and clauses in the subquery need to be conditional.(if possible) Only included in the select if @current_hazards = True Or @areas IS NOT NULL If @current_hazards = False AND @areas IS NULL then both add statements are to be ignored??? If thi...more >>