sql-server

How to debug TSQL on SQL Server 2005?

Hi guys, How can I debug TSQL on SQL Server 2005 instance? Thanks. ...

SQL Server: pushing development to production?

I'm currently using SQL Server Management Studio. I have a development database, and access to a live database. I just want to able to migrate my development tables to the live server. I'm consulting stack flow first, since I don't want to blow anything up. ...

Make your application DHCP recognizable like SQL Server instances

As you people know that you can easily identify systems in your network that are running SQL SERVER INSTANCE but is this possible that I can force DHCP to recognize my application? example: I have many clients and a single server. but in secure networks out-bond traffic and port listeners are blocked. so I am trying to use reverse model...

Shared Stored procedures for Multiple Databases

We have an application that has multiple identical databases apart from customer customizations. we would like to centralize the stored procedures that are common across all databases. Currently, we are having problems with keeping all databases up to date as customizations are done in the common database. We have tried to make some cus...

SQL Server 2008 Transaction, rollback required?

Hi, I have a stored procedure that has a BEGIN TRANSACTION and COMMIT TRANSACTION statement. Within the transaction is a select query WITH(XLOCK, ROWLOCK). The transaction can potentially fail due to some calculations that cause an arithmetic overflow error if out of bounds values are supplied. This error would happen before any inser...

jitterbit sql server problem

To whom it may respond to, I got this message while trying to design a synch job for 2 different tables from 2 different databases : "Last reported error: SqlState = 42S22 NativeError = 207" [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'Credit Limit'." Have modified the config file to use quotes instead of bracke...

Error in IQueryable + foreach

I just want to know if there is any known issue, or if I'm doing something wrong. Everytime I do something like the example below, I get an error "the server failed to resume the transaction". I had already asked another question about this error, but now I've figured out it only appears in the foreach loops //Listing orde...

UNIQUE constraint controlled by a bit column

Hi. I have a table, something like FieldsOnForms( FieldID int (FK_Fields) FormID int (FK_Forms) isDeleted bit ) The pair (FieldID,FormID) should be unique, BUT only if the row is not deleted (isDeleted=0). Is it possible to define such a constraint in SQLServer 2008? (without using triggers) P.S. Setting (FieldID, FormID, isDel...

How to only add values that end with 's'?

I'd like to do this but I know this isn't the right syntax: INSERT INTO TBL1 SELECT Col1 FROM TBL2 WHERE Col1.endswith('s') ...

Get value inserted in RowGuid?

Hi, i have a simple table "MyTable" with single colum "id" which type is uniqueidetifier and rowguid is set to true. I insert new values like this INSERT INTO MyTable DEFAULT VALUES how to get inserted by server guid ? Best Regards, Iordan ...

Can I add a not null column without DEFAULT value

Hi, Can I add a column which is I specify as NOT NULL,I don't want to specify the DEFAULT value but MS-SQL 2005 says: "ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditi...

Why does ADO BeginTrans() do something different than "BEGIN TRANSACTION"?

I've run into some surprising behavior while using ADO with C++ and Microsoft SQL Server 2008 (express). Essentially, I had code that did this: //pseudocode pseudocode pseudocode adoConnection->Execute("BEGIN TRANSACTION;"); Insert( adoRecordsetPtr ); SelectAll( adoRecordsetPtr ); adoConnection->Execute("COMMIT TRANSACTION;"); But...

work with an already open database connection

This is a little wierd, but I want to check if connection to my database is already open or not? How do I check that? and if open I want to be able to work with it straightaway without going through all the statements: sqlconnection conn = new sqlconnection("string ..."); Can this be done? I know the connection string and the connecti...

How to generate a sql script to update existing database and retain its data

I have a SQL Server 2008 database (call it productionDB) that contains data and will be used in a production environment. I have another SQL Server 2008 database (call it stagingDB) that is used in a staging environment. The application that I am working on continues to evolve such that I am often making changes to the database schema ...

Communicating with Informix from SQL Server

Right... I've got a program I'm doing some maintenance on. Urgh. Even describing it makes me shudder... Right, okay. Every night, a database running on what we think is SQL Server 2000 hooks up to an Informix database and copies it over into SQL Server. The Informix/SQL data is accessed by the program I'm maintaining, which then store...

How to query for a value from the last month of each quarter?

SELECT YEAR(aum.AUM_Timeperiod) as Year, DATEPART(q, aum.AUM_TimePeriod) AS Quarter, SUM(cast(aum.AUM_AssetValue AS money)) as total_AssetValue FROM AssetUnderManagement as aum, LineOfBusiness where aum.LOB_ID = LineOfBusiness.LOB_ID and LineOfBusiness.LOB_Name = 'Asset Management' GROUP BY YEAR...

SQL Server combining data question

Hey all I am trying to combine my data into one sum. This is my output right now: Amount --------- $258.0 $400.0 $1011.0 $628.0 $628.0 $340.0 $340.0 $1764.0 of course the total would be $5369. This is the type of output I need Description | Quantity | Price | Amount -------------------------------------------- Fees 8 ...

SQL Server 2008: how do I grant privileges to a username?

I need to be able to establish an ODBC connection through SQL Server authentication. In SSMS how do I grant permission for a user to be able to have ALL RIGHTS on a specific database? is there a way to do this graphically with SSMS? ...

Why do i get "An attempt to attach an auto-named database for file ..." exception

Hi, I create a database in SQL Server 2008 Express (Advanced). Then i have a connection string in my C# using 'SharpDevelop' IDE, Version : 3.2.0.5777, .NET Version : 2.0.50727.3615 <add name="ConnectionString" connectionString='Data Source=.\SQLEXPRESS;AttachDbFilename="D:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS...

SQL Server 2008: should I be using Windows auth or SQL Server auth?

I have an MS-Access 2007 front end. I will have multiple users on it. They are all going to be on the network company domain. Should I be using Windows authentication or SQL Server authentication to be connecting to SQL Server 2008 via ODBC? ...