sql-server

SQL Server - Filter field contents to numbers only

How can I copy the value of a field, but only its numbers? I am creating a computed column for fulltext search, and I want to copy the values from my Phone Number fields (which are varchar) into it, but not with their formatting - numbers only. What is the command that would do this in my computed column formula? Thank you! ...

T-SQL Dynamic SQL and Temp Tables

It looks like #temptables created using dynamic SQL via the EXECUTE string method have a different scope and can't be referenced by "fixed" SQLs in the same stored procedure. However, I can reference a temp table created by a dynamic SQL statement in a subsequence dynamic SQL but it seems that a stored procedure does not return a query r...

Possible to exclude or reorder a column from `*` ?

Possible Duplicate: SQL exclude a column using SELECT * [except columnA] FROM tableA? Is it possible to exclude a column from a select * from table statement with SQL Server? I have a need for this and this is my only option other than parsing a raw SQL string to get out the required field names (I really don't want to do th...

Escape ContainsTable Keywords

How can I escape ContainsTable control keywords (such as NEAR or ISABOUT) so that Full Text Search will actually look for those words, and not treat them as control keywords? ...

Error while rendering .rdl file into pdf format

I an generating reports using SQL Server reporting services. I have generated a report and have put .rdl report file in the "E" drive. Now, when I am going to render the .rdl report file into pdf format,I am getting the exception : - "An error occurred during local report processing." The stack trace is follows : - at Microsoft.Report...

Speed up SQL Server Fulltext Index through Text Duplication of Non-Indexed Columns

1) I have the text fields FirstName, LastName, and City. They are fulltext indexed. 2) I also have the FK int fields AuthorId and EditorId, not fulltext indexed. A search on FirstName = 'abc' AND AuthorId = 1 will first search the entire fulltext index for 'abc', and then narrow the resultset for AuthorId = 1. This is bad because it ...

Which index is used in select and why?

I have the table with zip codes with following columns: id - PRIMARY KEY code - NONCLUSTERED INDEX city When I execute query SELECT TOP 10 * FROM ZIPCodes I get the results sorted by id column. But when I change the query to: SELECT TOP 10 id FROM ZIPCodes I get the results sorted by code column. Again, when I change the query t...

computed column with aggregate function

I have seven columns in my table: hours, weight, status, total_hours, total_weight and percentage total_weight = weight where status = 'X' total_hours = hours where status = 'X' percentage = total_hours/sum(weight) sum(weight) is an aggregate function I would like to specify formula to generate the three computed columns. What do i...

SQL Server 2005 Merge Replication: Under what circumstances would the base snapshot expire?

Obviously changing schema, adding / removing published articles etc expires the snapshot, but is there anything else that would cause the publication to expire? We're seeing a snapshot expiring without any changes to schema or data on the server. If a snapshot is older than the subscription expiration, would that cause it to be expired...

SQL Substring and Last index of

Hi, i've got stuck with substring. On input i've got a string that looks like Sometext (123456). Those digits at the end are random. I need to get only text from that string. ...

Why does a carriage-return character disappear when converting a string to SQL Server XML datatype?

Whenever I parse a string to SQL XML datatype I notice that carriage-returns disappear whenever whitespace and XML carriage returns are mixed. If they are not mixed things work fine. Does anyone know why this is happening? See the SQL example code below DECLARE @var XML PRINT 'This is a statement with two XML carriage-return character...

Manually removing sql server 2008

I am not able to remove sql server 2008 from my machine. I installed it in the past as a part of the installation of an application, this installed the runtime of sql server 2008 SP0. But now I cannot uninstall. I tried to use the setup of SP0 and even of SP2 but it was not able to uninstall, by giving fatal errors when it does the pre...

Local and Global temporary table in SQL SERVER

What is the difference between Local and Global temporary table in sql server ...

How to use local variables in stored procedures?

if I want to select any id from a table and want to insert it's value in another table as foreign key then how i will do it through stored procedure? ...

At what point should a Table Variable be abandoned for a Temp Table?

Is there a row count that makes table variable's inefficient or what? I understand the differences between the two and I've seen some different figures on when that point is reached, but I'm curious if anyone knows. ...

SQL - Break a start/end time into 15 minute records

I've got a record set that consists of a start and end time in two separate fields: id - Int startTime - DateTime endTime - DateTime I'd like to find a way to query a record and return it as X records based on the number of 15 minute intervals found between the start and end times. For example, let's say I have a record like this: ...

TransactionScope Prematurely Completed

I have a block of code that runs within a TransactionScope and within this block of code I make several calls to the DB. Selects, Updates, Creates, and Deletes, the whole gamut. When I execute my delete I execute it using an extension method of the SqlCommand that will automatically resubmit the query if it deadlocks as this query could ...

How do I Change SQL Server column names in T-SQL?

I have a table with 600+ columns imported from a csv with special chars % _ - in the column names, is there a way to change the column names to remove these special chars ? The code can be in T-SQL. ...

Is it necessary to create ASP.NET 4.0 SQL session state database, distinct from existing ASP.NET 2.0 ASPState DB?

Is the ASP.NET 4.0 SQL session state mechanism backward-compatible with the ASP.NET 2.0 schema for session state, or should/must we create a separate and distinct session state database for our ASP.NET 4.0 apps? I'm leaning towards the latter anyway, but the 2.0 database seems to just work, though I'm wondering if there are any substant...

SQL Server compare table entries for update

I have a trade table with several million rows. Each row represents the version of a trade. If I'm given a possibly new trade I compare it to the latest version in the trade table. If it has changed I add a new version, otherwise I do nothing. In order to compare the 2 trades I read the version from the trade table into my application. ...