For background see this question: http://stackoverflow.com/questions/1415003/sql-server-xml-data-type-query-issue I'm trying to query against an XML object in SQL Server 2005. The query works fine when there's no namespace defined in the XML. However, when the namespace element is there I cannot seem to get the value for a node element...
The title pretty much says it all. How can I view the original SQL that created a stored procedure in SQL Server 2008? Is this possible? I've been searching online for some leads, but I'm either missing correct vernacular or I'm just looking for something that can be found by some other means. My basic problem is that I've got a SQL ...
Using WITH OVER or by using other method, how to start new sequence number for another group of records? It is SQL Server 2005. E.g. how to get following ouput (I am talking about RowNum column in expected output)? Table: id name 100 A 200 B 300 C 200 B 200 B Expected ouput: RowNum id 1 100 1 200 2 ...
I'm doing a bulk insert from a fixed width text file using INSERT INTO blah SELECT blah1, blah2 FROM OPENROWSET(BULK 'filename.txt', FORMATFILE='format.xml'); It's working fine, except that I want NULLs for the empty (all spaces) fields in the file. This is no problem for fields marked in the format file as SQLINT, SQLDATETIME, et...
Comparing two codes below,both do the same,but with slighty differences: ALTER procedure [dbo].[SP_USUARIOS_UPDATE] @usu_ds varchar(100), @usu_dt_lst_log datetime, @usu_ds_senha varchar(255), @usu_ds_email varchar(100) as begin declare @usu_ID int; create table #TempUser ( UsuID int, S...
I have a column that should contain one of values of 2 power n: 2,4,8,16,32 etc. I want to enforce that on table schema level - is there a way to specify such a column constraint? Thanks! ...
Hi, I just get a problem executing my sql statement, This is the Northwind db. declare @PageIndex int declare @PageSize int declare @PageLowerBound int declare @PageUpperBound int declare @sql nvarchar(4000) select @PageIndex = 3 select @PageSize = 5 SET @PageLowerBound = @PageSize * @PageIndex SET ...
Hello, Well I am a beginner in SQL so plz excuse if this problem is too novice for you. In Northwind (Am sure everyone is aware of Northwind) - I an trying to get for each month the product that sold (by quantity) the most and the one that sold the least in the year 1997 (usind orders, order details, product tables) Thanks UPDATE: Th...
So I have 2 SQL servers, both internal on the firewall, and my webserver is on the DMZ. The webserver currently connects to one of the SQL servers for it's data. I need some information from the other SQL server which I cannot connect to from the DMZ because of security risks that the company is not willing to take. How can I connect ...
Hello all, I'm not an expert in SQL by any means, and am having a hard time getting the data I need from a query. I'm working with a single table, Journal_Entry, that has a number of columns. One column is Status_ID, which is a foreign key to a Status table with three values "Green", "Yellow", and "Red". Also, a journal entry is logged ...
I have a string which contains a comma delimited list of id's which I want to use in a where clause. How can I achieve this? Essentially I have: set @ids = '5,8,14,5324,' SELECT * FROM theTable WHERE Id IN (@ids) but @ids is a varchar and Id is an int. Any ideas much appreciated. ...
I need some help with this procedure: What its supposed to do is try to insert a new user if there is no other with same NAME. If there is a already an user, it should rollback else commit. But it doesn't work, it commits anyway. Any suggestions? SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON GO ALTER procedure [dbo].[SP_USUARIOS_INSERT...
Hi I am not sure if the transaction log is what I need or what. My first problem is that I have a stored procedure that inserts some rows. I was just checking out elmah and I see that some sql exceptions happens. They all are the same error(a PK constraint was violated). Other than that elmah is not telling me much more. So I don't k...
I've been using the entities framework with ASP.NET MVC and I'm looking for an easy and fast way to drop all of the information in the database. It takes quite a while to delete all of the information from the entities object and then save the changes to the database (probably because there are a lot of many-to-many relationships) and I ...
I'm trying to use this query to delete the rows that are already on a linked server's Database: GO USE TAMSTest GO DELETE from [dbo].[Hour] WHERE [dbo].[Hour].[InHour] = (SELECT [InHour] FROM [TDG-MBL-005].[TAMSTEST].[dbo].[Hour]) GO When there is only 1 row in the linked server's table, SELECT [InH...
I am setting up Castle Active Record to access a legacy database on an Microsoft SQL Server. The table in question has a column named function. When I attampt to load a record it gives me this error: Incorrect syntax near the keyword 'function' This error comes about because the SQL query nhibernate generates attempts to access the c...
Well interesting to me at least... Say I have two tables: myLookUpTable: lookupId | Name -------- ----- 1 Red 2 Green 3 Blue and InfoTable: infoId lookupId Amount ParentId ------ -------- ------ -------- 1 1 2 332 2 3 14 332 How would I write a query t...
Does anyone know the range comparison of the BETWEEN clause? if I have a datetime datatype, does the BETWEEN clause compare until hour/minute/second level? ...
This is the output I got from my query. You can see Monthly sales for 1997 is followed by monthly sales of 1998. (It might not show in proper format here but they are all in a row) Month Year Sales --------------------------- 1 1997 61258.07045 2 1997 38483.63504 3 1997 38547.21998 4 1997 53032.95254...
hi, i am doing a unit test against my project. in one case, i have to stop the sql server and verify if my application could handle such exception elegantly. but i found the after this test case has run, any test case run after it would fail if the test case need to access the database. and the error was 'transportation level error'. ...