ActiveDirectory Group Security

Hi all

What i want to do - Execute a Stored Procedure when a user log on that is in aActiveDirectory Group.

I want to create a storedprocedure that will be executed when a windows user log on that is part of a specific ADGroup.

I was able to create the ADGroup and add it to logins. I was able to create the procedure with the ADGroup as owner.

The problem is when the user log on, he is not seen as part of the group that has rights on the DB.

Please help

From your description, I am assuming that you are having trouble to access a database (different than master) where your SP resides, correct?

If this is the case, probably what happened is that the AD group in that DB doesn’t have access to it. This is typically the case when users are created implicitly. You can try the following:

USE [<db_name>]

go

GRANT CONNECT TO [<ADGroup_name>]

go

BTW. If you only want the AD group to be able to execute the SP, you don’t need to make them owners, granting EXECUTE on the SP should be sufficient.

I hope this information helps, let us know if this solved your problem.

- Raul Garcia

SDE/T

SQL Server ENgine

Active/Active cluster to same default instance

We have a 2 node SQL2K active/passive cluster on WIN2K3 OS configured with
single virtual server with deafult instance. What is the procedure to change
this to active/active cluster so that we could use both servers processing
power to the same virtual server default instance?
"Active/Active" refers to multiple instances running on multiple nodes in a
cluster. SQL does not have any native support for sharing physical database
files between multiple active instances of SQL Server.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Rajan" <Rajan@.discussions.microsoft.com> wrote in message
news:5D9906BB-7503-425A-AB35-279EAF0D2D57@.microsoft.com...
> We have a 2 node SQL2K active/passive cluster on WIN2K3 OS configured with
> single virtual server with deafult instance. What is the procedure to
> change
> this to active/active cluster so that we could use both servers processing
> power to the same virtual server default instance?
|||This is part of the terminology misunderstanding concerning clustering.
Generally speaking, Active/Passive clusters are what Microsoft considers as
their cluster model (Shared Nothing). Active/Active clusters, on the other
hand, are when the cluster members all share the disk drives, network, and
IP resources and provide load balancing capabilities like Oracle's RAC
systems, or quick failover scenarios like Neverfail or Polyserve.
Unfortunately, Microsoft also used this terminology, but since MSCS is a
shared nothing model, only one member can own resources at a time. What
they meant by A/P and A/A is single and multi-instancing, respectively.
So, the short answer is No, you can't load balance with MSCS SQL Server
Failover clusters.
If you want to load-balance, then you have to build a Server Federation,
which is a system distribution technology, which requires application
redirection and database partitioning to function. It is not truly
load-balancing like MS NLB.
You CAN, however, have multiple instances, but each would have its own
dedicated disk, Network Names, and IP Addresses and Ports assigned to them.
If you can configure your application to direct to multiple databases, and
then have these database distributed on multiple SQL Server instances, then
you can achieve some workload distribution in a clustered configuration
without have to resort to a full Server Federation.
Anthony Thomas

"Rajan" <Rajan@.discussions.microsoft.com> wrote in message
news:5D9906BB-7503-425A-AB35-279EAF0D2D57@.microsoft.com...
> We have a 2 node SQL2K active/passive cluster on WIN2K3 OS configured with
> single virtual server with deafult instance. What is the procedure to
change
> this to active/active cluster so that we could use both servers processing
> power to the same virtual server default instance?
|||Thanks all for the detailed information - Rajan.
"Anthony Thomas" wrote:

> This is part of the terminology misunderstanding concerning clustering.
> Generally speaking, Active/Passive clusters are what Microsoft considers as
> their cluster model (Shared Nothing). Active/Active clusters, on the other
> hand, are when the cluster members all share the disk drives, network, and
> IP resources and provide load balancing capabilities like Oracle's RAC
> systems, or quick failover scenarios like Neverfail or Polyserve.
> Unfortunately, Microsoft also used this terminology, but since MSCS is a
> shared nothing model, only one member can own resources at a time. What
> they meant by A/P and A/A is single and multi-instancing, respectively.
> So, the short answer is No, you can't load balance with MSCS SQL Server
> Failover clusters.
> If you want to load-balance, then you have to build a Server Federation,
> which is a system distribution technology, which requires application
> redirection and database partitioning to function. It is not truly
> load-balancing like MS NLB.
> You CAN, however, have multiple instances, but each would have its own
> dedicated disk, Network Names, and IP Addresses and Ports assigned to them.
> If you can configure your application to direct to multiple databases, and
> then have these database distributed on multiple SQL Server instances, then
> you can achieve some workload distribution in a clustered configuration
> without have to resort to a full Server Federation.
>
> Anthony Thomas
>
> --
> "Rajan" <Rajan@.discussions.microsoft.com> wrote in message
> news:5D9906BB-7503-425A-AB35-279EAF0D2D57@.microsoft.com...
> change
>
>

Active directory linked server access!

I got a linked server to retrieve information from our Active Directory. Using a stored procedure that accesses the linked server I update a table. The procedure works!

But when I try to run it from a task, it does not work.

Does someone know, which permission has to be configured and where?

Depending on the impersonation defined on the linked server, the service account running SQL Server Agent needs to have access to the Active directory information. Normally this is done by using a domain account as the service account for SQL Server Agent.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Activation/Security/Dynamic SQL Question

I will have a variety of different types of work that will come into my Service Broker queue and I'll likely have a stored procedure or two for each of the different types of work (ie. move order header, move items, move payment, etc.) What is required to be done in each of these steps may vary by the subsidiary and type of order coming in. My plan is to use exclusively stored procedures but to execute them dynamically using sp_executesql. I think I should use sp_executesql because that way I can have a config file (in xml) that I can store what stored procedures need to be called for which unit of work/order type/subsidiary. If I do this I should be able to easily configure each type of work to be done in a config file and let Service Broker handle the execution dynamically. As long as I keep the parameters the same for each of the stored procedures (I'm thinking maybe 4 or 5 parameters) and passing them to each of the stored procedures, this approach will allow me to dynamically configure Service Broker to do what it is supposed to do. I can pull what needs to be done out of the message that comes in with an XQuery expression on the config file. I know that I will have to configure my user (activation user) to be able to run sp_executesql and the security may be complex (especially since I'm using certificates). I can not use trusted databases. Are there any other considerations I should think about?

Gary

You should be able to execute dynamic SQL as well as impersonate database principals (using the 'EXECUTE AS' statement or clause) to call message type handlers from the activation stored procedure. If you stay within the database, your database does not have to be trustworthy.

Hope that helps,
Rushi

Activation procedure design

I've been experimenting with Service Broker and was surprised at one aspect of the design: the interface to Activation stored procedures.

I would have expected the queue to be a parameted passed to the procedure rather than having to hard code the queue query into the SP.

In a system with lots of queues it seems plausible that the same activation procedure might want to be used with several queues.

Any comments?

David.

Stored procedures are compiled into execution plans that bind strictly the rowsets being involved. That means that a stored procedure cannot be compiled to issue a RECEIVE (or SELECT for the matter) against a generic 'queue', but only agains a very specific <queue_name>. This is the same reason why one cannot write a SELECT where the table name is a @.variable. The only workaround, both in SELECT and in activation case, is to use dynamic SQL, with the likely cost of having to compile the dynamic SQL when the procedure executes.

Given this it would not make sense to have the queue name passed as a parameter, it is not a performant pattern. However, if you must, there is the trick to get the queue you were activated for from sys.dm_broker_activated_tasks and build dynamic SQL to RECEIVE the messages.

HTH,

~ Remus

Activated stores procedure and Batch Processing

Hi There

2 Questions :

1. Almost in every SB example you will see this sql :

BEGIN TRANSACTION

WAITFOR (

RECEIVE TOP (1)

@.MessageType = message_type_name,

@.Message = message_body

FROM [Queue1]

WHERE conversation_handle = @.ConversationHandle

), timeout 5000;

If this sql in an activated sp do you really have to have the waitfor ? Since the sp will only be fired if there is a message on the queue ?

2. It is reccomended that for high volume SB apps you do not do a top(1) receive but process batches. Exactly what is the best practice to do this. Receive a batch into a table variable and then what ? Process through it with a cursor ? That is not very efficient either, i would just like some insight into batch queue processing as everywhere i have seen uses top (1) from the queue ?

Thanx

Hi Dietz,

1. The purpose of the WAITFOR is to have the procedure linger a few seconds when it empties the queue, in hope a new message comes in and gets processed. W/o this technique, the procedure might be deactivated just to be activated immedeatly by a new message.

2. To process a batch of messages, the best approach is indeed to RECEIVE into a @.table variable and process a cursor over the @.table variable. A cursor over a @.table variable is quite efficient, specially if the cursor is kept open between RECEIVEs.
One thing to keep in mind is the particular message pattern expected by the service. Remeber that RECEIVE only returns messages for one particular conversation_group at a time, which in most cases equates one conversation. If the typical message pattern is request-reply, then the RECEIVE will mostly return only 1 message at a time anyway, so using the TOP(1) might be simpler and more efficient.
My recommendation is to actually test and measure the performance, because it depends a lot with the message flow pattern.

HTH,
~ Remus

|||

Hi Remus

Initially it seems our app will only send individual messages and the target simpy receive and end conversation, and not have long conversatons or conversation groups spanning many dailogs, so in that case would you agree a top(1) should work fine ? So processing batches is only more efficient if you have large conversation groups , which is not true in our case where it is just a single conversation.

Thanx

Activated Stored Proc blocking?

Hi There

I was wondering, i have experienced alot of stored procedure blocking where you have a stored procedure that get executed very frequently and is complex, when the sp has to recompile it is locked and cannot be executed by other processes.

How will this work with an activated stored procedure, our activated stored procedure will probably not be too complex but it may exec complex ones base on the message type.

Now lets say that a message is recieved by the activated sp he in turn executes a complex sp to process the message this causes the complex sp to recompile, during this time another activated sp is spawned by the queue with the same message type he execs the same complex sp that is busy recompiling, i presume that the second activated sp will be blocked until the sp has completed recompiling.

Is this correct, because on a very busy queue with very complex sp's being called by the activated stored procedure, the benefit of having multiple queue readers is negated by the blocking caused by the recompiling sp's called by the activated sp.

Basically no matter how many queue readers kick in to handle the messages they all wait for the sp to recompile from the first message.

I am hoping multiple spawned activated stored procedures somehow over come this? Or work differently somehow.

Does anyone know ?

Thanx

The stored proc will be compiled only once. After that, all callers should be able to use the stored proc without blocking other callers.

If you need the stored procs to be modified frequently, that could result in constant recompilation as you have described above. In that case, you could use dynamic SQL and put the frequently changing code as strings in a table.

Hope that helps.

|||

Hi Rushi

Perhaps i need to found out more about sp recompiles, we have a large sp about 1000 lines. It never changes, however it recompiles everytime it executes, not sure why. Thats why i was under the impression that very large complex sp's executed by the activated stored prcedure would blocked the additional spawned queue readers?

What are you r thoughts on this? Is it correct that sp's that have large amounts of dynamic sql will recompile frequently, i cannot put these strings in a table since they are dynamically created with variables passed to the sp.

Thank You

acess I/O File

Is it possible to access a file (for example, to write in a text file)
with transact-sql?.
I've create a stored procedure and I'd like to write in a log file
some traces depends on the result of the statements executed in this
stored procedure.
ThanksHere's one method:

ALTER PROC WriteToLog
@.Message varchar(100)
AS
DECLARE @.Command varchar(255)
BEGIN TRAN
-- ensure only one process writes to the log at a time
EXEC sp_getapplock
@.Resource = 'MyLog.txt',
@.lockmode = 'Exclusive'
SET @.Command = 'ECHO ' +
@.Message +
' >>"C:\MyLog.txt"'
EXEC master..xp_cmdshell @.Command, NO_OUTPUT
EXEC sp_releaseapplock @.Resource = 'MyLog.txt'
COMMIT
GO

EXEC WriteToLog 'Test message'

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Bego?a" <bego_colomer@.yahoo.es> wrote in message
news:c04c2a85.0311180409.4631ec98@.posting.google.c om...
> Is it possible to access a file (for example, to write in a text file)
> with transact-sql?.
> I've create a stored procedure and I'd like to write in a log file
> some traces depends on the result of the statements executed in this
> stored procedure.
> Thanks

Accounting procedure

Hi,
I have the following procedure that I am trying to run on a data warehouse -
it is supposed to increase or decrease the running premium balance and the
fire fee balance based on the type of transaction, but it seems to just be
increasing the running balance - do you see where I am going wrong?
Thanks in advance
-- Local Variables
DECLARE
@.PolicyKeyID INT,
@.Policy_Number VARCHAR (10),
@.Trans_Type VARCHAR(40),
@.Billed_Premium DECIMAL(10,2),
@.Billed_Fire_Fee DECIMAL(10,2),
@.Pymt_Recvd DECIMAL(10,2),
@.SC_Recvd DECIMAL(10,2),
@.Transcode VARCHAR (4),
@.Portfolio_Set VARCHAR (4),
@.Type VARCHAR (25),
@.Typecode VARCHAR (4),
@.Previous_Policy_Number VARCHAR (10),
@.Running_Premium DECIMAL(10,2),
@.Running_Fire_Fee DECIMAL(10,2),
@.Error_Code_Tran INT,
@.Error_Code_Proc INT,
-- CONSTANTS declared for Transaction Types
@.NEW_BILL VARCHAR(15),
@.RENEWAL VARCHAR(20),
@.CANCELLATION VARCHAR(35),
@.CHANGE VARCHAR(15),
@.CASH_WITH_APP VARCHAR(25),
@.RETURNED_CHECK VARCHAR(35),
@.PAYMENT VARCHAR(35),
@.PYMT_REVERSAL VARCHAR(25),
@.DISBURSEMENT VARCHAR(25),
@.CANCEL_DISBURSEMENT VARCHAR(35)
-- Assign CONSTANTS
SET @.NEW_BILL = 'New'
SET @.RENEWAL = 'Renewal'
SET @.CANCELLATION = 'Cancellation'
SET @.CHANGE = 'Change'
SET @.CASH_WITH_APP = 'Cash With App'
SET @.RETURNED_CHECK = 'Returned Check'
SET @.PAYMENT = 'Payment'
SET @.PYMT_REVERSAL = 'Payment Reversal'
SET @.DISBURSEMENT = 'Disbursement'
SET @.CANCEL_DISBURSEMENT = 'Cancel Disbursement'
-- Set Error Codes
SET @.Error_Code_Tran = 0
SET @.Error_Code_Proc = 0
SET NOCOUNT ON
DECLARE crsrTransactions CURSOR LOCAL STATIC FOR
SELECT PolicyKeyID, Policy_Number, Portfolio_Set, Billed_Premium,
Billed_Fire_Fee, Pymt_Recvd, SC_Recvd,Trans_Type,Transcode,Type,Typec
ode
FROM Stage_Fire_Fee
ORDER BY Policy_Number, Policy_Date_Time ASC
OPEN crsrTransactions
FETCH NEXT FROM crsrTransactions INTO @.PolicyKeyID, @.Policy_Number,
@.Portfolio_Set, @.Billed_Premium, @.Billed_Fire_Fee, @.Pymt_Recvd,
@.SC_Recvd,@.Trans_Type,@.Transcode,@.Type,@.
Typecode
-- Outer Loop for all Transactions
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Initialize Running totals, reinitialize for each Policy #
SET @.Running_Premium = 0
SET @.Running_Fire_Fee = 0
SET @.Previous_Policy_Number = @.Policy_Number
IF @.Trans_Type IN
(@.NEW_BILL,@.RENEWAL,@.CANCELLATION,@.CHANG
E,@.RETURNED_CHECK,@.DISBURSEMENT,@.CAN
CEL_DISBURSEMENT,@.PYMT_REVERSAL)
-- Found basic Billed, so increment our running Premium and Fire Fee totals
BEGIN
SET @.Running_Premium = @.Running_Premium + @.Billed_Premium
SET @.Running_Fire_Fee = @.Running_Fire_Fee + @.Billed_Fire_Fee
END
ELSE IF @.Trans_Type = (@.PAYMENT)
-- Found Payment transaction, so subtract the paymnet from the total
amounts. Apply as much of the payment as possible to the Fire Fee Balance
BEGIN
SET @.Running_Premium = (@.Running_Fire_Fee + @.Running_Premium) -
@.Pymt_Recvd
IF @.Running_Fire_Fee <= @.Pymt_Recvd SET @.Running_Fire_Fee = 0
ELSE SET @.Running_Fire_Fee = @.Running_Fire_Fee - @.Pymt_Recvd
END
ELSE IF @.Trans_Type = (@.CASH_WITH_APP)
-- Found Payment transaction, so subtract the paymnet from the total
amounts. Apply as much of the payment as possible to the Fire Fee Balance
BEGIN
SET @.Running_Premium = (@.Running_Fire_Fee + @.Running_Premium) -
@.Pymt_Recvd
IF @.Running_Fire_Fee <= @.Pymt_Recvd SET @.Running_Fire_Fee = 0
ELSE SET @.Running_Fire_Fee = @.Running_Fire_Fee - @.Pymt_Recvd
END
ELSE
BEGIN
RAISERROR ('Unknown Transaction Type: %s', 0, 1, @.Trans_Type)
SET @.Error_Code_Tran = 1
SET @.Error_Code_Proc = 1
END
IF @.Error_Code_Tran = 0 UPDATE Stage_Fire_Fee SET Stage_Premium_Bal =
@.Running_Premium, Stage_Fire_Fee_Bal = @.Running_Fire_Fee WHERE PolicyKeyID =
@.PolicyKeyID
SET @.Previous_Policy_Number = @.Policy_Number
FETCH NEXT FROM crsrTransactions INTO @.PolicyKeyID, @.Policy_Number,
@.Portfolio_Set, @.Billed_Premium, @.Billed_Fire_Fee, @.Pymt_Recvd,
@.SC_Recvd,@.Trans_Type,@.Transcode,@.Type,@.
Typecode
SET @.Error_Code_Tran = 0
END
ENDOn Sun, 6 Nov 2005 16:44:36 -0800, Patrice wrote:

>Hi,
>I have the following procedure that I am trying to run on a data warehouse
-
>it is supposed to increase or decrease the running premium balance and the
>fire fee balance based on the type of transaction, but it seems to just be
>increasing the running balance - do you see where I am going wrong?
>Thanks in advance
Hi Patrice,
First some general advice:
1. Generally, don't store data that can be calculated in the database.
Each time the base data changes, you'll have to re-do all calculations.
Or you can choose to recalculate periodically, but then, the stored data
might be incorrect when you query the table.
2. Try to avoid cursors. They should only be used when all else fails,
or when you can prove that there is no reasonable set-based alternative.
On to your code.
It's hard to see what's going wrong, becuase you didn't include all
information needed to troubleshoot. Please post:
- The table structures, as CREATE TABLE statements (including all
constraints and properties, but excluding irrelevant columns),
- Some rows of sample data to illustrate the problem, as INSERT
statements,
- The expected output, and
- The output you are actually seeing.
Check out www.aspfaq.com/5006 for more suggestions on how to provide the
information we need in order to help you.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Accidental duplicate results...

ALTER PROCEDURE discussions_GetTopics
(@.board_id as int)
AS

SELECT discussions_Topics.*, discussions_Posts.*, user_1.UserName AS Topic_Author_Username,
user_1.UserId AS Topic_Author_ID, user_2.UserName AS Post_Author_Username, user_2.UserId AS Post_Author_ID
FROM discussions_Topics INNER JOIN
discussions_Posts ON discussions_Topics.topic_id = discussions_Posts.topic_id INNER JOIN
aspnet_Users AS user_1 ON user_1.UserId = discussions_Topics.topic_poster INNER JOIN
aspnet_Users AS user_2 ON user_1.UserId = discussions_Posts.poster_id
WHERE (discussions_Topics.board_id = @.board_id)

I am simply trying to return a result for each topic, that has user info for both the author of the topic and the author of the last post (user_1, user_2)

The problem is, it will return multiple datarows with the same topic, and each of them have a different last post author.. when there can only be one last poster... idk.. im confused.. help?

Try:

ALTER PROCEDURE discussions_GetTopics(@.board_idas int)ASSELECT discussions_Topics.*, discussions_Posts.*, user_1.UserNameAS Topic_Author_Username, user_1.UserIdAS Topic_Author_ID, user_2.UserNameAS Post_Author_Username, user_2.UserIdAS Post_Author_IDFROM discussions_TopicsINNERJOIN discussions_PostsON discussions_Topics.topic_id = discussions_Posts.topic_idINNERJOIN aspnet_UsersAS user_1ON user_1.UserId = discussions_Topics.topic_posterINNERJOIN aspnet_UsersAS user_2ON user_2.UserId = discussions_Topics.last_poster_idWHERE (discussions_Topics.board_id = @.board_id)

|||

SELECT discussions_Topics.*, discussions_Posts.*, user_1.UserName AS Topic_Author_Username,
user_1.UserId AS Topic_Author_ID, user_2.UserName AS Post_Author_Username, user_2.UserId AS Post_Author_ID
FROM discussions_Topics INNER JOIN
discussions_Posts ON discussions_Posts.topic_id = discussions_Topics.topic_id INNER JOIN
aspnet_Users AS user_1 ON user_1.UserId = discussions_Topics.topic_poster INNER JOIN
aspnet_Users AS user_2 ON user_2.UserId = discussions_Posts.poster_id
WHERE (discussions_Topics.board_id = @.board_id)

In the Topics table, I did not have a field for "last_poster_id". I only have a "last_post_id" to use to search the "Posts" table to find the author of the post.

The problem with this query is that it returns a seperate topic for each user that has posted in the topic as the last poster. any help?

|||

WAIT!

GOT IT!

ALTER PROCEDURE discussions_GetTopics
(@.board_id as int)
AS

SELECT discussions_Topics.*, discussions_Posts.*, user_1.UserName AS Topic_Author_Username,
user_1.UserId AS Topic_Author_ID, user_2.UserName AS Post_Author_Username, user_2.UserId AS Post_Author_ID
FROM discussions_Topics INNER JOIN
discussions_Posts ON discussions_Posts.post_id = discussions_Topics.topic_last_post_id INNER JOIN
aspnet_Users AS user_1 ON user_1.UserId = discussions_Topics.topic_poster INNER JOIN
aspnet_Users AS user_2 ON user_2.UserId = discussions_Posts.poster_id
WHERE (discussions_Topics.board_id = @.board_id)

Dumb noob mistake.. lol... im still learning...

Accessing web.config from stored procedure

I want to access a key from appSettings section of web.config.

I have the number of days allowed for a user to activate his/her account as a key in appSettings.

I have a maintenance procedure to delete all accounts that are not activated before that many days.

In this context, i have to access web.config from stored procedure. The procedure will be scheduled as a JOB in sql server.

Thanks.

Hi,

Are you having problem to access the appSettings secion in the web.config file? If so, you can use ConfigurationManager.AppSettings property to achieve that.

Here is a link for your reference.

http://msdn2.microsoft.com/en-us/library/system.configuration.configurationmanager.appsettings.aspx

If your stored procedure, that is running as a job, is trying to access the value, you might need to save the value to a database table or somewhere. Then your stored procedure can get that. It cannot get the web.config value directly.

|||

Thanks Kevin.

My requirement is to access appSettings from a stored procedure of SQL Server. So that whenever I change web.config, the procedure must

automatically access the new value.

Is there any way to access any XML file from a stored procedure. May be that could solve my problem. I heard about XML support in SQL Server. What does that do?

Thanks again.

Srikanth.

|||

Hi Srikanth,

Teh SQL Server 2005 support for Xml is for Xml column type and Xml manipulation. It stays in the database level, but not for reading an external file.

A traditional stored procedure does not read from a file. In this case, I think you have 2 options.

1. Make your app write that appSetting to a certain place in the database timely. The stored procedure can get that as a parameter.

2. Write a CLR stored procedure. Since SQL Server 2005 supports running .NET code, you can write a method and put it in assembly. Each time, you can have the assembly read from certain file, parse the xml and get the setting value.

There are many articles talking about how to create a CLR stored procedure. Here are some of them.

http://msdn2.microsoft.com/en-us/library/ms131094.aspx
http://msdn2.microsoft.com/en-us/library/5czye81z(VS.80).aspx

Accessing URL

Hi!

I have two assets: a URL that points to an XML file, and a stored procedure that can accept this file as a text variable and store it in a SQL 2005 table.

create procedure [dbo].[insertObjects]

@.availabilityXml text

as

DECLARE @.xmlHndAdd INT

EXEC sp_xml_prepareDocument @.xmlHndAdd OUTPUT, @.availabilityXml

TRUNCATE TABLE Objects

INSERTObjects

SELECT *

FROM OPENXML(@.xmlHndAdd, '//NewDataSet/Table1', 2)
WITH Objects

Now, I need to find a solution to combine the URL with the proc.Does anyone have any suggestions on how I can pass my URL as a text variable to the procedure?SSIS, vb-script, etc. are welcome!

Thank you!

I believe these are your choices:

a) Store the data into a table through the URL and read it, this approach is mentioned here ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/dff99404-a002-48ee-910e-f37f013d946d.htm (Bulk Importing and Exporting XML Documents) in the SQL Server BOL.

b) Use OPENXML for loading data into a variable. This approach is given here: http://www.perfectxml.com/articles/xml/importxmlsql.asp#openxml

Hope this helps.

Thanks

Waseem

|||Thanks for the reply! I used the a approach, and it's working!|||Which approach did you use?|||OPENXML (b)

Accessing URL

Hi!

I have two assets: a URL that points to an XML file, and a stored procedure that can accept this file as a text variable and store it in a SQL 2005 table.

create procedure [dbo].[insertObjects]

@.availabilityXml text

as

DECLARE @.xmlHndAdd INT

EXEC sp_xml_prepareDocument @.xmlHndAdd OUTPUT, @.availabilityXml

TRUNCATE TABLE Objects

INSERTObjects

SELECT *

FROM OPENXML(@.xmlHndAdd, '//NewDataSet/Table1', 2)
WITH Objects

Now, I need to find a solution to combine the URL with the proc.Does anyone have any suggestions on how I can pass my URL as a text variable to the procedure?SSIS, vb-script, etc. are welcome!

Thank you!

I believe these are your choices:

a) Store the data into a table through the URL and read it, this approach is mentioned here ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/dff99404-a002-48ee-910e-f37f013d946d.htm (Bulk Importing and Exporting XML Documents) in the SQL Server BOL.

b) Use OPENXML for loading data into a variable. This approach is given here: http://www.perfectxml.com/articles/xml/importxmlsql.asp#openxml

Hope this helps.

Thanks

Waseem

|||Thanks for the reply! I used the a approach, and it's working!|||Which approach did you use?|||OPENXML (b)

Accessing URL

Hi!

I have two assets: a URL that points to an XML file, and a stored procedure that can accept this file as a text variable and store it in a SQL 2005 table.

create procedure [dbo].[insertObjects]

@.availabilityXml text

as

DECLARE @.xmlHndAdd INT

EXEC sp_xml_prepareDocument @.xmlHndAdd OUTPUT, @.availabilityXml

TRUNCATE TABLE Objects

INSERTObjects

SELECT *

FROM OPENXML(@.xmlHndAdd, '//NewDataSet/Table1', 2)
WITH Objects

Now, I need to find a solution to combine the URL with the proc.Does anyone have any suggestions on how I can pass my URL as a text variable to the procedure?SSIS, vb-script, etc. are welcome!

Thank you!

I believe these are your choices:

a) Store the data into a table through the URL and read it, this approach is mentioned here ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/dff99404-a002-48ee-910e-f37f013d946d.htm (Bulk Importing and Exporting XML Documents) in the SQL Server BOL.

b) Use OPENXML for loading data into a variable. This approach is given here: http://www.perfectxml.com/articles/xml/importxmlsql.asp#openxml

Hope this helps.

Thanks

Waseem

|||Thanks for the reply! I used the a approach, and it's working!|||Which approach did you use?|||OPENXML (b)

accessing two databases from a single Stored Procedure

Is it possible to access another database from a single stored procedure of another database. If it is possible, please show how.

If both the DBs are on single Server and the current user have access permission on both database then you can use the following query..

Select SomeColumns From CurrentDBName..TableName
Select SomeColumns From OtherDBName..TableName

If the other database on different database Server then you have to use the Linked Server..

EXEC sp_addlinkedserver @.server = 'SERVER', @.provider = 'SQLOLEDB.1', @.srvproduct = '', @.provstr = 'Privider=SQLOLEDB.1;Data Source=TargetServer;Initial Catalog=Database'

go
Exec sp_addlinkedsrvlogin @.rmtsrvname = 'SERVER', @.useself = true, @.locallogin = null, @.rmtuser = 'Userid', @.rmtpassword = 'Password'

On your SP you can use..

Select * From OpenQuery(MyRemoteServer, 'Select * From Sysobjects')

--OR

Select * From MyRemoteServer.DatabaseName.dbo.Sysobjects

|||

If the databases are on the same server just qualify the tables (in the database) with the database name

i.e.

Assume your stored procedure is in a database called MyDB. In your stored procedure, you can use the follwoing to access tables on from two different databases.

Select col1 from pubs.dbo.authors t1
Inner join adventurewords.dbo.sales t2 on t1.id = t2.id

If the database is on another server, you can create a linked server (and assuming security is OK).

Assume stored procedure is on a database called MyDB on server named Server1 and table you are trying to access is on Server2

Select * from [Server2].pubs.dbo.authors

|||

You can validate the Linked Server already present or not using..

select srvname From Master..sysservers

|||create procedure Proc_name as
begin
select * from database1..Tablename left join
select * from database2..Tablename
end

Accessing stored procedure with another role?

Hi NG,
I have the problem that I need to give certain users the right to add and
remove other users from roles. I have a stored procedure that looks
simplified like this
CREATE PROCEDURE UpdateUserRoles
(
@.UserName varchar(255),
@.RoleName varchar(255),
)
AS
EXEC sp_addrolemember @.RoleName, @.UserName
GO
This works for me fine because I have the db_owner role, but if you don't
have the role you are not allowed to execute the system sp
"sp_addrolemember". And I need that some users of the programm can execute
this procedure, but I can grant them all rights there are, they habe to be
in the db_owner role and I don't want to do that for obvious reasons.
My question, is it possible, that everybody who ist allowed to use this
procedure is allowed to use it as db_owner? I don't want them to be in the
db_owner role, they should just have the right for this one procedure...
I tried:
GRANT Execute ON UpdateUserRoles to User as db_owner
but I got
Grantor does not have GRANT permission...
Any help is appreciated...Christian
I've got
Server: Msg 15247, Level 16, State 1, Procedure sp_addrolemember, Line 49
User does not have permission to perform this action.
I'm affraid you cannot. However , please take a look at an Application Role
that you can activate on the connection to the database
"Christian" <uce@.cash4banners.de> wrote in message
news:u6gw5jLnFHA.3656@.TK2MSFTNGP09.phx.gbl...
> Hi NG,
> I have the problem that I need to give certain users the right to add and
> remove other users from roles. I have a stored procedure that looks
> simplified like this
> CREATE PROCEDURE UpdateUserRoles
> (
> @.UserName varchar(255),
> @.RoleName varchar(255),
> )
> AS
> EXEC sp_addrolemember @.RoleName, @.UserName
> GO
> This works for me fine because I have the db_owner role, but if you don't
> have the role you are not allowed to execute the system sp
> "sp_addrolemember". And I need that some users of the programm can execute
> this procedure, but I can grant them all rights there are, they habe to be
> in the db_owner role and I don't want to do that for obvious reasons.
> My question, is it possible, that everybody who ist allowed to use this
> procedure is allowed to use it as db_owner? I don't want them to be in the
> db_owner role, they should just have the right for this one procedure...
> I tried:
> GRANT Execute ON UpdateUserRoles to User as db_owner
> but I got
> Grantor does not have GRANT permission...
> Any help is appreciated...
>|||Thanks Uri.
Changing shortly the connection to another user worked for me. Thanks
again...
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:uZ2KtuLnFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Christian
> I've got
> Server: Msg 15247, Level 16, State 1, Procedure sp_addrolemember, Line 49
> User does not have permission to perform this action.
>
> I'm affraid you cannot. However , please take a look at an Application
> Role that you can activate on the connection to the database
>
>
> "Christian" <uce@.cash4banners.de> wrote in message
> news:u6gw5jLnFHA.3656@.TK2MSFTNGP09.phx.gbl...
>

Accessing Stored Procedure parameters through XSD

Hi All,

I have created a stored procedure (in SQL Server 2005 - Developer) with input and output parameters. Please somebody let me know how I can call this store procedure from code behind using TableAdapter i.e. through XSD.

Thanks,

Long Live Microsoft ;)

You can start from here:Working with a Typed DataSet, and this tutorial should help:Working with Data in ASP.NET 2.0 :: Sorting Custom Paged Data. However if you just want to call the stored procedure from .net application, it should be?much easier to use SqlCommand/SqlDataAdapter.|||

Thanks for the response,

I have created a store procedure

CREATEPROCEDURE [GetUserDetails]

(

@.paramUserID INT ,

@.paramName VARCHAR(50) OUTPUT,

@.paramEmail VARCHAR(150) OUTPUT,

@.paramDOB DATETIME OUTPUT

…

…

--here more parameters will come

)

AS

BEGIN

SELECT

@.paramName = [Name]

,@.paramEmail =

,@.paramDOB = [DOB]

FROM

[Users]

WHERE

[UserID] = @.paramUserID

--more sql script will be here for additional parameters

END

I know we can easily call the strored procedure by using sqlcommand and adding sqlparameters etc.

But I have create the Dataset(xsd) in my application and I need to channel all my data operations through this.

I'v create a tableadapter for 'user' table and I need to add a query using existing stored proc 'GetUserDetails'. This procedure will expand cause I need to add another functionalities to it.

Please could you explaing how can I do this.

|||Well it's not a short explanation,?and?I?think?it's?better?to?follow?some?tutorial:

Working with Data in ASP.NET 2.0 :: Creating a Data Access Layer|||

Thanks Iori_Jay

Finally Igot the solution, and its working.

Myproblem was:

Executing stored procedure with inputand output parameters through XSD.

Stored Procedure

Posted earlier

User.xsd

TableAdapter: UserDetailsTableAdapter

Query: GetUserDetails(@.paramUserID,@.paramName, @.paramEmail, @.paramDOB)

Code to access outputparameter values

UsersTableAdapters.UserDetailsTableAdapterssobjGetUser =new UsersTableAdapters.UserDetailsTableAdapter();

String strName ="",

strEmail ="";

DateTime? dtDOB =DateTime.Now;

objGetUser.GetUserDetails(3/*userid*/, ref strName,refstrEmail,ref dtDOB);

Response.Write("<h3>UserDetails</h3>");

Response.Write("<strong>Name:</strong> " +strName);

Response.Write("<br/> <strong>Email:</strong>" + strEmail);

Response.Write("<br/><strong>DOB:</strong> " + dtDOB.ToString());

Although "System.String" and "string" are reference typesit showed error when "string" is used, so I used "String" instead of "string".

Added "?" to avail the reference property of value type "Datetime".

Accessing Stored procedure in ASP.Net - What is wrong?

I have the following stored procedure

drop procedure ce_selectCity;

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
create PROCEDURE ce_selectCity
@.recordCount int output
-- Add the parameters for the stored procedure here

--<@.Param2, sysname, @.p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2,, 0>
AS

declare @.errNo int

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

select ciId,name from ce_city order by name

select @.recordCount = @.@.ROWCOUNT
select @.errNo = @.@.ERROR

if @.errNo <> 0 GOTO HANDLE_ERROR

return @.errNo
HANDLE_ERROR:
Rollback transaction
return @.errNo

Go

and i was just testing it like

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
db.connect()

Dim reader As SqlDataReader

Dim sqlCommand As New SqlCommand("ce_selectCity", db.getConnection)

Dim recordCountParam As New SqlParameter("@.recordCount", SqlDbType.Int)
Dim errNoParam As New SqlParameter("@.errNo", SqlDbType.Int)

recordCountParam.Direction = ParameterDirection.Output
errNoParam.Direction = ParameterDirection.ReturnValue

sqlCommand.Parameters.Add(recordCountParam)
sqlCommand.Parameters.Add(errNoParam)

reader = db.runStoredProcedureGetReader(sqlCommand)

If (db.isError = False And reader.HasRows) Then
Response.Write("Total::" & Convert.ToInt32(recordCountParam.Value) & "<br />")
While (reader.Read())
Response.Write(reader("ciId") & "::" & reader("name") & "<br />")
End While

End If
db.close()
End Sub

It returns ALL ROWS (5 in the table right now). So,recordCount should be 5. (When i run it inside SQL Server (directly) it does return 5, so i know its working there).

BUT, its returning 0.

What am i doing wrong??

EDIT:
Oh, and this is the function i use to execute stored procedure and get the reader

Public Function runStoredProcedureGetReader(ByRef sqlCommand As SqlCommand) As SqlDataReader
sqlCommand.CommandType = CommandType.StoredProcedure
Return sqlCommand.ExecuteReader
End Function
I dont know why, but its not showing the code properly.
i tried to put [ code ] [ /code ] aroung the code as in other forums, but i think this one works differently.

Can any Mod please change it? i couldn't find how to edit my post.|||

drop procedure ce_selectCity;

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
create PROCEDURE ce_selectCity
@.recordCount int output
-- Add the parameters for the stored procedure here

--<@.Param2, sysname, @.p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2,, 0>
AS

declare @.errNo int

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;

-- Insert statements for procedure here

select ciId,name from ce_city order by name

select @.recordCount = @.@.ROWCOUNT
select @.errNo = @.@.ERROR

if @.errNo <> 0 GOTO HANDLE_ERROR

return @.errNo
HANDLE_ERROR:
Rollback transaction
return @.errNo

Go

and i was just testing it like

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
db.connect()

Dim reader As SqlDataReader

Dim sqlCommand As New SqlCommand("ce_selectCity", db.getConnection)

Dim recordCountParam As New SqlParameter("@.recordCount", SqlDbType.Int)
Dim errNoParam As New SqlParameter("@.errNo", SqlDbType.Int)

recordCountParam.Direction = ParameterDirection.Output
errNoParam.Direction = ParameterDirection.ReturnValue

sqlCommand.Parameters.Add(recordCountParam)
sqlCommand.Parameters.Add(errNoParam)

reader = db.runStoredProcedureGetReader(sqlCommand)

If (db.isError = False And reader.HasRows) Then
Response.Write("Total::" & Convert.ToInt32(recordCountParam.Value) & "<br />")
While (reader.Read())
Response.Write(reader("ciId") & "::" & reader("name") & "<br />")
End While

End If
db.close()
End Sub

It returns ALL ROWS (5 in the table right now). So, recordCount should be 5. (When i run it inside SQL Server (directly) it does return 5, so i know its working there).

BUT, its returning 0.

What am i doing wrong??

EDIT:
Oh, and this is the function i use to execute stored procedure and get the reader

Public Function runStoredProcedureGetReader(ByRef sqlCommand As SqlCommand) As SqlDataReader
sqlCommand.CommandType = CommandType.StoredProcedure
Return sqlCommand.ExecuteReader
End Function

|||

I don't do that personally, however, this has been answered a few times on this forum already, so I'll repeat it for you:

Output parameters are not available until you have read all the records if you use .ExecuteReader.

|||Damn and double damn for me

and
thanks and double thanks to you.

Sorry, i should've checked before, but was frustrated as i've been working on this one for 2 hours.|||

Besides what Motley said here's more info:

@.@. functions need to follow the statements for which they intend to be used against. In your code @.recordcount will have the correct value.But @.@.ERROR wil have the error information for the SELECT @.recordcount = @.@.Rowcount statement rather than the original SELECT you intended to. If you change the order it will be the other way. Your @.@.ERROR will have correct information but @.@.Rowcount will show only 1 record since it gets you the information from the SELECT @.err?No = @.@.ERROR statement.

Hope I was clear. Now to get both info, you can use COUNT(*) in your SELECT statement and follow it up with SELECT @.@.ERROR.

As for your .NET code its all messed up. You are initializing all your command objects and connection info and calling a function and not passing any of that info. You are better off putting everything in one event. Declare and initialize all the variables either in the page_load or in the function and just get the result.

Finally, you would add the parameters as :

sqlCommand.Parameters.Add("@.recordCountParam")
sqlCommand.Parameters.Add("@.errNoParam")

Accessing Stored Procedure from IIS

I have a stored procedure that is supposed to
1. Increment a counter in Table A via a transaction
2. Use this value as the primary key to add in an address to customers
Table B
(Referenced as a "DECLARE @.CustomerID INT" just after the AS
clause)
3. Return the primary key.
This works perfectly when being called from Query Analyzer supplying values
in an EXEC line, however, accessing it from .ASP (IIS 5.0 on Win2K), the
execution falls right through without adding the customer or incrementing
the counter or giving an error. All conditional routines are executed, but
no work is being done.
Is there anything I can do to raise some sort of error to let me know what
is or isn't happening?
adovbs.inc is linked and the "conditional code" I refer to swaps the stored
procedure name (for add/edit) to add in one more parameter needed for
editing records. The parameters are referenced in exactly the same order as
they are in the procedures, with the return value being mentioned first.
The append parameters lines have been rewritten in short form, long form,
and in a "with" block as shown.
for example:
(Blocked within conditional code)
adocmd.CommandType = adCmdStoredProc
adocmd.CommandText = "spr_AddCustomer"
adocmd.ActiveConnection = conn.ConnectionObject
set param = adocmd.createparameter("@.RETURN_VALUE", adInteger,
adParamReturnValue, 0)
adocmd.parameters.append param
(Conditional code end)
With adocmd
set param = .createparameter("@.Company", adVarChar, adParamInput, 40,
company)
.parameters.append param
set param = .createparameter("@.FirstName", adVarChar, adParamInput, 15,
firstname)
.parameters.append param
set param = .createparameter("@.MiddleInitial", adVarChar, adParamInput,
1, middleinitial)
.parameters.append param
set param = .createparameter("@.LastName", adVarChar, adParamInput, 20,
lastname)
.parameters.append param
... (continuing to add parameters in the same order as SP)
.execute lngRecs,,adexecutenorecords
CustomerId = .Parameters("@.RETURN_VALUE").Value
End WithMake sure you disable "on error resume next" in your ASP page.
Make sure the stored procedure has SET NOCOUNT ON at the beginning.
Have a look at http://www.aspfaq.com/2201
http://www.aspfaq.com/
(Reverse address to reply.)
"stjulian" <anonymous@.discussions.microsoft.com> wrote in message
news:#l$GjY2GFHA.2736@.TK2MSFTNGP09.phx.gbl...
> I have a stored procedure that is supposed to
> 1. Increment a counter in Table A via a transaction
> 2. Use this value as the primary key to add in an address to customers
> Table B
> (Referenced as a "DECLARE @.CustomerID INT" just after the AS
> clause)
> 3. Return the primary key.
> This works perfectly when being called from Query Analyzer supplying
values
> in an EXEC line, however, accessing it from .ASP (IIS 5.0 on Win2K), the
> execution falls right through without adding the customer or incrementing
> the counter or giving an error. All conditional routines are executed, but
> no work is being done.
> Is there anything I can do to raise some sort of error to let me know what
> is or isn't happening?
> adovbs.inc is linked and the "conditional code" I refer to swaps the
stored
> procedure name (for add/edit) to add in one more parameter needed for
> editing records. The parameters are referenced in exactly the same order
as
> they are in the procedures, with the return value being mentioned first.
> The append parameters lines have been rewritten in short form, long form,
> and in a "with" block as shown.
> for example:
> (Blocked within conditional code)
> adocmd.CommandType = adCmdStoredProc
> adocmd.CommandText = "spr_AddCustomer"
> adocmd.ActiveConnection = conn.ConnectionObject
> set param = adocmd.createparameter("@.RETURN_VALUE", adInteger,
> adParamReturnValue, 0)
> adocmd.parameters.append param
> (Conditional code end)
> With adocmd
> set param = .createparameter("@.Company", adVarChar, adParamInput, 40,
> company)
> .parameters.append param
> set param = .createparameter("@.FirstName", adVarChar, adParamInput, 15,
> firstname)
> .parameters.append param
> set param = .createparameter("@.MiddleInitial", adVarChar, adParamInput,
> 1, middleinitial)
> .parameters.append param
> set param = .createparameter("@.LastName", adVarChar, adParamInput, 20,
> lastname)
> .parameters.append param
> ... (continuing to add parameters in the same order as SP)
> .execute lngRecs,,adexecutenorecords
> CustomerId = .Parameters("@.RETURN_VALUE").Value
> End With
>|||Hi
You don't provide DDL for the procedure.
http://www.aspfaq.com/etiquette.asp?id=5006
You may want to check what is happening using profiler, and make sure that
NOCOUNT is ON.
John
"stjulian" wrote:

> I have a stored procedure that is supposed to
> 1. Increment a counter in Table A via a transaction
> 2. Use this value as the primary key to add in an address to customers
> Table B
> (Referenced as a "DECLARE @.CustomerID INT" just after the AS
> clause)
> 3. Return the primary key.
> This works perfectly when being called from Query Analyzer supplying value
s
> in an EXEC line, however, accessing it from .ASP (IIS 5.0 on Win2K), the
> execution falls right through without adding the customer or incrementing
> the counter or giving an error. All conditional routines are executed, but
> no work is being done.
> Is there anything I can do to raise some sort of error to let me know what
> is or isn't happening?
> adovbs.inc is linked and the "conditional code" I refer to swaps the store
d
> procedure name (for add/edit) to add in one more parameter needed for
> editing records. The parameters are referenced in exactly the same order a
s
> they are in the procedures, with the return value being mentioned first.
> The append parameters lines have been rewritten in short form, long form,
> and in a "with" block as shown.
> for example:
> (Blocked within conditional code)
> adocmd.CommandType = adCmdStoredProc
> adocmd.CommandText = "spr_AddCustomer"
> adocmd.ActiveConnection = conn.ConnectionObject
> set param = adocmd.createparameter("@.RETURN_VALUE", adInteger,
> adParamReturnValue, 0)
> adocmd.parameters.append param
> (Conditional code end)
> With adocmd
> set param = .createparameter("@.Company", adVarChar, adParamInput, 40,
> company)
> .parameters.append param
> set param = .createparameter("@.FirstName", adVarChar, adParamInput, 15,
> firstname)
> .parameters.append param
> set param = .createparameter("@.MiddleInitial", adVarChar, adParamInput,
> 1, middleinitial)
> .parameters.append param
> set param = .createparameter("@.LastName", adVarChar, adParamInput, 20,
> lastname)
> .parameters.append param
> ... (continuing to add parameters in the same order as SP)
> .execute lngRecs,,adexecutenorecords
> CustomerId = .Parameters("@.RETURN_VALUE").Value
> End With
>
>|||Thank you both for your attention...
DDL follows
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.spr_WriteCustomers Created: 2/24/05
JS ******/
CREATE PROCEDURE spr_WriteCustomers
@.Company varchar(40) = NULL,
@.FirstName varchar(15) = NULL,
@.MiddleInitial varchar(1) = NULL,
@.LastName varchar(20) = NULL,
@.Title varchar(30) = NULL,
@.BillingAttnLine varchar(40) = NULL,
@.BillingAddress1 varchar(40) = NULL,
@.BillingAddress2 varchar(40) = NULL,
@.BillingCity varchar(20) = NULL,
@.BillingState varchar(3) = NULL,
@.BillingZip varchar(10) = NULL,
@.FK_CountryCode varchar(3) = NULL,
@.BillingCountry varchar(25) = NULL,
@.BillingPhone varchar(25) = NULL,
@.BillingFax varchar(15) = NULL,
@.ShippingFirstName varchar(15) = NULL,
@.ShippingLastName varchar(20) = NULL,
@.ShippingCompany varchar(40) = NULL,
@.ShippingTitle varchar(40) = NULL,
@.ShippingAttnLine varchar(40) = NULL,
@.ShippingAddress1 varchar(40) = NULL,
@.ShippingAddress2 varchar(40) = NULL,
@.ShippingCity varchar(20) = NULL,
@.ShippingState varchar(3) = NULL,
@.ShippingZip varchar(10) = NULL,
@.FK_SCountryCode varchar(3) = NULL,
@.ShippingCountry varchar(25) = NULL,
@.ShippingPhone varchar(25) = NULL,
@.ShippingFax varchar(15) = NULL,
@.FK_CustomerTierID int = 0,
@.UserName varchar(45) = NULL,
@.Password varchar(20) = NULL,
@.EMail varchar(45) = NULL,
@.TaxExempt bit = 0,
@.NoEmail bit= 0,
@.GREETING1 varchar(35) = NULL,
@.GREETING2 varchar(35) = NULL,
@.BelongsTo int = 0
AS
BEGIN
SET NOCOUNT ON
DECLARE @.custid INT
DECLARE @.CREATEDATE DATETIME
-- Begin process
--Get New CustomerID
BEGIN TRAN
SELECT @.custid = nextid
FROM tblAutoNumber
WHERE TableName = 'tblCustomers'
UPDATE tblAutoNumber
SET nextid = @.custid + 1
WHERE TableName = 'tblCustomers'
COMMIT TRAN
SELECT @.CREATEDATE = getdate()
BEGIN
INSERT INTO tblCustomers
(PK_ID,
Company,
FirstName,
MiddleInitial,
LastName,
Title,
BillingAttnLine,
BillingAddress1,
BillingAddress2,
BillingCity,
BillingState,
BillingZip,
FK_CountryCode,
BillingCountry,
ShippingFirstName,
ShippingLastName,
ShippingCompany,
ShippingTitle,
ShippingAttnLine,
ShippingAddress1,
ShippingAddress2,
ShippingCity,
ShippingState,
ShippingZip,
FK_SCountryCode,
ShippingCountry,
FK_CustomerTierID,
UserName,
Password,
Email,
BillingPhone,
ShippingPhone,
BillingFax,
ShippingFax,
LeaseStatus,
LeaseCreditLimit,
FK_CurrencyId,
DisableLogin,
LastModified,
Created,
TaxExempt,
NoEmail,
GREETING1,
GREETING2,
TaxExemptVerified,
AutoCancel,
LastLogin,
BelongsTo)
VALUES(
@.custid,
@.Company,
@.FirstName,
@.MiddleInitial,
@.LastName,
@.Title,
@.BillingAttnLine,
@.BillingAddress1,
@.BillingAddress2,
@.BillingCity,
@.BillingState,
@.BillingZip,
@.FK_CountryCode,
@.BillingCountry,
@.ShippingFirstName,
@.ShippingLastName,
@.ShippingCompany,
@.ShippingTitle,
@.ShippingAttnLine,
@.ShippingAddress1,
@.ShippingAddress2,
@.ShippingCity,
@.ShippingState,
@.ShippingZip,
@.FK_SCountryCode,
@.ShippingCountry,
@.FK_CustomerTierID,
@.UserName,
@.Password,
@.Email,
@.BillingPhone,
@.ShippingPhone,
@.BillingFax,
@.ShippingFax,
'',
0,
0,
0,
@.CREATEDATE,
@.CREATEDATE,
@.TaxExempt,
@.NoEmail,
@.GREETING1,
@.GREETING2,
0,
0,
@.CREATEDATE,
@.BelongsTo)
END
RETURN @.custid
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:A7EC9405-C84D-4ABC-B9D7-34ED6A4A1BC6@.microsoft.com...
> Hi
> You don't provide DDL for the procedure.
> http://www.aspfaq.com/etiquette.asp?id=5006
> You may want to check what is happening using profiler, and make sure that
> NOCOUNT is ON.
> John
>
> "stjulian" wrote:
>|||Wait, I think I got it ... The On Error was in an include file.
Thank you all for your help.
Julian
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:A7EC9405-C84D-4ABC-B9D7-34ED6A4A1BC6@.microsoft.com...
> Hi
> You don't provide DDL for the procedure.
> http://www.aspfaq.com/etiquette.asp?id=5006
> You may want to check what is happening using profiler, and make sure that
> NOCOUNT is ON.
> John
>
> "stjulian" wrote:
>

accessing stored procedure

Hi there!
I 've defined a sp while I was logged as 'sa' into query
analyser...well, I defined this sp with a specific owner, I mean:
create proc myowner.myproc...Etc...
once defined, I can invoke this sp from query analyser, no pb..(exec
myowner.myproc...)
But, when I log into database ('sa' user) from VBscript and I try to
run my sp, it says, it can't find my sp...despite the fact I can run
it from query analyser without any troubles...
did I miss something'
thanks a lot
++
Vince
note: this sp uses bulk insert statement, so the user needs to be
either symin or bulkadmin, that's why I chose to log as 'sa', I
noticed that no need for 'myowner' to be in bulkinsert roleVince <vincent@.<remove>.> wrote in news:nkf551h5lc7dc0rllkdvrvtvslocfebkri@.
4ax.com:

> Hi there!
> I 've defined a sp while I was logged as 'sa' into query
> analyser...well, I defined this sp with a specific owner, I mean:
> create proc myowner.myproc...Etc...
> once defined, I can invoke this sp from query analyser, no pb..(exec
> myowner.myproc...)
>
> But, when I log into database ('sa' user) from VBscript and I try to
> run my sp, it says, it can't find my sp...despite the fact I can run
> it from query analyser without any troubles...
> did I miss something'
> thanks a lot
>
If the object is owned by a user account other than sa (sa would show
"dbo" as the owner), then you must preface it with the owner name
(myowner.myproc) when logged in as anyone other than the owner, including
sa.
Rumble
"Write something worth reading, or do something worth writing."
-- Benjamin Franklin|||On Tue, 05 Apr 2005 16:56:47 GMT, Rumbledor
<Rumbledor@.hotspamsuxmail.com> wrote:

>If the object is owned by a user account other than sa (sa would show
>"dbo" as the owner), then you must preface it with the owner name
>(myowner.myproc) when logged in as anyone other than the owner, including
>sa.
this is what I did in my VBscript, I logged in as 'sa' but I invoke my
sp naming it by its owner...
that's why I don't understand why it doesn't work...
I got It '
++
Vince|||Vince <vincent@.<remove>.> wrote in
news:7ih551t07mds2rt30dkl779dunangq4aj0@.
4ax.com:

> On Tue, 05 Apr 2005 16:56:47 GMT, Rumbledor
> <Rumbledor@.hotspamsuxmail.com> wrote:
>
> this is what I did in my VBscript, I logged in as 'sa' but I invoke my
> sp naming it by its owner...
> that's why I don't understand why it doesn't work...
> I got It '
It sounds like it should work, then. Perhaps if you posted the VBScript
code, the problem might be more apparent.
Rumble
"Write something worth reading, or do something worth writing."
-- Benjamin Franklin