Can we have a folder as input path for SSIS package, and process all files within it?

Can we have a folder as input path for SSIS package, and process all files within it?

My client wish to run a batch process where in a folder he will put number of files, which needs to be processed by ssis package. Is that possible directly in ssis?

Alternate solution that I have thought of is...
1. user will copy all files in c:\folder1
2. a .NET component will
2.1 pick up files from folder1 one by one (process all files in the folder)
2.2 will copy that file in c:\INPUT folder by the name in.xls
2.3 will call SSIS package
2.4 SSIS package will process the file in.xls
2.5 end loop..go back to step 2.1

Do we have any othre way of achieving this task? Any suggestions are welcome. Thanks in advanc.

Regards,
MS Guy

MS Guy wrote:

Can we have a folder as input path for SSIS package, and process all files within it?

My client wish to run a batch process where in a folder he will put number of files, which needs to be processed by ssis package. Is that possible directly in ssis?

Alternate solution that I have thought of is...
1. user will copy all files in c:\folder1
2. a .NET component will
2.1 pick up files from folder1 one by one (process all files in the folder)
2.2 will copy that file in c:\INPUT folder by the name in.xls
2.3 will call SSIS package
2.4 SSIS package will process the file in.xls
2.5 end loop..go back to step 2.1

Do we have any othre way of achieving this task? Any suggestions are welcome. Thanks in advanc.

Regards,
MS Guy

There's a few things that make the answer "It depends" but there's a few things you should look at:
1) MULTIFLATFILE connection manager. This connection manager points to a colelction of files (usually all files in a folder) and you can then apply a single operation (such as loading the contents of all those files into a table) in one step. Usually the metadata of the files needs to be the same - it depends on what operation you're applying.

2) Use a Foreach loop container to loop over the files and process them individually. There's a bit more guidance on this here: http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx plus Darren and Allan have some good stuff on this at www.sqlis.com.

-Jamie

Can truncate but not delete

Hi
I have a 43,000 row table but can no longer delete the data from it all of a
sudden..it just hangs on the process and the processor shoots up to 100%. Bu
t
I have tried truncating this and it works, I know deleting logs every row
deleted but am not sure why I am getting this problem or what to monitor to
solve it.
Any help or advice would be great thanks
SammySammy
Do you have a WHERE clause in your DELETE statement?
Actually , DELETE and TRUNCATE commands are 'slightly' different in terms
how they do the job
DELETE command is fully logged (every row was deleted is recorded in TL) ,
as opposed TRUNCATE command which
logs only the deallocation of whole data pages
"Sammy" <Sammy@.discussions.microsoft.com> wrote in message
news:1B557178-E1BA-4D16-B4E8-2B9519856649@.microsoft.com...
> Hi
> I have a 43,000 row table but can no longer delete the data from it all of
> a
> sudden..it just hangs on the process and the processor shoots up to 100%.
> But
> I have tried truncating this and it works, I know deleting logs every row
> deleted but am not sure why I am getting this problem or what to monitor
> to
> solve it.
> Any help or advice would be great thanks
> Sammy|||Hi Uri,
No its just delete quotedetail it always worked before... but I get no
errors it just hangs so not sure what to check and how to solve this issue
"Uri Dimant" wrote:

> Sammy
> Do you have a WHERE clause in your DELETE statement?
>
> Actually , DELETE and TRUNCATE commands are 'slightly' different in terms
> how they do the job
> DELETE command is fully logged (every row was deleted is recorded in TL) ,
> as opposed TRUNCATE command which
> logs only the deallocation of whole data pages
> "Sammy" <Sammy@.discussions.microsoft.com> wrote in message
> news:1B557178-E1BA-4D16-B4E8-2B9519856649@.microsoft.com...
>
>|||So , do you have WHERE clause or you don't?
It is possible that blocks are occured because if you don't have an index on
the column in WHERE clause , SQL Server may lock entire table.
How many rows do you trying to delete?
"Sammy" <Sammy@.discussions.microsoft.com> wrote in message
news:6B563C5B-9D6E-44E5-AA43-404DA791730B@.microsoft.com...
> Hi Uri,
> No its just delete quotedetail it always worked before... but I get no
> errors it just hangs so not sure what to check and how to solve this issue
> "Uri Dimant" wrote:
>|||Here's a couple of things to check...
1. Is there another connection which has a lock on the table?
2. Is there a delete trigger on the table? This would be executing and the
problem could be there.
Regards
Colin Dawson
www.cjdawson.com
"Sammy" <Sammy@.discussions.microsoft.com> wrote in message
news:6B563C5B-9D6E-44E5-AA43-404DA791730B@.microsoft.com...
> Hi Uri,
> No its just delete quotedetail it always worked before... but I get no
> errors it just hangs so not sure what to check and how to solve this issue
> "Uri Dimant" wrote:
>|||Sammy (Sammy@.discussions.microsoft.com) writes:
> I have a 43,000 row table but can no longer delete the data from it all
> of a sudden..it just hangs on the process and the processor shoots up to
> 100%. But I have tried truncating this and it works, I know deleting
> logs every row deleted but am not sure why I am getting this problem or
> what to monitor to solve it.
> Any help or advice would be great thanks
Let's first rule out a few things it can't be:
o Blocking. Had it been a blocking issue, your processor would stay calm.
o Referencing foreign key. A delete operation can take a very long time
if there are referencing foreign keys that are not indexed, or if there
cascading deletes/updates. However, had there been referencing FKs,
SQL Server would not permit you to run TRUNCATE TABLE.
That leaves two options:
o There is a trigger on the table, and the trigger performs something
wild. TRUNCATE TABLE does not fire triggers, so it could not cause
this problem.
o The log file (or the data file) is full, and needs to autogrow. If the
the database is large, and you have the default 10% autogrow, this can
give quite an impact. As TRUNCATE TABLE just logs the extent
deallocations, it causes far less load.
Thus you should examine if you have triggers on the table, and run
and run DBCC SQLPERF ( LOGSPACE ) to determine how log space you have
in use.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||No triggers or other processes accessing the table, its a 44,000 row table
and delete quotedetail (no where clause in the delete command) does not work
but truncate table quotedetail works with no problems.
Have u any other ideas how to reslove this as I am stumped?
"Colin Dawson" wrote:

> Here's a couple of things to check...
>
> 1. Is there another connection which has a lock on the table?
> 2. Is there a delete trigger on the table? This would be executing and th
e
> problem could be there.
>
> --
> Regards
> Colin Dawson
> www.cjdawson.com
>
> "Sammy" <Sammy@.discussions.microsoft.com> wrote in message
> news:6B563C5B-9D6E-44E5-AA43-404DA791730B@.microsoft.com...
>
>|||Hi Erland I discovered it was part of a snapshot replication, I did not thin
k
it was part of replication as I could truncate the table but I guess its onl
y
merge and transactional replication that does not allow you to truncate the
tables. But I don't think this should affect my problem.
My processor queue length goes up to 25 when the delete command is run and
I think my processor might be having problems now and may need to be replace
d
to solve this issue.
"Erland Sommarskog" wrote:

> Sammy (Sammy@.discussions.microsoft.com) writes:
> Let's first rule out a few things it can't be:
> o Blocking. Had it been a blocking issue, your processor would stay calm.
> o Referencing foreign key. A delete operation can take a very long time
> if there are referencing foreign keys that are not indexed, or if there
> cascading deletes/updates. However, had there been referencing FKs,
> SQL Server would not permit you to run TRUNCATE TABLE.
> That leaves two options:
> o There is a trigger on the table, and the trigger performs something
> wild. TRUNCATE TABLE does not fire triggers, so it could not cause
> this problem.
> o The log file (or the data file) is full, and needs to autogrow. If the
> the database is large, and you have the default 10% autogrow, this can
> give quite an impact. As TRUNCATE TABLE just logs the extent
> deallocations, it causes far less load.
> Thus you should examine if you have triggers on the table, and run
> and run DBCC SQLPERF ( LOGSPACE ) to determine how log space you have
> in use.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Sammy (Sammy@.discussions.microsoft.com) writes:
> Hi Erland I discovered it was part of a snapshot replication, I did not
> think it was part of replication as I could truncate the table but I
> guess its only merge and transactional replication that does not allow
> you to truncate the tables. But I don't think this should affect my
> problem. My processor queue length goes up to 25 when the delete command
> is run and I think my processor might be having problems now and may
> need to be replaced to solve this issue.
Ah, so replication set in? That was interesting to hear, as I'm not
doing much replication myself. Good to know, the next time someone
else asks a similar question.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||See some details/background data on "Live-Lock" vs. "Dead-Lock" in SQL
Server:
http://www.dbforums.com/showthread...099#post4505099
It also gives some coverage on data accessibility in SQL Server.
You can download as a FREE-WARE SQL Server package that detects
Live-lock situations on SQL Server (and notifies production DBA, if
configured).
(SQL Server Live_Lock_Monitor).
It also continuously monitors/logs SQL Server Response time (and
notifies DBA if very slow response detected).
Note: SQL Server can "Freese" when it:
1) Automatically "Expands" any database (DB options set to "Autogrow"-
NOT recommended in production)
2) When programmer misses the join (cross product or Cartesian product)
3) when a number of processes are lined up to access some resource
(e.g. table, being heavily updated)
4) table scans against large tables done
5) a table is locked (reindexation, DBCC operation etc)
6) a lot of data is being "pumped" to the client side (i.e. programmer
writes:
SELECT * from Users (against tables with millions of rows)
and in a lot of other scenarios.
Most of them (not all) should be detected by the package (since actual
response time is measured).
Alexei Akimov

can to hold a pdf in sql field ? (i.e like bmp as binary)

Hi,
I understand you can hold and image in a sql server field as a binary file.
Can this same process be used to hold an actuall PDF file in a field ? (i.e
as opposed to a link).
note: i would like to display the pdf doc via and asp page.
Thanks for any advice
Scott
It is binary data, so you can hold anything you like in the column. If you don't have a PDF
displayer that can display based on data in a table, you would have to materialize the data to a
file and then use that file in your pdf displayer.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"scott" <nospam@.yahoo.com> wrote in message news:%2375LjvhsFHA.3180@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I understand you can hold and image in a sql server field as a binary file. Can this same process
> be used to hold an actuall PDF file in a field ? (i.e as opposed to a link).
> note: i would like to display the pdf doc via and asp page.
> Thanks for any advice
> Scott
>
|||Hi
A PDF would need to be an image data type. It is in effect is a binary data
type.
You need to write code using Getchunk and Appendchunk.
http://support.microsoft.com/default...b;en-us;194975
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"scott" wrote:

> Hi,
> I understand you can hold and image in a sql server field as a binary file.
> Can this same process be used to hold an actuall PDF file in a field ? (i.e
> as opposed to a link).
> note: i would like to display the pdf doc via and asp page.
> Thanks for any advice
> Scott
>
>
|||thanks
scott

can to hold a pdf in sql field ? (i.e like bmp as binary)

Hi,
I understand you can hold and image in a sql server field as a binary file.
Can this same process be used to hold an actuall PDF file in a field ? (i.e
as opposed to a link).
note: i would like to display the pdf doc via and asp page.
Thanks for any advice
ScottIt is binary data, so you can hold anything you like in the column. If you don't have a PDF
displayer that can display based on data in a table, you would have to materialize the data to a
file and then use that file in your pdf displayer.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"scott" <nospam@.yahoo.com> wrote in message news:%2375LjvhsFHA.3180@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I understand you can hold and image in a sql server field as a binary file. Can this same process
> be used to hold an actuall PDF file in a field ? (i.e as opposed to a link).
> note: i would like to display the pdf doc via and asp page.
> Thanks for any advice
> Scott
>|||Hi
A PDF would need to be an image data type. It is in effect is a binary data
type.
You need to write code using Getchunk and Appendchunk.
http://support.microsoft.com/default.aspx?scid=kb;en-us;194975
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"scott" wrote:
> Hi,
> I understand you can hold and image in a sql server field as a binary file.
> Can this same process be used to hold an actuall PDF file in a field ? (i.e
> as opposed to a link).
> note: i would like to display the pdf doc via and asp page.
> Thanks for any advice
> Scott
>
>|||thanks
scott

can to hold a pdf in sql field ? (i.e like bmp as binary)

Hi,
I understand you can hold and image in a sql server field as a binary file.
Can this same process be used to hold an actuall PDF file in a field ? (i.e
as opposed to a link).
note: i would like to display the pdf doc via and asp page.
Thanks for any advice
ScottIt is binary data, so you can hold anything you like in the column. If you d
on't have a PDF
displayer that can display based on data in a table, you would have to mater
ialize the data to a
file and then use that file in your pdf displayer.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"scott" <nospam@.yahoo.com> wrote in message news:%2375LjvhsFHA.3180@.TK2MSFTNGP10.phx.gbl...[
vbcol=seagreen]
> Hi,
> I understand you can hold and image in a sql server field as a binary file
. Can this same process
> be used to hold an actuall PDF file in a field ? (i.e as opposed to a lin
k).
> note: i would like to display the pdf doc via and asp page.
> Thanks for any advice
> Scott
>[/vbcol]|||Hi
A PDF would need to be an image data type. It is in effect is a binary data
type.
You need to write code using Getchunk and Appendchunk.
http://support.microsoft.com/defaul...kb;en-us;194975
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"scott" wrote:

> Hi,
> I understand you can hold and image in a sql server field as a binary file
.
> Can this same process be used to hold an actuall PDF file in a field ? (i
.e
> as opposed to a link).
> note: i would like to display the pdf doc via and asp page.
> Thanks for any advice
> Scott
>
>|||thanks
scott

Can the use of Shrinkfile break the Transaction Log chain?

My Log Shipping process has been up and running fine for about 6 months now.
Then as part of the preparation for upgrading the front end application for
the log shipped database, my partner DBA thought he would help the backup
speed by running a SHRINKFILE over the database files.
It was on or about this time that log shipping stopped working and reported
errors just like the ones you get when the transaction log has been
truncated. Basically it thinks the T Log chain has been broken.
Can SHRINKFILE break the chain? I thought it only compacted the unused file
space?
In my tests log shipping was not sensitive to shrinkfile statements. Perhaps
something else has gone wrong.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"GRP" <GRP@.discussions.microsoft.com> wrote in message
news:BD8BC1B6-FD32-43C2-B34E-7CA0397CAC35@.microsoft.com...
> My Log Shipping process has been up and running fine for about 6 months
now.
> Then as part of the preparation for upgrading the front end application
for
> the log shipped database, my partner DBA thought he would help the backup
> speed by running a SHRINKFILE over the database files.
> It was on or about this time that log shipping stopped working and
reported
> errors just like the ones you get when the transaction log has been
> truncated. Basically it thinks the T Log chain has been broken.
> Can SHRINKFILE break the chain? I thought it only compacted the unused
file
> space?
|||Hilary, thanks for taking the time to test this out. The only other
explanation I can think of is that the other DBA truncated the log prior to
the shrinkfile.
"Hilary Cotter" wrote:

> In my tests log shipping was not sensitive to shrinkfile statements. Perhaps
> something else has gone wrong.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "GRP" <GRP@.discussions.microsoft.com> wrote in message
> news:BD8BC1B6-FD32-43C2-B34E-7CA0397CAC35@.microsoft.com...
> now.
> for
> reported
> file
>
>

Can table1 in example below get updated by another process while the transaction is in pro

I am afraid that just after @.statusOfEmployee is retrieved from table1, but before table2 is updated, someone else (a second user) calls this same stored procedure and changes the @.statusOfEmployee value. This would create aninconsistentupdate of table2 by first user, since the update of table2 'might' not have gone ahead if the latest value of @.statusOF Employee was used. CAN SOMEONE PLEASE HELP ME WITH THIS SITUATION AND HOW I CAN BE SURE THAT ABOVE DOES NOT HAPPEN SINCE MULTIPLE USERS WILL BE HITTING THIS STORED PROCEDURE?

declare @.status int
begin tran
set @.status = (select statusOfEmployee from table1)
if @.@.ERROR = 0
begin
update table2
set destination = @.destination /* @.destination is an input parameter passed to the sp*/
where @.currentStatus = @.status
if @.ERROR = 0
commit tran
else
rollback tran
end
else
rollback tran

return

update table2
set destination = @.destination /* @.destination is an input parameter passed to the sp*/
where @.currentStatus = (select @.statusOfEmployee from table1)

This will only work if you have only 1 row in table1, but I'm guessing this isn't your real SP. If there can be more than 1, then you have other issues. And to answer your question, yes, it could have been updated between those statements.

|||

Sorryy. I meant a field there. I will edit it.

One question for you: If I used the original sp I mentioned in my post, then is there a danger of incosistent update as I have explained OR because the select is in a transaction, SQL Server will prevent any changes to tables being used in the transaction?

I was going to mark the ADO.Net code that calls this stored procedure as 'critical' in my C# code using lock(this) { }. That way only one user can execute this stored procedure at a time from the application, which eliminates any chances of inconsistent updates. ANYONE HAS ANY COMMENTS ON USING THIS APPROACH TO PREVENT INCOSISTENT UPDATES?

|||

I would think even with your approach, since the select and update are on different tables, there is nothing preventing another user from updating the table used in select statement. SQL Server willonlyprevent any user from updating 'table2' while this update statement is in progress.

|||

Yes, your original SP isn't multi-user safe. No, the one I gave you is. The difference is the type of locks that are requested and the duration for which they are held.

|||

So even though in your query a simple select is being executed on 'table1', SQL Server will place an exclusive lock on 'table1' row.

I thought that under default SQL Server 2000 locking (read committed), select statements will not place an exclusive lock on the row involved in select query. And if this is true, then including the 'select' within the 'update' will still allow someone else to update 'table1' before the update to 'tabl2' happens. Right or wrong?

|||

Because the select is happening within the confines of the UPDATE statement, the subquery will place a read-lock (sharing) on table1's row during the entire time the update is occuring. The update can not happen without this read-lock, and no other updates can happen to table1 while this statement has the read-lock in place.

The real issue that your prior SP had was that the read-lock on table 1 was released as soon as the SET @.var= was completed, which would allow someone to update table1 before table2 was updated. You could accomplish nearly the same thing with some of the locking hints, or changing the transaction isolation mode, but the SP won't execute as quickly as incorporating it into one statement like I did, and that means locks are being held longer than they need to be.

SET @.var=(SELECT ... WITH (HOLDLOCK) ...)

would have accomplished the same thing. If you add more steps to your transaction, then the read lock will be held until the completion of the transaction. With the combined UPDATE, the read lock is dropped when the UPDATE completes (The update lock on table2 is held until the end of the UPDATE if it's not in a transaction, or until the transaction is commited/rolled back...).

|||

Great explanation. It helped clarify an important point to me.

Thanks for that.

Can someone explain the precision of an integer in a sql db pls

Hi I am in the process of creating a new db in sql. In my users table I wish to set the UserIds as Integer datatype. It defualts on precision 4. Does this mean that when the column auto increments as its my primary key with a seed of one, my highest number allowed in the table would be row 9999. ?

Also if you where to store a phone number in your db, what column type would you give it. I have used varChar but its all numbers i want to store. Would this suffice.

ThanksHi,

That's four bytes, not four number places. So the range is from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). That's a lot of users... :-D

I usually store phone numbers as varchars, whether I include formatting symbols--(, ), -, whatever--or not. That way you don't have to worry about trailing zeroes (or leading ones if you also are using international phone numbers), and formatting for the user interface is way easier. In fact, if you are storing international phone numbers, you probably want to include the symbols as well.

Don

Can service broker process a email message

How do you set up the service broker to process an email message, and how do you format that message and send it to the que.

Can the service broker alos process an html form from a que.

Thanks

Service Broker can only be used for exchanging messages between two SQL Instances. You cannot send nor receive email messages directly to/from Service Broker.

Why do you need to send email messages to a queue?

HTH,
~ Remus

Can report results be filtered by Windows user login?

Good morning,

I'm in the process of creating a report to show employees and managers holiday and absence information. Is there a way of filtering the results of the report based on who is running the report, so that employees could only see their own information and managers could only see theirs and their subordinates information?

What I was hoping to do was create a lookup table which cross-references Windows logins with employee numbers and then use this information to pass a parameter to the SQL query, but I don't know how to retrieve the login from the machine being used to view the report.

I've heard about row level security and it seems ideal in theory but I fear the implimentation of row level security would be far beyond my meagre knowledge.

Any constructive suggestions welcomed.

Thanks,

Paul

Hello Paul,

Whenever you create an expression, take a look under the Globals node. One your options is User!UserID, this will give you the Windows login of the user running the report.

Hope this helps.

Jarret

|||

Jarret,

I came across the same solution a little earlier, works a treat.

Thanks,

Paul

Can not save sql view in Enterprise Mgr of data on a linked oracle

I have a sql server that has a linked server set up which is an Oracle
database. I can start the process of creating a SQL view of the needed data
in the Oracle database as follows:
SELECT * FROM OPENQUERY(cadtelbase, 'select * from cadtel_data_724.E356_S0
where ATTR964 = ''V01-03-00108''') If I execute the view I get the expected
results, but as soon as I try to save the view I receive the following SQL
Server Enterprise Manager error message.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
could not be performed because the OLE DB provider â'OraOLEDB.Oracleâ' was
unable to begin a distributed transaction. [Microsoft][ODBC SQL Server
Driver][SQL Server][OLE/DB provider returned message: New transaction cannot
enlist in the specified transaction coordinator. ] [Micorsoft][ODBC SQL
Server Driver][SQL Server][OLE DB error trace [OLE/DB Provider
â'OraOLEDB.Oracleâ' ITransactionJoi JoinTransaction returned 0x8004d00a].
Any help on this would greatly be appreciated.
Thanks!
JackieNever mind. I found the solution. Simply can not create the view using
Enterprise Manager view designer, must create the view using query analyzer!
"Wishing I was skiing mom" wrote:
> I have a sql server that has a linked server set up which is an Oracle
> database. I can start the process of creating a SQL view of the needed data
> in the Oracle database as follows:
> SELECT * FROM OPENQUERY(cadtelbase, 'select * from cadtel_data_724.E356_S0
> where ATTR964 = ''V01-03-00108''') If I execute the view I get the expected
> results, but as soon as I try to save the view I receive the following SQL
> Server Enterprise Manager error message.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server] The operation
> could not be performed because the OLE DB provider â'OraOLEDB.Oracleâ' was
> unable to begin a distributed transaction. [Microsoft][ODBC SQL Server
> Driver][SQL Server][OLE/DB provider returned message: New transaction cannot
> enlist in the specified transaction coordinator. ] [Micorsoft][ODBC SQL
> Server Driver][SQL Server][OLE DB error trace [OLE/DB Provider
> â'OraOLEDB.Oracleâ' ITransactionJoi JoinTransaction returned 0x8004d00a].
> Any help on this would greatly be appreciated.
> Thanks!
> Jackie
>

Can NOT login in to SQL2005 with SQl Server Mangement Tool

I get the following error when trying to connect to my server that is on my local maching:

provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233

It happened after I changed the allowed remote connections to 1. It appears tha I can not log into the server at all. Any way to fix this besides reinstalling SQL Server?

Thanks, Doug

You do not need to re-install. Start here for a complete tutorial:

http://msdn2.microsoft.com/en-us/library/ms345332.aspx

And then check here as well:

http://support.microsoft.com/default.aspx/kb/265808/

If you've done all that, make sure your firewall is set up, as referenced by the "see also" links.

Buck Woody

Can NOT login in to SQL2005 with SQl Server Mangement Tool

I get the following error when trying to connect to my server that is on my local maching:

provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233

It happened after I changed the allowed remote connections to 1. It appears tha I can not log into the server at all. Any way to fix this besides reinstalling SQL Server?

Thanks, Doug

You do not need to re-install. Start here for a complete tutorial:

http://msdn2.microsoft.com/en-us/library/ms345332.aspx

And then check here as well:

http://support.microsoft.com/default.aspx/kb/265808/

If you've done all that, make sure your firewall is set up, as referenced by the "see also" links.

Buck Woody

Can not kill a process

When I try to stop a process which running command 'UPDATE STATISTICS
table_name WITH SAMPLE 50 PERCENT' with 'kill spid', then run sp_who2, the
output window shows that this process is 'KILLED/ROLLBACK'. It looks like it
is continue updating statistics.
Why 'Cancel Query' command in the query analyser works, but kill command
does not wok?
How can I stop this process?
Thanks,
LijunHi,
Kill command is always safe and will rollback all the transaction.
Thanks
Hari
MCDBA
"Lijun Zhang" <nospam@.nospam.nospam> wrote in message
news:e3jW7Iq6DHA.3648@.TK2MSFTNGP11.phx.gbl...
> When I try to stop a process which running command 'UPDATE STATISTICS
> table_name WITH SAMPLE 50 PERCENT' with 'kill spid', then run sp_who2, the
> output window shows that this process is 'KILLED/ROLLBACK'. It looks like
it
> is continue updating statistics.
> Why 'Cancel Query' command in the query analyser works, but kill command
> does not wok?
> How can I stop this process?
> Thanks,
> Lijun
>|||Hi Lijun,
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
From you information, when you run 'UPDATE STATISTICS table_name WITH
SAMPLE 50 PERCENT' and kill this process. But when run sp_who2, you found
that the process is 'KILLED/ROLLBACK'. But you think you might not seen
this process when running sp_who2, right?
Is this problem always happen? Could you provide the information which
edition of SQL Server you are using and could you please provide me the
table definition by right-click the table and choose 'all task' and choose
'genterate SQL script'? Or just by running
use database_name
go
exec sp_help table_name
Waiting for you reply and thank you for you post.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||I can reproduce this problem regardless which table I use. Our server
edition is:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
Lijun
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:9yJvtc56DHA.2164@.cpmsftngxa07.phx.gbl...
> Hi Lijun,
> Thank you for using the newsgroup and it is my pleasure to help you with
> you issue.
> From you information, when you run 'UPDATE STATISTICS table_name WITH
> SAMPLE 50 PERCENT' and kill this process. But when run sp_who2, you found
> that the process is 'KILLED/ROLLBACK'. But you think you might not seen
> this process when running sp_who2, right?
> Is this problem always happen? Could you provide the information which
> edition of SQL Server you are using and could you please provide me the
> table definition by right-click the table and choose 'all task' and choose
> 'genterate SQL script'? Or just by running
> use database_name
> go
> exec sp_help table_name
> Waiting for you reply and thank you for you post.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hi Lijun,
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
As far as I know, when you are runing T-SQL command as 'UPDATE STATISTICS
table_name WITH SAMPLE 50 PERCENT', by default, one singel T-SQL statement
will be a transaction without explicitly claim. If it will take many
actions within this transaction within it, when killed this process by
'KILL SPID' it will take some time to rollback all the actions it has
taken. So, from the sp_who2, you will notice that the status is
'KILLED/ROLLBACK'; If it is still running in the Query Analyzer and you
press 'Cancel Query Execution' button, it will also be stoped and be rolled
back. For the rolled back process, it will take time depends on what it has
done before it is killed/stopped when it begin. So, in Query Analyzer and
for 'Kill', both will work and stop the 'update statistics' and rolled it
back.
Hope this answered you questions and if you still have questions, please
feel free to post new message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||I don't think it is really ROLLBACK. For example, if UPDATE STATISTICS WITH
FULLSCAN on a table takes one hour, pressing 'Cancel Query Excution' button
will stop the process immediately. But if you using KILL command, even you
just started the UPDATE STATISTICS two second ago, it will continue run
until the procees is done. If you issues two UPDATE STATISTICS on a single
batch, it will stop until both table is done. You can check the result with
STATS_DATE, and it shows that both table's statistics have been updated.
Lijun
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:jueBFVK7DHA.808@.cpmsftngxa07.phx.gbl...
> Hi Lijun,
> Thank you for using the newsgroup and it is my pleasure to help you with
> you issue.
> As far as I know, when you are runing T-SQL command as 'UPDATE STATISTICS
> table_name WITH SAMPLE 50 PERCENT', by default, one singel T-SQL
statement
> will be a transaction without explicitly claim. If it will take many
> actions within this transaction within it, when killed this process by
> 'KILL SPID' it will take some time to rollback all the actions it has
> taken. So, from the sp_who2, you will notice that the status is
> 'KILLED/ROLLBACK'; If it is still running in the Query Analyzer and you
> press 'Cancel Query Execution' button, it will also be stoped and be
rolled
> back. For the rolled back process, it will take time depends on what it
has
> done before it is killed/stopped when it begin. So, in Query Analyzer and
> for 'Kill', both will work and stop the 'update statistics' and rolled it
> back.
> Hope this answered you questions and if you still have questions, please
> feel free to post new message here and I am ready to help!
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||U can also use
kill spid with statusonly to tell U how much percentage rollback is left . . .|||Hi Lijun,
Thank you for your update and it is my pleasure to help you with you issue.
I need to make some explanation here. When a transaction is terminated
before it completed, it must be roll back. Also, when a transaction is
cancelled, the rollback time will not based on how long this transation it
has run. In Query Analyzer, when you run 'UPDATE STATISTICS...' and then
press' Cancel Query Execution', the transaction is terminated within the
same thread. Could you run the Stats_date of DBCC SHOWSTATISTICS to check
that, if the 'UPDATE STATISTICS' is running and you press the 'Cancel Query
Execution' button, is the 'UPDATE STATISTICS' rolled back'
When you kill a SPID, a kill command is sent to the SPID, that is, one
thread will send a KILL command to another thread, it is not guaranteed
that the SPID is killed. A spid may respond to the KILL command
immediately, or after a delay, or not at all.
Please refer to the following KB:
INF: Understanding How the Transact-SQL KILL Command Works
http://support.microsoft.com/?id=171224
When you kill a SPID running 'UPDATE STATISTICS, could you check the status
by DBCC PSS? What the status of it? When you restart the SQL Server Service
and restart it, when using Stats_date of DBCC SHOWSTATISTICS, how about the
'Updated' time?
I am looking forward to your reply. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks for you reply. I suggest that you try to reproduce the scenario by
yourself (It is not difficult to do). By the way, the article you motioned
is apply to SQL Server 6.0 and 6.5.
Lijun
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:IkW$Pnt7DHA.568@.cpmsftngxa07.phx.gbl...
> Hi Lijun,
> Thank you for your update and it is my pleasure to help you with you
issue.
> I need to make some explanation here. When a transaction is terminated
> before it completed, it must be roll back. Also, when a transaction is
> cancelled, the rollback time will not based on how long this transation it
> has run. In Query Analyzer, when you run 'UPDATE STATISTICS...' and then
> press' Cancel Query Execution', the transaction is terminated within the
> same thread. Could you run the Stats_date of DBCC SHOWSTATISTICS to check
> that, if the 'UPDATE STATISTICS' is running and you press the 'Cancel
Query
> Execution' button, is the 'UPDATE STATISTICS' rolled back'
> When you kill a SPID, a kill command is sent to the SPID, that is, one
> thread will send a KILL command to another thread, it is not guaranteed
> that the SPID is killed. A spid may respond to the KILL command
> immediately, or after a delay, or not at all.
> Please refer to the following KB:
> INF: Understanding How the Transact-SQL KILL Command Works
> http://support.microsoft.com/?id=171224
> When you kill a SPID running 'UPDATE STATISTICS, could you check the
status
> by DBCC PSS? What the status of it? When you restart the SQL Server
Service
> and restart it, when using Stats_date of DBCC SHOWSTATISTICS, how about
the
> 'Updated' time?
> I am looking forward to your reply. Thanks.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hi Lijun,
Thank you very much for your update!
Yes, I create a huge database and tested what you have done. Yes, you are
right that when using the KILL to kill the SPID running 'UPDATE
STATISTICS', although SP_WHO2 showed the command is KILLED/ROLLBACK and I
could even got the information indicating that it is rolled back 100% and
estimated time for roll back is 1 second. However, the 'UPDATE STATISTICS'
is not stopped by the KILL command.
I tried to figure it out and it really took me some time. Later, I
discussed it with my senior engineers in my team and I got the some
information of it. It is a known issue of SQL Server. I should appologize
that in my last reply to your question, I did not provide you the right
answer, and I did not get the information of it in time. Could I provide
some more assistance this time. I mean if you have any bussiness impact
from it, please feel free to post here and I would try my best to help you!
Thank you very much.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Can you please share with us what the reason for this problem is and how it can be solved?|||Hi Lijun,
Thank you for your update. Our development team is aware of it and is
working on that, I cannot guarantee anything of it. I will update you as
soon as possilbe for the progress of it. Thanks for you understanding.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Can not kill a process

When I try to stop a process which running command 'UPDATE STATISTICS
table_name WITH SAMPLE 50 PERCENT' with 'kill spid', then run sp_who2, the
output window shows that this process is 'KILLED/ROLLBACK'. It looks like it
is continue updating statistics.
Why 'Cancel Query' command in the query analyser works, but kill command
does not wok?
How can I stop this process?
Thanks,
LijunHi,
Kill command is always safe and will rollback all the transaction.
Thanks
Hari
MCDBA
"Lijun Zhang" <nospam@.nospam.nospam> wrote in message
news:e3jW7Iq6DHA.3648@.TK2MSFTNGP11.phx.gbl...

quote:

---

> When I try to stop a process which running command 'UPDATE STATISTICS
> table_name WITH SAMPLE 50 PERCENT' with 'kill spid', then run sp_who2, the
> output window shows that this process is 'KILLED/ROLLBACK'. It looks like

---

it

quote:

---

> is continue updating statistics.
> Why 'Cancel Query' command in the query analyser works, but kill command
> does not wok?
> How can I stop this process?
> Thanks,
> Lijun
>

---

|||Hi Lijun,
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
From you information, when you run 'UPDATE STATISTICS table_name WITH
SAMPLE 50 PERCENT' and kill this process. But when run sp_who2, you found
that the process is 'KILLED/ROLLBACK'. But you think you might not seen
this process when running sp_who2, right?
Is this problem always happen? Could you provide the information which
edition of SQL Server you are using and could you please provide me the
table definition by right-click the table and choose 'all task' and choose
'genterate SQL script'? Or just by running
use database_name
go
exec sp_help table_name
Waiting for you reply and thank you for you post.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||I can reproduce this problem regardless which table I use. Our server
edition is:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 4.0 (Build 1381: Service Pack 6)
Lijun
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:9yJvtc56DHA.2164@.cpmsftngxa07.phx.gbl...

quote:

---

> Hi Lijun,
> Thank you for using the newsgroup and it is my pleasure to help you with
> you issue.
> From you information, when you run 'UPDATE STATISTICS table_name WITH
> SAMPLE 50 PERCENT' and kill this process. But when run sp_who2, you found
> that the process is 'KILLED/ROLLBACK'. But you think you might not seen
> this process when running sp_who2, right?
> Is this problem always happen? Could you provide the information which
> edition of SQL Server you are using and could you please provide me the
> table definition by right-click the table and choose 'all task' and choose
> 'genterate SQL script'? Or just by running
> use database_name
> go
> exec sp_help table_name
> Waiting for you reply and thank you for you post.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>

---

|||Hi Lijun,
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
As far as I know, when you are runing T-SQL command as 'UPDATE STATISTICS
table_name WITH SAMPLE 50 PERCENT', by default, one singel T-SQL statement
will be a transaction without explicitly claim. If it will take many
actions within this transaction within it, when killed this process by
'KILL SPID' it will take some time to rollback all the actions it has
taken. So, from the sp_who2, you will notice that the status is
'KILLED/ROLLBACK'; If it is still running in the Query Analyzer and you
press 'Cancel Query Execution' button, it will also be stoped and be rolled
back. For the rolled back process, it will take time depends on what it has
done before it is killed/stopped when it begin. So, in Query Analyzer and
for 'Kill', both will work and stop the 'update statistics' and rolled it
back.
Hope this answered you questions and if you still have questions, please
feel free to post new message here and I am ready to help!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||I don't think it is really ROLLBACK. For example, if UPDATE STATISTICS WITH
FULLSCAN on a table takes one hour, pressing 'Cancel Query Excution' button
will stop the process immediately. But if you using KILL command, even you
just started the UPDATE STATISTICS two second ago, it will continue run
until the procees is done. If you issues two UPDATE STATISTICS on a single
batch, it will stop until both table is done. You can check the result with
STATS_DATE, and it shows that both table's statistics have been updated.
Lijun
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:jueBFVK7DHA.808@.cpmsftngxa07.phx.gbl...
> Hi Lijun,
> Thank you for using the newsgroup and it is my pleasure to help you with
> you issue.
> As far as I know, when you are runing T-SQL command as 'UPDATE STATISTICS
> table_name WITH SAMPLE 50 PERCENT', by default, one singel T-SQL
statement
> will be a transaction without explicitly claim. If it will take many
> actions within this transaction within it, when killed this process by
> 'KILL SPID' it will take some time to rollback all the actions it has
> taken. So, from the sp_who2, you will notice that the status is
> 'KILLED/ROLLBACK'; If it is still running in the Query Analyzer and you
> press 'Cancel Query Execution' button, it will also be stoped and be
rolled
> back. For the rolled back process, it will take time depends on what it
has
> done before it is killed/stopped when it begin. So, in Query Analyzer and
> for 'Kill', both will work and stop the 'update statistics' and rolled it
> back.
> Hope this answered you questions and if you still have questions, please
> feel free to post new message here and I am ready to help!
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||U can also use
kill spid with statusonly to tell U how much percentage rollback is left . .
.|||Hi Lijun,
Thank you for your update and it is my pleasure to help you with you issue.
I need to make some explanation here. When a transaction is terminated
before it completed, it must be roll back. Also, when a transaction is
cancelled, the rollback time will not based on how long this transation it
has run. In Query Analyzer, when you run 'UPDATE STATISTICS...' and then
press' Cancel Query Execution', the transaction is terminated within the
same thread. Could you run the Stats_date of DBCC SHOWSTATISTICS to check
that, if the 'UPDATE STATISTICS' is running and you press the 'Cancel Query
Execution' button, is the 'UPDATE STATISTICS' rolled back'
When you kill a SPID, a kill command is sent to the SPID, that is, one
thread will send a KILL command to another thread, it is not guaranteed
that the SPID is killed. A spid may respond to the KILL command
immediately, or after a delay, or not at all.
Please refer to the following KB:
INF: Understanding How the Transact-SQL KILL Command Works
http://support.microsoft.com/?id=171224
When you kill a SPID running 'UPDATE STATISTICS, could you check the status
by DBCC PSS? What the status of it? When you restart the SQL Server Service
and restart it, when using Stats_date of DBCC SHOWSTATISTICS, how about the
'Updated' time?
I am looking forward to your reply. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Thanks for you reply. I suggest that you try to reproduce the scenario by
yourself (It is not difficult to do). By the way, the article you motioned
is apply to SQL Server 6.0 and 6.5.
Lijun
"Baisong Wei[MSFT]" <v-baiwei@.online.microsoft.com> wrote in message
news:IkW$Pnt7DHA.568@.cpmsftngxa07.phx.gbl...
> Hi Lijun,
> Thank you for your update and it is my pleasure to help you with you
issue.
> I need to make some explanation here. When a transaction is terminated
> before it completed, it must be roll back. Also, when a transaction is
> cancelled, the rollback time will not based on how long this transation it
> has run. In Query Analyzer, when you run 'UPDATE STATISTICS...' and then
> press' Cancel Query Execution', the transaction is terminated within the
> same thread. Could you run the Stats_date of DBCC SHOWSTATISTICS to check
> that, if the 'UPDATE STATISTICS' is running and you press the 'Cancel
Query
> Execution' button, is the 'UPDATE STATISTICS' rolled back'
> When you kill a SPID, a kill command is sent to the SPID, that is, one
> thread will send a KILL command to another thread, it is not guaranteed
> that the SPID is killed. A spid may respond to the KILL command
> immediately, or after a delay, or not at all.
> Please refer to the following KB:
> INF: Understanding How the Transact-SQL KILL Command Works
> http://support.microsoft.com/?id=171224
> When you kill a SPID running 'UPDATE STATISTICS, could you check the
status
> by DBCC PSS? What the status of it? When you restart the SQL Server
Service
> and restart it, when using Stats_date of DBCC SHOWSTATISTICS, how about
the
> 'Updated' time?
> I am looking forward to your reply. Thanks.
> Best regards
> Baisong Wei
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>|||Hi Lijun,
Thank you very much for your update!
Yes, I create a huge database and tested what you have done. Yes, you are
right that when using the KILL to kill the SPID running 'UPDATE
STATISTICS', although SP_WHO2 showed the command is KILLED/ROLLBACK and I
could even got the information indicating that it is rolled back 100% and
estimated time for roll back is 1 second. However, the 'UPDATE STATISTICS'
is not stopped by the KILL command.
I tried to figure it out and it really took me some time. Later, I
discussed it with my senior engineers in my team and I got the some
information of it. It is a known issue of SQL Server. I should appologize
that in my last reply to your question, I did not provide you the right
answer, and I did not get the information of it in time. Could I provide
some more assistance this time. I mean if you have any bussiness impact
from it, please feel free to post here and I would try my best to help you!
Thank you very much.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.