More queries more problems

I'm trying to write a query that returns a list of all employess from one table, and their hours worked from another table. If there are no entries in the hours table, I want to still show them with value of zero hours worked. I almost have this working, but I'm having some issues. Here's my query:

SELECT th.ID, tp.FirstName + ' ' + tp.LastName as Name, IsNull(Total_Hours, '0.0') as Hours1
FROM tblProfiles tp LEFT JOIN tblHours th on tp.ID = th.userID
WHERE (th.Start_Date = '" + myStartDate + "' OR th.Start_Date IS NULL)
ORDER BY tp.FirstName

For testing, in my profiles table, I have 5 users. In my hours table, I have entries for 3 of those users. Of those three users, 2 have entries for both 1/1/03 and 1/16/03, and the other has just 1 entry for 1/1/03.

When I run the query above for myStartDate = '1/1/03', I get the correct output, which shows the hours for 3 users, and shows 0 hours for the other 2. However, if I run the query on myStartDate = '1/16/03', my result set has the hours for the 2 users that have entries for that date, and also has 0 hours for the 2 users that have no entries. The user that has only 1 entry (for 1/1/03)should show up with 0 hours, but instead, it doesn't show up at all.

I'm guessing that the problem is with the way the table is joined, and that because that user has 1 valid Start_Date, it is getting excluded from the WHERE conditions, but I can't seem to make it work.

help!can you show the structure of the 2 tables ...|||tblProfiles has the following columns:

ID FirstName LastName ... (other unimportant columns)

tblHours has the following columns:

ID userID Start_Date End_Date Total_Hours

|||have you tried running this query in Query Analyzer as with the straight sql to make sure that you are getting the same results?|||yeah, I did... in Enterprise Manager, if I run the same query, I get the same results... the user with 1 entry for 1/1/03 does not show up at all if I run the query with:

WHERE Start_Date = '1/16/03' OR Start_Date IS NULL

I am assuming that "Start_Date IS NULL" is not evaluating to true because, with the table join, it is picking up the other record with the 1/1/03 Start_Date ...|||In my WHERE clause:

WHERE Start_Date = '" + myStartDate + "' OR Start_Date IS NULL

I think theStart_Date IS NULL part needs to specifically refer only the record for the given "myStartDate", but I can't make it jive...|||Yeah this is ringing a bell, I had something similiar. Maybe try wrapping everything in parens, like

WHERE ((Start_Date = '" + myStartDate + "') OR (Start_Date IS NULL))

Not sure but this might force SQL to evaluate the where clause correctly.|||I tried your suggestion, but it didn't change the results at all...|||have you thought abt using a cursor...
loop through for each record in tblprofiles...and for each record join with tblhours on id and start_date and get the hours..
this way you can get those ids tht dont have any entries...startdates/hours...etc and still show them.

HTH|||I'm not familiar with using cursors, but I'm willing to try anything... can you give me an example of how I would use it?|||heres a sample cursor tht i am using

DECLARE rs CURSOR
LOCAL
FORWARD_ONLY
OPTIMISTIC
TYPE_WARNING
FOR SELECT ID,FirstName,LastName, ... (other columns)
OPEN rs
fetch next from rs into @.id,@.fname,@.lname...( other columns)
WHILE ( @.@.FETCH_STATUS = 0 )
begin
 /* here do your stuff - join with the other table and get the stuff */
 use the @.id to join with tblhours..
 FETCH NEXT FROM rs INTO @.id,@.fname,@.lname...( other columns)
END
close rs
deallocate rs

HTH|||Ok, I'm getting lost in the syntax... here's what I have, but it's not working:

DECLARE @.id integer, @.fname varchar(50), @.lname varchar(50)
DECLARE rs CURSOR
LOCAL
FORWARD_ONLY
OPTIMISTIC
TYPE_WARNING
FOR SELECT tp.ID, tp.FirstName, tp.LastName FROM tblProfiles tp
OPEN rs
fetch next from rs into @.id,@.fname,@.lname
WHILE ( @.@.FETCH_STATUS = 0 )
begin
 SELECT @.id, @.fname, @.lname
SELECT th.Total_Hours FROM tblHours th WHERE th.userID = @.id AND th.Start_Date='1/16/03'
 fetch next from rs into @.id,@.fname,@.lname
END
close rs
deallocate rs

what am I doing wrong?|||I finally got it to work, and didn't have to use the cursor! Here's the working query:

SELECT IsNull(th.ID, '0') as ID, tp.FirstName + ' ' + tp.LastName as Name, IsNull(Total_Hours, '0.0') as Hours1
FROM tblProfiles tp LEFT JOIN tblHours th on tp.ID = th.userID AND th.Start_Date = '" + myStartDate + "'
WHERE ((th.Start_Date = '" + myStartDate + "') OR (th.Start_Date IS NULL)) ORDER BY th.Total_Hours DESC, tp.FirstName

The solution was to addStart_Date = '" + myStartDate + "' to the LEFT JOIN criteria.

Thanks for all the help along the way guys...
<sigh of relief>|||

CREATE PROCEDURE get_hrs AS
begin
DECLARE @.id as int, @.fname as varchar(50), @.lname as varchar(50)
declare @.mystartdate as datetime
set @.mystartdate='1/1/2003'
declare @.hrs as int
DECLARE rs CURSOR
LOCAL
FORWARD_ONLY
OPTIMISTIC
TYPE_WARNING
FOR SELECT [ID], FName, LName FROM tblpro
OPEN rs
fetch next from rs into @.id,@.fname,@.lname
WHILE ( @.@.FETCH_STATUS = 0 )
begin
select @.hrs=hours from tblhours where ([ID] =@.id and startdate=@.mystartdate)
print convert(varchar(5),@.id) + ' ' + @.fname + '-' + @.lname + ' ' + convert(nvarchar(10),@.hrs)
set @.hrs=0
 fetch next from rs into @.id,@.fname,@.lname
END
close rs
deallocate rs
end
GO

you might need to change the column names, table names...

HTH

More problems with updateText

Hi,

Basically I am trying to add 2 ntext fields together sandwhiched by a literal ( '<BR /><BR />' ) in a SP for a report I will be running.

First step is to add <BR /><BR />, which I have done with some help from this forum, using a cursor and temp tables.

The last step is add the second ntext column (if it exists for the case only though). So like the 1st step I am using cursor and updatetext to amend the temporary table. The problem is that rather than update the temp table, the query section I have highlighted is run and nothing at all gets added to temptable. Since the column I am trying to add is a ntext I can't create a local variable. Does anyone know where I am going wrong?

Thanks in advance

Declare @.value2 varbinary(16)
DECLARE TEXTPTR_CURSOR2 CURSOR FOR
SELECT TEXTPTR(RT.Status) FROM #TempReport RT
DECLARE @.CurrentCase int

OPEN TEXTPTR_CURSOR2
FETCH NEXT FROM TEXTPTR_CURSOR2 INTO @.value2

WHILE @.@.FETCH_STATUS = 0
BEGIN

SELECT @.CurrentCase = caseid from #tempreport rt
where TEXTPTR(RT.Status) = @.value2

If ( select count(*) from tbl_memo m
where caseid = @.CurrentCase
and memotypeid = 8) > 0
BEGIN

UPDATETEXT #tempreport.Status @.value2 null 0
(select IsNull(thevalue,'')
from tbl_memo m
where caseid = @.CurrentCase
and memotypeid = 8)

END

FETCH NEXT FROM TEXTPTR_CURSOR2 INTO @.value2

END

CLOSE TEXTPTR_CURSOR2
DEALLOCATE TEXTPTR_CURSOR2

For those interested, I managed to crack this by using another text pointer:

Declare @.value2 varbinary(16)
DECLARE TEXTPTR_CURSOR2 CURSOR FOR
SELECT TEXTPTR(RT.Status) FROM #TempReport RT
DECLARE @.CurrentCase int

OPEN TEXTPTR_CURSOR2
FETCH NEXT FROM TEXTPTR_CURSOR2 INTO @.value2

WHILE @.@.FETCH_STATUS = 0
BEGIN

SELECT @.CurrentCase = caseid from #tempreport rt
where TEXTPTR(RT.Status) = @.value2

If ( select count(*) from tbl_memo m
where caseid = @.CurrentCase
and memotypeid = 8) > 0
BEGIN
declare @.value3 varbinary(16)
select @.value3 = textptr(thevalue) from tbl_memo m where caseid = @.currentcase and memotypeid = 8

UPDATETEXT #tempreport.Status @.value2 null 0 tbl_memo.thevalue @.value3

END

FETCH NEXT FROM TEXTPTR_CURSOR2 INTO @.value2
END

CLOSE TEXTPTR_CURSOR2
DEALLOCATE TEXTPTR_CURSOR2

more problems with unique sequences

I have another different schema with the same data, but this one its like this

Code Snippet

CREATE Table events (
id INT not null,
PxMiss Real Not Null,
PyMiss Real Not Null,
filenames Varchar(50));

ALTER TABLE events
ADD CONSTRAINT pk_particle PRIMARY KEY (id,filenames);

GO

CREATE Table Muon (
idap INT Not Null,
id INT Not Null,
eventid INT Not Null,
Px Real,
Py Real,
Pz Real,
Kf Real,
Ee Real);

GO

CREATE Table Electron(
idap INT Not Null,
id INT NOT NULL,
eventid INT Not Null,
Px Real,
Py Real,
Pz Real,
Kf Real,
Ee Real);

GO

CREATE Table Jet (
idap INT Not Null,
id INT NOT NULL,
eventid INT Not Null,
Px Real,
Py Real,
Pz Real,
Kf Real,
Ee Real);

GO

Create View lepton AS
select * from Muon
Union all
select * from Electron;
GO

Create View particle AS
select * from lepton
Union all
select * from Jet;
GO

I need that every particle had a different idap, but all the date is filled in muon, electron and jet. and then is joined in a view called particle.

The way that you are going about this is likely to cause some problems.

For example, while it is possible to create a VIEW that would provide a unique idap for for each Particle, since it is a view and will be re-constituted at every execution, there is no certainity that the idap will be the same at each execution. (In my opinion -that is a big issue. -but maybe not for your situation...)

It seems more stable if you were to create a Particle idap that was the combination of each constituent Identifier + idap. Something like this for example:

CREATE VIEW Particle
AS

SELECT
'M' + cast( idap AS varchar(12)),
id,
eventid,
Px,
Py
Pz
Kf,
Ee
FROM Muon

UNION ALL

SELECT
'E' + cast( idap AS varchar(12),
id,
eventid,
Px,
Py,
Pz,
Kf,
Ee
FROM Electron

UNION ALL

SELECT
'J' + cast( idap AS varchar(12),
id,
eventid,
Px,
Py,
Pz,
Kf,
Ee
FROM Jet
GO

With this example, each idap will be unique, and also give you a clue about the constitutent component.

More problems with hidden parameters

I've read that in SP1, it should be possible to hide a parameter without
blanking the prompt and therefore making it read only.
I have attempted to do this in the recommended way, i.e. by unticking the
"prompt user" check box for the parameter within report manager and
leaving the prompt with a value in it. However, when I then try to pass a
value for the parameter at runtime via a URL I get the error "Parameter1 is
read-only and cannot be modified".
N.B. I notice that when I then go back into the properties of the report,
the prompt for the parameter has been
changed from what it originally was to "Parameter1:" which is the name of
the parameter. I should also mention that this report is running against a
Sybase database.This is the defined behavior. If the 'prompt user' check box is not checked
then the parameter value can never be passed in, not via URL or SOAP. If
the parameter is marked 'prompt user' but it has no prompt string then the
Report Server toolbar will not prompt for the parameter and the parameter
can be passed in. If the 'prompt user' check box is check and there is a
prompt string then the Report Server toolbar will prompt the user and the
value can be passed in.
Does that help?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"GML" <g_m_lowe@.hotmail.co.uk> wrote in message
news:#9sQcGIgEHA.2020@.TK2MSFTNGP10.phx.gbl...
> I've read that in SP1, it should be possible to hide a parameter without
> blanking the prompt and therefore making it read only.
> I have attempted to do this in the recommended way, i.e. by unticking the
> "prompt user" check box for the parameter within report manager and
> leaving the prompt with a value in it. However, when I then try to pass a
> value for the parameter at runtime via a URL I get the error "Parameter1
is
> read-only and cannot be modified".
> N.B. I notice that when I then go back into the properties of the report,
> the prompt for the parameter has been
> changed from what it originally was to "Parameter1:" which is the name of
> the parameter. I should also mention that this report is running against
a
> Sybase database.
>
>|||Actually that helps me, wished I'd found this one before posting my own
question.
I should add that this is not the way the readme for SP1 suggests it should
work, it says that previously removing the check from the Promp User used to
make the parameter read only, but this behaviour has changed.
Regards
Mike Hanson
"Daniel Reib [MSFT]" wrote:
> This is the defined behavior. If the 'prompt user' check box is not checked
> then the parameter value can never be passed in, not via URL or SOAP. If
> the parameter is marked 'prompt user' but it has no prompt string then the
> Report Server toolbar will not prompt for the parameter and the parameter
> can be passed in. If the 'prompt user' check box is check and there is a
> prompt string then the Report Server toolbar will prompt the user and the
> value can be passed in.
> Does that help?
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "GML" <g_m_lowe@.hotmail.co.uk> wrote in message
> news:#9sQcGIgEHA.2020@.TK2MSFTNGP10.phx.gbl...
> > I've read that in SP1, it should be possible to hide a parameter without
> > blanking the prompt and therefore making it read only.
> > I have attempted to do this in the recommended way, i.e. by unticking the
> > "prompt user" check box for the parameter within report manager and
> > leaving the prompt with a value in it. However, when I then try to pass a
> > value for the parameter at runtime via a URL I get the error "Parameter1
> is
> > read-only and cannot be modified".
> >
> > N.B. I notice that when I then go back into the properties of the report,
> > the prompt for the parameter has been
> > changed from what it originally was to "Parameter1:" which is the name of
> > the parameter. I should also mention that this report is running against
> a
> > Sybase database.
> >
> >
> >
>
>sql

More problems with Gridview and SQL Procedures

I have successfully used SQL procedures to provide data to a Gridview previously, and I just can't see what's different about the simplified case that works and the real case that doesn't. I've extracted the code into a test page to avoid extraneous bumph.

1. I have developed a SQL procedure, GDB_P_Children, and tested this in SQL Server Management Studio Express. It returned exactly what it should. The procedure is: -

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GDB_P_Children]
@.IndiiD uniqueidentifier,
@.Option int,
@.Owner Varchar(50),
@.User Varchar(50)
AS
Select * from GDBChildren(@.Indiid, @.Option, @.Owner, @.User)
GO

2. I then tried to use it in my program to power a Gridview, but the gridview was blank. I put the Gridview into a test page that only had code: -

Option Compare Text
Partial Class test
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Session("INDIid") = "ffe1fb2f-88ce-4b64-a358-e21efd161d70"
Label2.Text = Session("INDIid") ' These are just for debugging
Session("Owner") = "robertb"
Label3.Text = Session("Owner") & ","
Session("Option") = 0
Label4.Text = Session("Option") & ","
Session("gdvChildUser") = ""
Label5.Text = Session("gdvChildUser") & ","
End Sub
End Class

I then regenerated the gridview from scratch: -

Toolbox: drag a gridview on to the page
Click "Configure Data sourec" => New Data Source => SQL Database. Name the datasource SQLChildren.
Connection string - select as normal
Configure: Specify a custom SQL statement or procedure
Select Stored Procedure, select GDB_P_Children
Set parameters to: -
INDIid Session("INDIid")
Option Session("Option")
Owner Session("Owner")
User Session("gdbChildUser")

Test Query showed that the first parameter, INDIid, had type Object, and I know that this will fail (seehttp://forums.asp.net/thread/1390374.aspx), so I finished data source configuration and edit the source of the test page to remove Type="Object" from the parameter definition. The parameter now has type "Empty", and filling it in with the values ffe1fb2f-88ce-4b64-a358-e21efd161d70, 0, robertb, and '' returns data as expected. Click Finish and the Gridview configures itself with the correct column headings: -

Exactly correct so far. Yet executing the page shows only my debugging labels, with no sign of the gridview.

I changed the stored procedure: -

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- Temporary testing version, stripped to bare essentials
ALTER PROCEDURE [dbo].[GDB_P_Children]
@.IndiiD uniqueidentifier
AS
Select INDIid, Indiusername, dbo.gdbindinamedate(INDIid) as IndiNameDate, Indisex
from nzgdb_Indi where INDIMothersindiid = @.INDIid

Data configuration was redone, then the page was displayed. It now correclty shows data from the database. So what's different?

I changed the procedure back to the original, and again the blank (except for debugging labels) page was displayed.

The data configuration was changed so that only the first parameter, @.INDIid, was passed, and the other were set to default values. Still the blank page.

Procedure GDB_P_Children was then changed to have only one parameter, with the other three declared and given values internally: -

ALTER PROCEDURE [dbo].[GDB_P_Children]
@.IndiiD uniqueidentifier
AS
Declare @.Option int
Declare @.Owner Varchar(50)
Declare @.User Varchar(50)
Set @.Option = 0
Set @.Owner = 'robertb'
set @.User =''
Select * from GDBChildren(@.Indiid, @.Option, @.Owner, @.User)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

The page is now OK, with the gridview showing data as expected.

So why can't I use this procedure with the four arguments that it is supposed to have? This is driving me crazy! I have other procedures with 2 arguments (both Uniqueidentifier) that work fine, so it's not the fact that this procedure has more than one argument that's causing problems. Help!!!!

Regards,

Robert Barnes

It look like a parameter issue to me. Can you open SQL profile and run a trace to see what is the parameter passed to the sp? normally that will tell you what is wrong

Hope this help

|||

My guess...

User you are setting to an empty string. The user parameter is set up to convert empty strings to a null. You also have the sqldatasource set up to cancel selects when one or more of the parameters is a null value.

|||Motely, you're right. The program worked fine when User is not null, and then when I changed the last parameter (User) to "Convert Empty String:False" it worked fine whether it was empty or not. Thank you.|||

David, I am having trouble running SQL profile. It won't recognise the SQL 2000 server on my laptop, even though it's started from Enterprise Manager which is running perfectly. In the meantime, Motely has solved my problem for me. Thank you for your help.

|||What's the meaning of "It won't recognise the SQL 2000 server on my laptop"? Did you mean SQL Profiler can not connect to the SQL 2000 instance by using the instance name? Then make sure you've enabled both TCP/IP and Named Pipes are enabled on the SQL instance as well as client.|||

"It won't recognize the SQL 2000 server on my laptop" means that: -

1. I have SQL 2000 running smoothly, using Windows Authentication. I can see it with either (or both) Enterprise Manager and with MS SQL Server Management Studio Express, and I can do any of the usual functions such as opening tables, running queries, ... The database is functioning normally with my application code.

2. I open SQL profiler (all programs/MS SQL Server/SQL Profiler) and click New Trace, which opens a dialog "Connect to SQL Server". Clicking the combo shows blank. Clicking the [...] opens another dialog, "Select server", but there is nothing in the list of active servers (there should be my laptop server, plus another). Going back to "Connect to SQL Server", I try typing the name of the server into the combo: the system thinks for a few seconds (30? I didn't time it) and then responds "SQL Server does not exist or access denied"

3. Using Enterprise Manager, I right-clicked my laptop server, clicked properties, on the general tab clicked Network Properties. TCP/IP and Named Pipes are already enabled.

So why won't SQL Profiler see the server?

|||

If you're using Profiler to connect to remote SQL2000 instance, and SQL2005/Express exists on the remote server, make sure the SQL Brower Service on the remote server is started. If the connection still fails, you can take a look at this post to get an idea of checking remote database connection:

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

|||Did this thread get mixed up - this seems to be part of another conversation. My SQL Server 2000, VWD2005, etc are ALL running locally on my laptop. Presumably my inablity to run SQL Profiler is not a network issue.|||

Of course we can disscuss this in a new post Then run 'cliconfg' to make sure you've enabled Shared Memory connectivity

|||

I ran cliconfg, and shared memory connectivity was already on: -

Click start button => click Run => Run dialog box. Enter cliconfg, click OK => SQL Server Client Network Connectivity. On General tab, towards the bottom, there is a check box "Enable Shared Memory Protocol". This is already checked.

What next?

Regards, Robert

|||There two other places you may be able to access Profiler connect to Query Analyzer and at the top you will see tools you should see the profiler there, another place is through the wizards most people don't know they exist because they are context sensitive so open your database and at the top of Enterprise Manager you will see the wizard and click on the index tuning wizard it is part of the profiler just like perf mon. Hope this helps.|||

Thanks Caddre, that looks promising. I'm still having a bit of difficulty - the profile isn't under Enterprise Manager/Tools, and when I go into the index tuning wizard it asks for a file that I haven't found (yet). I'll have to open up by SQL book and have a read. In the meantime I'd better close this thread off as I'm about to go on holiday, but I might be back in a few weeks.

Thanks for your help,

Robert

More problems with activation

I have two databases on the same instance.

One is Basket_ODS and the other is Intelligence_ODS. I am using service broker activation on a queue to move data from the Basket_ODS table to the Intelligence_ODS database. Previously I was able to move from table to table in Basket_ODS, however now that I am moving it to another database on the same instance it is no longer working.

If I set my active connection in SQL Management Studio to this user(BrokerUser) and execute the "move" procedure it works. When activated by Service Broker however, it does not. Here is the error message:

2006-05-09 14:47:52.940 spid86s The activated proc [ODS].[ProcessOrderQueue] running on queue Basket_ODS.ODS.Order Process Queue output the following: 'The server principal "BrokerUser" is not able to access the database "Intelligence_ODS" under the current security context.'

I'm sure I missed something becasue it works fine in the same database. BrokerUser has datareader and datawriter in both databases.

Thanks for any help on this matter.

Gary

Activated task run under impersonated security context, similar to using the EXECUTE AS clause. Let me cross-post a reply from Remus:

<remus>
The explanation is detailed in the 'Extending Database Impersonation by Using EXECUTE AS' chapter in BOL (http://msdn2.microsoft.com/en-us/library/ms188304(en-us,VS.90).aspx)

A short explanation is this: when executing under an EXECUTE AS context (as activated procedures always are), the trust is given by the dbo of the database. Therefore, the procedure is trusted only at the level of the database, not at the level of the server. Server level views require server level trust, and you execution context is lacking it. You execution context behaves as if you logged in with [Public] in the server. By marking the database as trustworthy, the dbo of the database is trusted at the server level and you execution context inherits all the permissions you expect at the server level.

Marking the database trustworthy is quite a powerfull step. The dbo of that database can elevate itself to sysadmin, there's no way of preventing it. A more refined approach is to sign the activated procedure with a server level certificate that has proper rights (ADD SIGNATURE).
</remus>

While the above could solve your problem, it may be worthwhile trying to ask yourself, why is the service located in Basket_ODS instead of Intelligence_ODS.

Hope that helps,
Rushi

|||

I actually have a sample showing how to do this: http://blogs.msdn.com/remusrusanu/archive/2006/03/07/545508.aspx

HTH,
~ Remus

|||

Thanks Rushi,

As usual you do a great job with follow up. The activated stored procedure copies data from the Basket_ODS database to the Intelligence_ODS database. If I move the proc to the Basket_ODS database, I ran into the same issue when it tried to copy over.

I did read the information on BOL and your earlier post on this. I guess I'll have to work my way through signing the stored procedure. The trust database option really won't be a player for me I think. I did set up trust tonight and it worked though. I have to do another run in the morning but it was incredibly slower than the copy to the same database.

I'll try to worth through the signing tomorrow. That seems the way to go for me. We are trying to operate in least-trust mode. The DBAs start getting white hair when I ask for things like database trust.

Thank you so much for your help and your contribution to the community.

Gary

|||

Thank's Remus. Wow that's a great sample. I wish I found that earlier today. Thanks again for the great information.

Gary

|||

I created the certificates as noted in your scripts. The user associated with the certificate in my system is BrokerUser2. I do have a question though.

why can't I do this?

ALTER QUEUE ODS.[Order Process Queue] WITH

ACTIVATION (

STATUS = ON,

PROCEDURE_NAME = ODS.ProcessOrderQueue,

MAX_QUEUE_READERS = 4,

EXECUTE AS 'BrokerUser2'

)

when I try this it says BrokerUser2 does not exist or I do not have access to it. I can do an sp_helpuser 'BrokerUser2' and see his rights though.

It did appear to work if I did this:

ALTER QUEUE ODS.[Order Process Queue] WITH

ACTIVATION (

STATUS = ON,

PROCEDURE_NAME = ODS.ProcessOrderQueue,

MAX_QUEUE_READERS = 4, EXECUTE AS OWNER)

I did create the ODS.ProcessOrderQueue stored procedure [With Execute As 'BrokerUser2'].

|||

The user executing the ALTER QUEUE statement must have IMPERSONATE permission over 'BrokerUser2'.

HTH,
~ Remus

More Problems With 32 Bit Package on x64 SQL Server

Hello,

I have gone through some documentation on how to execute a 32 bit package on SQL Server 2005 x64, and I'm still running into problems when I attempt to execute such a package from a Job.

Critical Points:

-The package was imported into an instance of Integration Services 2005 from the file system to a subdirectory of the MSDB folder. From Management Studio, Integration Services, the path to the package looks like this: Stored Packages\MSDB\CPS\CPS_Collections. 'CPS_Collections' is the name of the package.

-This package has a connection to an Access 2000 database using the Native OLE DB\Microsoft Jet 4.0 OLE DB Provider. I understand that there is not a 64 bit version of this provider. The Access database is stored on a different machine than the one that hosts the SQL Server 2005 x64 instance.

-According to this; http://msdn2.microsoft.com/en-us/library/ms141766.aspx I should be able to create the command line with the dtexecui.exe utility and copy and paste this line into a Job Step with the Job Step Type as operating system. Here is what my command line looks like: /SQL "\CPS\CPS_Collections" /SERVER bwdbfin1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E . I have the Job Type set up as Operating System, and the Job runs as SQL Agent Service Account

-When I attempt to run the Job, the Job fails. Here is a part of the error message from the Job history:

The process could not be created for step 1 of job 0x9B318B226174A24B8BD63CE8F4814864 (reason: The system cannot find the file specified).

Does this point to a problem of where the Access datbase is located? Could it be that the account that runs SQL Agent does not have access rights to the directory? Is there a way to run an operating system command as someone other than SQL Agent? Is there something wrong with the command line that I am using?

Please share if you have any ideas on this.

Thank you for your help!

cdun2

On a 64-bit server, the SSIS job step calls the 64-bit version of DTEXEC. You need to call the 32-bit version (located in C:\Program Files (x86)\Microsoft Sql Server\90\DTS on most installs). To do this, you need to use a CmdExec job step.|||Hi,

Since the error description said : The system cannot find the file specified, maybe you set the location of Access database into a mapping drive or something, try using this format \\ip_address_of_the_machine\

Best regards,

Hery|||

cdun2 wrote:

-According to this; http://msdn2.microsoft.com/en-us/library/ms141766.aspx I should be able to create the command line with the dtexecui.exe utility and copy and paste this line into a Job Step with the Job Step Type as operating system. Here is what my command line looks like: /SQL "\CPS\CPS_Collections" /SERVER bwdbfin1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E . I have the Job Type set up as Operating System, and the Job runs as SQL Agent Service Account

You can use DTEXECUI to construct the command arguments, but you still need to supply the executable name for the command line. I.e. your full command line should look like

"C:\Program Files (x86)\Microsoft Sql Server\90\DTS\Binn\DtExec.exe" /SQL "\CPS\CPS_Collections" /SERVER bwdbfin1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

|||

Thanks for the help, and sorry for the late response. I'll take your responses and look into this further. I can say that the location to the Access database is not expressed as a mapped drive.