I'm in a very, very tight situation here. I have an SQL query running on SQL Server 2005: SELECT col1,col2,col3 FROM myTable Which of course gives: col1 | col2 | col3 ------------------ 1 | a | i 2 | b | ii etc I need to, if possible, add a COUNT query so that it will return the number of records returned. I cannot use...
I have created a stored procedure CREATE PROCEDURE GetCustomerWiseSales(@StartDate nvarchar(10), @EndDate nvarchar(10)) AS SELECT C.cCode, min(C.cName) as Customer, sum(P.BeerValue) as BeerValue, sum(P.RestGroup)as RestGroup from Customers C Join ( SELECT Sales.CustomerID, SUM(SalesLog.Quantity * SalesLog.Price) as BeerValue, 0 Re...
I have a project that requires me to do development in SQL Server 2005, but do deployments to a SQL Server 2000 box. For 99% of the SQL code, I have no problems, everything appears to be backwards compatible. Now I am just about to start adding all the Stored Procedures (SPs) to source control, and I like the idea of doing a drop-add e...
Given a folder full of simple XML documents that all have the same structure, is there a quick way to either load all the documents into a temporary table or treat the folder as a table, in order to query the documents with the SQL 2005 XML query syntax? The bulk load examples that I have seen, all try to parse the XML document while lo...
I am generating a very large dataset into an XML file to send to an external web service. This file is about 20 megabytes and has a validation error somewhere near character 18995504 of the only line in the file. DECLARE @Text nvarchar(MAX) SET @Text = (SELECT xml FROM (...) multiLeveledQueryFromHell) SET @Text = '<root xmlns="urn:exam...
I have 2 tables, one called dbo.dd and one called dbo.gt. where dbo.gt.v_products_model = dbo.dd.[Vendor Stock Code] I would like to update the field dbo.gt.v_products_price with the dbo.dd.[Dealer Ex] Sorry, forgot syntax of SQL 2005 and in a jam! ...
Consider this simple user defined function: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER FUNCTION [dbo].[ufn_GetFirstDayOfMonth] ( @pInputDate DATETIME ) RETURNS DATETIME BEGIN RETURN CAST(CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' + CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/01' AS DATETIME) END whi...
I'm trying to write this transaction where I'm trying to add a column to a table using TRY..CATCH and TRANSACTION. Here's my code. But the problem is the column already exists in the table and the catch block should execute, but the catch block is not executing and the transaction is not being rolled back and also the select error_number...
Given the following (heavily simplified) tables: create table Tags ( TagId int Primary Key ) create table OrderLines ( Branch int, Station int, TransNo int, TagId int foreign key references Tags, primary key (Branch, Station, TransNo) ) I need a list of Tags along with an OrderLine which references each Tag. I am ex...
Hello, im using the PATINDEX sentence into a case sentence: select Choosed1= CASE PATINDEX('%1|%',field1) //Here im getting an error: WHEN >0 THEN 'X' END from testtable How could i put the >0 condition to avoid the error? Thanks in advance Best Regards. Jose ...
I'm working with some SQL 2005 CLR code written in C#. We have recently altered a few of the functions to allow NULL parameters. We did this by changing parameters from 'double' to 'SqlDecimal' types. We successfully tested the changes in development and have moved to deploy the updates to the production server. We're using a SQL script ...
I’ve had an open ticket with Microsoft on a timeout issue we are having for over a month now with no resolution. I’m throwing the problem out to see if anybody else is experiencing this and any suggested work-arounds. SS2005, SP3 database running on WinSrvr2003 cluster with NETAPP SAN. Problem is that we are experiencing dotnet applic...
I'm trying to set up a Report server on my local machine, but I cannot get past the Database Setup step in the Reporting SErvices Configuration Manager. I have SQL Server 2005 on my machine, which runs XP SP3. I have reporting services installed, and the service is running, but anytime I try to setup the Reports and ReportServer datab...
Hi, I'm not a real database expert, and i was wondering if you could tell me what kind of lock Sql server uses in combination with the entity framework in the following cases: I read 40 records from a table, update them in code and call SaveChanges to the context I read 1 record from a table, update it in code and call SaveChanges to ...
Hello, Hope someone can help - I am a novice SQL hacker (and very bad at it indeed!) I have two tables on SQL Server 2005 TABLE 1 and TABLE2: TABLE1 COL1 COL2 1 10 2 20 3 30 4 10 4 20 5 20 6 30 7 10 7 20 TABLE2 COL1 CO...
64 bit windows server 2008, sql server 2005. 4 processors. Database is in simple recovery model- when the following batch script is run, the transaction starts to rollback at "ALTER TABLE PS_RC_CASE_AUDIT ADD Z_TMP_ALTER_1 NVARCHAR(80) NULL" - the following are the wait types observed. We have had to roll back this transaction already a ...
I have a statement which is complicated like this: select x.ColA as ColA , case when x.optA = 'AB' or x.optA = 'FG' or x.optA = 'LM' or x.optA = 'QR' then X.ColB / 100 else X.ColB / 900 End as ColB , case when x.optA = 'AB' or x.optA = 'FG' or x.optA = 'LM' or x.optA = 'QR' then X.ColC / 100 else X.ColC / 900 End as ColC ,...
SELECT Date_Received, DateAdd(Year, DateDiff(year, Cast('3/01/2010 12:00:00AM' as DateTime) , GetDate())-1, Cast('3/01/2010 12:00:00AM' as DateTime)) as minimum_date FROM [Volunteers].[dbo].[Applications] WHERE Date_received >= DateAdd(Year, DateDiff(year, Cast('3/01/2010 12:00:00AM' as DateTime), GetDate())-1, Cast('3/01/2010 12:00...
DBMS: MS Sql Server 2005, Standard I'd like to make a table constraint to have only one record have a particular value within a subset of the table (where the rows share a value in a particular column). Is this possible? Example: I have records in myTable which have a non-unique foreign key (fk1), and a bit column called isPrimary to ...
Hi consider that I have 3 tables(Order and OrderDetials and Products) and each these entities has attributes and I have saved some data in those tables and now I want to save all those data and keep them some where and then change some value of those attributes and start a new work with them how can I do that can I put those useless data...