SQLServerCentral / SQL Server 2005 / Development

How do I get by with "The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."

hbanerje — Thu, 24 May 2012 11:04:08 GMT

select ID_NUM as ID_num, LAME1, RESP_CODE as RESP_CODE1,cast(QUERY_COMM as nvarchar(max)) as QUERY_COMM1 from dbo.DRGQueries group by ID_NUM, LAME, RESP_CODE,QUERY_COMM having COUNT(*) >1where I have Query_comm as ntext as datatype and Resp_code nvarchar(50) in the above table.I am getting the error message "The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."Thanks,Haimanti

Updating text within a single column, but not the entire string, duplicates string being updated

rabisco — Mon, 07 May 2012 13:40:19 GMT

Apologies for post this issue again, but I am tacking it from various angles and logging new post as I find new issues (while moving forward with the work)My issue very similar to http://www.sqlservercentral.com/Forums/Topic1012573-392-1.aspx, but in my case, there could be more than one paragraph in a column...I'm going to make this as simplified as possible...These are the contents of a sample column[code]<body>Link with only valid <a macro_name="productLink" code1="1055288" href="" macro_uid="_13360741834971941">code1 </a>skuLink with only valid <a macro_name="productLink" macro_uid_13360741834942686" href="" macro_uid="_13360741834942686" code2="01F8179">code2 </a>skuLink with <a macro_name="productLink" macro_uid_1336074183491872" code1="1055355" href="" macro_uid="_1336074183491872" code2="01F8179">both valid </a>code1 and code2 Skus[/code]I have come up the following query....[code]declare @myvar varchar(max)declare @myvartmp varchar(max)declare @code2sku varchar(50)declare @code2sku varchar(50)declare @prereplace varchar(50)declare @postreplace varchar(50)declare @inposcode1 intdeclare @poscode1 intdeclare @poscode2 intdeclare @poscode1tmp intdeclare @poslsqbrkt intdeclare @posrsqbrkt intdeclare @posrsqbrkttmp intdeclare @counter intset @myvar = ''set @myvartmp = ''set @counter = '0'declare c1 cursor for select bodytext from documentbody where bodyid = '236786'open c1fetch next from c1 into @myvar while @@fetch_status = 0 begin set @myvartmp = @myvar set @poslsqbrkt = (select charindex('<a __jive_macro_name',@myvar)) set @posrsqbrkt = (select charindex('">',@myvar,@poslsqbrkt)) set @poscode1 = (select charindex('code1="', @myvar, @poslsqbrkt)) set @poscode2 = (select charindex('code2="',@myvar,@poslsqbrkt)) if (@poscode1 between @poslsqbrkt and @posrsqbrkt) and (@poscode2 not between @poslsqbrkt and @posrsqbrkt) begin set @counter = @counter + 1 set @code2sku = (select substring(@myvar, @poscode1 + 12,7)) set @code2sku = (select max(code2_part_sku) from LinkSKU where code1_part_sku = @code2sku) if @code2sku <> '' begin select 'update: ' + cast(@counter as varchar) select @myvartmp select 'leftsquarebracket: ' + cast(@poslsqbrkt as varchar) select 'code1: ' + cast(@poscode1 as varchar) select 'code2 :' + cast(@poscode2 as varchar) select 'rightquarebracket: ' + cast(@posrsqbrkt as varchar) set @prereplace = 'code1="'+@code2sku+'"' set @postreplace = @prereplace+' code2="'+@code2sku+'"' set @postreplace = ' code2="'+@code2sku+'" ' + @prereplace set @myvar = replace(@myvar, @prereplace,@postreplace) select 'before update: ' + @prereplace select 'after update: ' + @postreplace update documentbody set bodytext = @myvar where current of c1 end end while (@poslsqbrkt <> '0') begin set @myvar = @myvartmp set @posrsqbrkttmp = @posrsqbrkt set @poscode1 = '' set @poscode2 = '' set @poslsqbrkt = '' set @posrsqbrkt = '' set @poslsqbrkt = (select charindex('<a macro_name',@myvar,@posrsqbrkttmp)) set @posrsqbrkt = (select charindex('">',@myvar,@poslsqbrkt)) set @poscode1 = (select charindex('code1="', @myvar, @poslsqbrkt)) set @poscode2 = (select charindex('code2="',@myvar,@poslsqbrkt)) if (@poscode1 between @poslsqbrkt and @posrsqbrkt) and (@poscode2 not between @poslsqbrkt and @posrsqbrkt) begin set @counter = @counter + 1 set @code2sku = (select substring(@myvar, @poscode1 + 12,7)) set @code2sku = (select max(pf_part_sku) from pfAutoLinkSKU where nw_part_sku = @code2sku) if @code2sku <> '' begin select 'update: ' + cast(@counter as varchar) set @prereplace = 'code1="'+@code2sku+'"' set @postreplace = @prereplace+' code2="'+@code2sku+'"' set @postreplace = ' code2="'+@code2sku+'" ' + @prereplace set @myvar = replace(@myvar, @prereplace,@postreplace) select 'before update: ' + @prereplace select 'after update: ' + @postreplace update documentbody set bodytext = @myvar where current of c1 end end end fetch next from c1 into @myvarendclose c1deallocate c1[/code]I hope my query is not to convoluted to read..Essentially this query does what I need it to do, except that if it finds a code2="01F8179", it updates every occurence of it, rather than just the one which is missing a code1. Thanks.

SCOPE_IDENTITY()

thbaig1 — Mon, 21 May 2012 10:41:38 GMT

I am using SCOPE_IDENTITY() to get last generated id in session,scope.but this is giving me values for all execution at once. I am using it in sp and that is causing some integrity constraint violation . it can be fixed if SCOPE_IDENTITY() give me only one value.my scenario.......WHILE @@FETCH_STATUS = 0BEGINinsert into table T1values ();SELECT @PageIdIndt = SCOPE_IDENTITY(); --- e.g. it gives 121,123,124Insert into table T2values();SELECT @PageIdIndt_two = SCOPE_IDENTITY();-- its expected values is 321, but it is giving 121,123,321,124insert into table T3 (col,col2,col3)values(val1,val2,@PageIdIndt_two);.......

Where I can get SQL Server SMO library

ssa2010 — Mon, 21 May 2012 01:11:39 GMT

I am trying to develop a program for create table script for sql 2000 db using SMO.How can I add SMO library in my program.I installed SQL 2005 express edition, but some errors.Please help me

Cast erroring

Sqlsavy — Thu, 17 May 2012 04:45:28 GMT

I'm new to development below script is erroring any idea why?select cast(right(@@servicename,2) as int)Thanks in advance

Delete Duplicates

vani_r14 — Tue, 08 May 2012 06:57:56 GMT

HI allAm trying to work on deleting duplicate rows from a table and am kind of stuck. Could i please get some help. I have a table with staff and training data [code="sql"]create table temptable (empno varchar(10), name varchar(50), cid varchar(10), grade varchar(10), enddate datetime[/code][code="sql"] Insert into temptable values ('1234', 'apple', 'hr7890', 'Completed', '2004-03-01 00:00:00.000' )Insert into temptable values ('1234', 'apple', 'rb7820', 'Did not complete', '2004-03-01 00:00:00.000') Insert into temptable values ('1234', 'apple', 'pt7830', 'Cancelled', '2004-04-05 00:00:00.000') Insert into temptable values ('1234', 'apple', 'sp7840', 'Completed', '2004-10-30 00:00:00.000') Insert into temptable values ('1234', 'apple', 'rb7820', 'Completed', '2005-12-01 00:00:00.000') [/code][code="sql"] Insert into temptable values ('2344', 'apple', 'hr7890', 'Completed', '2004-03-01 00:00:00.000') Insert into temptable values ('2344', 'apple', 'rb7820', 'Completed', '2004-03-01 00:00:00.000') Insert into temptable values ('2344', 'apple', 'pt7830', 'Completed', '2004-03-01 00:00:00.000') Insert into temptable values ('2344', 'apple', 'sp7840', 'Completed', '2004-03-01 00:00:00.000') [/code]CurrentlyIn my current query, i pivot the data so the course names are the headings of table with one row of employee 1234 and the appropriate value under each course name. Am using max for the grades to pick up the highest of the grade for each course namehowever since 1234 has a completed and did not complete entry for course rb7820 using max results in Did not attend for that course in the final query. Tried using min but it complicates the other data. I would like to find out alternative way of deleting the Did not Complete row from the temptable so i would only have the one row for the course rb7820 for employee 1234 and that is Completed. Can someone please suggest any ideasthanksvani

Accessing SQL Server through a web page

rd_dart — Wed, 02 May 2012 06:09:16 GMT

Hi,I have a query regarding SQL Server. Currently I am using SQL Server 2005 Express and I have a couple of databases for various clients. This database is hosted on a machine locally. What I want is that I will be able to read and update data in this database from a web page on the internet. Is it possible? Maybe it is possible with the use of web services?If someone can help me I would be very grateful. Thanks in advance for your time.

Auto generate insert Procedure

vietcode — Tue, 17 Aug 2010 10:31:17 GMT

CRATE procedure [dbo].[Auto_Create_Procedure]@table_name varchar(200)ASDECLARE @column_name varchar(30)DECLARE @column_length int--DECLARE @table_name varchar(30)DECLARE @column_string varchar(4000)DECLARE @value_string varchar(4000)DECLARE @ROW_COUNT intDECLARE @data_type varchar(30)set @ROW_COUNT = 0set @column_string = ''set @value_string = ''--set @table_name ='tblBook'DECLARE column_cursor CURSOR FORselect b.name column_name, b.length, c.name data_typefrom sysobjects a, syscolumns b, systypes cwhere a.id = b.id and b.xtype = c.xtype and a.name = @table_name and c.name <> 'sysname'order by colorder-- Print PROCEDURE commandPRINT 'CREATE PROCEDURE Prc_Insert_' + @table_name + '('OPEN column_cursorFETCH NEXT FROM column_cursorINTO @column_name, @column_length, @data_typeWHILE @@FETCH_STATUS = 0BEGINset @ROW_COUNT = @ROW_COUNT + 1if(@ROW_COUNT <> @@CURSOR_ROWS)-- IN TUNG THAM SO RA MAN HINHPRINT ' @' + @column_name + ' ' + dbo.fnc_get_datatype(@data_type, @column_length) + ', 'elsePRINT ' @' + @column_name + ' ' + dbo.fnc_get_datatype(@data_type, @column_length)--set @column_string = @column_string + @column_name + ', '-- set @value_string = @value_string + '@' + @column_name + ', 'FETCH NEXT FROM column_cursorINTO @column_name, @column_length, @data_typeENDCLOSE column_cursor;DEALLOCATE column_cursor;PRINT ')'PRINT 'AS'PRINT 'BEGIN'-- Print Insert commandprint ' INSERT INTO ' + @table_nameprint ' (' + SUBSTRING(@column_string, 1, LEN(@column_string) - 1 ) + ') 'print ' VALUES'print ' (' + SUBSTRING(@value_string, 1, LEN(@value_string) - 1 ) + ')'PRINT 'END'PRINT 'GO'Visit [b][url=http://sql.entersources.com]this site[/url][/b] to reference SQL command

Establishing FK with a primary key that changes everytime db updated

annastassia1935 — Fri, 11 May 2012 13:01:10 GMT

The [lab_seq] column is updated within the code using the array index. A decision was made years ago to DELETE and INSERT as a way to update the data from the application server. Therefore, every time the data is rearranged in the array, the [lab_seq] is updated with a new number. The [document_uid] stays static. I want to determine what type of performance on the database if I add the child table. What good business justification can I provide to the client and developers to change the way the [lab_seq] is handled?At this time, the maximum number of rows in one LAB table is 3 million growing by 20,000 monthly.(The SQL statements are written in java not handled by stored procedures.)Relationship: 1 to 1 (one test has one 10,000 char result)Parent TableCREATE TABLE [dbo].[LAB]( [document_uid] [char](16) NOT NULL, [lab_seq] [int] NOT NULL, [sample_id] [varchar](50) NULL, [provider_uid] [char](16) NULL, [specimen] [char](3) NULL, [sample_dt] [char](8) NULL, [receive_dt] [char](8) NULL, [manufacturer] [varchar](2) NULL, [clia_uid] [char](11) NULL, [type] [varchar](3) NULL, [loinc_cd] [varchar](7) NULL, [result_interpretation] [varchar](100) NULL, [result] [varchar](10) NULL, [result_units] [varchar](4) NULL, [result_range] [varchar](15) NULL, [result_rpt_dt] [char](8) NULL, [case_cd] [char](2) NULL, [comments] [varchar](100) NULL, [starhs_sample_id] [varchar](15) NULL, [accession_number] [varchar](50) NULL, [sreason] [char](1) NULL, [facility_uid] [char](16) NULL, CONSTRAINT [PK_LAB] PRIMARY KEY CLUSTERED ( [document_uid] ASC, [lab_seq] ASCChild TableCREATE TABLE [dbo].[L_SEQUENCE]( [document_uid] [char](16) NOT NULL, [lab_seq] [int] NOT NULL, [g_result] [varchar](MAX) NULL CONSTRAINT [PK_L_SEQUENCE] PRIMARY KEY CLUSTERED ( [document_uid] ASC, [lab_seq] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [DATA]) ON ALTER TABLE [dbo].[L_SEQUENCE] WITH CHECK ADD CONSTRAINT [FK_SEQUENCE_LAB] FOREIGN KEY([document_uid], [lab_seq])REFERENCES [dbo].[LAB] ([document_uid],[lab_seq])

Row level read-lock in SQL Server 2005

Balaji-485243 — Wed, 10 Jun 2009 03:04:39 GMT

Hi,I would like to know how to lock a particular row of a table being read by more than one user at a time.For example, I have a table called "Emp" with the following data:EmpID EmpName Salary1 AAA 10002 BBB 20003 CCC 30004 DDD 4000If i write a "SELECT" query against "Emp" table like:BEGIN TRAN SELECT * FROM Emp WHERE EmpID = 2 < some SQL Statements..............>COMMIT TRANWhen i run the above query, other than me no body else should be able to access([b]read[/b]/write/update) the row where EmpID = 2 till SQL Server commits this transaction.Basically, i want to prevent the users reading the same row from a table at the same time.Any help is appreciated...:-)

how to check if SQL Server is running or NOT

Shamshad Ali — Tue, 16 Jun 2009 05:22:44 GMT

I have two SQL Servers (Server A and Server B) replicating one database via sql server merge replication.I add update delete data from any server and data has been replicated among both servers.I have 3rd SQL Server (Server C) which has a job that update add delete some records from Server A, based on some condition from SQL Server C database.few days ago Server A was down and the job at Server C started. it becomes fail because of the Server A was not available, but if i make some changes in job that check if Server A is down then i updates data in Server B. so when Server A is on it will automatically synchronize data between them. Ping via xp_cmdshell checks for server availabitlity but i want to check SQL Server services if running then it will start rest of the job activitites otherwise it will try to connect Server B similar way.How do i do this? checking Server A SQL Server Services running or not using TSQL code in SQL job.Please help.Shamshad Ali.

Timeout errors from SQL Server 2005

ravish.ramesh — Wed, 09 May 2012 03:10:48 GMT

Hi,I have configured 6 databases on my db server and recently startedgetting timeout errors from my .net web application for all the databases.When I checked the events of db server i could find the below errorEvent Type: InformationEvent Source: MSSQLSERVEREvent Category: (2)Event ID: 833Date: 5/9/2012Time: 11:24:47 AMUser: N/AComputer: CMS-DB1Description:SQL Server has encountered 12 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [D:\DB\DBMH.mdf] in database [DBMH] (7). The OS file handle is 0x00000844. The offset of the latest long I/O is: 0x00000227be6000For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.Data:0000: 41 03 00 00 0a 00 00 00 A.......0008: 08 00 00 00 43 00 4d 00 ....C.M.0010: 53 00 2d 00 44 00 42 00 S.-.D.B.0018: 31 00 00 00 00 00 00 00 1.......

using left join and TOP

bherman-628226 — Wed, 09 May 2012 06:33:02 GMT

I have two separate queries that I would like to combine into 1. My first query returns a set of rows from TABLEA. Such as:SELECT * FROM TABLEA WHERE ChecklistID = 12This will return say 10 rows. For each one of those rows, I then query TABLEB. There can be multiple rows or no rows in TABLEB for each row in TABLEA, but I only want to return the last one.SELECT TOP 1 * FROM TABLEB WHERE ChecklistStepID = X ORDER BY ID DESC(Where X is the ID of the row from TABLEA)I've tried all sorts of things using left join because I want all the entries from TABLEA even if there are no corresponding rows in TABLEB. Either my query returns all the columns with the entries from TABLEB set to null or it returns multiple rows from TABLEA, one for each entry in TABLEB instead of a single row containing the last entry from TABLEB.Any assistance would be greatly appreciated.Thanks,Brad

SQL backing out rows

pgs9002 — Tue, 08 May 2012 07:41:32 GMT

Overview..I am working on an application in Heatlh Home Care which uses Allscripts Home Care. Due to billing regulations for Medicaid, bills have to be created to bill Medicare, but not part of the regular Allscripts application. A separate database server has been set up to handle this situation with a copy of production (This was suggested as well by Allscripts). A Vb.net application has been written to insert rows into a claim table and a claims detail table for particular patients to avoid reentering everything manually, based on their Medicaid bills. The application completes successfully, and I see the rows inserted into the tables. I also see the data in the Allscripts appplication. But then after some time, they are deleted somehow. Can some one give me a clue as to what or how to figure out why the rows would be backed out of SQL Server. Any help is greatly appreciated. Thanks,Paul

Update using Cascade

babaichatterjee — Tue, 08 May 2012 01:12:54 GMT

[b]Hi everybody.This is Animesh here, I need help:[/b]I have got three table namely 'EmpMaster','DesigMaster' and 'UnitMaster' having the following structure:EmpMasterImpID varchar(10) pk,UnitCode varchar(10) fk reference UnitMaster(Unitcode)UnitMasterUnitCode varchar(10) pkUnitName varchar(50)DesigMasterDesigID int auto seed(1) pkDesigCode varchar(10)UnitCode varchar(10) fk reference UnitMaster(Unitcode)Now as far this it is quite clear. [b]Business Logic: [/b] (i)An employee may or may not hold designation and it also says that an employee can hold a max three designations.(ii) Every designation is unit specific.(iii)An employee can get transfer from one unit to another.So how a table can be designed so that designations can be tracked with respect to an employee and if an employee gets transfered from one unit to another then how that particular designation be freed to allocate to some another employee..If we create a lookup table also the problem is not solved...As if the unit changes in EmpMaster then designation for that employee should also be resetted...Thanks in advance..

DATEPART Function

moorthy.jy — Mon, 07 May 2012 01:58:13 GMT

Why does this returns 59 seconds ?SELECT DATEPART(SS,CAST('2012-05-06 11:07:09.313' AS DATETIME) - CAST('2012-05-06 11:07:09.317' AS DATETIME))Thanks

Getting text of system stored procedures in 2005

Yelena Varshal — Tue, 04 Jan 2005 15:31:00 GMT

Hi,

I wanted to get a text of a stored procedure in SQL Server 2005 and I could not find it. Nowhere to right-click and select properties. I also could not use sp_helptext. It would say

Msg 15197, Level 16, State 1, Procedure sp_helptext, Line 95

There is no text for object 'sp_adduser'.

I am perfectly able to run sp_helptext 'sp_adduser' in SQL Server 2000. This is important because I have to modify system procedures for some applications. I put the same topic under Administration section too.

Yelena

Charindex backwards i.e from right to left

rabisco — Thu, 03 May 2012 21:35:32 GMT

From the following string.....Embedded link <a class="productLink href=";" macro="productLink" code1="1055288" uid="_13360741834971941">code1</a>skuI'm trying to detemine the position of "<a class="productLink" working backwards from the position of "code1="Thanks

varchar desc order

s.khann786 — Tue, 01 May 2012 00:03:58 GMT

Hi,I am new in sql.i have column rack_location which is having rack number of ware house.data type is varchar(50).i am inserting rack number like c1,c2,c3,c10,c12,c25,c100,c102 .....when i am using order by i am gettingc1c10c100c102c12c2c25c3but i need c1c2c3c10c12c25c100c102plz help methanks in advance

querying xml

rams.prsk — Fri, 27 Apr 2012 00:53:22 GMT

Hi All,From the below shown xml, i nead the sql query to show the output as below:DECLARE @Data XML = '<Data xmlns:ident="http://rddl.xmlinside.net/PowerMeasurement/data/ion/identity/1/" xmlns:ion="http://rddl.xmlinside.net/PowerMeasurement/data/ion/presentvalue/1/" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns="http://rddl.xmlinside.net/PowerMeasurement/data/ion/presentvalue/1/"><ident:GeneratedBy ident:dateTime="2012-04-24T08:12:31.385-08:00" ident:signature="SI-1105B245-02" ident:type="9510RTU" ident:namespace="EnterDeviceNamespaceHere" ident:name="EnterDeviceNameHere"/><ident:Configuration ident:tag2="" ident:tag1="" ident:owner="" ident:template="" ident:revision="9510RTUV365"><ident:Timezone><ident:Bias>480</ident:Bias><ident:DSTBias>-60</ident:DSTBias><ident:DSTStart>2012-03-11T02:00:00.000-08:00</ident:DSTStart><ident:DSTEnd>2012-11-04T02:00:00.000-08:00</ident:DSTEnd><ident:DSTStart>2013-03-10T02:00:00.000-08:00</ident:DSTStart><ident:DSTEnd>2013-11-03T02:00:00.000-08:00</ident:DSTEnd></ident:Timezone></ident:Configuration><Page xlink:title="PDU1A-1" xlink:role="http://rddl.xmlinside.net/PowerMeasurement/data/ion/presentvalue/dataset/" xlink:type="simple" ident:namespace="EnterDeviceNamespaceHere" ident:name="EnterDeviceNameHere" xlink:href="http://192.168.0.32/PDU1A-1.xml" ion:module="Web Page 1"><Item v="481.90" l="PDU1A-1 Volt AB" h="23264"/><Item v="485.30" l="PDU1A-1 Volt BC" h="23265"/></Page></Data>'Output : PDU1A-1 Volt AB PDU1A-1 Volt BC481.90 485.30 Kindly help me on the same

Have table name wants to know DB name

ramadesai108 — Fri, 03 Dec 2010 08:58:19 GMT

I have a table name and I want to know which database contains that table. How do I find out?Thanks.

sql server 2005

eboucicaut — Mon, 23 Apr 2012 08:22:05 GMT

Configuration option 'allow updates' changed from 0 to 0. Run the RECONFIGURE statement to install.Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.Msg 574, Level 16, State 0, Procedure SP_SQL_AGENT_START, Line 13CONFIG statement cannot be used inside a user transaction.The statement has been terminated.

sql server 2005

eboucicaut — Mon, 23 Apr 2012 08:19:32 GMT

can you help me.CONFIG statement cannot be used inside a user transaction.

Insert questions

kabaari — Thu, 19 Apr 2012 08:41:40 GMT

Hi all,I have a table that stores Purchase Order information. I want check the log for PO and item /sku. The PO does not exists then insert. But, if the PO exists but not the sku then Insert the sku record.[code="sql"]If not exists(select r.itnbr, r.ordno from recvLogTest as r, recvTemp as t where r.ordno = t.ordno and t.ordno = 'P046354')Begin insert into recvLogTest(ordno, itnbr, house, poisq, blcod, b_blksq, cmpdt, itdsc, engno, itcls, duedt, umord, qtyor, stkqt, staic, vndnr, vname, b_relqt, b_reldt, b_stkqt, b_staic, pitd1, pitd2, duedate)select ordno, itnbr, house, poisq, blcod, b_blksq, cmpdt, itdsc, engno, itcls, duedt, umord, qtyor, stkqt, staic, vndnr, vname, b_relqt, b_reldt, b_stkqt, b_staic, pitd1, pitd2, duedatefrom recvTempwhere ordno = 'P046354'ENDELSEIF not EXISTS(select r.itnbr, r.ordno from recvLogTest as r, recvTemp as t where r.ordno = t.ordno and t.ordno = 'P046354' and r.itnbr not in (select itnbr from recvTemp where ordno = 'P046354' and itnbr = 'PH01340'))Begininsert into recvLogTest(ordno, itnbr, house, poisq, blcod, b_blksq, cmpdt, itdsc, engno, itcls, duedt, umord, qtyor, stkqt, staic, vndnr, vname, b_relqt, b_reldt, b_stkqt, b_staic, pitd1, pitd2, duedate)select ordno, itnbr, house, poisq, blcod, b_blksq, cmpdt, itdsc, engno, itcls, duedt, umord, qtyor, stkqt, staic, vndnr, vname, b_relqt, b_reldt, b_stkqt, b_staic, pitd1, pitd2, duedatefrom recvTempwhere ordno = 'P046354' and itnbr = 'PH01340'END[/code]The individual item will not load. Any help?

How to find differnce between two dates

mahinder.singh.chauhan — Wed, 18 Apr 2012 14:01:41 GMT

Hi to all i have a query how to find the result between two shifts.like one shift is start from 06:00am to 2:00pm and other shift is start from 3:00 to 11:00 pm ............................ so i want to put a check that 2nd shift timming is always greater than first shift timming...................

Saving a timestamp value

d viz — Mon, 10 Jul 2006 13:10:00 GMT

Hello everyone,I need to store a timestamp value from a record table A, in another table B.Later I want to select all the records from table A that have a timestamp value grater than the one stored in B.What data type should I use to store this timestamp value?

How to save a TimeStamp value

charles-1011021 — Wed, 18 Apr 2012 12:16:22 GMT

I am trying to save a timestamp to a Binary(8) column and converting it to Binary(8), withno luck updating the table where I am tring to save it. This is an example of what I am tryingUPDATE [TS].[dbo].[MPPS Data Vault] SET [LastBarcodeTimeStamp] = (SELECT CONVERT(varbinary(max), MAX(timestamp), 1) as TS From [TS].[dbo].[Barcodes]) WHERE RowCounter = 1Any ideas?

OPENROWSET Access denied error

jchappell 92664 — Wed, 18 Apr 2012 06:45:45 GMT

Running into an issue. I have a stored proc that's trying to bulk load a data file that's located on another server. SQL Server Agent is running under a domain account corporate\sqladmin for example.When the proc runs, I get the error that it can't access the data file on the other server. I have added the domain account to the folder where it's trying to access the file.i'm not sure what else to try.... any help would be appreciated.John

Store result in Variable of dynamic query

Bhuvnesh — Tue, 11 Nov 2008 04:31:21 GMT

hi declare @lstr varchar(200)declare @word varchar(20)set @lstr = 'select top 1 name from sysobjects'set @word = exec(@lstr) select @wordi want ot store result comes from dynamic query into variable @word.(or above code is not working)Please help

sql server 2005

eboucicaut — Tue, 17 Apr 2012 10:28:14 GMT

ALTER DATABASE statement not allowed within multi-statement transaction.No changes made to database. Someone can help me

sql server 2005

eboucicaut — Tue, 17 Apr 2012 09:04:38 GMT

Someone can help me pleaseIF OBJECT_ID ('dbo.Send_decaissements','TR') IS NOT NULL DROP TRIGGER dbo.Send_decaissements;GOCREATE TRIGGER Send_decaissementsON dbo.DECAISSEMENTS_SGXPFOR INSERT ASdeclare @srvr nvarchar(128),@retval intset @srvr ='LK_SGSOL_SGXP' begin try exec @retval = sys.sp_testlinkedserver @srvr end try begin catch set @retval = sign(@@Error) end catch if @retval<> 0 begin insert into decaissements_sgxp_en_attente select * from decaissements_sgxp --RAISERROR('Problème de connexion', 16, 1) -- WITH SETERROR; rollback transaction end else EXEC SP_TRANS_DECAISSEMENT_VERS_SGXP GO"trouble"Unfortunately when connection is down I get error message'Msg 3616, Level 16, State 1, Line 2''Transaction doomed in trigger. Batch has been aborted'and locally made update is rolled back.Is there a way to maintain this error and keep local updates?Note that I'm using SQL Server 2005 on both PCs running Windows server 2003

sql server 2005

eboucicaut — Tue, 17 Apr 2012 09:01:26 GMT

someone can help me please , i got a problemIF OBJECT_ID ('dbo.Send_decaissements','TR') IS NOT NULL DROP TRIGGER dbo.Send_decaissements;GOCREATE TRIGGER Send_decaissementsON dbo.DECAISSEMENTS_SGXPFOR INSERT ASdeclare @srvr nvarchar(128),@retval intset @srvr ='LK_SGSOL_SGXP' begin try exec @retval = sys.sp_testlinkedserver @srvr end try begin catch set @retval = sign(@@Error) end catch if @retval<> 0 begin insert into decaissements_sgxp_en_attente select * from decaissements_sgxp --RAISERROR('Problème de connexion', 16, 1) -- WITH SETERROR; rollback transaction end else EXEC SP_TRANS_DECAISSEMENT_VERS_SGXP GOresultUnfortunately when connection is down I get error message'Msg 3616, Level 16, State 1, Line 2''Transaction doomed in trigger. Batch has been aborted'and locally made update is rolled back.Is there a way to maintain this error and keep local insert?Note that I'm using SQL Server 2005 on both PCs running Windows server 2003

(Triggers)match win/lose trigger Send UPdated Email in sql server

kranthi.india123 — Tue, 17 Apr 2012 03:43:47 GMT

Hi all,how to write trigger Match win\lose\draw send Email for Users.For Example:india vs Aus match..here when user Asked to me india win\lose\draw\tie the Match Updated on send on Email.is it possible?? win and loss or drawn in Match send Updated Mail to User(i mean Match will be started on 4PM and Ending on 8Pm.This situation 7.59PM india win the Match i.e.,automatic updated on user Email or mobile..how????????pls suggest me on send Email????????????????

Join problem when convert joins with non ansi to ANSI format

girishk — Fri, 01 May 2009 08:59:54 GMT

--First Query SELECT DISTINCT branch.branch_no, branch.branch_name, site.site_no FROM site, customer_site, branchWHERE site.site_no *= customer_site.site_no and getdate() between customer_site.start_date and customer_site.end_date and branch.branch_no = site.branch_no and ( site.co_no = 1 ) ORDER BY branch.branch_no ASC, site.site_no ASC--Second Query SELECT DISTINCT branch.branch_no, branch.branch_name, site.site_no FROM site left join customer_site on site.site_no = customer_site.site_no inner join branch on branch.branch_no = site.branch_no WHERE getdate() between customer_site.start_date and customer_site.end_date and ( site.co_no = 1 ) ORDER BY branch.branch_no ASC, site.site_no ASCCan any one help me why both query not returning same count?Thanks in advance .

How do i drop identity for a Column

patelmohamad — Fri, 13 Apr 2012 00:16:16 GMT

Hi guys,I have a table [b]Devices[/b] and i am copying the structure of the table to temp table for further process,i want to disable or remove the identity for the column below query will illustrate my problem[code="other"]IF NOT Object_ID('tempdb..#TempTest') IS NULLBEGIN DROP TABLE #TempTest ENDSELECT Top 0 * INTO #TempTest FROM DevicesTRUNCATE TABLE #TempTest if @value = 1BEGININSERT INTO #TempTest SELECT * FROM Devices where condition1......-- calculation 1ENDELSE if @value = 2BEGININSERT INTO #TempTest SELECT * FROM Devices where condition2......-- calculation 2ENDELSEBEGININSERT INTO #TempTest SELECT * FROM Devices where condition3......-- calculation 3END [/code]the result for the above query is[code="other"](0 row(s) affected)Server: Msg 8101, Level 16, State 1, Line 12An explicit value for the identity column in table '#TempTest' can only be specified when a column list is used and IDENTITY_INSERT is ON.[/code]Please suggest some better solutionhow do i overcome with this problem...Thanks

sql server 2005

eboucicaut — Thu, 12 Apr 2012 10:46:11 GMT

can you help me pleaseHeterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.

Adding Primary key column to existing table

phareesh — Wed, 18 Mar 2009 10:19:26 GMT

I have a table with 10000 records. The table does not have any primary key constraint.Now i want to add a primary column.How can i add a column with primary key constraint to the existing table? Can somebody provide the query to alter the column

Get last record within group of dates

Tammy Robinson — Wed, 11 Apr 2012 13:21:47 GMT

Hi! I am needing some assistance with a date query.I have data that looks like this:Date column counts2012-03-28 2022012-03-21 2022012-03-14 2022012-03-07 2002012-02-29 2002012-02-22 2002012-02-15 2002012-02-08 2002012-02-01 2002012-01-25 2002012-01-18 1982012-01-11 1982012-01-04 198I am needing to extract the count for the last record of each month (something like the maximum date). In this example, I want to extract 200 for January, 200 for February, and 202 for March. Simply using Max(counts) does not work, because the count could lower. I really need the query to look at the last date record as the end of the month for each Period. Does anybody know how to accomplish this task?Thanks

Create Job

eboucicaut — Wed, 11 Apr 2012 07:12:57 GMT

could you help me please.Executed as user: NT AUTHORITY\SYSTEM. The OLE DB provider "SQLNCLI" for linked server "LK_SGSOL_SGXP" reported an error. Authentication failed. [SQLSTATE 42000] (Error 7399) Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "LK_SGSOL_SGXP". [SQLSTATE 42000] (Error 7303) OLE DB provider "SQLNCLI" for linked server "LK_SGSOL_SGXP" returned message "Invalid authorization specification". [SQLSTATE 01000] (Error 7412). The step failed.Boucicaut

sql

asranantha — Mon, 09 Apr 2012 22:42:28 GMT

1)can we call sp into another sp if yes plz give small example?if no plz tell me reason?2)can we call functionn into another function if yes plz give small example?if no plz tell me reason?3)can we call sp in function?if yes plz give small exmp?if no plz tell me reason?4)can we call function in sp?if yes plz give small examp?if no plz tell me reason?