Having Stored Procedure Problem - SQLExpress 2005

Hi.

I am writing a stored procedure for executing search
and returning results. The Sproc is a bit complicated and has
gotten away from me, now I can't understand why I am getting
bad results.

Its long and detailed so, before I dump code and get yelled at,
I am wondering if it is OK to post a large SPROC here for general
debugging help?

Thanks.

Absolutely that's ok.

It would also be helpful if you posted any ddl, sample data, and desired results as well.

|||

Yes, but ...

(And thanks for asking in advance!)

Before you do that, let me offer the following suggestions.

Often, it is best to 'build' a complicated procedure the way you build a structure, 'brick by brick'. Start out with a small part, get that working. Then add another small part, get that working.

When you are baffled, put in PRINT statements, printing the parameter and variable values, and even location in the code (for IF/WHILE switches) for verification.

And when you are ready to drop the whole thing here, please post the DDL for all associated tables, fabricate some sample data for those tables (in the form of INSERT statements), and a clear explanation of and example of desired output. See this link for help in putting it all together. The less 'set up' work we have to do, the more likely you are going to have folks tackle your problem and help you.

By going to that effort to prepare your 'presentation', the volunteers here can more easily get involved with your problem. If if is necessary to take the time to create a simulated environment just to try to help you, you will radically reduce the number of folks willing to make the time investment.

All that said, it is a great group of folks here, willing to help those willing to be helped.

|||

Concur 100% with Dale. Much better to give too much information. What will get you "yelled" at is asking a quesiton like:

My stored proc won't work. Why?

And yes, that is far too true of an example

Having problems UPDATING with a stored procedure with a variable

Hi,

I wrote this stored procedure that works, and returns what I want, but now I want to mark the "Active" field to 1 for each of the records returned by this. I have had no luck so far.

ALTER PROCEDURE [dbo].[SelectCurrent_acmdtn]

@.extractNum char(10)

AS

BEGIN

SET NOCOUNT ON;

SELECT id, efctv_from_dt, efctv_to_dt, modify_ts, extractno, Active, acmtdn_RECID

FROM (SELECT dbo.acmdtn.*, row_number() OVER (partition BY id

ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn

FROM dbo.acmdtn

WHERE extractno > @.extractNum) Rank

WHERE rn = 1

END

I have tried inserting Update between the 2 "WHERE" statements, but it returns an error

"Invalid column name 'rn'."

I have also tried opening the recordset in Access VB, but I am restricted to read-only.

I would prefer to have a stored procedure do this.

I can get it to work if I take out the parameter, but I need that part.

The purpose of this (if you care..) is I have a large amount of historical data (this is one of 42 tables) that I need to run reports on, but I need to have the data "as of a certain date (or extractno)". This is data exported from another application that I only get flat files for, that I have imported into SQL Server tables. So, by running this procedure, I get the latest "id" record as of the extractno (I get a new extract every day, with changes that were made the previous day). I want to mark these latest fields in the "Active" field so when I create reports, I can have them filter on this field.

Any help would be greatly appreciated.

Hi,

I wrote this stored procedure that works, and returns what I want, but now I want to mark the "Active" field to 1 for each of the records returned by this. I have had no luck so far.

ALTER PROCEDURE [dbo].[SelectCurrent_acmdtn]

@.extractNum char(10)

AS

BEGIN

SET NOCOUNT ON;

SELECT id, efctv_from_dt, efctv_to_dt, modify_ts, extractno, Active, acmtdn_RECID

FROM (SELECT dbo.acmdtn.*, row_number() OVER (partition BY id

ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn

FROM dbo.acmdtn

WHERE extractno > @.extractNum) Rank

WHERE rn = 1

END

I have tried inserting Update between the 2 "WHERE" statements, but it returns an error

"Invalid column name 'rn'."

I have also tried opening the recordset in Access VB , but I am restricted to read-only.

I would prefer to have a stored procedure do this.

I can get it to work if I take out the parameter, but I need that part.

The purpose of this (if you care..) is I have a large amount of historical data (this is one of 42 tables) that I need to run reports on, but I need to have the data "as of a certain date (or extractno)". This is data exported from another application that I only get flat files for, that I have imported into SQL Server tables. So, by running this procedure, I get the each latest "id" record as of the extractno (I get a new extract every day, with changes that were made the previous day). I want to mark these latest fields in the "Active" field so when I create reports, I can have them filter on this field.

Any help would be greatly appreciated.|||

Hi,

I wrote this stored procedure that works, and returns what I want, but now I want to mark the "Active" field to 1 for each of the records returned by this. I have had no luck so far. I am working in SQL server 2005.

ALTER PROCEDURE [dbo].[SelectCurrent_acmdtn]

@.extractNum char(10)

AS

BEGIN

SET NOCOUNT ON;

SELECT id, efctv_from_dt, efctv_to_dt, modify_ts, extractno, Active, acmtdn_RECID

FROM (SELECT dbo.acmdtn.*, row_number() OVER (partition BY id

ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn

FROM dbo.acmdtn

WHERE extractno > @.extractNum) Rank

WHERE rn = 1

END

I have tried inserting Update between the 2 "WHERE" statements, but it returns an error

"Invalid column name 'rn'."

I have also tried opening the recordset in Access VB , but I am restricted to read-only.

I would prefer to have a stored procedure do this.

I can get it to work if I take out the parameter, but I need that part.

The purpose of this (if you care..) is I have a large amount of historical data (this is one of 42 tables) that I need to run reports on, but I need to have the data "as of a certain date (or extractno)". This is data exported from another application that I only get flat files for, that I have imported into SQL Server tables. So, by running this procedure, I get the latest "id" record as of the extractno (I get a new extract every day, with changes that were made the previous day). I want to mark these latest fields in the "Active" field so when I create reports, I can have them filter on this field.

Any help would be greatly appreciated.|||

You are returning the results of a data manipulation - there may not be a 'match' between the resultset and the actual table.

My 'simple' recommendation is to:

capture the resultset in a @.Table variable, then use the [ID] value from that table variable to UPDATE the data, finally, returning the contents of the table variable with a SELECT.|||

Thanks for the reply. I posted this question a few too many times (was told it was deleted by administrator!).

Anyway, [ID] is not the primary key, [acmdtn_RECID] is. Any changes made to the master database simply add another record, and retain the old record, so historical queries can be run. So there can be dozens of records for each [ID]. What I want is the most recent record, for each [ID], as of a certain date.

I guess I just don't get why I can display the information, but I can't write back to the database based on that displayed information. It's a complex query, but there's no joins, or other tables involved, there can only be a 1:1 relationship between records in the table, and records in the resultset.

Having problems getting stored procedure to work.

I'm having trouble getting the following stored procedure to work. When I
call it, I get 0 rows returned. I call it like so:
EXEC RetrieveShipments 'City','Is','Omaha'
or
EXEC RetrieveShipments 'City','Contains','Omaha'
neither method returns any rows. Each field in the database is defined as
char(255).
There are numerous rows in the database where City is Omaha.
Any help is appreciated.
----
--
ALTER PROCEDURE [dbo].[RetrieveShipments]
@.ColumnName varchar(32),
@.Expr varchar(16),
@.Data varchar(64)
AS
BEGIN
SET NOCOUNT ON;
IF @.Expr = 'Is' BEGIN
SELECT *
FROM Shipped
WHERE @.ColumnName = @.Data
END
IF @.Expr = 'Contains' BEGIN
SELECT *
FROM Shipped
WHERE @.ColumnName LIKE '%'+@.Data
OR @.ColumnName LIKE '%'+@.Data+'%'
OR @.ColumnName LIKE @.Data+'%'
END
ENDYou can't pick a column name dynamically like that. Please read these
articles:
http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html
"Terry Olsen" <tolsen64@.hotmail.com> wrote in message
news:ODCTtz2RGHA.4792@.TK2MSFTNGP14.phx.gbl...
> I'm having trouble getting the following stored procedure to work. When I
> call it, I get 0 rows returned. I call it like so:
> EXEC RetrieveShipments 'City','Is','Omaha'
> or
> EXEC RetrieveShipments 'City','Contains','Omaha'
> neither method returns any rows. Each field in the database is defined as
> char(255).
> There are numerous rows in the database where City is Omaha.
> Any help is appreciated.
> ----
--
> ALTER PROCEDURE [dbo].[RetrieveShipments]
> @.ColumnName varchar(32),
> @.Expr varchar(16),
> @.Data varchar(64)
> AS
> BEGIN
> SET NOCOUNT ON;
> IF @.Expr = 'Is' BEGIN
> SELECT *
> FROM Shipped
> WHERE @.ColumnName = @.Data
> END
> IF @.Expr = 'Contains' BEGIN
> SELECT *
> FROM Shipped
> WHERE @.ColumnName LIKE '%'+@.Data
> OR @.ColumnName LIKE '%'+@.Data+'%'
> OR @.ColumnName LIKE @.Data+'%'
> END
> END
>
>|||Okay, after perusing through the articles (I printed them out for in-depth
reading later), I came up with this and it works. I'd like some input on
whether this is "good form" and "safe code". I'd like to see how it could
be done better if you have examples. Thanks.
ALTER PROCEDURE [dbo].[RetrieveShipments]
@.ColumnName char(255),
@.Expr char(255),
@.Data char(255)
AS
SET NOCOUNT ON;
DECLARE @.sql varchar(4000)
IF @.Expr = 'Is' BEGIN
SELECT @.sql = 'SELECT * FROM Shipped WHERE ' + @.ColumnName + ' = ''' + @.Data
+ ''''
END
IF @.Expr = 'Contains' BEGIN
SELECT @.sql = 'SELECT * FROM Shipped WHERE (' +
@.ColumnName + ' LIKE ' + '''%' + @.data + ''') OR (' +
@.ColumnName + ' LIKE ' + '''%' + @.data + '%'') OR (' +
@.ColumnName + ' LIKE ' + '''' + @.data + '%'')'
END
EXEC(@.sql)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:O1SUt42RGHA.4920@.tk2msftngp13.phx.gbl...
> You can't pick a column name dynamically like that. Please read these
> articles:
> http://www.sommarskog.se/dyn-search.html
> http://www.sommarskog.se/dynamic_sql.html|||On Tue, 14 Mar 2006 20:36:40 -0700, Terry Olsen wrote:

>Okay, after perusing through the articles (I printed them out for in-depth
>reading later), I came up with this and it works. I'd like some input on
>whether this is "good form" and "safe code".
Hi Terry,
Not at all.
Change this line
EXEC(@.sql)
to
PRINT @.sql
Then execute your procedure with these arguments:
EXEC [dbo].[RetrieveShipments]
@.ColumnName = '1 = 2; DROP TABLE Shipped; --',
@.Expr = 'Is',
@.Data = 'Irrelevant'
and imagine what would have happened if you had not changed the EXEC to
PRINT.
Other errors are the use of char instead of varchar (scroll the output
of the previous exercise to the right to see the rest of the query -
probably not what you intended either) and the unneeded use of three
LIKE expressions: % matches _ZERO_ or more characters, so you can just
use
LIKE '%Anything%'
instead of
LIKE '%Anything%' OR LIKE '%Anything' OR LIKE 'Anything%'

> I'd like to see how it could
>be done better if you have examples.
See the print-out that you've already made of:
http://www.sommarskog.se/dyn-search.html
Hugo Kornelis, SQL Server MVP|||You can do it, one of the things that you need to ensure that you do however
is give your user(s) Select permissions for the target table if your
security exposure permits you to do so...
Example based on your original...
--
alter PROCEDURE [dbo].[RetrieveShipments]
@.ColumnName varchar(32),
@.Expr varchar(16),
@.Data varchar(64)
AS
declare @.dothis nvarchar (2000)
BEGIN
SET NOCOUNT ON;
IF @.Expr = 'Is' BEGIN
select @.dothis = ' SELECT * FROM Shipped WHERE ' + @.ColumnName + ' = ' +
@.Data
END
IF @.Expr = 'Contains' BEGIN
select @.dothis = ' SELECT * FROM Shipped WHERE ' + @.ColumnName + ' LIKE %' +
@.Data + ' OR ' + @.ColumnName + ' LIKE %' + @.Data + '% OR ' + @.ColumnName + '
LIKE ' + @.Data + '%'
END
END
execute(@.dothis)
--
Alice
========================================
=================
please respond to the newsgroups so that everyone can see the answers!
========================================
=================
"Terry Olsen" <tolsen64@.hotmail.com> wrote in message
news:ODCTtz2RGHA.4792@.TK2MSFTNGP14.phx.gbl...
> I'm having trouble getting the following stored procedure to work. When I
> call it, I get 0 rows returned. I call it like so:
> EXEC RetrieveShipments 'City','Is','Omaha'
> or
> EXEC RetrieveShipments 'City','Contains','Omaha'
> neither method returns any rows. Each field in the database is defined as
> char(255).
> There are numerous rows in the database where City is Omaha.
> Any help is appreciated.
> ----
--
> ALTER PROCEDURE [dbo].[RetrieveShipments]
> @.ColumnName varchar(32),
> @.Expr varchar(16),
> @.Data varchar(64)
> AS
> BEGIN
> SET NOCOUNT ON;
> IF @.Expr = 'Is' BEGIN
> SELECT *
> FROM Shipped
> WHERE @.ColumnName = @.Data
> END
> IF @.Expr = 'Contains' BEGIN
> SELECT *
> FROM Shipped
> WHERE @.ColumnName LIKE '%'+@.Data
> OR @.ColumnName LIKE '%'+@.Data+'%'
> OR @.ColumnName LIKE @.Data+'%'
> END
> END
>
>

Having difficulty creating a stored procedure

I am trying to create stored procedure i Query analyzer in visual studio 2005. I am having
difficulty though. Whenever I press the execute button, here is the error message I get:

Msg 102, Level 15, State 1, Procedure MarketCreate, Line 21
Incorrect syntax near 'MarketName'.

Here is the stored procedure. Note that the very first column in named "MarketId" but I did not
include it in the stored procedure since it should be auto generated.

USE [StockWatch]
GO
/****** Object: StoredProcedure [dbo].[MarketCreate] Script Date: 08/28/2007 15:49:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[MarketCreate]

(
@.MarketCode nvarchar(20),
@.MarketName nvarchar(100),
@.LastUpdateDate nvarchar(2),
@.MarketDescription nvarchar(100)
)

AS
INSERT INTO Market
(
MarketCode
MarketName
LastUpdateDate
MarketDescription
)
VALUES
(
@.MarketCode
@.MarketName
@.LastUpdateUser
@.MarketDescription
)

You need to use comma's to separate the column names.

USE[StockWatch]

GO

/****** Object: StoredProcedure [dbo].[MarketCreate] Script Date: 08/28/2007 15:49:26 ******/

SET ANSI_NULLSON

GO

SET QUOTED_IDENTIFIERON

GO

CREATEPROCEDURE [dbo].[MarketCreate]

(

@.MarketCodenvarchar(20),

@.MarketNamenvarchar(100),

@.LastUpdateDatenvarchar(2),

@.MarketDescriptionnvarchar(100)

)

AS

INSERTINTO Market

(

MarketCode,

MarketName,

LastUpdateDate,

MarketDescription

)

VALUES

(

@.MarketCode,

@.MarketName,

@.LastUpdateUser,

@.MarketDescription

)

|||

Thanks !

HAVING Clause has no effect

I have this stored procedure. I want to run a few simple SQL functions against my tables. In particular I want to take a subset of records (One or Two years worth) and calculate AVG, VAR and STDEV.

It does not work the way I thought it would. I end up with the whole input table in #tempor1 which is about 6 years worth of records.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF

GO
ALTER PROCEDURE [dbo].[findAve1YearDailyClose_MSFT]
AS
BEGIN
SET NOCOUNT ON;
SELECT adjClosed, volume INTO #tempor1 FROM dbo.dailyCl_MSFT
GROUP BY dateTimed, adjClosed, volume
HAVING (dateTimed > DATEADD (year, -1, MAX (dateTimed)))

SELECT AVG (adjClosed) AS "AVGAdjClose1Year",
VAR (adjClosed) AS "VARAdjClose1Year", AVG (volume) AS "AVGVolume1Year",
STDEV (volume) AS "STDEVVolume1Year", COUNT (*) AS "total"
FROM #tempor1
END

Thus if I change the number of years I subtract from the latest date from 1 to 2 I end up with the same result. What is the problem?

Thanks.

What about using:

SELECT adjClosed, volume INTO #tempor1
FROM dbo.dailyCl_MSFT
WHERE dateTimed > (SELECT DATEADD(year, -1, MAX (dateTimed)) FROM dbo.dailyCl)
GROUP BY dateTimed, adjClosed, volume

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Jens K. Suessmeyer wrote:

What about using:

SELECT adjClosed, volume INTO #tempor1
FROM dbo.dailyCl_MSFT
WHERE dateTimed > (SELECT DATEADD(year, -1, MAX (dateTimed)) FROM dbo.dailyCl)
GROUP BY dateTimed, adjClosed, volume

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

It sure worked! Many thanks for a lesson. Marked as answered!

Thanks.

sql

Having a stored procedure copy tables & also preserve indexing/sch

Hello,
I created a stored procedure that renames a table to OLD_xxxxx and replaces
that table with another (copy) that resides on a different database. I pull
the
tablename names through the use of a cursor table and construct a SELECT INTO
statement as follows
'SELECT * INTO DB1.dbo.' + @.tableName + ' FROM DB2.dbo.' + @.tableName
It works great especially since there are 80+ tables that need to be copied
from one database to another. The drawback is that it doesn't preserve the
indexing/foriegn key constraints. Is there a way to do this without having to
deal with DTS or creating additional scripts? Ideally I would like to
replace
the "SELECT * INTO" statement with something that not only does a copy but
also preserves the indexing! Does such a command exist? Any help from the
Microsoft guru's would be greatly appreciated!!!!
SELECT ... INTO <tablename> doesn't create any of the PRIMARY KEY, UNIQUE,
FOREIGN KEY, CHECK, NOT NULL constraints and doesn't define DEFAULT and
IDENTITY column properties for the new table.
You will have to write seperate statements into your stored procedure to
create them.
--Vishal.
"Peter S." wrote:

> Hello,
> I created a stored procedure that renames a table to OLD_xxxxx and replaces
> that table with another (copy) that resides on a different database. I pull
> the
> tablename names through the use of a cursor table and construct a SELECT INTO
> statement as follows
> 'SELECT * INTO DB1.dbo.' + @.tableName + ' FROM DB2.dbo.' + @.tableName
> It works great especially since there are 80+ tables that need to be copied
> from one database to another. The drawback is that it doesn't preserve the
> indexing/foriegn key constraints. Is there a way to do this without having to
> deal with DTS or creating additional scripts? Ideally I would like to
> replace
> the "SELECT * INTO" statement with something that not only does a copy but
> also preserves the indexing! Does such a command exist? Any help from the
> Microsoft guru's would be greatly appreciated!!!!
>

having a constraint on a adding a record

how can i make a stored procedure for inserting a record in the database where it detect if the title is already present and then disregard insertion and just update the number of copies in that specific record?

i would be so thankful for any hel out there...tnx!

insert into yourtable(pk, col1, col2, col3)
select @.pk, @.col1, @.col2, @.col3
where not exists (select * from yourtable x where x.pk = @.pk)

if @.@.rowcount = 0 -- nothing inserted
begin
-- so do the update
update yourtable
set col1 = @.col1,
col2 = @.col2,
col3 = @.col3
where pk = @.pk
end|||

It would help to post the schema of the table or relevant columns. You can do something like below:

begin tran

if not exists( select * from titles with(updlock) where title = @.title )

insert into titles

values(....)

else

update titles

set numcopies = numcopies + 1

where title = @.title

commit

You need to add error handling and other necessary checks to the code.

|||Arguably the best way to do this would be using an isntead of INSERT trigger. In the trigger, run the if exists statement in the above posts, and if so, update, otherwise insert. That way you can rely on the code running when anything is inserted without having to update all your stored procedures to make that change.|||Sure, this is one way to do it. But you will have to watch out for performance issues. INSTEAD OF trigger requires materialization of the rows in the inserted/deleted tables. And this can be expensive depending on the number of rows being inserted. And for single row inserts the overhead of the trigger is probably unnecessary and it is easier to modify the SP that performs the insert into the base table.sql

Have Stored Procedure with input parameters and want to Use Spreadsheet

I have a stored procedure that is able to ultimately do an create a temp table and insert into another table. I'm trying to figure out how I could execute the stored procedure that would get it's input parameters from a spreadsheet rather than execute it line by line. Can anyone suggest anything?

CREATE PROCEDURE osp_xmlSvc_UpdateItemAttribute

@.PartNumber varchar(50) = '',

@.Rev varchar(50) = '',

@.AttributeName varchar(256) = '',

@.AttributeValue varchar(1024) = '',

@.EditSource varchar(255) = '',

@.UserName varchar(50) = 'admin',

@.ReturnState int = 0 Output

WITH ENCRYPTION

AS

DECLARE @.ItemID int

DECLARE @.RevID int

DECLARE @.AssignAllRevs int

DECLARE @.ParamIndexID int

DECLARE @.CurrentValue varchar(1024)

DECLARE @.UserID int

SET @.ReturnState = -1

SET @.ItemID = -1

SET @.RevID = -1

SET @.ParamIndexID = -1

SET @.CurrentValue = ''

SET @.AssignAllRevs = 0

SELECT @.UserID = ID FROM UserProfile WHERE UserName = @.UserName

SELECT @.ItemID = ID FROM Entry WHERE PartNumber = @.PartNumber

if(NOT(@.ItemID = -1) AND @.ItemID IS NOT NULL) begin

SELECT @.RevID = ID FROM Rev WHERE ItemID = @.ItemID AND Rev=@.Rev

if(@.RevID = -1 OR @.RevID IS NULL) begin

SET @.AssignAllRevs = 1

SELECT @.RevID = ID FROM Rev WHERE ItemID = @.ItemID AND Expired=0

end

SELECT @.ParamIndexID = ID FROM ParamIndex WHERE [Name]=@.AttributeName

if(NOT(@.ParamIndexID = -1) AND @.ParamIndexID IS NOT NULL) begin

SET @.ReturnState = 0

CREATE TABLE tmp_xml_AV (AttVal varchar(1024))

DECLARE @.tmpSQL nvarchar(2024)

SET @.tmpSQL = 'INSERT INTO tmp_xml_AV (AttVal) SELECT [' + @.AttributeName + '] FROM ParamValue WHERE Expired=0 AND ItemID=' + CAST(@.ItemID AS VARCHAR)+ ' AND RevID=' + CAST(@.RevID AS VARCHAR)

EXECUTE sp_executesql @.tmpSQL

SELECT @.CurrentValue = IsNull(AttVal, '') FROM tmp_xml_AV

DROP TABLE tmp_xml_AV

if(NOT(@.CurrentValue = @.AttributeValue) OR @.CurrentValue IS NULL) begin

SET @.ReturnState = 1

SET @.tmpSQL = 'UPDATE ParamValue SET [' + @.AttributeName + ']=''' + @.AttributeValue + ''' WHERE ItemID=' + CAST(@.ItemID AS VARCHAR)

if(@.AssignAllRevs = 0) begin

SET @.tmpSQL = @.tmpSQL + ' AND RevID=' + CAST(@.RevID AS VARCHAR)

end

EXECUTE sp_executesql @.tmpSQL

-- Record history

DECLARE @.tmpInt int

SELECT @.tmpInt = Max(ID)+1 FROM EntryChangeAction

INSERT INTO EntryChangeAction (ID,EntryAffected,RevID,ActionType,Details,Tool,UserID)

VALUES (@.tmpInt,@.ItemID,@.RevID,6,@.AttributeName + ': ' + @.CurrentValue + ' to: ' + @.AttributeValue,@.EditSource,@.UserID)

end

end

end

GRANT EXECUTE ON [dbo].[osp_xmlSvc_UpdateItemAttribute] TO [public]

GO

Thanks.

Amy

In the stored procedure, you could open and read the xls file and put the values into variables. (An xml file would be simpler -Excel could save the file as xml.)

Check in Books Online about [OpenXML].

Has anyone tried "Using SQL Stored procedure in MS ACCESS"?

I am developing a program using ms access with an SQL 2000 back end. I am using an odbc connection to access my data. Using this connection, i can only access the tables and views of my database. I am planning to create "Stored procedure" in SQL and connect it to my MS Access program but have no idea how to do it.

Please help! Thanks in Advance!Might I suggest an Access newsgroup as its an Access specific problem? SQL Server SPs should just be called like a SQL statement is executed, ie "exec usp_foo" instead of "select * from foo".|||if you want to view stored procedure from Access I suggest you to not use a Access Project (file .adp) instead of the classic Access database (.mdb);
in this way in the Query tab of you Access Project you will see views and stored procedure of the Sql server and then you can manage these directly by Access.
bye

Has anyone tried "Using SQL Stored procedure in MS ACCESS"?

I am developing a program using ms access with an SQL 2000 back end. I am using an odbc connection to access my data. Using this connection, i can only access the tables and views of my database. I am planning to create "Stored procedure" in SQL and connect it to my MS Access program but have no idea how to do it.

Please help! Thanks in Advance!Might I suggest an Access newsgroup as its an Access specific problem? SQL Server SPs should just be called like a SQL statement is executed, ie "exec usp_foo" instead of "select * from foo".|||if you want to view stored procedure from Access I suggest you to not use a Access Project (file .adp) instead of the classic Access database (.mdb);
in this way in the Query tab of you Access Project you will see views and stored procedure of the Sql server and then you can manage these directly by Access.
bye

Hardware design suggestion

I'm implementing a brand new project that consists in a soft-realtime application that executes scheduled operations (tcp commands) stored on a SqlServer 2005 express db. DB and app are always on the same workstation. Let's say that the workload is about 40000 events per day, with periods of activity and more idle periods. The DB is filled by external applications that provides insert/update using ad-hoc sprocs and providing only the 'preedceding event'. The major performance problem I see is given by the fact that each time an event is inserted the sproc must rebuild all the scheduled times (I've build an optimization that uses service broker as to update asynchronously the scheduled times). The application does not requires any special calculation and is very lightweight... it only fetches the db and sends tcp commands. The only 'hard work' is to guarantee the exact time for sending the commands.

Now I have to design the required hardware and I expect some tip from you on how to proceed to build a test workload and how to translate it into 'minimum hardware specification'. Consider that safety is not importand since system are redundant in other ways.
My questions are

- Should I put OS, tempdb, database and logs each on a separate disk array ? My idea is to have a two-disk RAID0 for OS and logfiles, and a n-disk RAID0 for tempdb and appdb

- How to extimate the number of disks to have in the RAID ?

- Should I also consider processor ? I have the idea that processor is not very important while I should pay attention to bus and memory bottlenecks: so I've thought to use DDR2 RAM with a FSB of 1066 MHz and a single dual or quad core cpu, so discarding XEON multiprocessor systems that requires FBDIMM, that AFAIK are slower that DDR2.

- any other suggestion ?

The process you are describing sounds like it could be very intensive. You may want to have others take a very close look at your design to make specific feedback on ways you could make this more efficient.

Regardless, it is very difficult with the information provided to give you a hardware recommendation. One thing I can recommend is you avoid the use of RAID 0 for this. With RAID 0, a single disk failure will cause you to lose the array.

Good luck,
Bryan Smith

|||

I would not use RAID 0 for anything for a project like this. If you lose any disks in a RAID 0 array, you are down.

I would put OS and page file on RAID 1, TempDB on a separate RAID 1, AppDB on separate RAID 10 or 1, and Log files on a separate RAID 10 or 1.

The Intel Tulsa MP 3.4 GHz Xeon (with 16MB of L3 cache) performs very well on SQL Server 2005. If you don't need four CPU's, I would look at the newer, Core2 Duo based CPU's, like the Xeon 5160. Make sure you have enough RAM to ensure you don't have memory pressure (which will also reduce I/O pressure). I would suggest 4GB minimum.

|||I see that both of you have suggested to avoid RAID0, and I want to clarify a point: in our system performance is more important than security on a single machine since the system is fully redundant in the sense that we have more machine that runs in parallel, each one with the same scheduling and performs the same task, plus an 'arbiter' that selects the active machine. So If I loose a disk I have another machine that is fully mirrored to the first one... a sort of RAID1 between machines.

Anyway I'm building a test environment and I expect some sort of failsafe RAID on production machines.
|||

If you have that kind of money to spend, go with RAID 10 (1+0). Even with what you have stated above, I would not recommend RAID 0.

B.

Hardware design suggestion

I'm implementing a brand new project that consists in a soft-realtime application that executes scheduled operations (tcp commands) stored on a SqlServer 2005 express db. DB and app are always on the same workstation. Let's say that the workload is about 40000 events per day, with periods of activity and more idle periods. The DB is filled by external applications that provides insert/update using ad-hoc sprocs and providing only the 'preedceding event'. The major performance problem I see is given by the fact that each time an event is inserted the sproc must rebuild all the scheduled times (I've build an optimization that uses service broker as to update asynchronously the scheduled times). The application does not requires any special calculation and is very lightweight... it only fetches the db and sends tcp commands. The only 'hard work' is to guarantee the exact time for sending the commands.

Now I have to design the required hardware and I expect some tip from you on how to proceed to build a test workload and how to translate it into 'minimum hardware specification'. Consider that safety is not importand since system are redundant in other ways.
My questions are

- Should I put OS, tempdb, database and logs each on a separate disk array ? My idea is to have a two-disk RAID0 for OS and logfiles, and a n-disk RAID0 for tempdb and appdb

- How to extimate the number of disks to have in the RAID ?

- Should I also consider processor ? I have the idea that processor is not very important while I should pay attention to bus and memory bottlenecks: so I've thought to use DDR2 RAM with a FSB of 1066 MHz and a single dual or quad core cpu, so discarding XEON multiprocessor systems that requires FBDIMM, that AFAIK are slower that DDR2.

- any other suggestion ?

The process you are describing sounds like it could be very intensive. You may want to have others take a very close look at your design to make specific feedback on ways you could make this more efficient.

Regardless, it is very difficult with the information provided to give you a hardware recommendation. One thing I can recommend is you avoid the use of RAID 0 for this. With RAID 0, a single disk failure will cause you to lose the array.

Good luck,
Bryan Smith

|||

I would not use RAID 0 for anything for a project like this. If you lose any disks in a RAID 0 array, you are down.

I would put OS and page file on RAID 1, TempDB on a separate RAID 1, AppDB on separate RAID 10 or 1, and Log files on a separate RAID 10 or 1.

The Intel Tulsa MP 3.4 GHz Xeon (with 16MB of L3 cache) performs very well on SQL Server 2005. If you don't need four CPU's, I would look at the newer, Core2 Duo based CPU's, like the Xeon 5160. Make sure you have enough RAM to ensure you don't have memory pressure (which will also reduce I/O pressure). I would suggest 4GB minimum.

|||I see that both of you have suggested to avoid RAID0, and I want to clarify a point: in our system performance is more important than security on a single machine since the system is fully redundant in the sense that we have more machine that runs in parallel, each one with the same scheduling and performs the same task, plus an 'arbiter' that selects the active machine. So If I loose a disk I have another machine that is fully mirrored to the first one... a sort of RAID1 between machines.

Anyway I'm building a test environment and I expect some sort of failsafe RAID on production machines.
|||

If you have that kind of money to spend, go with RAID 10 (1+0). Even with what you have stated above, I would not recommend RAID 0.

B.

Hard Stored Procedure?

I'm writing a stored procedure that will look for 'string' in the 'n'th
column. For instance,
exec QueryTable 1,'aString'
...should look for the string 'aString' in the first column of a specific
table. Does anyone know how to do this in a generic way, so that I don't hav
e
a big IF statement where I write the query once for each column?Try this, salt to taste (and add better exception handling):
create procedure ap_get_rows
@.table sysname,
@.field int,
@.value varchar(4000)
as
declare @.fieldname sysname
declare @.sql varchar(4000)
select @.fieldname = [name]
from syscolumns
where id = object_id(@.table) and colid = @.field
set @.sql = 'select * from [' + @.table + '] where [' + @.fieldname + '] = '''
+ @.value + ''''
print @.sql
exec (@.sql)
go
Mike|||courtesy of Steve Kass:
http://www.users.drew.edu/skass/sql...lTables.sql.txt
-oj
"Ken" <Ken@.discussions.microsoft.com> wrote in message
news:EAF72EFC-B55A-4F56-B6AF-DF48BB05177A@.microsoft.com...
> I'm writing a stored procedure that will look for 'string' in the 'n'th
> column. For instance,
> exec QueryTable 1,'aString'
> ...should look for the string 'aString' in the first column of a specific
> table. Does anyone know how to do this in a generic way, so that I don't
> have
> a big IF statement where I write the query once for each column?|||I forgot the disclaimer: "This stored procedure uses dynamic SQL which is
known to perform less efficiently than compiled SQL statements. Use with
caution especially in performance sensitive operations."
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:%23LXQyFeRFHA.4028@.tk2msftngp13.phx.gbl...
> Try this, salt to taste (and add better exception handling):
>
> create procedure ap_get_rows
> @.table sysname,
> @.field int,
> @.value varchar(4000)
> as
> declare @.fieldname sysname
> declare @.sql varchar(4000)
> select @.fieldname = [name]
> from syscolumns
> where id = object_id(@.table) and colid = @.field
> set @.sql = 'select * from [' + @.table + '] where [' + @.fieldname + '] =
'''
> + @.value + ''''
> print @.sql
> exec (@.sql)
> go
>
> Mike
>|||You have to use dynamic sql.
Example:
use northwind
go
create procedure dbo.proc1
@.ts sysname = N'dbo',
@.tn sysname,
@.ordinal_position int,
@.value varchar(50)
as
set nocount on
declare @.sql nvarchar(4000)
declare @.cn sysname
select
@.cn = column_name
from
information_schema.columns
where
table_schema = @.ts
and table_name = @.tn
and ordinal_position = @.ordinal_position
if @.cn is not null
begin
set @.sql = N'select ' + quotename(@.cn) + N' from ' + quotename(@.ts) + '.' +
quotename(@.tn) + N' where ' + quotename(@.cn) + N' like ''%' + replace(@.value
,
'''', ''') + N'%'''
print @.sql
exec sp_executesql @.sql
end
go
exec dbo.proc1 @.tn = N'customers', @.ordinal_position = 2, @.value = 'La
maison d''Asie'
go
exec dbo.proc1 @.tn = N'employees', @.ordinal_position = 4, @.value =
'Representative'
go
drop procedure proc1
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"Ken" wrote:

> I'm writing a stored procedure that will look for 'string' in the 'n'th
> column. For instance,
> exec QueryTable 1,'aString'
> ...should look for the string 'aString' in the first column of a specific
> table. Does anyone know how to do this in a generic way, so that I don't h
ave
> a big IF statement where I write the query once for each column?

hanlding null value in stored procedure

Hi,
I have a simple update query as
update tblApplication
set TotalSworn = MaleSworn + FemaleSworn,
TotalCivilian = MaleCivilian + FemaleCivilian,
GrandTotal = MaleSworn + FemaleSworn + MaleCivilian + FemaleCivilian
However, I need to build a stored procedure out of the above with the
fact that each of the fields MaleSworn, FemaleSworn, MaleCivilian and
FemeleCivilian fields can have null values.
Any help is appreciated. Thanks.Use COALESCE or ISNULL
like this
update tblApplication
set TotalSworn = Coalesce(MaleSworn,0) + Coalesce(FemaleSworn,0),
TotalCivilian = Coalesce(MaleCivilian,0) + Coalesce(FemaleCivilian,0),
GrandTotal = Coalesce(MaleSworn,0) + Coalesce(FemaleSworn,0) +
Coalesce(MaleCivilian,0) + Coalesce(FemaleCivilian,0)
http://sqlservercode.blogspot.com/
"Jack" wrote:

> Hi,
> I have a simple update query as
> update tblApplication
> set TotalSworn = MaleSworn + FemaleSworn,
> TotalCivilian = MaleCivilian + FemaleCivilian,
> GrandTotal = MaleSworn + FemaleSworn + MaleCivilian + FemaleCivilian
> However, I need to build a stored procedure out of the above with the
> fact that each of the fields MaleSworn, FemaleSworn, MaleCivilian and
> FemeleCivilian fields can have null values.
> Any help is appreciated. Thanks.
>|||Jack,
The column definition defines whether or not the column will allow NULLs.
HTH
Jerry
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:CA610AF3-57EA-4E1B-B1CC-665ABCCE6153@.microsoft.com...
> Hi,
> I have a simple update query as
> update tblApplication
> set TotalSworn = MaleSworn + FemaleSworn,
> TotalCivilian = MaleCivilian + FemaleCivilian,
> GrandTotal = MaleSworn + FemaleSworn + MaleCivilian + FemaleCivilian
> However, I need to build a stored procedure out of the above with the
> fact that each of the fields MaleSworn, FemaleSworn, MaleCivilian and
> FemeleCivilian fields can have null values.
> Any help is appreciated. Thanks.
>|||Thanks for the help to both of you. I appreciate it. Regards.
"Jerry Spivey" wrote:

> Jack,
> The column definition defines whether or not the column will allow NULLs.
> HTH
> Jerry
> "Jack" <Jack@.discussions.microsoft.com> wrote in message
> news:CA610AF3-57EA-4E1B-B1CC-665ABCCE6153@.microsoft.com...
>
>

Handling of xml data within Oracle 9/10 and sql Server 2005

Hi there,
I would like to know the best way to handle Xml data stored on Oracle and
Sql Server 2005 using XQuery/XPath and AdoNet. Ideally, the C# code should
not be different for Oracle and Sql Server. Any hint ?
Regards
Sql Server 2005/2000 has a OPENXML statement.
When I used Oracle 9 (while back), I was very disappointed in its xml
capabilities.
First question:
Are you pushing xml into the db, or reading it out?
It looks like reading it out, but wanted to make sure.
"Oriane" <oriane@.guermantes.fr> wrote in message
news:BD4ABE46-921B-4C70-92E4-F949528428AC@.microsoft.com...
> Hi there,
> I would like to know the best way to handle Xml data stored on Oracle and
> Sql Server 2005 using XQuery/XPath and AdoNet. Ideally, the C# code should
> not be different for Oracle and Sql Server. Any hint ?
> Regards
|||Hi Sloan,
"sloan" <sloan@.ipass.net> a crit dans le message de
news:uZvmoiYuHHA.536@.TK2MSFTNGP06.phx.gbl...
> Sql Server 2005/2000 has a OPENXML statement.
> When I used Oracle 9 (while back), I was very disappointed in its xml
> capabilities.
And what about Oracle 10g ?
> First question:
> Are you pushing xml into the db, or reading it out?
Reading and writing, but mostly reading...
> It looks like reading it out, but wanted to make sure.
|||Oracle 10? No idea.
Here is what I found.
I now remember how much I hated Oracle XML.
Good luck. I have no more I can offer beyond this.
/* Actual Logic of This procedure */
--this is just a check to make sure it can be cast as a XMLTYPE document
SELECT sys.xmltype.createxml(in_errorlogxml) INTO xmlvar FROM dual;
convertedBlobToXMLType := XMLTYPE(in_errorlogxml);
SELECT SEQ_ErrorLogID.NEXTVAL INTO ErrorID FROM DUAL;
--There is an issue with referring to the XML directly (as a cast clob
object)
--This is a workaround ... by putting the value into a temp database
--and then referring to that value, it will work.
delete from XMLTempHolderTable;
commit;
insert into XMLTempHolderTable values
(ErrorID,convertedBlobToXMLType);--in_errorlogxml);
commit;
INSERT INTO ERRORLOG (
ErrorID,
MachineName,
TimeStampValue,
FullName,
AppDomainName,
ThreadIdentity,
WindowsIdentity,
ExceptionType,
Message,
TargetSite,
Source,
StackTrace,
EntryDateTime
)
SELECT ErrorID,
extractValue(value(d),'ExceptionInformation/AdditionalInformationProperty/@.ExceptionManager.MachineName'),
extractValue(value(d),'ExceptionInformation/AdditionalInformationProperty/@.ExceptionManager.TimeStamp'),
extractValue(value(d),'ExceptionInformation/AdditionalInformationProperty/@.ExceptionManager.FullName'),
extractValue(value(d),'ExceptionInformation/AdditionalInformationProperty/@.ExceptionManager.AppDomainName'),
extractValue(value(d),'ExceptionInformation/AdditionalInformationProperty/@.ExceptionManager.ThreadIdentity'),
extractValue(value(d),'ExceptionInformation/AdditionalInformationProperty/@.ExceptionManager.WindowsIdentity'),
extractValue(value(d),'/ExceptionInformation/Exception/@.ExceptionType'),
extractValue(value(d),'/ExceptionInformation/Exception/@.Message'),
extractValue(value(d),'/ExceptionInformation/Exception/@.TargetSite'),
extractValue(value(d),'/ExceptionInformation/Exception/@.Source'),
extractValue(value(d),'/ExceptionInformation/Exception/StackTrace'),
sysdate
--FROM table (xmlsequence(extract(XMLTYPE.createXML(in_errorlog xml),
'/ExceptionInformation'))) d; --Does not work
--FROM XMLTempHolderTable tmp,table
(xmlsequence(extract(xmltype(tmp.XMLValue), '/ExceptionInformation')))
; --if the XMLValue is a clob
FROM XMLTempHolderTable tmp,
table (xmlsequence(extract((tmp.XMLValue),
'/ExceptionInformation'))) d--; --if the XMLValue is a XMLType
WHERE tmp.XMLID = ErrorID;
--Here's the deal. The second and third "FROM" is reading the value from
an intermediate table
--and it works
--the first FROM is trying to read the variable outright, and it fails
?
--For some reason, the code cannot refer to the cast clob (as xmltype)
directly
--but if one puts it into an intermediate table, and then read it, it
works?
COMMIT;
++++++++++++++++++++++++++=
DROP TABLE ERRORLOG
/
CREATE TABLE ERRORLOG (
/*
The below table definition maps to the information being provided by the
Microsoft.ApplicationBlocks.ExceptionManagement XMLPublisher
Here is a sample xml document.
<ExceptionInformation>
<AdditionalInformationProperty
ExceptionManager.MachineName="CelineDionXP1"
ExceptionManager.TimeStamp="11/8/2002 1:13:48 PM"
ExceptionManager.FullName="Microsoft.ApplicationBl ocks.ExceptionManagement,
Version=1.0.1769.18782, Culture=neutral, PublicKeyToken=null"
ExceptionManager.AppDomainName="ExceptionManagemen tQuickStartSamples.exe"
ExceptionManager.ThreadIdentity=""
ExceptionManager.WindowsIdentity="jean claude van damme" />
<Exception ExceptionType="System.DivideByZeroException"
Message="Attempted to divide by zero."
TargetSite="Void btnLogon_Click(System.Object, System.EventArgs)"
Source="ExceptionManagementQuickStartSamples">
<StackTrace> at
ExceptionManagementQuickStartSamples.Form1.btnLogo n_Click(Object sender,
EventArgs e) in c:\program files\microsoft application blocks for
..net\exception
management\code\cs\exceptionmanagementquickstartsa mples\form1.cs:line
171</StackTrace>
</Exception>
</ExceptionInformation>
*/
ErrorID int not null primary key ,
MachineName varchar2(128) null ,
TimeStampValue varchar2(64) null ,
FullName varchar2(128) null ,
AppDomainName varchar2(128) null ,
ThreadIdentity varchar2(128) null ,
WindowsIdentity varchar2(128) null ,
StackTrace varchar2(4000) null ,
ExceptionType varchar2(128) null ,
Message varchar2(640) not null ,
TargetSite varchar2(128) null ,
Source varchar2(128) null ,
EntryDateTime date default sysdate not null
)
/
DROP SEQUENCE SEQ_ErrorLog
/
CREATE SEQUENCE SEQ_ErrorLog
start with 1
increment by 1
nomaxvalue
/
DROP TABLE XMLTempHolderTable
/
CREATE TABLE XMLTempHolderTable (
/*
There is an issue reading a clob as an XMLTYPE directly
This is a temporary workaround, this table should never have more than 1
record in it
and is just a working table.
*/
XMLID int not null primary key ,
XMLValue XMLTYPE --CLOB
)
/
COMMIT
/
|||"sloan" <sloan@.ipass.net> a crit dans le message de
news:eadkgEeuHHA.3368@.TK2MSFTNGP02.phx.gbl...
> Oracle 10? No idea.
>
> Here is what I found.
>
> I now remember how much I hated Oracle XML.
> Good luck. I have no more I can offer beyond this.
Ok thanks

Handling of xml data within Oracle 9/10 and sql Server 2005

Hi there,
I would like to know the best way to handle Xml data stored on Oracle and
Sql Server 2005 using XQuery/XPath and AdoNet. Ideally, the C# code should
not be different for Oracle and Sql Server. Any hint ?
RegardsSql Server 2005/2000 has a OPENXML statement.
When I used Oracle 9 (while back), I was very disappointed in its xml
capabilities.
First question:
Are you pushing xml into the db, or reading it out?
It looks like reading it out, but wanted to make sure.
"Oriane" <oriane@.guermantes.fr> wrote in message
news:BD4ABE46-921B-4C70-92E4-F949528428AC@.microsoft.com...
> Hi there,
> I would like to know the best way to handle Xml data stored on Oracle and
> Sql Server 2005 using XQuery/XPath and AdoNet. Ideally, the C# code should
> not be different for Oracle and Sql Server. Any hint ?
> Regards|||Hi Sloan,
"sloan" <sloan@.ipass.net> a crit dans le message de
news:uZvmoiYuHHA.536@.TK2MSFTNGP06.phx.gbl...
> Sql Server 2005/2000 has a OPENXML statement.
> When I used Oracle 9 (while back), I was very disappointed in its xml
> capabilities.
And what about Oracle 10g ?
> First question:
> Are you pushing xml into the db, or reading it out?
Reading and writing, but mostly reading...
> It looks like reading it out, but wanted to make sure.|||Oracle 10? No idea.
Here is what I found.
I now remember how much I hated Oracle XML.
Good luck. I have no more I can offer beyond this.
/* Actual Logic of This procedure */
--this is just a check to make sure it can be cast as a XMLTYPE document
SELECT sys.xmltype.createxml(in_errorlogxml) INTO xmlvar FROM dual;
convertedBlobToXMLType := XMLTYPE(in_errorlogxml);
--
SELECT SEQ_ErrorLogID.NEXTVAL INTO ErrorID FROM DUAL;
--There is an issue with referring to the XML directly (as a cast clob
object)
--This is a workaround ... by putting the value into a temp database
--and then referring to that value, it will work.
delete from XMLTempHolderTable;
commit;
insert into XMLTempHolderTable values
(ErrorID,convertedBlobToXMLType);--in_errorlogxml);
commit;
INSERT INTO ERRORLOG (
ErrorID,
MachineName,
TimeStampValue,
FullName,
AppDomainName,
ThreadIdentity,
WindowsIdentity,
ExceptionType,
Message,
TargetSite,
Source,
StackTrace,
EntryDateTime
)
SELECT ErrorID,
extractValue(value(d),'ExceptionInformat
ion/AdditionalInformationProperty/@.E
xceptionManager.MachineName'),
extractValue(value(d),'ExceptionInformat
ion/AdditionalInformationProperty/@.E
xceptionManager.TimeStamp'),
extractValue(value(d),'ExceptionInformat
ion/AdditionalInformationProperty/@.E
xceptionManager.FullName'),
extractValue(value(d),'ExceptionInformat
ion/AdditionalInformationProperty/@.E
xceptionManager.AppDomainName'),
extractValue(value(d),'ExceptionInformat
ion/AdditionalInformationProperty/@.E
xceptionManager.ThreadIdentity'),
extractValue(value(d),'ExceptionInformat
ion/AdditionalInformationProperty/@.E
xceptionManager.WindowsIdentity'),
extractValue(value(d),'/ExceptionInformation/Exception/@.ExceptionType'),
extractValue(value(d),'/ExceptionInformation/Exception/@.Message'),
extractValue(value(d),'/ExceptionInformation/Exception/@.TargetSite'),
extractValue(value(d),'/ExceptionInformation/Exception/@.Source'),
extractValue(value(d),'/ExceptionInformation/Exception/StackTrace'),
sysdate
--FROM table (xmlsequence(extract(XMLTYPE.createXML(in_errorlogxml),
'/ExceptionInformation'))) d; --Does not work
--FROM XMLTempHolderTable tmp,table
(xmlsequence(extract(xmltype(tmp.XMLValue), '/ExceptionInformation')))
; --if the XMLValue is a clob
FROM XMLTempHolderTable tmp,
table (xmlsequence(extract((tmp.XMLValue),
'/ExceptionInformation'))) d--; --if the XMLValue is a XMLType
WHERE tmp.XMLID = ErrorID;
--Here's the deal. The second and third "FROM" is reading the value from
an intermediate table
--and it works
--the first FROM is trying to read the variable outright, and it fails
'
--For some reason, the code cannot refer to the cast clob (as xmltype)
directly
--but if one puts it into an intermediate table, and then read it, it
works'
COMMIT;

++++++++++++++++++++++++++=
DROP TABLE ERRORLOG
/
CREATE TABLE ERRORLOG (
/*
The below table definition maps to the information being provided by the
Microsoft.ApplicationBlocks.ExceptionManagement XMLPublisher
Here is a sample xml document.
<ExceptionInformation>
<AdditionalInformationProperty
ExceptionManager.MachineName="CelineDionXP1"
ExceptionManager.TimeStamp="11/8/2002 1:13:48 PM"
ExceptionManager.FullName="Microsoft.ApplicationBlocks.ExceptionManagement,
Version=1.0.1769.18782, Culture=neutral, PublicKeyToken=null"
ExceptionManager.AppDomainName="ExceptionManagementQuickStartSamples.exe"
ExceptionManager.ThreadIdentity=""
ExceptionManager.WindowsIdentity="jean claude van damme" />
<Exception ExceptionType="System.DivideByZeroException"
Message="Attempted to divide by zero."
TargetSite="Void btnLogon_Click(System.Object, System.EventArgs)"
Source="ExceptionManagementQuickStartSamples">
<StackTrace> at
ExceptionManagementQuickStartSamples.Form1.btnLogon_Click(Object sender,
EventArgs e) in c:\program files\microsoft application blocks for
.net\exception
management\code\cs\exceptionmanagementqu
ickstartsamples\form1.cs:line
171</StackTrace>
</Exception>
</ExceptionInformation>
*/
ErrorID int not null primary key ,
MachineName varchar2(128) null ,
TimeStampValue varchar2(64) null ,
FullName varchar2(128) null ,
AppDomainName varchar2(128) null ,
ThreadIdentity varchar2(128) null ,
WindowsIdentity varchar2(128) null ,
StackTrace varchar2(4000) null ,
ExceptionType varchar2(128) null ,
Message varchar2(640) not null ,
TargetSite varchar2(128) null ,
Source varchar2(128) null ,
EntryDateTime date default sysdate not null
)
/
DROP SEQUENCE SEQ_ErrorLog
/
CREATE SEQUENCE SEQ_ErrorLog
start with 1
increment by 1
nomaxvalue
/
DROP TABLE XMLTempHolderTable
/
CREATE TABLE XMLTempHolderTable (
/*
There is an issue reading a clob as an XMLTYPE directly
This is a temporary workaround, this table should never have more than 1
record in it
and is just a working table.
*/
XMLID int not null primary key ,
XMLValue XMLTYPE --CLOB
)
/
COMMIT
/|||"sloan" <sloan@.ipass.net> a crit dans le message de
news:eadkgEeuHHA.3368@.TK2MSFTNGP02.phx.gbl...
> Oracle 10? No idea.
>
> Here is what I found.
>
> I now remember how much I hated Oracle XML.
> Good luck. I have no more I can offer beyond this.
Ok thanks

Handling Events in Report Builder

Is there any way that I can handle events using a custom library in the
Report Builder? For example, I have written my stored procs to specifically
handle the "All" case of multi-select parameters without the necessity of a
huge comma-separated list. I'd like to post-process the parameters before the
report is rendered.
Any suggestions?
--
Helen Warn, PhD
Agile Software Inc.
www.agile-soft.com"Event driven" is not possible in ReportBuilder. Will check from my side,
whether any API's can be used to handle events. But I dont think this is
possible.
Amarnath
"Helen Warn" wrote:
> Is there any way that I can handle events using a custom library in the
> Report Builder? For example, I have written my stored procs to specifically
> handle the "All" case of multi-select parameters without the necessity of a
> huge comma-separated list. I'd like to post-process the parameters before the
> report is rendered.
> Any suggestions?
> --
> Helen Warn, PhD
> Agile Software Inc.
> www.agile-soft.com

Handling Dataset in Stored Proc.

Hi,

I want to create and populate a dataset from store procedure with following to querires & return the dataset as a result.

Select * from billmain where billno = 12

Select * from billdetails where billno = 12

I am currently performing this task aa a resultset. Now I want to use Dataset. Anybody can send me sample sp which returns dataset as a execution of the sp.

Nilkanth Desai

A simple select statement would do. Call the stored procedure from your ADO.NET code and store the results of this stored procedure in a DataSet object.

CREATE PROCEDURE SelectTable @.billNo int

AS

SELECT columnName FROM Table WHERE billno=@.billNo

|||

Hi,

Thanks fpr your Reply. This is working fine when I call it from my ADO.NET Code. But If I want to trf. Dataset as a return result of the said stored proc. where I will be doing multiple select in more then one table. So if I call it only once I can complete all task in a single call. In addition I want to marshal the job to server. So, In this case now tell me what should I do.I don't want to use CLR-Integrated stored procedure. In this case can How can I handle Dataset in above mentioned manner in a standard T-SQL stored procedure?

Nilkanth Desai

|||

You can return more than one SQL Server resultsets from a single stored procedure, see the example below.

Chris

CREATE PROCEDURE SelectTable @.billNo int

AS

SELECT columnName

FROM BillMain WHERE billno=@.billNo

SELECT columnName

FROM BillDetails WHERE billno=@.billNo

GO

handling columns with multiple values

I am writing a stored procedure that needs a access individual entries in a column with multiple entries delimited by a comma(yeah i know, not 1st NF) . Like this:

Key

NotANormalizedCol

1

1324, 5124, 5435,5467

2

423, 23, 5345

3

52334, 53443, 1224

4

12, 4, 1243,66

is there a function that returns a substring given a delimiter character? the only substring returning function that i found are the LEFT and RIGHT that returns fixed length substring.

I am pretty new to this, so I apologize if this is a trivial questions

Look at http://www.sommarskog.se/arrays-in-sql.html|||Hi,

I once wrote a function for that which can be found here in this post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=320221&SiteID=1

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Handle SQL errors with severity 10

how to handle sql errors with severity less then 10 in .NET

i call to stored procedure which raise error with severity 10 for example
however in c# i can't cach this error (with severiry >= 11 it going to the
catch block in my c# app), also the @.@.ERROR is equal to zero when severity
<= 10

--
Message posted via http://www.sqlmonster.comE B via SQLMonster.com (forum@.SQLMonster.com) writes:
> how to handle sql errors with severity less then 10 in .NET
> i call to stored procedure which raise error with severity 10 for example
> however in c# i can't cach this error (with severiry >= 11 it going to the
> catch block in my c# app), also the @.@.ERROR is equal to zero when severity
><= 10

You set up an InfoMessages event handler. Please see the .Net Framework
reference for details.

In SqlClient 2.0, there is a new property which permits you divert all
errors with severity <= 16, that is all user errors, to the InfoMessage
handler. This actually has a couple of advantages, particulary when
you use ExecuteReader, so there is all reason to get used to InfoMessages.

By the way, a small tidbit: you can never get a message with severity
10 from SQL Server. 10 is always changed to level 0.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||ok i understand., Thank u in andvance.

Learning learning an learning

--
Message posted via http://www.sqlmonster.com