The database have Table EMP with 3 columns empID , badgID , XMLDATA. The XMLDATA have datatype "clob" and data is in the form - <Record id="11"> <Demo rID="5"/> </Record> How can I read the attribute "rID" in node "Demo"value in the above XMLDATA in single query? Server - SQL Server 2005 ...
I have two tables: User --------------------- id primary key name varchar mobile varchar Sample data: (1, airtel, '9887456321,6985326598,88523695874') Client --------------------------- id primary key clientname varchar mobileno varchar Sample Data: (1,John Doe, 98874563...
Hello, I'm trying to write a query that gives me a percentage (i.e. something like .885, for example) by dividing 2 aggregate numbers I selected via SUM. But my results are coming out as 0.0 instead of the correct number. So just for reference, starting with 2 queries, I have: SELECT SUM(CASE WHEN Status_ID = 1 AND State_ID = 14 THEN 1...
Hi all, I have a query that looks like this, and which is getting a percentage: SELECT SUM(convert(decimal(3,2), CASE WHEN Status_ID = 1 AND State_ID = 14 THEN 1 ELSE 0 END))/ SUM(convert(decimal(3,2),CASE WHEN State_ID = 14 THEN 1 ELSE 0 END)) * 100 FROM SomeTable This may return something like 59.77803 in a norm...
I have a table var with some rows but only one column of type DATETIME, like this: [Day] 2010-08-03 2010-08-04 2010-08-10 2010-08-11 I need to show on some columns but in only one row. My result set will be limited to 5 rows, then I can limit to 5 columns too. Example of what I need: [Day1] [Day2] [Day3] [Day4] [D...
I have a stored procedure that returns a rowset that I'd like to pass into a CLR stored procedure to do some advanced calculations. How would I set this up? Take the input? Iterate the rowset within the CLR procedure? ...
I am trying to create an payment report which lists the sum in each account. The table is in SQL SERVER 2005 and it has the following table [Account] [Amount] [Type] 1111 10 C 1111 10 C 1111 15 D 1111 5 D 1112 10 C 1112 15 C 1112 ...
I have a question, but let me first say that this is being performed on a database which I inherited and I am currently trying to improve the design on. Also the reason for the syntax is the fact that there are a lot of tables that the datatypes have been 'tweaked' by tech support people (/facepalm) causing lots of issues. IF NOT EXISTS...
Is there support in SQL Server for loading/reading/transferring/importing/fetching/inserting XML, directly from a web-server, into a table? Pretend i want to fetch XML from a web-server, such as: exchange rates published by the Bank of Canada a web-site's current sitemap an rss feed SQL Server 2005 (and newer) has native support fo...
Hi. I have the below SQL which works just fine: SELECT Message, CreateDate, AccountId, AlertTypeId FROM dbo.Alerts UNION SELECT TOP (100) PERCENT Status, CreateDate, AccountId, (SELECT 10 AS Expr1) AS AlertTypeId FROM dbo.StatusUpdates WHERE AccountId = PassedInParameter ORDER BY Cre...
Hi In my production servers i have only excel 2003 and SQL server 2005 installed I have excel 2007 files to be imported is there a way to do this installing service pack, new driver etc.? ...
Hi all, I would like to store images in database by converting them in to binary objects or i will store them in to a temp folder and will save the path in my database. But i am unable to do the programming so can any give me a sample code to save images in to sql database using Asp.net. ...
Is there any system table to find the columns for DDLChangeLog_ID, InsertionDate,CurrentUser, LoginName, Username, EventType, objectName, objectType, tsql ...
Hello. I'm trying to add an aggregation function into SQL server 2005 via C#. But when I deploy the project it fails with the following error: .Net SqlClient Data Provider: Msg 6222, Level 16, State 1, Line 1 Type "AggregationTest.CountNonintersectingIntervals" is marked for native serialization, but field "intervals" of type "Aggre...
If any user gives the information of his photo in binary objects how can i search it and how can i display the corresponding image that was stored in the sql database ...
SELECT * FROM [tbl_students] WHERE course_id=@courseId AND branch_id IN(646768) AND (@firstYrPercent is null OR first_year_percent>=@firstYrPercent) AND (@secondYrpercent is null OR second_year_percent>=@secondYrPercent) AND (@thirdYrPe...
A rollback after an insert that contains an output statement fails with "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION." If the output statement is removed, then it works. Is there an explanation for this behavior? Example: create table test(i integer primary key) go begin transaction insert into test (i) va...
runing a select query on SQL Server 2008, sys.indexes gives me information on the index define for a database . There 2 fields is_unique and is_unique_constraint. I dont understand the difference b/w them . ...
I have to execute a statement like ( i need and keyword along with when ). select 'Is Allowed'= case A.Column when A.Column='XXXX' and Isnull(A.AnotherColumn,'')<>'' then 'Yes' else 'No' end from TableName I am getting syntax error,how to rewrit...
What is the best way to write a dynamic parametrized query for sql server 2005 where passed parameter value may be null? ...