Appropriate Data Type

I want to save some serialized data in a SQL Server 2005 table. What is the appropriate field type for this? I won't be associating a schema with the data. The data could vary in length--even up to several MB. I'm not sure whether to use text, NVARChar(MAX) or XML.

Brian

Brian,

A couple of SQL Server 2005 MSDN aritcles talk about when to use XML and some of the best practices. Some relevant ones are "XML Best Practices for Microsoft SQL Server 2005"; "XML Options in Microsoft SQL Server 2005"; "Performance Optimizations for the XML Data Type in SQL Server 2005".

In short XML is very useful 1) when you want schema validation (may not apply to your case); 2) when you want to query into the XML data or update granular parts of it. If your data is in XML format but your application merely uses the database to store and retrieve the data, an (n)varchar (max) column might suffice.

As a side note: Use varchar and nvarchar datatypes instead of text and ntext as the latter are in deprecation path (Deprecated Database Engine Features in SQL Server 2005)

Thanks

Babu

Applying the Schema Snapshot and Partitioned Snapshot to a NewSubscriber

If client is synchronizing for first time with server at that time
Snapshot is applied to the client database. If meanwhile application
crashes or network goes down then snapshot is partially applied. When
i am restarting my application and trying to synchronize again my
server database gets corrupted (i.e. some rows get deleted from
central server)
So when a new subsciber is synchronising for first time and meanwhile
the process crashes then what will happen and how to resolve it.
On Nov 15, 12:07 pm, Manish <jainmani...@.gmail.com> wrote:
> If client is synchronizing for first time with server at that time
> Snapshot is applied to the client database. If meanwhile application
> crashes or network goes down then snapshot is partially applied. When
> i am restarting my application and trying to synchronize again my
> server database gets corrupted (i.e. some rows get deleted from
> central server)
> So when a new subsciber is synchronising for first time and meanwhile
> the process crashes then what will happen and how to resolve it.
Currently I done following and problem disappeared. But pls suggest
other options.
When i observed replication related system tables i decided to kill
processed to related to
string sql = "select spid from master.dbo.sysprocesses WHERE
program_name like '" + getMergeAgentProgramName() + "%'"; where
getMergeAgentProgramName() is as follows
private string getMergeAgentProgramName()
{
return mPublisher + "-" +
mPublicationDatabase + "-" +
mPublication + "-" +
mSubscriber;
}
the query will get processes related to replication
after that i killed each process selected under above sql query.
foreach (string spid in spIds)
{
sql = "kill " + spid;
publisherConn.ExecuteNonQuery(sql);
}
but the solution seems un scalable and may be unreliable.

Applying Schema change.

Hi.
One of teh major problems we are facing is happening when we change the data
base structure for new releases and we want to dispatch this to existing
clients.
1.Is there a utility that allow us to apply the new schema on an exiting
data base.
2. Is there also a utility that will list records that are imcompatible with
the new schema (constraints violations ,etc...)
thanks1.
http://www.innovartis.co.uk/database_dbghost_home.aspx
http://www.red-gate.com/SQL_Compare.htm
2. I'm not aware of any. Typically I would expect to develop my own
transformations to align the legacy data with the new schema.
David Portas
SQL Server MVP
--

Append new records from ODBC source

I have created a data warehouse that pulls information from an ODBC source into a SQL database. The schema in the destination matches the source, and the packages clear the destination tables, then append all the records from the source. This is simpler than updating, appending new, and deleting on each table to get them in sync since there is no modify timestamp in the source.

There are cases where I just want to append records from the source table that do not already exist in the destination table, without clearing the destination table first.

How can this be done with a SSIS job? Also, how can the job be run from a Windows Forms application?

Steve Jensen wrote:

There are cases where I just want to append records from the source table that do not already exist in the destination table, without clearing the destination table first.

Steve,

Have you considered to use a Lookup task in your data flow to check if the row already exists in the destination table and then use the error output (no matches) for inserting only non existing rows? Notice that the error output of the lookup task needs to be set as 'redirect rows' in order to get this behavior

|||

Sounds good. Could you give me some detailed instructions for doing this?

Thanks,

Steve

|||

Steve here is a detailed description:

http://blogs.msdn.com/ashvinis/archive/2005/08/04/447859.aspx

I Hope this can help you

Rafael Salas

|||

Sorry, but I'm still at the 'first, you drag a data flow task onto the design surface ...' stage. I understand the concept but need a more step-by-step on how to do it.

Thanks,

Steve

Append new records from ODBC source

I have created a data warehouse that pulls information from an ODBC source into a SQL database. The schema in the destination matches the source, and the packages clear the destination tables, then append all the records from the source. This is simpler than updating, appending new, and deleting on each table to get them in sync since there is no modify timestamp in the source.

There are cases where I just want to append records from the source table that do not already exist in the destination table, without clearing the destination table first.

How can this be done with a SSIS job? Also, how can the job be run from a Windows Forms application?

Steve Jensen wrote:

There are cases where I just want to append records from the source table that do not already exist in the destination table, without clearing the destination table first.

Steve,

Have you considered to use a Lookup task in your data flow to check if the row already exists in the destination table and then use the error output (no matches) for inserting only non existing rows? Notice that the error output of the lookup task needs to be set as 'redirect rows' in order to get this behavior

|||

Sounds good. Could you give me some detailed instructions for doing this?

Thanks,

Steve

|||

Steve here is a detailed description:

http://blogs.msdn.com/ashvinis/archive/2005/08/04/447859.aspx

I Hope this can help you

Rafael Salas

|||

Sorry, but I'm still at the 'first, you drag a data flow task onto the design surface ...' stage. I understand the concept but need a more step-by-step on how to do it.

Thanks,

Steve

API for Synonyms

Hi Experts:

I am writing a general API which would fetch synonyms from any database
providers (and would filter for a given schema).

I am using GetSchema method on DBConnection object and it works fine for
oracle.

SQL Server 2005 has a support for synonyms, but the above piece of code does
not work. Is there any API by which i can get the list?.

Thanks

AK

bets thing would be to use the SMO libraries, a sample script would be:

Code Snippet

foreach (Synonym s in s.Databases["Somedb"].Synonyms)

{

Console.WriteLine(s.Name);

}

Jens K. Suessmeyer.

http://www.sqlserver2005.de