Delete connection file

I've inherited a procedure that performs a few transforms and writes to flat files in a working directory using a connection in connection manager.

After the manipulation, the system cleans up by deleting the working files. Works fine in development, but from the command line gives:

The process cannot access the file <filename> because it is being used by another process.".

I suspect this is because connection manager still has the file open.

Any ideas?

Thanks

Guy

Yes, you are right.try closing the connection.or try a switch from command line to do it.|||Any ideas on how to close a connection? As far as I can see a connection is opened by reference to it, but I can't see any way to close a connection.|||

//release here

managedOleInstance.AcquireConnections(null);

managedOleInstance.ReinitializeMetaData();

managedOleInstance.ReleaseConnections();

|||

So this would be in a script componenent after the last valid use of the connection and before the deletion I take it?

I would also probably need to force RetainSameConnection to use only one connection the whole way through.

|||

Can u show the code you have written?

Then i can favor you better.

Also, can u solve my problem?

I have a flat file with name "Employee.txt " (Full url: C:\Employee.txt ) .The File content is like this

Anil,Engineering,1997
Sunil,Sales,1981
Kumar,Inventory,1991
Rajesh,Engineering,1992

(Note: Items are Comma Seperated)

Now, i have a SQL Server database called "EmployeeDB" which has 2 tables "TblEmp1", "TblEmp2".
The Table is like this.

TblEmp1 : Columns
EmpName EmpDept EmpjoinDate

TblEmp2 : Columns
EName EDate Edept

using integration services (SSIS) i need code to Create a dtsx package so that i can push the flat file content to these 2 tables.
And the condition is :

After Executing the package Data loaded in TblEmp1 should be like this

EmpName EmpDept EmpjoinDate
Anil Engineering 1997
Sunil Sales 1981
Kumar Inventory 1991
Rajesh Engineering 1992

(No change in order compared to source)
And Data loaded inTBLEmp2 should be like this

EName EDate Edept
Anil 1997 Engineering
Sunil 1981 Sales
Kumar 1991 Inventory
Rajesh 1992 Engineering

Now, i know that we need to do like this in wizard
1) Create a flat file source component.
2) Create flat file connection and set the properties of flat file (delimeters and other things)
3) Create a Multicast Component.
4) Create a Path between Flat file source and Multicast.
5) Create 2 destination component(each for a table).
6) Create path from multicast to 2 destination components
7) Create a OledbConnection and set table names for 2 destination components..
7) Now,i have to do mapping for destination1.
8) Now, i have to do mapping for destination2( this mapping will be different from mapping done for destination1 because iam not inserting the data in the same order in which iam doing for TBLEmp1.

I have done it in wizard.I need to do it through code and i know that its not complicated.The main problem is Mapping differently for 2 destinations from source.for 1st one we can have a forloop for mapping.but for 2nd one iam confused!!

Please Get back ASAP today.
Thanks in Advance,
Anil Kumar MS

|||

Sorry mate, our days in Sydney obviously end earlier than wherever you are...

Firstly, I don't have code, just components. I've already outlined the relevant bits of what happened.

Secondly, with respect of your problem. Is there a constraint linking the two tables? If not, what you're doing looks just dandy. If there is, you may need to change your order or temporalily relax the constraint.

If its just a question of mapping why not just use the mapping tab on the destination?

Regards

Delay in connection

I'm working with Sql 2005 developer edition

It works well but some times I get long delay in connection and read data.is it any way to solve the problem?

for more information whene is working well I can connect to database

and get all information I need in .1 sec. when is going to be late this

action may takes 20 sec

What client provider/driver and what version are you using - MDAC, SQL Native Client, .Net SqlCLient 1.1, 2.0, etc.?

Is the client ans SQL Server on the same machine or not?

Is there are a firewall protecting the SQL Server machine, specifically its SQL Server TCP port (by default 1433)?

Most of the clients attempt TCP connection first. If the SQL Server is protected by a firewall without exception for SQL Server port this usually fails in about 21 seconds. Then they try other protocols, usuallu Named Pipes. This would succeed fast if File and Printer Sharing is enabled on the server. The protocol information gets cached for certain time, which could be an explanation why it sometimes takes short time and other times ~20 seconds.

|||Hi

Glad to hear u for my problem

I run my program on the same machin as sql server is on

and I use Sql server developer edition|||

Do you know what step takes the long time - connection etsablishment, a query, etc.?

Also, are you using C# (SqlCLient), C++ (OLEDB?, ODBC? - MDAC or SQL Native Client)?

Delay in connection

I'm working with Sql 2005 developer edition
It works well but some times I get long delay in connection and read data.is it any way to solve the problem?
for more information whene is working well I can connect to database and get all information I need in .1 sec. when is going to be late this action may takes 20 sec

What client provider/driver and what version are you using - MDAC, SQL Native Client, .Net SqlCLient 1.1, 2.0, etc.?

Is the client ans SQL Server on the same machine or not?

Is there are a firewall protecting the SQL Server machine, specifically its SQL Server TCP port (by default 1433)?

Most of the clients attempt TCP connection first. If the SQL Server is protected by a firewall without exception for SQL Server port this usually fails in about 21 seconds. Then they try other protocols, usuallu Named Pipes. This would succeed fast if File and Printer Sharing is enabled on the server. The protocol information gets cached for certain time, which could be an explanation why it sometimes takes short time and other times ~20 seconds.

|||Hi
Glad to hear u for my problem
I run my program on the same machin as sql server is on
and I use Sql server developer edition
|||

Do you know what step takes the long time - connection etsablishment, a query, etc.?

Also, are you using C# (SqlCLient), C++ (OLEDB?, ODBC? - MDAC or SQL Native Client)?

Delay before uncomplete transaction removed

When a workstation losts connection to server,
it can leave an uncomplete transaction. Then
SQL Server removes the transaction.
Could anyone guide me how to set the delay
before SQL Server do it ?

Thanks in advance
John S.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!john smile <nospam> wrote in message news:<40fda2d1$0$16449$c397aba@.news.newsgroups.ws>...
> When a workstation losts connection to server,
> it can leave an uncomplete transaction. Then
> SQL Server removes the transaction.
> Could anyone guide me how to set the delay
> before SQL Server do it ?
> Thanks in advance
> John S.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

I'm not sure I understand your question - as far as I know, MSSQL will
rollback the transaction immediately, but why would you want to delay
the rollback? Perhaps if you can explain what problem you are trying
to solve, someone will be able to make a suggestion.

Simon|||Simon, thanks for the response.

>>I'm not sure I understand your question -
as far as I know, MSSQL will
rollback the transaction immediately, >
It means : I want to know for sure how many seconds
is it from connection failure to the beginning
of rollback.

>>but why would you want to delay
the rollback? >
No, I do not want to delay it.
If possible, I want to make the delay short,
as short as possible by adjusting any parameters
(like in Novell Netware : "number of watchdog -
packets", "delay between watchdog packets").

>>Perhaps if you can explain what problem you are trying
to solve, someone will be able to make a suggestion. >
Currently, I have no problem. It is only a question.
I also want to ask :
If I found that SQL Server took long to rollback
an uncommitted small transaction caused by a network
failure, what should I do ?

Regards
John S.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||"john smile" <nospam> wrote in message
news:40fe5998$0$16470$c397aba@.news.newsgroups.ws.. .
> Currently, I have no problem. It is only a question.
> I also want to ask :
> If I found that SQL Server took long to rollback
> an uncommitted small transaction caused by a network
> failure, what should I do ?

Design your transactions better.

Seriously, this is one of the issues we've looked at as we're moving to
clustering. Clustering basically involves shutting down and restarting SQL
Server, so any long transactions affect your failover time. And if for
example you had transactions that take 15 minutes to rollforward or back,
your failover will take at least that long to occur. Which sorta negates
the usefullness in most cases.

Generally small transactions won't take long at all to rollback. Even lots
of them.

> Regards
> John S.
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!sql

degree of seperation

Hi,
how can I efficiently find out the shortest connection in the following scenario:
there's a table which has two colums, both of the same type.
two items in the same row means that they are connected. (there is no direction of the connection so that a connection shows up in both ways in a table for query performance issues)
ie. item1 is connected with item2 and item2 is connected with item3, then the table looks like:

row1: item1 , item2
row2: item2 , item1

row3: item2 , item3
row4: item3 , item2

so the connection item1 to item3 would be item1 - item2 - item3.
degree of seperation is 2 in this case. how can I implement this in general with sql?
making an exhaustive search would cost too much time. thanks for any hints!

Perhaps you could dream up a recursive CTE to do this, although I can't think how. You could write a SQL CLR routine to do itwith the CLR it should be easy enough to do a search of the graph.

What confuses me is that you don't want "an exhaustive search". There's no magic here: assuming the shortest path is of length n, I don't see a way to get away without considering all paths shorter than n in one way or another.

Cheers,

DefaultCodePage issue from Db2 source

Hi everyone,

We're stuck with this. We've got a Db2 source connection and when we try 'Preview' button appears the following error:

TITLE: Microsoft Visual Studio

The component reported the following warnings:

Warning at {420E9420-175B-4C2F-856A-EA5D51C23627} [OLE DB Source [1]]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.

Choose OK if you want to continue with the operation.
Choose Cancel if you want to stop the operation.

Does anyone have ever used DB2 driver from a dtsx package? If so, let me know if you had any related problem with.

Another day it happens with Oracle.

Thanks in advance for your time,

The DB2 OLE DB provider does expose code page information, so it is warning you of this fact. It also suggests a workaround/fix. The OLE-DB Source has a AlwaysUseDefaultCodePage property, it is exposed in the property grid, not the custom UI. Set this to true. You could also just ignore the error, it will work, even in the preview window.

The same applies to Oracle, again they have not implemented code page support in the provider, same solution.

|||

DarrenSQLIS wrote:

The DB2 OLE DB provider does expose code page information, so it is warning you of this fact. It also suggests a workaround/fix. The OLE-DB Source has a AlwaysUseDefaultCodePage property, it is exposed in the property grid, not the custom UI. Set this to true. You could also just ignore the error, it will work, even in the preview window.

The same applies to Oracle, again they have not implemented code page support in the provider, same solution.

Follow Darren's advice. Set the "AlwaysUseDefaultCodePage" to true and your warning will go away.|||

hi guys, not tested yet. That's for a colleague of mine.

thanks

Default Resolver

Dear all,
I'm just trying to do merge replication between to DB with Dialup connection.
- If any conflict occur, does the default resolver let the publisher win? or should we use another resolver? how?
- What will happen if the connection totally broken during running synchronization?
Thanks in advance.
ech
Yes, by default the publisher wins a conflict with a subscriber and the
first subscriber takes the place of the publisher in any conflicts with
other subscribers. You can use global conflict resolution (priority numbers
assigned to each subscriber), one of the many inbuilt conflict resolution
algorithms or use a custom resolver. If you want to use a custom resolver
there is an average price stored procedure in the samples which is good to
start with, or you might want to make a COM resolver.
If the connection is broken ('Network Failure') just restart the merge agent
once the connection is up and it will continue.
HTH,
Paul Ibison
|||It's helpful. Thanks Paul.

Default protocols ..

Hi everyone,
I have something weird with an SQL Client connection and the default
protocols.
I set the TCP has the only one default client protocol with the CLICONFG
tool. On most of the computers, when a client wants to connect to a server
with its IP (somewhere over Internet), it uses the default protocols
perfectly.
Let's say, ie : osql -S IP -U usedID -P password
BUT. Sometimes, it doesn't. if I add the port to the server ip, then it
works. But I don't want to (in fact, I can't do it into my distribued
application) !
osql -S IP,1433 -U usedID -P password
Of course, I checked the default port fort the TCP and it is the good one.
If a run a netstat to see what happens, I can see something like that :
TCP POSTE1:2612 IP:ms-sql-s TIME_WAIT
TCP POSTE1:2613 IP:ms-sql-s TIME_WAIT
TCP POSTE1:2614 IP:ms-sql-s TIME_WAIT
TCP POSTE1:2615 IP:ms-sql-s TIME_WAIT
TCP POSTE1:2616 IP:ms-sql-s TIME_WAIT
TCP POSTE1:2617 IP:ms-sql-s TIME_WAIT
TCP POSTE1:2618 IP:ms-sql-s TIME_WAIT
So, it uses the TCP, connects to the right port BUT does not makes the
connection. If I just add this f**** ,1433, then it connects fine to the
same port. Really crazy...
Does anyone have an idea please ?
Thanks a lot !
Patrick
Some low probability possibilities:
When you say, "I checked the default port fort the TCP and it is the good
one" did you mean you checked the default
port for TCP/IP in the Client Network Utility on the client that can't
connect?
And are you in a totally SQL Server 2000 environment? If your client has SQL
Server 2005 installed, you may be using the SQL Server Native Client, and
need to check the client protocol and port number using the SQL Server
Configuration Manager. If your server is SQL Server 2005 there are
additional things you can check, but it sounds like it's not.
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Patrick B." <patrick@.ikosoft.com> wrote in message
news:eOPhBmIyHHA.484@.TK2MSFTNGP06.phx.gbl...
> Hi everyone,
> I have something weird with an SQL Client connection and the default
> protocols.
> I set the TCP has the only one default client protocol with the CLICONFG
> tool. On most of the computers, when a client wants to connect to a server
> with its IP (somewhere over Internet), it uses the default protocols
> perfectly.
> Let's say, ie : osql -S IP -U usedID -P password
> BUT. Sometimes, it doesn't. if I add the port to the server ip, then it
> works. But I don't want to (in fact, I can't do it into my distribued
> application) !
> osql -S IP,1433 -U usedID -P password
> Of course, I checked the default port fort the TCP and it is the good one.
> If a run a netstat to see what happens, I can see something like that :
> TCP POSTE1:2612 IP:ms-sql-s TIME_WAIT
> TCP POSTE1:2613 IP:ms-sql-s TIME_WAIT
> TCP POSTE1:2614 IP:ms-sql-s TIME_WAIT
> TCP POSTE1:2615 IP:ms-sql-s TIME_WAIT
> TCP POSTE1:2616 IP:ms-sql-s TIME_WAIT
> TCP POSTE1:2617 IP:ms-sql-s TIME_WAIT
> TCP POSTE1:2618 IP:ms-sql-s TIME_WAIT
> So, it uses the TCP, connects to the right port BUT does not makes the
> connection. If I just add this f**** ,1433, then it connects fine to the
> same port. Really crazy...
> Does anyone have an idea please ?
> Thanks a lot !
> Patrick
>
|||Hi,
Thank you for you reply.
Yes, it is in a totally SQL Server 2000 environment, and every server has an
instance. And yes, I checked the default port in the Client Utility on the
client that can't connect.
I found new things, more and more weird.
On a client that can't connect, I tried with the local server (we install an
instancied MSDE with all of our softwares, and the configuration on clients
and servers are the same). The problem is exactly the same with the
localhost.
osql -S 127.0.0.1 -E ==> failed
osql -S 127.0.0.1\INSTANCENAME -E ==> ok
osql -S 127.0.0.1,1433 -E ==> ok
But on most pcs, those 3 cases are working perfectly ! I really don't
understand...
Thanks again,
Patrick
(sorry for my poor english !)
"Rick Byham, (MSFT)" <rickbyh@.REDMOND.CORP.MICROSOFT.COM> a crit dans le
message de news: 99849E87-A62F-40B7-AF97-567C1A3E9A6F@.microsoft.com...
> Some low probability possibilities:
> When you say, "I checked the default port fort the TCP and it is the good
> one" did you mean you checked the default
> port for TCP/IP in the Client Network Utility on the client that can't
> connect?
> And are you in a totally SQL Server 2000 environment? If your client has
> SQL Server 2005 installed, you may be using the SQL Server Native Client,
> and need to check the client protocol and port number using the SQL Server
> Configuration Manager. If your server is SQL Server 2005 there are
> additional things you can check, but it sounds like it's not.
> --
> Rick Byham (MSFT)
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Patrick B." <patrick@.ikosoft.com> wrote in message
> news:eOPhBmIyHHA.484@.TK2MSFTNGP06.phx.gbl...
>

Default protocols ..

Hi everyone,
I have something weird with an SQL Client connection and the default
protocols.
I set the TCP has the only one default client protocol with the CLICONFG
tool. On most of the computers, when a client wants to connect to a server
with its IP (somewhere over Internet), it uses the default protocols
perfectly.
Let's say, ie : osql -S IP -U usedID -P password
BUT. Sometimes, it doesn't. if I add the port to the server ip, then it
works. But I don't want to (in fact, I can't do it into my distribued
application) !
osql -S IP,1433 -U usedID -P password
Of course, I checked the default port fort the TCP and it is the good one.
If a run a netstat to see what happens, I can see something like that :
TCP POSTE1:2612 IP:ms-sql-s TIME_WAIT
TCP POSTE1:2613 IP:ms-sql-s TIME_WAIT
TCP POSTE1:2614 IP:ms-sql-s TIME_WAIT
TCP POSTE1:2615 IP:ms-sql-s TIME_WAIT
TCP POSTE1:2616 IP:ms-sql-s TIME_WAIT
TCP POSTE1:2617 IP:ms-sql-s TIME_WAIT
TCP POSTE1:2618 IP:ms-sql-s TIME_WAIT
So, it uses the TCP, connects to the right port BUT does not makes the
connection. If I just add this f**** ,1433, then it connects fine to the
same port. Really crazy...
Does anyone have an idea please ?
Thanks a lot !
PatrickSome low probability possibilities:
When you say, "I checked the default port fort the TCP and it is the good
one" did you mean you checked the default
port for TCP/IP in the Client Network Utility on the client that can't
connect?
And are you in a totally SQL Server 2000 environment? If your client has SQL
Server 2005 installed, you may be using the SQL Server Native Client, and
need to check the client protocol and port number using the SQL Server
Configuration Manager. If your server is SQL Server 2005 there are
additional things you can check, but it sounds like it's not.
--
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Patrick B." <patrick@.ikosoft.com> wrote in message
news:eOPhBmIyHHA.484@.TK2MSFTNGP06.phx.gbl...
> Hi everyone,
> I have something weird with an SQL Client connection and the default
> protocols.
> I set the TCP has the only one default client protocol with the CLICONFG
> tool. On most of the computers, when a client wants to connect to a server
> with its IP (somewhere over Internet), it uses the default protocols
> perfectly.
> Let's say, ie : osql -S IP -U usedID -P password
> BUT. Sometimes, it doesn't. if I add the port to the server ip, then it
> works. But I don't want to (in fact, I can't do it into my distribued
> application) !
> osql -S IP,1433 -U usedID -P password
> Of course, I checked the default port fort the TCP and it is the good one.
> If a run a netstat to see what happens, I can see something like that :
> TCP POSTE1:2612 IP:ms-sql-s TIME_WAIT
> TCP POSTE1:2613 IP:ms-sql-s TIME_WAIT
> TCP POSTE1:2614 IP:ms-sql-s TIME_WAIT
> TCP POSTE1:2615 IP:ms-sql-s TIME_WAIT
> TCP POSTE1:2616 IP:ms-sql-s TIME_WAIT
> TCP POSTE1:2617 IP:ms-sql-s TIME_WAIT
> TCP POSTE1:2618 IP:ms-sql-s TIME_WAIT
> So, it uses the TCP, connects to the right port BUT does not makes the
> connection. If I just add this f**** ,1433, then it connects fine to the
> same port. Really crazy...
> Does anyone have an idea please ?
> Thanks a lot !
> Patrick
>|||Hi,
Thank you for you reply.
Yes, it is in a totally SQL Server 2000 environment, and every server has an
instance. And yes, I checked the default port in the Client Utility on the
client that can't connect.
I found new things, more and more weird.
On a client that can't connect, I tried with the local server (we install an
instancied MSDE with all of our softwares, and the configuration on clients
and servers are the same). The problem is exactly the same with the
localhost.
osql -S 127.0.0.1 -E ==> failed
osql -S 127.0.0.1\INSTANCENAME -E ==> ok
osql -S 127.0.0.1,1433 -E ==> ok
But on most pcs, those 3 cases are working perfectly ! I really don't
understand...
Thanks again,
Patrick
(sorry for my poor english !)
"Rick Byham, (MSFT)" <rickbyh@.REDMOND.CORP.MICROSOFT.COM> a crit dans le
message de news: 99849E87-A62F-40B7-AF97-567C1A3E9A6F@.microsoft.com...
> Some low probability possibilities:
> When you say, "I checked the default port fort the TCP and it is the good
> one" did you mean you checked the default
> port for TCP/IP in the Client Network Utility on the client that can't
> connect?
> And are you in a totally SQL Server 2000 environment? If your client has
> SQL Server 2005 installed, you may be using the SQL Server Native Client,
> and need to check the client protocol and port number using the SQL Server
> Configuration Manager. If your server is SQL Server 2005 there are
> additional things you can check, but it sounds like it's not.
> --
> Rick Byham (MSFT)
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Patrick B." <patrick@.ikosoft.com> wrote in message
> news:eOPhBmIyHHA.484@.TK2MSFTNGP06.phx.gbl...
>