Every day, I transfer four tables from a remote SQL Server in Arizona to a local SQL Server in New Mexico. Sometimes, the tables from the remote SQL Server system don't contain data, so I check the record count before I transfer the tables. Managing these tables on a local SQL Server is easier than using another connection object in my Active Server Pages (ASP) code, and I'd like to automate this transfer instead of doing it manually each day. Can I use a stored procedure to do this job?
You can use linked servers to set up a stored procedure to automate the transfer. Here's the code:
USE Pubs
CREATE PROC mytransferproc
AS
IF (SELECT COUNT(*) FROM NewMexico.pubs.dbo.authors) > 1
INSERT myLocalAuthors SELECT * FROM NewMexico.pubs.dbo .authors
...
RETURN
Even better, you can create a DTS package to perform the transfer. Use the Export/Import Wizard to build a template, then use the DTS designer environment to add any validation checking you want. After that, simply schedule the transfer with the SQL Server Agent service. Setting up the DTS automation takes a little more time, but this method saves you time in the long run.
In SQL Server 6.5, I'm trying to create a table with several records that have only two columns: iincidentid and worknotetext. I'm populating this table with data from another table by selecting incidentid into iincidentid and vchWorknote1..vchworknote20 (varchar(255)) into worknotetext(text). I've followed examples from SQL Server Books Online (BOL) and other resources, but they updated only the last record.
You can use updatetext to update all the records. For example, Listing 2 contains a combination of code and pseudocode that loops through the old table, then loops through the 20 columns of the new table, adding each one with updatetext. The code isn't complete, but it will get you started.
If you run the following query in SQL Server 7.0
SELECT Len(' ')
the result is 0 (zero), but if you run the same query in SQL Server 6.5, the result is 1. Which answer is correct, and why does this happen?
The len function returns the length of a string, excluding trailing blanks. Because all blanks in this string are trailing, SQL Server eliminates them (per the SQL Server 7.0 documentation for this function—Microsoft didn't document or support len in SQL Server 6.5) and correctly returns the length as 0. BOL says len "returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks." Note that SQL Server 7.0 properly supports empty strings, whereas SQL Server 6.5 typically references empty strings as a single blank character.
End of Article
Prev. page
1
[2]
next page -->
