tsql

SQL Server 2005 truncates strings at double quotes

When I update the pollQuestion and pollName column that contain double quotes using the stored proc below, it saves the double quotes properly. However, for columns option1,...,option9, the stored proc doesn't save the double quotes or any characters after the double quotes. It's as though SQL Server truncates the string prematurely at t...

Is SQL Server Naming trailing space insensitive?

I have a Location table in my OLTP which, while coding SSIS against it, I found a LocationCode column name to have a trailing space in it. Create Table Location ( [LocationId] INT IDENTITY (1, 1) [LocationCode ] INT ) Note that LocationCode column name has a trailing space. And yet, the following code works. SELECT LocationC...

SOME in T-SQL: how to use it?

SELECT * FROM VNTREAT WHERE VN = SOME ('482') Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '482'. ...

Insert results of subquery into table with a constant

The outline of the tables in question are as follows: I have a table, lets call it join, that has two columns, both foreign keys to other tables. Let's call the two columns userid and buildingid so join looks like +--------------+ | join | |--------------| |userid | |buildingid | +--------------+ I basically need to...

Is there a quick way to change the keys and foreign keys of my database during the development stage with script

Is there a quick script I can use in SQL Server to change the ID from int to unique identifier without physically going through and doing it manually. I have contemplated code gen to achieve this, but I would have thought using TSQL would be the quickest if possible that is! ...

Begin and monitor progress on long running SQL queries via ajax

Is it possible to start a query that will take a significant amount of time and monitor progress from the UI via Ajax? I considered starting the process as a "run once" job that is scheduled to run immediately. I could store the results in a temporary table for quick retrieval once it's complete. I could also log the run time of the rep...

SQL - WHERE clause Return results with next working day

Hi, I seem to be asking a lot of SQL questions at the moment. (I would normally write a program to sort the data into a report table, however at the moment that is not possible, and needs to be done using SQL) The Question I need a where clause that returns results with the next working day. i.e. Monday 01/01/01 the next working day wou...

detach database/take offline fails

Hi, I'm currently in the process of detaching a development database on the production server. Since this is a production server I don't want to restart the sql service. That is the worst case scenario. Obviously I tried detaching it through SSMS. Told me there was an active connection and I disconnected it. When detaching the second t...

Max date in view on left outer join

Thanks to a previous question, I found out how to pull the most recent data based on a linked table. BUT, now I have a related question. The solution that I found used row_number() and PARTITION to pull the most recent set of data. But what if there's a possibility for zero or more rows in a linked table in the view? For example, t...

Cleaning up a database

I have a database with hundreds of tables. I am building a script to delete all of the rows in this database. Of course, being a relational database, I have to delete rows from the children before I can touch the parents. Is there something I can use for this or do I have to do this the hard way? EDIT Accepted Answer was modified t...

How to get stored procedure @@ERROR value in entity framework ?

Hello, I'm using .NET 3.5 SP1. I have a stored procedure returning a result of a transaction as shown below: Create PROCEDURE SetPrice @itemId int, @price int AS DECLARE @myERROR int -- Local @@ERROR , @myRowCount int -- Local @@ROWCOUNT SET NOCOUNT ON BEGIN TRAN1 UPDATE item_price_table SET price=@price WHERE...

Navigating the results of a stored procedure via a cursor using T-SQL

Due to a legacy report generation system, I need to use a cursor to traverse the result set from a stored procedure. The system generates report output by PRINTing data from each row in the result set. Refactoring the report system is way beyond scope for this problem. As far as I can tell, the DECLARE CURSOR syntax requires that its ...

[sp_executesql] Incorrect syntax near 'sp_executesql'

I don't understand why the following is giving me the error. I thought it was related to the commented out section, but @SQL is nvarchar(4000). BEGIN sp_executesql N'SELECT ''td''' --sp_executesql @SQL, N'@StartDate DateTime, @EndDate DateTime, @End2 DateTime, @Program varchar(4)', @StartDate, @EndDate, @End2, @Program END ...

Microsoft Access ADP UPDATE Query does NOT update

I have a (very simple and standard) UPDATE statement which works fine either directly in Query Analyser, or executed as a stored procedure in Query Analyser. UPDATE A SET A.field1 = B.col1 , A.field2 = B.col2 FROM tblA AS A INNER JOIN tblB AS B ON A.pk1 = B.pk1 AND A.pk2 = B.pk2 Problem is when i execute the same st...

How to make SQL Server 2008 Check Constraint of a table Allow Only Certain characters?

I'd like to create a check constraint for a table in SQL 2008 which would allow A-Z characters (non case sensitive), numbers, hyphen (-), dot(.), space and underscore (_). Below is my current expression: ([company_code] not like '%[^A-Za-z0-9_ .+]%'). It fulfills all the above requirements except for hyphen. How could I make the express...

SQL 2005 - Query to Find Tables with Most Rows

I searched for this for a while but came up empty ... hopefully someone here can help. Is there a query I can run on a database (SQL Server 2005) that will return the number of rows in each table? ...

Does a DDL trigger have visibility to the previous state?

A question came up in relation to another question regarding DDL triggers. If I have a DDL trigger such as the one below that is meant to warn or rollback a change if the parameter list changes, is there a way to get the previous state of (for example) the parameter list? As you can see, the trigger already references the new parameter...

Does LINQ to SQL support the t-sql "in" statement

I dont't know how to describe the title better (so feel free to change) but essntial I want to produce the equivilent of the following statement in LINQ to SQL: select * from products where category in ('shoes','boots') The fields will be coming from the query string essentially (more secure than this but for ease of explanation) i.e ...

What kind of return on investment is there in learning Set Theory, with respect to database design and query?

I am learning database right now -- and some of the queries are getting just plain crazy. I know that during query optimization you may sometimes rewrite queries in different ways to minimize certain things. I am looking at a correllated subquery example and see that it results in the same resultset as a LEFT JOIN. I am thinking that...

[SQL Server 2005]: XML query() works, value() requires singleton found xdt:untypedAtomic

I have a typed xml document stored as text. So I use CONVERT the data type to xml by using a Common Table Expression in order to be able to use XML methods: WITH xoutput AS ( SELECT CONVERT(xml, t.requestpayload) 'requestpayload' FROM TABLE t WHERE t.methodid = 1) SELECT x.requestpayload.query('declare namespace s="http://blah...