Foreach loop for DBASE IV problem

I've followed the steps on how to use the ForEach loop container using a Flat Text file and it's working fine. I tried reading the text file and put it in my SQL table. However, I have some DBF files as well and the ForEach loop does not work at all with my DBF files.

Here is my scenario:

A connection manager pointed to C:\DBF_FILES

On the Control Flow: a Foreach loop container with Foreach file enumerator. (checks for IFUL*.DBF files and retrieves the Fully qualified name.

Within the foreach loop container is my DataFlow (import)

Within the dataflow:

OLEDB datasource
data access mode: Table name or View Name variable
Variable nam: username::Filefound

Now from this point on, I'm already having problems, here is the error:

An OLEDB error has occured.
An OLEDB record is available. Source: "Microsoft OLE DB Provider for ODBC Drivers.
Data source name not found and no default driver specified.
Error at data flow task...

There is also some error that say i don't have a destination table specified. Plus the error is on the OLEDB datasource not having a destination table? I don't understand this part.

Please help!

From what I understand of your post in my thread, I think what you want to do is simple. On the Foreach loop set the Folder to C:\DBF_FILES, set Files to *.dbf and select the "Name only" radio box in teh Retrieve file name.

That should set your configured variable in the Variable mapping to the name of the dbf file for each iteration. In your DataFlow set the data access mode to "Table name of view name variable" and the variable to the one you used in the Foreach variable mapping.

|||

Oh you should note, that you can only open one type of DBF file with that OLE DB Source. Meaning, all the files will need to have the same schema. If you have files with different content your going to either have to select them and handle the differences before this part. I have a couple ideas but it really depends on what you situation is.

|||

I think I see your problem, I actually put something together really quick to test that idea. I'll have to think about it a bit more later.

|||

Thanks Dan,

Yup, i only have the same schema for all the DBF files that I'm trying to retrieve. If I test the data flow with an implicit database name, it works fine. It's when I put it to the Foreach loop that I'm having the problem.

When I test the foreach loop with flat file, it works fine, but when I use the OLEDB, that's when it fails, I think I am following everything that needs to be done, it's just that all the samples for foreach loop has flat files for data source, and I expect it to be the same to the other types of data source. Maybe there is a missing link when the data source is not a flat file...

|||

Opps, I didn't have my Connection manager pointing to the right directory. What I described does in fact work. I'll try and post up some screen captures of how I have the package setup and perhaps you can see what isn' t configured right on your end.

This is about what I can write-up at the momment.

http://www.infrandom.com/42/Blog/tabid/53/EntryID/2/Default.aspx

|||

I've been doing and followed the steps exactly as you have laid out. My problem is on the OLEDB source. When I fil up the: OLEDB Connection Manager, data source mode and the variable name, when I click OK, it gives me the error:

Error at Data Flow task [OLE DB Source(431)] A destination table name has not been provided.

I really really appreciate the screen caps that you have provided. It really helps a lot. However, it is on that OLEDB source that I can't click OK due to the error, although my screen looks exactly like yours already.

Is there perhaps something that's not installed on my machine?

Thanks a lot.

|||

Another thing that I'm playing around just to be able to fix this is giving my variable a valid initial value. I am sure that the DBF file exists in the list but the error that I get when I do that is:

Opening a rowset for 'TBLNAME' failed. Check that the object exist in the database.

I tried TBLNAME.DBF, I tried TBLNAME only, but it just can't find it.

I made DBASE IV a version V but it said couldn't find installable ISAM. So I put back the DBASE IV.

On OLEDB Source:

The thing is I made my table access mode as table or view, and I choose TBLNAME from the list, I am fine. The problem is when i make my table access mode as 'table name or view name variable' and on the variable, i select my variable with my initial value as TBLNAME, it just can't find it.

I'm already lost. I gotta get some fresh air.

|||

View the variables, if you scroll the pane out, you can set the initial value of the variable in the designer. Give it a valid file name for a file in the directory. (There has to be a file present in the directory design time at least).

|||

I got that error when my Foreach loop directory and my Connection Manager directory were not point to the same directory. Once I updated to connection manager to the same directory I was able to run the package. Again, once you set the detault value of the variable just name no extension I think you should have it.

|||

Whew!!!

sometimes, a fresh air is really what it takes to get the mind cleared out.

Yup, I give my variable a valid file name. The problem was that only the DBF files were in that directory, not the index files. So I copied the index files as well, and it's now doing it's thing.

I followed everything that you have laid out on your screen caps.

Added a valid value to the variable. Made sure that the name retrieval is set to 'NAME only'. I set it to 'fully qualified path' at first, which contributed to the error. And finally, make sure that both the DBF and CDX files are present.

THANKS A LOT!!!

Really appreciate it.

|||

Glad you got it working!

sql

ForEach file in folder - missing from dropdown list

I'm working through the SSIS tutorial, and am on lesson 2.
This says
"Double-click Foreach File in Folder to reopen the Foreach Loop Editor.

Click Collection.

On the Collection page, select Foreach File Enumerator."

The drop down only contains

foreach ado enumerator
foreach ado.net schema_rowset.enumerator
foreach from variable.enumerator
foreach nodelist.enumerator
foreach smo.enumerator

How do I get the "foreach file in folder" to appear in the drop down list?

Gary
A quick search of the forum will yield you the answer to your question.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1461435&SiteID=1|||

Mmmn,

I got our technical support team to load the sql server service pack 2 to fix another problem a couple of days ago.

This has not cured this problem (but did fix the other problem I was having).

Funny enough, both problems indicated that certain registry keys could not be read..

I'll get our technical support team to set up a restricted user and run the C# program the knowledgebase article gives.

Gary

Force memory release

Hi all,
in SQL Server 2005 is it possible to force the SQL Server runtime to release
memory when it's not working?
Thanks a lot.
--
LuigiLuigi
If some application/os needs e memory , SQL Server will release it , if you
set up MIN server memory BOL says
/*
The database engine will not free any of the acquired memory until it
reaches the amount specified in min server memory. Once min server memory is
reached, the database engine then uses the standard algorithm (keeping the
operating system's free memory within 4 MB to 10 MB) to acquire and free
memory as needed
*/
"Luigi" <ciupazNoSpamGrazie@.inwind.it> wrote in message
news:979ED183-7761-477A-89B8-E8FB11EF7DA1@.microsoft.com...
> Hi all,
> in SQL Server 2005 is it possible to force the SQL Server runtime to
> release
> memory when it's not working?
> Thanks a lot.
> --
> Luigi
>|||"Luigi" <ciupazNoSpamGrazie@.inwind.it> wrote in message
news:979ED183-7761-477A-89B8-E8FB11EF7DA1@.microsoft.com...
> Hi all,
> in SQL Server 2005 is it possible to force the SQL Server runtime to
> release
> memory when it's not working?
> Thanks a lot.
> --
> Luigi
>
Not exactly. SQL Server will dynamically allocate and deallocate memory as
it needs it. If you find other applications are being starved for memory,
there are a couple of things you can do.
1. In my opinion, a production SQL Server should live on it's own box and
that box should be doing nothing other than SQL Server.
2. If you can't do this, then take a look at the max server memory option.
This will limit the amount of memory SQL Server can use for it's own use.
Other potential memory saving options might be to clear/drop the buffer
cache and procedure cache. I don't know if this will force SQL to give up
the memory. The downside of course is that any sprocs and other saved
execution plans will be forced to recompile. In addition, any indexes and
data pages that had been put into buffer for speedy access will now need to
be reloaded, which means more i/o which of course means slower response
times.
Rick Sawtell|||Ok, very exaustive. Thanks a lot Uri and Rick.
Luigi

Force encryption on server not working?

Hi,

I am at a loss here, unless I misunderstand the whole point about server encryption. My 2005 SQL server has a certificate from a trust CA, I have turned on the 'force encryption' flags on the server. My understanding is any client will be "force" to connect with encryption? I found out that unless I turn on encryption on my clients, the server will allow connections without the requiring encryption. Am I missing something here? Thanks for any help you can provide.

Hi anhtuan,

Your understanding is correct. If you force encryption on the server, then all connections from clients will be encrypted; if the client doesn't support encryption (e.g., very old MDAC clients), then the server will reject the connection. Basic question -- I assume that you restarted the server after setting the FORCE ENCRYPTION setting?

Il-Sung.

Force Encryption Login not working

Hi,
i've enabled the certification authentication login i use SQL 2K5 ENT
my problem is like that: in the client network utility i check the Force protocol encryption and connect
to the server the client can't connect because he doesn't have the certificate install on his machine which it's OK.
the problem is when i remove the Force protocol encryption on the client he connect successfully without the
certificate install on his machine or any thing.
i double check that on the server i've YES on the Force Encryption in the protocols For MSSQLSERVER.
why is that?

THX

Hi Avi,

To clarify, SQL Server does not support any certificate authentication -- certificates are used to facilitate SSL encryption only. When encryption is forced on the client-side, the client will perform server validation to ensure that the certificate came from the correct host. In order for this validation to succeed, the client must trust the root signing authority of the certificate. If the server, and not the client, enforces encryption, then the client will not perform server validation. Furthermore, if a certificate hasn't been provisioned on the server machine, the server will use its self-generated cert for channel encryption. Obviously, the self-generated cert would fail server validation (see this post for more details: http://blogs.msdn.com/dataaccess/archive/2005/08/05/448401.aspx) so this is something to watch out for when forcing client-side encryption. I believe that this explains the behaviour that you are seeing.

Il-Sung.

For XML Problem with IIS6 and W2k3

Did anyone ever resolve this? I am having the exact same issue...returning
results from a FOR XML procedure to an ado stream object stops working every
several days.
"ajsmith02" wrote:

> I have this function that worked like a charm under IIS5 and W2K. You pas
s a
> sql string that has for xml auto or a stored produre that has for xml auto
in
> it. Under IIS6 and W2K3 it stops working after a couple of days with no
> rhyme or reason. No error log either. We applied all the service packs
> including sqlxml sp3. What is wrong? Thanks.
> Here is the code:
> function getSQLXML(byval sqlString)
> dim adoConn
> dim adoCmd
> dim adoStreamQuery
> set adoConn = vbsqlconnection 'located in sharedfunctions.asp
> adoConn.CommandTimeout = 300
> set adoStreamQuery = Server.CreateObject("ADODB.Stream")
> set adoCmd = Server.CreateObject("ADODB.Command")'
> adoCmd.ActiveConnection = adoConn
> adoCmd.CommandTimeout = 300
> adoConn.CursorLocation = adUseClient
> dim sQuery
> sQuery = "<recordset xmlns:sql='urn:schemas-microsoft-com:xml-sql'>"
> sQuery = sQuery + "<sql:query>"+sqlString+"</sql:query>"
> sQuery = sQuery + "</recordset>"
> adoStreamQuery.Open 'Open the command stream so it may be written to
> adoStreamQuery.WriteText sQuery, adWriteChar 'Set the input command
> stream's text with the query string
> adoStreamQuery.Position = 0 'Reset the position in the stream, otherwis
e
> it will be at EOS
> adoCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}" 'Set the
> dialect for the command stream to be a SQL query.
> adoCmd.CommandStream = adoStreamQuery 'Set the command object's comma
nd
> to the input stream set above
> dim outStrm
> set outStrm = Server.CreateObject("ADODB.Stream") 'Create the output
> stream
> outStrm.Open
> adoCmd.Properties("Output Stream").Value = outStrm 'Set command's output
> stream to the output stream just opened
> adoCmd.Execute , , adExecuteStream
> 'Response.Write(outStrm.ReadText)
> adoCmd.ActiveConnection = nothing
> adoConn.Close
> set adoConn = nothing
> getSQLXML = outStrm.ReadText
> end function
> P.S. Goorbeeman in the group microsoft.public.sqlserver.server has the sam
e
> problemNobody helped me. It turned out to be a blessing in disguise. I wound up
creating a component using .Net to pass through the "For XML" sql statements
and return the xml in for of a text stream. After I created the component I
Com Wrapper (using .NET) so that my old asp page to use it.
"ashort" wrote:
> Did anyone ever resolve this? I am having the exact same issue...returnin
g
> results from a FOR XML procedure to an ado stream object stops working eve
ry
> several days.
> "ajsmith02" wrote:
>|||Folks,
Has ANYONE solved this problem? I am getting "Catastrophic failure" message
s and Err.Number = -2147418113 from my ASP pages, consistently.
Thanks in advance...
- Tom

quote:

---

Originally posted by ashort
[B]Did anyone ever resolve this? I am having the exact same issue...returning
results from a FOR XML procedure to an ado stream object stops working every
several days.
"ajsmith02" wrote:
> I have this function that worked like a charm under IIS5 and W2K. You pas
s a
> sql string that has for xml auto or a stored produre that has for xml auto
in
> it. Under IIS6 and W2K3 it stops working after a couple of days with no
> rhyme or reason.

---

F|||Hi,
Could anyone reproduce the problem with a generic ASP page against e.g.
Northwind database? If so, please send me the code if possible. I have the
following ASP page and run it for days on Windows Server 2003 and SQLXML3
SP3, but didn't see it hanging using a stress app.
Also, Windows Server 2003 SP1 can be downloaded, so if someone could give it
a try, it would be great.
<!--#include file="common.inc"-->
<% Response.ContentType = "text/xml" %>
<object id="conn" progid="ADODB.Connection" runat="Server"></object>
<%
Dim strSQL, getSQLXML, strSQLXML, objCmd
dim outStrm
set outStrm = Server.CreateObject("ADODB.Stream") 'Create the output
stream
outStrm.Open
strSQL = "<sql:query>select * from orders for xml auto</sql:query>"
strSQLXML = "<?xml version=""1.0"" ?><root
xmlns:sql='urn:schemas-microsoft-com:xml-sql'>" & strSQL & "</root>"
conn.Open strConnNew
Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = conn
objCmd.CommandText = strSQLXML
objCmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}"
objCmd.Properties("Output Stream").Value = outStrm
objCmd.Execute , , 1024
outStrm.Position = 0
outStrm.Charset = "utf-8"
getSQLXML = outStrm.ReadText(-1)
outStrm.Close
Response.Write("<root/>")
%>
thx,
-kuen
This posting is provided "AS IS" with no warranties, and confers no rights.
"TomKelleher" wrote:

> Folks,
> Has ANYONE solved this problem? I am getting "Catastrophic failure"
> messages and Err.Number = -2147418113 from my ASP pages, consistently.
>
> Thanks in advance...
> - Tom
> ashort wrote:
> F
>
> --
> TomKelleher
> ---
> Posted via http://www.codecomments.com
> ---
>|||Thanks for the explanation. I am checking with the MDAC team to see if they
are aware of the issue.
Thanks
Michael
<spamgone@.cox.net> wrote in message
news:1114110540.157389.251100@.g14g2000cwa.googlegroups.com...
>I have started experiencing the same problem when my company switched
> to the Windows 2003 servers. Since the switch, our asp web pages that
> contain the adodb.stream objects will randomly lockup until the
> application pool is recycled in IIS. Then everything will run smoothly
> for a few days. All ASP pages that don't contain the adodb.stream
> object will continue to work as normal, when the lockup occurs. No
> errors are generated in the logs and the web server and database server
> doesn't show any signs of heavy memory or CPU usage. You can even
> run the "FOR XML AUTO" stored procedures in query analyzer without
> any errors during this time. To try and prevent the problem we setup
> the application pools to be recycled nightly, but that doesn't appear
> to make any difference. We have Microsoft SQL Server on a different
> machine then our web server, but I doubt that makes a difference. The
> offending ASP pages were working perfectly fine without any issues when
> the server was still Windows 2000 Server.
> Here is the basics of the offending ASP pages:
> <%@. Language=VBScript %>
> <%Option Explicit%>
> <%
> Dim objCommand,objXML,objStream,objRoot
>
> Set objCommand = Server.CreateObject("ADODB.Command")
> Set objXML = Server.CreateObject("MSXML2.DomDocument")
> Set objStream = Server.CreateObject("ADODB.Stream")
>
> objStream.Open
> With objCommand
> .ActiveConnection = "Provider=SQLOLEDB; Data Source=ExampleServer;
> Network Library=DBMSSOCN; Initial Catalog=ExampleDB; User Id=Example;
> Password=Example"
> .CommandType = adCmdStoredProc
> .CommandText = "SP_Example"
> .Properties("Output Stream") = objStream
> .Execute ,, adExecuteStream
> End With
> objXML.loadXML("<root>" & objStream.ReadText & "</root>")
> Set objRoot = objXML.documentElement
> %>
> I have not included the rest of the code that displays the returned
> data because the page does not appear to get past the Execute
> statement.
> The stored procedure is basically:
> CREATE PROCEDURE SP_Example
> AS
> SELECT ExampleID, ExampleName from ExampleTable
> FOR XML AUTO
> GO
> Any suggestions, beyond recoding every web page, would be greatly
> appreciated.
> Thanks.
> Paul
>|||Any result from the MDAC team? I am fighting the same issue, and would be
very interested in a solution or workaround.
"Michael Rys [MSFT]" wrote:

> Thanks for the explanation. I am checking with the MDAC team to see if the
y
> are aware of the issue.
> Thanks
> Michael
> <spamgone@.cox.net> wrote in message
> news:1114110540.157389.251100@.g14g2000cwa.googlegroups.com...
>
>|||MDAC team hasn't seen the problem you describe in our internal tests.
Does this page lockup mean that the thread executing the script hangs?
If so is it possbile to get the stack trace and the version information for
the modules involved?
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bob Hobnob" wrote:
> Any result from the MDAC team? I am fighting the same issue, and would be
> very interested in a solution or workaround.
> "Michael Rys [MSFT]" wrote:
>|||Below I have copied my post from the data.ado group - it contains my ASP cod
e
and the IISState log for the problem thread. I can provide module versions,
if you wouldn't mind telling me which dll's to examine. I did just install
the MDAC 2.8 upgrade, but the behavior was the same before as after. I'm no
t
sure how to provide a stack trace, but I'll see if I can Google something on
it. I'd be happy to provide anything I can to help solve this issue.
//bob
--snip--
Like many others, I am struggling with a problem in Win2K3 and IIS6 using an
ADO Stream object and an SQL FOR XML stored procedure. Let me preface by
saying that the ASP code has worked flawlessly on Win2K and IIS 5 for over a
year. Now on Win2K3, the page will work for a while, then it becomes
unresponsive. It returns no error, it just hangs the connection so that no
other site page will respond until the browser is closed and re-opened.
Sometimes recycling the app pool will clear it up temporarily, sometimes I
have to restart IIS. Page will work fine for a few hours or even days, then
it stops responding. CPU and memory utilization seem normal in TaskMan.
First, here's the ASP code:
<%
dim cn
set cn = server.CreateObject("ADODB.Connection")
cn.open MM_iiWeb_STRING
dim result
result = getSQLXML(cn, "exec proc_getLinkCatXML", "linkpage.xsl", ".")
response.Write result
if cn.state=1 then cn.close()
set cn = nothing
%>
<%
function getSQLXML(byref cn, byval sql, byval xslfile, byval basepath)
dim cmd
dim objOutStream
Set objOutStream = Server.CreateObject("ADODB.Stream")
objOutStream.open
Set cmd = Server.CreateObject("ADODB.Command")
cmd.ActiveConnection = cn
cmd.CommandText = sql
cmd.Properties("Base Path").Value = Server.MapPath(basepath)
cmd.Properties("XML Root") = "root"
cmd.Properties("XSL") = xslfile
cmd.Properties("Output Stream") = objOutStream
cmd.Execute , , adExecuteStream
getSQLXML = objOutStream.ReadText
objOutStream.Close
set objOutStream = nothing
set cmd = nothing
end function
%>
When it hangs, here is IISState log info for the thread:
Thread ID: 15
System Thread ID: bc0
Kernel Time: 0:0:0.109
User Time: 0:0:0.968
Thread Type: ASP
Executing Page: C:\INETPUB\WWWROOT\IIWEB\TEMPLATES\LINKS
.ASP
# ChildEBP RetAddr
00 025de3d8 77f4262b SharedUserData!SystemCallStub+0x4
01 025de3dc 77e418ea ntdll!NtDelayExecution+0xc
02 025de444 77e416ee kernel32!SleepEx+0x68
03 025de450 0439c4f9 kernel32!Sleep+0xb
04 025de464 04382a20 mo15!CQuery::Cancel+0x50
05 025de49c 0437d84d mo15!CCommand::Cancel+0x58
06 025de4ac 0437516f mo15!CCommand::Term+0xd
07 025de4c8 04373951 mo15!CStdSymbiontObject::InternalRele
ase+0x6c
08 025de4d8 7710736a mo15!ATL::CComObject<CRecordset>::Release+0x11
09 025de4e8 7346384b OLEAUT32!VariantClear+0xad
0a 025de4fc 734641cf vbscript!VAR::Clear+0xab
0b 025de50c 7346416a vbscript!CScriptRuntime::Cleanup+0x59
0c 025de84c 73465184 vbscript!CScriptRuntime::Run+0x2ccc
0d 025de504 80020102 vbscript!CScriptRuntime::Run+0x99
WARNING: Frame IP not in any known module. Following frames may be wrong.
0e 025de504 80020102 0x80020102
0f 00000000 00000000 0x80020102
I have seen the same problem posted several times in various newsgroups and
message boards for the past 8 months or so, but I have seen no explaination,
solution, or workaround offered. I would appreciate any insights.
//bob
--end snip--
"Anton Klimov [MS]" wrote:

> MDAC team hasn't seen the problem you describe in our internal tests.
> Does this page lockup mean that the thread executing the script hangs?
> If so is it possbile to get the stack trace and the version information fo
r
> the modules involved?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||The stack you provided shows a problem with releasing a pointer to a rowset.
However the code fragment you posted does not have any rowsets defined,
unless I'm missing something.
You should see where you might have recordsets created with asynchronous
execution.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bob Hobnob" wrote:

> Below I have copied my post from the data.ado group - it contains my ASP c
ode
> and the IISState log for the problem thread. I can provide module version
s,
> if you wouldn't mind telling me which dll's to examine. I did just instal
l
> the MDAC 2.8 upgrade, but the behavior was the same before as after. I'm
not
> sure how to provide a stack trace, but I'll see if I can Google something
on
> it. I'd be happy to provide anything I can to help solve this issue.
> //bob
> --snip--
> Like many others, I am struggling with a problem in Win2K3 and IIS6 using
an
> ADO Stream object and an SQL FOR XML stored procedure. Let me preface by
> saying that the ASP code has worked flawlessly on Win2K and IIS 5 for over
a
> year. Now on Win2K3, the page will work for a while, then it becomes
> unresponsive. It returns no error, it just hangs the connection so that n
o
> other site page will respond until the browser is closed and re-opened.
> Sometimes recycling the app pool will clear it up temporarily, sometimes I
> have to restart IIS. Page will work fine for a few hours or even days, th
en
> it stops responding. CPU and memory utilization seem normal in TaskMan.
> First, here's the ASP code:
> <%
> dim cn
> set cn = server.CreateObject("ADODB.Connection")
> cn.open MM_iiWeb_STRING
> dim result
> result = getSQLXML(cn, "exec proc_getLinkCatXML", "linkpage.xsl", ".")
> response.Write result
> if cn.state=1 then cn.close()
> set cn = nothing
> %>
> <%
> function getSQLXML(byref cn, byval sql, byval xslfile, byval basepath)
> dim cmd
> dim objOutStream
> Set objOutStream = Server.CreateObject("ADODB.Stream")
> objOutStream.open
> Set cmd = Server.CreateObject("ADODB.Command")
> cmd.ActiveConnection = cn
> cmd.CommandText = sql
> cmd.Properties("Base Path").Value = Server.MapPath(basepath)
> cmd.Properties("XML Root") = "root"
> cmd.Properties("XSL") = xslfile
> cmd.Properties("Output Stream") = objOutStream
> cmd.Execute , , adExecuteStream
> getSQLXML = objOutStream.ReadText
> objOutStream.Close
> set objOutStream = nothing
> set cmd = nothing
> end function
> %>
> When it hangs, here is IISState log info for the thread:
> Thread ID: 15
> System Thread ID: bc0
> Kernel Time: 0:0:0.109
> User Time: 0:0:0.968
> Thread Type: ASP
> Executing Page: C:\INETPUB\WWWROOT\IIWEB\TEMPLATES\LINKS
.ASP
> # ChildEBP RetAddr
> 00 025de3d8 77f4262b SharedUserData!SystemCallStub+0x4
> 01 025de3dc 77e418ea ntdll!NtDelayExecution+0xc
> 02 025de444 77e416ee kernel32!SleepEx+0x68
> 03 025de450 0439c4f9 kernel32!Sleep+0xb
> 04 025de464 04382a20 mo15!CQuery::Cancel+0x50
> 05 025de49c 0437d84d mo15!CCommand::Cancel+0x58
> 06 025de4ac 0437516f mo15!CCommand::Term+0xd
> 07 025de4c8 04373951 mo15!CStdSymbiontObject::InternalRele
ase+0x6c
> 08 025de4d8 7710736a mo15!ATL::CComObject<CRecordset>::Release+0x11
> 09 025de4e8 7346384b OLEAUT32!VariantClear+0xad
> 0a 025de4fc 734641cf vbscript!VAR::Clear+0xab
> 0b 025de50c 7346416a vbscript!CScriptRuntime::Cleanup+0x59
> 0c 025de84c 73465184 vbscript!CScriptRuntime::Run+0x2ccc
> 0d 025de504 80020102 vbscript!CScriptRuntime::Run+0x99
> WARNING: Frame IP not in any known module. Following frames may be wrong.
> 0e 025de504 80020102 0x80020102
> 0f 00000000 00000000 0x80020102
> I have seen the same problem posted several times in various newsgroups an
d
> message boards for the past 8 months or so, but I have seen no explainatio
n,
> solution, or workaround offered. I would appreciate any insights.
> //bob
> --end snip--
> "Anton Klimov [MS]" wrote:
>
>|||The code I posted is the complete content of the "links.asp" problem page.
This page is called via an ASP Server.Execute command inside another page.
This "parent" page does have 2 database routines of it's own - both use the
ADODB.Connection "Execute" method to run stored procedures that return
recordsets into variables (no recordset objects explicitly created). As far
as I know, the default "Execute" behavior is synchronous, yes? I certainly
am not specifying the asynchronous option. After the recordsets are
returned, I explicitly close and "nothing" the recordsets, then close and
"nothing" the connection object. This code is all supposed to run and
complete before the "links.asp" page gets executed.
Does any of this info raise a red flag?
//bob
"Anton Klimov [MS]" wrote:

> The stack you provided shows a problem with releasing a pointer to a rowse
t.
> However the code fragment you posted does not have any rowsets defined,
> unless I'm missing something.
> You should see where you might have recordsets created with asynchronous
> execution.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>

FOR XML not working in a subquery

The following seems to work in SQL Server 2005, but I'm getting the
error 'Incorrect syntax near xml' when I run it in SQL Server 2000.

select a.accountid , (select street, city, state, zip from account b
where a.accountid =b.accountid for xml auto, elements) as xmldata
from account a

The idea is to return 2 columns:
accountid
xmldata (address as xml)

Assuming the fields are correct, any ideas on what the problem might be?(jonathaneggert@.hotmail.com) writes:

Quote:

Originally Posted by

The following seems to work in SQL Server 2005, but I'm getting the
error 'Incorrect syntax near xml' when I run it in SQL Server 2000.
>
>
select a.accountid , (select street, city, state, zip from account b
where a.accountid =b.accountid for xml auto, elements) as xmldata
from account a
>
The idea is to return 2 columns:
accountid
xmldata (address as xml)
>
Assuming the fields are correct, any ideas on what the problem might be?

The problem is simply that you try to achieve something which is not
possible in SQL 2000.

--
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|||Well thanks for such a detailed explanation of the answer.

Why can't this be done in SQL Server 2000? It is a sub-query which I
have used extensively in SQL Server 2000--why the problem with FOR XML?

Erland Sommarskog wrote:

Quote:

Originally Posted by

(jonathaneggert@.hotmail.com) writes:

Quote:

Originally Posted by

The following seems to work in SQL Server 2005, but I'm getting the
error 'Incorrect syntax near xml' when I run it in SQL Server 2000.

select a.accountid , (select street, city, state, zip from account b
where a.accountid =b.accountid for xml auto, elements) as xmldata
from account a

The idea is to return 2 columns:
accountid
xmldata (address as xml)

Assuming the fields are correct, any ideas on what the problem might be?

>
The problem is simply that you try to achieve something which is not
possible in SQL 2000.
>
>
--
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

|||(jonathaneggert@.hotmail.com) writes:

Quote:

Originally Posted by

Well thanks for such a detailed explanation of the answer.
>
Why can't this be done in SQL Server 2000? It is a sub-query which I
have used extensively in SQL Server 2000--why the problem with FOR XML?

In SQL 2000, FOR XML can only be used in the outermost SELECT, to
produce a one-row, one-column result set. It cannot be used in subqueries,
derived tables. A good reason for this is that in SQL 2000, there is
not really any xml data type. Yet the result set returned by a FOR
XML clause is not really any of the SQL Server data types - it's XML.
It works thanks to some special hooks in the client APIs that can see
that here comes a one-row, one-column result set, which is an XML
document. There is no plumbing to permit FOR XML be composed with other
sorts of data.

This is all different in SQL 2005, where XML is a first-class citizen.

See also Books Online, the topic
XML and Internet Support ->
Retrieving and Writing XML Data ->
Retrieving XML Documents Using FOR XML ->
Guidelines for Using the FOR XML Clause

this topic lists a number of limitations with FOR XML.

--
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

FOR XML EXPLICIT and XML HEADER

I have a FOR XML EXPLICIT
statement that is working correctly in every way except for the VERSION
header at the very top.
<?xml version="1.0" encoding="utf-8"?>
Is there anyway to get SQL Server to put this into the returned results?
Thank you
Chris
Since version 1.0 is the default and the encoding is implied by the
resulting codepage, FOR XML itself does not add any of that. You however
should be able to set the encoding through the client-side API (such as the
ADO command stream object).
Best regards
Michael
"Chris Alm" <none@.none.com> wrote in message
news:%23SiV0W3aFHA.2180@.TK2MSFTNGP12.phx.gbl...
>I have a FOR XML EXPLICIT
> statement that is working correctly in every way except for the VERSION
> header at the very top.
> <?xml version="1.0" encoding="utf-8"?>
> Is there anyway to get SQL Server to put this into the returned results?
> Thank you
> Chris
>

FOR XML EXPLICIT and XML HEADER

I have a FOR XML EXPLICIT
statement that is working correctly in every way except for the VERSION
header at the very top.
<?xml version="1.0" encoding="utf-8"?>
Is there anyway to get SQL Server to put this into the returned results?
Thank you
ChrisSince version 1.0 is the default and the encoding is implied by the
resulting codepage, FOR XML itself does not add any of that. You however
should be able to set the encoding through the client-side API (such as the
ADO command stream object).
Best regards
Michael
"Chris Alm" <none@.none.com> wrote in message
news:%23SiV0W3aFHA.2180@.TK2MSFTNGP12.phx.gbl...
>I have a FOR XML EXPLICIT
> statement that is working correctly in every way except for the VERSION
> header at the very top.
> <?xml version="1.0" encoding="utf-8"?>
> Is there anyway to get SQL Server to put this into the returned results?
> Thank you
> Chris
>

For XML Explicit

k. I'm going crazy here. I have some for xml explicit sql working (created a
test against the adventureworks database) but how can I get the results of
this sql into an xml variable. I have tried inserting it into a table
variable or setting an xml variable equal to it...I can't get it to
working...any help given is appreciated.
Select 1 as Tag
,null as Parent
,null as [Audit!1]
,'Test' as [Audit!1!type]
,null as [TestText!2]
,null as [Contacts!3]
,null as [ContectId!4]
Union All
Select 2
,1
,null
,null
,'This is test text'
,null
,null
Union All
Select 3
,1
,null
,null
,null
,null
,null
Union All
Select 4
,3
,null
,null
,null
,null
,ContactId
from Person.Contact
where contactId between 1 and 10
For XML ExplicitHello JI,
J> k. I'm going crazy here. I have some for xml explicit sql working
J> (created a test against the adventureworks database) but how can I
J> get the results of this sql into an xml variable. I have tried
J> inserting it into a table variable or setting an xml variable equal
J> to it...I can't get it to working...any help given is appreciated.
SQL 2000 or 2005? In 2005 this should work:
declare @.x xml;set @.x = (select * from (select 1 as Tag,null as Parent,null
as [Audit!1],'Test' as [Audit!1!type],null as [TestText!2],null as [Contacts
!3],null
as [ContectId!4] Union All Select 2,1,null,null,'This is test text',null,nul
l
Union All Select 3,1,null,null,null,null,null Union All Select 4,3,null,null
,null,null,ContactId
from Person.Contact where contactId between 1 and 10) as c for xml explicit,
type);select
@.x;
In 2000, XML aggreation of the UT occurs post query, so you couldn't do that
there.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||It is 2k5.
Thank you Kent!
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad741ea2a8c8432165f54180@.news.microsoft.com...
> Hello JI,
> J> k. I'm going crazy here. I have some for xml explicit sql working
> J> (created a test against the adventureworks database) but how can I
> J> get the results of this sql into an xml variable. I have tried
> J> inserting it into a table variable or setting an xml variable equal
> J> to it...I can't get it to working...any help given is appreciated.
> SQL 2000 or 2005? In 2005 this should work:
> declare @.x xml;set @.x = (select * from (select 1 as Tag,null as
> Parent,null as [Audit!1],'Test' as [Audit!1!type],null as
> [TestText!2],null as [Contacts!3],null as [ContectId!4] Union All Select
> 2,1,null,null,'This is test text',null,null Union All Select
> 3,1,null,null,null,null,null Union All Select
> 4,3,null,null,null,null,ContactId from Person.Contact where contactId
> between 1 and 10) as c for xml explicit,type);select @.x;
> In 2000, XML aggreation of the UT occurs post query, so you couldn't do
> that there.
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>

For Deletion..trigger Is Not Working

Hi,
I have this trigger, it is working fine when i add new data but it doesn't work when I delete data from the table?

Any idea?

Any help will be highly appreciated.

CREATE TRIGGER [PROP_AMT] ON [dbo].[cqe_item]
FOR INSERT, UPDATE, DELETE
AS
DECLARE
@.var_DB_contract INTEGER,
@.var_CQE INTEGER,
@.var_PC INTEGER,
@.var_item VARCHAR(7),
@.var_AMT_PAID INTEGER,
@.var_AMT_RET INTEGER,
@.var_ITEM_NEW VARCHAR(1),
@.var_quant DECIMAL,
@.var_fiyr INTEGER,
@.var_amt_result INTEGER,
@.var_amt_ret_result INTEGER,
@.var_amt_old INTEGER,
@.var_amt_ret_old INTEGER,
@.var_quant_result INTEGER,
@.var_quant_new INTEGER,
@.var_quant_old INTEGER,
@.Item_new VARCHAR(7),
@.var_chk varchar(1)

--If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)
set @.var_db_contract =(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
IF @.var_db_contract IS NOT NULL
BEGIN
SET @.var_db_contract=(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @.var_cqe=(SELECT a.cqe_numb FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @.var_pc=(SELECT a.pc_code FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @.var_item=(SELECT a.item_no FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @.var_fiyr=(SELECT a.fy_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
set @.var_chk ="Y"
END
ELSE
BEGIN
SET @.var_db_contract=(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @.var_cqe=(SELECT a.cqe_numb FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @.var_pc=(SELECT a.pc_code FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @.var_item=(SELECT a.item_no FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @.var_fiyr=(SELECT b.fy_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
set @.var_chk="N"
END
SET @.var_amt_paid=(SELECT a.amt_paid_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @.var_amt_old=(SELECT b.amt_paid_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @.var_amt_result =ISNULL(@.var_amt_paid,0) - ISNULL(@.var_amt_old,0)

SET @.var_amt_ret = (SELECT a.amt_ret_item from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @.var_amt_ret_old=(SELECT b.amt_ret_item from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @.var_amt_ret_result = isnull(@.var_amt_ret,0) - isnull(@.var_amt_ret_old,0)

SET @.var_quant_new = (SELECT a.quantity from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @.var_quant_old =(SELECT b.quantity from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @.var_quant_result = isnull(@.var_quant_new,0) - isnull(@.var_quant_old,0)
SELECT @.item_new = new_item
FROM VALID_ITEM
WHERE DB_CONTRACT = @.var_db_contract
AND PC_CODE = @.var_PC
AND ITEM_NO = @.var_ITEM

UPDATE ae_contract
set amt_paid_contr = isnull(amt_paid_contr,0) +@.var_amt_result,
amt_ret_contr = isnull(amt_ret_contr,0) + @.var_amt_ret_result
where db_contract = @.var_db_contract

IF @.item_new = 'N'
BEGIN
update vendor
set used_amt = isnull(used_amt,0) + @.var_amt_result + @.var_amt_ret_result
where db_vendor = (select gen_contr from ae_contract
where ae_contract.db_contract=@.var_db_contract);
END
UPDATE enc_det
set amt_paid_fy = isnull(amt_paid_fy,0) + @.var_amt_result,
amt_ret_fy = isnull(amt_ret_fy,0) + @.var_amt_ret_result
where db_contract = @.var_db_contract
and pc_code = @.var_pc
and fy = @.var_fiyr

UPDATE valid_item
set tamt_ret_item = isnull(tamt_ret_item,0) + @.var_amt_ret_result,
tamt_paid_item = isnull(tamt_paid_item,0) + @.var_amt_result,
qtd = isnull(qtd,0) + @.var_quant_result
where db_contract = @.var_db_contract
and pc_code = @.var_pc
and item_no = @.var_itemstrange logic...

on delete
inserted table will be empty

FROM inserted a,deleted b where a.db_contract = b.db_Contract and
a.cqe_numb = b.cqe_numb and
a.pc_code = b.pc_code and a.item_no = b.item_no

and what will You do if updated/deleted more then one records?
sorry for my English