sql-server

Sql Server 2008 not reporting compile errors for missing objects

SQL Server 2008 doesn't always report a compile error when a stored procedure references an object that doesn't exist. Is there a way to cause it to always report errors? ...

Access is denied when attaching a database

I am using SQL Server 2008 developer edition. I was trying to attach the AdventureWorks2008 database. When I tried to attach, I received an "access is denied" error. According to the event log, it came from the O/S: Open failed: Could not open file D:\ProjectData\AdventureWorks\AdventureWorksLT2008_Data.mdf for file number 0. OS ...

Find broken objects in SQL Server

Is there a tool that will find all objects in SQL Server (functions, procs, views) that cannot possibly work because they refer to objects that don't exist? ...

Insert 2 rows that are related in both directions in SQL

I have two tables kinda like this: // Person.Details Table: PersonID int [PK] | EmployeeCreatorID int [FK] | FirstName varchar | OtherInfo... // Employee.Details Table: EmployeeID int [PK] | PersonID int [FK] | IsAdmin bit | OtherInfo... Each table is related to the other: [Employee.Details.PersonID]===>[Person.Details.PersonID] AND...

Modify SQL result set before returning from stored procedure

I have a simple table in my SQL Server 2008 DB: Tasks_Table -id -task_complete -task_active -column_1 -.. -column_N The table stores instructions for uncompleted tasks that have to be executed by a service. I want to be able to scale my system in future. Until now only 1 service on 1 computer read from the table. I have a stored proc...

how to create tables using sql schemas.

how to create the schema, AND USING SCHEMA HOW TO create tables under the schemas ...

Creating an audit table in SQL Server not affected by Transaction rollbacks

Hello All, I'm new to SQL. I have a large number of stored procedures in my production database. I planned to write an audit table that would be used by these stored procedures to keep track of changes ( these stored procedures would write to this audit table ). But the issue is that when a transaction rolls back, the rows inserted into...

Why are SQL-Server UDFs so limited?

From the MSDN docs for create function: User-defined functions cannot be used to perform actions that modify the database state. My question is simply - why? Yes, a UDF that modifies data may have potentially unwanted side-effects. Yes, there is overhead involved if a UDF is called thousands of times. But that is the whole point of d...

SQL CLR Programmability - Synchronization

Hello All, I'm new to SQL CLR programmability. I have a CLR stored procedure that writes to a local file. But when I have multiple connections calling the same stored procedure, how do I synchronize access to the local file? Any inbuilt features? Or any other input is welcome. ...

How do I test database migrations?

I'm using Migrator.NET to write database migrations for the application. Marc-André Cournoyer wrote: Like any code in your application you must test your migrations. Ups and downs code. Do it part of your continuous build process and test it on as many different databases and environment as you can. How do I do that? Say I ...

how can i determine a day name in sql server 2005??

i want to know the query so that i can get the name of today`s day ...

An app to search a database (SQL Sever)

Im not a developer (not a proper one) but always up for an excuse for self-development. I work in a support team for a reputable software vendor, and we currently use a helpdesk piece of software called iSupport. - Its not a bad piece of kit, and im not sure if it has been set up badly (trying to find out) but the biggest problem I fa...

Equally divide resultset into groups, with cursor or not?

I'm building a race administration system, with drivers and race heats. I need to divide, lets say, 13 drivers into groups of maximum 6 per group. It's not possible since the result will be 2.2 groups, wich is impossible, 3 groups is required. Smaller groups than 6 is allowed, so I decide to divide 13 by 3 to accomplish the follwing di...

sql stored procedure clear resultsets

Can I clear the multiple result sets that a stored procedure has already gathered so that the next result set that is created becomes the very first result set returned by the procedure? ...

SQL Server UPDATE from SELECT

Thanks in advance guys. Been searching quite a bit for this and I'm either searching badly, or there isn't much out there to explain it. In SQL server you can insert into a table using a select statement. INSERT INTO table(col,col2,col3) SELECT col,col2,col3 FROM other_table (in this case is a temp table) WHERE sql = 'cool' Would be...

T-SQL Script unconsitant in running time. Why?

I've got this script that runs on a SQL Server Express 2008 machine. /* BUD501T */ declare @ErrMsg nvarchar(4000) declare @ErrLine nvarchar(100) declare @currentFY nvarchar(1000) declare @programName nvarchar(1000) set @ErrMsg = '' set @ErrLine = '' set @programName = 'BUDPROD - GL_Exp.sql' select @currentFY = value from Budget...

Year to date per month

Hi all, I have this table called Sales: ID Date DepartmentID Amount 1 10-12-2009 12 10 2 18-01-2010 3 23 3 08-02-2010 4 7 ... Now I need to retrieve YTD values from the Amount column for each month and department. First I tried this query: SELECT MonthSales.[Month], MonthSale...

CakePHP model useTable with SQL Views

I'm in the process converting our CakePHP-built website from Pervasive to SQL Server 2005. After a lot of hassle the setup I've gotten to work is using the ADODB driver with 'connect' as odbc_mssql. This connects to our database and builds the SQL queries just fine. However, here's the rub: one of our Models was associated with an SQ...

Consolidating subsets in a table

I have a table in SqlServer 2008 with data of the form UserID StartWeek EndWeek Type 1 1 3 A 1 4 5 A 1 6 10 A 1 11 13 B 1 14 16 A 2 1 5 A 2 6 9 A 2 10 16 B ...

Transfer permissions from one domain to another in SQL Server

At the bottom of most of our stored procedures we have a grant similar to GRANT EXECUTE ON [dbo].[uspFOO] TO [DOMAIN\SQLServerUsers] Luckily for me, our domain is changing and we now need to go through and change the permissions. Does anyone know of an easy way to do this using the DB metadata so I can pull out all the places where ...