Showing posts with label SQL Server 2005. Show all posts
Showing posts with label SQL Server 2005. Show all posts

April 6, 2010

SQL SERVER SSIS: Get File Name with Flat File Source, Data Flow Component

As I explained earlier about For Each Loop Container, which process each file from selected folder.

There is a requirement to save File name along with record, so later on we can identify which record comes from which file.

Let me explain it how to achieve with FileNameColumnName property of Flat File Connection to get it easily.

1. Setup Flat File Connection with CSV file, as mentioned Basic Example of Data Flow Task.
2. Right click on Flat File Source, and click on Show Advanced Editor:
Derived FileName Show Advanced Editor
3. Click on "Component Properties" and go to FileNameColumnName, Custom properties:
  Derived FileName Component Properties



4. Setup FileNameColumnName value with desired column name. Let's say "File Name". Congratulations, This column is added to output list with actual filename of that connection.

image
Let me know your comments/questions.
 

March 7, 2010

SQL SERVER: Execute Stored Procedure when SQL SERVER is started

We have a requirements to execute Stored Procedure when SQL SERVER is started/restarted and we need to start some processes. I found that SQL SERVER provides a way to call Stored Procedure when SQL services are restarted.

SQL SERVER provides this SP: "sp_procoption", which is auto executed every time when SQL SERVER service has been started. I found this SP and it helps me to figure it out the solution for the request as following way.

Let me show you how to use it. Syntax to use SP:

EXEC SP_PROCOPTION     
@ProcName = 'SPNAME',
@OptionName = 'startup',
@OptionValue = 'true/false OR on/off'



  • @ProcName, should be Stored procedure name which should be executed when SQL SERVER is started. This stored procedure must be in "master" database.
  • @OptionName, should be "startup" always.
  • @OptionValue, this should be set up to execute this given sp or not. If it is "true/on", given sp will be execute every time when SQL SERVER is started. If it is "false/off", it will not.


That's it, I hope this is very clear to use this feature.


Reference : Tejas Shah (http://www.SQLYoga.com)


January 10, 2010

SQL SERVER: SSIS - Derived Column Data Flow Transformation

As I explained earlier about Foreach Loop Container. One of regular reader of blog send me an email about one issue.

Let me share that problem with all readers.

With this example, Foreach Loop Container, What to do if we want to save file name along with each row, so we can come to know that which row is from which file ?

This is very practical problem that we need to fix.

To solve this, I come up with following solution.

1. I used "Derived Column", one of Data Flow Transformations in Data Flow Operations.


 SSIS For Each Loop Container II 

2. Configure Derived Column:

SSIS Derived Column Data Flow Transformation Configuration 
As we have variable, FileName, as defined in, SQL SERVER: SSIS - Foreach Loop Container. Here I used that variable as a new column. By dragging  that User variable to Expression.

By default it assign UNICODE STRING DataType to this new column. We need to change it by:

A. Right click on "Derived Column", Go to Show Advanced Editor
B. Set DataType to String as:

SSIS For Each Loop Container II Advanced Editor

3. That's it. Now just add it to Destination Column Mapping with your Database column.

Let me know your suggestions.


 

December 7, 2009

SQL SERVER: SSIS - Transfer Jobs Task

The Transfer Jobs task can be configured to transfer all jobs, or only specified jobs. You can also indicate whether the transferred jobs are enabled at the destination.

The jobs to be transferred may already exist on the destination. The Transfer Jobs task can be configured to handle existing jobs in the following ways:

  • Overwrite existing jobs.
  • Fail the task when duplicate jobs exist.
  • Skip duplicate jobs.
Let's take an example to easily understand how to use Transfer Jobs Task with SSIS.

1. Select and Drag, Transfer Jobs Task, from Container Flow Items to designer surface.

SSIS Transfer Jobs Task

2. To configure a task, Right click on  Transfer Jobs Task, which we dragged to Design surface. Click on "Edit.", you will get page as:

SSIS Transfer jobs Task Editor

3. SSIS Transfer Jobs Task - General : Here we need to assign unique name to this task and also we can specify brief description, so we will get idea why we need to design this task.

4. SSIS Transfer Jobs Task - Jobs : Jobs page of the Transfer Jobs Task Editor dialog box is required to specify properties for copying one or more SQL Server Agent jobs from one instance of SQL Server to another. 

SSIS Transfer jobs Task Editor Jobs

Let's take a view how each properties are used.

SourceConnection: Select a SMO connection manager in the list, or click <New connection...> to create a new connection to the source server

DestinationConnection: Select a SMO connection manager in the list, or click <New connection...> to create a new connection to the destination server.

TransferAllJobs: Select whether the task should copy all or only the specified SQL Server Agent jobs from the source to the destination server.  This contains two options:

TRUE: Which copy all jobs
FALSE: Which copy only selected jobs.

JobsList: Click the browse button (.) to select the jobs to copy. At least one job must be selected.

SSIS Transfer jobs Task Editor JobList

IfObjectExists: Select how the task should handle jobs of the same name that already exist on the destination server.
This property has the options listed in the following table:

SSIS Transfer jobs Task Editor ObjectExist

FailTask: If job of the same name already exists on the Destination Server then task will fail.

Overwrite: If job of the same name already exists on the Destination Server then task will overwrite the job.

Skip: If job of the same name already exists on the Destination Server then task will skip that job.

EnableJobsAtDestination: Select whether the jobs copied to the destination server should be enabled. This contains two options:

TRUE: Enable jobs on destination server.

FALSE: Disable jobs on destination server.

5. SSIS Transfer Jobs Task - Expressions: Click the ellipsis to open the Property Expressions Editor dialog box.

SSIS Transfer jobs Task Editor Expression

Property expressions update the values of properties when the package is run. The expressions are evaluated and their results are used instead of the values to which you set the properties when you configured the package and package objects. The expressions can include variables and the functions and operators that the expression language provides.

Now let's run task, by right click on task and click on Execute Task, as shown in following figure. You can either Execute Package by right click on Package name, from Solution Explorer.

SSIS Transfer jobs Task Editor Execute

Once you run this then all/selected jobs will be transferred to destination server as per given criteria.

December 3, 2009

SQL SERVER: How to Read Excel file by TSQL

Many times developers asked that, they want to import data from Excel file.

We can do this by many ways with SQL SERVER.

1. We can use SSIS package
2. Import/Export Wizard
3. T-SQL

Today, I am going to explain, How to import data from Excel file by TSQL.

To import Excel file by TSQL, we need to do following:

1. Put Excel file on server, means we need to put files on server, if we are accessing it from local.

2. Write following TSQL, to read data from excel file

SELECT Name, Email, Phone 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\SQLYoga.xls', [SQL$])

ExcelFile

NOTE: Here, Excel file is on "C:\" named "SQLYoga.xls", and I am reading sheet "SQL" from this excel file

If you want to insert excel data into table,

INSERT INTO [Info]
SELECT Name, Email, Phone 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\SQLYoga.xls', [SQL$])

That's it.

December 2, 2009

SQL SERVER: How to remove cursor

Many times developer ask me that How can they remove Cursor?

They need to increase Query Performance, that's why they need to remove SQL SERVER Cursor and find the alternate way to accomplish the same.
Please find this code to remove cursor with Table variable:
--declare table to keep records to be processed
DECLARE @Table AS TABLE(AutoID INT IDENTITY, Column1 VARCHAR(100), Column2 VARCHAR(100))
--populate table variable with data that we want to process
INSERT INTO @Table(Column1, Column2)
SELECT    Column1, Column2
FROM    <Table>
WHERE    <Conditions>
--declare variables to process each record
DECLARE @inc INT, @cnt INT
--Assign increment counter
SELECT @inc = 1
--Get Number of records to be processed
SELECT @cnt = COUNT(*)
FROM @Table
WHILE @inc <= @cnt BEGIN
    --As we have AutoID declared as IDENTITY, it always get only one record.
    --Get values in Variable and process it as you want.
    SELECT    @Column1 = Column1,
            @Column2 = Column2
    FROM    @Table
    WHERE    AutoID = @inc        
    --do your calculation here
    ....
    ....
         
     --Select next record
     SET @inc = @inc = 1 
END


By this way, we can remove CURSOR by Table variable. 
It is quite easy to implement.
One more benefit is: It will process one record at a time, so it locks only that record at a time.
Let me know if you have any questions.
 

November 28, 2009

SQL SERVER SSIS: How to assign Connection from variable

Last Article, SSIS - Foreach Loop Container, We need to assign dynamic connection to file connection, so SSIS For each loop Task can take each file from folder.

Lets configure File connection from variable for SSIS - Foreach Loop Container.

What we need to do is, we need to process each file from folder, so we need to assign value from variable to File connection, so SSIS Task will read that file and process that file.

To assign FileConnection dynamicaly we need to do following.

1. Right click on File Connection, click Properties.

2. Set DelayValidation = "False", as we need to assign connection dynamically.

3. Click on "Expression", and enter variable  name, which we used in SSIS - Foreach Loop Container.

SSIS Foreach Loop Container Connection

That's it.

It will assign connection from variable and process that file.

Let me know if you have any question for the same.

November 2, 2009

SQL SERVER SSIS - For Loop Container

Today, I am going to explain SQL SERVER SSIS, For Loop Container.

The For Loop container defines a repeating control flow in a package. The loop implementation is the same concept of the For looping structure in programming languages. In each repeat of the loop, the For Loop container evaluates an expression and repeats its workflow until the expression evaluates to False.

The For Loop container uses the following elements to define the loop:

  • An optional initialization expression that assigns values to the loop counters.
  • An evaluation expression that contains the expression used to test whether the loop should stop or continue.
  • An optional iteration expression that increments or decrements the loop counter.

Let's take an example to easily understand how to use For Loop Container with SSIS. Here I take example to iterate ActiveX Task.

1. Select and Drag, For Loop Container, from Container Flow Items to designer surface and add ActiveX Script Task to run inside the loop.

SSIS For Loop Container

2. To configure this container, right click on this and click on 'edit'.

SSIS For Loop Container editor

 

First we need to create variables to run this package based on variables. We can create variables by: View -> Other Windows -> Variables. Please find variable screen as below:

For Loop Container 2

Here, I specified both variables. Count and Increment, that I am going to use for this example. I specified value Count = 20. So loop will be executed 20 times.

 

Let's take a view how each properties are used:

For Loop Properties:

1. InitExpression: Type an Initialization Expression in the given textbox. Initialization ensures that we are starting by setting out increment counter to 1. Here I specified variable to 1.

2.EvalExpression: Type an Evaluation Expression in the  given textbox. For each iteration the evaluation expression checks to see if we have reached our maximum iteration count as set above when we defined @Counter. Here I specified that @Increment <= @Count, code inside the for loop will execute @Count times.

3. AssignExpression: Type an Assignment Expression in the given textbox. This is used to increment the counter by one for each iteration of the loop, otherwise the loop would never finish. Here I specified to increment variable by 1.

That's it. We have configured SSIS For Loop container.

Now when we execute this package, it will execute ActiveX Task, 20 times (as specified in variable count).

Let me know if you have any questions.

October 24, 2009

SQL SERVER SSIS: Basic Example Of Data Flow Task

Today I am going to give Basic example of Data Flow Task in SSIS, import CSV file to SQL SERVER Database

Many times user asked to import their data from their files. They might have data in Microsoft Access, Microsoft Excel, CSV File, Text Tile etc..

Microsoft SQL SERVER, SSIS, has features to import data from any heterogonous format to SQL and also capabilities to export it to any format. That is the power of SSIS.

1. Drag Control: Drag Data Flow Task from Toolbox, to Design interface


Data Flow Task 1
2. Configure Data Flow Task: Once you have Data Flow Task on Design surface, Double click on it, or Right click on task and click on "Edit", it will redirect you to Data Flow Tab, as shown in Figure 2:

Data Flow Task 2 
As shown in above figure, SQL SERVER SSIS give features to import/export data from/to many formats. You can find the different formats that is supported by SSIS to import data on LEFT PANE.

3. Configure Source Connection: Here, I have selected "Flat File Source", as we need to import data from CSV File. Drag "Flat File Source" to design surface and right click on edit and select "Edit", you will get screen like:

 Data Flow Task 3
As shown in figure, Click on "New", It will open a form which allows us to select file which we want to import to our Database.

First, Enter connection manager name and description.
Then, select a file which you want to import to database.
Then, Select Locale and Code page as per your requirement, For this demo, I don't need to change anything.
Then, we need to select Format of the file. SSIS provide us three formats: Delimited, Fixed Width, Ragged Right. We need to import CSV file (Comma Separated file), so I have selected "Delimited".
Then, We need to specify Text Qualifier, Select based upon your requirements.
Then, we need to specify delimiter for Header row, as we have CSV  file, I have selected CR LF, means new row for header.
Then, SSIS also give us features to skip rows. By Header Rows to skip, it allows us to skip n number of rows.
At Last, If there is Header in column names, we just need to select this checkbox, "Column names in the data row". So SSIS understands to set the first row as Header row.

That's it for configure file, Now we need to specify delimiter for column, which comes on next section called "Columns"

 Data Flow Task 4


As shown in figure, Now we need to specify delimiter for Rows and Columns.
As we are going to import CSV file (comma separated file), Row Delimiter is "{CR}{LF}" and column delimiter is ",".

NOTE:
Also notice that, it display Column Names in Header, as we have checked, "Column Names in the First data row".

This is the data that we are going to import to SQL Database.

You can use Advanced and Preview tab, to configure each column and preview, respectively.

Now once, configuration is done, we need to select OLE DB Destination, as we need to import it to SQL SERVER database.

4. Configure Destination Connection: Here, I have selected "OLE DB Destination", as we need to import data from CSV File to SQL SERVER Database. Drag "OLE DB Destination" to design surface.

Data Flow Task 5 
Now, we need to design data flow. Here we need to import data from Flat File connection, I have dragged "Success" (Green Arrow), link to OLE Db Destination for column Mapping.

Now, we need to configure OLE DB destination, to insert data to SQL Database. Right click on it and click on "EDIT", it will open screen like this:

 Data Flow Task 6
Data Flow Task 7



I have entered SQL server Login information to connect to SQL SERVER where I need to import data from CSV file.

Once, SQL SERVER is configured, we need to select Table Name to which we need to insert data from CSV file. See the following screen, I have selected "Employee" Table: Please find script to create "Employee Table"

CREATE TABLE [dbo].[Employee](
    [EmpID] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeName] [varchar](max) NULL,
    [EmployeeCode] [varchar](max) NULL,
    [EmployeeDesignation] [varchar](max) NULL,
    [CreatedDate] [datetime] DEFAULT GETDATE()
)

Data Flow Task 8

Now we have selected SQL SERVER table to which we need to import data, Now we need to map columns names, so SSIS will insert data accordingly.

Data Flow Task 9

By default, SSIS provides mapping for columns which have same name, In this case Column Mapping is ok, so we don't need to do anything. If you want to change mapping, you can do it by just changing links between Input Columns and Destination Columns.

NOTE: Here Destination table has two more columns, EmpID and CreatedDate, which is not available in source file, CSV file. We can just ignore it, as EmpID is Identity column while CreatedDate is GETDATE(). We have such columns in SQL database to keep track when this record is added.

That's it. We have configured Data Flow Task.

Now just right click on package and "Execute Package". You will find that Employee table will be populated with information from CSV file.

Let me know if you have any questions in designing SSIS Package.

October 22, 2009

SQL SERVER: What is SSIS?

SSIS (SQL Server Integration Services) is an upgrade of DTS (Data Transformation Services), which is a feature of the previous version of SQL Server

SSIS (SQL Server Integration Services) is introduced in SQL SERVER 2005. It is totally new transformation of data. Previously we have DTS package in SQL SERVER 2000. Now they introduced SSIS with many features.

SSIS is an extensive data integration platform which is used to transfer and merge the information from various sources and load to multiple systems. It contains wide range of capabilities.

Business Intelligence is used for decision making, future trends based on collected data. I can find very good article written by Microsoft SQL SERVER MVP, Pinalkumar Dave. He write about BI on his one of the best article: http://blog.sqlauthority.com/2009/10/21/sql-server-introduction-to-business-intelligence-important-terms-definitions/.

SSIS packages can be created in BIDS (Business Intelligence Development Studio).

I am going to explain all features of SSIS for easy and better understanding by following articles.

Reference: Tejas Shah (http://www.SQLYoga.com

August 12, 2009

SQL SERVER: Check if Node exists in XML or not






Today, I have one requirement to check dynamically if a node exists in my xml or NOT.

I have a stored procedure that receives XML and I need to check if the message information xml contains one Node or NOT. If that node exists then I need to execute that Stored Procedure by different logic and if not it should run with different logic.

I figure it out by using EXISTS.

This is my XML, that I got as parameter.

DECLARE @ExportData  XML
SELECT @ExportData =
'<Data Number="A123">
  <BulkData>
    <EachData Parts="Test1" />
    <EachData Parts="Test2" />
    <EachData Parts="Test3" />
  </BulkData>
</Data>'
Now I need to check if "BulkData" node exists in XML, then I need to write different logic to get the result.
So, I used this

SELECT @ExportData.exist('(//BulkData)')
This will return "1" if node is exists else return "0".

That's it. I can write based on the return result by this statement.

Let me know if it helps you.

Reference : Tejas Shah(http://www.SQLYoga.com)

July 31, 2009

SQL SERVER: Use CONTEXT_INFO

Recently I have following situation:
I don't need to execute trigger's code on some condition, like if it is called from particular stored procedure. If data is being updated from any other places, like application, or any other stored procedures, trigger code should be executed, but when one particular Stored Procedure is updating data to that table, it should not allow to do so.
I search and I got the option CONTEXT_INFO with SQL SERVER. Let me share this with all of you.
NOTE: We can use CONTEXT_INFO value in that session only. If there is new session we cannot use that value stored in CONTEXT_INFO.
We can set CONTEXT_INFO up to 128 bytes of binary information with the current session or connection. Reference: http://msdn.microsoft.com/en-us/library/ms180125.aspx
Let see an example, for better understanding:
CREATE TABLE tblA(
 ID INT IDENTITY,
 ColVal VARCHAR(100)
)
Let's create two SPs, which will insert a record into tblA, which is just created
CREATE PROC TestA
AS
BEGIN 
    INSERT INTO tblA(ColVal)
    SELECT 'Allow To insert'
END
CREATE PROC TestB
AS
BEGIN 

    DECLARE @UID VARBINARY(128)
    SELECT @UID = CAST('TestB' AS VARBINARY(128))
    SET CONTEXT_INFO 0x5465737442
    
    
    INSERT INTO tblA(ColVal)
    SELECT 'Not Allow To insert'

END
In this example, when we execute SP: TestA, it should allow to insert record in the table, while we execute SP: TestB, it should restrict. Here I set CONTEXT_INFO with SPName. I can use the same whatever is stored in CONTEXT_INFO during the current session.
To make validation as defined above, lets create one trigger to restrict/allow user to proceed.
CREATE TRIGGER trg_TblA
   ON  tblA
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @Message varbinary(128)
    SELECT @Message = cast('TestB' as varbinary(128))
    
    IF    @Message = CONTEXT_INFO() BEGIN
        RAISERROR('Not Allowed to Insert/Update/Delete from SP: TestB',15,1)
        ROLLBACK TRAN
    END
        
END
In Trigger, as you can see, I again converted the SP name and compare it with CONTEXT_INFO.
When Stored Procedure TestA is being executed, CONTEXT_INFO is not set, so trigger will not find comparison and allows user to insert record.
While we execute TestB, as we have set CONTEXT_INFO, system will find comparison in trigger and restrict us to complete transaction. So system will give error message:
So, by this way we can validate value in trigger, to make such decision
Reference : Tejas Shah http://www.SQLYoga.com

July 15, 2009

SQL SERVER: Reset Setup Values, when SQL SERVER is started/restarted



We have a requirements to clear all setup values when SQL SERVER is started/restarted and we need to setup default values to setup table.

I found one Stored Procedure provided by MS SQL SERVER. Let me share it with all of you.

SQL SERVER provides this SP: “sp_procoption”, which is auto executed every time when SQL SERVER service has been started. I found this SP and it helps me to figure it out the solution for the request as following way. Let me show you how to use it

Syntax use this SP:

EXEC SP_PROCOPTION 
@ProcName = 'SPNAME',
@OptionName = 'startup',
@OptionValue = 'true/false OR on/off'

@ProcName, should be Stored procedure name which should be executed when SQL SERVER is started. This stored procedure must be in “master” database.


@OptionName, should be “startup” always.


@OptionValue, this should be set up to execute this given sp or not. If it is “true/on”, given sp will be execute every time when SQL SERVER is started. If it is “false/off”, it will not.


That’s it, lets take an example.


I have one Database called Test, I have created setup table:



CREATE TABLE SetupTable(
Seq INT IDENTITY,
Code VARCHAR(100)
)

Lets insert some default values to this table:



INSERT INTO SetupTable VALUES('A')
INSERT INTO SetupTable VALUES('B')
INSERT INTO SetupTable VALUES('C')
INSERT INTO SetupTable VALUES('D')

What I need to do is, I need to wipe out this values when SQL SERVER is started and fill it with the same default values, because these values might be updated by application.So, I created one stored procedure in master database, named,



CREATE PROC ClearAllData
AS

DELETE
FROM Test.dbo.SetupTable

INSERT INTO SetupTable VALUES('X')
INSERT INTO SetupTable VALUES('Y')
INSERT INTO SetupTable VALUES('Z')

and set up this stored procedure as auto executed every time when SQL SERVER is started as:



EXEC SP_PROCOPTION 
@ProcName = 'ClearAllData',
@OptionName = 'startup',
@OptionValue = 'true'



Now, restart SQL SERVICES, and you find that old values will be deleted and new values with ‘X’, ‘Y’, and ‘Z’ will be inserted automatically.


If now you want to stop it to execute automatically, we just need to execute this with “false” as:



EXEC SP_PROCOPTION 
@ProcName = 'ClearAllData',
@OptionName = 'startup',
@OptionValue = 'false'



I hope this is very clear to use this feature.


 


Reference : Tejas Shah (http://www.SQLYoga.com)

May 12, 2009

SQL SERVER: Configure Database Mail with SQL SERVER 2005

We used Database mail to send mail to client on each updates.

This is a very simple process to configure. Let me share how to configure Database mail with sql server 2005 with all of you.

After setting up Profile and Account properly, you just need to write following code to send a mail to client:

Step 1:

Configure Database Mail Step 1

Step 2:

Configure Database Mail Step 2

Step 3:

Configure Database Mail Step 3

Step 4: You might get this message:

Configure Database Mail Step 4

Step 5: Create Profile

Configure Database Mail Step 5

Step 6 : Create Account

Configure Database Mail Step 6

That's it.

exec msdb.dbo.sp_send_dbmail
@profile_name = 'ProfileName', IN our CASE, 'Tejas'
@recipients = 'Client Email Address' ,
@blind_copy_recipients = 'BCC Address',
@subject = 'Subject',
@BODY = 'Message Body',
@body_format = 'Message Type', it could be text OR html

Let me know if you have any complexity or comments in setting up Database mail.


April 20, 2009

SQL SERVER: Get Result ORDER BY Time regardless Date on SQL DateTime column


Usually we save Time with Dates in DATETIME column.
Today, I came across situation, where I need to sort my result set by Time, regardless the Date.

Let's see Example

I have some sample data like this:

DECLARE @Data TABLE(dt DATETIME)
INSERT INTO @Data(dt)
SELECT '2008-12-05 04:00:00.000'
UNION ALL
SELECT '2008-12-10 10:00:00.000'
UNION ALL
SELECT '2009-03-01 08:00:00.000'
UNION ALL
SELECT '2009-03-02 07:15:00.000'
UNION ALL
SELECT '2009-03-10 08:50:00.000'
UNION ALL
SELECT '2008-12-31 23:00:00.000'
UNION ALL
SELECT '2009-05-01 21:10:00.000'

SELECT * FROM @Data


Now we need output like this:

Get Result Order BY Time regardless Date on DateTime column


I found very quick solution for this. You can create query as follows:


SELECT * 
FROM @Data
ORDER BY Convert(VARCHAR, dt,108)


Let me know if it helps you in any way.

April 15, 2009

SQL SERVER: Difference between OpenQuery and OpenRowSet

Today, one developer asked me what is the difference between OpenQuery and OpenRowSet.

Let me share this thing with all of you.

Syntax for both the command:

OPENQUERY ( linked_server ,'query' )

OPENROWSET
( 'provider_name' , 'datasource' ; 'user_id' ; 'password'
, { [ catalog. ] [ schema. ] object | 'query' }
)

Difference is:
OpenQuery uses a predefined linked server,
While OpenRowSet has to specify all the connection options. So with OpenRowSet you can query to your remote SQL server from local.

Else it's the same.

Let me know if it helps you in any way.

April 14, 2009

SQL SERVER: SQL Query To Find Most used Tables



We have very large database and today we want to search the tables which are used mostly.

Means tables which are used in Procedures, Constraints, Views, Triggers etc.

I know this is very strange requirement, but we need to do this.

So, I tried to make an query which will help me to find out the top most tables used in other objects as I mentioned

Let me share that sp with all of you:
SELECT TableName, COUNT(*) 
FROM (
Select Distinct
o.Name 'TableName',
op.Name 'DependentObject'
From SysObjects o
INNER Join SysDepends d ON d.DepId = o.Id
INNER Join SysObjects op on op.Id = d.Id
Where o.XType = 'U'
Group by o.Name, o.Id, op.Name
) x
GROUP BY TableName
ORDER BY 2 desc

So, I made my life easy, by using this. I can get the list if Tables which are used most.





Let me know if it helps you in any way.






April 2, 2009

SQL SEVER: How to make an Entry in Event Viewer using SQL SERVER

Today I came across the situation where I need to following scenario.

I need to make entry in Event Viewer when there is an error in Stored Procedure.

I wondered to do this, but finally I come up with the solution. SQL is much powerful.

SQL provides us to make an entry in Event Viewer by two ways:
1. using XP_LogEvent

2. By Raiserror WITH LOG

Lets first see the way using XP_LogEvent:

Here I have created one SP which will raise an error “Divide by zero error encountered.” as I tried to do “10 / 0”.

CREATE PROCEDURE TestEventViewer
AS
BEGIN TRY
    SELECT  10/0
END TRY
BEGIN CATCH
    PRINT ERROR_Message()
    DECLARE @msg VARCHAR(100)
    SELECT @msg = ERROR_Message()

    EXEC xp_logevent 60000, @msg, informational

END CATCH

Lets Execute this SP:

EXEC TestEventViewer

This will write entry in Event Viewer. Now open Event Viewer. You can find Event Viewer at Control Panel –> Administrative Tools –> Event Viewer.

You will get en entry of Error there.

171

So we can do this by Extended Stored Procedure: “xp_logevent”.

Let see the parameters of this Procedure.

First Parameter: “60000” is the Error Number

Second Parameter: “@msg” is the message to be displayed in Event Viewer.

Third Parameter: “informational” is the Error Level. It could be “informational”, “Error”, “Warning”.

Now, Lets see by another way By Raiserror WITH LOG:

It is the same way as we used Raiserror to Raise an Error.

CREATE PROCEDURE TestEventViewer
AS
BEGIN TRY
    SELECT  10/0
END TRY
BEGIN CATCH
    PRINT ERROR_Message()
    DECLARE @msg VARCHAR(100)
    SELECT @msg = ERROR_Message()

    RAISERROR(@msg, 11, 1) WITH LOG

END CATCH

Lets Execute this SP:

EXEC TestEventViewer.

So By these ways we can make an entry to Event Viewer.

Let me know if it helps you in any way

March 26, 2009

SQL SERVER: How much space occupied by Each Table with sp_MSforeachtable procedure

Today, I came across requirement where I need to perform an action on all of the tables within a database.

For example, How much space occupied by each table.

I found undocumented Procedure: sp_MSforEachTable in the master database.

The following script reports the space used and allocated for every table in the database.

USE AdventureWorks;
EXECUTE sp_MSforeachtable 'sp_spaceused [?]'

So, We can use sp_MSforeachtable procedure when we need to loop through each table.

Let me know if it helps you in any way.

March 22, 2009

SQL SERVER: Unable to Launch Activity Monitor. How to add VIEW SERVER STATE Permissions to launch Activity Monitor

Activity Monitor, is used view to current executing queries, Query status, Start Time, Host, Blocking or not, Database etc.

Last week, one of my developer has one issue. When he try to open Activity Monitor, System gives the following Error:

152

I found that this user need to get VIEW STATE Permissions, Lets see how to give this permission to the user.

We can give permission to user by this two ways:

1. T-SQL: GRANT VIEW SERVER STATE TO YourLogin

2. GUI:

Step 1: Right mouse click the SQL Server and go to Properties. You need to give GRANT to “View server State’ for that user.

151


Let me know it it helps you.