Geo Diverse SQL Server site locations

Looking for a couple options to setup a geo-diverse sql cluster configuration
between two separate site locations. Trying to determine what tryp of
replication I should use and/or creating transactional and snapshot
publications. The sites will have the same high end hardware for the servers
and SAN arrays. We utilize about 4 databases with one of them being the
master DB for history of our services. Any input is helpful.
Corey
Depending on the SAN vendor, they have some solutions out there.. Or else
there are 3rd party products from NSI, Xosoft,Neverfail just to name a few
that can help
"Corey" <Corey@.discussions.microsoft.com> wrote in message
news:E703FBF0-8671-4308-A929-DEC2DA58DECC@.microsoft.com...
> Looking for a couple options to setup a geo-diverse sql cluster
configuration
> between two separate site locations. Trying to determine what tryp of
> replication I should use and/or creating transactional and snapshot
> publications. The sites will have the same high end hardware for the
servers
> and SAN arrays. We utilize about 4 databases with one of them being the
> master DB for history of our services. Any input is helpful.
> Corey

Generate SQL scripts creating a separate file per object

Hello all,

What is an easy way of scripting all the database tables and sprocs automatically to a separate file each one? I can't find this option in SQL Server Management Studio.

Or is any other way of uploading all objects to a Visual Source Safe project that does not require to copy and paste each script into a VSS sql script?

Thanks!

David

Using SQL Server 2005, right click the database, choose Tasks-> Generate Scripts. You should see a new window open with an info screen. Click Next. Now you should be at the 'Select Database' screen. Select the database you want to generate scripts for, then check the "Script all objects in the selected database" checkbox. Click Next.

Now make sure your script options are correct. If you use the default properties for SQL Server setup, then default should be fine for this as well. Click Next after making any necessary changes.

Now you are at the "Output Option" screen. Select the "Script to file" radio button, and under "Files to generate", select the "File per object" option. Make sure you set your save location and encoding, if necessary.

At this point, you can either click Next to view a summary of what you just selected, or you can click Finish to start scripting. (There is also a "Finish" button on the summary screen.)

|||

That "File per Object" option does not appear in my SQL Server Manager 2005.

I think I have installed the latest service pack.

|||

neutrino:

That "File per Object" option does not appear in my SQL Server Manager 2005.

I think I have installed the latest service pack.

I'm not sure if it has to do with the service pack. I have two computers that should both be updated to the latest SP. However, I was able to reproduce this problem with a machine running XP Pro, and the SQL Server Management Studio version was 9.00.1399.x.

The version this works on is 9.00.3042.x, and it is running on Server 2003.

Both computers only have the Development version installed, so maybe it has to do with being on a server OS?

generate a text file from SQL Server

Hi

I need to generate a text file from SQL Server. The task automatically runs daily.

The format of the text file use "/" to separate the field and the row. At the end of the file, it adds number of char and uses "\". The number of char includes the data parts and excludes a few word of "the number of char".

For example,
The underline fonts mean the first row record. The Bold fonts mean the second row record. The Italic fonts mean the third row record. The red fonts mean the number of char and the end of file.

1/060222/008888/234/1/7441/2/BB/10000//////290025/////1/060222/008888/234/1/7441/3/XX/100-//////290025/////1/060222/008881/234/1/7442/2/BB/10000//////290025/////161\

I am no idea about this problem. Please give me some suggestions. Thanks a lot

regards
Alex

For your information

I use SQL Server 2000 in Windows 2000 Server environments

Hi Alex,

You need to generate a Text File from SQL Server with field delimeter as "/" and the line delimeter as "/////"?

Also please let us know that whether you want to generate a Text file with static filename or it may vary?

Anyway I will also try to generate the file based on my understandings from your post.

Regards,

Prakash Srinivasan

|||

Hi Prakash Srinivasan

The field delimeter is "/" and there is no line delimeter.

The task generates many files and place at different places where are the fixed folders. The filenames are static and need to override the old one.

Thank you for your help.

Alex

|||

Hi Alex,

If you don't have any line delimeter (Row delimeter), then how would you differentiate the records. So there should be a row delimeter in the file to separate the records.

I assume there is and proceed further.

Regards,

Prakash Srinivasan

|||

Hi Alex,

First create a Package and put a Data Flow. In the Data Flow, put one OleDb Source and Flat File Destination. Also create Connection for both OleDb Source and Flat File Destination.

And in the Flat File Connection Manager, specify the filename in which you want to dump the data, and leave the rest of the setting in General tab as it is.

In Advanced tab, create columns based on your requirement (Suppose if you need only 3 columns, create 3 columns and set the datatype based on the datatype specified in your SQL Server database).

In Columns tab, give the Row Delimeter as "////" and Column delimeter as "/".

Now connect your OleDb Source to the OleDb Connection manager and mention the tables which you want to access.

Now map the columns appropriately in the Flat File Destination and execute the package.

The results are coming exactly in my machine. And for writing the number of characters at the end of the file, put one Script Task and write a code to open the text file and read till the end of the file and write at the end of the file and add "\".

I hope there is a way to get this done in SSIS. If I get that, will let you know.

Regards,

Prakash Srinivasan.

|||

One record followed by one record. The number of field are fixed in each row, so there are not the row delimeter.

for example,
record1field1/record1field2/record2field1/record2field2/55\

--Alex

|||Being new to this, how would you do it with a dynamically generated name? I need to generate a text file with the date as the filename and a key field from from a table daily. Thanks in advance!|||

Miles Calunod wrote:

Being new to this, how would you do it with a dynamically generated name? I need to generate a text file with the date as the filename and a key field from from a table daily. Thanks in advance!

Miles,

Put an expression on the connection string property of the connection manager that you are using to point at the destination file. This expression will get evaluated at runtime and therefore can set the connection string (i.e. the file location) to be whatever you like.

-Jamie

generate a text file from SQL Server

Hi

I need to generate a text file from SQL Server. The task automatically runs daily.

The format of the text file use "/" to separate the field and the row. At the end of the file, it adds number of char and uses "\". The number of char includes the data parts and excludes a few word of "the number of char".

For example,
The underline fonts mean the first row record. The Bold fonts mean the second row record. The Italic fonts mean the third row record. The red fonts mean the number of char and the end of file.

1/060222/008888/234/1/7441/2/BB/10000//////290025/////1/060222/008888/234/1/7441/3/XX/100-//////290025/////1/060222/008881/234/1/7442/2/BB/10000//////290025/////161\

I am no idea about this problem. Please give me some suggestions. Thanks a lot

regards
Alex

For your information

I use SQL Server 2000 in Windows 2000 Server environments

Hi Alex,

You need to generate a Text File from SQL Server with field delimeter as "/" and the line delimeter as "/////"?

Also please let us know that whether you want to generate a Text file with static filename or it may vary?

Anyway I will also try to generate the file based on my understandings from your post.

Regards,

Prakash Srinivasan

|||

Hi Prakash Srinivasan

The field delimeter is "/" and there is no line delimeter.

The task generates many files and place at different places where are the fixed folders. The filenames are static and need to override the old one.

Thank you for your help.

Alex

|||

Hi Alex,

If you don't have any line delimeter (Row delimeter), then how would you differentiate the records. So there should be a row delimeter in the file to separate the records.

I assume there is and proceed further.

Regards,

Prakash Srinivasan

|||

Hi Alex,

First create a Package and put a Data Flow. In the Data Flow, put one OleDb Source and Flat File Destination. Also create Connection for both OleDb Source and Flat File Destination.

And in the Flat File Connection Manager, specify the filename in which you want to dump the data, and leave the rest of the setting in General tab as it is.

In Advanced tab, create columns based on your requirement (Suppose if you need only 3 columns, create 3 columns and set the datatype based on the datatype specified in your SQL Server database).

In Columns tab, give the Row Delimeter as "////" and Column delimeter as "/".

Now connect your OleDb Source to the OleDb Connection manager and mention the tables which you want to access.

Now map the columns appropriately in the Flat File Destination and execute the package.

The results are coming exactly in my machine. And for writing the number of characters at the end of the file, put one Script Task and write a code to open the text file and read till the end of the file and write at the end of the file and add "\".

I hope there is a way to get this done in SSIS. If I get that, will let you know.

Regards,

Prakash Srinivasan.

|||

One record followed by one record. The number of field are fixed in each row, so there are not the row delimeter.

for example,
record1field1/record1field2/record2field1/record2field2/55\

--Alex

|||Being new to this, how would you do it with a dynamically generated name? I need to generate a text file with the date as the filename and a key field from from a table daily. Thanks in advance!|||

Miles Calunod wrote:

Being new to this, how would you do it with a dynamically generated name? I need to generate a text file with the date as the filename and a key field from from a table daily. Thanks in advance!

Miles,

Put an expression on the connection string property of the connection manager that you are using to point at the destination file. This expression will get evaluated at runtime and therefore can set the connection string (i.e. the file location) to be whatever you like.

-Jamie