How to find current transaction level?
How do you find current database's transaction level on SQL Server? ...
sql-server-2008
How can I change identity property in SQL Server 2008?
Hi there. I have a table which has no identity column. I want to change a column's identity specification, but SQL Server 2008 doesn't allow that. So, how can I change identity property in SQL Server 2008? ...
How to Get Corresponding datafile path if i pass datafile name
Hi How to get enumfiles method in DMO in SMO means if i pass the master datafil;es to a method then it wil return the corresponding datafile path and logfile path. is there is any such method in SMO ?? for ex:in DMo Enumfiles(master) then it wil return corr filepath . is such method in SMO Plz Help me.. ...
Transaction
In explicit transaction, if i start the transaction by giving BEGIN TRANS but if i dont give COMMIT,ROLLBACK or END TRANS then what will happen to the application? ...
How to retrieve scalar value from stored procedure (ADO.NET)
Hello everyone, If in the stored procedure, I just execute one statement, select count(*) from sometable, then from client side (I am using C# ADO.Net SqlCommand to invoke the stored procedure), how could I retrieve the count(*) value? I am using SQL Server 2008. I am confused because count(*) is not used as a return value parameter of...
Wait for connections to close before restoring SQL Server database
I have a web application that uses two databases. DB1 Users perform their CRUD (Create, Read, Update, Delete) operations. Database DB2 is a readonly database on a different server that i use for reporting purposes. Every hour my DB1 saves transaction logs and on DB2 i have a job that restores them on that DB2 to keep it ~up to date. Pr...
What are the major differences between Sql Server 2005 and Sql Server 2008?
Duplicate Advantages of MS SQL Server 2008 over MS SQL Server 2005? What are the major differences between Sql Server 2005 and Sql Server 2008? How could I convince my organization to invest in Sql Server 2008? For me, one major aspect that I see is that they have a good framework to programmatically generate SSIS packages with the us...
DateTimeOffset.Now in T-SQL
I'm executing a INSERT to a sql 2008 db. How do I specify in T-SQL to insert NOW in a DATETIMEOFFSET column? GETDATE()? ...
SQL server 2008 domain change
In the near future, my organisation will have to change both the machine name and the Windows domain for one of our SQL servers. The server is running SQL Server 2008 Enterprise Edition and the OS is Windows Server 2008 Standard edition. I have found good references to handle the change of machine name, but can anyone help with what sh...
Get columns of a table SQL SERVER
Hi How can I get the name of all columns of a table in SQL SERVER 2008? Thank you ...
Recompile stored procs?
Is there a way to re-compile or at least 'check compile' stored procedures en masse? Sometimes we'll make schema changes - add or drop a column, etc. And do our best to identify affected procs only to be bitten by one we missed, which pukes when it runs next. SQLServer 2k5 or 2k8. ...
visual studio pointing to my sql server
in visual studio i have my code point to a sql server mdf file (in the APP_DATA folder). and i keep having to syncronize this local data to my server. is there anyway in visual studio i can just give it the connection string to my real server so i can debug directly against my server. i know this may be a bit slower but it also might ...
LINQ to SQL - need downloading sqlserver 2008 to a local mdf file
i have a sql server that i use and i am trying to code up a solution using LINQ to SQl in visual studio. is there a way i can: download the database from my server to my local desktop and have it as a mdf file so i can bring it into the app_data folder and it use this for LINQ to SQL code generation. also, please let me know if there ...
SQL Server 2008 Full-Text Search (FTS) extremely slow when more than one CONTAINSTABLE in query
SQL Server 2008 Full-Text Search (FTS) is extremely slow in this scenario: Query 1: SELECT [...] FROM ContentItem CI WHERE (EXISTS (SELECT TOP 1 * FROM CONTAINSTABLE([Table1], *, '"[search_string]*"') FT WHERE FT.[Key] = CI.ContentItem_Id)) ORDER BY [...] Results: super fast on SQL 2005 and SQL 2008 Query 2: SELECT [...] FROM Con...
How to Make this SQL Task Faster to Complete
Suppose table 1 Have 1,000,000 rows. In table 2 there are 50,000 rows INPUT Table 1 Id User InternetAmountDue 1 joe NULL Table 2 InternetUserId UserName AmountDue 21 kay 21.00 10091 joe 21.00 I want to merge data from table 2 to table 1 as follows: If user exists in Table 1, upda...
How to script a database in SQL Server 2008?
I'm trying to export the schema of my database so another developer can have it. On the SQL Server management Studio, if I right click on the database I see a "Script database as" -> "CREATE to" but that generates something like: ... CREATE DATABASE [Name] ON PRIMARY ( NAME = N'Name', FILENAME = N'C:\Program Files\Microsoft SQL Server...
Can you have relationships to system tables/views?
Is it possible to have a relationship from a user table to a system table view? To give context, I would like the values of a column in one of my tables to be restricted to the column names of another one of my tables, which seems easiest to do by going through the system view that houses the column names of the second table. So, using ...
Application cannot find Microsoft.SQLServer.SMO on SQL 2008 machine
Hi, I have code that uses Microsoft.SqlServer.Management.Smo. (Built in Visual Studio 2005 SP1) It works fine on SQL 2000 & SQL 2005 machine. But when it is run on SQL 2008 machine it throws an exception: System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, Pu...
Is it possible for the data to be returned in different column order between execution when you run SELECT * FROM multiple table joins multiple times?
For example I have the following tables resulting from: CREATE TABLE A (Id int, BId int, Number int) CREATE TABLE B (Id int, Number decimal(10,2)) GO INSERT INTO A VALUES(1, 3, 10) INSERT INTO B VALUES(3, 50) INSERT INTO A VALUES(2, 5, 20) INSERT INTO B VALUES(5, 671.35) GO And I run the following query multiple times: SELECT * FROM ...
Whats the Best Practice for a Search SQL Query?
I have a SQL 2008 Express database, which have following tables: CREATE TABLE Videos (VideoID bigint not null, Title varchar(100) NULL, Description varchar(MAX) NULL, isActive bit NULL ) CREATE TABLE Tags (TagID bigint not null, Tag varchar(100) NULL ) CREATE TABLE VideoTags (VideoID bigint not null, ...