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...
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...
SELECT * FROM VNTREAT WHERE VN = SOME ('482')
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '482'.
...
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 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!
...
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...
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...
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...
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...
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...
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...
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 ...
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
...
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...
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...
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?
...
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...
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
...
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...
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...