I have a table structure in SQL Server 2005 which has columns as QuestionID,QuestionDescription,IsRequired and Answer. I have to create a SP in which I have to check a question and on the basis of IsRequired column, I have to do some processing. I cannot use QuestionID as it is not coming as a parameter or so. I am left with only optio...
Is it possible to create unclustered indexes in SQL Server 2005 that are not updated as data is changed (insert/delete/udpate) so I can ensure a stable set of data to report on? Our data is changing frequently an I want to be able to snapshot it at a point with out having a column to show the latest change date/time and only selecting d...
Hello, I have a requirement to "flatten" a resultset in an SSIS package - this is, take a result set consisting of a number of rows and produce a single string with the concatenated values. The result set is returned to me via a stored procedure that for a number of reasons I cannot change. I do not want to duplicate the logic into an...
Is there a way in T-SQL (SQL Server 2005) to assign a whole record to a record variable and then refer to the particular values using column names? I mean, instead of: select @var1 = col1, @var2 = col2 from mytable where ID = 1; and referring to them as @var1 and @var2, something like @record = select col1, col2 from mytable...
I'm trying to get all of the attributes and node values from XML in a SQL query. I tried doing the following, but I'm missing out few values in the output. Here is what I tried: DECLARE @Details xml SET @Details = '<root> <SelectedCategory CategoryId="101"> <ItemID>120</ItemID> <ItemID>256</ItemID> </SelectedCat...
I have a simple query like this SELECT * FROM MY_TABLE; When I run it, SQL Server Management Studio hangs. Other tables and views are working fine. What can cause this? I've had locks while running UPDATE statements before, and I know how to approach those. But what could cause a SELECT to lock? I have run the "All Blocking Trans...
Hi all, I am having an issue figuring out what I thought would be a simple query. I have a table with two fields (EntryDateTime, Message) that as you can guess logs specific messages with a time date stamp for a monitoring app. I am looking to get a count of all messages of a type per day but the time part of the stamp (while needed fo...
Given the SQL statements below, I would expect the result of the query to be: |testX|5| |XXXXX|5| This is because the column is a char(5) and I would expect it to insert blank trailing spaces to fill the remaining space in the column since the inserted values are less than five characters. However, the query actually produces the fol...
We have a COM+ Data Layer that utilized Connection Pooling. Its deployed to 3 clusters, 2 SQL Server 2000 and 1 SQL Server 2005 environment. We noticed today that our monitoring software is reporting Thousands of Logins per minute on the SQL Server 2005 box. I did some tracing in both environments and profiler is reporting this for th...
I want to ask is there any issues or risks involved in installation of SQL Server 2005 Enterprise Edition on SQL Server 2000 Enterprise Edition in production server? Please tell me the guidelines in installation... ...
How do i limit the result of a query (in my case about 60K rows) and select only from the X row to the Y row? If I use ROW_NUMBER() I don't like my query because it involves 2 select queries .. one to return the rows and one to select the portion I need Update: Here's the query I use now: SELECT * FROM ( SELECT row_numb...
Can anybody tell me why my Database selection queries asking for an orderid WHERE [order].dateplaced >= DATEADD(millisecond,-3,ISNULL(@datefrom, @searchscope)) AND [order].dateplaced < DATEADD(millisecond,-3,DATEADD(day,1,ISNULL(@dateto, GETDATE()))) Is missing out an order over three and a half minutes before midnight on the @date...
What I am looking for is to group by and count the total of different data in the same table and have them show in two different columns. Like below. Data in table A Fields: Name - Type Bob - 1 John - 2 Bob - 1 Steve -1 John - 1 Bob - 2 Desired result from query: Name - Type 1 - Type 2 Bob - 2 - 1 ...
Trying to update a table on a linked server (SQL 2000/2005) but my server name will not be known ahead of time. I'm trying this: DECLARE @Sql NVARCHAR(4000) DECLARE @ParamDef NVARCHAR(4000) DECLARE @SERVER_NAME VARCHAR(35) SET @Sql = 'UPDATE @server_name_param.dba_sandbox.dbo.SomeTable SET SomeCol=''data''' SET @ParamDef = N'@server_n...
I am trying to run some update scripts on my database and I am getting the following error: The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_UPSELL_DT_AMRNO_AFMKTG_REF". The conflict occurred in database "ECOMVER", table "dbo.AFFILIATE_MKTG_REF", column 'AMRNO'. I am running the following script: ALTER TABLE [d...
I have a SQL Server 2005 that has a 'Linked Server' to another SQL Server 2005. When I run a simple query on one of the tables against the linked server: SELECT TOP 10 [Pat_Id] FROM [Prod].[PIS].[dbo].[W_PATIENT] This error occurs: Msg 8114, Level 16, State 8, Line 1 Error converting data type DBTYPE_DBTIMESTAMP to datetime. ...
Hi, i'm often dealing with some interfaces between two systems with data-import or data-export. Therefore i'm programming some t-sql procedures. It's is often necessary to use some vaiables inside the procedure, to hold some values or single records. The last time i set up some temp-tables e.g. one with name #tmpGlobals and another name...
I have the following Chars A C W B J M How can I insert some sequential numbers so that after insertion of the numbers the order of characters will not change? I mean if I use row_number(), the output Character order is changing like select ROW_NUMBER() over(order by chars) as id, t.* from @t t Output: id chars 1 A 2 B ...
I've got a simple table that I'm using to represent a hierarchy of categories. CREATE TABLE [dbo].[Categories]( [ID] [int] IDENTITY(1,1) NOT NULL, [Title] [varchar](256) NOT NULL, [ParentID] [int] NOT NULL, CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP...
I am investigating an issue relating to a large log expansion during an ETL process, even though the database is set in bulk logged mode (and it is not running in psuedo simple but truely bulk logged) Using the ::fn_dblog(null,null) function to examine the transaction log operations and the context of the operation, the log expansion is...