June 28, 2005 at 1:57 am
Ok so here is my complete dilemna.
What I am trying to do is to be able to update our client's database without being able to actually be there to access it.
For example, lets say the client has a database, and we want to send him some kind of script to load so that he can just run it and update his database. Let's say we wanted to add a table to his database, he would run a script and the table would be added. That would happen without me having to actually me at the location.
This seems SUPER HARD TO DO AND I AM A ROOKIE. HELP!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!1
SI
June 28, 2005 at 2:35 am
Creating the table structure isn't hard - it's easy to get yourself a CREATE script, if you already have the table locally. Just browse to the table in the Query Analyser Object Browser, right click it and select
Script Object to New Window as ... Create
This will open a new window full of script. Running the script will create the table structure. Save this file and then try testing it yourself (in another database - otherwise you will either overwrite or conflict with the one that already exists) before sending it to the client to run.
There are some options that you ought to check out regarding this sort of automated scripting - check out Tools/Options/Script in Query Analyser and see how you go.
The harder bit is if you want the table populated with data.
Good luck
Phil
June 28, 2005 at 12:33 pm
Writing or creating the update can be easier than it may seem.
This is a script with known errors and is provided simply for an idea of how to approach this situation. This script will allow multiple IDENTITY field entries and should have flow control (i.e., IF... ELSE statements) to prevent such an occurence.
If you comment out the -- PRINT statement, you will see the dynamic SQL that is created. You may want to copy that SQL and use it in your script to the Client rather than have it run dynamically - that way you can be sure of the new data you send for the Client to include.
CREATE TABLE #States( IdentityField integer IDENTITY(1,1),
State varchar(25))
INSERT INTO #States VALUES( 'Alabama')
INSERT INTO #States VALUES( 'California')
INSERT INTO #States VALUES( 'Florida')
INSERT INTO #States VALUES( 'Iowa')
INSERT INTO #States VALUES( 'North Carolina')
INSERT INTO #States VALUES( 'Vermont')
DECLARE @IDField integer,
@sql nvarchar(2000)
SELECT @IDField = (SELECT MIN( IdentityField) FROM #States)
WHILE @IDField IN( SELECT IdentityField FROM #States)
BEGIN
SELECT @sql = (SELECT ' SET IDENTITY_INSERT #States ON' + CHAR(10) +
' INSERT INTO #States( IdentityField, State) ' +
'VALUES( ' + CONVERT( varchar,@IDField) + ', ' + CHAR(39) + State + CHAR(39) + ') ' + CHAR(10) +
'SET IDENTITY_INSERT #States OFF'
FROM #States
WHERE @IDField = IdentityField)
SELECT @IDField = @IDField + 1
-- PRINT @sql
EXECUTE sp_executeSQL @sql
END
SELECT * FROM #States
DROP TABLE #States
I wasn't born stupid - I had to study.
June 28, 2005 at 12:35 pm
The best way to do this is to have a copy of the client's database that you can work with - you then run all the scripts, updates etc. testing it on the database copy & once you have everything working as it should, you send the script (update, create, whatever....) to the client!
However, this also depends on how much data modification is going on in the interim on the client's database.....for you may test something against one set of data and have it working fine only to find that the client side had some modifications made to it and now the same scripts don't work any more....
**ASCII stupid question, get a stupid ANSI !!!**
June 29, 2005 at 9:07 am
Hi Sarah,
I could not agree with Susila more. That is why we keep copies of some part of their production data at our local site on a designated Staging Server. We have three productions servers that host data for clients and we have 5 production servers at three client sites. Out of which, on two of our client sites we do not have SysAdmin privileges. It is great fun when we have some complex scripts to be executed. Don't we all love that!
As everyone here said, the script can be easily generated in Enterprise Manager or even in Query Analyzer.
If the client does not have QA then they can run the script using 'isql' or 'osql' which they should automatically have. The syntax they would write is as follows:
isql -Usa -Ppwd -Sservername -i'C:\Scripts\input.sql' -o'x.out'
OR
osql -Usa -Ppwd -Sservername -i'C:\Scripts\input.sql' -o'x.out'
where 'C:\Scripts\input.sql' is the location of your script on the client computer and the results of the script execution will be saved in 'x.out', so you can view if the script succeded or not.
'osql' uses ODBC connection while 'isq' is native.
Hope this helps.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy