FTP from AS400

I have been handed the directive that I am to ftp flat files from our AS400 to the SQL server where the web interfaces will read the data.

I need to know if anyone out here knows how to automate the FTP download from the AS400 system.

I don't know any UNIX and even if I did the SQL box is also running a 3rd party shipping label system that disallows the use/installation of MSK Toolkit.

I thought I could use the FTP Task in the DTS package but I don't know what to put for the internet location and this is certainly not a Mapped path.

Please Help!

There is a built-in ftp command-line utility in Windows Server 2003 that you can use to automate the ftp download. The FTP task in DTS should take a url like ftp://some_server.com/files/d1.dat. If you need more details on the DTS/SSIS task then please post this question in the SQL Server Integration Services forum here.

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

FrontPage/SharePoint Designer Search and Replace Fills Transaction

When we run a search and replace across all pages in one our web site (104
pages), my transaction log on the SQL Server grows by about 200 MB of space!
This has caused major system headaches - the transaction log fills, causes
IIS to fail, causing my clients using FP / SP Designer not to be able to save
the files they have opened / edited.
That database is 400MB in size while the Transaction Log is now over 6GB.
and the minimum Log is now 6GB too. We can't keep having this log grow
exponential like this.
Also - In the event log, I see that there a couple of 'webparts' that are
failing on the global search and replace: WSS 2.0 Error: Failing in loading
assemble Customer.WebParts, Version=1.0.2.4.
We're running SQL Server 2000 SP4, on a Windows 2003 Server with WSS 2.0
using the latest versions of FrontPage and SharePoint Designer.
Thoughts?
After posting this, I started to search the WSS / Sharepoint forums (instead
of just SQL Server) and see that this is a common issue and found a variety
of ways to correct this.
Iza
"iZa" wrote:

> When we run a search and replace across all pages in one our web site (104
> pages), my transaction log on the SQL Server grows by about 200 MB of space!
> This has caused major system headaches - the transaction log fills, causes
> IIS to fail, causing my clients using FP / SP Designer not to be able to save
> the files they have opened / edited.
> That database is 400MB in size while the Transaction Log is now over 6GB.
> and the minimum Log is now 6GB too. We can't keep having this log grow
> exponential like this.
> Also - In the event log, I see that there a couple of 'webparts' that are
> failing on the global search and replace: WSS 2.0 Error: Failing in loading
> assemble Customer.WebParts, Version=1.0.2.4.
> We're running SQL Server 2000 SP4, on a Windows 2003 Server with WSS 2.0
> using the latest versions of FrontPage and SharePoint Designer.
> Thoughts?

FrontPage/SharePoint Designer Search and Replace Fills Transaction

When we run a search and replace across all pages in one our web site (104
pages), my transaction log on the SQL Server grows by about 200 MB of space!
This has caused major system headaches - the transaction log fills, causes
IIS to fail, causing my clients using FP / SP Designer not to be able to save
the files they have opened / edited.
That database is 400MB in size while the Transaction Log is now over 6GB.
and the minimum Log is now 6GB too. We can't keep having this log grow
exponential like this.
Also - In the event log, I see that there a couple of 'webparts' that are
failing on the global search and replace: WSS 2.0 Error: Failing in loading
assemble Customer.WebParts, Version=1.0.2.4.
We're running SQL Server 2000 SP4, on a Windows 2003 Server with WSS 2.0
using the latest versions of FrontPage and SharePoint Designer.
Thoughts?After posting this, I started to search the WSS / Sharepoint forums (instead
of just SQL Server) and see that this is a common issue and found a variety
of ways to correct this.
Iza
"iZa" wrote:
> When we run a search and replace across all pages in one our web site (104
> pages), my transaction log on the SQL Server grows by about 200 MB of space!
> This has caused major system headaches - the transaction log fills, causes
> IIS to fail, causing my clients using FP / SP Designer not to be able to save
> the files they have opened / edited.
> That database is 400MB in size while the Transaction Log is now over 6GB.
> and the minimum Log is now 6GB too. We can't keep having this log grow
> exponential like this.
> Also - In the event log, I see that there a couple of 'webparts' that are
> failing on the global search and replace: WSS 2.0 Error: Failing in loading
> assemble Customer.WebParts, Version=1.0.2.4.
> We're running SQL Server 2000 SP4, on a Windows 2003 Server with WSS 2.0
> using the latest versions of FrontPage and SharePoint Designer.
> Thoughts?sql

FrontPage/SharePoint Designer Search and Replace Fills Transaction

When we run a search and replace across all pages in one our web site (104
pages), my transaction log on the SQL Server grows by about 200 MB of space!
This has caused major system headaches - the transaction log fills, causes
IIS to fail, causing my clients using FP / SP Designer not to be able to sav
e
the files they have opened / edited.
That database is 400MB in size while the Transaction Log is now over 6GB.
and the minimum Log is now 6GB too. We can't keep having this log grow
exponential like this.
Also - In the event log, I see that there a couple of 'webparts' that are
failing on the global search and replace: WSS 2.0 Error: Failing in loading
assemble Customer.WebParts, Version=1.0.2.4.
We're running SQL Server 2000 SP4, on a Windows 2003 Server with WSS 2.0
using the latest versions of FrontPage and SharePoint Designer.
Thoughts?After posting this, I started to search the WSS / Sharepoint forums (instead
of just SQL Server) and see that this is a common issue and found a variety
of ways to correct this.
Iza
"iZa" wrote:

> When we run a search and replace across all pages in one our web site (104
> pages), my transaction log on the SQL Server grows by about 200 MB of spac
e!
> This has caused major system headaches - the transaction log fills, causes
> IIS to fail, causing my clients using FP / SP Designer not to be able to s
ave
> the files they have opened / edited.
> That database is 400MB in size while the Transaction Log is now over 6GB.
> and the minimum Log is now 6GB too. We can't keep having this log grow
> exponential like this.
> Also - In the event log, I see that there a couple of 'webparts' that are
> failing on the global search and replace: WSS 2.0 Error: Failing in loadin
g
> assemble Customer.WebParts, Version=1.0.2.4.
> We're running SQL Server 2000 SP4, on a Windows 2003 Server with WSS 2.0
> using the latest versions of FrontPage and SharePoint Designer.
> Thoughts?

from test environment to web hosting company

I must be missing something, and its starting to fusterate me. Bear with me here.

I created a site for a ...client I guess you'd call it, and made this really slick newletter generator thing. The people from the web enter in their info, and if they want, they sign up for a newsletter -- all tied into a db, 1 table, 4 stored procedures, REALLY simple stuff. They insisted I used a certain webhost which, on paper, looks like it will fit the bill. I'm starting to question that.

On top of the newsletter thing, I created an aspnetdb for the "administration" side of it for her to log into and send out the newsletter so total, there's 2 dbs in the app_data folder. Locally, it works GREAT and on my test box (iis6) that is running 2k5 express. The webhost runs sql2k in (what I consider) a bastardized way. Can't use the management studio, can't use anything except a really weak web-based interface which adds to my fusteration.

Anyway, my questions : 1, is there a way to make the mdf files work with sql2k without having to re-do the whole thing and 2, if I have to redo it, does anyone have an example connection string that might help out?

Fubarian:

Can't use the management studio, can't use anything except a really weak web-based interface which adds to my fusteration.

Did you mean you can only access the SQL2K instance through web? If you have login to the SQL2K, you can register the SQL2K intance in Management Studio by using your account.

1. From your description I understand your website is using attached database file under your app_data. As I know the database you're using should be version 9.0, that means it can be used in SQL2005 or SQLExpress instance, but not SQL2K instance. You can check the version by right clieck the databasefile in Server Explorer->New query-> execute 'SELECT @.@.version'. So you have to transfer the tables in the database files into a database on the SQL2K instance. You can do this by using Import/Export Wizard.

2. The 2nd thing you need to do is change the connection string to point to the SQL2K instance. You can refer to this post:

http://forums.asp.net/thread/1281242.aspx

From excel file into MS SQL server

I need to find a way to upload an Excel file into an MS SQL database
using a web control front end. I have my ASP.Net control (using C#)
uploading a file to a directory, but the server people now tell me that
I cannot have a writeable area for the web and have a DTS see it as this
is too much of a security risk. So, I need a way to read the file
directly into the database. I've no idea how to do this. Does anyone
have ideas? I know loading MS Office into the web server is out of the
question. The webserver and database server are not the same physical
machine.
Thanks.
Yes you can upload any file directly into SQL Server.
Here's an example:
http://SteveOrr.net/Articles/EasyUploads.aspx
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net
"no one" <noone@.yahoo.com> wrote in message
news:41FEF4E4.7EED35A4@.yahoo.com...
>I need to find a way to upload an Excel file into an MS SQL database
> using a web control front end. I have my ASP.Net control (using C#)
> uploading a file to a directory, but the server people now tell me that
> I cannot have a writeable area for the web and have a DTS see it as this
> is too much of a security risk. So, I need a way to read the file
> directly into the database. I've no idea how to do this. Does anyone
> have ideas? I know loading MS Office into the web server is out of the
> question. The webserver and database server are not the same physical
> machine.
> Thanks.
>
|||Thanks for the link, but this is not what I want to do. I want to put the
data from the file into a table, not the file itself.
"Steve C. Orr [MVP, MCSD]" wrote:
[vbcol=seagreen]
> Yes you can upload any file directly into SQL Server.
> Here's an example:
> http://SteveOrr.net/Articles/EasyUploads.aspx
> --
> I hope this helps,
> Steve C. Orr, MCSD, MVP
> http://SteveOrr.net
> "no one" <noone@.yahoo.com> wrote in message
> news:41FEF4E4.7EED35A4@.yahoo.com...
|||"no one" <noone@.yahoo.com> wrote in message
news:41FF763D.6BFF199A@.yahoo.com...
> Thanks for the link, but this is not what I want to do. I want to put the
> data from the file into a table, not the file itself.
> "Steve C. Orr [MVP, MCSD]" wrote:
>
|||"no one" <noone@.yahoo.com> wrote in message
news:41FF763D.6BFF199A@.yahoo.com...
> Thanks for the link, but this is not what I want to do. I want to put the
> data from the file into a table, not the file itself.
> "Steve C. Orr [MVP, MCSD]" wrote:
>
<snip>
Have a look at DTS - it can load directly from Excel (or most other things)
to MSSQL, and you can change the source and destination connections at
runtime. This link discusses executing a package from ASP:
http://www.sqldts.com/default.aspx?207
Otherwise, you can parse the file and generate your own INSERT statements
(slow), or convert it to a flat text file and then use bcp.exe or BULK
INSERT to load the data.
Simon
|||I'm not sure what all security restrictions they want you to
follow, but one option is to use Openrowset and read the
file into a table using T-SQL, e.g.
insert into YourTable
SELECT * FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0; HDR=NO;Database=C:\YourFilet.xls',
'SELECT * FROM [Sheet1$]')
-Sue
On Tue, 01 Feb 2005 12:25:52 GMT, no one <noone@.yahoo.com>
wrote:
[vbcol=seagreen]
>Thanks for the link, but this is not what I want to do. I want to put the
>data from the file into a table, not the file itself.
>"Steve C. Orr [MVP, MCSD]" wrote:
|||Oh, I now see your dilemma. That's fairly complex functionality.
My only idea is this 3rd party product that can open an excel file from a
memory stream and will allow you to extract data from it:
http://www.SteveOrr.net/Reviews/AsposeWord.aspx
http://www.aspose.com/Products/Aspose.Excel/
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net
"no one" <noone@.yahoo.com> wrote in message
news:41FF763D.6BFF199A@.yahoo.com...
> Thanks for the link, but this is not what I want to do. I want to put the
> data from the file into a table, not the file itself.
> "Steve C. Orr [MVP, MCSD]" wrote:
>

From excel file into MS SQL server

I need to find a way to upload an Excel file into an MS SQL database
using a web control front end. I have my ASP.Net control (using C#)
uploading a file to a directory, but the server people now tell me that
I cannot have a writeable area for the web and have a DTS see it as this
is too much of a security risk. So, I need a way to read the file
directly into the database. I've no idea how to do this. Does anyone
have ideas? I know loading MS Office into the web server is out of the
question. The webserver and database server are not the same physical
machine.

Thanks.Yes you can upload any file directly into SQL Server.
Here's an example:
http://SteveOrr.net/Articles/EasyUploads.aspx

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net

"no one" <noone@.yahoo.com> wrote in message
news:41FEF4E4.7EED35A4@.yahoo.com...
>I need to find a way to upload an Excel file into an MS SQL database
> using a web control front end. I have my ASP.Net control (using C#)
> uploading a file to a directory, but the server people now tell me that
> I cannot have a writeable area for the web and have a DTS see it as this
> is too much of a security risk. So, I need a way to read the file
> directly into the database. I've no idea how to do this. Does anyone
> have ideas? I know loading MS Office into the web server is out of the
> question. The webserver and database server are not the same physical
> machine.
> Thanks.|||Thanks for the link, but this is not what I want to do. I want to put the
data from the file into a table, not the file itself.

"Steve C. Orr [MVP, MCSD]" wrote:

> Yes you can upload any file directly into SQL Server.
> Here's an example:
> http://SteveOrr.net/Articles/EasyUploads.aspx
> --
> I hope this helps,
> Steve C. Orr, MCSD, MVP
> http://SteveOrr.net
> "no one" <noone@.yahoo.com> wrote in message
> news:41FEF4E4.7EED35A4@.yahoo.com...
> >I need to find a way to upload an Excel file into an MS SQL database
> > using a web control front end. I have my ASP.Net control (using C#)
> > uploading a file to a directory, but the server people now tell me that
> > I cannot have a writeable area for the web and have a DTS see it as this
> > is too much of a security risk. So, I need a way to read the file
> > directly into the database. I've no idea how to do this. Does anyone
> > have ideas? I know loading MS Office into the web server is out of the
> > question. The webserver and database server are not the same physical
> > machine.
> > Thanks.|||"no one" <noone@.yahoo.com> wrote in message
news:41FF763D.6BFF199A@.yahoo.com...
> Thanks for the link, but this is not what I want to do. I want to put the
> data from the file into a table, not the file itself.
> "Steve C. Orr [MVP, MCSD]" wrote:

<snip
Have a look at DTS - it can load directly from Excel (or most other things)
to MSSQL, and you can change the source and destination connections at
runtime. This link discusses executing a package from ASP:

http://www.sqldts.com/default.aspx?207

Otherwise, you can parse the file and generate your own INSERT statements
(slow), or convert it to a flat text file and then use bcp.exe or BULK
INSERT to load the data.

Simon|||Oh, I now see your dilemma. That's fairly complex functionality.
My only idea is this 3rd party product that can open an excel file from a
memory stream and will allow you to extract data from it:
http://www.SteveOrr.net/Reviews/AsposeWord.aspx
http://www.aspose.com/Products/Aspose.Excel/

--
I hope this helps,
Steve C. Orr, MCSD, MVP
http://SteveOrr.net

"no one" <noone@.yahoo.com> wrote in message
news:41FF763D.6BFF199A@.yahoo.com...
> Thanks for the link, but this is not what I want to do. I want to put the
> data from the file into a table, not the file itself.
> "Steve C. Orr [MVP, MCSD]" wrote:
>> Yes you can upload any file directly into SQL Server.
>> Here's an example:
>> http://SteveOrr.net/Articles/EasyUploads.aspx
>>
>> --
>> I hope this helps,
>> Steve C. Orr, MCSD, MVP
>> http://SteveOrr.net
>>
>> "no one" <noone@.yahoo.com> wrote in message
>> news:41FEF4E4.7EED35A4@.yahoo.com...
>> >I need to find a way to upload an Excel file into an MS SQL database
>> > using a web control front end. I have my ASP.Net control (using C#)
>> > uploading a file to a directory, but the server people now tell me that
>> > I cannot have a writeable area for the web and have a DTS see it as
>> > this
>> > is too much of a security risk. So, I need a way to read the file
>> > directly into the database. I've no idea how to do this. Does anyone
>> > have ideas? I know loading MS Office into the web server is out of the
>> > question. The webserver and database server are not the same physical
>> > machine.
>>> > Thanks.
>|||DTS has been mentioned.
3rd parties that also do the job: SQLWays , DBUnit.|||"no one" <noone@.yahoo.com> wrote in message
news:41FEF4E4.7EED35A4@.yahoo.com...
>I need to find a way to upload an Excel file into an MS SQL database
> using a web control front end. I have my ASP.Net control (using C#)
> uploading a file to a directory, but the server people now tell me that
> I cannot have a writeable area for the web and have a DTS see it as this
> is too much of a security risk. So, I need a way to read the file
> directly into the database. I've no idea how to do this. Does anyone
> have ideas? I know loading MS Office into the web server is out of the
> question. The webserver and database server are not the same physical
> machine.
> Thanks.

Did you know cross-posting is one of the things some ISPs pick to identify
spam?

This'd be a whole lot easier if your app was windows rather than web.
Coz you don't have any way to be running c# on our client machine.

Is this really an extranet app?
I'd be concerned about who's loading what out a spreadsheet onto my database
server.
It does sound like a good way to open up a hole for hackers to walk in
through.
Uploading excel spreadsheets is also a good way to get a big heap of bad
data into a system.

Anyhow, it piqued my interest so I did a search on "javascript excel"
Here's an interesting page I found.
http://www.planet-source-code.com/v...d=2180&lngWId=2

Some gotchas but maybe they're not a problem for you.

--
Regards,
Andy O'Neill

Freezed column headers in RS Webreports

Hi,
a lot of my users were asking me to have the possibilty of a freeze
pane (similar to excel) in the RS web reports because often the reports
are to long to see the column haeders. Especially when you have
drilldowns it would help.
Has somebody tried to implement such feature by DLL to RS 2000. Is this
feature planned for RS 2005 '
thanks
BBIf I remember correctly, that might be in SQL 2005
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"BB_Reporting" <Bruce.Baessler@.web.de> wrote in message
news:1123855073.170843.218720@.o13g2000cwo.googlegroups.com...
> Hi,
>
> a lot of my users were asking me to have the possibilty of a freeze
> pane (similar to excel) in the RS web reports because often the reports
> are to long to see the column haeders. Especially when you have
> drilldowns it would help.
>
> Has somebody tried to implement such feature by DLL to RS 2000. Is this
> feature planned for RS 2005 '
>
> thanks
>
> BB
>|||I am sorry for not answering your question directly... I also have not seen
any 3rd party prods which currently do that in sql 2000 either
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"BB_Reporting" <Bruce.Baessler@.web.de> wrote in message
news:1123855073.170843.218720@.o13g2000cwo.googlegroups.com...
> Hi,
>
> a lot of my users were asking me to have the possibilty of a freeze
> pane (similar to excel) in the RS web reports because often the reports
> are to long to see the column haeders. Especially when you have
> drilldowns it would help.
>
> Has somebody tried to implement such feature by DLL to RS 2000. Is this
> feature planned for RS 2005 '
>
> thanks
>
> BB
>|||This is available in RS 2005.
I don't know of anyone who has implemented this for RS 2000 - but in that
case, you would need to implement a full custom rendering extension yourself
for RS 2000 (which is a major effort and lots of work).
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"BB_Reporting" <Bruce.Baessler@.web.de> wrote in message
news:1123855073.170843.218720@.o13g2000cwo.googlegroups.com...
> Hi,
>
> a lot of my users were asking me to have the possibilty of a freeze
> pane (similar to excel) in the RS web reports because often the reports
> are to long to see the column haeders. Especially when you have
> drilldowns it would help.
>
> Has somebody tried to implement such feature by DLL to RS 2000. Is this
> feature planned for RS 2005 '
>
> thanks
>
> BB
>|||Hi Robert,
I haven't had a chance to test this yet so, with fixed headers in 2005 will
the scroll position stay in place for auto-refreshing reports or will it be
a pain an spring back to the top?
Thanks in advance,
James Snape
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:O8oAI$goFHA.904@.TK2MSFTNGP10.phx.gbl...
> This is available in RS 2005.
> I don't know of anyone who has implemented this for RS 2000 - but in that
> case, you would need to implement a full custom rendering extension
> yourself for RS 2000 (which is a major effort and lots of work).
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "BB_Reporting" <Bruce.Baessler@.web.de> wrote in message
> news:1123855073.170843.218720@.o13g2000cwo.googlegroups.com...
>> Hi,
>>
>> a lot of my users were asking me to have the possibilty of a freeze
>> pane (similar to excel) in the RS web reports because often the reports
>> are to long to see the column haeders. Especially when you have
>> drilldowns it would help.
>>
>> Has somebody tried to implement such feature by DLL to RS 2000. Is this
>> feature planned for RS 2005 '
>>
>> thanks
>>
>> BB
>|||Auto-Refresh will remember the current page number, but it will not remember
the scroll position within the page.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"James Snape" <jim_snape.at.hotmail.com@.online.nospam> wrote in message
news:ubgRWP3oFHA.3988@.TK2MSFTNGP10.phx.gbl...
> Hi Robert,
> I haven't had a chance to test this yet so, with fixed headers in 2005
> will the scroll position stay in place for auto-refreshing reports or will
> it be a pain an spring back to the top?
> Thanks in advance,
> James Snape
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:O8oAI$goFHA.904@.TK2MSFTNGP10.phx.gbl...
>> This is available in RS 2005.
>> I don't know of anyone who has implemented this for RS 2000 - but in that
>> case, you would need to implement a full custom rendering extension
>> yourself for RS 2000 (which is a major effort and lots of work).
>> -- Robert
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "BB_Reporting" <Bruce.Baessler@.web.de> wrote in message
>> news:1123855073.170843.218720@.o13g2000cwo.googlegroups.com...
>> Hi,
>>
>> a lot of my users were asking me to have the possibilty of a freeze
>> pane (similar to excel) in the RS web reports because often the reports
>> are to long to see the column haeders. Especially when you have
>> drilldowns it would help.
>>
>> Has somebody tried to implement such feature by DLL to RS 2000. Is this
>> feature planned for RS 2005 '
>>
>> thanks
>>
>> BB
>>
>

Freeze Report Header

Is there a way to freeze the report header so when a user is looking at the
report, on the web, and they scroll down the page that they can always see
the header?
Thanks,
Jasonthis is not yet acheived by microsoft but it is to be done by them (as
they said so ) in any next version wait till that|||Yes, fixed headers will be available in SQL Server 2005 Reporting Services.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"** Spirits **" <muhammadhammad78@.gmail.com> wrote in message
news:1113385952.494987.10490@.z14g2000cwz.googlegroups.com...
> this is not yet acheived by microsoft but it is to be done by them (as
> they said so ) in any next version wait till that
>

Freeze pane in RS 2000 SP2 / RS2005 web reports

Hi,
a lot of my users were asking me to have the possibilty of a freeze
pane (similar to excel) in the RS web reports because often the reports
are to long to see the column haeders. Especially when you have
drilldowns it would help.
Has somebody tried to implement such feature by DLL to RS 2000. Is this
feature planned for RS 2005 '
thanks
BBThe only way (that I know of) to accomplish what you're asking for is to have
a custom application that modifies the output file after it has been saved
and then forwards it to the person who initially requested it. We have one
non-RS custom application that does this (internal use only) and are working
on a way to do this with RS rendered reports.
Basically what your application has to do:
1- Client requests report
2- Application renders report in excel and saves to disk.
3- Appliation modifies the excel file properties (page margins, fit to
page, freeze panes, etc.)
4- Appliation passes modified file to the Client
It's a lot of work.
"BB_Reporting" wrote:
> Hi,
> a lot of my users were asking me to have the possibilty of a freeze
> pane (similar to excel) in the RS web reports because often the reports
> are to long to see the column haeders. Especially when you have
> drilldowns it would help.
> Has somebody tried to implement such feature by DLL to RS 2000. Is this
> feature planned for RS 2005 '
> thanks
> BB
>

Framework Equivalent in MSSQL?

Introduction:
I want to put database based asp.net web site on a windows server.
For the asp.net files there is a free framework that will run the asp.net
files on the server.
The Question:
Is there any free PROGRAM that can run the .mdf and .ldf files on the
server?
And what is the name of that program?
Bishoy George
bishoy@.bishoy.com"Bishoy George" <bishoy@.bishoy.com> wrote in message
news:%23OPKnGAVGHA.5900@.tk2msftngp13.phx.gbl...
> Introduction:
> I want to put database based asp.net web site on a windows server.
> For the asp.net files there is a free framework that will run the asp.net
> files on the server.
> The Question:
> Is there any free PROGRAM that can run the .mdf and .ldf files on the
> server?
> And what is the name of that program?
SQL Server 2005 Express Edtion.
David|||it's name is MSDE (now SQL server express) if you're not planning on
more than a few concurrent connections. Otherwise you have to pay - or
SQL server would be free.|||Are you saying that there's a limit to the number of concurrent users in MSD
E
and SQL Server 2005 Express?
ML
http://milambda.blogspot.com/|||"ML" <ML@.discussions.microsoft.com> wrote in message
news:B8808488-053B-4525-9546-7C67A32A6F22@.microsoft.com...
> Are you saying that there's a limit to the number of concurrent users in
> MSDE
> and SQL Server 2005 Express?
>
There is no user limit in either product, but both products have baked-in
performance limitations.
In MSDE there is a workload throttle that slows down performance beyond 5
concurrent workloads. In SQL Server 2005 Express Edition you can only use 1
CPU and 1 GB of memory.
David|||There's no such limit in either of them. MSDE has a performance throttling f
unctionality which added
a wait for each I/O when you had more than 8 concurrently executing queries.
No such in Express. But
there are limits on db size, number of processors and memory, of course.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"ML" <ML@.discussions.microsoft.com> wrote in message
news:B8808488-053B-4525-9546-7C67A32A6F22@.microsoft.com...
> Are you saying that there's a limit to the number of concurrent users in M
SDE
> and SQL Server 2005 Express?
>
> ML
> --
> http://milambda.blogspot.com/|||Yes. Of course there's a limit. Otherwise everyone could just run sql
servers for free. I believe that they've slackened it a bit from the 5
for MSDE, but it's still limited. Last thing I read on it said that as
connections go above 5 MSDE massively throttles the performance of the
SQL server, so it's not an error thrown kind of limit, but your SQL
server becomes rubbish.|||The workload governor is said to be removed in the SQL 2005 Ecpress version.
Not trying to argue, just want clear facts. :)
ML
http://milambda.blogspot.com/|||Ecpress = Express
(in this particular case ;)|||> Yes. Of course there's a limit. Otherwise everyone could just run sql
> servers for free. I believe that they've slackened it a bit from the 5
> for MSDE, but it's still limited.
There is no longer any limitation that has aything to do with the number of
users, number of connections, or number of concurrent queries. The limit is
that Express will only be able to use 1 GB of memory, a single CPU, and the
database size is limited to 4GB. So, if one or more of those criteria do
not meet the requirements of your app, look elsewhere. I imagine there are
pleny of apps out there that could have 1000 concurrent users on Express and
work fine. On the flip side, if I tried real hard, I could design an
application that would completely suck wind with more than 1 concurrent
user, even if deployed to Enterprise edition with 32 GB of RAM. So the
question is more about design and requirements than any artificial
limitation.

> connections go above 5 MSDE massively throttles the performance of the
> SQL server,
[It's actually 8, not 5/]

> so it's not an error thrown kind of limit, but your SQL
> server becomes rubbish.
Have you actually experienced performance throttling THAT BAD on MSDE? Or
is it just hearsay? Granted, I would not personally deploy MSDE or Express
to a production application (because I want to sleep at night), but I have
not been able to reproduce this "rubbish" in some pretty serious testing.
A

Framework Equivalent in MSSQL?

Introduction:
I want to put database based asp.net web site on a windows server.
For the asp.net files there is a free framework that will run the asp.net
files on the server.
The Question:
Is there any free PROGRAM that can run the .mdf and .ldf files on the
server?
And what is the name of that program?
Bishoy George
bishoy@.bishoy.com
SQL Server Express?
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Bishoy George" <bishoy@.bishoy.com> wrote in message
news:Od2l9CAVGHA.2444@.TK2MSFTNGP14.phx.gbl...
> Introduction:
> I want to put database based asp.net web site on a windows server.
> For the asp.net files there is a free framework that will run the asp.net
> files on the server.
> The Question:
> Is there any free PROGRAM that can run the .mdf and .ldf files on the
> server?
> And what is the name of that program?
> Bishoy George
> bishoy@.bishoy.com
>