sql-server

Skip rows in query based on data

I have data as follow DECLARE @tmp TABLE(cid int, colspan int, rowspan int, corder int) INSERT INTO @tmp SELECT 1,2,null,1 UNION SELECT 2,null,null,2 UNION SELECT 3,null,null,3 UNION SELECT 4,3,null,4 UNION SELECT 5,null,null,5 UNION SELECT 6,null,null,6 UNION SELECT 7,null,null,7 I want to query to return cid colspan rowspan cor...

the different between join condition and where condition

Somebody tell me what's the difference between two queries: Version A select p.LastName, o.OrderNo from Persons p, Orders o where p.P_Id = o.P_Id ...and... Version B select p.LastName, o.OrderNo from Persons p join Orders o on p.P_Id = o.P_Id ...

SQL Server 2000 Script to list all Meta Data Services DTS packages

Hi Team, I am doing an SSIS package that would go out to all our DB servers to gather information about them, and what is on them. And in this one case there is a SQL 2000 box with a bunch of DTS packages on there. Now I can easily list the Local Packages using "exec sp_enum_dtspackages", but this doesn't list the Meta Data Services P...

SQL Server IF EXISTS

i have to insert data into table, before that have to check wherther same id is already exist or not. I checked like "IF EXISTS ( SELECT 'X' FROM Table1 where id =@id)". Will this improve performance instread of column name ? ...

sql query to get redundant record

I want to get redundant records from the database. Is my query correct for this? select (fields) from DB group by name, city having count(*) > 1 If wrong please let me know how can I correct this. Also if I want to delete duplicate record will it work? delete from tbl_name where row_id in (select row_id from tbl_name group by...

help me SELECT-ing from chained stored procedures

I added my local server pblack as linked server in SQL Server 2008 R2 ---1) EXEC master.dbo.sp_addlinkedserver @server = N'pblack', --'pblack' is my localhost @srvproduct=N'SQL Server' and executed successfully 2) and 3): --2) sp_MSforeachtable @command1="EXEC sp_spaceused '?'" --3) SELECT * INTO #te...

How to avoid joining with the same table many times in a query ?

Hi Friends My Problem is in a table i have 4 reference of the user ID Owner ID Assigner ID Taker ID Evaluator ID Final Evaluator ID & all these column values are FK to User detail table where i am having there name. If i want to have there name along with their ID while retrivieng the complete row I am joining 4 times with the us...

CTE and Hierarchical XML Result

Hei, I've a simple CTE that result with hierarchical list of items (Parent/Child relation). How can I convert it to XML with the "FOR XML" sql syntax while preserving the hierarchical structure? Thanks, Espen ...

Storing a column in a variable

I wanted a formatted text to be converted into unformatted text in the UI . for that I did the following String strInput; String strOutput; strInput = txtEditorAnswer.Text; strOutput = Regex.Replace(strInput, "<[^>]*>", String.Empty).Trim(); txtEditorAnswer.Text = strOutput; txtEditorAnswe...

Query Runs fast, but runs slow in Stored proc

I am doing some tests using the SQL 2005 profiler. I have a stored procedure which simply runs one SQL query. When I run the stored procedure, it takes a long time and performs 800,000 disk reads. When I run the same query separate to the stored procedure, it does 14,000 disk reads. I found that if I run the same query with OPTION(re...

sp_xml_preparedocument error handling, what does it mean: SELECT @err = @@error + coalesce(@err, 4711)

Here (and not only there) people are using the following code to handle an error from sp_xml_preparedocument EXEC @err = sp_xml_preparedocument @hdoc OUTPUT, @xmlDoc SELECT @err = @@error + coalesce(@err, 4711) Are there any reasons for this? And what does 4711 mean in this context? ...

Bulk add quotes to SQL values

Hey all Not sure if this is an appropriate "programming" issue but its relevant. Is there a way via SQL or excel even to add quotes to a big number of values, for example i want to add values a,b,c,d,e,f,g,j etc etc to a table is there a way i can automatically add quotes to them? as in 'a','b' etc... i have this issue especially in...

Accessing varchar field containing extended ASCII characters using ADO

Hi folks, I'm using ADO to connect to an SQL 6.5 Server and extract data from a column storing text data (field type returns as adLongVarChar). The column data was updated from an old legacy DOS system and contains a few extended ASCII characters like 0xFB (square-root glyph in Code Page 437). The problem is when I read the Field's V...

NHibernate code genaration tool for asp.net (c#)

Hello Guys, Any idea of any tool I can use to generate asp.net (c#) scripts for NHibernate. I was told some come with unitTest Scripts as well. I just need a good template to start a project using NHibernate with C# web application. This a new territory for me Cheers ...

Is there a possible race condition in this UPDATE statement?

I'm writing a synchronizer software which will take all changes in one DB and synchronize them to another DB. To this end I've added in my table T two columns: alter table T add LastUpdate rowversion, LastSync binary(8) not null default 0 Now I can easily select all rows that have changed since the last synchronization: select * from...

Connect to SQL Express with SSMS

Hello there, My machine Win7 has SQL Server 2005 and SQL Server 2008 installed. Also, Sql Server Management Studio is installed with SQL Server 2008. Now, I want to connect to SQLEXPRESS instance running currently on my machine. How do I know if i will need Management Studio 2005 or 2008 to connect to SQLEXPRESS? Just to add that I h...

How to create SQL Server stored procedure returning an array?

Hello guys. Sorry for this maybe foolish question but i'm newbie to SQL Server. Here structure of my table of divisions at organization: id int(16) -- simply unique id sub_id int(16) -- this id of parent division in this table (if zero, than it don't have parent) name VARCHAR(200) -- name of division I need to create a simply procedu...

Group by problem

I need to group by ,even if is there a difference in values for a column by converting to a text value and needs that text value to be included in select list How is this possibe? SELECT col1,col2 FROM ( SELECT col1,col2 FROM table1 UNION ALL SELECT col1,col2 FROM table2 UNION ALL SE...

SQL Server Query is faster in SSMS when including execution plan.

In SQL Server 2005 management studio I am testing a query which uses some table variables, one of which has a clustered unique constraint on it. I am noticing that the total execution time actually goes down by a good amount when I include the actual execution plan to analyze it. What is the reason for this, and should I only test f...

How to get SQL Server schema changes especially "column name" changes

I am using trigger to trace DDL changes but when I change column name from the SQL Server Management Studio the trigger is not working? create TRIGGER trgLogDDLEvent ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS FOR AS DECLARE @data XML SET @data = EVENTDATA() IF @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') <> 'CREATE_ST...