One more solution for a pagination

Let me know what you think about the following code:
DECLARE @.MaxIdValue int
DECLARE @.MaxSortFieldValue nvarchar(50)

SELECT TOP 1 @.MaxIdValue = [id], @.MaxSortFieldValue = [SortField]
FROM (
SELECT TOP PageNumber*RowsPerPage [id], [SortField]
FROM MyTable
WHERE (FilterCondition) ORDER BY [SortField], [id]
) T
ORDER BY [SortField] DESC, [id] DESC

SELECT TOP RowsPerPage * FROM MyTable
WHERE ([SortField] >= @.MaxSortFieldValue) AND (([id] > @.MaxIdValue) OR
([SortField] <> @.MaxSortFieldValue)) AND (FilterCondition)
ORDER BY [SortField], [id]

This is a dynamic SQL and it should be easily fixable.
PageNumber, RowsPerPage, FilterCondition and SortField are going to be
the variables and will be based on the user's search
condition/criteria.
----------
Thanks for you attention."Kurzman" <max@.virtuman.com> wrote in message
news:d8f86969.0408051335.56e477a2@.posting.google.c om...
> Let me know what you think about the following code:
> DECLARE @.MaxIdValue int
> DECLARE @.MaxSortFieldValue nvarchar(50)
> SELECT TOP 1 @.MaxIdValue = [id], @.MaxSortFieldValue = [SortField]
> FROM (
> SELECT TOP PageNumber*RowsPerPage [id], [SortField]
> FROM MyTable
> WHERE (FilterCondition) ORDER BY [SortField], [id]
> ) T
> ORDER BY [SortField] DESC, [id] DESC
> SELECT TOP RowsPerPage * FROM MyTable
> WHERE ([SortField] >= @.MaxSortFieldValue) AND (([id] > @.MaxIdValue) OR
> ([SortField] <> @.MaxSortFieldValue)) AND (FilterCondition)
> ORDER BY [SortField], [id]
> This is a dynamic SQL and it should be easily fixable.
> PageNumber, RowsPerPage, FilterCondition and SortField are going to be
> the variables and will be based on the user's search
> condition/criteria.
> ----------
> Thanks for you attention.

What I think is that your syntax is wrong :-)

If you want a pagination solution, then the usual answer is "do it on the
client side" - this article may be useful:

http://www.aspfaq.com/show.asp?id=2120

Simon|||> What I think is that your syntax is wrong :-)
> If you want a pagination solution, then the usual answer is "do it on the
> client side" - this article may be useful:
> http://www.aspfaq.com/show.asp?id=2120
> Simon

The client side is't primary. The bottleneck of pagination is a server
side, especially for a big tables. From this point of view following
code will works slowly. Also it will works correct only if ArtistName
+ '~' + Title is unique.
ArtistName + '~' + Title
>= @.aname + '~' + @.title

Following code hasn't this problem:

([SortField] >= @.MaxSortFieldValue) AND (([id] > @.MaxIdValue) OR
([SortField] <> @.MaxSortFieldValue)) AND (FilterCondition)
ORDER BY [SortField], [id]
If index [SortField], [id] exist it works fast.

P.S. Thanks for a link to intresting article.sql

one line of results

I am trying to do a select statement where the results show up in one line like a,b,c,d,e...

I think I'm on the right track with the following code, but I have no idea what direction to go in. (I think my notes are not totally correct, but heck they're my notes. :-))

@.UM is getting wiped out after each into @.UM, how do I make it add to @.UM

declare @.UM varchar(3000) --declares variable
DECLARE abc CURSOR FOR --declares object for recordset
SELECT CONDCD FROM IBACOSTOCK GROUP BY CONDCD ORDER BY CONDCD
OPEN abc --stores results in recordset variable
FETCH NEXT FROM abc --grabs one line from recordset
INTO @.UM --stores line into variable
SELECT CONDCD FROM IBACOSTOCK GROUP BY CONDCD ORDER BY CONDCD
WHILE (@.@.FETCH_STATUS = 0) --as long as there are records in the recordset
begin
FETCH NEXT FROM abc --grabs next line from recordset
INTO @.UM --stores new line into variable
end
SELECT @.um
CLOSE abc --go back to while statement
DEALLOCATE abc --erase recordset
GOHow about...

DECLARE @.UM

SET @.UM = ''

SELECT @.UM = @.UM + CONDCD
FROM IBACOSTOCK
GROUP BY CONDCD ORDER BY CONDCD|||Yeah. I'm slow. I looked at my code afterwards and summed it up, and that's what I got. Thanks!

One for the advanced programmers!

I have a string column but I need to check if it contains any numeric
data ( in a WHERE Clause).
I did the following without any success
WHERE CONVERT(int,geocode) > 0
Gives an error as soon as it finds any non-numeric data.
Thanks> I have a string column but I need to check if it contains any numeric
> data ( in a WHERE Clause).
Try:
WHERE geocode LIKE '%[0-9]%'
Hope this helps.
Dan Guzman
SQL Server MVP
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1140448043.080918.304840@.g47g2000cwa.googlegroups.com...
> I have a string column but I need to check if it contains any numeric
> data ( in a WHERE Clause).
> I did the following without any success
> WHERE CONVERT(int,geocode) > 0
> Gives an error as soon as it finds any non-numeric data.
> Thanks
>|||S Chapman wrote:
> I have a string column but I need to check if it contains any numeric
> data ( in a WHERE Clause).
> I did the following without any success
> WHERE CONVERT(int,geocode) > 0
> Gives an error as soon as it finds any non-numeric data.
> Thanks
WHERE geocode NOT LIKE '%[^0-9]%'
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi
CREATE TABLE Sometable (currencyvalues VARCHAR(20) PRIMARY KEY)
INSERT INTO Sometable VALUES ('xnl 23.15')
INSERT INTO Sometable VALUES ('xnl')
INSERT INTO Sometable VALUES ('-23.15')
INSERT INTO Sometable VALUES ('muu')
SELECT * FROM Sometable WHERE currencyvalues LIKE '%[0-9]%'
--or
SELECT * FROM Sometable WHERE PATINDEX('%[0-9]%',currencyvalues)>0
Be aware that SQL Server will not probably use an index on the columnn ( if
you have one)
"S Chapman" <s_chapman47@.hotmail.co.uk> wrote in message
news:1140448043.080918.304840@.g47g2000cwa.googlegroups.com...
> I have a string column but I need to check if it contains any numeric
> data ( in a WHERE Clause).
> I did the following without any success
> WHERE CONVERT(int,geocode) > 0
> Gives an error as soon as it finds any non-numeric data.
> Thanks
>|||S wrote on 20 Feb 2006 07:07:23 -0800:

> I have a string column but I need to check if it contains any numeric
> data ( in a WHERE Clause).
> I did the following without any success
> WHERE CONVERT(int,geocode) > 0
> Gives an error as soon as it finds any non-numeric data.
Are you look for numbers within the column, or a column that is just a
number? If the latter then you can using this:
WHERE ISNUMERIC(geocode) = 1
Dan|||Daniel
It will resturns wrong result as well as ISNUMERIC function is inaccurate
http://www.aspfaq.com/show.asp?id=2390
INSERT INTO Sometable VALUES ('xnl 23.15')
INSERT INTO Sometable VALUES ('.')
SELECT * FROM Sometable WHERE ISNUMERIC(currencyvalues)=1
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:%237y6iGjNGHA.740@.TK2MSFTNGP12.phx.gbl...
>S wrote on 20 Feb 2006 07:07:23 -0800:
>
> Are you look for numbers within the column, or a column that is just a
> number? If the latter then you can using this:
> WHERE ISNUMERIC(geocode) = 1
>
> Dan
>

one column causing duplicate rows - wrong join used?

Hi,
Consider the following result set:
PNID PN_NUMBER Date1 Date2 Status PN_Na
me
========================================
========================
27 2051 08 Sep 1941 NULL Received NULL
28 2143 01 Jan 1945 NULL Accepted NULL
28 2143 01 Jan 1945 NULL Accepted R Anderson
29 2151 NULL NULL Accepted NULL
29 2151 NULL NULL Accepted W Yarwood
30 1579 17 Nov 1925 NULL Received NULL
31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
Here is the sql that returns the above:
SELECT
DISTINCT(PNP.PNID) AS 'PN_ID',
PNP.PNNumber AS 'PN_NUMBER',
CONVERT(VARCHAR(15), PNP.PNDate, 106) AS 'Date1',
CONVERT(VARCHAR(15), PNP.InspectionDate, 106) AS 'Date2',
PNP.PNStatus AS 'Status',
BB.Name AS 'PN_NAME',
FROM
tblPNProperties PNP
LEFT JOIN tblBusinessBoard BB
ON PNP.PNID = BB.PNID
My desired resultset would be to have pnids 28 and 29 to be unique,
however because I am
selecting PN_Name it causes the rows to have duplicates. How would I be
able to obtain my desired resultset? (see below) Is my join correct?
PNID PN_NUMBER Date1 Date2 Status PN_Na
me
========================================
========================
27 2051 08 Sep 1941 NULL Received NULL
28 2143 01 Jan 1945 NULL Accepted R Anderson
29 2151 NULL NULL Accepted W Yarwood
30 1579 17 Nov 1925 NULL Received NULL
31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
Any ideas?
Thanks
qhChange the LEFT JOIN to a JOIN.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Quackhandle" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1133522905.310939.208490@.g49g2000cwa.googlegroups.com...
Hi,
Consider the following result set:
PNID PN_NUMBER Date1 Date2 Status PN_Name
========================================
========================
27 2051 08 Sep 1941 NULL Received NULL
28 2143 01 Jan 1945 NULL Accepted NULL
28 2143 01 Jan 1945 NULL Accepted R Anderson
29 2151 NULL NULL Accepted NULL
29 2151 NULL NULL Accepted W Yarwood
30 1579 17 Nov 1925 NULL Received NULL
31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
Here is the sql that returns the above:
SELECT
DISTINCT(PNP.PNID) AS 'PN_ID',
PNP.PNNumber AS 'PN_NUMBER',
CONVERT(VARCHAR(15), PNP.PNDate, 106) AS 'Date1',
CONVERT(VARCHAR(15), PNP.InspectionDate, 106) AS 'Date2',
PNP.PNStatus AS 'Status',
BB.Name AS 'PN_NAME',
FROM
tblPNProperties PNP
LEFT JOIN tblBusinessBoard BB
ON PNP.PNID = BB.PNID
My desired resultset would be to have pnids 28 and 29 to be unique,
however because I am
selecting PN_Name it causes the rows to have duplicates. How would I be
able to obtain my desired resultset? (see below) Is my join correct?
PNID PN_NUMBER Date1 Date2 Status PN_Name
========================================
========================
27 2051 08 Sep 1941 NULL Received NULL
28 2143 01 Jan 1945 NULL Accepted R Anderson
29 2151 NULL NULL Accepted W Yarwood
30 1579 17 Nov 1925 NULL Received NULL
31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
Any ideas?
Thanks
qh|||Follow-up: If that doesn't fix it, could you please post your DDL for the
two tables + INSERT's of the sample data? We may have to change your query
further.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uTb$rXz9FHA.916@.TK2MSFTNGP10.phx.gbl...
Change the LEFT JOIN to a JOIN.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Quackhandle" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1133522905.310939.208490@.g49g2000cwa.googlegroups.com...
Hi,
Consider the following result set:
PNID PN_NUMBER Date1 Date2 Status PN_Name
========================================
========================
27 2051 08 Sep 1941 NULL Received NULL
28 2143 01 Jan 1945 NULL Accepted NULL
28 2143 01 Jan 1945 NULL Accepted R Anderson
29 2151 NULL NULL Accepted NULL
29 2151 NULL NULL Accepted W Yarwood
30 1579 17 Nov 1925 NULL Received NULL
31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
Here is the sql that returns the above:
SELECT
DISTINCT(PNP.PNID) AS 'PN_ID',
PNP.PNNumber AS 'PN_NUMBER',
CONVERT(VARCHAR(15), PNP.PNDate, 106) AS 'Date1',
CONVERT(VARCHAR(15), PNP.InspectionDate, 106) AS 'Date2',
PNP.PNStatus AS 'Status',
BB.Name AS 'PN_NAME',
FROM
tblPNProperties PNP
LEFT JOIN tblBusinessBoard BB
ON PNP.PNID = BB.PNID
My desired resultset would be to have pnids 28 and 29 to be unique,
however because I am
selecting PN_Name it causes the rows to have duplicates. How would I be
able to obtain my desired resultset? (see below) Is my join correct?
PNID PN_NUMBER Date1 Date2 Status PN_Name
========================================
========================
27 2051 08 Sep 1941 NULL Received NULL
28 2143 01 Jan 1945 NULL Accepted R Anderson
29 2151 NULL NULL Accepted W Yarwood
30 1579 17 Nov 1925 NULL Received NULL
31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
Any ideas?
Thanks
qh|||Hi Tom,
thanks for both replies. Unfortunately using JOIN did not work. when
I type the following
select * from tblPNProperties
where pnid = '28'
I get 1 row
select * from tblBusinessboard
where pnid = '28'
however here I get two rows
I have a hunch that the data is incorrect.
Back to the drawing board
cheers
qh|||On 2 Dec 2005 03:28:25 -0800, Quackhandle wrote:

>Hi,
>Consider the following result set:
> PNID PN_NUMBER Date1 Date2 Status PN_Na
me
> ========================================
========================
>27 2051 08 Sep 1941 NULL Received NULL
>28 2143 01 Jan 1945 NULL Accepted NULL
>28 2143 01 Jan 1945 NULL Accepted R Anderson
>29 2151 NULL NULL Accepted NULL
>29 2151 NULL NULL Accepted W Yarwood
>30 1579 17 Nov 1925 NULL Received NULL
>31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
>
>Here is the sql that returns the above:
>SELECT
> DISTINCT(PNP.PNID) AS 'PN_ID',
> PNP.PNNumber AS 'PN_NUMBER',
> CONVERT(VARCHAR(15), PNP.PNDate, 106) AS 'Date1',
> CONVERT(VARCHAR(15), PNP.InspectionDate, 106) AS 'Date2',
> PNP.PNStatus AS 'Status',
> BB.Name AS 'PN_NAME',
>FROM
> tblPNProperties PNP
> LEFT JOIN tblBusinessBoard BB
> ON PNP.PNID = BB.PNID
>My desired resultset would be to have pnids 28 and 29 to be unique,
>however because I am
>selecting PN_Name it causes the rows to have duplicates. How would I be
>able to obtain my desired resultset? (see below) Is my join correct?
>
> PNID PN_NUMBER Date1 Date2 Status PN_Na
me
> ========================================
========================
>27 2051 08 Sep 1941 NULL Received NULL
>28 2143 01 Jan 1945 NULL Accepted R Anderson
>29 2151 NULL NULL Accepted W Yarwood
>30 1579 17 Nov 1925 NULL Received NULL
>31 4133 08 Feb 2002 NULL Accepted Mrs L Smith
>
>Any ideas?
>
>Thanks
>qh
Hi qh,
Since you didn't post CREATE TABLE and INSERT statements, here's a wild
and completely untested guess:
SELECT
PNP.PNID AS 'PN_ID',
PNP.PNNumber AS 'PN_NUMBER',
CONVERT(VARCHAR(15), PNP.PNDate, 106) AS 'Date1',
CONVERT(VARCHAR(15), PNP.InspectionDate, 106) AS 'Date2',
PNP.PNStatus AS 'Status',
MAX(BB.Name) AS 'PN_NAME'
FROM
tblPNProperties PNP
LEFT JOIN tblBusinessBoard BB
ON PNP.PNID = BB.PNID
GROUP BY
PNP.PNID,
PNP.PNNumber,
PNP.PNDate,
PNP.InspectionDate,
PNP.PNStatus
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Once again, no records returned....

The following sproc returns no records in query analyzer, although it doesn't error out either. Last time adding the length to the end of the variable fixed this problem, but this time it's an integer type which doesn't accept a length. Any ideas?

------------------------------
CREATE PROCEDURE spUnitsbyUnitID
@.unitid int
AS

SELECT
E.camid, E.camname, E.cammodel, E.unitid,
D.contactid, D.Contactfname, D.Contactlname, D.Contactphone, D.Contactcell, D.Contactcompany, D.unitid,
C.videoserverid, C.videoservermac, C.videoserveruser, C.videoserverpass, C.videoservermodel, C.videoserverip, C.unitid,
B.radioid, B.radioip, B.radiomac, B.radioessid, B.radiouser, B.radiopass, B.unitid,
A.unitid, A.unitcity, A.unitname, A.unitalias, A.unitdeploydate, A.unitpickupdate, A.unitattatchedcams, A.unitenabled

FROM tbl_units as A

INNER JOIN tbl_radios as B ON A.unitid = B.unitid
INNER JOIN tbl_videoservers as C ON A.unitid = C.unitid
INNER JOIN tbl_contacts as D on A.unitid = D.unitid
INNER JOIN tbl_cameras as E on A.unitid = E.unitid

WHERE A.UnitID = @.unitID
GO
-------------------------------Are you sure that there are records in all 5 tables with the same unitid ? Using inner joins, you are limiting the result set to that scenario.

Jeff|||You are missing the OUTPUT keyword which is required for stored Procs returning none Numeric value. As per the previous post you can only use INNER JOIN if both tables are equal and the ANSI SQL OUTER JOIN limit is four because OUTER JOIN has default NULL condition but that may not work for HTTP applications because HTTP aplications are Stateless. Check out the MSDN link below.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_07_3q7n.asp Hope this helps.

Kind regards,
Gift Peddie|||Found the problem, 1 of the tables didn't have a test value entered in for the unitID. Thanks for the help!sql

On which platforms is SQL Server 2005 Compact Edition supported.

Will it be possible to run SQL Server 2005 Compact Edition on the following platforms:

Windows 2000

Windows XP (if yes then on which versions of it)

Vista x86

Vista x64

Are there OLE DB providers for SQL Server 2005 Compact Edition?

I have it running comfortably on 2k, XP and Vista 32

I also have it running comfortably in VB6, VS2003 and VS2005|||

The SqlCe will run on this :

http://www.microsoft.com/downloads/details.aspx?FamilyId=85E0C3CE-3FA1-453A-8CE9-AF6CA20946C3&displaylang=en#Requirements

And it's not OleDb Provider, but it's SqlCe Providers. It's use the OleDb in under layers.

OleDbConnection -> SqlCeConnection
OleDbCommand -> SqlCeCommand
....

sql

On what basis sql server sorts the rows

Hi,

When I execute the following statement:

Select * from table1;

On what basis, SQL server decides the sequence of these rows?

I need to fetch data from a table with very large number of rows. Because of the datasize I need to do this in chunks. I am thinking of passing row counter and fetch N rows at a time. I want to know if there is a need for sorting a table before I apply the above logic or I can rely on default sorting.

Thanks veyr much.

Regards,

Tim

hi Tim,

you always have to provide a sort (ORDER BY) as the engine can return data in no actual order at all.. data is scanned with IO operations that are not limited to a "physical matter", so, without an ORDER BY clause, they are returned in the order they are read.. if you have a multi cpu machine, different processors can get data in "whatever order" and merged in the actual results... usually the "physical order" of a clustered table (a table with a clustered index) is used, but, again, that order is not guaranteed.. if you need (as you usually do) a particular order, whatever it could be, you have to provide that "hint" to the query processor... this is even more "important" if you have to do it in chunck (where you should use the ROW_NUMBER() OVER( ORDER BY orderCol) new clause of SQL Server 2005).. this obviously makes the query "heavier", as the result must be first generated and then ordered, but gives you the "real" taste of correct data and not data found over again and again in the successive calls..

remember that the ORDER BY clause is "cursor clause" and not part of the actual query.. logically, it's the "last part" of a complete plan, where the actual query result is passed to a cursor operation to sort data as desired..

regards

|||

If you do not use an ORDER BY clause, SQL Server will produce the data in whatever order it deems efficient.

At times, that may be the order in which the data has been put in the table -but that is just a temporal fluke. There is no guarantee that you will get the data in the same order the next time you query.

To control the presentation, you MUST use an ORDER BY statement.

Here are some other ideas and help about what is often referred to as 'paging' queries:

Paging Queries
www.aspfaq.com/2120

|||

Thanks very much.

Regards,

Tim

On Delete Triggers Question

Hello,

I've a 2 tables that would store Role/RoleMember The definition for those table is the following

Table Role Definition:

Code Snippet

CREATE TABLE [dbo].[Role](

[id] [int] IDENTITY(1,1) NOT NULL,

[isAdministratorRole] [bit] NOT NULL CONSTRAINT [DF_Role_idAdminRole] DEFAULT ((0)),

[isUserRole] [bit] NOT NULL CONSTRAINT [DF_Role_isUserRole] DEFAULT ((0)),

[isSystemRole] [bit] NOT NULL CONSTRAINT [DF_Role_isSystemRole] DEFAULT ((0)),

CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Table RoleMember Definition:

Code Snippet

CREATE TABLE [dbo].[RoleMember](

[role_id] [int] NOT NULL,

[member_id] [int] NOT NULL,

CONSTRAINT [PK_RoleMember] PRIMARY KEY CLUSTERED

(

[role_id] ASC,

[member_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role] FOREIGN KEY([role_id])

REFERENCES [dbo].[Role] ([id])

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role]

GO

ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role1] FOREIGN KEY([member_id])

REFERENCES [dbo].[Role] ([id])

GO

ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role1]

The foreign key on RoleMember table points both to id Field in Role. I've been able to define ON DELETE CASCADE to one of the ForeignKey Constraint but obviously not to the other one! I've desided to trick this by setting a DELETE Triger to delete RoleMember records whose member_id match deleted Role.id. The records whose id match deleted Role.id are deleted by the foreign key constraint.

The Trigger is define as follow:

Code Snippet

CREATE TRIGGER [dbo].[OnRoleDelete]

ON [dbo].[Role]

FOR DELETE

AS

BEGIN

SET NOCOUNT ON

DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)

END

Suprisingly whenever I delete an entry from the Role table the deletion failed with the message

The DELETE statement conflicted with the REFERENCE constraint "FK_RoleMember_Role1". The conflict occurred in database "edh", table "dbo.RoleMember", column 'member_id'.

Is seems that the trigger is never called! Whats wrong with this?

Thanks for help

mavrj

You have to instead of trigger in your case...

Code Snippet

Create TRIGGER [dbo].[OnRoleDelete]

ON [dbo].[Role]

INSTEAD OF DELETE

AS

BEGIN

SET NOCOUNT ON

DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)

DELETE Role WHERE Id IN (SELECT id FROM deleted)

END

|||

Thanks for the quick answer

So what is the "FOR DELETE" for?

I assume that the "INSTEAD OF" triggers disable table trigger for the scope

|||

For Delete only executed when the delete operation is performaing or just perfomed (with out any error). In your case because of the Foreign key the delete operation is not happening.

Instead of trigger means, Instead of doing the given query operation (insert/update/delete), do the operation which is written in my trigger body. So that helped you to complete your requirement.

On Delete Triggers Question

Hello,

I've a 2 tables that would store Role/RoleMember The definition for those table is the following

Table Role Definition:

Code Snippet

CREATE TABLE [dbo].[Role](

[id] [int] IDENTITY(1,1) NOT NULL,

[isAdministratorRole] [bit] NOT NULL CONSTRAINT [DF_Role_idAdminRole] DEFAULT ((0)),

[isUserRole] [bit] NOT NULL CONSTRAINT [DF_Role_isUserRole] DEFAULT ((0)),

[isSystemRole] [bit] NOT NULL CONSTRAINT [DF_Role_isSystemRole] DEFAULT ((0)),

CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Table RoleMember Definition:

Code Snippet

CREATE TABLE [dbo].[RoleMember](

[role_id] [int] NOT NULL,

[member_id] [int] NOT NULL,

CONSTRAINT [PK_RoleMember] PRIMARY KEY CLUSTERED

(

[role_id] ASC,

[member_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role] FOREIGN KEY([role_id])

REFERENCES [dbo].[Role] ([id])

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role]

GO

ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role1] FOREIGN KEY([member_id])

REFERENCES [dbo].[Role] ([id])

GO

ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role1]

The foreign key on RoleMember table points both to id Field in Role. I've been able to define ON DELETE CASCADE to one of the ForeignKey Constraint but obviously not to the other one! I've desided to trick this by setting a DELETE Triger to delete RoleMember records whose member_id match deleted Role.id. The records whose id match deleted Role.id are deleted by the foreign key constraint.

The Trigger is define as follow:

Code Snippet

CREATE TRIGGER [dbo].[OnRoleDelete]

ON [dbo].[Role]

FOR DELETE

AS

BEGIN

SET NOCOUNT ON

DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)

END

Suprisingly whenever I delete an entry from the Role table the deletion failed with the message

The DELETE statement conflicted with the REFERENCE constraint "FK_RoleMember_Role1". The conflict occurred in database "edh", table "dbo.RoleMember", column 'member_id'.

Is seems that the trigger is never called! Whats wrong with this?

Thanks for help

mavrj

You have to instead of trigger in your case...

Code Snippet

Create TRIGGER [dbo].[OnRoleDelete]

ON [dbo].[Role]

INSTEAD OF DELETE

AS

BEGIN

SET NOCOUNT ON

DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)

DELETE Role WHERE Id IN (SELECT id FROM deleted)

END

|||

Thanks for the quick answer

So what is the "FOR DELETE" for?

I assume that the "INSTEAD OF" triggers disable table trigger for the scope

|||

For Delete only executed when the delete operation is performaing or just perfomed (with out any error). In your case because of the Foreign key the delete operation is not happening.

Instead of trigger means, Instead of doing the given query operation (insert/update/delete), do the operation which is written in my trigger body. So that helped you to complete your requirement.

On Delete Triggers Question

Hello,

I've a 2 tables that would store Role/RoleMember The definition for those table is the following

Table Role Definition:

Code Snippet

CREATE TABLE [dbo].[Role](

[id] [int] IDENTITY(1,1) NOT NULL,

[isAdministratorRole] [bit] NOT NULL CONSTRAINT [DF_Role_idAdminRole] DEFAULT ((0)),

[isUserRole] [bit] NOT NULL CONSTRAINT [DF_Role_isUserRole] DEFAULT ((0)),

[isSystemRole] [bit] NOT NULL CONSTRAINT [DF_Role_isSystemRole] DEFAULT ((0)),

CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Table RoleMember Definition:

Code Snippet

CREATE TABLE [dbo].[RoleMember](

[role_id] [int] NOT NULL,

[member_id] [int] NOT NULL,

CONSTRAINT [PK_RoleMember] PRIMARY KEY CLUSTERED

(

[role_id] ASC,

[member_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role] FOREIGN KEY([role_id])

REFERENCES [dbo].[Role] ([id])

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role]

GO

ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role1] FOREIGN KEY([member_id])

REFERENCES [dbo].[Role] ([id])

GO

ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role1]

The foreign key on RoleMember table points both to id Field in Role. I've been able to define ON DELETE CASCADE to one of the ForeignKey Constraint but obviously not to the other one! I've desided to trick this by setting a DELETE Triger to delete RoleMember records whose member_id match deleted Role.id. The records whose id match deleted Role.id are deleted by the foreign key constraint.

The Trigger is define as follow:

Code Snippet

CREATE TRIGGER [dbo].[OnRoleDelete]

ON [dbo].[Role]

FOR DELETE

AS

BEGIN

SET NOCOUNT ON

DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)

END

Suprisingly whenever I delete an entry from the Role table the deletion failed with the message

The DELETE statement conflicted with the REFERENCE constraint "FK_RoleMember_Role1". The conflict occurred in database "edh", table "dbo.RoleMember", column 'member_id'.

Is seems that the trigger is never called! Whats wrong with this?

Thanks for help

mavrj

You have to instead of trigger in your case...

Code Snippet

Create TRIGGER [dbo].[OnRoleDelete]

ON [dbo].[Role]

INSTEAD OF DELETE

AS

BEGIN

SET NOCOUNT ON

DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)

DELETE Role WHERE Id IN (SELECT id FROM deleted)

END

|||

Thanks for the quick answer

So what is the "FOR DELETE" for?

I assume that the "INSTEAD OF" triggers disable table trigger for the scope

|||

For Delete only executed when the delete operation is performaing or just perfomed (with out any error). In your case because of the Foreign key the delete operation is not happening.

Instead of trigger means, Instead of doing the given query operation (insert/update/delete), do the operation which is written in my trigger body. So that helped you to complete your requirement.

On Delete Triggers Question

Hello,

I've a 2 tables that would store Role/RoleMember The definition for those table is the following

Table Role Definition:

Code Snippet

CREATE TABLE [dbo].[Role](

[id] [int] IDENTITY(1,1) NOT NULL,

[isAdministratorRole] [bit] NOT NULL CONSTRAINT [DF_Role_idAdminRole] DEFAULT ((0)),

[isUserRole] [bit] NOT NULL CONSTRAINT [DF_Role_isUserRole] DEFAULT ((0)),

[isSystemRole] [bit] NOT NULL CONSTRAINT [DF_Role_isSystemRole] DEFAULT ((0)),

CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

Table RoleMember Definition:

Code Snippet

CREATE TABLE [dbo].[RoleMember](

[role_id] [int] NOT NULL,

[member_id] [int] NOT NULL,

CONSTRAINT [PK_RoleMember] PRIMARY KEY CLUSTERED

(

[role_id] ASC,

[member_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role] FOREIGN KEY([role_id])

REFERENCES [dbo].[Role] ([id])

ON DELETE CASCADE

GO

ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role]

GO

ALTER TABLE [dbo].[RoleMember] WITH CHECK ADD CONSTRAINT [FK_RoleMember_Role1] FOREIGN KEY([member_id])

REFERENCES [dbo].[Role] ([id])

GO

ALTER TABLE [dbo].[RoleMember] CHECK CONSTRAINT [FK_RoleMember_Role1]

The foreign key on RoleMember table points both to id Field in Role. I've been able to define ON DELETE CASCADE to one of the ForeignKey Constraint but obviously not to the other one! I've desided to trick this by setting a DELETE Triger to delete RoleMember records whose member_id match deleted Role.id. The records whose id match deleted Role.id are deleted by the foreign key constraint.

The Trigger is define as follow:

Code Snippet

CREATE TRIGGER [dbo].[OnRoleDelete]

ON [dbo].[Role]

FOR DELETE

AS

BEGIN

SET NOCOUNT ON

DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)

END

Suprisingly whenever I delete an entry from the Role table the deletion failed with the message

The DELETE statement conflicted with the REFERENCE constraint "FK_RoleMember_Role1". The conflict occurred in database "edh", table "dbo.RoleMember", column 'member_id'.

Is seems that the trigger is never called! Whats wrong with this?

Thanks for help

mavrj

You have to instead of trigger in your case...

Code Snippet

Create TRIGGER [dbo].[OnRoleDelete]

ON [dbo].[Role]

INSTEAD OF DELETE

AS

BEGIN

SET NOCOUNT ON

DELETE [RoleMember] WHERE member_id IN (SELECT id FROM deleted)

DELETE Role WHERE Id IN (SELECT id FROM deleted)

END

|||

Thanks for the quick answer

So what is the "FOR DELETE" for?

I assume that the "INSTEAD OF" triggers disable table trigger for the scope

|||

For Delete only executed when the delete operation is performaing or just perfomed (with out any error). In your case because of the Foreign key the delete operation is not happening.

Instead of trigger means, Instead of doing the given query operation (insert/update/delete), do the operation which is written in my trigger body. So that helped you to complete your requirement.

OleDBException Overflow

Im getting the following error :

System.Data.OleDb.OleDbException was unhandled

ErrorCode=-2147217833

Message="Overflow"

Source="Microsoft JET Database Engine"

StackTrace:

at

System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS

dbParams, Object& executeResult)

at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)

at

System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior,

Object& executeResult)

at

System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior

behavior, String method)

at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()

at

Home_Party_Solutions.PartyDBaseAccess.getCustomerID(Customer cust) in

C:\Documents and Settings\Andrew Buis.HAL\My Documents\Visual Studio

2005\Projects\Trunk\PartyDBaseAccess.vb:line 138

at

Home_Party_Solutions.Customer.getCustomerID(IPartyDBase& p_dbase)

in C:\Documents and Settings\Andrew Buis.HAL\My Documents\Visual Studio

2005\Projects\Trunk\Customer.vb:line 212

at

Home_Party_Solutions.PartyOrder.Done_Click(Object sender, EventArgs e)

in C:\Documents and Settings\Andrew Buis.HAL\My Documents\Visual Studio

2005\Projects\Trunk\PartyOrder.vb:line 150

at System.Windows.Forms.Control.OnClick(EventArgs e)

at System.Windows.Forms.Button.OnClick(EventArgs e)

at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)

at

System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons

button, Int32 clicks)

at System.Windows.Forms.Control.WndProc(Message& m)

at System.Windows.Forms.ButtonBase.WndProc(Message& m)

at System.Windows.Forms.Button.WndProc(Message& m)

at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)

at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)

at

System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32

msg, IntPtr wparam, IntPtr lparam)

at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)

at

System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32

dwComponentID, Int32 reason, Int32 pvLoopData)

at

System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32

reason, ApplicationContext context)

at

System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32

reason, ApplicationContext context)

at System.Windows.Forms.Application.Run(ApplicationContext context)

at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()

at

Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()

at

Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[]

commandLine)

at

Home_Party_Solutions.My.MyApplication.Main(String[] Args) in

17d14f5c-a337-4978-8281-53493378c1071.vb:line 81

Basically I am inserting a row into a table. The sql line looks like :

Insert into Customer VALUES ('1', 'Jane', 'Doe', '123 Nowhere', 'Kalamazoo', 'MI', '49024', 'a@.a.com', '3335551234')

When I copy and paste the command into Access, it successfully adds the

row into the table. However, I am getting that error when I run

it in my program. I create the string, then this is the code I am

using :

command = New OleDbCommand

command = m_Connection.CreateCommand()

command.CommandText = tempString

Dim tempInt As Integer = -1

tempInt = command.ExecuteNonQuery()

At the last line, I get the overflow.

Just for clarification, the values are (Cust ID as long, firstName as

text, lastName as text, Street as text, City as text, State as text,

Zip as long, email as text, phone as double).

Any insights into the problem? The error message isnt all that insightful.

Thanks

Hi,

first of all name the columns which have to be inserted, this would provide much more concistence accross your code and will be much easier to maintain if error occur.

Do something like the following:

INSERT INTO TableName
(
COL1,
COL2
)
VALUES
(
1,
'2'
)

Perhaps this could already solve the problem or help you to find where the problem is located.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Getting the same uninformative error when I try this.|||

You might want to try Data Access Tracing to find out what happened. Please see the link http://msdn2.microsoft.com/en-us/library/aa964124.aspx.

|||

Hi ab2034,

From the error code it seems you are getting the DB_E_DATAOVERFLOW error from the OLEDB provider. This error might be caused when "Literal value in the command exceeded the range of the type of the associated column.". So it means the provider did not like one or more of your values. I would recommend you to go ahead and try to use smaller input values and see if that works. I would start with the integral/double values first e.g. phone number. For example try putting 1 or 2 as the phone number values to see if that works and continue with other fields.

Thanks

Waseem

|||

Try
Insert into Customer VALUES (1, 'Jane', 'Doe', '123 Nowhere', 'Kalamazoo', 'MI', 49024, 'a@.a.com', 3335551234)

Rather than
Insert into Customer VALUES ('1', 'Jane', 'Doe', '123 Nowhere', 'Kalamazoo', 'MI', '49024', 'a@.a.com', '3335551234')

Or better yet, use placeholders and DbParameter to substitute values in sql string.

sql

OleDbConnection with MSDE

I'm trying to connect to a MSDE database using the following code:

 'set up connection
 dim myConnection as new OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=LearnASP_DB;Integrated Security=SSPI;Connect Timeout=30")
 'open connection
 dim myCommand as new OleDbDataAdapter("select * from tblUsers",myConnection)
 'fill dataset
 dim ds as DataSet = new DataSet()
 myCommand.Fill(ds, "tblUsers")

And I'm getting the following error:

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

Can someone tell me what I'm doing wrong?

Thanks in advanceI am pretty sure you should use (local) instead of localhost for the Data Source.

Terri|||localhost calls the webserver. (local) is the correct syntax.

should also add that if you're using "Provider=SQLOLEDB;", you might as well use System.Data.SqlClient|||you said if I'm using Provider="SQLOLEDB" I migh as well use System.Data.SqlClient

What are my other choices for Provider?
And what the difference between OleDbConnection and SqlClient?

Thanks again|||SqlClient was made specifically as the namespace to use for SqlServer. No "Provider" is needed.

http://able-consulting.com/dotnet/adonet/Data_Providers.htm#SQLClientManagedProvider|||thanks for you help :)

OLEdb Issue

I am getting the following error:
Error 2 An attempt has been made to use a data extension 'OLEDB' that is not
registered for this report server. C:\Documents and
Settings\Administrator.WILLCARE\My Documents\Visual Studio
2005\Projects\Willcare\Willcare\Report2.rdl 0 0
I am trying to design a report to use an OLEDB data source. the designer is
working, I can connect, design the report, preview. everything looks great.
but when i go to publish this, I get the error above.
Advice?found my own answer.
it appears SQL Express with RS is handicapped to SQL and LOCAL database
support only. not much use there IMHO...
"john doe" <jdoe@.doe.com> wrote in message
news:u60hYOBhGHA.3496@.TK2MSFTNGP02.phx.gbl...
>I am getting the following error:
> Error 2 An attempt has been made to use a data extension 'OLEDB' that is
> not registered for this report server. C:\Documents and
> Settings\Administrator.WILLCARE\My Documents\Visual Studio
> 2005\Projects\Willcare\Willcare\Report2.rdl 0 0
> I am trying to design a report to use an OLEDB data source. the designer
> is working, I can connect, design the report, preview. everything looks
> great. but when i go to publish this, I get the error above.
> Advice?
>

OLE-Db connection in Transformation Script Component

Hello,

Using the following documentation as a guide:

http://msdn2.microsoft.com/zh-cn/library/aa337080.aspx

I instantiated a new script component into an existing Data Flow in my SSIS project.

In the Script Transformation Editor, under the Connection Managers section, I associated the name dbConnManager to an already existing Connection Manager in the project.

My Connection Manager is of the type oOLEDB.

I then opened up the script designer and added the following lines of code where it said "Add your code here"

Dim myConnManager As IDTSConnectionManager90 = _

Me.Connections.ECFconnection

Dim dbConn As OleDb.OleDbConnection = _

CType(myConnManager.AcquireConnection(Nothing), OleDb.OleDbConnection)

When I test run the project I get the following error and the new script component is red:

Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.OleDb.OleDbConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.

I know the database connection works since I am using it in a component that executes before this new script component.

I am stuck...Any suggestions?

Unfortunately you cannot use a Connection Manager that returns a native type in managed code. This includes OLE DB and Excel.

This limitation is noted in BOL in
http://msdn2.microsoft.com/en-us/library/ms136018.aspx
and
http://msdn2.microsoft.com/en-us/library/aa337080.aspx

with this suggestion:

If you need to call the AcquireConnection method of a connection manager that returns an unmanaged object, use an ADO.NET connection manager. When you configure the ADO.NET connection manager to use an OLE DB provider, it connects by using the .NET Framework Data Provider for OLE DB. In this case, the AcquireConnection method returns a System.Data.OleDb.OleDbConnection instead of an unmanaged object. To configure an ADO.NET connection manager for use with an Excel data source, select the Microsoft OLE DB Provider for Jet, specify an Excel workbook, and then enter Excel 8.0 (for Excel 97 and later) as the value of Extended Properties on the All page of the Connection Manager dialog box.

-Doug

|||

Thank you. That was the nudge in the right direction that I needed.

Greg.

OLE-Db connection in Transformation Script Component

Hello,

Using the following documentation as a guide:

http://msdn2.microsoft.com/zh-cn/library/aa337080.aspx

I instantiated a new script component into an existing Data Flow in my SSIS project.

In the Script Transformation Editor, under the Connection Managers section, I associated the name dbConnManager to an already existing Connection Manager in the project.

My Connection Manager is of the type oOLEDB.

I then opened up the script designer and added the following lines of code where it said "Add your code here"

Dim myConnManager As IDTSConnectionManager90 = _

Me.Connections.ECFconnection

Dim dbConn As OleDb.OleDbConnection = _

CType(myConnManager.AcquireConnection(Nothing), OleDb.OleDbConnection)

When I test run the project I get the following error and the new script component is red:

Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.OleDb.OleDbConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.

I know the database connection works since I am using it in a component that executes before this new script component.

I am stuck...Any suggestions?

Unfortunately you cannot use a Connection Manager that returns a native type in managed code. This includes OLE DB and Excel.

This limitation is noted in BOL in
http://msdn2.microsoft.com/en-us/library/ms136018.aspx
and
http://msdn2.microsoft.com/en-us/library/aa337080.aspx

with this suggestion:

If you need to call the AcquireConnection method of a connection manager that returns an unmanaged object, use an ADO.NET connection manager. When you configure the ADO.NET connection manager to use an OLE DB provider, it connects by using the .NET Framework Data Provider for OLE DB. In this case, the AcquireConnection method returns a System.Data.OleDb.OleDbConnection instead of an unmanaged object. To configure an ADO.NET connection manager for use with an Excel data source, select the Microsoft OLE DB Provider for Jet, specify an Excel workbook, and then enter Excel 8.0 (for Excel 97 and later) as the value of Extended Properties on the All page of the Connection Manager dialog box.

-Doug

|||

Thank you. That was the nudge in the right direction that I needed.

Greg.

OLEDB connection error with (win 64 bit server)

Hi

Im trying to connecting from SQL 2005 (win 64bit server) to a Oracle database via OLEDB but get the following message. The OraOLEDB.Oracle.1 provider is not registered on the local machine.

If we try ODBC we get the following message "error in initializing provider. Attemt to load Oracle client libraries threw BadImageFormatException.This problem will occur when running in 64bit mode whit the 32 bit Oracle Client components installed."

It should be the 64 bit client that is installed.

In the test environment (32 bit win) both works.

Pleas help

Rickard

One of the following two should work -

1) Get a Oracle client installed that is 64-bit compliant (http://www.oracle.com/technology/tech/windows/faq.html#x86-64)

2) Compile your application as "x32" instead of "anycpu" or "x64" and it will work

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

Let me know if you require more information

Best

Sriram

OLEDB connection error with (win 64 bit server)

Hi

Im trying to connecting from SQL 2005 (win 64bit server) to a Oracle database via OLEDB but get the following message. The OraOLEDB.Oracle.1 provider is not registered on the local machine.

If we try ODBC we get the following message "error in initializing provider. Attemt to load Oracle client libraries threw BadImageFormatException.This problem will occur when running in 64bit mode whit the 32 bit Oracle Client components installed."

It should be the 64 bit client that is installed.

In the test environment (32 bit win) both works.

Pleas help

Rickard

One of the following two should work -

1) Get a Oracle client installed that is 64-bit compliant (http://www.oracle.com/technology/tech/windows/faq.html#x86-64)

2) Compile your application as "x32" instead of "anycpu" or "x64" and it will work

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

Let me know if you require more information

Best

Sriram

OLEDB command gets compile errors but works in Query analyzer.

The following statement is valid in query analyzer but will not compile as a prepared statement in an OLEDB command in DTS 2005.

delete
from
purEncumbrance_Fct
where
AgreementId = ?
and FundId = ?
and AccountId = ?
and coalesce(PODistributionId,0) = coalesce(?,0)
and coalesce(VoucherDistributionId,0) = coalesce(?,0)

Why does this statement not compile?

KenTry putting square brackets around the table and column names. I have a vague recollection of this working for me in the dim and distant past.

-Jamie|||Jamie,

I tried your suggestion with great hopes, but it did not work.

I wonder if this is a bug or a limitation with prepared statements. I know this command works in query analyzer, so maybe if I place it in a stored procedure it will work. I don't want to have to manage another piece of code, but if that is what it takes, I will.|||Not tested, but I know the statement prepare stuff and OLE-DB parameters can be rather fussy. Try loosing the coalesce(?, 0) and just use ?. Assuming that works, handle the coalesce values through a derived column, e.g.

ISNULL(Column) ? 0 : Column

OLE/DB provider returned message: Invalid authorization specification

Hello,
I'm trying to import a table from a MSDE database (databaseB) into a SQL
server database (database A).
Using to following sql statement:
insert tableA
select a.*
from openrowset(sqloledb,'Provider=sqloledb;Password=pw d;User ID=usr;Initial
Catalog=databaseA;Data Source=server', select * from [dbo].[tableB]') as a
I'm getting the following error:
[OLE/DB provider returned message: Invalid authorization specification]
[OLE/DB provider returned message: Invalid connection string attribute]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize
returned 0x80004005: ].
Everything runs fine if I use integrated security!? The used usr/pwd is a
MSDE login account.
A UDL file connection test directly to the MSDE database runs fine.
Thanx!
CHU! Eric
Hello Eric,
I have reproduced the issue on my side. Based on my research, the following
command works well:
insert into tableA
SELECT a.*
FROM OPENROWSET('SQLOLEDB','sophietest\msdeinstance';'s a';'password',
'SELECT * FROM test.dbo.tableB ') AS a
GO
or
insert into tableA
select a.*
from
openrowset('sqloledb','Provider=sqloledb;UID=sa;PW D=password;Database=test;S
erver=sophietest\msdeinstance', 'select * from [dbo].[tableB]') as a
Therefore, I recommend you perform the following commands:
1. Make sure the Authentication Mode of MSDE is mixed mode.
The following article is for your reference:
INFO: MSDE Security and Authentication
http://support.microsoft.com/default...en-us;325022#3
2. Run the following command to test:
insert into tableA
SELECT a.*
FROM OPENROWSET('SQLOLEDB','<your MSDE instance name> ';'sa';'password',
'SELECT * FROM databaseA.dbo.tableB ') AS a
GO
Or
insert into tableA
select a.*
from
openrowset('sqloledb','Provider=sqloledb;UID=sa;PW D=password;Database=databa
seA;Server=<your MSDE instance name>', 'select * from [dbo].[tableB]') as a
Note:
1. You need to replace the <your MSDE instance name> with your MSDE
instance name.
For more detailed information about OPENROWSET, please refer to the
OPENROWSET topic in SQL Books Online(BOL).
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hello Sophie,
Thank you! It works fine now
"Sophie Guo [MSFT]" <v-sguo@.online.microsoft.com> wrote in message
news:n2c8bveKFHA.2876@.TK2MSFTNGXA02.phx.gbl...
> Hello Eric,
> I have reproduced the issue on my side. Based on my research, the
> following
> command works well:
>
> insert into tableA
> SELECT a.*
> FROM OPENROWSET('SQLOLEDB','sophietest\msdeinstance';'s a';'password',
> 'SELECT * FROM test.dbo.tableB ') AS a
> GO
> or
> insert into tableA
> select a.*
> from
> openrowset('sqloledb','Provider=sqloledb;UID=sa;PW D=password;Database=test;S
> erver=sophietest\msdeinstance', 'select * from [dbo].[tableB]') as a
>
> Therefore, I recommend you perform the following commands:
> 1. Make sure the Authentication Mode of MSDE is mixed mode.
> The following article is for your reference:
> INFO: MSDE Security and Authentication
> http://support.microsoft.com/default...en-us;325022#3
>
> 2. Run the following command to test:
> insert into tableA
> SELECT a.*
> FROM OPENROWSET('SQLOLEDB','<your MSDE instance name> ';'sa';'password',
> 'SELECT * FROM databaseA.dbo.tableB ') AS a
> GO
>
> Or
>
> insert into tableA
> select a.*
> from
> openrowset('sqloledb','Provider=sqloledb;UID=sa;PW D=password;Database=databa
> seA;Server=<your MSDE instance name>', 'select * from [dbo].[tableB]') as
> a
>
> Note:
> 1. You need to replace the <your MSDE instance name> with your MSDE
> instance name.
>
> For more detailed information about OPENROWSET, please refer to the
> OPENROWSET topic in SQL Books Online(BOL).
>
> I hope the information is helpful.
> Sophie Guo
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> ================================================== ===
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>