i've three variables in my stored proc. & i want get the least of them. i know i can do that using case or if statements but i want to does T-SQL provides any other way of doing it. regards, ramesh ...more >>

Hello: My datetime column contains values like this : 2006-01-31 11:34:30.997. How can I transform this value to : 2006-01-31 00:00:00 ? Thanks, GB ...more >>

I have a t-sql stored procedure that looks like this: ------------------------------------------------------------------------------------------------------------- insert into myTable field1,field2 values myUniqueValue1, myOtherInfo2 if @@error <> 0 begin update myTable set field2 = m...more >>

I have a monthly process where I replace all the data in certain tables. I truncate the tables then DTS the new data in from another SQL server. I've found that it's much faster to drop all the indexes on my tables and then rebuild them at the end. I'm looking to automate this process. I...more >>

I am trying to get a rollup report with several summaries included, but I keep getting errors that complain about columns not in the GROUP by included in the select list , even though are not. They are used in a subquery and a join however. If I use the integer IDs the query works fine, but ...more >>

i want to write a query that will return last created 200 records + record with the minimum ID. is there a way to do that in one single Select statement? Following statement should return the record that has the minimum ID too. SELECT TOP 200 ID , CREATEDATE FROM TBL_RECORDS ORDER BY C...more >>

I would like to write a query that returns only valid e-mail addresses: SELECT email FROM individuals WHERE IsValid(Email) = "yes" IsValid would be a fuction...can someone help me write a good function for this (one that does not do any lengthy DNS look-ups)... ______ DC G ...more >>

We've recently begun planning our upgrade to SQL 2005 from SQL 2000 (see versions below). During testing we discovered we can no longer specify conflicting locking hints within an UPDATE statement. For example, our SQL 2000 systems have code similar to this (line numbers added): 1| UP...more >>

Hello I'm trying to get data from a view and it's not going well, im trying to "group" the rows abit so I dont end up with a million rows to display a date range..... Hope this makes sense..... CREATE TABLE #Test ( Startdate datetime, Enddate datetime, Avalible char(1) ) INSER...more >>

We are trying to transisiton our network to sql server 2005... however havent received our media kit yet for sql server 2005 and probably wont for a while going by their transit times... we already have the license for sql server 2005 standard edition but need to get this server up and running...more >>

Hi All, I have a field which stored date as char datatype in mmyy format. Acutally, I have inherited this and there is no way I can change the design of the database. Now, I needed to run a qeury between two months (from December 2005 to January 2006). My query was: select * from table1 w...more >>

Hi all, Sorry to re-post but I was wondering if anyone has any idea's on this one. I have the following example Table & Data... Create Table Test ( Cust_Code varchar(10), Acc_Deal_Type Varchar(3), Account_No Varchar(30), AccountTypes Varchar(50) ) Insert Into Test (Cust_Code, ...more >>

I have a table, I'll call contacts, where the primary key is called id and it's an integer field. I have another table, I'll call tempcontacts, and it's primary key is id but it's a guid for many reasons I won't go into now. These 2 tables are never combined in the same query. Table 3 is a...more >>

In SQL Server 2005 I have created a UDT that is of type datetime, then I bind a rule to it that = GETDATE() IF NOT EXISTS(SELECT * FROM systypes WHERE name = 'ProjectTimestamp') EXEC sp_addtype 'ProjectTimestamp', datetime, 'NOT NULL' GO IF OBJECT_ID('ProjectGetDate') IS NULL EXEC(...more >>

sql functions vs sub selects vs inner joins instead of sub selects. Which is faster. ...more >>

I'm making a change to my update trigger, and it's taking more than 2 minutes... is this normal? ...more >>

We have over 20 stored procedures and they all seem to work fine. Every once in a while one of the stored procedures will glitch and return the error, "Incorrect syntax near 'E'." One week it will be one stored procedure. Another week it will be another. We try to 'solve' the problem by dropp...more >>

I have all my website access statics logging data into a SQL table with the following structure: id int identity ip nvarchar 23 referer nvarchar 512 request nvarchar 512 website nvarchar 15 bytes int process_time int access_time datetime Each time a page is loaded the values are logged...more >>

I have a cursor that is taking about 4:00 minutes to run on 100 records. The cursor works on 11 different tables and inserts on average around 25 records per table. Does this time frame sound right? When I do the same thing with a temp table it only takes about 2:00 minutes for 1000 records...more >>

hi all, i was wondering if there is an alternate way of creating a simple query that'' get the results below, without using temp tables or declaring any cursors. Table: col1 col2 col3 col4 col5 ----------------------------------------------------- A Bob 2...more >>

how can I capture datbase login information for SQL 6.5 SQL 7.0 SQL 2000 SQL 2005...more >>

if I have a table to hold answer names such as 1 yes 2 no 3 maybe and a table to hold question answers as the integer id value question 1 - 1 question 2 - 3 question 3 - 2 question 4 - 1 to extract the literal represention i have to do select (select valuname from Answers wh...more >>

This might be a dumb question but I am not an exper on XML. I have daily exports in XML format that need to be imported into SQL Server. What's the easiest way of accomplishing what seems to be an easy task. The XML schema is the same as the DB schema. Many thanks... Bob ...more >>

Hi, Can someone tell how I can get a list of all server roles for a specific user. TIA, Eric...more >>

My apologies if this is not the right group for this question, but I couldn't find a "maintenance plans" newsgroup for SQL Server . . . About 3 weeks ago I created maintenance plans on four databases: master, msdb and two production databases (I'll call them DB1 and DB2). I included: 1) upda...more >>

Hi, I need to write a query which will help me clean up some data. I know the format of the data isn't ideal - however I need a query to help me extract what I need. COL1 COL2 COL3 Smith Peter Mr Smith Colin Mr Jones Peter Mr James John Mr I need to run a query on that table that will...more >>

Dear folks, We've currently got a database in such status. Usual actions has been taken (sp_resetstatus, modifying sysdatabases, bla,bla) and we have come to the conclusion that the database is corrupted Any ideas/suggestions/thoughts about this issue? Thanks in advance and regards,...more >>

Hi all, With the addition of SQL 2005 im having to make some alterations to my program to be able to cope with databases of either 2000 (version 8) or 2005 (version 9). That is not a problem using the SQLDMO.dll and the 'VersionMajor' attribute which will return either 8 or 9. Great! N...more >>

When I execute the following command I get the error "xp_sendmail: failed with operating system error 5" EXEC master.dbo.xp_sendmail @recipients = 'mymail@abc.org', @subject = 'Request for sample', @Query = 'SELECT Message FROM tempTable', @attachments = 'TResults.txt', @attach_re...more >>

Hello everybody i do have varchar column with DD-MM-YYYY. How can i convert it to datetime data type ? pls let me know...more >>

Hi all, I have the following example Table & Data... Create Table Test ( Cust_Code varchar(10), Acc_Deal_Type Varchar(3), Account_No Varchar(30), AccountTypes Varchar(50) ) Insert Into Test (Cust_Code, Acc_Deal_Type, Account_No) Values ('123456','MMC','12345678') Insert Into Test (C...more >>

Hi I have the following procedure that accepts two CSV lists of values, the first list contains primary key numbers, and the second values to update. When the list gets over about 200 items, I am getting intermittent Timout errors. Currently just over 500,000 records in the table. Is there...more >>

hi, i wonder if anyone would be able to help me? i'm pretty new to writing SQL triggers and cursors so apologies in advance! what i'm trying to do is write a trigger on our EMPLOYEE table which will insert into the USR_PENS_LEFT date (this is the date a person leaves our pensions schem...more >>

hi all, i have a simple stored procedure that takes input an integer..then inside it it creates a temporary table & adds columns which are equal in number to the integer that was inputed. but iam encountering variuos error when i execute this procedure here is the code(a very simple & sma...more >>

I want to use SELECT DISTINCT to output all unique values for multiple columns. However, I do not want the output to show all unique combinations of those columns, as this query will do: select distinct job_grade_cd, job_family from d_demog For instance, if d_demog looks like this: job_gr...more >>

hello, after executing a insert in the query anlayser i get a info like (228 row(s) affected) How can i acces this value. Is there a internal variablename @@.... I want to use this value for writing it in a logtable.... Is there any overview of the internal variables which can be used .......more >>

Howzit, Trying to run sp_fkeys stored proc in SQL2005 - extremely slow (compared to same call on SQL2000 on the same machine). Anyone know if there is an equivalent call - may be deprecated in SQL2005. Regards, Gilbert...more >>

Guys, Hopefully someone can help. We have a monitoring program that has threads which start and stop monitoring at various times. There are two tables: THREADLIFECYCLE unique_id start_time (always populated) end_time (not populated until the thread ends) MONITORRESULTS unique...more >>

I have a table with a specific column that i get from an AS/400 The column holds worktime specified in HH,MM format. How do i convert that do a decimal number. ie. 7,45 (7 Hours and 45 minutes) i want it to become 7,75. Thanks ...more >>

Hi. I have this sql query, which works: INSERT INTO tblac (type, t_id, startdate) SELECT tblac.type, ***182*** AS Expr1, tblac.startdate FROM tblac WHERE tblac.t_id = @t_id This works, but I want to put it into an SP. I can't figure out from books online, how to...more >>

Hi There, is it possible find all users in a sql server group, I'm not administrator, I can use query analyser to run scripts that's it. -- Thanks Ganesh...more >>

Hi there, I hoping someone can help me reduce the number of line of code I'm using, as these IF's are nested inside a bigger one. The main problem I have is I need to add another variable to the IF and don't want to copy and paste and make this statement even larger. I have tried playing ...more >>

I got no responses to the 'Complex query' thread, which probably contained too much information. I'll try simplifying it: I have 2 tables create table Minima (MI_ID int, MI_BeginTime smalldatetime, MI_EndTime smalldatetime, MI_RequiredStaff tinyint) this table contains the required staff ...more >>

Hi, I am trying to insert an XML document into into 3 tables which match the hierachy of the xml and which appears to be working. This data could be an update or an insert so for simplicity I have an initial procedure which clears down the existing data using a cascading delete. This too ...more >>

Previously I am using mssql2000 My sql is like this SELECT mycol, mycol FROM mytable WHERE active=1 ORDER BY mycol This query is running fine Then recently I upgrade my mssql server to mssql2005 The query above return an error said "ambiguos" Then I change my query to SELECT mycol, mycol ...more >>

hello, in a table i have 3 fields custnr weeknr profit example 1 1 100 1 2 110 1 3 120 1 4 150 i want to calculate how much the profit grows referenced from weeknr 1 and the relative to the precedent week 1 1 0% 0% 1 2 10% 10% 1 3 20% 10% 1 4 50% 30% thanks ...more >>

Hi I have some SQL code below which I'm running on a server DEV_TESTSTAGE2 with Windows 2000 and SQL Server 7. I'm trying to send an attachment which works fine if the attachment is on DEV_TESTSTAGE2 but if the attachement is somewhere else on the network it send the email without the at...more >>

Dear folks, I can't work out with this issue. I am trying to create a link betwen an old sql server against a new. However, there is a link from that same sql server 2000 till 7.0 and it works fine. Message is clear but I don't understand at all, because of I've got two logins with enou...more >>

I query two fields in my string.. Those are myHour and myCounty. There are about 5 million records. myHour , myCountry and cpm fields are indexed. It returns too late.. SELECT SUM(cpm) from dbilgi where myHour >= '2006-02-05 00:00:00' and myHour <= '2006-02-05 23:59:59' and myCountry = '...more >>

HI all, I have a claim table. An insert trigger, in that insert trigger I want to retrieve the current identity value of that insert, to be used elswhere which is best scope_identity(), ident_current(), or @@identity Thanks Robert ...more >>

Hi, I am writing some updates scripts for a database and need to add a new column at a specific position in the table. I am using the 'ALTER TABLE' command but this just tags it on the end. Is there any way of doing (via TSQL) this without rebuilding the table? Thanks in advance, Stu ...more >>

Hi. I need some help on teh following: Let us say i have table 1 with 2 coloumns ID, Date Table2 with n columns ID, Date, xxx.,xxxx,xxxx I need a query that returns all record from table 1, and for each record from table one, i need the1st record from table2 having the same id, with a ...more >>

HI all, Where can I read up about the best practice when it comes to creatin audit trail. What to store how much to store, etc What do some of the gurus do, Thanks Robert ...more >>

HI all, whats the easiest way of stopping a column from being updated. I want to keep the original inserted value, thereafter it must not be changed. Thanks Robert ...more >>

If I run the following 3 statements (on sql server 2000): create table foo (x nvarchar(128)) create unique index ix_foo on foo (x) alter table foo alter column x nvarchar(128) not null I get this error: Server: Msg 5074, Level 16, State 8, Line 1 The index 'ix_foo' is dependent on column...more >>

HI all, I have a question about update triggers. I have a update trigger that updates a 'dupdated' column to the current date and time. I this so that I have an accurate record of when the row was updated. due to my mistake, users where changing the dupdated column to cover their sloppiness. My...more >>