SQL Server Mailbag: Stored procedures, triggers and SSRS reports

In In this edition of the SQL Server mailbag, expert Denny Cherry answers reader questions regarding editing data in Reporting Services, retrieving images stored as bytes, writing stored procedures and more.

Have a question of your own? Send it along to editor@SearchSQLServer.com.

How do I write a stored procedure for an INSERT query and how can I retrieve the affected rows in a CS file? -- Shital

Stored procedures are written much like a normal insert statement. This example assumes that your table has an identity column.

CREATE PROCEDURE YourInsertProcedureName
             @YourVariable1 INT,
             @YourVariable2 VARCHAR(40),
             @IdValue INT OUTPUT
AS
INSERT INTO YourTable
(Column1, Column2)
VALUES
(@YourVariable1, @YourVariable2)

SET @IdValue = scope_identity()
GO

To retreive the record you'd need to query the database for the record, either directy through T/SQL or via another stored procedure.

CREATE PROCEDURE YourSelectProcedure
             @IdValue INT
AS
SELECT Column1, Column2
FROM YourTable
WHERE IdColumn = @IdValue
GO

I have two SQL Server 2000 instances on two different machines. I want Instance A to work as the client to Instance B without uninstalling instance A. This way, all the requests to Instance A will be requested to Instance B via. Can I do this? -- Haroon

There is no way to have one SQL Server redirect all connections to another SQL Server. You'll need to shut down the server running Instance A then add a DNS record to your internal DNS servers pointing the old server's name to the new server's name. If your clients are connecting by IP address, you'll need to add the old server's IP address to the new server's IP address, as well.

Can we edit the data in the SQL Server Reporting Services (SSRS) report directly and save it to the database? Please Help. -- Charan

More answers from Denny Can I downgrade from Enterprise to Standard Edition? Is SharePoint an alternative to SQL Server?

No, you can't edit the data in the SQL Server Reporting Services report directly. You could setup a report that takes the needed input parameters, and use the click through functionality to open that report and use that second report's input parameters to edit the value and upload the change.

I stored some images to SQL Server as bytes and I want to retrieve them and display them in the ListView control in my Windows application. I tried using FILESTREAM and MEMORYSTREAM but they didn't work. Could you please help? I program with VBnet 2005. Thanks. -- Zainh

To get the data from the SQL Server you'll need to either use a SELECT statement, or a stored procedure to get the binary data from the database. At that point, the easiest way to get the image into the ListView would be to save the binary data to the hard drive in a graphic file of the correct type, then load the file using normal methods.

I need to create triggers that will synchronize two separate database tables (both directions). Is there a way of having the trigger verify an update that doesn't already exist on the other database/table before applying the change? I'd like to avoid an infinite trigger loop if a change already exists. An example would be:

A new record is added to table A. The trigger will check if table B already has the record before an INSERT is executed. Table B has a change, and the trigger checks table A to see if the change already exists before executing an UPDATE.

I'm using SQL Server 2005 to create the triggers. I don't have access to any of the ADMIN tools or data services. -- Bruce

Yes, this can be done. It just requires a bit of extra logic in the triggers. You'll need to modify the column names and tables to match yours. Of course if you don't need the nested triggers, you can disable nested triggers via the sp_configure system stored procedure.

CREATE TRIGGER Trigger1 ON Table1
FOR
INSERT, UPDATE
AS
BEGIN
     IF NOT EXISTS (SELECT * FROM deleted)
     BEGIN
          INSERT INTO Table2
          (YourColumns...)
          SELECT YourColumns
          FROM inserted
          WHERE NOT EXISTS (SELECT * FROM Table2 WHERE inserted.PrimaryKey = Table2.PrimaryKey)
     END
     ELSE
     BEGIN
          UPDATE Table2
               SET YourColumn1 = inserted.YourColumn1,...
          FROM inserted
          WHERE inserted.PrimaryKey = Table2.PrimaryKey
               AND (inserted.YourColumn1 <> Table2.YourColumn1
                    OR inserted.YourColumn2 <> Table2.YourColumn2
                    OR ...)
     END
END
GO
CREATE TRIGGER Trigger2 ON Table2
FOR
INSERT, UPDATE
AS
BEGIN
     IF NOT EXISTS (SELECT * FROM deleted)
     BEGIN
          INSERT INTO Table1
          (YourColumns...)
          SELECT YourColumns
          FROM inserted
          WHERE NOT EXISTS (SELECT * FROM Table1 WHERE inserted.PrimaryKey = Table1.PrimaryKey)
     END
     ELSE
     BEGIN
          UPDATE Table1
               SET YourColumn1 = inserted.YourColumn1,...
          FROM inserted
          WHERE inserted.PrimaryKey = Table1.PrimaryKey
               AND (inserted.YourColumn1 <> Table1.YourColumn1
                    OR inserted.YourColumn2 <> Table1.YourColumn2
                    OR ...)
     END
END
GO

How do I connect to SQL Server Plus through VB code? I need to connect and login then execute the query, all with VB. -- Jay

You'll need to Create a SqlConnection, a SqlCommand and a SqlDataAdapter. Then open the SqlConnection, and use the SqlCommand to execute the query, and load the resulting recordset into the SqlDataAdapter. Here is some sample code. You'll need to replace the query with your query or stored procedure, and replace the connection string with your connection string.

        Dim SQLCmd As SqlCommand
        SQLCmd = New SqlCommand()
        Dim Connection As SqlConnection

        Dim SQLda As SqlDataAdapter
        Connection = New SqlConnection("Your Connection String")
        Using Connection
            Connection.Open()
            SQLCmd.CommandText = "SELECT YourColumns FROM YourTable WHERE Column1 = 3"
            SQLCmd.CommandType = CommandType.StoredProcedure
            SQLCmd.Connection = Connection
            SQLCmd.ExecuteNonQuery()
            SQLda = New SqlDataAdapter(SQLCmd)
        End Using
        SQLCmd.Dispose()
        Connection.Close()
        Connection.Dispose()

You can pass the bulk of this information in as parameters to a procedures or function so that you can reuse the code. To do this, use SQLda.fill to put the recordset from the SqlDataAdapter into a DataSet which can be passed back to the calling code.

Have a burning SQL Server question of your own? Let us know at editor@SearchSQLServer.com, or pose it to our IT Knowledge Exchange forum for fast responses from your peers.

ABOUT THE AUTHOR:

Denny Cherry has over a decade of experience managing SQL Server, including MySpace.com's over 175-million-user installation, one of the largest in the world. Denny's areas of expertise include system architecture, performance tuning, replication and troubleshooting. He currently holds several Microsoft certifications related to SQL Server and is a Microsoft MVP. Check out his blog: SQL Server with Mr. Denny