I am able to view the Estimated Execution Plan (Management Studio 9.0) for a query without a problem but when it comes to stored procedures I do not see an easy way to do this without copying the code from the ALTER screen and pasting it into a query window, otherwise it will show the plan for the ALTER and not the procedure. Even after ...
I am working in development under SQL 2008, however I have a SQL 2000 database (in compatibility mode). There are several queries that are using the "TSEQUAL" function to do concurrency checks. It seems however that this function has been completely removed in SQL 2008 and these statements (sitting in SPROCS) no longer compile. What i...
I have a table like so: Fiscal Year, Region, Country, Office1, Office2, Office3, Office4 Where office 1-4 are foreign keys. I would like to get output like so: Office 1: Fiscal Year, Region, Country Office 2: Fiscal Year, Region, Country Office 3: Fiscal Year, Region, Country Office 4: Fiscal Year, Region, Country Can this be done usi...
I have a primary key that I don't want to auto increment (for various reasons) and so I'm looking for a way to simply increment that field when I INSERT. By simply, I mean without stored procedures and without triggers, so just a series of SQL commands (preferably one command). Here is what I have tried thus far: BEGIN TRAN INSERT INT...
My system is for development, and I run a local copy of sql server 2005. I want to move all the database files out of program files, and put in a better location on another partition. To do this would you simply detach all the databases in SSMS, move the .mdf and .ldf files with windows explorer, then reattach? ...
I have a simple SQL table which has a DateTime column. I would like to update all the rows (>100000 rows) with a random date. Is there a simple way to do this a SQL Query? ...
I have the following SQL query: SELECT TOP 200 * FROM article WITH (nolock) WHERE CONTAINS(*,'"ram*" and "1*"') ORDER BY article_number I am getting no results returned within 10 minute. If I stop the query after a few minutes then it returns a few records. In article table there are 10,000 records. The full text catalog is on artic...
The TSQL BEGIN TRY and BEGIN CATCH block pattern does not catch errors due to a lost client connection. How can I catch and handle a lost client connection? I'm setting a flag that indicates processing while processing a loop of individual transactions and the catch block re-sets that flag on error, but if the client connection is lo...
I am working off this example. http://www.drury.net.nz/2005/04/15/specifying-a-sort-parameter-for-a-tsql-stored-procedure/ CREATE PROCEDURE getEmployees ( @ColumnName varchar(100) ) AS SELECT EmployeeID, FirstName, LastName, SSN, Salary FROM Employees ORDER BY CASE WHEN @ColumnName=’...
I use the Sql Server Mgmt Studio to script the creation of our database and all entities. Recently we migrated from SQL Server 2005 to SQL Server 2008 and I'm now using the 2008 version of Mgmt Studio. I'm noticing some small but annoying differences in its scripting support that are making it really hard for me to diff my existing 2005...
Hi all - a SQL Reporting Services Question - for SQL Server 2008. Given that SQL Server Reporting Services features a Scheduler which can be used to schedule the running of SQL Reports, does anyone know a way to programatically (via C#) read a report's history from the Report Server (and then perhaps retrieve the results of the report)?...
We're using an old application that stores dates in C / Unix format. C time is basically the number of seconds since Jan 1st, 1970. The dates are stored as an integer in a SQL Server database. I am writing a view for a report that uses these dates. So far, I'm converting from the UNIX time to a native datetime with: DateAdd(s,3600+uni...
Hi Since SQLSMO is available in .net 2.0 that how i can use it in VS2003 which compiled using .net 1.1. What can i do to this. Is it is possible use of SQLSMO in VS2003????? Plz clarify my doubt???? Thanks in advance... ...
I've been doing a lot of DB refactoring lately and synonyms have come in incredibly useful. When I originally put in the synonyms I was thinking they would be very temporary while I refactor. Now I am thinking there might be some good reasons to keep some of these synonyms around. Has anyone used them as full blow abstraction layer? Wh...
Is Managed code is the only way to use sqlsmo. Can ay body help me ...
Say I have a few tables in the MSSQL database, each with about 5-10 attributes. There are some simple associations between the tables, but each of the table have 500,000 to 1,000,000 rows. There is an algorithm that runs on that data (all of it), so before running the algorithm, I have to retrieve all the data from the database. The alg...
I am trying to connect to SQL Server 2008 (not express) with PHP 5.2.9-2 on Windows XP sp2. I can connect to the remote SQL Server fine from SQL Server Management Studio from the same machine. My first attempt was this: $conn = new PDO("mssql:host={$host};dbname={$db}", $user, $pass); Which gives this error: PHP Fatal error: Unc...
I have the following the query running dynamically SELECT * FROM Vehicles WHERE (DKID IN (69954)) ORDER BY case when ImageName1 = 'na' then 0 else 1 end, Make , Model, Year DESC This is returning the following error: Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'na'. ...
Hi I am using Enterprise Library Data for my Sql database. I am using version 3.1. I am using this code to execute a long running sp (about 1 min). Dim db As SqlDatabase = New SqlDatabase(System.Web.Configuration.WebConfigurationManager.ConnectionStrings("portalConnection").ConnectionString) db.ExecuteNonQuery("spna...
As this article's figure 4 says, SQL Server 2005 + can return you a list of missing indexes. It stores 2 important info about missing indexes: [EqualityUsage],[InequalityUsage] If I have a missing index where: [EqualityUsage]='col1',[InequalityUsage]='col2' Should I create an index with Indexed Key Columns: 'col1,col2' or 'col2,co...