Tag: sql-server-2005

Summarize Data for Report using T-SQL Case Statement

I want to create a simple summary report in Reporting Services using age, account and age group as follows: SELECT AGE,COUNT(ACCOUNT)AS TOTALCASES, ‘AGEGRP’ =CASE WHEN AGE <=5 THEN ‘AGE 0 TO 5’ WHEN AGE >=6 THEN ‘AGE 6 AND OLDER’ END FROM MAIN GROUP BY ‘AGEGRP’ When I run this in SQL Server Management Studio, […]

Long query prevents inserts

I have a query that runs each night on a table with a bunch of records (200,000+). This application simply iterates over the results (using a DbDataReader in a C# app if that’s relevant) and processes each one. The processing is done outside of the database altogether. During the time that the application is iterating […]

How to debug stored procedures in sql server 2005

How can I debug stored procedures in SQL server 2005.

SQL Server stored procedure issue calling another stored procedure

Here’s a issue I have with a stored procedure (using SQL Server 2005), inside this stored procedure it calls another stored procedure putting the data into a temp table. INSERT INTO #tmpTable (Column1, Column2, Column3) EXEC psp_rptInsideStoredprocedure 2 This inside stored procedure has a mode parameter that determines which columns get passed out. In this […]

Return a recordset from a SQL CLR Assembly?

Guys lease help me out I think I’m close, but I’m missing something. Background I’m rewriting a SQL CLR assembly (stored procedure), my assembly contacts another resource and gets back XML. I want to return this XML as a recordset, NOT a scalar value. From what I’ve seen, this is how to return a recordset: […]

SQL Server Blocking Issue

We currently have an issue that occurs roughly once a day on SQL 2005 database server, although the time it happens is not consistent. Basically, the database grinds to a halt, and starts refusing connections with the following error message. This includes logging into SSMS: A connection was successfully established with the server, but then […]

How can I have Sql Server 2005 asynchronously call a DOS batch file from a DDL trigger?

I created a batch file to run SqlMetal and generate Linq2Sql data classes, check into source control triggering a build, etc… I’d like to have this script run anytime there is a DDL change in Sql Server 2005. Running the batch file via xp_cmdshell works fine outside of a trigger, like this: exec master..xp_cmdshell ‘d:\dev\db_triggers\generatedataclasses.bat’, […]

SQL Server – A script to loop through all remote tables and perform “Select * into …'

Here’s what I’d like to do. For each table in linkedserver.database whose tablename is like ‘text%’ (inside loop) A. If current_table exists locally, drop it B. select * into table.name (local) from linkedserver.tablename (copy schema + data) C. Possibly check for errors and Print some text about it? Next Any idea if this script is […]

Sql Server 2005 SSIS/Agent – Query status of a job

Is there a way to query the current status (executing, idle, etc) and the last result (successfull, failed, etc), and the last run time for a specific job name? The end result I am looking for is being able to display this information in an internal web application for various SSIS packages.

SQL Server Performance Problem

Our primary database server is an 8 core box with 8GB of RAM. The CPU is a Xeon E7330 @ 2.4GHz. It runs Windows Server 2003 R2 (x64 edition) and SQL Server 2005 I wanted to do some testing so I set up SQL Server 2005 on another brand-new server which is an 8 core […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.