sql-server-2005

why this behaviour with int in SQL Server 2005

I have a requirement in which i need to see whether that key already exists. If it already exists update the data based on the key else just insert new data. The approach which i took is DECLARE @Tracking_Id INT SELECT @Tracking_Id = Tracking_Id FROM DOCUMENT_TRACKING WHERE Secondary_Document_Id = @Secondary_Docum...

Things to remember while doing code review of Stored Procedures in SQL Server

What do you guys look for when you are doing a code review from performance prospective. When i do it I look for the following things 1) Use NOLOCK when doing select so that table is not locked. 2) Prefix dbo. before a table name. 3) Use table variables instead of temp tables when dealing with less data 4) Avoid cursors, use while lo...

SQL Syntax: Casting a datatype that is stored as data

Hi, I have a table that looks like this: id datatype name value 0 nvarchar(255) myName 'Paul' 1 int age '30' 2 float(53) Custom1 0.5 3 float(53) Custom2 1.3 4 float(53) Custom3 2.7 I am wondering if it is possible to do something like the following where I cast ...

Simulation of Generate Scripts in sql server 2005+(without using Management Studio)

Is there any inbuilt store proc / any database program by which I can generate all the database object script(like stored procs, triggers, functions, tables etc.) from a particular database. I am using SQL SERVER 2005 + 2008 Thanks in advance ...

mcts 70-431 exam

i wish to give MCTS 70-431 exam which is SQL Server 2005 Implementation & Maintenance. I have not so many days left with me hence i am giving this exam as the exam would be sponsered by my company. If i had much time i would rather go for mcts in .net 3.5.(Web based) My question. if i give mcts 70-431 and i pass. Is it possible for me t...

Copying database objects using SMO

Hi, I am trying to copy table, indexes, keys, views and functions in SQL 2005. Any idea how I could do so using SqlServer.Management.Smo? The user will specify the object names to be copied and the new names to be given to the objects. I need to do it in the most efficient way Thanks :) ...

Are there any built in SQL Server 2005 functions to serialize / deserialize string parameters to a table?

The big question is are there any "built in" SQL Server 2005 functions to serialize / deserialize string parameters to a table variables? The rest explains my need further, but may not be necessary. I have a SQL SP I would like to refactor. It currently accepts five customers ids and five sets of order ids relevant to those customers....

Storing XSLT in SQL Server 2005 with xml type?

I have a lot of XSL files in my ASP.NET web app. A lot. I generate a bunch of AJAX HTML responses using this kind of generic transform method: public void Transform(XmlDocument xml, string xslPath) { ... XslTransform myXslTrans = new XslTransform(); myXslTrans.Load(xslPath); myXslTrans.Transform(xml,null, HttpContext.Cu...

Is it possible to insert data in MySQL from a SQL Server trigger?

A fun question for a Friday afternoon, no? As the question states, is it possible to write a SQL Server trigger that inserts data into a MySQL table? EDIT: Yes, I know this is basically a dumb idea and that the application should be doing it and so on. But this is a legacy Classic ASP application which we're transitioning to MySQL; a...

Get ID of the newly inserted record, using SqlDataAdapter

BGREX.BGREXDataTable _bRexDataTable = new BGREX.BGREXDataTable(); BGREX.BGREXRow bgRexRow = _bRexDataTable.NewBGREXRow(); bgRexRow.BGRes_TITLE = "abc"; bgRexRow.BGRes_VERSION = 123; _bRexDataTable.AddBGREXRow(bgRexRow); int rewEffected = Adapter.Update(_bRexDataTable); Have beed using above to insert record in Database, workes perfec...

how to do attachments for each sql record in c#

I am developing a desktop application in c# & sql 2005. With candidate data entry form I want to provide option to attach required documents(in pdf format) wid data. Kindly let me know the best method. Thank you in advance. ...

Computed bit column that returns whether another column is null

I try to have this computed column: CREATE TABLE dbo.Item ( ItemId int NOT NULL IDENTITY (1, 1), SpecialItemId int NULL, --I tried this IsSpecialItem AS ISNULL(SpecialItemId, 0) > 0, --I tried this IsSpecialItem AS SpecialItemId IS NOT NULL --Both don't work ) ON [PRIMARY] ...

sql server 2005 database mail error (The operation has timed out)

I have configured Database Mail for sending newsletter emails from sql database. I have a stored procedure that selects the emails of the subscribers and send them the mails like this: EXEC msdb.dbo.sp_send_dbmail @profile_name = 'ProfileName', @recipients = @email, @subject = @thesubject, @body = @themessage, @body_format = 'HTML' ; ...

Finding Day in T-SQL

Hi, Please let me know how can I find if it is Sunday on given Date? EDIT: From answers another thing comes to my mind: How to find what is first day of week? ...

SQL Server 2005 filtering and paging with ROW_NUMBER()

I have a table called with thousands of records and would like to implement paging logic. After doing some research, I came across the ROW_NUMBER() function introduced in SQL Server 2005. My problem is, it seems to not meet my exact need and I'm wondering how to tweak my stored procedure to make it work as expected: ALTER PROCEDURE dbo....

database connectivity?

How to establish SQL 2005 database connectivity with Java application. Actually I don't know how to set path for JDBC. Any help would be great. ...

Scripting through SMO

Hi, Is it possible to run a script using Microsoft.SqlServer.Management.Smo? I am able to generate script using the Microsoft.SqlServer.Management.Smo.Scripter class. I would like to edit it and run it to create copy of database objects. Thanks. ...

SQL Server distributed databases

how to link two different database in same SQL Server instance and send queries between them ...

What would be a reliable way to get fractional value from a number?

I have a number of type Decimal(8, 2) and have been using Substring to get fractional value. E.g.) declare @val decimal(8, 2), @strVal varchar(10) set @val = 15.80 set @strVal = cast(@val as varchar) select @val, substring(@strVal, charindex('.', @strVal), len(@strVal)) Is there a better way to simply get fractional value, .80 fro...

Blob in Java/Hibernate/sql-server 2005

Hi, I'm trying to insert an HTML blob into our sql-server2005 database. i've been using the data-type [text] for the field the blob will eventually live in. i've also put a '@Lob' annotation on the field in the domain model. The problem comes in when the HTML blob I'm attempting to store is larger than 65536 characters. Its seems that...