Opening Table in Management Studio is very slow

Dear Gurus

When I open a large table (say more than 1,000,000 Rows) in the SSMS by right clicking on the table name, it takes a very big time to fully open the table.More than 20 minutes for 1,000,000 records on a local instance.

SQL Server 2000 EM was extremely faster. Does any one knows a work around?

I need to be able to view and edit the data in SSMS.

Thanks in advance.

Parviz

I do not suggest ever opening a large table like this and editing the data through management studio. It is better to write queries to view the data as needed, and dml statements to update the data as needed.
Tim

opendatasouce and ingres database

Dear all,

I want to connect to an Ingres Database in Sql Server 2000 using OPENDATASOUCE

But I don't know the syntax about OPENDATASOURCE when connect to an Ingres Database?

Thanks you

Hi,

The OPENDATASOURCE syntax is quite simple: OPENDATASOURCE (provider_name, init_string). In order to connect to the Ingres this way, you need an OLE DB provider for Ingres. Microsoft does not ship one, but there're 3rd party ones available on the market. The corresponding vendor documents the provider and all the relevant connection string tokens, that is the right place to look for this information.

HTH,

Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Open a new project using SQL Server management Studio Express

Dear Friends

I am unable to open e new project using SQL Server management Studio Express. On the File menu it displays only "Query with current connection" and "Data base Engine Query". Please help me on thi Do I have to Install SQL Server management Studio instead of using express version. Cant I use SQL Server and SQL server management studio for deploy to make a small database for my a wharehouse. Please help me

Amila

Management Studio Express does not include the Project functionality of the full version. You can still create queries and scripts in SSMSE which can be saved to a .sql file, moved to a different computer and run withing SSMSE on that computer.

Use either of the options you mention to create a new query window and create the script you wish to use to build your database. Once you've got the script working, use Save from the File menu and give the file a name. You can send this file around like any text file and it can be loaded into any copy of Management Studio or Management Studio Express and run.

Regards,

Mike Wachal
SQL Express team

only Japanese got error from WebSphere to SQL 2000

Dear all:
Please help.
I have the java code to insert multi-language data from WebSphere in
Unix to MS SQL 2000 by using MS JDBC driver Service Pack 1 Version
2.2.0029.
All other language are ok but not Japanese.
I am sure that I input the correct Japanese in the statement before I
send the insert statement because I put the string into log file to
double check.
Does any one have a clue?
Thanks in advance
dennis_chen_canada@.hotmail.com
Dear all:
It is too bad.
I download and installed the MS SQL JDBC driver SP2.
The result is the same.
The traditional Chinese, simple Chinese, German, Franch are OK.
Only some Japanese will be garbage in the table. (some of the Charactor is ok, strange?)
more info:
- I log the sql statement before calling JDBC and it is correct Japanese letter
- I copy theis sql statement into a cold fusion (which use odbc to connection to the same MS SQL)
then run the cold fusion page, the result is OK in table
more question:
- How can I get the help from MS because the document said that this JDBC driver will be supported by MS?
It is kind of urgent.
Any help is welcome
|||dennis_chen_canada@.hotmail.com wrote:

> Dear all:
> It is too bad.
> I download and installed the MS SQL JDBC driver SP2.
> The result is the same.
> The traditional Chinese, simple Chinese, German, Franch are OK.
> Only some Japanese will be garbage in the table. (some of the Charactor is ok, strange?)
> more info:
> - I log the sql statement before calling JDBC and it is correct Japanese letter
> - I copy theis sql statement into a cold fusion (which use odbc to connection to the same MS SQL)
> then run the cold fusion page, the result is OK in table
> more question:
> - How can I get the help from MS because the document said that this JDBC driver will be supported by MS?
> It is kind of urgent.
> Any help is welcome
>
Hi. You would only get support from MS by paying for support. They rarely
answer posts here unless they can identify the poster as a paying support
customer... I suggest you try a commercial driver. Try the DataDirect driver,
because that is likely to indicate how quickly MS will provide a fixed
driver. DataDiarect made MS's free driver and will likely make the next version.
Their commercial driver is their most advanced product, so if that works, it will
show that they know the problem already. If you do buy a commercial driver you
will likely get faster support.
Joe
|||From: Dennis Chen <dennis_chen_canada@.hotmail.com>
To: joeNOSPAM@.bea.com
Subject: Re: only Japanese got error from WebSphere to SQL 2000
Dear Joe:
Thanks for your email.
I will try to get it later on.
Currrently, we are facing the urgent issue that comes from customers.
Do you have any other suggestion.
rgds,
Dennis Chen
Techinical Manager, E-Commerce Div.
www.ulead.com
|||dennis_chen_canada wrote:

> From: Dennis Chen <dennis_chen_canada@.hotmail.com>
> To: joeNOSPAM@.bea.com
> Subject: Re: only Japanese got error from WebSphere to SQL 2000
> Dear Joe:
> Thanks for your email.
> I will try to get it later on.
> Currrently, we are facing the urgent issue that comes from customers.
> Do you have any other suggestion.
> rgds,
> Dennis Chen
> Techinical Manager, E-Commerce Div.
> www.ulead.com
Call Microsoft technical support and pay them to take your case,
but don't expect rapid response because typical MS support only
has a vague idea of what Java/JDBC is, so they would eventually
pass the case on to the folks in MS who talk to the external company
that makes the driver...
I would create a standalone jdbc program that demonstrates the problem
if possible. I would suggest also trying to get support from IBM
Websphere folks. They should have some MS/JDBC experts. If you had
been using BEA's Weblogic, I would have been able to do more, and
would have solved your problem...
Joe Weinstein at BEA

>

Online Data Transform

Dear All,

I am new to SSIS, and just watched some of the webcasts and take a look at SSIS Hands on Lab on the MSDN site.

I want to handle an online data transformation scenario which has some data mappings between. I want the SSIS to add each new record in source table, insert in the destination tables online. If this is possible, please let me know where can I find a sample package and configurations.

Happy new year,
Sassan

What is an "online data transformation"?

What do you mean by "insert into the destination table online"?

Do you mean that you want each record in a source table to be inserted into a destination table? If so, what exactly are you having trouble achieving?

Please be more specific.

-Jamie

|||

Dear Jamie,

Thanks for taking time to answer my question.

Your assumption from my scenario is right. I just wanted to know whether SQL 2005 support this scenario and if yes, I need a sample package and its configuration.

Look forward to hearing from you ASAP.

Regards,
Sassan

PS. I use BizTalk Server 2004 now and I want to change it using SSIS

|||

There are lots of SSIS samples provided with SQL 2005, take a look.

The task you are describing seems to fit exactly Import/Export Wizard goals - just run the wizard, follow it, and it will create the package you need.

If needed, you may then open this package in BI Dev Studio and modify it further.

|||

Dear Michael

As I am working with the BI Dev Studio and ran some of the Hands On Labs, I see that after I create the package, I must run it (Or schedule to run it regurarly) and then all the data wil be transformed by the rules I set.

I need something like trigger in order my package run by each record addition in source.

Therefore I need a sample for a scenario I explained.

Regards,

Sassan

|||

So, you mean you want the SSIS package to execute whenever a certain event occurs? Well, you can execute a SSIS package using a command line utility called dtexec.exe. It is up to you how you capture the events that you need trigger the call to dtexec.exe

-Jamie

|||

Dear Jamie,

You are right. I have two questions which needs help:

1- I have an application server which stores data via its Data Access Layer and My DB Server is not on the same server, Therefore is there another way except using triggers?

2- How can I call the package for transforming just the new record that I want to be added in the old system. I see that running package cause all data to be transformed. The only solution I see is that I set a flag in the source record after transformation and then in my package use a conditional block to just transform the records with the flag unset.

Look forward to hearing from you ASAP.

Regards,
Sassan

|||

sassankz wrote:

You are right. I have two questions which needs help:

1- I have an application server which stores data via its Data Access Layer and My DB Server is not on the same server, Therefore is there another way except using triggers?

Don't really understand what you're trying to do. When a record arrives in your "data access layer" (which I presume is a database), do you want to automatically put it into your DB server? If so....SSIS is NOT the tool that you should be using to do this. SQL Server has a number of methods of achieving this. Look at replication, log shipping or data mirroring.

sassankz wrote:

2- How can I call the package for transforming just the new record that I want to be added in the old system. I see that running package cause all data to be transformed. The only solution I see is that I set a flag in the source record after transformation and then in my package use a conditional block to just transform the records with the flag unset.

If you are going to continue using SSIS (which I don't think you should) then that sounds like a possible solution.

-Jamie

|||

Dear Jamie,

Let me explain the whole scenario. I am developing a Human Resource Management System for my client. Therefore I have my database design based on the customer requirements. The client has a payroll application which is working now and will continue to use it also. Therefore only HRM handles the Employee Introduction and I must feed the Payroll required data in order to work properly parallel with HRM.

Now there exists some data transformation from HRM to Payroll and also the data must be synchronized. It means that if I even can delete an employee from HRM, for sure I can do that it in payroll and must do that.

Now I want to have SSIS for this scenario. I want to develop some packages which handles the transformation and like you said some how data mirroring.

Please let me know whether you see SSIS useful for the above task or not.

Regards,
Sassan

|||

SSIS is inherently a tool for batch operations. If you want the changes between databases to be propogated immediately then you should be loking at some other tool in my opinion.

If the data has to be transformed in some way then perhaps you may want to look at BizTalk. I don't know much about BizTalk but this seems to be the sort of scenario for which it is designed.

-Jamie

|||

Dear Jamie,

Thanks for your recommendations. I decided to schedule package runs.

Is there a solution in SSIS in order to update destination source if the row exists, since it truncate the whole table or append data in its wizard? (I know I can write SQL Codes, I just want to know whether it has a ready configuration to support this.)

Regards,
Sassan

|||

Sassan,

SSIS is really not good in updates... Doing that in SQL is much better... So it might be a good idea to design something like an "update table" which is filled within the data flow where you "buffer" all the changes and commit that updates to the "real" table using a SQL update...

|||

Sassan,

just to add a little thing...

I just wrote something about the updates on the whislist maintained by Kirk in this forum... So I thought about how to implement that "update table"...

There might be (at least) to approaches, one is "clean" and one is "dirty", but might be a little bit more performant...

The "clean" way would be using a lookup to find out if a record already exists in the destination. If it does, then it's an update (redirect the record to the update table), if not it's new and you can send it to the destination.

The "dirty" way would be to send all records to the destination. If the write fails (because the primary key already exists) you redirect the error records to the update table. I didn't try this approach, yet. But it might work...

You might get into trouble if you can have a record more than once in a run. Then it might be a "new" and an "update" in the same batch. That might be interesting to find out...

Online Data Transform

Dear All,

I am new to SSIS, and just watched some of the webcasts and take a look at SSIS Hands on Lab on the MSDN site.

I want to handle an online data transformation scenario which has some data mappings between. I want the SSIS to add each new record in source table, insert in the destination tables online. If this is possible, please let me know where can I find a sample package and configurations.

Happy new year,
Sassan

What is an "online data transformation"?

What do you mean by "insert into the destination table online"?

Do you mean that you want each record in a source table to be inserted into a destination table? If so, what exactly are you having trouble achieving?

Please be more specific.

-Jamie

|||

Dear Jamie,

Thanks for taking time to answer my question.

Your assumption from my scenario is right. I just wanted to know whether SQL 2005 support this scenario and if yes, I need a sample package and its configuration.

Look forward to hearing from you ASAP.

Regards,
Sassan

PS. I use BizTalk Server 2004 now and I want to change it using SSIS

|||

There are lots of SSIS samples provided with SQL 2005, take a look.

The task you are describing seems to fit exactly Import/Export Wizard goals - just run the wizard, follow it, and it will create the package you need.

If needed, you may then open this package in BI Dev Studio and modify it further.

|||

Dear Michael

As I am working with the BI Dev Studio and ran some of the Hands On Labs, I see that after I create the package, I must run it (Or schedule to run it regurarly) and then all the data wil be transformed by the rules I set.

I need something like trigger in order my package run by each record addition in source.

Therefore I need a sample for a scenario I explained.

Regards,

Sassan

|||

So, you mean you want the SSIS package to execute whenever a certain event occurs? Well, you can execute a SSIS package using a command line utility called dtexec.exe. It is up to you how you capture the events that you need trigger the call to dtexec.exe

-Jamie

|||

Dear Jamie,

You are right. I have two questions which needs help:

1- I have an application server which stores data via its Data Access Layer and My DB Server is not on the same server, Therefore is there another way except using triggers?

2- How can I call the package for transforming just the new record that I want to be added in the old system. I see that running package cause all data to be transformed. The only solution I see is that I set a flag in the source record after transformation and then in my package use a conditional block to just transform the records with the flag unset.

Look forward to hearing from you ASAP.

Regards,
Sassan

|||

sassankz wrote:

You are right. I have two questions which needs help:

1- I have an application server which stores data via its Data Access Layer and My DB Server is not on the same server, Therefore is there another way except using triggers?

Don't really understand what you're trying to do. When a record arrives in your "data access layer" (which I presume is a database), do you want to automatically put it into your DB server? If so....SSIS is NOT the tool that you should be using to do this. SQL Server has a number of methods of achieving this. Look at replication, log shipping or data mirroring.

sassankz wrote:

2- How can I call the package for transforming just the new record that I want to be added in the old system. I see that running package cause all data to be transformed. The only solution I see is that I set a flag in the source record after transformation and then in my package use a conditional block to just transform the records with the flag unset.

If you are going to continue using SSIS (which I don't think you should) then that sounds like a possible solution.

-Jamie

|||

Dear Jamie,

Let me explain the whole scenario. I am developing a Human Resource Management System for my client. Therefore I have my database design based on the customer requirements. The client has a payroll application which is working now and will continue to use it also. Therefore only HRM handles the Employee Introduction and I must feed the Payroll required data in order to work properly parallel with HRM.

Now there exists some data transformation from HRM to Payroll and also the data must be synchronized. It means that if I even can delete an employee from HRM, for sure I can do that it in payroll and must do that.

Now I want to have SSIS for this scenario. I want to develop some packages which handles the transformation and like you said some how data mirroring.

Please let me know whether you see SSIS useful for the above task or not.

Regards,
Sassan

|||

SSIS is inherently a tool for batch operations. If you want the changes between databases to be propogated immediately then you should be loking at some other tool in my opinion.

If the data has to be transformed in some way then perhaps you may want to look at BizTalk. I don't know much about BizTalk but this seems to be the sort of scenario for which it is designed.

-Jamie

|||

Dear Jamie,

Thanks for your recommendations. I decided to schedule package runs.

Is there a solution in SSIS in order to update destination source if the row exists, since it truncate the whole table or append data in its wizard? (I know I can write SQL Codes, I just want to know whether it has a ready configuration to support this.)

Regards,
Sassan

|||

Sassan,

SSIS is really not good in updates... Doing that in SQL is much better... So it might be a good idea to design something like an "update table" which is filled within the data flow where you "buffer" all the changes and commit that updates to the "real" table using a SQL update...

|||

Sassan,

just to add a little thing...

I just wrote something about the updates on the whislist maintained by Kirk in this forum... So I thought about how to implement that "update table"...

There might be (at least) to approaches, one is "clean" and one is "dirty", but might be a little bit more performant...

The "clean" way would be using a lookup to find out if a record already exists in the destination. If it does, then it's an update (redirect the record to the update table), if not it's new and you can send it to the destination.

The "dirty" way would be to send all records to the destination. If the write fails (because the primary key already exists) you redirect the error records to the update table. I didn't try this approach, yet. But it might work...

You might get into trouble if you can have a record more than once in a run. Then it might be a "new" and an "update" in the same batch. That might be interesting to find out...