SQL SQLExpress: create

Showing posts with label create. Show all posts

Sunday, March 25, 2012

@@ERROR & CREATE TABLE

Hi

I've been looking at scripting some create tables, but want to know if the table created successfully. I've been doing the following:

DECLARE @.ERRCODE INT

CREATE TABLE x
SET @.ERRCODE = @.@.ERROR

This works ok, if there is no erroor. However if I run this again, then obviously I get the error "This object already exists" and the script stops executing.

Is there a way that I can capture the error using @.@.ERROR and still let the script run ?

Thanks in advance

MicksterWell, you shouldn't. "On Error Resume Next" is not present in T-SQL. What you should do is perform a validation for presence of object before attempting to create it.if objectproperty(object_id('dbo.x'), 'isusertable')=1 drop table dbo.x

create table dbo.x (f1 int, ...)|||Thanks, but I already understand that I should check for the existance of the object - as stated in my last mail. I want to check the table is being created correctly incase of other events, like permissions, file full, etc.

@@ identity insert

Is it possible to create a trigger that inserts the @.@.identity (primary key) from Table1 into a field in Table2?
If so, how? Thank you.
-D-yes it's possible. Create a trigger on Table1 for insert and execute an insert-statement using @.@.identity. Are you stuck?

@!#$%^&*( SSL/TLS

OK, I have a fresh installation. I installed SSL prior to SQL Server 2005 and let the installer create the default configuration. SSL is working, cert auth is in place. Started up Report Manager and it is working. Tried out Report Builder and it is working just fine. I go to run some reports, I get the parameters (some are dynamic) working fine. When I go to render the report I get the almighty dreaded

The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel. The remote certificate is invalid according to the validation procedure.

Anyone have any ideas what is going on?

Report Server Virtual directory is set to 3 - All Soap APIs.

Just to recap, Report Manager, OK. Report Builder, OK, Params OK, Report Data, NOT OK.

Just found out, it isn't asking for credentials. Looks like it is trying to use ASPNET. Now I am lost.

Hi Ron. I'm having the exact same issue as you, and I'm also completely lost. I even can see the reports from ReportServer (https://myserver:mysslport/ReportServer), but not from Report Manager. Have you find any clue on this?

Thanks,

Julio

@!#$%^&*( SSL/TLS

The underlying connection was closed: Could not establish trust relationship for the SSL/TLS secure channel. The remote certificate is invalid according to the validation procedure.

Anyone have any ideas what is going on?

Report Server Virtual directory is set to 3 - All Soap APIs.

Just to recap, Report Manager, OK. Report Builder, OK, Params OK, Report Data, NOT OK.

Just found out, it isn't asking for credentials. Looks like it is trying to use ASPNET. Now I am lost.

Thanks,

Julio

|||I was also having this problem and found a solution here:
http://ezra.auerbach.co.il/?postid=7

You need to add the root certifacate of the one being used by the sharepoint and reporting services IIS sites to the computer's Trusted Root Authorities.sql

Monday, March 19, 2012

.sql scripts NEBIE ALERT

I am new to SQL 2005
I want to know how to run a .sql file someone gave me to create a new
database. Where is the command or gui to do this.
Thanks
NEBIEHi,
From SQL Server 2005 program groups; open SQL Server Management Studio..
Connect to SQL Server..Open a Query window...
Oprn the .SQL file and press execute or press F5 to execute the SQL File.
Otherwise from command prompt using SQLCMD you could use -ic:\file.SQL to
execute the SQL file
Thanks
Hari
"frogman7" <frogman7@.googlemail.com> wrote in message
news:1160363466.452205.98690@.b28g2000cwb.googlegroups.com...
>I am new to SQL 2005
> I want to know how to run a .sql file someone gave me to create a new
> database. Where is the command or gui to do this.
>
> Thanks
> NEBIE
>

.sql scripts NEBIE ALERT

I am new to SQL 2005
I want to know how to run a .sql file someone gave me to create a new
database. Where is the command or gui to do this.
Thanks
NEBIE
Hi,
From SQL Server 2005 program groups; open SQL Server Management Studio..
Connect to SQL Server..Open a Query window...
Oprn the .SQL file and press execute or press F5 to execute the SQL File.
Otherwise from command prompt using SQLCMD you could use -ic:\file.SQL to
execute the SQL file
Thanks
Hari
"frogman7" <frogman7@.googlemail.com> wrote in message
news:1160363466.452205.98690@.b28g2000cwb.googlegro ups.com...
>I am new to SQL 2005
> I want to know how to run a .sql file someone gave me to create a new
> database. Where is the command or gui to do this.
>
> Thanks
> NEBIE
>

Thursday, March 8, 2012

.Net Framework Data Provider for SQL Server

If I try to create a database reference in VS 2008 B2 I get an error with "This server version is not supported. Only serves up to Microsoft SQL Server 2005 are supported."

Is this a problem with the SqlClient provider? Or is this a problem with VS integration with Katmai?

? VS Integration with Katmai. The designers don't support this yet. There will be a fix for this in a later release. Cheers, Bob Beauchemin SQLskills ""Scott Sharpe"@.discussions.microsoft.com" <"=?UTF-8?B?U2NvdHQgU2hhcnBl?="@.discussions.microsoft.com> wrote in message news:9c9cb3d8-3d35-448c-858c-644b593f2e16@.discussions.microsoft.com... If I try to create a database reference in VS 2008 B2 I get an error with "This server version is not supported. Only serves up to Microsoft SQL Server 2005 are supported." Is this a problem with the SqlClient provider? Or is this a problem with VS integration with Katmai?

Tuesday, March 6, 2012

.NET CLR stored procedure capabilities

I've want to create a typed DataSet and use it inside a .NET CLR stored
procedure. But the option to add a typed DataSet to the project
doesn't exist. Am I missing something? Or are .NET CLR stored
procedures not all they're hyped up to be?
Todd
toddpiltingsrud@.altoconsulting.com wrote in
news:1132776964.626979.93470@.g47g2000cwa.googlegro ups.com:

> I've want to create a typed DataSet and use it inside a .NET CLR
> stored procedure. But the option to add a typed DataSet to the
> project doesn't exist. Am I missing something? Or are .NET CLR
> stored procedures not all they're hyped up to be?
Course you can create a typed DataSet and use it in SQLCLR. Younhave to
create it manually though instead of drag 'n drop.
Just out of curiousity; why do you want a typed dataset inside SQL
Server?
Niels
**************************************************
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
**************************************************

.NET CLR stored procedure capabilities

I've want to create a typed DataSet and use it inside a .NET CLR stored
procedure. But the option to add a typed DataSet to the project
doesn't exist. Am I missing something? Or are .NET CLR stored
procedures not all they're hyped up to be?
Toddtoddpiltingsrud@.altoconsulting.com wrote in
news:1132776964.626979.93470@.g47g2000cwa.googlegroups.com:

> I've want to create a typed DataSet and use it inside a .NET CLR
> stored procedure. But the option to add a typed DataSet to the
> project doesn't exist. Am I missing something? Or are .NET CLR
> stored procedures not all they're hyped up to be?
Course you can create a typed DataSet and use it in SQLCLR. Younhave to
create it manually though instead of drag 'n drop.
Just out of curiousity; why do you want a typed dataset inside SQL
Server?
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********

.NET CLR stored procedure capabilities

I've want to create a typed DataSet and use it inside a .NET CLR stored
procedure. But the option to add a typed DataSet to the project
doesn't exist. Am I missing something? Or are .NET CLR stored
procedures not all they're hyped up to be?
Toddtoddpiltingsrud@.altoconsulting.com wrote in
news:1132776964.626979.93470@.g47g2000cwa.googlegroups.com:
> I've want to create a typed DataSet and use it inside a .NET CLR
> stored procedure. But the option to add a typed DataSet to the
> project doesn't exist. Am I missing something? Or are .NET CLR
> stored procedures not all they're hyped up to be?
Course you can create a typed DataSet and use it in SQLCLR. Younhave to
create it manually though instead of drag 'n drop.
Just out of curiousity; why do you want a typed dataset inside SQL
Server?
Niels
**************************************************
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
**************************************************

.net and Sql Express

Hi all, I have written a simple .net class to call sql functions like create subscription and sync replication. I am using merge replication. My Question is this:-- my class calls Microsoft.SqlServer.Replication.dll. On a machine that has SQL Express loaded it works but on a machine that does not have sql express it fails. I have made a setup project and deployed my "service". I added all references into the project and all works well on a machine that has sql express. We are running a network with 1 machine that has sql express and other machines that plug into this sql express. Is there a sql express runtime setup or do I have to load sql express on each machine. OR Am I deploying my class wrong. my setup project has the Microsoft.SqlServer.Replication.dll under detected dependencies but not any where else. Do I need to add it as reference. Tried Under Properties of Microsoft.SqlServer.Replication.dll in setup project Register : vsdraCOMRelativePath / vsdraCOM Exclude : False All the dlls I use are deployed to the install directory but gives Error "could not load dll or assembly SqlServer.Replication" Cheers

I havn't use replication with express yet, but yu might want to install the sqlnative client onto the client machines. You can get this download from the microsoft downloads site.

|||

This particular dll seems to be part of the sdk kit of sql server 2005.

directory 90\SDK\Assemblies

The sql native client did not help. I installed it and still no avail.

How would one deploy the sdk kit files

Cheers

|||

Hi Robert,

I'm not clear on why you would want to run your service on a computer that doesn't have SQL Express installed, could you explian? Replication is about communication between two SQL Servers, without a SQL Server, what exactly are you synchronizing?

Thanks for the further detail.

Regards,

Mike

|||

hi,

Mike already pointed out there's no real sens in doing what you are trying to do...

are you perhaps trying something already built-in, like Query Notifications? http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/querynotification.asp, http://msdn2.microsoft.com/en-us/library/t9x04ed2.aspx

regards

|||

I am trying to have one machine that replicates to a internet based server. This one machine is on our local lan. Other users can log into our program which then connects to the local "lan" sql express. Once all work is done, a "On Demand " sync is done. I wrote the dll to perform this on demand sync.I don't want sql express installed on all 10 machines but rather on one lan machine that everyone can use.

Problem is that the dll needs the sql replication dll to function. A machine without sql express installed cannot ask for the sync via the dll.It generates an error
I have tried to include the sql replication dll in my install but it still does not want to work.I think I am registering it wrong.If I install sql express then the dll works.
(I am using VS 2005 .net setup to deploy the project dll.). This is something I am trying to avoid.
At the moment the way it has to work is that everybody does their changes and then requests a sync update from the user, who has the sql express installed, to update the replication.Quite tiresome.

Regards

Robert

|||Robert you answer lies in the application distribution. So this thread should probably be moved to Coding forum.

But anyway. I think your answer lies in remoting, using a multi-tier application.

We have a similar setup where sql exp is used as a server for two or more client applications. You will need to create Data class that run on the machine where SQL Express is installed and then use .Net Remoting from your client machines to invoke the Data class. This centralised data class will be the one that instantiates the SQL replication dll and thus it will call it from it's local machine (The Data Server). For this purpose the data server is just the machine where SQL exp is installed.

There fore the clients need know nothing about SQL Replication only that they create the remoting object and instruct the data class on the server to do the work. Hence they don't need SQL Express installed on every machine.

If you are not familiar with remoting then you will need to do quite a bit of reading up as there can be pitfalls and it may require you application to be restructured completely.

Hope this points you to your answer

Cheers
Rab|||

Just a few question.

Why does the dll not work on its own.
Can I not simply distribute the dll.
I suppose the dll does not contain all the libary files from SQL to invoke the calls made to sql express.
Thanks anyway for the answer.

Cheers

Robert

.net and Sql Express

I havn't use replication with express yet, but yu might want to install the sqlnative client onto the client machines. You can get this download from the microsoft downloads site.

|||

This particular dll seems to be part of the sdk kit of sql server 2005.

directory 90\SDK\Assemblies

The sql native client did not help. I installed it and still no avail.

How would one deploy the sdk kit files

Cheers

|||

Hi Robert,

Thanks for the further detail.

Regards,

Mike

|||

hi,

Mike already pointed out there's no real sens in doing what you are trying to do...

regards

|||

Regards

Robert

|||Robert you answer lies in the application distribution. So this thread should probably be moved to Coding forum.

But anyway. I think your answer lies in remoting, using a multi-tier application.

If you are not familiar with remoting then you will need to do quite a bit of reading up as there can be pitfalls and it may require you application to be restructured completely.

Hope this points you to your answer

Cheers
Rab|||

Just a few question.

Cheers

Robert

.net and Sql Express

I havn't use replication with express yet, but yu might want to install the sqlnative client onto the client machines. You can get this download from the microsoft downloads site.

|||

This particular dll seems to be part of the sdk kit of sql server 2005.

directory 90\SDK\Assemblies

The sql native client did not help. I installed it and still no avail.

How would one deploy the sdk kit files

Cheers

|||

Hi Robert,

Thanks for the further detail.

Regards,

Mike

|||

hi,

Mike already pointed out there's no real sens in doing what you are trying to do...

regards

|||

Regards

Robert

|||Robert you answer lies in the application distribution. So this thread should probably be moved to Coding forum.

But anyway. I think your answer lies in remoting, using a multi-tier application.

If you are not familiar with remoting then you will need to do quite a bit of reading up as there can be pitfalls and it may require you application to be restructured completely.

Hope this points you to your answer

Cheers
Rab
|||

Just a few question.

Cheers

Robert

Saturday, February 25, 2012

.Ldf and transactions

Can I create a database without an .ldf file
even if I use updates in transactionsAs far as I know, you cannot. DB has to have a transaction file. You can keep the size to a minimum if you set the recovery mode of the db to simple (if you don't care for recovery of the data).
Originally posted by Karolyn
Can I create a database without an .ldf file
even if I use updates in transactions|||that's what I tought,
thks|||The Holy book says :

Microsoft SQL Server 2000 maps a database using a set of operating-system files. All data and objects in the database, such as tables, stored procedures, triggers, and views, are stored within these operating-system files:

Primary
This file contains the startup information for the database and is used to store data. Every database has one primary data file.

Secondary
These files hold all of the data that does not fit in the primary data file. If the primary file can hold all of the data in the database, databases do not need to have secondary data files. Some databases may be large enough to need multiple secondary data files or to use secondary files on separate disk drives to spread data across multiple disks.

Transaction Log
These files hold the log information used to recover the database. There must be at least one log file for each database.|||There are certain transactions that ss2k has to manage like ddl commands and checkpoints. So you will always have a log file.|||If you are curious about what is kept in the log file you can use the dbcc log command.|||I've read in the Online Help
that exists a Virtual Transaction Log in SQL Server|||the transaction log file is made up of many virtual logs - SQL uses these to manage the usage, reusage, growth and shrinking of the transaction log file.|||thx all|||Originally posted by rnealejr
If you are curious about what is kept in the log file you can use the dbcc log command.

HI,

I AM REWIEVING THE FORUM SEEKING A HINT FOR OUR PROBLEM. OUR LOG FILE HAS DRAMATICLY INCREASED WITHIN FEW HOURS... IT HAS HAPPEND TWICE SO FAR.

I AM CURIOUS HOW CAN I REVIEW THE LOG FILE CONTENTS SO THAT TO LOCALISE THE SOURCE OF THAT SUDDEN GROWTH.

IS IT A GOOD WAY TO TRY DBCC? I TRIED TO FIND A PROPER COMMAND ON THE LIST IN BOOKS ONLINE, BUT I AM NOT SURE WHICH ONE I SHOULD USE.

THANKS IN ADVANCE FOR ANY HELP
JACK|||There is no command that I'm aware of that will tell you the SQL of the transactions held in the trans log. There are a few 3rd party tools about that claim to do it - although I have never used them.

There is a dbcc loginfo command which will tell which parts of the log file are active and which are not, although no more detail than that I'm afraid.

Hope this helps|||You can use

dbcc log ('dbname',type) where type is a number between 0-4 to get a general idea. However this will not show you the actual queries that were executed.|||THANKS!

Originally posted by dbabren
There is no command that I'm aware of that will tell you the SQL of the transactions held in the trans log. There are a few 3rd party tools about that claim to do it - although I have never used them.

There is a dbcc loginfo command which will tell which parts of the log file are active and which are not, although no more detail than that I'm afraid.

Hope this helps|||Thanks!

Do You know maybe where I can read more about the logic of each column extracted?

Thanks for futher hints in advance.

Jack

Originally posted by Enigma
You can use

dbcc log ('dbname',type) where type is a number between 0-4 to get a general idea. However this will not show you the actual queries that were executed.|||Do You remember maybe any of those companies www pages?

Thanks a lot in advance.
Jack

Originally posted by dbabren
There is no command that I'm aware of that will tell you the SQL of the transactions held in the trans log. There are a few 3rd party tools about that claim to do it - although I have never used them.

There is a dbcc loginfo command which will tell which parts of the log file are active and which are not, although no more detail than that I'm afraid.

Hope this helps|||Search for Lumigent's Log Explorer at

www.lumigent.com

You can download a trial version of the product.

Originally posted by JackKaton
Do You remember maybe any of those companies www pages?

Thanks a lot in advance.
Jack|||We have recently purchased Diagnostic Manager from NetIq (risking like sounding like an advert!) - thay have a trans log reader in that tool - www.netiq.com. - you get a free 30 day trial!

Altern look on a site like http://www.sql-server-performance.com/ and there should be some links to 3rd party vendors|||Many Thx to all of YOU!

Originally posted by dbabren
We have recently purchased Diagnostic Manager from NetIq (risking like sounding like an advert!) - thay have a trans log reader in that tool - www.netiq.com. - you get a free 30 day trial!

Altern look on a site like http://www.sql-server-performance.com/ and there should be some links to 3rd party vendors|||When a log file suddenly becomes huge, it's usually caused by a transaction that hasn't been committed. Other trannies start piling up behind it too. Always a programmer error.|||It's been a while since I was deep into admin, but isn't there a way to store the database log totally in cache memory? The option was there for speed, though it decreased recoverability.

Then there would be no file on disk.

blindman|||Interesting, enlighten me , oh Grand Poobah blindman! :)|||I must be suffering from Triptophan withdrawal after all that Thanksgiving turkey and gravy.

I was thinking about the "tempdb in ram" option.

blindman

.fmt

Friday, February 24, 2012

.fmt

Hi,
How is it possible to create a .fmt file with the following informations if
they are required?
server1
database1
table1
Thanksbcp pubs..authors2 in
c:\new_auth.dat -fc:\authors.fmt -Sservername -Usa -Ppassword
Read Using Format File article in the bol"farshad"
<farshad@.discussions.microsoft.com> wrote in message
news:A9363EE5-807D-4BCB-9C2D-BC7ABCEDFB10@.microsoft.com...
> Hi,
> How is it possible to create a .fmt file with the following informations
> if
> they are required?
> server1
> database1
> table1
> Thanks|||Hi,
Does this mean I have to have a .dat file? How do I create it?
P.S. I run the bcp to insert using bulk insert.
Thanks
"Uri Dimant" wrote:
> bcp pubs..authors2 in
> c:\new_auth.dat -fc:\authors.fmt -Sservername -Usa -Ppassword
> Read Using Format File article in the bol"farshad"
> <farshad@.discussions.microsoft.com> wrote in message
> news:A9363EE5-807D-4BCB-9C2D-BC7ABCEDFB10@.microsoft.com...
> > Hi,
> > How is it possible to create a .fmt file with the following informations
> > if
> > they are required?
> > server1
> > database1
> > table1
> >
> > Thanks
>
>|||Hi
On SQL 2000 BCP has a format option that will create a format for you, see
Books online for more.
John
"farshad" <farshad@.discussions.microsoft.com> wrote in message
news:31F50883-0BF3-49B9-BB17-A45EB192F489@.microsoft.com...
> Hi,
> Does this mean I have to have a .dat file? How do I create it?
> P.S. I run the bcp to insert using bulk insert.
> Thanks
> "Uri Dimant" wrote:
>> bcp pubs..authors2 in
>> c:\new_auth.dat -fc:\authors.fmt -Sservername -Usa -Ppassword
>> Read Using Format File article in the bol"farshad"
>> <farshad@.discussions.microsoft.com> wrote in message
>> news:A9363EE5-807D-4BCB-9C2D-BC7ABCEDFB10@.microsoft.com...
>> > Hi,
>> > How is it possible to create a .fmt file with the following
>> > informations
>> > if
>> > they are required?
>> > server1
>> > database1
>> > table1
>> >
>> > Thanks
>>|||I would have if I could find it in books online.
Thanks
"John Bell" wrote:
> Hi
> On SQL 2000 BCP has a format option that will create a format for you, see
> Books online for more.
> John
> "farshad" <farshad@.discussions.microsoft.com> wrote in message
> news:31F50883-0BF3-49B9-BB17-A45EB192F489@.microsoft.com...
> > Hi,
> > Does this mean I have to have a .dat file? How do I create it?
> > P.S. I run the bcp to insert using bulk insert.
> > Thanks
> >
> > "Uri Dimant" wrote:
> >
> >> bcp pubs..authors2 in
> >> c:\new_auth.dat -fc:\authors.fmt -Sservername -Usa -Ppassword
> >> Read Using Format File article in the bol"farshad"
> >> <farshad@.discussions.microsoft.com> wrote in message
> >> news:A9363EE5-807D-4BCB-9C2D-BC7ABCEDFB10@.microsoft.com...
> >> > Hi,
> >> > How is it possible to create a .fmt file with the following
> >> > informations
> >> > if
> >> > they are required?
> >> > server1
> >> > database1
> >> > table1
> >> >
> >> > Thanks
> >>
> >>
> >>
>
>|||Hi
Try the online copy:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp_bcp_61et.asp
John
farshad wrote:
> I would have if I could find it in books online.
> Thanks
> "John Bell" wrote:
> > Hi
> >
> > On SQL 2000 BCP has a format option that will create a format for you, see
> > Books online for more.
> >
> > John
> > "farshad" <farshad@.discussions.microsoft.com> wrote in message
> > news:31F50883-0BF3-49B9-BB17-A45EB192F489@.microsoft.com...
> > > Hi,
> > > Does this mean I have to have a .dat file? How do I create it?
> > > P.S. I run the bcp to insert using bulk insert.
> > > Thanks
> > >
> > > "Uri Dimant" wrote:
> > >
> > >> bcp pubs..authors2 in
> > >> c:\new_auth.dat -fc:\authors.fmt -Sservername -Usa -Ppassword
> > >> Read Using Format File article in the bol"farshad"
> > >> <farshad@.discussions.microsoft.com> wrote in message
> > >> news:A9363EE5-807D-4BCB-9C2D-BC7ABCEDFB10@.microsoft.com...
> > >> > Hi,
> > >> > How is it possible to create a .fmt file with the following
> > >> > informations
> > >> > if
> > >> > they are required?
> > >> > server1
> > >> > database1
> > >> > table1
> > >> >
> > >> > Thanks
> > >>
> > >>
> > >>
> >
> >
> >|||farshad wrote:
> I would have if I could find it in books online.
> Thanks
>
What is it you can't find? Try to look for bcp in Books On Line. That
will give you a number of "hits" - and also about bcp.fmt.
Regards
Steen

.fmt

.dtsx package from Informix using ODBC

Help!

I'm trying to create a simple .dtsx package that imports data to SQL server 2005 from an informix 7.3 db using an ADO.net ODBC connection. I am first creating the groundwork for the dtsx package in SQL server using the wizard, and then editing the file later in visual studio.

My data source SQL in the dataflow task is simple and it works great until I hit a locked record on the Informix database.

select <coulmns>

from <table>

Where <condition>

The work around syntax for the locked row on the informix DB should be:

set isolation to dirty read

select <coulmns>

from <table>

where <condition>

This syntax will return the data correctly using a non-microsoft SQL editor, however it will will not parse corectly within visual studio. Interestingly enough, in visual studio I can parse the islolation and the select indenpendantly, just not in the same statement.

Has anyone come across this before? Any ideas on what I can do to resolve my problem?

Thanks in advance!

I ended up having the dtsx package run a sql statement before the import.....using an ODBC link in the .dtsx package that had priviledges enough to drop and create a temp table on the Informix side.

I imported the data into the temp table on informix using "set isolation level to dirty read" on the informix side.

Then a standard dtsx package to import from the temp table where there weren't any lock issues.

It works....I'm glad it doesnt have to run often.

|||

I experience exactly the same Problem:

Try to get data from an Informix-Server to a MS SQL2k5-Server using an ODBC-Driver (IBM Informix ODBC 2.90.0000 TC3).

My problem is that I have "readonly"-Permission to the Informix-Server, so the workaround with the temporary table isn′t suitable for me. Is there a "beautiful" solution? I set the "set isolation to dirty read"-command in a seperate SQL-Task, then the data flow task where I try to access the Informix server. It seems that the connection opened for setting the isolation level is closed when the task ends and the data flow task opens a new one.

Please help me, I′m quite desperate now...

|||Ok, so now I figured out that there′s an option in the connection manager properties "RetainSameConnection", which I set "TRUE". Unfortunately I still get the same error. The connection to the Informix-DB fails if there are other users logged on or I don′t get the permission to read data. I also increased the "MaxConcurrentExecutables" Value to 3 due to the fact that I got 3 Tasks which have to use the connection. Still the execution fails.

Any ideas?

BTW, here′s my error output: Parts of it are in german but perhaps it helps:
_
SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Datenflusstask, DTS.Pipeline: Die Phase 'überprüfung' beginnt.
Information: 0x4004300A at Datenflusstask, DTS.Pipeline: Die Phase 'überprüfung' beginnt.
Information: 0x40043006 at Datenflusstask, DTS.Pipeline: Die Phase 'Ausführung vorbereiten' beginnt.
Information: 0x40043007 at Datenflusstask, DTS.Pipeline: Die Phase 'Vor der Ausführung' beginnt.
Information: 0x402090DC at Datenflusstask, Flatfileziel [10279]: Die Verarbeitung der Datei 'C:\temp\flatfile_KDstat_Kunden.csv wurde gestartet.
Information: 0x4004300C at Datenflusstask, DTS.Pipeline: Die Phase 'Ausführung' beginnt.
Error: 0xC02090F5 at Datenflusstask, DataReader-Quelle [6976]: 'Komponente 'DataReader-Quelle' (6976)' konnte die Daten nicht verarbeiten.
Error: 0xC0047038 at Datenflusstask, DTS.Pipeline: Die PrimeOutput-Methode in 'Komponente 'DataReader-Quelle' (6976)' hat den Fehlercode 0xC02090F5 zurückgegeben. Die Komponente gab einen Fehlercode zurück, als das Pipelinemodul 'PrimeOutput()' aufgerufen hat. Die Bedeutung des Fehlercodes wird von der Komponente definiert. Der Fehler ist jedoch schwerwiegend, und die Ausführung der Pipeline wurde beendet.
Error: 0xC0047021 at Datenflusstask, DTS.Pipeline: Der Thread 'SourceThread0' wurde mit dem Fehlercode 0xC0047038 beendet.
Error: 0xC0047039 at Datenflusstask, DTS.Pipeline: Der Thread 'WorkThread0' hat ein Signal zum Herunterfahren erhalten und wird beendet. Der Benutzer hat das Herunterfahren angefordert, oder ein Fehler in einem anderen Thread hat dazu geführt, dass die Pipeline heruntergefahren wird.
Error: 0xC0047021 at Datenflusstask, DTS.Pipeline: Der Thread 'WorkThread0' wurde mit dem Fehlercode 0xC0047039 beendet.
Information: 0x40043008 at Datenflusstask, DTS.Pipeline: Die Phase 'Nach der Ausführung' beginnt.
Information: 0x402090DD at Datenflusstask, Flatfileziel [10279]: Die Verarbeitung der Datei 'C:\temp\flatfile_KDstat_Kunden.csv wurde beendet.
Information: 0x40043009 at Datenflusstask, DTS.Pipeline: Die Phase 'Cleanup' beginnt.
Information: 0x4004300B at Datenflusstask, DTS.Pipeline: 'Komponente 'Flatfileziel' (10279)' schrieb 0 Zeilen.
Task failed: Datenflusstask
Warning: 0x80019002 at Package: Die Execution-Methode wurde erfolgreich ausgeführt, aber die Anzahl von ausgel?sten Fehlern (5) hat den maximal zul?ssigen Wert erreicht (1). Deshalb tritt ein Fehler auf. Dieses Problem tritt auf, wenn die Anzahl von Fehlern den in 'MaximumErrorCount' angegebenen Wert erreicht. ?ndern Sie den Wert für 'MaximumErrorCount', oder beheben Sie die Fehler.
SSIS package "Package.dtsx" finished: Failure.
__
|||I got a workaround now: I built an "Execute DTS2000 task" in my SSIS-package. There the isolation and the select-statement are executed correctly. Of course this is ***, because my primary goal is to replace our existing DTS-packages on the SQL2000 Server with SSIS-packages on SQL2k5 :-/
We set up a Microsoft call, I′m very curious, what they will figure out.
|||Still we haven′t found a proper solution. If anyone has an idea of how to solve this, please respond. Any help is greatly appreciated.|||

We finally found a possibility to import data from Informix via ODBC. It′s not straight forward, but it works.

1. Set up the Informix Source as System DSN Source

2. Create a linked Server on the Destination Server in the Management Studio, giving it the name of the ODBC-Source

3. Set up an OLE-DB Data Source in BIDS with your Destination Server as Target, giving it the following query:

SELECT <column1,column2...>

FROM openquery

(<lnksrv_name>,

'{SET ISOLATION TO DIRTY READ} SELECT src1 AS column1, src2 AS column2... FROM... WHERE...')

The important thing is the Isolation in the brackets. AFAIK it′s the only way to set a propper isolation level.

.dtsx package from Informix using ODBC

Help!

My data source SQL in the dataflow task is simple and it works great until I hit a locked record on the Informix database.

select <coulmns>

from <table>

Where <condition>

The work around syntax for the locked row on the informix DB should be:

set isolation to dirty read

select <coulmns>

from <table>

where <condition>

Has anyone come across this before? Any ideas on what I can do to resolve my problem?

Thanks in advance!

I ended up having the dtsx package run a sql statement before the import.....using an ODBC link in the .dtsx package that had priviledges enough to drop and create a temp table on the Informix side.

I imported the data into the temp table on informix using "set isolation level to dirty read" on the informix side.

Then a standard dtsx package to import from the temp table where there weren't any lock issues.

It works....I'm glad it doesnt have to run often.

|||

I experience exactly the same Problem:

Try to get data from an Informix-Server to a MS SQL2k5-Server using an ODBC-Driver (IBM Informix ODBC 2.90.0000 TC3).

Please help me, I′m quite desperate now...

Any ideas?

BTW, here′s my error output: Parts of it are in german but perhaps it helps:
_
SSIS package "Package.dtsx" starting.
Information: 0x4004300A at Datenflusstask, DTS.Pipeline: Die Phase 'überprüfung' beginnt.
Information: 0x4004300A at Datenflusstask, DTS.Pipeline: Die Phase 'überprüfung' beginnt.
Information: 0x40043006 at Datenflusstask, DTS.Pipeline: Die Phase 'Ausführung vorbereiten' beginnt.
Information: 0x40043007 at Datenflusstask, DTS.Pipeline: Die Phase 'Vor der Ausführung' beginnt.
Information: 0x402090DC at Datenflusstask, Flatfileziel [10279]: Die Verarbeitung der Datei 'C:\temp\flatfile_KDstat_Kunden.csv wurde gestartet.
Information: 0x4004300C at Datenflusstask, DTS.Pipeline: Die Phase 'Ausführung' beginnt.
Error: 0xC02090F5 at Datenflusstask, DataReader-Quelle [6976]: 'Komponente 'DataReader-Quelle' (6976)' konnte die Daten nicht verarbeiten.
Error: 0xC0047038 at Datenflusstask, DTS.Pipeline: Die PrimeOutput-Methode in 'Komponente 'DataReader-Quelle' (6976)' hat den Fehlercode 0xC02090F5 zurückgegeben. Die Komponente gab einen Fehlercode zurück, als das Pipelinemodul 'PrimeOutput()' aufgerufen hat. Die Bedeutung des Fehlercodes wird von der Komponente definiert. Der Fehler ist jedoch schwerwiegend, und die Ausführung der Pipeline wurde beendet.
Error: 0xC0047021 at Datenflusstask, DTS.Pipeline: Der Thread 'SourceThread0' wurde mit dem Fehlercode 0xC0047038 beendet.
Error: 0xC0047039 at Datenflusstask, DTS.Pipeline: Der Thread 'WorkThread0' hat ein Signal zum Herunterfahren erhalten und wird beendet. Der Benutzer hat das Herunterfahren angefordert, oder ein Fehler in einem anderen Thread hat dazu geführt, dass die Pipeline heruntergefahren wird.
Error: 0xC0047021 at Datenflusstask, DTS.Pipeline: Der Thread 'WorkThread0' wurde mit dem Fehlercode 0xC0047039 beendet.
Information: 0x40043008 at Datenflusstask, DTS.Pipeline: Die Phase 'Nach der Ausführung' beginnt.
Information: 0x402090DD at Datenflusstask, Flatfileziel [10279]: Die Verarbeitung der Datei 'C:\temp\flatfile_KDstat_Kunden.csv wurde beendet.
Information: 0x40043009 at Datenflusstask, DTS.Pipeline: Die Phase 'Cleanup' beginnt.
Information: 0x4004300B at Datenflusstask, DTS.Pipeline: 'Komponente 'Flatfileziel' (10279)' schrieb 0 Zeilen.
Task failed: Datenflusstask
Warning: 0x80019002 at Package: Die Execution-Methode wurde erfolgreich ausgeführt, aber die Anzahl von ausgel?sten Fehlern (5) hat den maximal zul?ssigen Wert erreicht (1). Deshalb tritt ein Fehler auf. Dieses Problem tritt auf, wenn die Anzahl von Fehlern den in 'MaximumErrorCount' angegebenen Wert erreicht. ?ndern Sie den Wert für 'MaximumErrorCount', oder beheben Sie die Fehler.
SSIS package "Package.dtsx" finished: Failure.
__
|||I got a workaround now: I built an "Execute DTS2000 task" in my SSIS-package. There the isolation and the select-statement are executed correctly. Of course this is ***, because my primary goal is to replace our existing DTS-packages on the SQL2000 Server with SSIS-packages on SQL2k5 :-/
We set up a Microsoft call, I′m very curious, what they will figure out.|||Still we haven′t found a proper solution. If anyone has an idea of how to solve this, please respond. Any help is greatly appreciated.|||

We finally found a possibility to import data from Informix via ODBC. It′s not straight forward, but it works.

1. Set up the Informix Source as System DSN Source

2. Create a linked Server on the Destination Server in the Management Studio, giving it the name of the ODBC-Source

3. Set up an OLE-DB Data Source in BIDS with your Destination Server as Target, giving it the following query:

SELECT <column1,column2...>

FROM openquery

(<lnksrv_name>,

'{SET ISOLATION TO DIRTY READ} SELECT src1 AS column1, src2 AS column2... FROM... WHERE...')

The important thing is the Isolation in the brackets. AFAIK it′s the only way to set a propper isolation level.

Sunday, March 25, 2012

@@ERROR & CREATE TABLE

@@ identity insert

@!#$%^&*( SSL/TLS

@!#$%^&*( SSL/TLS

Monday, March 19, 2012

.sql scripts NEBIE ALERT

.sql scripts NEBIE ALERT

.sql scripts NEBIE ALERT

Thursday, March 8, 2012

.Net Framework Data Provider for SQL Server

Tuesday, March 6, 2012

.NET CLR stored procedure capabilities

.NET CLR stored procedure capabilities

.NET CLR stored procedure capabilities

.net and Sql Express

.net and Sql Express

.net and Sql Express

Saturday, February 25, 2012

.Ldf and transactions

.fmt

Friday, February 24, 2012

.fmt

.fmt

.dtsx package from Informix using ODBC

.dtsx package from Informix using ODBC

SQL SQLExpress

Blog Archive

About Me