Re: Set permissions on stored procedures

Hello to all,
How do I set permission to allow some users to Execute the stored procedures without setting indiviually in ea. stored procedure?
Thank you much!You don't

Place the users in a role and grant permission to the role.

re: cursor + rounding?

Hi Guys,

I've created a cursor inside a function. When I break it down and execute the code piece by piece, no problem. However, try and parse it together and I get an error - 'Mixing old and new syntax is not allowed?' Something to do with the return statements? as I can alter the function to a procedure and it parses fine. Anyone come across this before?

Also, the values are being rounded when putting them into the cursor, even though I've declared the variables the cursor uses specifically as decimal? How can I get around this please?

Cheers,

Michelle

Michelle:

Can you describe what you are doing? This sounds rather vague.

|||

Blast, it didn't post. Umm, I've sorted the first problem, and I think I may have the answer to the second one. Will give it a go and let you know. Thanks for your help!

Cheers,

Michelle

|||

OK, both of those problems fixed. Should have been

DECLARE product_cursor CURSOR local SCROLL

NOT

DECLARE product_cursor SCROLL CURSOR

and Should have been:

decimal(8,2)

NOT

decimal.

Stupid mistakes! However, now I'm able to parse it fine, but am getting:

Msg 443, Level 16, State 15, Procedure CalculateFreight, Line 15

Invalid use of side-effecting or time-dependent operator in 'SELECT INTO' within a function.

Msg 443, Level 16, State 15, Procedure CalculateFreight, Line 25

Invalid use of side-effecting or time-dependent operator in 'UPDATE' within a function.

when I execute it!?

The SELECT INTO and UPDATE statements are fine when executed alone

Michelle

RDA problem

I am attempting to execute an RDA.pull from my master database and it is generating exceptions the second time I attempt the pull.

NOTE: I always drop the table prior to executing the pull.

It seems that after the first time I execute a pull against a particular table name on the PDA I get the following:

"A duplicate value cannot be inserted into a unique index. [ Table name = __sysRDASubscriptions,Constraint name = c_LocalTableName ]"

Given that the table doesn't exist when I do the pull (I have already dropped it, and verified it's non-existence via the Query Analyzer), why does this error appear? It's almost as though some artifact of the table still exists in the background somewhere and it doesn't like responding to a pull...

Anyone see this before?

This only seems to manifest when I choose one of the trackingOn options--when tracking is off, this doesn't happen.|||

If you call CompactDatabase after dropping the table, does that resolve the error ? This is just a troubleshooting step to try.

Thank you!

Syed N. Yousuf

Microsoft Developer Support Professional

This posting is provided “AS IS” with no warranties, and confers no rights.

|||

Hmm..Drop all these

1) The table you have pulled

2) The error table (if any you chose)

3) The PK Indexes of the pulled table (if you pulled with IndexesOn)

4) The Constraints for that table

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Good idea Syed, but no luck. It didn't seem to make any difference if I compacted the database and then pulled into the new db created.

-Kevin

|||

Thanks for your suggestions Laxmi

I'm having trouble with this. How can I drop the constraints and PK Indexes if I don't know what they're called? It would seem that SQL Server Mobile has assigned generated names to these, and I can't figure out how to drop them.

Is this even possible?

-Kevin

|||

To know the indexes and their information such as on what table and what columns they are created use this query:

SELECT * FROM INFORMATION_SCHEMA.INDEXES;

Similarly for constraints,

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS;

SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

Hope this helps!

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Hi Laxmi

Thanks for the info. This allows me to see the constraints and indexes, but Sql Mobile doesn't seem to keen on giving them up. When I try to delete my PK constraint (using ALTER TABLE xxx DROP CONSTRAINT) I get a message saying "DDL Operations on this table are restricted".

One thing I found that works is to simply delete the database file and recreate it prior to calling rda.pull. This would seem to be the simplest--are there any problems you see in this approach?

-Kevin

|||

No problem, only if there is no other table in that database. Please make sure that you transfer the data from old database to new database in this approach.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation