If I had the following string in a table how could I find all records that
contained the term 'Business' for example? Would Regular Expressions be
supported in this context? Your comments regarding performance of a category
schema such as this?
// Example
PrivateSector_Business_Services
<%= Clinton GallagherClinton,
You could use the LIKE operator with the % wildcard character as:
WHERE COLUMN LIKE '%Business%'
Optionally you could use a Full-Text index as well.
HTH
Jerry
"clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
news:OOuBDiczFHA.1192@.TK2MSFTNGP10.phx.gbl...
> If I had the following string in a table how could I find all records that
> contained the term 'Business' for example? Would Regular Expressions be
> supported in this context? Your comments regarding performance of a
> category schema such as this?
> // Example
> PrivateSector_Business_Services
> <%= Clinton Gallagher
>
>
>
>|||Thank you Jerry. That was simple :-)
<%= Clinton Gallagher
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OZqoPlczFHA.2884@.TK2MSFTNGP09.phx.gbl...
> Clinton,
> You could use the LIKE operator with the % wildcard character as:
> WHERE COLUMN LIKE '%Business%'
> Optionally you could use a Full-Text index as well.
> HTH
> Jerry
> "clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
> news:OOuBDiczFHA.1192@.TK2MSFTNGP10.phx.gbl...
>|||For what it's worth, you can make real regular expression query searches via
calls to external COM libraries.
http://blogs.msdn.com/khen1234/arch.../11/416392.aspx
However, if using the native LIKE operator satisfies your requirement, then
use that instead.
"clintonG" < csgallagher@.REMOVETHISTEXTmetromilwaukee
.com> wrote in message
news:OOuBDiczFHA.1192@.TK2MSFTNGP10.phx.gbl...
> If I had the following string in a table how could I find all records that
> contained the term 'Business' for example? Would Regular Expressions be
> supported in this context? Your comments regarding performance of a
> category schema such as this?
> // Example
> PrivateSector_Business_Services
> <%= Clinton Gallagher
>
>
>
>
Showing posts with label queries. Show all posts
Showing posts with label queries. Show all posts
Thursday, March 29, 2012
do queries running from clr stored procs tie up one available connection
I'm wondering if one less external sql server connection is available when my clr stored proc querys my database inside of the db engine.
AFAIK, noNiels
Do pass thru query connections persist?
I have an Access 2003 front end with a SQL Server 2005 Express backend. I was thinking of using pass thru queries as row sources for some combo boxes such as states/countries for addresses. My question is do pass thru queries, when used as a row source, keep a connection to the DB server? Or do they get the data, disconnect and populate the control?
I realize I could populate the controls with code, but this seems less hassle and will overcome the ValueList size limit if needed.Unless you explicitely dis-connect the connection always remains open. this will hold true even if you use a DB control.|||Hi
Access will open a connection the first time it interacts with SQL Server (linked table, pass through) and retain this connection until the application closes. A pass through query, however, is like a client side cursor (as I understand it) - it uses no further server resources once it has run.
BTW - disconnected list filling is perfectly simple and more secure than pass throughs:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsmart01/html/sa01l8.asp
The bottom entry (Assigning recordsets to controls) is one I like.
HTH|||Pootle that article was very helpful. It also mentioned using a properly shaped recordset for reports which was a question of mine on another post.
I did a quick search on properly shaped recordsets but didn't find anything. Does anyone know what it is?
Using a Access Project I was able to have a report use an ADO recordset however it seems the connection and recordset must remain open the whole time the report is open. When using the a recordset with a control I opened the recordset assigned it to the control and closed it and everything was fine. When I did the same for the report it would not open. If I don't close the recordset or connection the report works fine. Is there anyway around this?
Pootle thanks again for the article.|||Using a Access Project I was able to have a report use an ADO recordset however it seems the connection and recordset must remain open the whole time the report is open. When using the a recordset with a control I opened the recordset assigned it to the control and closed it and everything was fine. When I did the same for the report it would not open. If I don't close the recordset or connection the report works fine. Is there anyway around this?The key will be the cursor location - that is the magic setting that takes you into the world of The Disconnected. Did you set it the location to client in your first attempt (remembering that the default is server)?|||Yes, I copied the code verbatim from the control code. In the control code I had not set the connection cursor so I did it in the report code and it still did not work.
Here is my code behind the report.
Private Sub Report_Open(Cancel As Integer)
Dim conGlob As New ADODB.Connection
Dim rst As New ADODB.Recordset
conGlob.ConnectionString = "Provider=SQLOLEDB;" _
& "Data Source=SERVER;" _
& "Initial Catalog=DatabaseTable;" _
& "Trusted_Connection=Yes;"
conGlob.CursorLocation = adUseClient
conGlob.Open
With rst
.ActiveConnection = conGlob
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockReadOnly
End With
rst.Open "usp_GetStates", , , , adCmdStoredProc
Set Me.Recordset = rst
rst.Close
conGlob.Close
End Sub|||Your best bet would be to use an access data project and use stored procedures
First time I tried to "upgrade" a consultants "application" I noticed that one form opened 19 connections
1 for every objects data source and an additional connection for any object that was updateable...it was very ugly and very slow|||There's nothing inherent to disconnected access that means you need to open multiple connections. One form, one connection. 19+ connections sounds ugly and there would have been an overhead but I would have thought the other processes (populating 19 objects for starters) would be the killer. I imagine you smoothed a lot of other rough edges to get the improvement I presume you got.|||There's nothing inherent to disconnected access that means you need to open multiple connections. One form, one connection. 19+ connections sounds ugly and there would have been an overhead but I would have thought the other processes (populating 19 objects for starters) would be the killer. I imagine you smoothed a lot of other rough edges to get the improvement I presume you got.
Yeah, it's called a total rewrite using Java and actually doing data modeling with the business BEFORE we did a conversion|||I may have figured it out. I have been using the activity monitor in Management Studio Express looking at the active processes and locks.
If I set the recordset activeConnection to nothing I can then close the original connection without the report closing. In the activity monitor it looks like the connection times out or something. It doesn't disappear right away like when I close the report but it does after awhile even if the report is still open.
I realize I could populate the controls with code, but this seems less hassle and will overcome the ValueList size limit if needed.Unless you explicitely dis-connect the connection always remains open. this will hold true even if you use a DB control.|||Hi
Access will open a connection the first time it interacts with SQL Server (linked table, pass through) and retain this connection until the application closes. A pass through query, however, is like a client side cursor (as I understand it) - it uses no further server resources once it has run.
BTW - disconnected list filling is perfectly simple and more secure than pass throughs:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsmart01/html/sa01l8.asp
The bottom entry (Assigning recordsets to controls) is one I like.
HTH|||Pootle that article was very helpful. It also mentioned using a properly shaped recordset for reports which was a question of mine on another post.
I did a quick search on properly shaped recordsets but didn't find anything. Does anyone know what it is?
Using a Access Project I was able to have a report use an ADO recordset however it seems the connection and recordset must remain open the whole time the report is open. When using the a recordset with a control I opened the recordset assigned it to the control and closed it and everything was fine. When I did the same for the report it would not open. If I don't close the recordset or connection the report works fine. Is there anyway around this?
Pootle thanks again for the article.|||Using a Access Project I was able to have a report use an ADO recordset however it seems the connection and recordset must remain open the whole time the report is open. When using the a recordset with a control I opened the recordset assigned it to the control and closed it and everything was fine. When I did the same for the report it would not open. If I don't close the recordset or connection the report works fine. Is there anyway around this?The key will be the cursor location - that is the magic setting that takes you into the world of The Disconnected. Did you set it the location to client in your first attempt (remembering that the default is server)?|||Yes, I copied the code verbatim from the control code. In the control code I had not set the connection cursor so I did it in the report code and it still did not work.
Here is my code behind the report.
Private Sub Report_Open(Cancel As Integer)
Dim conGlob As New ADODB.Connection
Dim rst As New ADODB.Recordset
conGlob.ConnectionString = "Provider=SQLOLEDB;" _
& "Data Source=SERVER;" _
& "Initial Catalog=DatabaseTable;" _
& "Trusted_Connection=Yes;"
conGlob.CursorLocation = adUseClient
conGlob.Open
With rst
.ActiveConnection = conGlob
.CursorType = adOpenStatic
.CursorLocation = adUseClient
.LockType = adLockReadOnly
End With
rst.Open "usp_GetStates", , , , adCmdStoredProc
Set Me.Recordset = rst
rst.Close
conGlob.Close
End Sub|||Your best bet would be to use an access data project and use stored procedures
First time I tried to "upgrade" a consultants "application" I noticed that one form opened 19 connections
1 for every objects data source and an additional connection for any object that was updateable...it was very ugly and very slow|||There's nothing inherent to disconnected access that means you need to open multiple connections. One form, one connection. 19+ connections sounds ugly and there would have been an overhead but I would have thought the other processes (populating 19 objects for starters) would be the killer. I imagine you smoothed a lot of other rough edges to get the improvement I presume you got.|||There's nothing inherent to disconnected access that means you need to open multiple connections. One form, one connection. 19+ connections sounds ugly and there would have been an overhead but I would have thought the other processes (populating 19 objects for starters) would be the killer. I imagine you smoothed a lot of other rough edges to get the improvement I presume you got.
Yeah, it's called a total rewrite using Java and actually doing data modeling with the business BEFORE we did a conversion|||I may have figured it out. I have been using the activity monitor in Management Studio Express looking at the active processes and locks.
If I set the recordset activeConnection to nothing I can then close the original connection without the report closing. In the activity monitor it looks like the connection times out or something. It doesn't disappear right away like when I close the report but it does after awhile even if the report is still open.
Thursday, March 22, 2012
Do I have to install SQL server 2000
I have made a small database with SQL server 2000, and i have made
interface using C#.NET
and ADO.net ( this interface manage all queries -SELECT UPDATE INSERT
DELETE-), now i want to give this application ( database and interface
) to my friends,
so do they have to install SQL server 2000 ( 1 GB or more ) to use this
small database ( 1 MB )
or there is another solution which cost less space which i ask for
thanks for helpI'd test the app for SQL Server 2005 and have the friends use SQL Server express.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<yosif4444@.gmail.com> wrote in message news:1136199441.120847.28540@.g14g2000cwa.googlegroups.com...
>I have made a small database with SQL server 2000, and i have made
> interface using C#.NET
> and ADO.net ( this interface manage all queries -SELECT UPDATE INSERT
> DELETE-), now i want to give this application ( database and interface
> ) to my friends,
> so do they have to install SQL server 2000 ( 1 GB or more ) to use this
> small database ( 1 MB )
> or there is another solution which cost less space which i ask for
> thanks for help
>|||On 2 Jan 2006 02:57:21 -0800, yosif4444@.gmail.com wrote:
> I have made a small database with SQL server 2000, and i have made
> interface using C#.NET
> and ADO.net ( this interface manage all queries -SELECT UPDATE INSERT
> DELETE-), now i want to give this application ( database and interface
> ) to my friends,
> so do they have to install SQL server 2000 ( 1 GB or more ) to use this
> small database ( 1 MB )
> or there is another solution which cost less space which i ask for
> thanks for help
You could use MSDE 2000
http://www.microsoft.com/downloads/details.aspx?familyid=413744d1-a0bc-479f-bafa-e4b278eb9147&displaylang=en
or you could use its replacement SQL Express|||Your freinds has to install MSDE 2000 (Microsoft Desktop Engine 2000) which
is a free version. They can download the same from the link
http://www.microsoft.com/downloads/details.aspx?FamilyID=413744D1-A0BC-479F-BAFA-E4B278EB9147&displaylang=en
regs
--
T.E.Nagaraj
Project Manager
Induscorp India Pvt Ltd
Blore
"Paul" wrote:
> On 2 Jan 2006 02:57:21 -0800, yosif4444@.gmail.com wrote:
> > I have made a small database with SQL server 2000, and i have made
> > interface using C#.NET
> > and ADO.net ( this interface manage all queries -SELECT UPDATE INSERT
> > DELETE-), now i want to give this application ( database and interface
> > ) to my friends,
> > so do they have to install SQL server 2000 ( 1 GB or more ) to use this
> > small database ( 1 MB )
> > or there is another solution which cost less space which i ask for
> > thanks for help
> You could use MSDE 2000
> http://www.microsoft.com/downloads/details.aspx?familyid=413744d1-a0bc-479f-bafa-e4b278eb9147&displaylang=en
> or you could use its replacement SQL Express
>sql
interface using C#.NET
and ADO.net ( this interface manage all queries -SELECT UPDATE INSERT
DELETE-), now i want to give this application ( database and interface
) to my friends,
so do they have to install SQL server 2000 ( 1 GB or more ) to use this
small database ( 1 MB )
or there is another solution which cost less space which i ask for
thanks for helpI'd test the app for SQL Server 2005 and have the friends use SQL Server express.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<yosif4444@.gmail.com> wrote in message news:1136199441.120847.28540@.g14g2000cwa.googlegroups.com...
>I have made a small database with SQL server 2000, and i have made
> interface using C#.NET
> and ADO.net ( this interface manage all queries -SELECT UPDATE INSERT
> DELETE-), now i want to give this application ( database and interface
> ) to my friends,
> so do they have to install SQL server 2000 ( 1 GB or more ) to use this
> small database ( 1 MB )
> or there is another solution which cost less space which i ask for
> thanks for help
>|||On 2 Jan 2006 02:57:21 -0800, yosif4444@.gmail.com wrote:
> I have made a small database with SQL server 2000, and i have made
> interface using C#.NET
> and ADO.net ( this interface manage all queries -SELECT UPDATE INSERT
> DELETE-), now i want to give this application ( database and interface
> ) to my friends,
> so do they have to install SQL server 2000 ( 1 GB or more ) to use this
> small database ( 1 MB )
> or there is another solution which cost less space which i ask for
> thanks for help
You could use MSDE 2000
http://www.microsoft.com/downloads/details.aspx?familyid=413744d1-a0bc-479f-bafa-e4b278eb9147&displaylang=en
or you could use its replacement SQL Express|||Your freinds has to install MSDE 2000 (Microsoft Desktop Engine 2000) which
is a free version. They can download the same from the link
http://www.microsoft.com/downloads/details.aspx?FamilyID=413744D1-A0BC-479F-BAFA-E4B278EB9147&displaylang=en
regs
--
T.E.Nagaraj
Project Manager
Induscorp India Pvt Ltd
Blore
"Paul" wrote:
> On 2 Jan 2006 02:57:21 -0800, yosif4444@.gmail.com wrote:
> > I have made a small database with SQL server 2000, and i have made
> > interface using C#.NET
> > and ADO.net ( this interface manage all queries -SELECT UPDATE INSERT
> > DELETE-), now i want to give this application ( database and interface
> > ) to my friends,
> > so do they have to install SQL server 2000 ( 1 GB or more ) to use this
> > small database ( 1 MB )
> > or there is another solution which cost less space which i ask for
> > thanks for help
> You could use MSDE 2000
> http://www.microsoft.com/downloads/details.aspx?familyid=413744d1-a0bc-479f-bafa-e4b278eb9147&displaylang=en
> or you could use its replacement SQL Express
>sql
Do I have to install SQL server 2000
I have made a small database with SQL server 2000, and i have made
interface using C#.NET
and ADO.net ( this interface manage all queries -SELECT UPDATE INSERT
DELETE-), now i want to give this application ( database and interface
) to my friends,
so do they have to install SQL server 2000 ( 1 GB or more ) to use this
small database ( 1 MB )
or there is another solution which cost less space which i ask for
thanks for helpI'd test the app for SQL Server 2005 and have the friends use SQL Server exp
ress.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<yosif4444@.gmail.com> wrote in message news:1136199441.120847.28540@.g14g2000cwa.googlegroups
.com...
>I have made a small database with SQL server 2000, and i have made
> interface using C#.NET
> and ADO.net ( this interface manage all queries -SELECT UPDATE INSERT
> DELETE-), now i want to give this application ( database and interface
> ) to my friends,
> so do they have to install SQL server 2000 ( 1 GB or more ) to use this
> small database ( 1 MB )
> or there is another solution which cost less space which i ask for
> thanks for help
>|||On 2 Jan 2006 02:57:21 -0800, yosif4444@.gmail.com wrote:
> I have made a small database with SQL server 2000, and i have made
> interface using C#.NET
> and ADO.net ( this interface manage all queries -SELECT UPDATE INSERT
> DELETE-), now i want to give this application ( database and interface
> ) to my friends,
> so do they have to install SQL server 2000 ( 1 GB or more ) to use this
> small database ( 1 MB )
> or there is another solution which cost less space which i ask for
> thanks for help
You could use MSDE 2000
http://www.microsoft.com/downloads/...&displaylang=en
or you could use its replacement SQL Express|||Your freinds has to install MSDE 2000 (Microsoft Desktop Engine 2000) which
is a free version. They can download the same from the link
http://www.microsoft.com/downloads/...&displaylang=en
regs
--
T.E.Nagaraj
Project Manager
Induscorp India Pvt Ltd
Blore
"Paul" wrote:
> On 2 Jan 2006 02:57:21 -0800, yosif4444@.gmail.com wrote:
>
> You could use MSDE 2000
> http://www.microsoft.com/downloads/...&displaylang=en
> or you could use its replacement SQL Express
>
interface using C#.NET
and ADO.net ( this interface manage all queries -SELECT UPDATE INSERT
DELETE-), now i want to give this application ( database and interface
) to my friends,
so do they have to install SQL server 2000 ( 1 GB or more ) to use this
small database ( 1 MB )
or there is another solution which cost less space which i ask for
thanks for helpI'd test the app for SQL Server 2005 and have the friends use SQL Server exp
ress.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<yosif4444@.gmail.com> wrote in message news:1136199441.120847.28540@.g14g2000cwa.googlegroups
.com...
>I have made a small database with SQL server 2000, and i have made
> interface using C#.NET
> and ADO.net ( this interface manage all queries -SELECT UPDATE INSERT
> DELETE-), now i want to give this application ( database and interface
> ) to my friends,
> so do they have to install SQL server 2000 ( 1 GB or more ) to use this
> small database ( 1 MB )
> or there is another solution which cost less space which i ask for
> thanks for help
>|||On 2 Jan 2006 02:57:21 -0800, yosif4444@.gmail.com wrote:
> I have made a small database with SQL server 2000, and i have made
> interface using C#.NET
> and ADO.net ( this interface manage all queries -SELECT UPDATE INSERT
> DELETE-), now i want to give this application ( database and interface
> ) to my friends,
> so do they have to install SQL server 2000 ( 1 GB or more ) to use this
> small database ( 1 MB )
> or there is another solution which cost less space which i ask for
> thanks for help
You could use MSDE 2000
http://www.microsoft.com/downloads/...&displaylang=en
or you could use its replacement SQL Express|||Your freinds has to install MSDE 2000 (Microsoft Desktop Engine 2000) which
is a free version. They can download the same from the link
http://www.microsoft.com/downloads/...&displaylang=en
regs
--
T.E.Nagaraj
Project Manager
Induscorp India Pvt Ltd
Blore
"Paul" wrote:
> On 2 Jan 2006 02:57:21 -0800, yosif4444@.gmail.com wrote:
>
> You could use MSDE 2000
> http://www.microsoft.com/downloads/...&displaylang=en
> or you could use its replacement SQL Express
>
Do I have to install SQL server 2000
I have made a small database with SQL server 2000, and i have made
interface using C#.NET
and ADO.net ( this interface manage all queries -SELECT UPDATE INSERT
DELETE-), now i want to give this application ( database and interface
) to my friends,
so do they have to install SQL server 2000 ( 1 GB or more ) to use this
small database ( 1 MB )
or there is another solution which cost less space which i ask for
thanks for help
I'd test the app for SQL Server 2005 and have the friends use SQL Server express.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<yosif4444@.gmail.com> wrote in message news:1136199441.120847.28540@.g14g2000cwa.googlegro ups.com...
>I have made a small database with SQL server 2000, and i have made
> interface using C#.NET
> and ADO.net ( this interface manage all queries -SELECT UPDATE INSERT
> DELETE-), now i want to give this application ( database and interface
> ) to my friends,
> so do they have to install SQL server 2000 ( 1 GB or more ) to use this
> small database ( 1 MB )
> or there is another solution which cost less space which i ask for
> thanks for help
>
|||On 2 Jan 2006 02:57:21 -0800, yosif4444@.gmail.com wrote:
> I have made a small database with SQL server 2000, and i have made
> interface using C#.NET
> and ADO.net ( this interface manage all queries -SELECT UPDATE INSERT
> DELETE-), now i want to give this application ( database and interface
> ) to my friends,
> so do they have to install SQL server 2000 ( 1 GB or more ) to use this
> small database ( 1 MB )
> or there is another solution which cost less space which i ask for
> thanks for help
You could use MSDE 2000
http://www.microsoft.com/downloads/d...displaylang=en
or you could use its replacement SQL Express
|||Your freinds has to install MSDE 2000 (Microsoft Desktop Engine 2000) which
is a free version. They can download the same from the link
http://www.microsoft.com/downloads/d...displaylang=en
regs
T.E.Nagaraj
Project Manager
Induscorp India Pvt Ltd
Blore
"Paul" wrote:
> On 2 Jan 2006 02:57:21 -0800, yosif4444@.gmail.com wrote:
>
> You could use MSDE 2000
> http://www.microsoft.com/downloads/d...displaylang=en
> or you could use its replacement SQL Express
>
interface using C#.NET
and ADO.net ( this interface manage all queries -SELECT UPDATE INSERT
DELETE-), now i want to give this application ( database and interface
) to my friends,
so do they have to install SQL server 2000 ( 1 GB or more ) to use this
small database ( 1 MB )
or there is another solution which cost less space which i ask for
thanks for help
I'd test the app for SQL Server 2005 and have the friends use SQL Server express.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<yosif4444@.gmail.com> wrote in message news:1136199441.120847.28540@.g14g2000cwa.googlegro ups.com...
>I have made a small database with SQL server 2000, and i have made
> interface using C#.NET
> and ADO.net ( this interface manage all queries -SELECT UPDATE INSERT
> DELETE-), now i want to give this application ( database and interface
> ) to my friends,
> so do they have to install SQL server 2000 ( 1 GB or more ) to use this
> small database ( 1 MB )
> or there is another solution which cost less space which i ask for
> thanks for help
>
|||On 2 Jan 2006 02:57:21 -0800, yosif4444@.gmail.com wrote:
> I have made a small database with SQL server 2000, and i have made
> interface using C#.NET
> and ADO.net ( this interface manage all queries -SELECT UPDATE INSERT
> DELETE-), now i want to give this application ( database and interface
> ) to my friends,
> so do they have to install SQL server 2000 ( 1 GB or more ) to use this
> small database ( 1 MB )
> or there is another solution which cost less space which i ask for
> thanks for help
You could use MSDE 2000
http://www.microsoft.com/downloads/d...displaylang=en
or you could use its replacement SQL Express
|||Your freinds has to install MSDE 2000 (Microsoft Desktop Engine 2000) which
is a free version. They can download the same from the link
http://www.microsoft.com/downloads/d...displaylang=en
regs
T.E.Nagaraj
Project Manager
Induscorp India Pvt Ltd
Blore
"Paul" wrote:
> On 2 Jan 2006 02:57:21 -0800, yosif4444@.gmail.com wrote:
>
> You could use MSDE 2000
> http://www.microsoft.com/downloads/d...displaylang=en
> or you could use its replacement SQL Express
>
Monday, March 19, 2012
DMX Queries with MS Time Series
Hi!
I have a table Month_Sales(Month, product_1, .., product_n). The value of column product_i is the sale in this month.
so when i build MS Time Series for this domain, i want to query to find top m product is seld most in next month?
How do i buid that query?
DMX doesn't provide a way to order by the forecast value. However, you should be able to easily do this in client side code. Assuming your model looks like this:
CREATE MINING MODEL SalesForecast(
Product TEXT KEY,
Month LONG KEY TIME,
Sales LONG CONTINUOUS)
USING Microsoft_Time_Series
The following query will return the prediction for the next month for all products:
SELECT Product, Sales from SalesForecast
You can sort the query result by Sales in client code and pick the top N..
DMX from Java
My preferred programming language is Java (sorry Microsoft). I've searched for examples of running DMX queries into an Analysis Services database from Java but failed to locate any. I've seen suggestions that XMLA could be used but again, I can't locate any examples (in any language). For my current project I ran up the white flag and used C# instead but this wouldn't be an option in other cases. It would be possible to make the DMX calls from C# objects and call those from Java but that's pretty labourious to code.
Suggestions?
have you tried the XMLA Thin Client sample at http://www.sqlserverdatamining.com/DMCommunity/LiveSamples/124.aspx ?
|||I found that page but I didn't find any code sample to go with it.|||There's an old(er) code sample here http://www.sqlserverdatamining.com/DMCommunity/SQLServer2000/Links_LinkRedirector.aspx?id=96
Sunday, March 11, 2012
division in a view
In trying to simplify an overly complex query [at one time, 1000+ views and
14 queries run in query analyzer!], I am trying to incorporate many of the
different views/queries together but am having troubles with one thing;
trying to divide one of the columns in a view.
SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
Doesn't workPerhaps you can incorporate a CASE statement
select
case MLD1ST when 0 then 0
else
100 * (RCD1ST / MLD1ST)
end
from <your table>
"notme" <not@.me.com> wrote in message
news:uOoxI89LFHA.1144@.TK2MSFTNGP09.phx.gbl...
> In trying to simplify an overly complex query [at one time, 1000+ views
and
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work|||Are you encountering a divide by zero error? Make sure that you code the
T-SQL in such a way as to not have any problems with a divide by zero error:
create table #foo (RCD1ST int, MLD1ST decimal(5,2))
insert into #foo values (5, 2)
insert into #foo values (10, 2)
insert into #foo values (15, 0)
insert into #foo values (20, null)
PRINT ''
PRINT '********************* this fails *********************'
SELECT *, 100 * (RCD1ST / MLD1ST) AS POR FROM #foo
go
PRINT ''
PRINT '********************* this works *********************'
SELECT *, CASE WHEN MLD1ST = 0 THEN NULL ELSE 100 * (RCD1ST / MLD1ST) END AS
POR FROM #foo
Keith
"notme" <not@.me.com> wrote in message
news:uOoxI89LFHA.1144@.TK2MSFTNGP09.phx.gbl...
> In trying to simplify an overly complex query [at one time, 1000+ views
and
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work|||You can not reference a column alias in the same column list. Try:
SELECT
FIPS,
[MTG TYPE],
COUNT(RCVD_1ST) AS RCD1ST,
COUNT(MAILED_1ST) AS MLD1ST,
AVG(DATEDIFF(day, MAILED_1ST, RCVD_1ST)) AS mldgap,
AVG(DATEDIFF(day, CLOSING, MAILED_1ST)) AS closegap,
100 * (COUNT(RCVD_1ST) / nullif(COUNT(MAILED_1ST), 0)) AS POR
from ...
AMB
"notme" wrote:
> In trying to simplify an overly complex query [at one time, 1000+ views an
d
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work
>|||Not really, depend if sql server decides to escalate the lock .
Example:
-- connection 1
use northwind
go
begin transaction
update orders
set orderdate = orderdate
where orderid = 10250
-- connection 2
use northwind
go
select * from orders
where orderid < 10250 or orderid > 10250
-- connection 1
rollback transaction
AMB
"notme" wrote:
> In trying to simplify an overly complex query [at one time, 1000+ views an
d
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> Not really, depend if sql server decides to escalate the lock .
> Example:
> -- connection 1
> use northwind
> go
> begin transaction
> update orders
> set orderdate = orderdate
> where orderid = 10250
> -- connection 2
> use northwind
> go
> select * from orders
> where orderid < 10250 or orderid > 10250
> -- connection 1
> rollback transaction
>
> AMB
>
> "notme" wrote:
>|||On 3/23/2005 1:49:09 PM, "examnotes" wrote:
>You can not reference a column alias in the same column list. Try:
>SELECT
> FIPS,
> [MTG TYPE],
> COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST,
> AVG(DATEDIFF(day, MAILED_1ST, RCVD_1ST)) AS mldgap,
> AVG(DATEDIFF(day, CLOSING, MAILED_1ST)) AS closegap,
> 100 * (COUNT(RCVD_1ST) / nullif(COUNT(MAILED_1ST), 0)) AS POR
>from ...
>
>AMB
>
>
That was it - thanks
jeff
14 queries run in query analyzer!], I am trying to incorporate many of the
different views/queries together but am having troubles with one thing;
trying to divide one of the columns in a view.
SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
Doesn't workPerhaps you can incorporate a CASE statement
select
case MLD1ST when 0 then 0
else
100 * (RCD1ST / MLD1ST)
end
from <your table>
"notme" <not@.me.com> wrote in message
news:uOoxI89LFHA.1144@.TK2MSFTNGP09.phx.gbl...
> In trying to simplify an overly complex query [at one time, 1000+ views
and
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work|||Are you encountering a divide by zero error? Make sure that you code the
T-SQL in such a way as to not have any problems with a divide by zero error:
create table #foo (RCD1ST int, MLD1ST decimal(5,2))
insert into #foo values (5, 2)
insert into #foo values (10, 2)
insert into #foo values (15, 0)
insert into #foo values (20, null)
PRINT ''
PRINT '********************* this fails *********************'
SELECT *, 100 * (RCD1ST / MLD1ST) AS POR FROM #foo
go
PRINT ''
PRINT '********************* this works *********************'
SELECT *, CASE WHEN MLD1ST = 0 THEN NULL ELSE 100 * (RCD1ST / MLD1ST) END AS
POR FROM #foo
Keith
"notme" <not@.me.com> wrote in message
news:uOoxI89LFHA.1144@.TK2MSFTNGP09.phx.gbl...
> In trying to simplify an overly complex query [at one time, 1000+ views
and
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work|||You can not reference a column alias in the same column list. Try:
SELECT
FIPS,
[MTG TYPE],
COUNT(RCVD_1ST) AS RCD1ST,
COUNT(MAILED_1ST) AS MLD1ST,
AVG(DATEDIFF(day, MAILED_1ST, RCVD_1ST)) AS mldgap,
AVG(DATEDIFF(day, CLOSING, MAILED_1ST)) AS closegap,
100 * (COUNT(RCVD_1ST) / nullif(COUNT(MAILED_1ST), 0)) AS POR
from ...
AMB
"notme" wrote:
> In trying to simplify an overly complex query [at one time, 1000+ views an
d
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work
>|||Not really, depend if sql server decides to escalate the lock .
Example:
-- connection 1
use northwind
go
begin transaction
update orders
set orderdate = orderdate
where orderid = 10250
-- connection 2
use northwind
go
select * from orders
where orderid < 10250 or orderid > 10250
-- connection 1
rollback transaction
AMB
"notme" wrote:
> In trying to simplify an overly complex query [at one time, 1000+ views an
d
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> Not really, depend if sql server decides to escalate the lock .
> Example:
> -- connection 1
> use northwind
> go
> begin transaction
> update orders
> set orderdate = orderdate
> where orderid = 10250
> -- connection 2
> use northwind
> go
> select * from orders
> where orderid < 10250 or orderid > 10250
> -- connection 1
> rollback transaction
>
> AMB
>
> "notme" wrote:
>|||On 3/23/2005 1:49:09 PM, "examnotes" wrote:
>You can not reference a column alias in the same column list. Try:
>SELECT
> FIPS,
> [MTG TYPE],
> COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST,
> AVG(DATEDIFF(day, MAILED_1ST, RCVD_1ST)) AS mldgap,
> AVG(DATEDIFF(day, CLOSING, MAILED_1ST)) AS closegap,
> 100 * (COUNT(RCVD_1ST) / nullif(COUNT(MAILED_1ST), 0)) AS POR
>from ...
>
>AMB
>
>
That was it - thanks
jeff
Friday, February 24, 2012
Distributing/importing semantic models
Hi,
i'm using SQL Server 2005 Reporting Services to create ad-hoc reports.
Using the BI Dev Studio i'm defining a DSV and adding queries to it
before refining in the semantic model. i need to be able to distribute
these to our customers, who use an SS DB with known schema.
In a simulated customer environment i've successfully imported the DSV
file (using SMSS) to RS. When i subsequently try to import the SMDL
file i get an error complaining that the DataSourceView element is
missing from the SemanticModel. Quite correct. But how do i specify
the DSV element or otherwise make the association?
It is quite acceptable to me to make a simple text edit to the SMDL
file.
TIA for any help.OK, slight correction - i haven't successfully imported the DSV.
Import appeared to succeed but there's actually no data visible for it
in SMSS.|||Since there are no replies i'm either very stupid or nobody knows. If
it's the latter then for the benefit of all here's how i did it in the
end.
DataSourceView is a valid element of the SemanticModel element. So
manually edit your SMDL file and paste the entire content of your DSV
file (which is a single DataSourceView element) just above the
SemanticModel closure tag ("</SemanticModel>").
The Management Studio will now happily import the modified SMDL file.
The customer just needs to manually change the Data Source to their own
database using Management Studio (or Report Manager).
Cheers all.
i'm using SQL Server 2005 Reporting Services to create ad-hoc reports.
Using the BI Dev Studio i'm defining a DSV and adding queries to it
before refining in the semantic model. i need to be able to distribute
these to our customers, who use an SS DB with known schema.
In a simulated customer environment i've successfully imported the DSV
file (using SMSS) to RS. When i subsequently try to import the SMDL
file i get an error complaining that the DataSourceView element is
missing from the SemanticModel. Quite correct. But how do i specify
the DSV element or otherwise make the association?
It is quite acceptable to me to make a simple text edit to the SMDL
file.
TIA for any help.OK, slight correction - i haven't successfully imported the DSV.
Import appeared to succeed but there's actually no data visible for it
in SMSS.|||Since there are no replies i'm either very stupid or nobody knows. If
it's the latter then for the benefit of all here's how i did it in the
end.
DataSourceView is a valid element of the SemanticModel element. So
manually edit your SMDL file and paste the entire content of your DSV
file (which is a single DataSourceView element) just above the
SemanticModel closure tag ("</SemanticModel>").
The Management Studio will now happily import the modified SMDL file.
The customer just needs to manually change the Data Source to their own
database using Management Studio (or Report Manager).
Cheers all.
Friday, February 17, 2012
distributed transaction error from CLR Trigger
I have a stored procedure in SQL2005 that queries and updates a linked oracle server. The sp runs fine from Management Studio, but when called by a CLR trigger I get the following error message:
--
HOW TO CONFIGURE DISTIBUTED TRANSACTION COORDINATION
--
Command attempted:
if @.@.trancount > 0 rollback tran
(Transaction sequence number: 0x000000000000000032DD00000000, Command ID: 1)
Error messages:
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
Get help: http://help/7391
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
Get help: http://help/7391
A .NET Framework error occurred during execution of user defined routine or aggregate 'PriorityTrigger':
System.Data.SqlClient.SqlException: The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction.
Changed database context to 'pims'.
OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" returned message "New transaction cannot enlist in the specified transaction coordinator. ".
System.Data.S (Source: MSSQLServer, Error number: 6549)
Get help: http://help/6549
Any thoughts or direction appreciated
Richard
Hi!
You may try to play with settings of OraOLEDB (DistribTX).
|||The procedure will probably fail the same way when run from a standard TSQL trigger. DTC is pulled in when you run the update within an existing local transaction. There should be an option for your linked server that will disable automatic enlistment if you don't need the transaction distributed to the remote machine. Otherwise you'll have to play with the Oledb provider & DTC setup to get distributed transactions working between the two.|||--
HOW TO CONFIGURE DISTIBUTED TRANSACTION COORDINATION
--
First verify the "Distribute Transaction Coordinator" Service is
running on both database server computer and client computers
1. Go to "Administrative Tools > Services"
2. Turn on the "Distribute Transaction Coordinator" Service if it is not running
If it is running and client application is not on the same computer as
the database server, on the computer running database server
1. Go to "Administrative Tools > Component Services"
2. On the left navigation tree, go to "Component Services > Computers
> My Computer" (you may need to double click and wait as some nodes
need time to expand)
3. Right click on "My Computer", select "Properties"
4. Select "MSDTC" tab
5. Click "Security Configuration"
6. Make sure you check "Network DTC Access", "Allow Remote Client",
"Allow Inbound/Outbound", "Enable TIP" (Some option may not be
necessary, have a try to get your configuration)
7. The service will restart
8. BUT YOU MAY NEED TO REBOOT YOUR SERVER IF IT STILL DOESN'T WORK
(This is the thing drove me crazy before)
On your client computer use the same above procedure to open the
"Security Configuration" setting, make sure you check "Network DTC
Access", "Allow Inbound/Outbound" option, restart service and computer
if necessary.
On you SQL server service manager, click "Service" dropdown, select
"Distribute Transaction Coordinator", it should be also running on
your server computer.
distributed transaction error from CLR Trigger
I have a stored procedure in SQL2005 that queries and updates a linked oracle server. The sp runs fine from Management Studio, but when called by a CLR trigger I get the following error message:
--
HOW TO CONFIGURE DISTIBUTED TRANSACTION COORDINATION
--
Command attempted:
if @.@.trancount > 0 rollback tran
(Transaction sequence number: 0x000000000000000032DD00000000, Command ID: 1)
Error messages:
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
Get help: http://help/7391
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
Get help: http://help/7391
A .NET Framework error occurred during execution of user defined routine or aggregate 'PriorityTrigger':
System.Data.SqlClient.SqlException: The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" was unable to begin a distributed transaction.
Changed database context to 'pims'.
OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE_LINK" returned message "New transaction cannot enlist in the specified transaction coordinator. ".
System.Data.S (Source: MSSQLServer, Error number: 6549)
Get help: http://help/6549
Any thoughts or direction appreciated
Richard
Hi!
You may try to play with settings of OraOLEDB (DistribTX).
|||The procedure will probably fail the same way when run from a standard TSQL trigger. DTC is pulled in when you run the update within an existing local transaction. There should be an option for your linked server that will disable automatic enlistment if you don't need the transaction distributed to the remote machine. Otherwise you'll have to play with the Oledb provider & DTC setup to get distributed transactions working between the two.|||--
HOW TO CONFIGURE DISTIBUTED TRANSACTION COORDINATION
--
First verify the "Distribute Transaction Coordinator" Service is
running on both database server computer and client computers
1. Go to "Administrative Tools > Services"
2. Turn on the "Distribute Transaction Coordinator" Service if it is not running
If it is running and client application is not on the same computer as
the database server, on the computer running database server
1. Go to "Administrative Tools > Component Services"
2. On the left navigation tree, go to "Component Services > Computers
> My Computer" (you may need to double click and wait as some nodes
need time to expand)
3. Right click on "My Computer", select "Properties"
4. Select "MSDTC" tab
5. Click "Security Configuration"
6. Make sure you check "Network DTC Access", "Allow Remote Client",
"Allow Inbound/Outbound", "Enable TIP" (Some option may not be
necessary, have a try to get your configuration)
7. The service will restart
8. BUT YOU MAY NEED TO REBOOT YOUR SERVER IF IT STILL DOESN'T WORK
(This is the thing drove me crazy before)
On your client computer use the same above procedure to open the
"Security Configuration" setting, make sure you check "Network DTC
Access", "Allow Inbound/Outbound" option, restart service and computer
if necessary.
On you SQL server service manager, click "Service" dropdown, select
"Distribute Transaction Coordinator", it should be also running on
your server computer.
Tuesday, February 14, 2012
Distributed Transaction
I want to do distributed queries in a transaction, I'm using win XP pro, then I connect to a remote server (win 2003 standard), the remote server has sql server standard edition and my computer has sql server personal edition.
when I try to do this
begin tran
select * from rafael.tcadbcoi.dbo.invlin
commit tran
I get this error
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
without "begin tran" , the sicript works ritght> when I try to do this
> begin tran
> select * from rafael.tcadbcoi.dbo.invlin
> commit tran
> I get this error
> The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to
> begin a distributed transaction. [OLE/DB provider returned message: New
transaction cannot enlist in
> the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction
> returned 0x8004d00a].
> without "begin tran" , the script works right
--
Error 0x8004d00a is an issue with MSDTC. On the Windows 2003 server, verify
whether "Enable DTC for remote access" is turned on, reboot and try again.
For more information, consult the "Configuring an application server"
documentation of Windows 2003 online help.
Hope this helps,
--
Eric Cárdenas
SQL Server support
when I try to do this
begin tran
select * from rafael.tcadbcoi.dbo.invlin
commit tran
I get this error
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
without "begin tran" , the sicript works ritght> when I try to do this
> begin tran
> select * from rafael.tcadbcoi.dbo.invlin
> commit tran
> I get this error
> The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to
> begin a distributed transaction. [OLE/DB provider returned message: New
transaction cannot enlist in
> the specified transaction coordinator. ]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction
> returned 0x8004d00a].
> without "begin tran" , the script works right
--
Error 0x8004d00a is an issue with MSDTC. On the Windows 2003 server, verify
whether "Enable DTC for remote access" is turned on, reboot and try again.
For more information, consult the "Configuring an application server"
documentation of Windows 2003 online help.
Hope this helps,
--
Eric Cárdenas
SQL Server support
distributed query runs indefinitely
Matt,
I never tire of queries
I'm just very pressed for time
at the moment as we're in the middle of moving our
servers to a hosted environment and I'm managing the
database side of things. If possible, I'll take a look
late this afternoon (UK time) if nobody else has replied.
Rgds,
Paul Ibison (SQL Server MVP)
Paul,
Thanx, I greatly appreciate any time you can set aside whenever it is
Hope your transition goes smoothly,
Matt
|||Matt,
please try this type of approach:
CREATE TRIGGER ti_tst ON [dbo].[tst]
FOR INSERT
AS
set xact_abort on
insert into [dhsrvskh504\stst].dba_admin.dbo.tst (name)
select name from inserted
This works fine for me - hopefully for you as well.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Matt,
if you still have problems, please look at these
troubleshooting guides:
http://www.kbalertz.com/Feedback.aspx?kbNumber=250367
http://support.microsoft.com/kb/306212
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I greatly appreciate all help that you have provided. It appears that what
you have telling me the whole time was correct. After much looking,
searching, and cussing I found that it was a problem with windows 2003 and
the way it handles RPC requests. For any other here is a link to article to
work around.
http://support.microsoft.com/?kbid=555017
Once again thank you for all your help through this.
Matt
I never tire of queries
I'm just very pressed for timeat the moment as we're in the middle of moving our
servers to a hosted environment and I'm managing the
database side of things. If possible, I'll take a look
late this afternoon (UK time) if nobody else has replied.
Rgds,
Paul Ibison (SQL Server MVP)
Paul,
Thanx, I greatly appreciate any time you can set aside whenever it is
Hope your transition goes smoothly,
Matt
|||Matt,
please try this type of approach:
CREATE TRIGGER ti_tst ON [dbo].[tst]
FOR INSERT
AS
set xact_abort on
insert into [dhsrvskh504\stst].dba_admin.dbo.tst (name)
select name from inserted
This works fine for me - hopefully for you as well.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Matt,
if you still have problems, please look at these
troubleshooting guides:
http://www.kbalertz.com/Feedback.aspx?kbNumber=250367
http://support.microsoft.com/kb/306212
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I greatly appreciate all help that you have provided. It appears that what
you have telling me the whole time was correct. After much looking,
searching, and cussing I found that it was a problem with windows 2003 and
the way it handles RPC requests. For any other here is a link to article to
work around.
http://support.microsoft.com/?kbid=555017
Once again thank you for all your help through this.
Matt
Subscribe to:
Posts (Atom)