Real Time DSS: receiving

Showing posts with label receiving. Show all posts

Friday, March 30, 2012

Receiving this error since migrating to SQL 2000 from SQL 7

Three simple suggestions:
1- Run the DTS package manually and see where (Which step)
you get the error.
2- Create an output file through Package-->Properties--
>Logging in the DTS package and read the output file for
the error.
3- If you upgraded from SQL Server 7.0 to 2000, run
sp_updatestats stored proc for all the databases in the QA.

>--Original Message--
>Ever since migrating to SQL 2000 from SQL 7 I receive
this error at least
>once a day while running jobs through SQL Agent. It may
not be the same job
>having the problem. Any thoughts?
>Executed as user: domain\user. DTSRun: Loading...
Error: -2147467259
>(80004005); Provider Error: 0 (0) Error string:
Timeout expired
>Error source: Microsoft OLE DB Provider for SQL
Server Help file:
> Help context: 0. Process Exit Code 1. The step failed.
>.
>
The job is only failing once or twice a day even though it runs every hour or
in the case of another job, every 1/2 hour. It is never the same time of
day. I can run the DTS job manually and it work without failing. I will
give the logging a try and the sp_updatestats. I didn't upgrade the server -
it was a clean install and a restore from SQL 7. Thanks for your help
"Mark" wrote:

> Three simple suggestions:
> 1- Run the DTS package manually and see where (Which step)
> you get the error.
> 2- Create an output file through Package-->Properties--
> the error.
> 3- If you upgraded from SQL Server 7.0 to 2000, run
> sp_updatestats stored proc for all the databases in the QA.
>
>
> this error at least
> not be the same job
> Error: -2147467259
> Timeout expired
> Server Help file:
>

Receiving this error since migrating to SQL 2000 from SQL 7

Ever since migrating to SQL 2000 from SQL 7 I receive this error at least
once a day while running jobs through SQL Agent. It may not be the same job
having the problem. Any thoughts?
Executed as user: domain\user. DTSRun: Loading... Error: -2147467259
(80004005); Provider Error: 0 (0) Error string: Timeout expired
Error source: Microsoft OLE DB Provider for SQL Server Help file:
Help context: 0. Process Exit Code 1. The step failed.Three simple suggestions:
1- Run the DTS package manually and see where (Which step)
you get the error.
2- Create an output file through Package-->Properties--
>Logging in the DTS package and read the output file for
the error.
3- If you upgraded from SQL Server 7.0 to 2000, run
sp_updatestats stored proc for all the databases in the QA.
>--Original Message--
>Ever since migrating to SQL 2000 from SQL 7 I receive
this error at least
>once a day while running jobs through SQL Agent. It may
not be the same job
>having the problem. Any thoughts?
>Executed as user: domain\user. DTSRun: Loading...
Error: -2147467259
>(80004005); Provider Error: 0 (0) Error string:
Timeout expired
>Error source: Microsoft OLE DB Provider for SQL
Server Help file:
> Help context: 0. Process Exit Code 1. The step failed.
>.
>|||The job is only failing once or twice a day even though it runs every hour or
in the case of another job, every 1/2 hour. It is never the same time of
day. I can run the DTS job manually and it work without failing. I will
give the logging a try and the sp_updatestats. I didn't upgrade the server -
it was a clean install and a restore from SQL 7. Thanks for your help
"Mark" wrote:
> Three simple suggestions:
> 1- Run the DTS package manually and see where (Which step)
> you get the error.
> 2- Create an output file through Package-->Properties--
> >Logging in the DTS package and read the output file for
> the error.
> 3- If you upgraded from SQL Server 7.0 to 2000, run
> sp_updatestats stored proc for all the databases in the QA.
>
>
> >--Original Message--
> >Ever since migrating to SQL 2000 from SQL 7 I receive
> this error at least
> >once a day while running jobs through SQL Agent. It may
> not be the same job
> >having the problem. Any thoughts?
> >
> >Executed as user: domain\user. DTSRun: Loading...
> Error: -2147467259
> >(80004005); Provider Error: 0 (0) Error string:
> Timeout expired
> >Error source: Microsoft OLE DB Provider for SQL
> Server Help file:
> > Help context: 0. Process Exit Code 1. The step failed.
> >.
> >
>|||I have ran the jobs numerous times with DTS and they do not fail. I have run
the sp_updatestats on all databases on this server and I have turned on
logging with DTS and this job does not log anything when I get the timeout
message. Thanks for any other advice you can give. My customer is getting
very frustrated that this keeps not running.
"Connie" wrote:
> Ever since migrating to SQL 2000 from SQL 7 I receive this error at least
> once a day while running jobs through SQL Agent. It may not be the same job
> having the problem. Any thoughts?
> Executed as user: domain\user. DTSRun: Loading... Error: -2147467259
> (80004005); Provider Error: 0 (0) Error string: Timeout expired
> Error source: Microsoft OLE DB Provider for SQL Server Help file:
> Help context: 0. Process Exit Code 1. The step failed.sql

Receiving system error when retrieving database record with null value

I have some VB.NET code to retrieve data from an SQL Server database and display it. The code is as follows:

-------------------------------

sw_calendar = calendarAdapter.GetEventByID(cid)

If sw_calendar.Rows.Count > 0Then

lblStartDateText.Text = sw_calendar(0).eventStartDate
lblEndDateText.Text = sw_calendar(0).eventEndDate
lblTitleText.Text = sw_calendar(0).title
lblLocationText.Text = sw_calendar(0).location
lblDescriptionText.Text = sw_calendar(0).description

Else

lblStartDateText.Text ="*** Not Found ***"
lblEndDateText.Text ="*** Not Found ***"
lblTitleText.Text ="*** Not Found ***"
lblLocationText.Text ="*** Not Found ***"
lblDescriptionText.Text ="*** Not Found ***"

EndIf

-------------------------------

If all of the fields in the database has values, everything works ok. However, if the title, location or description fields have a null value, I receive the following error message:

Unable to cast object of type 'System.DBNull' to type 'System.String'.

I've tried a bunch of different things such as:

Adding ".ToString" to the database field,Seeing if the value is null: If sw_calendar(0).description = system.DBnull.value...

...but either I get syntax errors in the code, or if the syntax is ok, I still get the above error message.

Can anyone help me with the code required to trap the nullwithin the code example I've provided? I'm sure there are other, and better, ways to code this, but for now I'd really like to get it working as is, and then optimize the code once the application is working (...can you tell I have a tight deadline Big Smile )

Thanks,

Brad

Check forDBNullin VB.NET, with optional specification of type, so itconverts null to the appropriate value (e.g., "" for string, 0 fornumbers).

Good luck.

|||

Something like:

sw_calendar = calendarAdapter.GetEventByID(cid)If sw_calendar.Rows.Count > 0Then IF NOT IsDBNull(sw_calendar(0).eventStartDate)Then lblStartDateText.Text = sw_calendar(0).eventStartDate ELSEblStartDateText.Text ="*** Not Found ***" END IF IF NOT IsDBNull(sw_calendar(0).eventEndDate)Then lblEndDateText.Text = sw_calendar(0).eventEndDate ELSE lblEndDateText.Text ="*** Not Found ***" END IF IF NOT IsDBNull(sw_calendar(0).title) THEN lblTitleText.Text = sw_calendar(0).title ELSE lblTitleText.Text ="*** Not Found ***" END IF IF NOT IsDBNull(sw_calendar(0).location) THEN lblLocationText.Text = sw_calendar(0).location ELSE lblLocationText.Text ="*** Not Found ***" END IF IF NOT IsDBNull(sw_calendar(0).description) THEN lblDescriptionText.Text = sw_calendar(0).description ELSE lblDescriptionText.Text ="*** Not Found ***" END IFGood luck.

|||

Hi,

I tried addingIF NOT IsDBNull... but I still get the same error message. To make sure the problem is what I think it is, I changed the value of the description field in the database to a single space. After doing this, page renders fine. When I delete the space, the error returns. So, there is still a problem evaluating sw_calendar(0).location within the IsDBNull function.

Any ideas?

|||

I just tried:

If sw_calendar(0).description.Length >= 1Then

lblDescriptionText.Text = sw_calendar(0).description

Else

lblDescriptionText.Text =" "

EndIf

and itstill generates theUnable to cast object of type 'System.DBNull' to type 'System.String'. error message!

|||

The problem isnt with IsDbNull. The problem is with the strongly typed data row you are using. If you try to do IsDbNull(sw_calendar(0).description), sw_calendar tries to convert the description to a string and then pass that value to IsDBNull. However, sincesw_calendar(0).description is DBNull, it will always throw this error before DbNull ever gets it.

This, in my opinion, has crippled the Strongly Typed DataSets that are created with the TableAdapters.

The workaround is not to try to get the description withsw_calendar(0).description. Instead, usesw_calendar(0)("description"). Its not strongly typed, but at least it wont crash your app.

|||

One option would be to convert the values of NULLs to '' in case of strings and 0 or something similar in case of integers. You can achieve this in your query itself. There is a function call ISNULL in SQL. Basically you can use this function like ISNULL ( <column name> , '' ). This will replace the null values in the column to '' ( blank which is a legal string BLOCKED EXPRESSION. You can write select isnull ( description , '' ) as description. Then you are rest assured that the query itself will give you the valid string values instead of null and you having to bother to convert those nulls to blank string or something similar.

You can use this type of query when you are not sured about the values contained in the column, I mean in case the column may contain nulls also.

Hope this will help.

Receiving SQL Server Error messages in SQL Server Log

Hi:
I've been receiving the below mentioned error messages in
my SQL Server Log for all databases ;
1. IO is thawed
2. IO is frozen for snapshot
Any idea as to for what reason this would be happening.
Thanks very much in advance!Most likely due to using dbcc freeze_io and thaw_io.
The commands are depreciated in SQL Server 2000 and are not
recommended to be used due to the depreciation as well as
the potential for these commands to hang the server.
-Sue
On Tue, 13 Apr 2004 07:39:20 -0700, "Arpit"
<apatel@.esricanada.com> wrote:
>Hi:
>I've been receiving the below mentioned error messages in
>my SQL Server Log for all databases ;
>1. IO is thawed
>2. IO is frozen for snapshot
>Any idea as to for what reason this would be happening.
>Thanks very much in advance!|||Thanks Sue!
I haven't use the dbcc freeze_io and thaw_io. Would there
any process of job that would have used this in the
background?
Thanks
>--Original Message--
>Most likely due to using dbcc freeze_io and thaw_io.
>The commands are depreciated in SQL Server 2000 and are
not
>recommended to be used due to the depreciation as well as
>the potential for these commands to hang the server.
>-Sue
>On Tue, 13 Apr 2004 07:39:20 -0700, "Arpit"
><apatel@.esricanada.com> wrote:
>>Hi:
>>I've been receiving the below mentioned error messages
in
>>my SQL Server Log for all databases ;
>>1. IO is thawed
>>2. IO is frozen for snapshot
>>Any idea as to for what reason this would be happening.
>>Thanks very much in advance!
>.
>|||I've read a couple posts where it seems some third party
vendor for backups or shadow copy backups uses this. SQL
Server itself or any of native functionality isn't likely to
be implementing this. I'd look at whatever third party
tools, products you may be using. You could look at the
times you are getting the messages logged and try to figure
out what's running at those times.
-Sue
On Tue, 13 Apr 2004 08:52:43 -0700, "Arpit"
<anonymous@.discussions.microsoft.com> wrote:
>Thanks Sue!
>I haven't use the dbcc freeze_io and thaw_io. Would there
>any process of job that would have used this in the
>background?
>Thanks
>>--Original Message--
>>Most likely due to using dbcc freeze_io and thaw_io.
>>The commands are depreciated in SQL Server 2000 and are
>not
>>recommended to be used due to the depreciation as well as
>>the potential for these commands to hang the server.
>>-Sue
>>On Tue, 13 Apr 2004 07:39:20 -0700, "Arpit"
>><apatel@.esricanada.com> wrote:
>>Hi:
>>I've been receiving the below mentioned error messages
>in
>>my SQL Server Log for all databases ;
>>1. IO is thawed
>>2. IO is frozen for snapshot
>>Any idea as to for what reason this would be happening.
>>Thanks very much in advance!
>>.

Receiving SQL Server Error messages in SQL Server Log

Hi:
I've been receiving the below mentioned error messages in
my SQL Server Log for all databases ;
1. IO is thawed
2. IO is frozen for snapshot
Any idea as to for what reason this would be happening.
Thanks very much in advance!
Most likely due to using dbcc freeze_io and thaw_io.
The commands are depreciated in SQL Server 2000 and are not
recommended to be used due to the depreciation as well as
the potential for these commands to hang the server.
-Sue
On Tue, 13 Apr 2004 07:39:20 -0700, "Arpit"
<apatel@.esricanada.com> wrote:

>Hi:
>I've been receiving the below mentioned error messages in
>my SQL Server Log for all databases ;
>1. IO is thawed
>2. IO is frozen for snapshot
>Any idea as to for what reason this would be happening.
>Thanks very much in advance!
|||Thanks Sue!
I haven't use the dbcc freeze_io and thaw_io. Would there
any process of job that would have used this in the
background?
Thanks
>--Original Message--
>Most likely due to using dbcc freeze_io and thaw_io.
>The commands are depreciated in SQL Server 2000 and are
not[color=darkblue]
>recommended to be used due to the depreciation as well as
>the potential for these commands to hang the server.
>-Sue
>On Tue, 13 Apr 2004 07:39:20 -0700, "Arpit"
><apatel@.esricanada.com> wrote:
in
>.
>
|||I've read a couple posts where it seems some third party
vendor for backups or shadow copy backups uses this. SQL
Server itself or any of native functionality isn't likely to
be implementing this. I'd look at whatever third party
tools, products you may be using. You could look at the
times you are getting the messages logged and try to figure
out what's running at those times.
-Sue
On Tue, 13 Apr 2004 08:52:43 -0700, "Arpit"
<anonymous@.discussions.microsoft.com> wrote:
[color=darkblue]
>Thanks Sue!
>I haven't use the dbcc freeze_io and thaw_io. Would there
>any process of job that would have used this in the
>background?
>Thanks
>not
>in

Receiving SQL Server Error messages in SQL Server Log

Receiving queue does not fire stored procedure

I am doing my first SSB application where two different servers send messages to each other. The logic was all previously tested on a single server between two databases and it worked OK.

The problem I am having is that when the message is received at the target server (I see this in profiler), the stored procedure associated with the queue does not fire.

I see an acknowledgment fire back to the initiator, but it is like the target server does nothing with the initial message.

Any ideas on how I can further troubleshoot this? FWIW, I used the setup tool provided by RemusResanu to set up the routes and service bindings.

Thanks for any help!
John
Here is what my target server shows in profiler

Event Class/Sub Class

Broker:Conversation Group 1 - Create
Broker:Conversation 12 - Dialog Created
Broker:Conversation 6 - Received Sequenced Message
Broker:Remote Message Acknowledgement 3 - Message with Acknowledgement Received
Broker:Message Classify 2 - Remote
|||Also, when I query the queue, I can see all of the messages just sitting there. They are reaching the right place, just nothing happens from there.

Please help!
John

Code Snippet

CREATE QUEUE [dbo].[SiteChangeNotifyReceiveQueue] WITH STATUS = ON , RETENTION = ON , ACTIVATION ( STATUS = ON , PROCEDURE_NAME = [dbo].[SiteChangeQueueReader] , MAX_QUEUE_READERS = 10 , EXECUTE AS N'sqlservices' ) ON [PRIMARY]

|||Have you tried to execute the procedure manually?
Can the user 'sqlservices' be impersonated? I.e. executing EXECUTE AS USER='sqlservices' works fine w/o errors?
Do you see any errors in the ERRORLOG file or in the NT application event log (eventvwr.exe)?|||Yes, I just tried that and it worked fine. In fact, it seemed to go in to the queue and process all of the pending messages.

So, here is the really strange part:

Now, everything works fine.

After I ran the SP manually, my messages come through and the SP fires by itself again.

I would really like it if someone could give me an explanation of this. Is this part of Service Broker enforcing the order of transactions- there were errors in my SP at one time. Perhaps the SSB was not processing the later messages until the old ones went through?

I don't t like it when I can't explain how things went from broken to working!
|||If the situation repeats please look at sys.dm_broker_queue_monitors and tell us what is the state of the queue with the problem.

Receiving 'PRIMARY KEY constraint 'PK__@snapshot_seqnos' error...

I'm adding a new subscriber to a transactional publication (no snapshot) and
I'm receiving a 'Violation of PRIMARY KEY constraint
'PK__@.snapshot_seqnos__543BF21A'. Cannot insert duplicate key in object
'#5253A9A8'.' error. The subscriber, remote distributor and publisher are all
running SP3.
I did some research and it appears this is fixed in MS03-031:
http://support.microsoft.com/default...b;en-us;813494
Can this be installed just on the distributor w/o affecting the other
systems? We actually have 5 other subscribers and some also publish to other
subscribers and also to other publishers so I don't want to affect any of the
other other servers.
TIA!!
Darin
While I can't comment about your individual case, I find that using
different service pack levels/hotfixes across the computers involved in a
replication topology can give unpredictable results. The recommended upgrade
path does start with the distributor, then publisher then subscriber, but in
your case I'd apply sp4 to all computers (in this order but in one shot)
rather than one by one and running replication inbetween.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Have a look at this post.
http://groups.google.com/group/micro...0?dmode=source
This will fix it. IIRC you will make this change on the distributor.
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
"DTully" <DTully@.discussions.microsoft.com> wrote in message
news:45B780CE-96CF-45AE-8716-BE22A062C5FE@.microsoft.com...
> I'm adding a new subscriber to a transactional publication (no snapshot)
> and
> I'm receiving a 'Violation of PRIMARY KEY constraint
> 'PK__@.snapshot_seqnos__543BF21A'. Cannot insert duplicate key in object
> '#5253A9A8'.' error. The subscriber, remote distributor and publisher are
> all
> running SP3.
> I did some research and it appears this is fixed in MS03-031:
> http://support.microsoft.com/default...b;en-us;813494
> Can this be installed just on the distributor w/o affecting the other
> systems? We actually have 5 other subscribers and some also publish to
> other
> subscribers and also to other publishers so I don't want to affect any of
> the
> other other servers.
> TIA!!
> Darin

REceiving notification in windows application

Hi,

Is this possible to receive notifications from Notification Service in my windows application written in Framework 1.1? I am using SqlServer 2K.

I want to create a notification when number of rows in a given table reach a specific limit. I want to receive the notification in my application and prompt user through my application's user-interface instead of sending sms or emails to users.

Thanks,

You can do this by creating your own custom delivery protocol.

HTH...

|||Any sample ?|||

Why not just use a MessageBox ?

You could simply write a SqlCommand counting the number of rows already in your databasetable...

|||Its a little complicated and then I need to do this very frequently and speed is an important factor here as well.|||

I don't have one off hand that I can post. Shyam Pather's book describes how to build one though. It's definitely worth the purchase price.

HTH...

RECEIVING messages from a Windows service

I've done a bit of work with the External Activator but I think it may be a bit overkill for what I need to do (which is RECEIVE messages from a single queue and process them with managed code). I've tried creating a Service Broker Interface service that retrieves messages from this queue, but I notice that if I set the timeout to -1 to watch for messages indefinitely, the Service never completes the OnStart code.

I notice if I change the service's timeout to something greater than 0, the message is retrieved, but this defeats the purpose of using a Windows Service app, which I want to continuously monitor the queue. I noticed the External Activator spawns a thread to start monitoring an EventNotification queue, which I can bypass since I want to monitor the notification's target queue.

Rushi, can you point me in the right direction to create a Windows Service that constantly monitors a queue? Also, I'd like the ability to monitor multiple databases (the queue name would be the same) as well, so if that is not feasible from a Windows Service please let me know.

Also, am I sacrificing scalability by NOT using the External Activator and switching to a Windows Service (I believe the External Activator will spawn multiple instances of the processing executable)?

Thanks,

Chris

If you do not need dynamic launching of one or more instances of your service program then you are fine with your approach of writing a single threaded process (which could be a Windows Service) that receives messages from the queue serially. (Of course, you could get fancy and have multiple (but a fixed number of) threads in your process simultaneously pulling messages from the queue). But if you need the ability to control the number of instances of your service program (processes or threads) based on rate of incoming messages, you will need to either implement the external activator based on the QUEUE_ACTIVATION event notification or use the sample.

If you choose the non-activation approach (i.e. your process is always running and waiting for messages), then setting WaitforTimeout to -1 seems appropriate to me. The Service class does not have an OnStart method, so I'm not sure what you are referring to. It does have a Run() method; and if you set WaitforTimeout to -1, Run will never return. While it would be nice to have a Cancel() method that would gracefully tear down the Service class, but since the Service Broker Interface is only a sample, we did not implement that. It should be fine to terminate the process while Run() has not returned. (The transaction will automatically be rolled back).

Rushi

|||

Rushi,

Thanks for the reply. Sorry, I was using the term "service" to refer to the Service class defined in the ServiceBrokerInterface project and a Windows Service app. What I mean is, in the OnStart() method of a Windows Service app, I would need to call the Service class' Run() method, which will never return. This means that the Windows Service's OnStart() method never completes, and you end up getting an error message from the ServiceInstaller (I think). I realize this may be more of a development question, and if so, I can try posting in another forum. I was just hoping you'd seen an example of RECEIVEing messages from a queue with a timeout of -1 from a Windows Service app.

Thanks,

Chris

|||

You will need to create a Thread in the constructor of your Windows Service class which is started by the OnStart() method. The ThreadStart should point to the ServiceBrokerInterface Service.Run() method.

Hope that helps,
Rushi

|||

Rushi:

You say...."While it would be nice to have a Cancel() method that would gracefully tear down the Service class, but since the Service Broker Interface is only a sample"

What would be those graceful tear down steps ..If I want to implement one?. I can see the following...

If there are still pending messages read from the queue but not yet processed ...return such entries back to the queue by rolling back the transaction.

Implementation wise.... "cancel()" can set a flag and the Dispatch function does not dispatch messages if this flag is set ....this way messages that are already read from queue can be prevented from getting processed.

What else I am missing.?

RECEIVING messages from a Windows service

Thanks,

Chris

Rushi

|||

Rushi,

Thanks,

Chris

|||

Hope that helps,
Rushi

|||

Rushi:

You say...."While it would be nice to have a Cancel() method that would gracefully tear down the Service class, but since the Service Broker Interface is only a sample"

What would be those graceful tear down steps ..If I want to implement one?. I can see the following...

If there are still pending messages read from the queue but not yet processed ...return such entries back to the queue by rolling back the transaction.

What else I am missing.?

sql

Receiving garbage in excel when rendered via Subscription

Hi,
I set up a standard subscription and am rendering the report to excel.
After recieving the email, I open the excel report and it displays a bunch of
garbage. Is there a known issue or a patch out for this?Did you install the SQL 2000 Reporting Services SP1?
"clutch" <clutch@.discussions.microsoft.com> wrote in message
news:3C59803E-CAB9-4BEC-BA2A-A5E90A0B21B2@.microsoft.com...
> Hi,
> I set up a standard subscription and am rendering the report to excel.
> After recieving the email, I open the excel report and it displays a bunch
> of
> garbage. Is there a known issue or a patch out for this?|||Yes, we have.
"Sal Young" wrote:
> Did you install the SQL 2000 Reporting Services SP1?
>
> "clutch" <clutch@.discussions.microsoft.com> wrote in message
> news:3C59803E-CAB9-4BEC-BA2A-A5E90A0B21B2@.microsoft.com...
> > Hi,
> >
> > I set up a standard subscription and am rendering the report to excel.
> > After recieving the email, I open the excel report and it displays a bunch
> > of
> > garbage. Is there a known issue or a patch out for this?
>
>|||What email system is being used for delivery (I know there has been some
issue with Lotus for example).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"clutch" <clutch@.discussions.microsoft.com> wrote in message
news:0469D6D0-9A84-4BF5-B927-2308CB81F882@.microsoft.com...
> Yes, we have.
> "Sal Young" wrote:
> > Did you install the SQL 2000 Reporting Services SP1?
> >
> >
> > "clutch" <clutch@.discussions.microsoft.com> wrote in message
> > news:3C59803E-CAB9-4BEC-BA2A-A5E90A0B21B2@.microsoft.com...
> > > Hi,
> > >
> > > I set up a standard subscription and am rendering the report to excel.
> > > After recieving the email, I open the excel report and it displays a
bunch
> > > of
> > > garbage. Is there a known issue or a patch out for this?
> >
> >
> >|||Our email system is Novell GroupWise 6.5.
"Bruce L-C [MVP]" wrote:
> What email system is being used for delivery (I know there has been some
> issue with Lotus for example).
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "clutch" <clutch@.discussions.microsoft.com> wrote in message
> news:0469D6D0-9A84-4BF5-B927-2308CB81F882@.microsoft.com...
> > Yes, we have.
> >
> > "Sal Young" wrote:
> >
> > > Did you install the SQL 2000 Reporting Services SP1?
> > >
> > >
> > > "clutch" <clutch@.discussions.microsoft.com> wrote in message
> > > news:3C59803E-CAB9-4BEC-BA2A-A5E90A0B21B2@.microsoft.com...
> > > > Hi,
> > > >
> > > > I set up a standard subscription and am rendering the report to excel.
> > > > After recieving the email, I open the excel report and it displays a
> bunch
> > > > of
> > > > garbage. Is there a known issue or a patch out for this?
> > >
> > >
> > >
>
>|||Unless someone else jumps in I suggest calling support. If it is a bug you
will not be charged.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"clutch" <clutch@.discussions.microsoft.com> wrote in message
news:E23BCE25-0B8A-4259-9446-2479071A3144@.microsoft.com...
> Our email system is Novell GroupWise 6.5.
> "Bruce L-C [MVP]" wrote:
> > What email system is being used for delivery (I know there has been some
> > issue with Lotus for example).
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "clutch" <clutch@.discussions.microsoft.com> wrote in message
> > news:0469D6D0-9A84-4BF5-B927-2308CB81F882@.microsoft.com...
> > > Yes, we have.
> > >
> > > "Sal Young" wrote:
> > >
> > > > Did you install the SQL 2000 Reporting Services SP1?
> > > >
> > > >
> > > > "clutch" <clutch@.discussions.microsoft.com> wrote in message
> > > > news:3C59803E-CAB9-4BEC-BA2A-A5E90A0B21B2@.microsoft.com...
> > > > > Hi,
> > > > >
> > > > > I set up a standard subscription and am rendering the report to
excel.
> > > > > After recieving the email, I open the excel report and it displays
a
> > bunch
> > > > > of
> > > > > garbage. Is there a known issue or a patch out for this?
> > > >
> > > >
> > > >
> >
> >
> >|||We also recieve garbage when trying to open a PDF report via email as well. I
assume this is the same as Excel exporting garbage? One other item, when we
include the link in the email, the link itself is placed on two lines. The
top line is a hyperlink and the bottom isn't. Meaning, you can't click on it.
We have to place the top line in and then go back and copy the bottom line
and paste it after the first. Can this be fixed?
"Bruce L-C [MVP]" wrote:
> Unless someone else jumps in I suggest calling support. If it is a bug you
> will not be charged.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "clutch" <clutch@.discussions.microsoft.com> wrote in message
> news:E23BCE25-0B8A-4259-9446-2479071A3144@.microsoft.com...
> > Our email system is Novell GroupWise 6.5.
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > What email system is being used for delivery (I know there has been some
> > > issue with Lotus for example).
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "clutch" <clutch@.discussions.microsoft.com> wrote in message
> > > news:0469D6D0-9A84-4BF5-B927-2308CB81F882@.microsoft.com...
> > > > Yes, we have.
> > > >
> > > > "Sal Young" wrote:
> > > >
> > > > > Did you install the SQL 2000 Reporting Services SP1?
> > > > >
> > > > >
> > > > > "clutch" <clutch@.discussions.microsoft.com> wrote in message
> > > > > news:3C59803E-CAB9-4BEC-BA2A-A5E90A0B21B2@.microsoft.com...
> > > > > > Hi,
> > > > > >
> > > > > > I set up a standard subscription and am rendering the report to
> excel.
> > > > > > After recieving the email, I open the excel report and it displays
> a
> > > bunch
> > > > > > of
> > > > > > garbage. Is there a known issue or a patch out for this?
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||I have having the same problem.
"clutch" wrote:
> We also recieve garbage when trying to open a PDF report via email as well. I
> assume this is the same as Excel exporting garbage? One other item, when we
> include the link in the email, the link itself is placed on two lines. The
> top line is a hyperlink and the bottom isn't. Meaning, you can't click on it.
> We have to place the top line in and then go back and copy the bottom line
> and paste it after the first. Can this be fixed?
> "Bruce L-C [MVP]" wrote:
> > Unless someone else jumps in I suggest calling support. If it is a bug you
> > will not be charged.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "clutch" <clutch@.discussions.microsoft.com> wrote in message
> > news:E23BCE25-0B8A-4259-9446-2479071A3144@.microsoft.com...
> > > Our email system is Novell GroupWise 6.5.
> > >
> > > "Bruce L-C [MVP]" wrote:
> > >
> > > > What email system is being used for delivery (I know there has been some
> > > > issue with Lotus for example).
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "clutch" <clutch@.discussions.microsoft.com> wrote in message
> > > > news:0469D6D0-9A84-4BF5-B927-2308CB81F882@.microsoft.com...
> > > > > Yes, we have.
> > > > >
> > > > > "Sal Young" wrote:
> > > > >
> > > > > > Did you install the SQL 2000 Reporting Services SP1?
> > > > > >
> > > > > >
> > > > > > "clutch" <clutch@.discussions.microsoft.com> wrote in message
> > > > > > news:3C59803E-CAB9-4BEC-BA2A-A5E90A0B21B2@.microsoft.com...
> > > > > > > Hi,
> > > > > > >
> > > > > > > I set up a standard subscription and am rendering the report to
> > excel.
> > > > > > > After recieving the email, I open the excel report and it displays
> > a
> > > > bunch
> > > > > > > of
> > > > > > > garbage. Is there a known issue or a patch out for this?
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Hi bokey,
This may help you.
http://support.microsoft.com/default.aspx?scid=kb;[LN];872774
"bokey" wrote:
> I have having the same problem.
> "clutch" wrote:
> > We also recieve garbage when trying to open a PDF report via email as well. I
> > assume this is the same as Excel exporting garbage? One other item, when we
> > include the link in the email, the link itself is placed on two lines. The
> > top line is a hyperlink and the bottom isn't. Meaning, you can't click on it.
> > We have to place the top line in and then go back and copy the bottom line
> > and paste it after the first. Can this be fixed?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > Unless someone else jumps in I suggest calling support. If it is a bug you
> > > will not be charged.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > >
> > > "clutch" <clutch@.discussions.microsoft.com> wrote in message
> > > news:E23BCE25-0B8A-4259-9446-2479071A3144@.microsoft.com...
> > > > Our email system is Novell GroupWise 6.5.
> > > >
> > > > "Bruce L-C [MVP]" wrote:
> > > >
> > > > > What email system is being used for delivery (I know there has been some
> > > > > issue with Lotus for example).
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > >
> > > > > "clutch" <clutch@.discussions.microsoft.com> wrote in message
> > > > > news:0469D6D0-9A84-4BF5-B927-2308CB81F882@.microsoft.com...
> > > > > > Yes, we have.
> > > > > >
> > > > > > "Sal Young" wrote:
> > > > > >
> > > > > > > Did you install the SQL 2000 Reporting Services SP1?
> > > > > > >
> > > > > > >
> > > > > > > "clutch" <clutch@.discussions.microsoft.com> wrote in message
> > > > > > > news:3C59803E-CAB9-4BEC-BA2A-A5E90A0B21B2@.microsoft.com...
> > > > > > > > Hi,
> > > > > > > >
> > > > > > > > I set up a standard subscription and am rendering the report to
> > > excel.
> > > > > > > > After recieving the email, I open the excel report and it displays
> > > a
> > > > > bunch
> > > > > > > > of
> > > > > > > > garbage. Is there a known issue or a patch out for this?
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >|||That work-around (sending just a link to the report, etc.) does get
around the problem in some cases, but unfortunately it is impossible to
control what users select when they create a subscription.
I want to make sure that people were aware of a third-party product,
Proposion Report Adapter for Microsoft Reporting Services and Lotus
Notes/Domino, that fixes this problem and a lot more. It not only
allows you to deliver reports via NATIVE NOTES MAIL, it also allows you
to use Notes/Domino as data sources for reports and/or allows you to
automatically deposit scheduled reports into Notes databases.) See
http://www.proposion.com/ReportAdapter.

RECEIVING from QUEUE by ConversationHandle

Is it possible to receive from a queue by a conversation handle? In the documentation there is an example that show you how to do it. Yet, if you "read" the whole document it says that the conversation handle can not be an expression.

The WHERE clause of the RECEIVE statement may only contain search conditions that use conversation_handle or conversation_group_id. The search condition may not contain any of the other columns in the queue. The conversation_handle or conversation_group_id may not be an expression.

Here is what I'm trying to do:

;RECEIVE TOP(1) @.MsgBody = CAST(message_body as XML)

FROM ProcessingLetters

WHERE conversation_handle = @.Conversation_Handle

It doesn't seem to matter if I use RECEIVE or SELECT. It will return nothing.

I've even tried this:

where cast(Conversation_Handle as varchar(100)) = cast(@.Conversation_Handle as varchar(100))

Why am I doing this? I've put something into the queue to let me know that something is processing. When it is done I want to pull it out and end the conversation.

So is the WHERE conversation_handle = @.Conversation_Handle supposed to work?

Thanks.

Trish wrote:
So is the WHERE conversation_handle = @.Conversation_Handle supposed to work?

It does work, but a message with the conversation handle value (@.Conversation_Handle) has to exist in the queue in order to be RECEIVEd. It seems you are asking for a conversation handle that does not have any message ready to be received. You can look into queue using SELECT * FROM [ProcessingLetters] to see what messages are available for RECEIVE in the in the first place.

HTH,
~ Remus

|||

When I do a SELECT * FROM ProcessingLetters it does have a record in it and it has the conversationhandle that I'm looking for.

|||

Make sure that the conversation is the same, often is confusing that the initiator and target handle of the same conversation are sequential guids and look identical.

HTH,
~ Remus

|||The record that is in the queue has a field named conversation_handle. In that field is a GUID. That GUID is 598C2A9E-EE11-DB11-ABC7-00150042E356. This is what is in my variable that I'm using in the WHERE clause:

568C2A9E-EE11-DB11-ABC7-00150042E356.

They sure look the same to me. But I'm becoming more convinced I'm just not doing something right.

|||One starts with 598, one starts with 568.|||

Ok...here is a complete test senerio. It was working until I passed the conversation handle into a stored procedure (exec SB_FinalizeQBForLetterGeneration 0,@.Conversation_Handle_To_Start_Processing,null). It will create everything and then drop everything when it's done. All I did was copy the code into a stored procedure and called the stored procedure...then it quit working.

use adventureworks

-- Drop stored procedure if it already exists

IF EXISTS (

SELECT *

FROM INFORMATION_SCHEMA.ROUTINES

WHERE SPECIFIC_SCHEMA = N'dbo'

AND SPECIFIC_NAME = N'SB_FinalizeQBForLetterGeneration'

)

DROP PROCEDURE dbo.SB_FinalizeQBForLetterGeneration

CREATE PROCEDURE dbo.SB_FinalizeQBForLetterGeneration

@.Result int,

@.ProcessingHandle uniqueidentifier,

@.FailureText nvarchar(256)

DECLARE @.MsgBody XML

;RECEIVE TOP(1) @.MsgBody = CAST(message_body as XML)

FROM ProcessingLetters

WHERE conversation_handle = @.ProcessingHandle

print 'receiving letter from in processing'

if @.MsgBody is null

BEGIN

print 'conversation handle was not found - '+cast(@.ProcessingHandle as varchar(50))

END

ELSE

BEGIN

print 'conversation handle FOUND!!!! '

END

CREATE MESSAGE TYPE SubmitQB VALIDATION = WELL_FORMED_XML;

CREATE MESSAGE TYPE ProcessQB VALIDATION = WELL_FORMED_XML;

CREATE MESSAGE TYPE SubmitGrouping VALIDATION = WELL_FORMED_XML;

CREATE CONTRACT QBLetterCreation (SubmitQB SENT BY INITIATOR, ProcessQB SENT BY INITIATOR, SubmitGrouping SENT BY INITIATOR)

CREATE QUEUE WaitingQBs WITH STATUS=ON

CREATE QUEUE ProcessingLetters WITH STATUS=ON

CREATE QUEUE WaitingToGroup WITH STATUS=ON

CREATE SERVICE QBWaiting ON QUEUE WaitingQBs (QBLetterCreation)

CREATE SERVICE QBLetterProcessing ON QUEUE ProcessingLetters (QBLetterCreation)

CREATE SERVICE LetterGrouping ON QUEUE WaitingToGroup (QBLetterCreation)

-- add to the WaitingQBs queue

BEGIN TRANSACTION

DECLARE @.conversationHandle UNIQUEIDENTIFIER

BEGIN DIALOG CONVERSATION @.conversationHandle

FROM SERVICE QBWaiting

TO SERVICE 'QBWaiting'

ON CONTRACT QBLetterCreation

WITH ENCRYPTION=OFF;

-- Send a message on the conversation

DECLARE @.message nvarchar(max), @.xmlmsg XML

SET @.message = '<createletter><info QBGroupID="111111" Who="111" Why="jest testing" Retries="0"/></createletter>' ;

SET @.xmlmsg = CAST(@.message as XML)

;SEND ON CONVERSATION @.conversationHandle MESSAGE TYPE SubmitQB(@.xmlmsg)

COMMIT TRANSACTION

END CONVERSATION @.conversationHandle

-- retrieve from waiting qbs

DECLARE @.Conversation_Handle_For_Waiting_QB uniqueidentifier;

DECLARE @.MsgBody XML

;RECEIVE TOP(1) @.MsgBody = CAST( message_body as XML ),

@.Conversation_Handle_For_Waiting_QB = conversation_handle

FROM WaitingQBs

IF @.Conversation_Handle_For_Waiting_QB is not null

BEGIN

-- end this conversation -- the initiator doesn't care so clean it up

print 'Ending the conversation for the waiting QB'

END CONVERSATION @.Conversation_Handle_For_Waiting_QB

DECLARE @.QBGroupID bigint,@.Who bigint, @.Why varchar(256), @.Retries int

SET @.QBGroupID = @.MsgBody.value('(/createletter/info/@.QBGroupID)[1]','bigint');

SET @.Who = @.MsgBody.value('(/createletter/info/@.Who)[1]','bigint');

SET @.Why = @.MsgBody.value('(/createletter/info/@.Why)[1]','nvarchar(256)');

SET @.Retries = @.MsgBody.value('(/createletter/info/@.Retries)[1]','int');

print @.QBGroupID

print @.Who

print @.Why

print @.Retries

-- now submit this QB to processing

-- first check to see if this qbgroupid is already in the queue

select message_body

from ProcessingLetters

WHERE cast(message_body as xml).value('(/createletter/info/@.QBGroupID)[1]','bigint') = @.QBGroupID

IF @.@.ROWCOUNT = 0

BEGIN

-- adding to processing queue

BEGIN TRANSACTION

DECLARE @.Conversation_Handle_To_Start_Processing uniqueidentifier;

-- 300 = 300 seconds = 5 minutes

BEGIN DIALOG CONVERSATION @.Conversation_Handle_To_Start_Processing

FROM SERVICE QBLetterProcessing

TO SERVICE 'QBLetterProcessing'

ON CONTRACT QBLetterCreation

WITH ENCRYPTION=OFF, LIFETIME = 300;

-- Send a message on the conversation

--DECLARE @.message nvarchar(max), @.xmlmsg XML

SET @.message = '<createletter><info QBGroupID="'+cast(@.QBGroupID AS nvarchar(20))+'" Who="'+cast(@.Who AS nvarchar(20))+'" Why="'+@.Why+'" Retries="'+cast(@.Retries as nvarchar(3))+'" Starttime="'+cast(getdate() as varchar(30))+'" /></createletter>' ;

SET @.xmlmsg = CAST(@.message as XML)

;SEND ON CONVERSATION @.Conversation_Handle_To_Start_Processing MESSAGE TYPE ProcessQB(@.xmlmsg)

COMMIT TRANSACTION

print 'conversation started for processingletters on '+cast(@.Conversation_Handle_To_Start_Processing as varchar(50))

-- we don't want to end the conversation...it stays open ..

-- it will end it after processing is done...properly

END

ELSE

BEGIN

print 'this qb group is already in processing letters'

END

ELSE

BEGIN

print 'getting next qb to process...no conversation handle'

END

-- now retrieve from the QBLetterProcess Queue

exec SB_FinalizeQBForLetterGeneration 0,@.Conversation_Handle_To_Start_Processing,null

DROP SERVICE QBWaiting

DROP SERVICE QBLetterProcessing

DROP SERVICE LetterGrouping

DROP QUEUE WaitingQBs

DROP QUEUE ProcessingLetters

DROP QUEUE WaitingToGroup

DROP CONTRACT QBLetterCreation

DROP MESSAGE TYPE SubmitQB

DROP MESSAGE TYPE ProcessQB

DROP MESSAGE TYPE SubmitGrouping

-- Drop stored procedure if it already exists

IF EXISTS (

SELECT *

FROM INFORMATION_SCHEMA.ROUTINES

WHERE SPECIFIC_SCHEMA = N'dbo'

AND SPECIFIC_NAME = N'SB_FinalizeQBForLetterGeneration'

)

DROP PROCEDURE dbo.SB_FinalizeQBForLetterGeneration

|||

Remus,

Did you run the script? Did you get the same results?

|||

Remus,

So what I'm seeing is that my @.Conversation_Handle_To_Start_Processing is not what is actually stored in the queue as the conversation_handle. So how can I point back to conversation?

CREATE MESSAGE TYPE ProcessQB VALIDATION = WELL_FORMED_XML;

CREATE CONTRACT QBLetterCreation (ProcessQB SENT BY INITIATOR)

CREATE QUEUE ProcessingLetters WITH STATUS=ON

CREATE SERVICE QBLetterProcessing ON QUEUE ProcessingLetters (QBLetterCreation)

-- adding to processing queue

BEGIN TRANSACTION

DECLARE @.Conversation_Handle_To_Start_Processing uniqueidentifier;

-- 300 = 300 seconds = 5 minutes

BEGIN DIALOG CONVERSATION @.Conversation_Handle_To_Start_Processing

FROM SERVICE QBLetterProcessing

TO SERVICE 'QBLetterProcessing'

ON CONTRACT QBLetterCreation

WITH ENCRYPTION=OFF, LIFETIME = 300;

-- Send a message on the conversation

DECLARE @.message nvarchar(max), @.xmlmsg XML

SET @.message = '<createletter><info QBGroupID="11111" Who="111" Why="test" Retries="0" /></createletter>' ;

SET @.xmlmsg = CAST(@.message as XML)

;SEND ON CONVERSATION @.Conversation_Handle_To_Start_Processing MESSAGE TYPE ProcessQB(@.xmlmsg)

COMMIT TRANSACTION

print 'conversation started for processingletters on '+cast(@.Conversation_Handle_To_Start_Processing as varchar(50))

DECLARE @.MsgBody2 nvarchar(max)

;RECEIVE TOP(1) @.MsgBody2 = CAST(message_body as nvarchar(max))

FROM ProcessingLetters

WHERE conversation_handle = @.Conversation_Handle_To_Start_Processing

print 'receiving letter from in processing'

if @.MsgBody2 is null

BEGIN

DECLARE @.c uniqueidentifier

print 'conversation handle was not found - '+cast(@.Conversation_Handle_To_Start_Processing as varchar(50))

select @.c = conversation_handle from ProcessingLetters

print 'in table: '+cast(@.c as varchar(200))

select * from ProcessingLetters

END

ELSE

BEGIN

print 'conversation handle FOUND!!!! '

print @.MsgBody2

END

DROP SERVICE QBLetterProcessing

DROP QUEUE ProcessingLetters

DROP CONTRACT QBLetterCreation

DROP MESSAGE TYPE ProcessQB

|||

I can't believe that you BEGIN a conversation by giving it a conversation handle and that is not what is stored in the queue(table) as the conversation_handle!!!! Why the heck then do you have to specifiy a conversation handle if it is never going to be used? And if you ARE going to do it that way, then have the SEND tell us what it created as a conversation handle!

So this is what I had to do.

SELECT @.Conversation_Handle_To_Start_Processing = conversation_handle

FROM ProcessingLetters

WHERE cast(message_body as xml).value('(/createletter/info/@.QBGroupID)[1]','bigint') = '11111'

Then I could do my RECEIVE. You have to make sure you have a unique identifier in your message body if you want to do this. Otherwise you have no way of knowing what the conversation handle is.

I think the documentation should say that the conversation handle you send in is not the actual conversation handle that is stored.

|||

A conversation consists from two endpoints, initiator and target, each with its own handle. The initiator endpoint is the one returned by the BEGIN DIALOG. The target endpoint is created by the first message arriving at the target service. Looking into sys.conversation_endpoints will show these two endpoints. The two endpoints belongig to the same conversation will have same value for conversation_id. BOL describes this here: http://msdn2.microsoft.com/en-us/library/ms166083.aspx

In your example, you are sending on the initiator's handle and trying to receive on the same handle. The initiator has no messages to availabe to be received, the message on the queue belongs to the target (since it was sent by initiator to target). You would have to receive this message (using the target's handle) and send back a reply to the initiator. Then the initiator would have a message available to be received.

HTH,
~ Remus

Receiving Export file name

Hello,
I work with Crystal in Visual Studio 2005.
Is it possible to receive the export file name after the report viewer finished exporting?I think it is not possible. It is also not possible to figure out which format was used for exporting, which optional parameters were used etc.
If I needed that information, I would probably create my own form for entering export file name and export options.

Receiving errors when using foreach loop and excel connection manager...

Purpose: Need to import excel source data into SQL Server 2005 tables. Excel source data comes in nulitple excel files with the same structure but different data. I would appreciate someone taking a look at the following information and notifying me of what I am doing incorrectly.

I Inserted a foreach loop container, a data flow task located inside the foreach loop contaiiner, an excel and SQL Server 2005 connections.

After trying multiple times I went the following URL and followed step by step direction on how to connect excel workbooks dynamically: http://msdn2.microsoft.com/en-us/library/ms345182.aspx . I also used http://www.sqlstrings.com/ as a reference when creating the connection string.

Creating a Foreach Loop Container:

1. Opened foreach loop container 2.Set the Enumerator to 'Foreach File Enumerator" and configured the enumerator by setting the directory location and file base name to E:\Clients\Dep Comm\BEA\BEA_Test_Source and *PersonnelExpense*.xls respectively. 3. Clicked Variable Mapping; created two variables called, "ExcelFile", and "ExtProperties" and closed out of the foreach loop container.

I. Created Excel Connection:

Created excel connection called, “Dynamic Excel Connection Manager,” that initially pointed to one of the excel workbooks.
Went to the connection properties by right clicking the connection manager.
Expanded Expressions and clicked the ellipsis button to bring up property expressions
Chose Connection String in the Property.
Clicked the Expression Ellipsis button.
Put the following inside the Expression multi line text box:

A. "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +@.[User::ExcelFile] + ";Extended Properties=\"" + @.[User::ExtProperties] + "\""

Clicked the Evaluate Expression button to get the following:
1. Provider=Microsoft.Jet.OLEDB.4.0;Data Source=;Extended Properties=""
Clicked Ok button
Inserted a Data flow task inside the foreach loop container.

II. Configured Tasks that is associated with Dynamic Excel Connection Manager or Package:

Set the Foreach loop container Delay Validation to true.
Set the Data Flow Task Container Delay Validation to true.
Set the Dynamic Excel Connection Manager Delay Validation to true.
Set the SQL Server Connection Manager Delay Validation to true.
Set the Package Delay Validation to true.
Package Locale ID set to English

Ran the package after connecting the excel source data flow to the OLEDB destination and have inserted part of the error in this post. Please see below.

Error: 0xC0202009 at Package, Connection manager "Dynamic Excel Connection Manager": An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available.Source: "Microsoft JET Database Engine"Hresult: 0x80004005Description: "Could not find installable ISAM.".

I modified the connection string after receiving the error by removing the extended properties. The following is the modified connection string: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +@.[User::ExcelFile]

I repeated step I.6 above and received the following expression: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=

I ran the package and received the following error in part: OLE DB record is available.Source: "Microsoft JET Database Engine"Hresult: 0x80004005Description: "Unrecognized database format 'E:\Clients\Dep Comm\BEA\BEA_Test_Source\PersonnelExpense_OCCs_051007.xls'."

I did not find anything helpful when I searched for the above errors and would very much appreciate anyone’s assistance on this issue as this issue needs to be taken care of ASAP.

Does anyone have any ideas as to why I received this error and what can I do to resolve this issue?

Your assistance in this matter is truly appreicated!

Thanks!!

Lee

Are there headers on the Excel spreadsheets?

The error is looking for a database format where the headers are matched as column headers.

If there are no headers, it doesnt' know how to match it.

Just my twist on it,

Adamus

|||

Hi Adamus,

I appreciate your feedback. To answer your question, the first row does contain column headers.The data flow task has an excel source file which uses the Dynamic Excel Connection manager that I mentioned in my original post. The excel source file connects to a derived column data task that creates derived columns and changes the data type to reflect that of the destination. The derived columns is then connected to the SQL Server destinationation table that matches the excel header columns to the destination table columns.

What should I do with the connection string that I mentioned in my first post? I have provided it here for you or anyone else to review.

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +@.[User::ExcelFile] + ";Extended Properties=\"" + @.[User::ExtProperties] + "\""

Can you or anyone else tell me what I am doing wrong here?

Thanks!!

Lee

|||

Can you actually import at least one single file? forget about the foreach loop and the expression in the connection manager.

I just did a search on 'Could not find installable ISAM ' and found this:

http://support.microsoft.com/kb/209805

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

So, before going deeper, try to connect to a single execel file.

BTW, I just found a test package I created long time ago, and I see some differences with your approach:

I have only one variable in my for each loop container to hold the file name; and since I configured the loop container to retrive 'Fully Qualified" file name; the variable contains the path and the file name. In the excel connection manager I applied an expression to the ExcelFilePath property (not to the Connection String). The expression is just: @.{User::VarFileName] ; which is the variable I use in the loop container. I don't mess with the extended properties.sql

Receiving Error: 'QUOTED_IDENTIFIER' when updating

Hello, I am getting the following message when running a stored proc:
Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Line
261
UPDATE failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
I cannot find any information about this error. This happens with
QUOTED_IDENTIFIER on or off.Ric,
Try going to Query Analyzer and editing the sp. You should be able to see
the settings of "QUOTED_IDENTIFIER" when the sp was created. chnage it to
accomodate your needs.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure dbo.SZ_ProcessToCPInvoices
...
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
AMB
"Ric" wrote:

> Hello, I am getting the following message when running a stored proc:
> Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Lin
e
> 261
> UPDATE failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> I cannot find any information about this error. This happens with
> QUOTED_IDENTIFIER on or off.
>

Receiving Error: 'QUOTED_IDENTIFIER' when updating

Hello, I am getting the following message when running a stored proc:
Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Line
261
UPDATE failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'.
I cannot find any information about this error. This happens with
QUOTED_IDENTIFIER on or off.
Ric,
Try going to Query Analyzer and editing the sp. You should be able to see
the settings of "QUOTED_IDENTIFIER" when the sp was created. chnage it to
accomodate your needs.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER procedure dbo.SZ_ProcessToCPInvoices
...
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
AMB
"Ric" wrote:

> Hello, I am getting the following message when running a stored proc:
> Server: Msg 1934, Level 16, State 1, Procedure SZ_ProcessToCPInvoices, Line
> 261
> UPDATE failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'.
> I cannot find any information about this error. This happens with
> QUOTED_IDENTIFIER on or off.
>

Receiving error when trying to use try..catch block

I am using SQL Server 2005 and am trying to execute the following statements:

BEGIN TRY SELECT 1/0 END TRY BEGIN CATCH SELECT 'Error Caught' END CATCH I am getting the following errors:

Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near 'TRY'.

Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'END'.

This code should work, can anyone tell me why it it not working?

Thanks in advace.

Hmmm, works fine here.

Is there something else you have coded in the same script?

Are you using SSMS?

|||Sorry, my mistake. I was using our SQL Server 2005 Management Studio, but had accidentally connected to the SQL Server 2000 box to test with. Thanks.|||Been there, done that

Receiving Error 26 when trying to connect to DB...Some Machines

Good Day,

We have a recently developed (and in testing) VB .Net application which attaches to a specific SQL2005 DB. The issue is that when installed (or run in DEBUG mode) on the developer box it successfully connects to the appropriate db locally and to the live server. When installed on a test box (several of them) we receive error - 26 Error locating Server/Instance Specified.

The SQL Server instance is set up for remote connections and works for other dbs.

All of the boxes are running XP Pro, with all SPs applied.

Any thoughts on what may be occuring would be greatly appreciated.

This might help:
"Connection error occurs when the Database Engine service account password expires

The

following error occurs when you connect to a report server, and the

service account password has expired for the SQL Server Database Engine

instance that hosts the report server database: "The report server

cannot open a connection to the report server database. A connection to

the database is required for all requests and processing.

(rsReportServerDatabaseUnavailable)."

The error message includes

these additional statements: "An error has occurred while establishing

a connection to the server. When connecting to SQL Server 2005, this

failure may be caused by the fact that under the default settings SQL

Server does not allow remote connections. (provider: SQL Server Network

Interfaces, error: 26 - Error Locating Server/Instance Specified)."

To resolve this error, reset the password. For more information, see Changing Passwords and User Accounts."

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

Also check out this kb:
http://support.microsoft.com/kb/905618

Friday, March 30, 2012

Receiving this error since migrating to SQL 2000 from SQL 7

Receiving this error since migrating to SQL 2000 from SQL 7

Receiving this error since migrating to SQL 2000 from SQL 7

Receiving system error when retrieving database record with null value

Receiving SQL Server Error messages in SQL Server Log

Receiving SQL Server Error messages in SQL Server Log

Receiving SQL Server Error messages in SQL Server Log

Receiving queue does not fire stored procedure

Receiving 'PRIMARY KEY constraint 'PK__@snapshot_seqnos' error...

REceiving notification in windows application

RECEIVING messages from a Windows service

RECEIVING messages from a Windows service

Receiving garbage in excel when rendered via Subscription

RECEIVING from QUEUE by ConversationHandle

Receiving Export file name

Receiving errors when using foreach loop and excel connection manager...

Receiving Error: 'QUOTED_IDENTIFIER' when updating

Receiving Error: 'QUOTED_IDENTIFIER' when updating

Receiving error when trying to use try..catch block

Receiving Error 26 when trying to connect to DB...Some Machines

Real Time DSS

Blog Archive

About Me