FTP Task for SSIS missing

Hi Everyone,

When I first installed BI Studio everything was working fine, but I went into it today and I noticed that the FTP task seems to be missing, I can't see it in the toolbox, everything else appears to be their but not that.

Any idea how I can get this back, I really don't wont to uninstall and reinstall at the moment, and I'm not even sure if that would work.

ThanksRight-click the toolbox, Choose Items. Select the SSIS Control Flow page and have a look for any missing tasks, those not checked.

FTP task - StopOnFailure not working as I expect?

Hi

I'm using an FTP task in a loop to download files from a WAN based FTP server. I've got a script to generate the name of the file I require and place into a variable. Problem is sometimes some of the remote files haven't been published so the FTP component errors and the package fails.

I tried setting StopOnFailure to False - all I get is the following errors

[FTP Task] Error: File represented by "User::RemotePath" does not exist.
[FTP Task] Error: Attempt to read message string for 0x80020918 failed with error 0xc02090f3. Make sure all message related files are registered.

What I want to do is ignore the error and perform the next loop iteration

Is this a bug, or am I missing something

Dave

To ignore the ftp task error within a loop, put an OnError event handler on the FTP task. In the handler, set the System::Propagate variable to false. This setting will keep the error(s) in the ftp task from bubbling up the container hierarchy, and thus keep the loop from failing on a missing file.

For example:

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
Private vars As Variables
Public Sub Main()
Dts.TaskResult = Dts.Results.Success
Dts.VariableDispenser.LockForWrite("System::Propagate")
Dts.VariableDispenser.GetVariables(vars)
vars("System::Propagate").Value = False
vars.Unlock()
End Sub
End Class

P.S. Another way to ignore the ftp error is by configuring the MaximumErrorCount property on the loop executable to some relatively high number.
|||Excellent thanks, I initially set the MaximumErrorCount as a quick hack but thanks for the first suggestion - I've been meaning to figure out how OnError works.

|||

This sounds like the perfect solution for what I'm trying to do... but one question:

How do you "put an OnEvent handler on the ftp task"?

Not sure what this means.

Thanks much!!

|||

Ok, I figured out the event handler tab functionality.

So, for my FTP task, I created an OnError event handler. I put a script task in the event handler.

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
Private vars As Variables
Public Sub Main()
Dts.TaskResult = Dts.Results.Success
Dts.VariableDispenser.LockForWrite("System:ropagate")
Dts.VariableDispenser.GetVariables(vars)
vars("System:ropagate").Value = False
vars.Unlock()
End Sub
End Class

However, the FTP task is still failing as before, and failing the ForLoop container with it.

What am I doing wrong?

Thanks

|||

Hello,

I am still trying to figure out why FTP the error is still "bubbling up" to the ForLoop container?

This is what I have:

ForLoop [ FTP task --> script task ]

In the FTP task Event Handler tab, I created an "On Error" event handler. It is a script task with the above poster's code that is supposed to turn off the System propogate variable. However, it isn't working. What am I doing wrong?

I need to turn off error handers in the FTP task, so that when I get a file not found error, it doesn't cause the ForLoop container to fail also. It needs to keep looping, even if the file isn't there yet.

Thanks much!

|||

The system variable System:ropagate effects propagation of execution errors. Validation errors will always propagate at least one level up the container hierarchy, probably because the validation error of a task becomes an execution error of the for loop container.

So if an error occurs in the validation phase of the ftp task, the first time a System:ropagate=false error handler could catch it would be in the for each loop. Execution errors, on the other hand, can be caught be an error handler on the ftp task itself.

The two properties which also account for validation errors (which always bubble up at least one level) are ForceExecutionResult and MaximumErrorCount. By setting ForceExecutionResult=Sucess on the for each loop container or by setting MaximumErrorCount on the for each loop container to a large number, the loop will continue.

You can determine in which phase the the error(s) occur by observing if the error events fall exclusively between the OnPrevalidate / OnPostValidate events of the ftp task.

FTP task - StopOnFailure not working as I expect?

Hi

I'm using an FTP task in a loop to download files from a WAN based FTP server. I've got a script to generate the name of the file I require and place into a variable. Problem is sometimes some of the remote files haven't been published so the FTP component errors and the package fails.

I tried setting StopOnFailure to False - all I get is the following errors

[FTP Task] Error: File represented by "User::RemotePath" does not exist.
[FTP Task] Error: Attempt to read message string for 0x80020918 failed with error 0xc02090f3. Make sure all message related files are registered.

What I want to do is ignore the error and perform the next loop iteration

Is this a bug, or am I missing something

Dave

To ignore the ftp task error within a loop, put an OnError event handler on the FTP task. In the handler, set the System::Propagate variable to false. This setting will keep the error(s) in the ftp task from bubbling up the container hierarchy, and thus keep the loop from failing on a missing file.

For example:

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
Private vars As Variables
Public Sub Main()
Dts.TaskResult = Dts.Results.Success
Dts.VariableDispenser.LockForWrite("System::Propagate")
Dts.VariableDispenser.GetVariables(vars)
vars("System::Propagate").Value = False
vars.Unlock()
End Sub
End Class

P.S. Another way to ignore the ftp error is by configuring the MaximumErrorCount property on the loop executable to some relatively high number.
|||Excellent thanks, I initially set the MaximumErrorCount as a quick hack but thanks for the first suggestion - I've been meaning to figure out how OnError works.|||

This sounds like the perfect solution for what I'm trying to do... but one question:

How do you "put an OnEvent handler on the ftp task"?

Not sure what this means.

Thanks much!!

|||

Ok, I figured out the event handler tab functionality.

So, for my FTP task, I created an OnError event handler. I put a script task in the event handler.

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
Private vars As Variables
Public Sub Main()
Dts.TaskResult = Dts.Results.Success
Dts.VariableDispenser.LockForWrite("System:ropagate")
Dts.VariableDispenser.GetVariables(vars)
vars("System:ropagate").Value = False
vars.Unlock()
End Sub
End Class

However, the FTP task is still failing as before, and failing the ForLoop container with it.

What am I doing wrong?

Thanks

|||

Hello,

I am still trying to figure out why FTP the error is still "bubbling up" to the ForLoop container?

This is what I have:

ForLoop [ FTP task --> script task ]

In the FTP task Event Handler tab, I created an "On Error" event handler. It is a script task with the above poster's code that is supposed to turn off the System propogate variable. However, it isn't working. What am I doing wrong?

I need to turn off error handers in the FTP task, so that when I get a file not found error, it doesn't cause the ForLoop container to fail also. It needs to keep looping, even if the file isn't there yet.

Thanks much!

|||

The system variable System:ropagate effects propagation of execution errors. Validation errors will always propagate at least one level up the container hierarchy, probably because the validation error of a task becomes an execution error of the for loop container.

So if an error occurs in the validation phase of the ftp task, the first time a System:ropagate=false error handler could catch it would be in the for each loop. Execution errors, on the other hand, can be caught be an error handler on the ftp task itself.

The two properties which also account for validation errors (which always bubble up at least one level) are ForceExecutionResult and MaximumErrorCount. By setting ForceExecutionResult=Sucess on the for each loop container or by setting MaximumErrorCount on the for each loop container to a large number, the loop will continue.

You can determine in which phase the the error(s) occur by observing if the error events fall exclusively between the OnPrevalidate / OnPostValidate events of the ftp task.

FTP task - StopOnFailure not working as I expect?

Hi

I'm using an FTP task in a loop to download files from a WAN based FTP server. I've got a script to generate the name of the file I require and place into a variable. Problem is sometimes some of the remote files haven't been published so the FTP component errors and the package fails.

I tried setting StopOnFailure to False - all I get is the following errors

[FTP Task] Error: File represented by "User::RemotePath" does not exist.
[FTP Task] Error: Attempt to read message string for 0x80020918 failed with error 0xc02090f3. Make sure all message related files are registered.

What I want to do is ignore the error and perform the next loop iteration

Is this a bug, or am I missing something

Dave

To ignore the ftp task error within a loop, put an OnError event handler on the FTP task. In the handler, set the System::Propagate variable to false. This setting will keep the error(s) in the ftp task from bubbling up the container hierarchy, and thus keep the loop from failing on a missing file.

For example:

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
Private vars As Variables
Public Sub Main()
Dts.TaskResult = Dts.Results.Success
Dts.VariableDispenser.LockForWrite("System::Propagate")
Dts.VariableDispenser.GetVariables(vars)
vars("System::Propagate").Value = False
vars.Unlock()
End Sub
End Class

P.S. Another way to ignore the ftp error is by configuring the MaximumErrorCount property on the loop executable to some relatively high number.
|||Excellent thanks, I initially set the MaximumErrorCount as a quick hack but thanks for the first suggestion - I've been meaning to figure out how OnError works.

|||

This sounds like the perfect solution for what I'm trying to do... but one question:

How do you "put an OnEvent handler on the ftp task"?

Not sure what this means.

Thanks much!!

|||

Ok, I figured out the event handler tab functionality.

So, for my FTP task, I created an OnError event handler. I put a script task in the event handler.

Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
Private vars As Variables
Public Sub Main()
Dts.TaskResult = Dts.Results.Success
Dts.VariableDispenser.LockForWrite("System:ropagate")
Dts.VariableDispenser.GetVariables(vars)
vars("System:ropagate").Value = False
vars.Unlock()
End Sub
End Class

However, the FTP task is still failing as before, and failing the ForLoop container with it.

What am I doing wrong?

Thanks

|||

Hello,

I am still trying to figure out why FTP the error is still "bubbling up" to the ForLoop container?

This is what I have:

ForLoop [ FTP task --> script task ]

In the FTP task Event Handler tab, I created an "On Error" event handler. It is a script task with the above poster's code that is supposed to turn off the System propogate variable. However, it isn't working. What am I doing wrong?

I need to turn off error handers in the FTP task, so that when I get a file not found error, it doesn't cause the ForLoop container to fail also. It needs to keep looping, even if the file isn't there yet.

Thanks much!

|||

The system variable System:ropagate effects propagation of execution errors. Validation errors will always propagate at least one level up the container hierarchy, probably because the validation error of a task becomes an execution error of the for loop container.

So if an error occurs in the validation phase of the ftp task, the first time a System:ropagate=false error handler could catch it would be in the for each loop. Execution errors, on the other hand, can be caught be an error handler on the ftp task itself.

The two properties which also account for validation errors (which always bubble up at least one level) are ForceExecutionResult and MaximumErrorCount. By setting ForceExecutionResult=Sucess on the for each loop container or by setting MaximumErrorCount on the for each loop container to a large number, the loop will continue.

You can determine in which phase the the error(s) occur by observing if the error events fall exclusively between the OnPrevalidate / OnPostValidate events of the ftp task.

sql

FTP Task - GET Command

I am working with an odd FTP site that is using a custom pointer file (denoted by a .LMP) extension to provide pointers to dynamic data. To understand the gist, the FTP site will have a list such as:

FileName Size
SomeFile.gz.lmp 0 bytes

In order to obtain the actual file, they want me to run the following command (in BINARY mode only):

GET SomeFile.gz

If I run this command from an command prompt, the file downloads after a few seconds with a size around 3 or 4 MB.

So I need to connect to an FTP site inside of an SSIS project, grab all of the files, strip off the .LMP extension and then perform a GET on each file (RETR does not work). The nI have to gunzip each file prior to finally accessing the connects therein.

Is there any way of accomplishing this task within SSIS or do I have to create some separate shell application?

Thank you!

I don't have any suggestions for getting the list of files, other than a script task.

You could use FOR loop container to iterate the list of file names, once you have it, with an expression on the filename to strip off the .LMP extension.

Check this thread for some unzip ideas:

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

|||

Here is a thread with the script task to loop through a folder on the ftp server. You can just put code in to search for files with the .lmp extension and strip them off where it says "whatever operation you need to do to find the correct file"......

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

|||I ended up determining that the problem was due to an incompatability between SSIS FTP and Unix. I have posted my solution here:

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

FTP Task - GET Command

I am working with an odd FTP site that is using a custom pointer file (denoted by a .LMP) extension to provide pointers to dynamic data. To understand the gist, the FTP site will have a list such as:

FileName Size
SomeFile.gz.lmp 0 bytes

In order to obtain the actual file, they want me to run the following command (in BINARY mode only):

GET SomeFile.gz

If I run this command from an command prompt, the file downloads after a few seconds with a size around 3 or 4 MB.

So I need to connect to an FTP site inside of an SSIS project, grab all of the files, strip off the .LMP extension and then perform a GET on each file (RETR does not work). The nI have to gunzip each file prior to finally accessing the connects therein.

Is there any way of accomplishing this task within SSIS or do I have to create some separate shell application?

Thank you!

I don't have any suggestions for getting the list of files, other than a script task.

You could use FOR loop container to iterate the list of file names, once you have it, with an expression on the filename to strip off the .LMP extension.

Check this thread for some unzip ideas:

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

|||

Here is a thread with the script task to loop through a folder on the ftp server. You can just put code in to search for files with the .lmp extension and strip them off where it says "whatever operation you need to do to find the correct file"......

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

|||I ended up determining that the problem was due to an incompatability between SSIS FTP and Unix. I have posted my solution here:

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

FTP job in I64 machine running Windows2003 and SQL2000(64bits)

I am in the process of moving our old SQLServer to a new I64 server.
Everything has been tested and working fine except there is a scheduled FTP
job task that didn't go through. I am hoping if I need to make changes to
accomodate the new I64 platform.
In the new SQL Server, the job task is the following:
ftp -s:"\\fileserverA\data\FTPScript.txt" 10.10.1.99
The content FTPScript.txt is as follows:
userid
userpassword
cd unixdatafolder
lcd D:\Data
put FileTransfer.txt
bye
After the job is run, the status shows successful but when I telnet to the
unix server, the file is not there. When I do ftp manually and locally in
the I64 server, it has no problem.
Thank you for your asssistance.
MartinHi Martin
If you change the FTP command for (say) an echo statement do you get output?
If so you may want to add the -d parameter to show the commands and capture
the output into a file to see what has happened. Also agent service account
has access to the source file, you may want to try using a cd command before
starting ftp to remove the need for the lcd subcommand.
John
"Martin" wrote:
> I am in the process of moving our old SQLServer to a new I64 server.
> Everything has been tested and working fine except there is a scheduled FTP
> job task that didn't go through. I am hoping if I need to make changes to
> accomodate the new I64 platform.
> In the new SQL Server, the job task is the following:
> ftp -s:"\\fileserverA\data\FTPScript.txt" 10.10.1.99
> The content FTPScript.txt is as follows:
> userid
> userpassword
> cd unixdatafolder
> lcd D:\Data
> put FileTransfer.txt
> bye
> After the job is run, the status shows successful but when I telnet to the
> unix server, the file is not there. When I do ftp manually and locally in
> the I64 server, it has no problem.
> Thank you for your asssistance.
> Martin|||John,
Thank you. The new 64bits machine did have some 'strange behavior'. I will
try using the echo as recommended and see if it makes the difference. I've
spent last two days trying to resolve this issue and after I sent the
question out, just by a random chance, I've found out that the 64bits Job
schedule won't take the url or mapped network drive. After I moved the file
to the local drive on the 64bits machine instead of using network drive other
than the local in the ftp scheduled job task statement, I got the file in
unix box after running the job.
Hope this information could save others time.
Sincerely,
Martin
"John Bell" wrote:
> Hi Martin
> If you change the FTP command for (say) an echo statement do you get output?
> If so you may want to add the -d parameter to show the commands and capture
> the output into a file to see what has happened. Also agent service account
> has access to the source file, you may want to try using a cd command before
> starting ftp to remove the need for the lcd subcommand.
> John
> "Martin" wrote:
> > I am in the process of moving our old SQLServer to a new I64 server.
> > Everything has been tested and working fine except there is a scheduled FTP
> > job task that didn't go through. I am hoping if I need to make changes to
> > accomodate the new I64 platform.
> >
> > In the new SQL Server, the job task is the following:
> >
> > ftp -s:"\\fileserverA\data\FTPScript.txt" 10.10.1.99
> >
> > The content FTPScript.txt is as follows:
> > userid
> > userpassword
> > cd unixdatafolder
> > lcd D:\Data
> > put FileTransfer.txt
> > bye
> >
> > After the job is run, the status shows successful but when I telnet to the
> > unix server, the file is not there. When I do ftp manually and locally in
> > the I64 server, it has no problem.
> >
> > Thank you for your asssistance.
> >
> > Martin|||Hi Martin
You may want to try http://www.sqldts.com/default.aspx?302 and see if it is
more reliable. I don't know if the wininet libraries are compatible with this
for I64.
John
"Martin" wrote:
> John,
> Thank you. The new 64bits machine did have some 'strange behavior'. I will
> try using the echo as recommended and see if it makes the difference. I've
> spent last two days trying to resolve this issue and after I sent the
> question out, just by a random chance, I've found out that the 64bits Job
> schedule won't take the url or mapped network drive. After I moved the file
> to the local drive on the 64bits machine instead of using network drive other
> than the local in the ftp scheduled job task statement, I got the file in
> unix box after running the job.
> Hope this information could save others time.
> Sincerely,
> Martin
> "John Bell" wrote:
> > Hi Martin
> >
> > If you change the FTP command for (say) an echo statement do you get output?
> > If so you may want to add the -d parameter to show the commands and capture
> > the output into a file to see what has happened. Also agent service account
> > has access to the source file, you may want to try using a cd command before
> > starting ftp to remove the need for the lcd subcommand.
> >
> > John
> >
> > "Martin" wrote:
> >
> > > I am in the process of moving our old SQLServer to a new I64 server.
> > > Everything has been tested and working fine except there is a scheduled FTP
> > > job task that didn't go through. I am hoping if I need to make changes to
> > > accomodate the new I64 platform.
> > >
> > > In the new SQL Server, the job task is the following:
> > >
> > > ftp -s:"\\fileserverA\data\FTPScript.txt" 10.10.1.99
> > >
> > > The content FTPScript.txt is as follows:
> > > userid
> > > userpassword
> > > cd unixdatafolder
> > > lcd D:\Data
> > > put FileTransfer.txt
> > > bye
> > >
> > > After the job is run, the status shows successful but when I telnet to the
> > > unix server, the file is not there. When I do ftp manually and locally in
> > > the I64 server, it has no problem.
> > >
> > > Thank you for your asssistance.
> > >
> > > Martin

ftp Failure

I have designed an SSIS that needs to download a set of files via ftp. The ftp is working fine under Business Studio. When I move it to The server and try runing the package I'm getting the following errors:

Error: Unable to connect to FTP server using "FTP Server"

Error: An error occurred in the requested FTP operation. Detailed error description: The password was not allowed.

This is not my first FTP in SSIS, so I also have the following item set:

Protectionlevel = EncryptProtectedWithPassword

I have several other FTP packages that go to the exact same server, with the exact same user ID and password that are working.

What am I missing?

Since noone is responding...

I also tried changing the protection level to "DontSaveEncrypted" and set up a package configuration with the password in it. I thought this would work, but I'm still getting the same messages.

|||This is an FTP server error, not an SSIS related error.

Search the Internet for "The password was not allowed." for discussions on this message.|||

Thanks for replying Phil.

If this is an FTP server error, why does it work in one package, but not in another that's using the exact same connection?

And why does it work in SSIS designer?

FTP Delete task in SQL agent

I have a FTP task created using SSIS that need to delete lot of files in a directory. This task is working fine if I'm Executing the SSIS package manualy but not working if I'm scheduling the SSIS package in SQL agent job.
Anybody have faced this kind if situation is there any solution for this?.

have you gotten a resolution on this problem? I am having the same issue on a FTP send task and it works fine when I run it manually but it bombs out when I schedule it.

Let me know... Thanks

|||

Error messages really help in these situations guys.

-Jamie

|||

I actually found the answer for my problem here:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=356558&SiteID=1

I just made the protection level to "DontSaveSensitive" and when the SQL Server agent ran the package it ran properly.

Hope it helps :)

FT Index on [German|English] columns. SQL Server 2005

Hi,
sorry for the cryptic topic. I wasn't able to come up with anything
better.
We are currently working on a project thats aims to keep all students
thesis es in a database and enable searches on it via a ASP .NET web
app. The problem we have is that some students write their thesis in
german and some in english. Right now there are more germans texts than
english ones but i guess that will change in the near future. We've
heard it's possible to maintain a fulltext index with both languages at
once but we would have to specify with our queries which index should
be used. While this is ok for entering data, asking the user if he is
going to enter his thesis in english or german, asking someone that is
searching we find it to be troublesome or at least leading to
confusion. Most of the german thesis texts contain a fair number of
english words too (computer science kinda lives from english buzzwords
;-)). So languages might be even mixed inside a text.
Anyone recommendations on how to solve this kinda of problem? Would it
be reasonable to "merge" the englisch and german stopword list and
create a "new language"?
Thanks for your help! All hints/links appreciated.
Kind regards from Germany
Phil
Phil,
In SQL Server 2005 Full-Text Search (FTS) you can store and search on
language-specific words or phrases at query time via CONTAINS or
FREETEXT. So, you can store both languages (English and German are both
supported FTS languages) and use either English or German for the
"Language for Word Breaker" and then use the following CONTAINS
statement (with or without the formsof(inflectional) parameter):
select * from FTSTable where
contains(*,'formsof(inflectional,"english_word")', language 1033) OR
contains(*,'formsof(inflectional,"German_word")',l anguage 1031)
Additionally, on an advance search form, you may want to add a box for
keyword and the associated language, so that the searchers can select
the approprate language for the keyword. Note, the default language is
whatever language you defined for the "Language for Word Breaker".
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
|||Such a project is doomed. German and English generate a large number of
false friends/false conjugates; wander words/worts which are common in both
languages but with different meanings. For instance Gift in English means a
present; in German its means poison.
So what will happen no matter what the language you specify your query to
occur in you will get false hits. The better way to do it is to sniff your
browser settings and check for German as the supported language and then
have the docs stored in different columns/tables/databases. Then a query
would be directed against a language segregated column/table or database.
If your queries are done using the Contains predicate the false conjugates
will be minimized.
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
"Philipp Stader" <pstader@.gmail.com> wrote in message
news:1131651975.271992.103620@.z14g2000cwz.googlegr oups.com...
> Hi,
> sorry for the cryptic topic. I wasn't able to come up with anything
> better.
> We are currently working on a project thats aims to keep all students
> thesis es in a database and enable searches on it via a ASP .NET web
> app. The problem we have is that some students write their thesis in
> german and some in english. Right now there are more germans texts than
> english ones but i guess that will change in the near future. We've
> heard it's possible to maintain a fulltext index with both languages at
> once but we would have to specify with our queries which index should
> be used. While this is ok for entering data, asking the user if he is
> going to enter his thesis in english or german, asking someone that is
> searching we find it to be troublesome or at least leading to
> confusion. Most of the german thesis texts contain a fair number of
> english words too (computer science kinda lives from english buzzwords
> ;-)). So languages might be even mixed inside a text.
> Anyone recommendations on how to solve this kinda of problem? Would it
> be reasonable to "merge" the englisch and german stopword list and
> create a "new language"?
> Thanks for your help! All hints/links appreciated.
> Kind regards from Germany
> Phil
>
|||Thanks for your input! This basicaly means i'll have to tell SQL Server
which language i want it to use? On inserts and searches? Meaning i
have to ask the user if the text he is going to submit is english or
german (or apply some magic to detect the language)?
|||I wish there was a german-english word breaker :-) Detecting browser
language settings won't help us much. People can choose to write their
diploma/master thesis in german or english. Browser language settings
will be german all the way since student can only enter their thesis
from within the university, where all clients are "german". Most user
searching for a thesis will use some acronyms/keywords like XML, C#,
UML etc which are kinda language indepedent. But there are some
technological terms which have a good and commonly used german
expression, usually a longer word combined from substantives so i think
the correct wordbreaker would help a lot if a user search for a
substring of the longer word. Thanks for pointing the problems out.
Seems to me that our goal is kinda not feasable right now :-/
|||You're welcome, Philipp,
Yes, you still need to define the "Language for Word Breaker" (either German
or English) and you only need to do this once when you define the Full-Text
Catalog and the specific FT-enabled column where these languages are stored.
The default will be which ever language you define, and you only need to
specific the non-default language at query time, i.e., when you execute a
CONTAINS or FREETEXT searches.
No, you don't (and cannot) define the language during INSERTS as
syntactically this is not possible with the INSERT statement. The SQL Server
2005 FTS engine (msftesql.exe) will detect and determine the language of the
mixed text in this column.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Philipp Stader" <pstader@.gmail.com> wrote in message
news:1131886568.811593.201700@.o13g2000cwo.googlegr oups.com...
> Thanks for your input! This basicaly means i'll have to tell SQL Server
> which language i want it to use? On inserts and searches? Meaning i
> have to ask the user if the text he is going to submit is english or
> german (or apply some magic to detect the language)?
>

From SQL Server 2005 to 2000

Hi all.

I'm doing some testing database on my computer that I've installed SQL server 2005. But the server at the company I'm working with has SQL Server 2000.

What do I need to do to convert my database file in 2005 into 2000 so that I can add it in the company's server?

TIA.

you can not "convert" SQL Server 2005 database to SQL Server 2000 database. The otherway round is possible.

Madhu

|||

While you cannot 'convert' the SQL 2005 database to a SQL 2000 format, you can script out the tables, views, functions, stored procedures, etc., then after checking those scripts for compatibility, you can run the scripts on SQL 2000 and re-create the database.

|||It would be advisable to change the compatability level of the 9.0 database to 8.0.|||Thank you all for replying.

One question, how to change the compatibility level?
|||

It would be nice if it were so simple as changing the compatibility level.

The physical files are different between SQL 2000 and SQL 2005, and to my knowledge, there is no current mechanism to 'downgrade' a database.

I think that you are left with my earlier suggestion about scripting out the database objects, and running those scripts in SQL 2000 to recreate a SQL 2000 database. Perhaps these links will help with scripting:

DDL -Script Database to File
http://www.wardyit.com/blog/blog/archive/2006/07/21/133.aspx
http://www.sqlteam.com/publish/scriptio
http://www.aspfaq.com/etiquette.asp?id=5006
http://www.codeproject.com/dotnet/ScriptDatabase.asp
http://www.nigelrivett.net/DMO/DMOScriptAllDatabases.html
http://rac4sql.net/qalite_main.asp

DDL –Script Data to file (Database Publishing Wizard)
http://www.microsoft.com/downloads/details.aspx?FamilyID=29B4FFD8-AC3A-4481-B352-9B185619A901

|||Thanks for all the links, most helpful!

However, the last link didn't point to a website. Was it this:
http://www.microsoft.com/downloads/details.aspx?FamilyID=56e5b1c5-bf17-42e0-a410-371a838e570a&DisplayLang=en
that you want to link to?
|||

use system sp sp_dbcmptlevel

Eg: EXEC sp_dbcmptlevel 'AdventureWorks', '80';

Madhu

|||Hi all,

Taken into considerations of all your suggestions, I'm compiling my step-by-step of 'downgrading' my database from version 2005 to 2000:

1) Set the compatibility of database to SQL server 2000:
EXEC sp_dbcmptlevel 'myDatabase', 80

2) Next, I will do this:
Open SQL Server Management Studio, and connect.
Right-click on myDatabase, choose 'Script Database As > CREATE To > New query editor window.
Save as .sql

Would it be advisable to script my tables one by one? But then I'll have to run the script in order of which table is created first based on relationships. Would scripting the whole database in one go takes care of the order of creating tables?
Any suggestions on what else I need to consider before/after doing this?

TIA.
|||

in this case there is no need to degrade the copatibility level. You can script all the objects into a single file and the run the script in 2000. by this step your schema will be ready. Now you need to improt the data using import/export or ssis or simple insert into statment after configuring linked server.

Madhu

|||Hello Madhu,

I've tried right-click on my database and Script Database to a new query window but there are only 60-70 lines and I didn't see any CREATE TABLE anywhere. So I would assume that I need to right-click on each table and Script table to a different .sql file for each table?

Eera
|||I probably script my database the wrong way. Sorry for giving you the wrong description earlier. Now, when I right-click my database, point to Tasks and chose 'Generate Scripts' and the scripts generated looks more promising.

Just started using SQL server this week.
|||

how many tables are there in this database ?

Madhu

|||There are (only) 8 tables in my database.

I have yet to research and find out the process of importing data using the method that u mentioned.
|||

if its only 8 table then the best method will be

(a) Configure LinkedServer in 2000 for SQL Server 2005

(b) use your own script like Insert into tablename select *from <LinkedserverName>.databasename.owner.tablename

Madhu

freetexttable query continued

Still working on this query:
select *
from products
inner join freetexttable(products,*,@.searchstr) ft1
on ft1.[key] = products.p_id
left join sku
on sku.p_id= products.p_id
inner join freetexttable(sku,*,@.searchstr) ft2
on ft2.[key] = sku.sku
I replaced the inner join with a left join on the second table.
I still get no results if the searchstr exists in the first table but not in
the second table. Full join doesn't work either.
Did I modify the wrong join? If I left-join on the FTcat I get all the rows
in both tables.
TIA!
can you post the schemas of all related tables?
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
"geek-y-guy" <noone@.nowhere.org> wrote in message
news:utMeoreVHHA.5108@.TK2MSFTNGP06.phx.gbl...
> Still working on this query:
> select *
> from products
> inner join freetexttable(products,*,@.searchstr) ft1
> on ft1.[key] = products.p_id
> left join sku
> on sku.p_id= products.p_id
> inner join freetexttable(sku,*,@.searchstr) ft2
> on ft2.[key] = sku.sku
> I replaced the inner join with a left join on the second table.
> I still get no results if the searchstr exists in the first table but not
> in the second table. Full join doesn't work either.
> Did I modify the wrong join? If I left-join on the FTcat I get all the
> rows in both tables.
> TIA!
>
>
|||> can you post the schemas of all related tables?
Products:
p_id varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
p_name varchar no 255 yes no yes SQL_Latin1_General_CP1_CI_AS
p_desc varchar no 1024 yes no yes SQL_Latin1_General_CP1_CI_AS
p_man int no 4 10 0 no (n/a) (n/a) NULL
row_id int no 4 10 0 no (n/a) (n/a) NULL
date_created datetime no 8 no (n/a) (n/a) NULL
date_modified datetime no 8 no (n/a) (n/a) NULL
(p_id is PK)
Manufacturers:
m_name varchar no 100 no no no SQL_Latin1_General_CP1_CI_AS
m_id int no 4 10 0 no (n/a) (n/a) NULL
(m_id is PK)
SKUs:
p_id varchar no 50 yes no yes SQL_Latin1_General_CP1_CI_AS
sku varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS
sku_weight decimal no 9 18 1 yes (n/a) (n/a) NULL
attribute1 varchar no 50 yes no yes SQL_Latin1_General_CP1_CI_AS
attribute2 varchar no 50 yes no yes SQL_Latin1_General_CP1_CI_AS
status int no 4 10 0 yes (n/a) (n/a) NULL
wholesale_cost decimal no 5 7 2 no (n/a) (n/a) NULL
special_order bit no 1 no (n/a) (n/a) NULL
date_available datetime no 8 yes (n/a) (n/a) NULL
(sku is PK)
where
m_id in manufacturers = p_man in products
and p_id in sku = p_id in products

> --
> 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
> "geek-y-guy" <noone@.nowhere.org> wrote in message
> news:utMeoreVHHA.5108@.TK2MSFTNGP06.phx.gbl...
>

freetexttable issue

Hi,

We are using freetexttable as our search function in our application and it seems to be partially working. I search for a word from a column of a table I included in the search catalog and it sometimes pickup the record and sometimes it doesn't. I got 2 records having 'business' as the keyword. And when I search that keyword, it only returned 1 record.

here is the codesnippet:

select * from freetexttable(<tablename>, *, 'business')

Any help is greatly appreciated.

Baldwin

bbudiongan@.misicompany.com

Are the two keyword int he same column ? if no, are there different word breakers defined on the column ? Are the two columns freetextindexed ?

Jens Suessmeyer.

http://www.sqlserver2005.de

freetexttable issue

Hi,
I am using freetexttable as a search tool for our application but it
seems that it is partially working. I am able to search on some words
specified in my catalog but not on others. For example I got keywords
- stamps business cards. The freetexttable function can pick up only
'stamps' but not 'business'. I created another record in the table
included in the catalog and have the same keywords. Now I can search
on 'stamps' of the first record but not on the second record.
here is the code snippet:
select * from freetexttable(<table name>, *, 'keywordhere')
where: tablename is included in the catalog
keywordhere is the keyword in which i use 'stamps' or
'business'.
Any help is greatly appreciated.
thanks!
Baldwin
Can you try this
select * from freetexttable(<table name>, *, 'Stamps')
select * from freetexttable(<table name>, *, 'business')
select * from freetexttable(<table name>, *, 'Cards')
Do the same rows show up?
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
<ibaldwinjr@.gmail.com> wrote in message
news:1184851168.766256.92770@.z28g2000prd.googlegro ups.com...
> Hi,
> I am using freetexttable as a search tool for our application but it
> seems that it is partially working. I am able to search on some words
> specified in my catalog but not on others. For example I got keywords
> - stamps business cards. The freetexttable function can pick up only
> 'stamps' but not 'business'. I created another record in the table
> included in the catalog and have the same keywords. Now I can search
> on 'stamps' of the first record but not on the second record.
> here is the code snippet:
> select * from freetexttable(<table name>, *, 'keywordhere')
> where: tablename is included in the catalog
> keywordhere is the keyword in which i use 'stamps' or
> 'business'.
> Any help is greatly appreciated.
> thanks!
> Baldwin
>

Freetexttable

Okay I have finally found full text-searching and got it working with SQL2000.

Now it appears to be working correctly however I am having difficulty getting to grips with FREETEXTTABLE, and help/advice would be appreciated.

Now this is what I would like to do. One form field on a web page, passes over a search phrase which then want to use to search across all rows of my selected table and then return WEIGHTED results or RANKED results.

Now I have used CONTAINS etc and had fairly good results however assuming what I am reading via MSDN using FREETEXTTABLE will give me much better and fairly accurate results based on a whole search phrase entered by a user.

Incidentally should I use CONTAINSTABLE?

Quick Note: I am only wanting to search through one table, no more than 2000 rows.

So I have my table (lets call it Account_List) containing the following rows:

AccountID (int,Not Null) <-PrimaryKey
AccountName (char(50), Null)
AccountAddress1 (char(50), Null)
AccountAddress2 (char(50), Null)
AccountTown (char(50), Null)
AccountCounty (char(50), Null)
AccountPostcode (char(10), Null)
AccountTelephone (nvchar(50), Null)

I have a populated full-text catalog for the above table containing all of the rows.

Now what I want to do is say for example the user inputs the phrase 'Argos in Milton Keynes' I would like it to go an search each row and return weighted results based on that. In this case the key columns are AccountName and AccountTown.

Now the only example so far I have is:

USE Northwind
SELECT FT_TBL.CategoryName,
FT_TBL.Description,
KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
FREETEXTTABLE(Categories, Description,
'sweetest candy bread and dry meat') AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]
GO

Microsoft standard example, however I don't get why I would have to do an INNER JOIN, what am I missing here? Like said before I am only using one table.

I would like a example of a simple string to search my database from the user input form.

Can anyone enlighten me :)As far as JOINing goes this is from Microsoft:

Queries that use the CONTAINSTABLE and FREETEXTTABLE functions are more complex than those that use the CONTAINS and FREETEXT predicates because qualifying rows returned by the functions must be explicitly joined with the rows in the original SQL Server table.

For more examples :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_qd_15_1m9f.asp|||Got to the bottom of it!

The correct Query is as follows for anyone wanting to know :eek:

USE mydata
GO
SELECT *
FROM Account_List AS FT_TBL
INNER JOIN
FREETEXTTABLE(Account_List, *,
'Argos in Milton Keynes') AS KEY_TBL
ON FT_TBL.AccountID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC
GO

Oh how I like technology (when it works :mad: )

Thanks!

Freetext search not working after SQL 2005 upgrade

*** See next post as I have found it is nothing to do with freetext search I just assumed it was this ***

Hi

I have upgraded our test system to SQL 2005 from 2000 and restored a production database.

I have rebuilt the freetext index and it would seem that if you query this from an .ASP page using OLEDB it does not return any results even though there are some. If you then run the same query in Management studio you do get the expected results. The code worked perfectly under SQL 2000. Also all other queries that run from the .ASP page work correctly that do not involve a freetext seach.

I have made sure the client is using the lastest MDAC pack 2.8 sp1

The query is

SELECT title
FROM catalog
WHERE CONTAINS(title, 'green*')

Connection string in .ASP page

Provider=SQLOLEDB;Data Source=192.168.0.5;Initial Catalog=DVD;User Id=XXXXXX;Password=XXXXXX;

Thanks in Advance

MatRight after more digging I have found out it is NOT the freetext seach at all. I simplified the query above and removed a left join when I posted the question as I thought it could not be this.

I have now just created two simple tables in my database
linkme
linkme2

both of which have one column called [catalog-no]

I have put one record in linkme

select * from linkme -- pulls back one record correctly via .asp and management studio
select * from linkme left join linkme2 on linkme.[catalog-no] = linkme2.[catalog-no] -- pulls back one record in management studio and 0 records in .asp connection !!!

HELP|||Just to make sure I was not going mad I have created the same two table in SQL 2000 and pointed the .asp script to this and sure enough i get 1 record ?

freaking out job!

i have created a sql server job that i have scheduled to run every 10 min. in this job i have diferent steps wich seem to be working fine i also have 2 alerts that i have created for this job in case some steps fails so that i can be notififed via email with specific messages. the Weird thing is this job keeps re executing over and over again and never whaits for that 10 min delay and of course i get flooded with emails since i have set up test data to get these messages on purpose. so y doesnt wait 10 min like i have scheduled .PLZ HELP...Somewhere you are going into a loop ...
Check the Advanced properties for the job steps .. ?

Check on success , on faliure and retry attempts ... the error seems to be from somewhere there only.|||i have set up every step to go to step A and quit on failure on each of my steps and so on and the last one with quit on suceess and quit on failure ...its still looping HELP|||Originally posted by hillcat
i have set up every step to go to step A and quit on failure on each of my steps and so on and the last one with quit on suceess and quit on failure ...its still looping HELP

I always set it up as go to next step on success and quit on failure.Did you check the no of retry attempts also|||i have done what you said and all the retrys are at 0......?|||still doesnt work|||Hmmm .. that is wierd !!!

Will have to do some research here ... try to find the step after which the job re-executes

Can do so by ... changing the advanced option .. on success of each step one by one from the bottom upwards to quit the job returning success.|||Why don't you script the job abd post/attach it?|||wait a minute. Did you say you put every step to go to step A on success? So step 1 (on success) -> step 2 (on success) -> step 1?

Isn't this a perfect endless loop?|||Yup, I love those ;)|||BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Commander') < 1
EXECUTE msdb.dbo.sp_add_category @.name = N'Commander'

SELECT @.JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Test_Upload')
IF (@.JobID IS NOT NULL)
BEGIN

IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @.JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''Test_Upload'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE

EXECUTE msdb.dbo.sp_delete_job @.job_name = N'Test_Upload'
SELECT @.JobID = NULL
END

BEGIN

EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT , @.job_name = N'Test_Upload', @.owner_login_name = N'sa', @.description = N'Execute package: Test_Upload Commandes', @.category_name = N'Commander', @.enabled = 1, @.notify_level_email = 2, @.notify_level_page = 0, @.notify_level_netsend = 0, @.notify_level_eventlog = 2, @.delete_level= 0, @.notify_email_operator_name = N'Email'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback

EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 1, @.step_name = N'Test_UpLoad_En cours', @.command = N'DTSRun /~Z0x3C2C60E6E69FDE4FD4E6E831CD32E2FC93EBDFD722093E B44F362DAB46FD1068596FBE179AAB0CBFC9253FBE160DD1AC 517927A330B272A8182D277B7FE8A0B25807CCA4CC03F11C88 59B5558AB6C1C60AB73140DD13B81B818692A28E23714096F3 C81B31D380168FE17CAB4BE11405820A0A411D90300F55E071 9CFF7A9D4B7BEA8A', @.database_name = N'', @.server = N'', @.database_user_name = N'', @.subsystem = N'CmdExec', @.cmdexec_success_code = 0, @.flags = 2, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'c:\DTS\Logs\Test_UploadCommandesStep.log', @.on_success_step_id = 0, @.on_success_action = 3, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 2, @.step_name = N'Test_Upload_Vrification', @.command = N'DTSRun /~Z0x77673A40FE51DCB3FDE10E99A1046569DE918CA37A09EA 1936C2699A12469122352F4D900875502D1ABCDEEB38E19B7A A4EEAEB430318EF008E74C940CEEDA61F3128D9C3658E57A07 692F84203B7CDE3FD074CAF73B5B7475F2A2DF33D3CDB9BA2B B1C7909B2656915BD9D8B5695DAD0A14723D9C40A502744A73 559B604CDCA4EAB538C7243875A187BA', @.database_name = N'', @.server = N'', @.database_user_name = N'', @.subsystem = N'CmdExec', @.cmdexec_success_code = 0, @.flags = 2, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'c:\DTS\Logs\Test_UploadCommandesStep.log', @.on_success_step_id = 0, @.on_success_action = 3, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 3, @.step_name = N'Test_Upload_Validation', @.command = N'IF (SELECT PC_TR_UPLOAD
FROM TEST_UPLOAD) = ''X''
BEGIN
PRINT ''Upload en cours dans Pc''
END
ELSE
IF (SELECT MAESTRO_TR_UPLOAD
FROM TEST_UPLOAD) = ''X''
BEGIN
RAISERROR(''*** Avertissement *** Upload en cours dans Maestro'', 16, 1)
END
ELSE
IF (SELECT COPY_TR_UPLOAD
FROM TEST_UPLOAD) = ''X''
BEGIN
RAISERROR(''*** Avertissement *** Copy Upload dans Maestro '', 16, 1)
END
', @.database_name = N'AS400', @.server = N'', @.database_user_name = N'', @.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 2, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'c:\DTS\Logs\Test_UploadCommandesStep.log', @.on_success_step_id = 0, @.on_success_action = 3, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 4, @.step_name = N'Test_Upload Commandes', @.command = N'DTSRun /~Z0x5F4F7BA66CB617B86BCC563E86B5C07376BFA0C96766A8 972F7E2AE768C0D2D4B5921094A7CFDAF205EAD298C872263D B963EDDEE1E6D5029CCB81BD1ECFF3932DF1A04B107B2B1514 C18176F412201E686A7DC37753F0D7036BC4076C1B87A404EE 673B7DBDC60F860ABED0E9F04600EA05DC37239265F49D18C9 26FC683E0E542F40E7B838BB2664CF12', @.database_name = N'', @.server = N'', @.database_user_name = N'', @.subsystem = N'CmdExec', @.cmdexec_success_code = 0, @.flags = 2, @.retry_attempts = 0, @.retry_interval = 0, @.output_file_name = N'c:\DTS\Logs\Test_UploadCommandesStep.log', @.on_success_step_id = 0, @.on_success_action = 3, @.on_fail_step_id = 0, @.on_fail_action = 3
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 5, @.step_name = N'Test_Upload Collections', @.command = N'DTSRun /~Z0xC4DCE73BFF8EB8958A569CF001E7764EDCA3DBA6CD29A1 3E9150B7C34F88FD0EA3114E6F6CA688F0E6D03DCC2B0B0996 1E529D40BC65AED39E97EBF3153D41F9A663C8AAEBB9D62DDE 119F217125C85BA50A662F8EEA95E08B5ECAB21C3627000E13 CAC07CA608E402106F5ECA139DA115EE94F9C5709FDCF46256 2B00F3020D772FBBCBD9D77397E3BD3A398CB8D8E360998F ', @.database_name = N'', @.server = N'', @.database_user_name = N'', @.subsystem = N'CmdExec', @.cmdexec_success_code = 0, @.flags = 2, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'c:\DTS\Logs\Test_UploadCollectionsStep.log', @.on_success_step_id = 0, @.on_success_action = 3, @.on_fail_step_id = 0, @.on_fail_action = 3
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 6, @.step_name = N'UpLoad_V_Entete', @.command = N'DECLARE
@.TransactionNb varchar(10),
@.EqId varchar(10)

DECLARE TransactionNb_cursor CURSOR
FOR
SELECT TransactionNb, EqId
FROM EntCom
WHERE UpdCode = ''C''

OPEN TransactionNb_cursor

FETCH NEXT FROM TransactionNb_cursor
INTO @.TransactionNb, @.EqId

WHILE @.@.FETCH_STATUS <> -1
BEGIN
EntCom
IF (SELECT UpdCode
FROM DetCom
WHERE TransactionNb = @.TransactionNb and EqId = @.EqId) = ''C''
BEGIN
CONTINUE
END
ELSE
BEGIN
RAISERROR (50005, 10, 0, @.TransactionNb, @.EqId)
END

FETCH NEXT FROM TransactionNb_cursor
INTO @.TransactionNb, @.EqId
END

CLOSE TransactionNb_cursor
DEALLOCATE TransactionNb_cursor
', @.database_name = N'Test_Commander', @.server = N'', @.database_user_name = N'', @.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 2, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'c:\DTS\Logs\Test_UploadCommandesStep.log', @.on_success_step_id = 0, @.on_success_action = 3, @.on_fail_step_id = 0, @.on_fail_action = 3
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 7, @.step_name = N'UpLoad_V_Dtail', @.command = N'DECLARE
@.TransactionNb varchar(10),
@.EqId varchar(10)

DECLARE TransactionNb_cursor CURSOR
FOR
SELECT TransactionNb, EqId
FROM DetCom
WHERE UpdCode = ''C''

OPEN TransactionNb_cursor

FETCH NEXT FROM TransactionNb_cursor
INTO @.TransactionNb, @.EqId

WHILE @.@.FETCH_STATUS <> -1
BEGIN
-- Vrifier s''il existe une transaction avec le UpdCode = ''C'' dans EntCom
IF (SELECT UpdCode
FROM EntCom
WHERE TransactionNb = @.TransactionNb and EqId = @.EqId) = ''C''
BEGIN
CONTINUE
END
ELSE
BEGIN
RAISERROR (50006, 10, 0, @.TransactionNb, @.EqId)
END

FETCH NEXT FROM TransactionNb_cursor
INTO @.TransactionNb, @.EqId
END

CLOSE TransactionNb_cursor
DEALLOCATE TransactionNb_cursor
', @.database_name = N'Test_Commander', @.server = N'', @.database_user_name = N'', @.subsystem = N'TSQL', @.cmdexec_success_code = 0, @.flags = 2, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'c:\DTS\Logs\Test_UploadCommandesStep.log', @.on_success_step_id = 0, @.on_success_action = 3, @.on_fail_step_id = 0, @.on_fail_action = 3
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id = 8, @.step_name = N'Test_UpLoad_Demande', @.command = N'DTSRun /~Z0xBCAC64110FBAE51F592FE69C2AD17FA3221C1CD7655D78 57A08FFE18C4FCA0A3F030E6ED91BD8962BBDE9C3FCDC5F340 ED2D68443C7577C5DBD2AD344EA9A1CED6568FA45E339E95C9 831E4DD5D783C0C52D2BD5DD5964040600AA94A3272BB3AB2A 5A5C9F9AAA3F428F8B4D327E4307B6E379BAAEAE4DCBC5F1EB 02416E763CEBCE05', @.database_name = N'', @.server = N'', @.database_user_name = N'', @.subsystem = N'CmdExec', @.cmdexec_success_code = 0, @.flags = 2, @.retry_attempts = 0, @.retry_interval = 1, @.output_file_name = N'c:\DTS\Logs\Test_UploadCommandesStep.log', @.on_success_step_id = 0, @.on_success_action = 1, @.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.JobID, @.start_step_id = 1

IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback

-- Add the job schedules
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name = N'Test_Upload Commandes', @.enabled = 1, @.freq_type = 8, @.active_start_date = 20031020, @.active_start_time = 60000, @.freq_interval = 62, @.freq_subday_type = 4, @.freq_subday_interval = 10, @.freq_relative_interval = 1, @.freq_recurrence_factor = 1, @.active_end_date = 99991231, @.active_end_time = 220000
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback

-- Add the Target Servers
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.JobID, @.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback

END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:|||Originally posted by Brett Kaiser
Why don't you script the job abd post/attach it?

Well, you asked for it, and hillcat ran with it ;)|||but it hink that its this part that doesnt not work

DECLARE
@.TransactionNb varchar(10),
@.EqId varchar(10)

DECLARE TransactionNb_cursor CURSOR
FOR
SELECT TransactionNb, EqId
FROM DetCom
WHERE UpdCode = 'C'

OPEN TransactionNb_cursor

FETCH NEXT FROM TransactionNb_cursor
INTO @.TransactionNb, @.EqId

WHILE @.@.FETCH_STATUS <> -1
BEGIN
-- Vrifier s'il existe une transaction avec le UpdCode = 'C' dans EntCom
IF (SELECT UpdCode
FROM EntCom
WHERE TransactionNb = @.TransactionNb and EqId = @.EqId) = 'C'
BEGIN
CONTINUE
END
ELSE
BEGIN
RAISERROR (50006, 10, 0, @.TransactionNb, @.EqId)
END

FETCH NEXT FROM TransactionNb_cursor
INTO @.TransactionNb, @.EqId
END

CLOSE TransactionNb_cursor
DEALLOCATE TransactionNb_cursor|||shes a tuffy hehe|||can someone please respond to this|||This is the same as the other thread, isn't it?

Anyway, make sure the on success and on failure directives are not looping.

Make sure you use WHILE @.@.FETCH_STATUS = 0

Using <> -1 could cause it to loop unexpectedly.