Archive

Archive for the ‘SQL Server Questions’ Category

MERGE data from source Table to target Table across Databases with a SP – MSDN TSQL forum

December 18, 2013 Leave a comment

–> Question:

How do I merge data from Table1 on Server-1 to final table on Server-2 with a Stored Procedure to execute every so many hours?
 

–> My Answer:

A basic approach would be to create a Linked Server in Server2 that will point to Server1.

On Server2 you can create a Stored Procedure, which will read the table via Linked Server from Server1. You can use MERGE statement to merge (or INSET/UPDATE/DELETE) records in final table on Server2 form table1 in Server1.

If you are dealing with millions of records then you can go with Incrememtal/Delta load approach, here you will have to store the timestamp of previous load in some meta-data table.

To keep executing every 2 Hrs, you will need to create a SQL Agent Job, and schedule it to run for ever 2 Hrs.

You can also use SSIS or other ETL tool as a better approach to setup this load.
 

Ref Link.

Categories: SQL Server Questions Tags: , , ,

Convert XML to Columns – MSDN TSQL forum

July 13, 2013 Leave a comment

–> Question:

Below is the XML that i need to covert into columns please help, XML is coming from column name called “DESC” table name is “rawXML”

Columns: USER id, US_USERID, US_PASSWORD, US_SHORT, FIRST, LAST, US_LAST_PASSWORD_UPDATE

<USER id="05100">

 <US_USERID>YU</US_USERID>

  <US_PASSWORD>4026531934</US_PASSWORD>

  <US_SHORT>yu</US_SHORT>

  <US_XPN>

    <FIRST>Yehuda</FIRST>

    <LAST>Unger</LAST>

  </US_XPN>

  <US_LAST_PASSWORD_UPDATE>2006-01-19T16:10</US_LAST_PASSWORD_UPDATE>
</USER>

 

–>My Answer:

Check the query below:

declare @xml xml
set @xml = '<USER id="05100">

 <US_USERID>YU</US_USERID>

  <US_PASSWORD>4026531934</US_PASSWORD>

  <US_SHORT>yu</US_SHORT>

  <US_XPN>

    <FIRST>Yehuda</FIRST>

    <LAST>Unger</LAST>

  </US_XPN>

  <US_LAST_PASSWORD_UPDATE>2006-01-19T16:10</US_LAST_PASSWORD_UPDATE>
</USER>'

select 
	t.c.value('../@id[1]', 'varchar(10)') as [USER],
	t.c.value('../US_USERID[1]', 'varchar(10)') as [US_USERID],
	t.c.value('../US_PASSWORD[1]', 'varchar(10)') as [US_PASSWORD],
	t.c.value('../US_SHORT[1]', 'varchar(10)') as [US_SHORT],
	t.c.value('./FIRST[1]', 'varchar(10)') as [FIRST],
	t.c.value('./LAST[1]', 'varchar(10)') as [LAST],
	t.c.value('../US_LAST_PASSWORD_UPDATE[1]', 'varchar(10)') as [US_LAST_PASSWORD_UPDATE]
from @xml.nodes('//USER/US_XPN') as t(c)

 

Ref Link.

Categories: SQL Server Questions, XML Tags: ,

Reference a table in a Stored Procedure (SP), and call the SP from the trigger? – MSDN TSQL forum

June 6, 2013 Leave a comment

–> Question:

I have three tables “a”, “b”, “c “.

I want to make one trigger on table “a” and one on table “b”, the trigger inturn calls a stored proc(SP) to perform insertion deletion task on table “c”.

When some body inserts or updates table “a” , the trigger on table “a” inserts or updates table “c” by calling the stored proc(SP), and there after updates table “b” from trigger it self (this stmt is below the call of (SP).

Q1) i am using table “a” in (SP), i wanted to know that, is it correct to use the table “a” in stored proc(SP) to get the new inserted record or updated record to insert or update table “c” as the control is till in tigger of table “a”?

Well when table “b” gets updated from the trigger of table “a” , then the trigger of table “b” gets activated and it inserts or update the table “c” by calling (SP).

Q2) at this place when trigger of table”b” calls the same (SP) to insert table “c”, it uses table “a” and “b” to insert update tabel “c”, at this point also the control is still in trigger, so i whould like to know is it correct (technically).

I know that by changing few things in tables i can make things easy

I read this in msdn:

“AFTER specifies that the trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger fires.”

But I would to know that, what exactally happens in above case , how and when event are fired and gets affected. and finally is it correct?
 

–> My Answer:

Why are you creating a separate Stored Proc to fire from the trigger?

You can have the SP logic in the Trigger itself. If you use an SP, then you would not get the INSERTED & DELETED Trigger/Magic tables that you can get in the Trigger.

Also you can avoid Triggers and create single SP to add records in Table a & b and update the table c in a single transaction.
 

Ref Link.

Categories: SQL Server Questions Tags: , , ,

Linked Server error: Named Pipes Provider: Could not open a connection to SQL Server [53] – MSDN TSQL forum

May 31, 2013 Leave a comment

–> Question:

I am trying to create a linked server of a remote server in my local system.

and getting following error:

Linked Server error

Named Pipes Provider: Could not open a connection to SQL Server [53]
OLE DB provider “SQLNCLI10” for linked server “MY_LINKED_SERVER” returned message “Login timeout expired”…
 

–> My Answer:

What SQL Server version you are working on?

Do you have proper access rights and permission to connect to the Remote server?

Check, if TCP/IP and Named Pipes are enabled in Remote Server, in Config Manager?

And, are you executing Linked Server from the same machine, or any remote client?

The error number is 53, which means Network Path is not Found, try to ping the server, check Server Name in your Linked Server DDL script, Also check for Windows Firewall settings.
 

Ref Link.

Categories: SQL Errors, SQL Server Questions Tags: ,

Database TRIGGER on delete – MSDN TSQL forum

March 8, 2013 Leave a comment

–> Question:

Today we saw that some rows got deleted in our Dataware House. Normally rows are never deleted, they are just marked as deleted because we want to keep the history. Now there are two important questions :

How can we detect who or what deleted this rows ?

How can we prevent that they are being deleted ?

We have around 450 tables and I think around 250 ETL packages.
 

–> My Answer:

You can create a INSTEAD OF DELETE (DML) TRIGGER on that table, and inside this trigger apply UPDATE statement to soft-delete the records.

With this every DELETE statement fired on that particular table will be an UPDATE.

To Track who deleted those records you can create a DDL TRIGGER that will identify and log the users who issued DELETE statement.
 

Ref Link.

Categories: SQL Server Questions Tags: , ,