FTP TASK PROBLEM

Dear all,

I have problem in my ftp task.For example,
I create two variable remotepath and localpath to set up remote and local path of ftp task. And the IsRemotePathVariable and IsLocalPathVariable is set to true.

I create two funtions to retrun the pathvalue. The return value

is like 'D:\A.TXT' , but in DTSX the varible value is changed to 'D:\\A.TXT'. So my ftp task fail.

How can I overcome this problem?

Thanks a lot.

Regards

wen

Replace all occurences of "\\" with "\" if its causing you a problem.

You can do this in an expression.

-Jamie

|||

It doesn't actually change the value. It is just that the undelying engine for SSIS is written in C and "\" is an escape character. To actually display the value of "\" it has to be preceeded by a "\". so to use/display it in a string it appears as "\\". For example \r actually means a carriage return.

As for your situation what error are you getting?

|||

when the path value is changed to "\\" , the error is as bellow,

Error: 0xC0029183 at FTP Task, FTP Task: File represented by "User::FTPPATH" does not exist.
Task failed: FTP Task

sql

Frustrated with Service Broker Example

I want to use a Service Broker Queue as a processing queue. I found several examples on the WEB and have tried to make them work but they do not. Below is the example I used and I ASSUME I should get a Hello World in the Message field at the end but its empty.

Any ideas as to why?
I have tried
select * from ReceiverQueue
and
select * from SenderQueue
before I do a RECIEVE and both QUEUE's are empty

Scooter
-CODE--
USE AdventureWorks
GO

CREATE MESSAGE TYPE HelloMessage VALIDATION = NONE
GO

CREATE CONTRACT HelloContract (HelloMessage SENT BY INITIATOR)
GO

CREATE QUEUE SenderQueue
CREATE QUEUE ReceiverQueue
GO

CREATE SERVICE Sender ON QUEUE SenderQueue
CREATE SERVICE Receiver ON QUEUE ReceiverQueue (HelloContract)
GO

DECLARE @.conversationHandle UNIQUEIDENTIFIER
DECLARE @.message NVARCHAR(100)
BEGIN
BEGIN TRANSACTION;
BEGIN DIALOG @.conversationHandle
FROM SERVICE Sender
TO SERVICE 'Receiver'
ON CONTRACT HelloContract
-- Send a message on the conversation
SET @.message = 'Hello, World';
SEND ON CONVERSATION @.conversationHandle
MESSAGE TYPE HelloMessage (@.message)
COMMIT TRANSACTION
END
GO

--select * from ReceiverQueue
--select * from SenderQueue
RECEIVE CONVERT(NVARCHAR(max), message_body) AS message FROM ReceiverQueue

-- Cleanup
DROP SERVICE Sender
DROP SERVICE Receiver
DROP QUEUE SenderQueue
DROP QUEUE ReceiverQueue
DROP CONTRACT HelloContract
DROP MESSAGE TYPE HelloMessage
GO

Secure dialogs require a database master key. Either create a database master key, either add an WITH ENCRYPTION = OFF clause to the BEGIN DIALOG statement.

See this mini troubleshooting guide at http://blogs.msdn.com/remusrusanu/archive/2005/12/20/506221.aspx

HTH,
~ Remus

|||

That was it. I knew I wasn't totally crazy!

Thanks

|||

Basically there are a lot of examples out there that worked on the the Beta 2, but don't work on the RTM: http://www.sqljunkies.com/WebLog/ktegels/archive/2006/03/08/18625.aspx

|||

Is there a way to insert a message into a SB queue without creating the Contract, Services, Sender and Reciever Queue's?

For example, I want a queue that I can send messages for a process job queue. It feels real redundant to have to send a message to a queue that sends its message to the queue. I just want a gueue that has an attached procedure I can fire.

I may just use a table with a trigger as that almost makes more sense.

Scooter

|||

The only way to send a message is with a conversation, with all wisles and bells.

You must create at least a service and a queue. The [DEFAULT] (name is case sensitive) contract and message type are always there and you can use them. In fact, you can ommit the ON CONTRACT clause of BEGIN DIALOG and the MESSAGE TYPE clause of SEND and the [DEFAULT] contract and message type will be used.

A trigger is executed in the context of the caller and the caller must wait until the trigger completes, while an activated procedure executes asynchronously, after the caller commited it's call. This is the typical reason why people are considering using activation instead or triggers. If the code to be executed by the activation/trigger is small and compact, or the caller can tolerate a long delay waiting for the trigger to complete, then triggers are simpler to deploy indeed.

What was the reason you considered activation instead of triggers in the first place?

HTH,
~ Remus

|||

Here is an example similar to what Remus described. I simplified your code snippet to kick something off in the background using one queue and one service. Btw, I also added (as comments) two END CONVERSATION calls. If you don't do these somewhere in your application you will orphan the row in the sys.conversation_endpoints table that represents the conversation you began.

-Gerald Hinson

-- code snippet -

CREATE QUEUE ReceiverQueue

GO

CREATE SERVICE Receiver ON QUEUE ReceiverQueue ([DEFAULT])

GO

DECLARE @.InitiatorConversationHandle UNIQUEIDENTIFIER;

DECLARE @.message NVARCHAR(100);

BEGIN TRANSACTION;

BEGIN DIALOG @.InitiatorConversationHandle

FROM SERVICE Receiver

TO SERVICE 'Receiver';

-- Send a message on the conversation

SET @.message = 'Hello, World';

SEND ON CONVERSATION @.InitiatorConversationHandle (@.message);

COMMIT TRANSACTION

--select * from ReceiverQueue

--select * from SenderQueue

DECLARE @.TargetConversationHandle UNIQUEIDENTIFIER;

DECLARE @.MessageBody NVARCHAR(100);

BEGIN TRANSACTION;

RECEIVE @.TargetConversationHandle=conversation_handle,

@.MessageBody=message_body FROM ReceiverQueue;

print @.MessageBody

-- Don't forget to issue END CONVERSATION on the two conversation handles!!!

-- Put these where appropriate for your application

--END CONVERSATION @.InitiatorConversationHandle;

--END CONVERSATION @.TargetConversationHandle;

COMMIT TRANSACTION;

-- Cleanup

DROP SERVICE Receiver

DROP QUEUE ReceiverQueue

GO

Frustrated with Service Broker Example

I want to use a Service Broker Queue as a processing queue. I found several examples on the WEB and have tried to make them work but they do not. Below is the example I used and I ASSUME I should get a Hello World in the Message field at the end but its empty.

Any ideas as to why?
I have tried
select * from ReceiverQueue
and
select * from SenderQueue
before I do a RECIEVE and both QUEUE's are empty

Scooter
-CODE--
USE AdventureWorks
GO

CREATE MESSAGE TYPE HelloMessage VALIDATION = NONE
GO

CREATE CONTRACT HelloContract (HelloMessage SENT BY INITIATOR)
GO

CREATE QUEUE SenderQueue
CREATE QUEUE ReceiverQueue
GO

CREATE SERVICE Sender ON QUEUE SenderQueue
CREATE SERVICE Receiver ON QUEUE ReceiverQueue (HelloContract)
GO

DECLARE @.conversationHandle UNIQUEIDENTIFIER
DECLARE @.message NVARCHAR(100)
BEGIN
BEGIN TRANSACTION;
BEGIN DIALOG @.conversationHandle
FROM SERVICE Sender
TO SERVICE 'Receiver'
ON CONTRACT HelloContract
-- Send a message on the conversation
SET @.message = 'Hello, World';
SEND ON CONVERSATION @.conversationHandle
MESSAGE TYPE HelloMessage (@.message)
COMMIT TRANSACTION
END
GO

--select * from ReceiverQueue
--select * from SenderQueue
RECEIVE CONVERT(NVARCHAR(max), message_body) AS message FROM ReceiverQueue

-- Cleanup
DROP SERVICE Sender
DROP SERVICE Receiver
DROP QUEUE SenderQueue
DROP QUEUE ReceiverQueue
DROP CONTRACT HelloContract
DROP MESSAGE TYPE HelloMessage
GO

Secure dialogs require a database master key. Either create a database master key, either add an WITH ENCRYPTION = OFF clause to the BEGIN DIALOG statement.

See this mini troubleshooting guide at http://blogs.msdn.com/remusrusanu/archive/2005/12/20/506221.aspx

HTH,
~ Remus

|||

That was it. I knew I wasn't totally crazy!

Thanks

|||

Basically there are a lot of examples out there that worked on the the Beta 2, but don't work on the RTM: http://www.sqljunkies.com/WebLog/ktegels/archive/2006/03/08/18625.aspx

|||

Is there a way to insert a message into a SB queue without creating the Contract, Services, Sender and Reciever Queue's?

For example, I want a queue that I can send messages for a process job queue. It feels real redundant to have to send a message to a queue that sends its message to the queue. I just want a gueue that has an attached procedure I can fire.

I may just use a table with a trigger as that almost makes more sense.

Scooter

|||

The only way to send a message is with a conversation, with all wisles and bells.

You must create at least a service and a queue. The [DEFAULT] (name is case sensitive) contract and message type are always there and you can use them. In fact, you can ommit the ON CONTRACT clause of BEGIN DIALOG and the MESSAGE TYPE clause of SEND and the [DEFAULT] contract and message type will be used.

A trigger is executed in the context of the caller and the caller must wait until the trigger completes, while an activated procedure executes asynchronously, after the caller commited it's call. This is the typical reason why people are considering using activation instead or triggers. If the code to be executed by the activation/trigger is small and compact, or the caller can tolerate a long delay waiting for the trigger to complete, then triggers are simpler to deploy indeed.

What was the reason you considered activation instead of triggers in the first place?

HTH,
~ Remus

|||

Here is an example similar to what Remus described. I simplified your code snippet to kick something off in the background using one queue and one service. Btw, I also added (as comments) two END CONVERSATION calls. If you don't do these somewhere in your application you will orphan the row in the sys.conversation_endpoints table that represents the conversation you began.

-Gerald Hinson

-- code snippet -

CREATE QUEUE ReceiverQueue

GO

CREATE SERVICE Receiver ON QUEUE ReceiverQueue ([DEFAULT])

GO

DECLARE @.InitiatorConversationHandle UNIQUEIDENTIFIER;

DECLARE @.message NVARCHAR(100);

BEGIN TRANSACTION;

BEGIN DIALOG @.InitiatorConversationHandle

FROM SERVICE Receiver

TO SERVICE 'Receiver';

-- Send a message on the conversation

SET @.message = 'Hello, World';

SEND ON CONVERSATION @.InitiatorConversationHandle (@.message);

COMMIT TRANSACTION

--select * from ReceiverQueue

--select * from SenderQueue

DECLARE @.TargetConversationHandle UNIQUEIDENTIFIER;

DECLARE @.MessageBody NVARCHAR(100);

BEGIN TRANSACTION;

RECEIVE @.TargetConversationHandle=conversation_handle,

@.MessageBody=message_body FROM ReceiverQueue;

print @.MessageBody

-- Don't forget to issue END CONVERSATION on the two conversation handles!!!

-- Put these where appropriate for your application

--END CONVERSATION @.InitiatorConversationHandle;

--END CONVERSATION @.TargetConversationHandle;

COMMIT TRANSACTION;

-- Cleanup

DROP SERVICE Receiver

DROP QUEUE ReceiverQueue

GO

sql

from sql - checking if a file exists in a given share

Hi,
from sql, what is the best way to check if a file exists in a share - given
the file's partial name (for example the first 20 characters) ?
I thought about saving the result set of "xp_cmdShell dir shareName ... "
into a temp table and query the temp table for the filename... but are there
better ways that do not require xp_cmdShell (that needs special permissions
to execute)?
ThanksYou could try using the sp_OA* procedures and the FileSystemObject in VBS.
ML
http://milambda.blogspot.com/|||This sounds interesting. Could you please point me to where I can find
sample code?
Thank you.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:D4DE0DE1-BA83-4810-B385-FC6005DE4B45@.microsoft.com...
> You could try using the sp_OA* procedures and the FileSystemObject in VBS.
>
> ML
> --
> http://milambda.blogspot.com/|||A few samples are available in Books Online, the rest comes down to your
experience with VBS.
This sample demonstrates the use of the sp_OA* procedures:
http://msdn.microsoft.com/library/d...r />
_2ktw.asp
ML
http://milambda.blogspot.com/|||If your on 2005, you could do a UDF such as:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static bool FileExists(string path)
{
return File.Exists(path);
}
};
William Stacey [MVP]
"Ramadan" <noOne@.hotmail.com> wrote in message
news:eIcSTaPAGHA.532@.TK2MSFTNGP15.phx.gbl...
> Hi,
> from sql, what is the best way to check if a file exists in a share -
> given
> the file's partial name (for example the first 20 characters) ?
> I thought about saving the result set of "xp_cmdShell dir shareName ...
> "
> into a temp table and query the temp table for the filename... but are
> there
> better ways that do not require xp_cmdShell (that needs special
> permissions
> to execute)?
> Thanks
>|||You can check this via the undocumented procedure (but be aware that
this is undocumented, not supported and could be deppricated in further
versions):
http://www.sql-server-performance.c..._procedures.asp
xp_fileexist
You can use this extended stored procedure to determine whether a
particular file exists on the disk or not. The syntax for this xp is:
EXECUTE xp_fileexist filename [, file_exists INT OUTPUT]
For example, to check whether the file boot.ini exists on disk c: or
not, run:
EXEC master..xp_fileexist 'c:\boot.ini'
HTH, jens Suessmeyer.sql

From MDB TO ADP

I am trying to move a database from an MDB to an ADP, but I'm getting some errors. For example

SELECT MachineList.[Machine Number], MachineList.[Serial Number], MachineList.Location, MachineList.[Manuf ID], MachineList.[Game Theme], MachineList.Class, MachineList.Installed, MachineList.Certified, MachineList.[EZ-Pay]
FROM MachineList
WHERE (((MachineList.[Machine Number])=[Please enter the machine number]))
ORDER BY MachineList.Location;

Now I know there is a difference between Jet SQL and MS SQL, I guess I just need to revert this to MSSQL

Heres the Error Message I'm getting

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Enter the installation date'.Never Mind I Figured It Out Its A Function...hoooorrraaaayyyyyyyy

From 1 field to another

Hi, how can I copy the records from some table and field to the same table
but another field?
Example: Table: Customer->Address field
to Table: Customer->AddressBkp field
Select inside Insert ? how?
Thanks!> Select inside Insert ? how?
Try the example below and map the other columns as desired too:
INSERT INTO dbo,Customer (AddressBkp)
SELECT Address FROM dbo,Customer
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Paulo" <prbspfc@.uol.com.br> wrote in message
news:%23BmusD9bIHA.4684@.TK2MSFTNGP06.phx.gbl...
> Hi, how can I copy the records from some table and field to the same table
> but another field?
> Example: Table: Customer->Address field
> to Table: Customer->AddressBkp field
> Select inside Insert ? how?
> Thanks!
>|||I think you need to do an update:
UPDATE Customer
SET AddressBkp = Address
Note that this query does not have a WHERE clause, so all rows in the
Customer table will be updated.
HTH,
Plamen Ratchev
http://www.SQLStudio.com

French characters on English Platform dropped

Hi all,
I Had installed an english platform with full text indexing enable.
My data are French (with some accents for example).
When I search with a CONTAINS Statement on my Data, SQL Server drops my
accentuates characters.
How can I resolve this problem ?
What must I install on my platform to resolve this problem ?
Thanks for your answers.
Alex.
Exactly what do you mean by this.
Do you mean that a search on cafe will not match with a search on the
accented version of cafe?
This problem is fixed in SQL 2005.
In the meantime you have to expand your search on both the accented and
unaccented version of the search term.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Alexandre BARBIER" <(del_this)abarbier@.sopragroup.com> wrote in message
news:e8S11xylEHA.704@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I Had installed an english platform with full text indexing enable.
> My data are French (with some accents for example).
> When I search with a CONTAINS Statement on my Data, SQL Server drops my
> accentuates characters.
> How can I resolve this problem ?
> What must I install on my platform to resolve this problem ?
> Thanks for your answers.
> Alex.
>
|||Hi,
No, I mean that my data are in french version and my full text search turn
on an English Platform.
When I search a french word, the fulltext search dropped my accented
characters so my search doesn't work.
Any Ideas ?
Thanks.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23CSrWe0lEHA.3016@.tk2msftngp13.phx.gbl...
> Exactly what do you mean by this.
> Do you mean that a search on cafe will not match with a search on the
> accented version of cafe?
> This problem is fixed in SQL 2005.
> In the meantime you have to expand your search on both the accented and
> unaccented version of the search term.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Alexandre BARBIER" <(del_this)abarbier@.sopragroup.com> wrote in message
> news:e8S11xylEHA.704@.TK2MSFTNGP09.phx.gbl...
>
|||Alexandre,
This has been a long outstanding bug in SQL Server 7.0 and SQL Server 2000,
that is only "fixed" in SQL Server 2005 (Yukon) via the following new T-SQL
syntax:
CREATE FULLTEXT CATALOG Employee_FTC WITH ACCENT_SENSITIVITY = ON
CREATE FULLTEXT CATALOG Employee_FTC WITH ACCENT_SENSITIVITY=OFF
-- Or by using ALTER FULLTEXT CATALOG:
ALTER FULLTEXT CATALOG Employee_FTC REBUILD WITH ACCENT_SENSITIVITY=ON
The SQL Server 7.0 or SQL Server 2000 solution requires the duplication of
the accented data with the removal of accents via a UDF and insert/update
trigger to maintain the duplicate data. You would create a FT Index on the
non-accented data and return the accented data to your searcher.
Regards,
John
"Alexandre BARBIER" <(del_this)abarbier@.sopragroup.com> wrote in message
news:eE9gBIWmEHA.412@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Hi,
> No, I mean that my data are in french version and my full text search turn
> on an English Platform.
> When I search a french word, the fulltext search dropped my accented
> characters so my search doesn't work.
> Any Ideas ?
> Thanks.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23CSrWe0lEHA.3016@.tk2msftngp13.phx.gbl...
my
>

FREETEXT results

Using the following query I get my desired results. However, in the
resulting recordset, how can I show for example 10 words before and 10 words
after the keyword or phrase that was searched upon? If I get 20 resulting
records I think it would be easier for the user to decide which they want to
view. I know Google does this. Is this an SQL issue or ASP issue?
thanks
DECLARE @.SearchString varchar(100)
SET @.SearchString = ' "stress" '
SELECT KEY_TBL.RANK, Title, Body
FROM Articles INNER JOIN
FREETEXTTABLE(Articles,*, @.SearchString) AS KEY_TBL
ON Articles.ID = KEY_TBL.[KEY]
ORDER BY Rank DESC
string handling is more efficiently done on the client level as opposed to
within SQL Server.
"shank" <shank@.tampabay.rr.com> wrote in message
news:Obj%23ddqZEHA.1508@.TK2MSFTNGP09.phx.gbl...
> Using the following query I get my desired results. However, in the
> resulting recordset, how can I show for example 10 words before and 10
words
> after the keyword or phrase that was searched upon? If I get 20 resulting
> records I think it would be easier for the user to decide which they want
to
> view. I know Google does this. Is this an SQL issue or ASP issue?
> thanks
> DECLARE @.SearchString varchar(100)
> SET @.SearchString = ' "stress" '
> SELECT KEY_TBL.RANK, Title, Body
> FROM Articles INNER JOIN
> FREETEXTTABLE(Articles,*, @.SearchString) AS KEY_TBL
> ON Articles.ID = KEY_TBL.[KEY]
> ORDER BY Rank DESC
>
|||Hilary,
While in some cases, client-side process might be better, for example
client-side paging and sorting of results, however, in this case, I must
respectively disagree...
Shank, you can use Substring and PatIndex along with your FREETEXTTABLE
query and get the "Goggle like" results that you have requested.
Specifically, the following SQL FTS query on the pubs table pub_info will
return rows that match the FTS search word (books) and display the near by
words from 20 characters before the searched keyword(books) for a total
length of 100 characters.
SELECT pub_id, SubString(pr_info,PatIndex ('%books%',pr_info)-20,100)
FROM pub_info
WHERE Contains(pr_info, 'books')
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:#eM2eLrZEHA.644@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> string handling is more efficiently done on the client level as opposed to
> within SQL Server.
>
> "shank" <shank@.tampabay.rr.com> wrote in message
> news:Obj%23ddqZEHA.1508@.TK2MSFTNGP09.phx.gbl...
> words
resulting[vbcol=seagreen]
want
> to
>
|||I really don't think so. In fact I know so. You can easily demonstrate this
within vbscript.
In fact I have written and ISAPI extension that does this - and posted it
here. Thinking about doing it within a database is simply not a good choice.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%2364LxM9ZEHA.2792@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Hilary,
> While in some cases, client-side process might be better, for example
> client-side paging and sorting of results, however, in this case, I must
> respectively disagree...
> Shank, you can use Substring and PatIndex along with your FREETEXTTABLE
> query and get the "Goggle like" results that you have requested.
> Specifically, the following SQL FTS query on the pubs table pub_info will
> return rows that match the FTS search word (books) and display the near by
> words from 20 characters before the searched keyword(books) for a total
> length of 100 characters.
> SELECT pub_id, SubString(pr_info,PatIndex ('%books%',pr_info)-20,100)
> FROM pub_info
> WHERE Contains(pr_info, 'books')
> Regards,
> John
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:#eM2eLrZEHA.644@.tk2msftngp13.phx.gbl...
to
> resulting
> want
>
|||Hi Hilary,
Then I guess we can agree that we disagree <G>, as I did say in " some
cases" and not in all cases, so we can agree, on a case-by-case basis. Are
you saying that in all cases, that all types of "string handling" are "bad"
if handled on the server-side?
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:ue$hecFaEHA.2844@.TK2MSFTNGP12.phx.gbl...
> I really don't think so. In fact I know so. You can easily demonstrate
this
> within vbscript.
> In fact I have written and ISAPI extension that does this - and posted it
> here. Thinking about doing it within a database is simply not a good
choice.[vbcol=seagreen]
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:%2364LxM9ZEHA.2792@.TK2MSFTNGP09.phx.gbl...
will[vbcol=seagreen]
by[vbcol=seagreen]
opposed[vbcol=seagreen]
> to
10
>