SQL Server 2000
SQL Server 2000
Here's a brief guide to set up Transactional Replication from a back up copy.
-
Restore the publication database onto subscriber.
-
Restore the last transaction log backup onto subscriber.
-
Drop all foreign key constraints on the subscriber database.
SELECT DISTINCT 'ALTER TABLE [dbo].[' + object_name(fkeyid) +
'] DROP CONSTRAINT ' + object_name(constid) +
CHAR(13) + CHAR(10) + 'go'
FROM SYSFOREIGNKEYS
GO
UPDATE syscolumns SET xtype=173, xusertype=173 WHERE xtype=189
--If updates not allowed use foll. script
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
-
Set up Publication on the publisher database using the Replication -> Configure Publishing, Subscription and Distribution wizard.
-
Get the code for sp_scriptpublicationcustomprocs from MASTER database. Make sure the width of the results window is set to 1200 in the Tools -> Options window for both Publisher and Subscriber.
-
Compile and create the stored proc code on the publication database.
-
Execute the stored proc on the publication. The stored proc generates all the required Add/Update/Delete stored procedures for all the articles.
exec sp_scriptpublicationcustomprocs @publication = N'Pub Name here'
Note: If publisher DB is not enabled for 'Publish' use the following code to enable it first.
USE master
GO
EXEC sp_replicationdboption @dbname = N'CopyProd', @optname = N'publish', @value = N'true'
GO
-
Compile the generated script on the subscriber database.
-
Set up the Subscriber. Right click on the Publisher -> Properties -> "Push New". Dont forget to choose the "No, the Subscriber already has the schema and data" option on the Initialize Subscription screen.
Issues with updates on tables with IDENTITY Columns
If you are updating a table that has an identity column and if replication distribution agent fails with error: “Cannot update identity column ”, the following steps can help resolve the issue.
The advantage with this method compared to dropping and adding the article from subscription is that there is no need to generate the snapshot which means no need to transfer the millions of rows for large tables over the network to the subscriber.
- Get the script from MASTER db for the stored proc sp_scriptdynamicupdproc
- Compile the stored proc on the publisher DB
- Get the article-Id from sysarticles for the table in question.
- Execute the stored proc sp_scriptdynamicupdproc @artid =
- The stored proc will generate a script for “update” procedure.
- Compile the generated script on the subscriber database.
If you have many tables with IDENTITY columns, you can write up some dynamic SQL to generate the update stored procs as mentioned above to a file and compile all the stored procs at once on the subscriber.
I am sure every SQL Developer/DBA must have had to do this more than once. There is no way to copy the heading from the result set if the output is a grid. If the output is in text you could cut/paste but there were formatting issues especially with datetime columns and with columns that are of varcahr types and have numbers in them..etc.
Recently I was asked to provide some data in an Excel sheet. Some of my columns had values as:
1855760000019
1855760000019
714 Gresham St
4615 Willow Street
2620 Santa Domingo Dr
I tried the methods from The Best Kept Secret About SQL Query Analyzer @ SQLServerCentral.com but I had formatting issues.
The issue I had was that the excel sheet was showing the values as
1.85576E+12
1.85576E+12
714 Gresham St
4615 Willow Street
2620 Santa Domingo Dr
The only way I could get the actual value to show up was by formatting the cell content as "Fraction". Anything else I tried would either put comma's or currency symbols or percentage symbols. And I couldnt do this for each cell either since the result set was a few thousand records and there's no sense in going through each record and formatting. I have googled around and saw some tricks and hacks. Almost all of them dealt with some hacks from Query Analyzer. So I started looking at all the options available from Excel. Surely its not just some tool to pass around chunks of data. It can do more. And I did find my solution. So I thought I'd blog about this if any one else is in a similar situation.
Check this:
In Excel Under Data > Import External Data > New Database Query
If the Data Source is not already in there you can easily create one.
By default the option is selected. So click on OK and the wizard to create the new Data Source will appear.
- Give a name
- select the database driver (SQL Server is the last one in the list)
- click "Connect"
- Enter or choose the Server Name
- Enter the type of connection - windows/sql
- Click on "Options >>" Select the database from the list
- If your data is coming straihgt from a table we wouldnt even be going through all this. So assuming the source is a query, leave the option for data source drop down blank and click "OK"
- The Query Wizard to choose tables/columns appears. Click on "Cancel". Then close the window that pops up with table names again.
- Click on the SQL tab. A new query editor pops up. Copy your query from the Query Analyzer into this editor.
- Click on "yes" if it says the results cant be represented graphically.
- The query results should appear in a tabular format.
- Locate the button that says "Return Data" to the left of the SQL button and click it.
The results should be in the spread sheet now without losing any formatting or any unnecessary data conversions by excel.
Have fun.
I digged the RS books online and I found that there is a noRows property in the report which can be used to display some message when there is no data...
From Books Online :
Empty Data Regions
When the dataset for a data region returns no data, the data region is not rendered. Instead, a text box is rendered that displays the value of the NoRows property. You can edit the NoRows property in the Property window of Report Designer. The appearance properties for the data region (for example, Color, Font, and Padding) apply to the NoRows text box.
I thought of concatenating all the column names and use the NoRows property but that will be exported as 1 column rather than individual columns. I found a couple of hacks for this. Thought it could be useful for someone else...
(1) I can put the columns in the Page header ( Since I can't put table in the header I can throw in some textboxes) with the same width as the column below in the body section. This is a feasible solution.
(2) Modify the stored proc to something like :
SELECT
col1, col2...
FROM
<table>
UNION ALL
SELECT
NULL,
NULL
This will return a blank row at the end of the report and RS treats this as a row. So when exported we do get a row with headers and a blank row.
(3) This is not very efficient but works...
Get the results of the stored proc into a temp table. If there are no rows in the temp table then do a UNION with nulls (like in (2) above) and return the result set else do a SELECT * FROM temptable. This can affect the performance coz it uses temp tables...but for cases where the Report design cannot be modified and the stored proc cannot be modified ..this could be handy.
Edit :
One of my colleagues found another hack to get around this and here's how it goes :
(4) Create a table with just header and delete the detail and footer sections. Throw in all the columns that need to be in the report. Then create another table with just the detail section and put the field values appropriately. Finally line them up against each other so they appear to be from the same table header/detail sections. This is probably the most easiest and simplest of all the solutions.
Sometimes we need to pass an array to the Stored Procrdure and split the array inside the stored proc. For example, lets say there is a datagrid displaying sales orders, each sales order associated with an orderid (PK in the Sales table). If the user needs to delete a bunch of sales orders ( say 10-15 etc)..it would be easier to concatenate all the orderid's into one string like 10-24-23-34-56-57-....etc and pass it to the sql server stored proc and inside the stored proc, split the string into individual ids and delete each sales order.
There can be plenty of other situations where passing a delimited string to the stored proc is faster than making n number of trips to the server.
Create procedure ParseArray ( @Array varchar(1000), @separator char(1) ) AS
set nocount on
-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma
declare @separator_position int -- This is used to locate each separator character
declare @array_value varchar(1000) -- this holds each array value as it is returned
-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
set @array = @array + @separator
-- Loop through the string searching for separtor characters
while patindex('%' + @separator + '%' , @array) <> 0
begin
-- patindex matches the a pattern against a string
select @separator_position = patindex('%' + @separator + '%' , @array)
select @array_value = left(@array, @separator_position - 1)
-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @array_value holds the value of this element of the array
select Array_Value = @array_value
-- This replaces what we just processed with and empty string
select @array = stuff(@array, 1, @separator_position, '')
end
set nocount off
GO
Most of the code is pretty well documented and self-explanatory.
I found the above code-snippet during one of my searches and thought it would be a useful piece of code.