sql

Reclaim space in SQL 2005 table after changing datatype?

I inhereited a database with tables with many nvarchar columns. The tables were getting fairly large, and I decided to change the datatypes to varchar to cut storage because we do not use international characters. The "data space" on the table (Right-click, then "Properties") has not changed. However, if I copy this table into a new tabl...

How to override SQL sanitization in ColdFusion

I have the unfortunate task of cleaning up a bunch of old ColdFusion code. Queries are all over the place, I am working on moving them all to common CFCs for easier maintenance. I am running into a problem because cfquery is automatically converting the single quotes to double-single-quotes. How can I override that behavior? More spe...

How do you access data that has been inserted using a ADO.NET transaction?

I'm trying to get the data that has been successfully input into the database via ADO.NET transaction. Once you've called trans.Commit() there doesn't seem to be a way of getting back the data that has been committed since all identity columns that were created during the transaction are 'virtual' since it is an offline dataset until co...

Audit whether stored proc was executed - in the transaction logs

We have SQL Server 2005 database with full backup and transaction logs. We have a problem with the database - and need the SQL CSI Forensic team to help. Is there a way to look at the transaction logs and identify whether a stored procedure was executed? We know the time that it happened (if it happened) but there is a dispute wheth...

SQL Sum Column before inserting into #temp

In my SPROC a table named #temp1 contains the following columns: #temp1 (StoreId, StoreDesc, ReservedQty, AvgPrice, QtyOnHand) My question is based on the following query INSERT INTO #temp2 (StoreId, StoreDesc, CommittedQty) (SELECT StoreId, StoreDesc, CASE WHEN ReservedQty > QtyOnHand THEN sum(QtyOnHand * AvgPrice) ELSE su...

How do I create a row specific sql cache dependency?

I want to use data caching on my .net C# application. So far I added data caching and added sql cache dependencies on specific tables. But thats not good enough. These tables will be updated too frequently but not relevant to a lot of the cached objects. This will make the data caching almost useless because it will be flushed to frequen...

Which of these select statements is "better," and why?

Hi all, I have 2 table person and role. I have to all the persons based on role. select person.* from person inner join role on person.roleid = role.id Where role.id = @Roleid or select person.* from person inner join role on person.roleid = role.id AND role.id = @Roleid Which one of the above two solutions is better and Why? ...

.NET DataReader and SQL joins

string query = "SELECT * FROM table1, table2 WHERE table1.Id = table2.fId"; ... using(IDataReader dataReader = db.ExecuteReader(CommandType.Text, query)) .. string value = dataReader["table2.field"]; //dies I'm currently writing some .NET code which involves executing a join query and then accessing the returned data using a ...

Copy Data from a table in one Database to another separate database

Hello Basically I have a two databases on SQL Server 2005. I want to take the table data from one database and copy it to another database's table. I tried this: SELECT * INTO dbo.DB1.TempTable FROM dbo.DB2.TempTable This didn't work. I don't want to use a restore to avoid data loss... Any ideas? ...

Is it possible to use GROUP BY with bind variables?

I want to issue a query like the following select max(col1), f(:1, col2) from t group by f(:1, col2) where :1 is a bind variable. Using PreparedStatement, if I say connection.prepareStatement ("select max(col1), f(?, col2) from t group by f(?, col2)") I get an error from the DBMS complaining that f(?, col2) is not a GROUP BY exp...

Concat groups in SQL Server

If I have a table like this: +------------+ | Id | Value | +------------+ | 1 | 'A' | |------------| | 1 | 'B' | |------------| | 2 | 'C' | +------------+ How can I get a resultset like this: +------------+ | Id | Value | +------------+ | 1 | 'AB' | |------------| | 2 | 'C' | +------------+ I know this is really easy ...

Using TSQL, how can I tell if my CHAR is last one in a sequence? Also, what if the letter sequences "rolls over" from 'z' to 'a'?

Let's say I have the following table in SQL Server 2005: id | Date | Letter ----------------------------- 01 | 5/1/2009 | W 02 | 5/1/2009 | X 03 | 5/1/2009 | Y 04 | 5/1/2009 | Z 05 | 5/1/2009 | A 06 | 5/1/2009 | B 07 | 5/1/2009 | D 08 | 5/1/2009 | E 09 | 5/2/2009 | W 10 | 5/2/2009 | X 11 | 5/2/2009 | Y 12 | 5/2/2009 | Z 13 | 5/2/2009 | ...

Is it possible to create a table with a variable name in Postgre SQL?

Using PL/pgSQL or (some other mechanism), is it possible to create a table with a variable name? I would like to create multiple tables named table_1, table_2, table_3, etc... and it would be simpler if I could use a loop to create them, instead of explicitly creating each one. I suspect the answer to this is no, but I would like to co...

What happens if you close a SqlConnection before the SqlDataReader?

What would happen if you call Close() on a SqlConnection object before you call Close() on a SqlDataReader using that connection? Actually, what I really want to know is whether or not the order in which you Close them matters. Does calling SqlConnection.Close() completely close the connection, or will it remain open if you do not call...

How can I fix this SQL error on this query

DECLARE @providerIdList varchar(400) DECLARE @q varchar(400) SELECT @q = '' SELECT @providerIdList = '(1, 5, 15)' SET @q = 'SELECT u.Id FROM [user] u LEFT JOIN Provider p ON u.Provider_FK = p.Id LEFT JOIN Providers2Users pu ON pu.user_FK = u.Id LEFT JOIN Provider ap ON ap.Id = pu.provider_fk WHERE p.Id IN ' + @pr...

SQL Server: Extracting a Column Into a Table

I have a table with a column that I want to extract out and put into a separate table. For example, lets say I have a table named Contacts. Contacts has a column named Name which stores a string. Now I want to pull out the names into another table named Name and link the Contact.Name column to the Id of the Name table. I can only use ...

Reporting Services Chart - Custom Axis Label

Hello, I have a SQL Server Reporting Services (2008) chart (error chart). The X-axis has date intervals 1/1/2009, 2/1/2009, etc. The Y-axis has numeric intervals of 50. Values on the Y-axis are 0, 50 and 100. However, instead of displaying 0, 50 and 100 i would like to display "Small","Medium" and "Large" respectively. Does anyone know ...

Extract Unique Pairs from Table Containing Reciprocal Relationships

Consider a SQL Server 2005 database with a bunch of data on a lot of people, a large chunk of whom are married. To track relationships among people, a Relationship table exists that serves to link the ID of one person to the ID of another. A Relationship Type on each Relationship record serves to indicates the type of relationship exists...

Copy data from one field to another on every row

So I've got: id number 1 0 2 0 3 0 Is there a sql statement to copy everything from id into number? I'm about to write a php scrip to select, then update every row. My SQL knowledge is pretty basic, but I'm sure there's a smart guy way to to do this: Background: The id used to be a number that was displayed in...

VBScript SQL sanitization

Wary of Jeff Atwood's "Bathroom Wall of Code" post, I thought it would be useful to have a trustworthy SQL sanitisation function for VBScript, similar to PHP's mysql_real_escape_string() function. So, how can I properly sanitise data input into a SQL query using VBScript? ...