conversion from Access query to mssql query

Hello Guys

I am chaging the connectivity of MSaccess2K to sqlserver
the code is written in vb editor of access
i have established the connection string
but the following query is generating error of
invalid object name

strSQL = SELECT DISTINCT [Sites Union Controls].Description,
[Sites Union Controls].[Rous Reportable Site], [Sites Union Controls].[Type], Samples.SequenceNumber FROM Jobs INNER JOIN ([Sites Union Controls] INNER JOIN Samples ON [Sites Union Controls].SiteSerial = Samples.SiteSerial) ON Jobs.JobSerial = Samples.JobSerial
WHERE ((Jobs.JobSerial) = " & intJobSerial & ") ORDER BY Samples.SequenceNumber

I am getting an error invalid object name sites union controls

can't we do union of two tables as above in mssql
Please Help

Thanks In AdvanceI'm sure some one will correct me if Im wrong but I dont think this query will ever work in SQL Server, it does look like something that might run in access though.

Ive had problems like this before, Access loves adding in brackets () that just get in the way and confuse things in SQL Server. It also seams to list all the tables then join them in the FROM statement which is not s SQL thing either.

I think your also going to have a problem with the WHERE clause, namely the part " & intJobSerial & " reefers to a variable in Access. Even if you have created the variable in SQL Server the syntax is still wrong

Your query doesnt look like a union query, but a standard select query gone a bit wrong in the FROM part. Your query should look something like

DECLARE @.intJobSerial VARCHAR(50) -- this creates the variable as a 50 character text field, don't need this bit if youve done it already

SET @.intJobSerial = 'XXX' -- this sets the variable to XXX, don't need this bit if youve done it already

SELECT DISTINCT [Sites Union Controls].[Description],[Sites Union Controls].[Rous Reportable Site], [Sites Union Controls].Type, Samples.SequenceNumber

FROM [Sites Union Controls]
INNER JOIN Samples
ON [Sites Union Controls].SiteSerial = Samples.SiteSerial
INNER JOIN Jobs
ON Jobs.JobSerial = Samples.JobSerial

WHERE Jobs.JobSerial = @.intJobSerial

ORDER BY Samples.SequenceNumber

The invalid object sites union controls in actually the table used the query, Im guessing its because the FROM Part was all messed up at it was the first thing it came across after it went wrong.

Hope this helps|||Hello,

Trying by used the next name Sites_Union_Controls because I'm not sure that you can have a name with blanc characters

Good luck

Sylvie

Quote:

Originally Posted by aakash

Hello Guys

I am chaging the connectivity of MSaccess2K to sqlserver
the code is written in vb editor of access
i have established the connection string
but the following query is generating error of
invalid object name

strSQL = SELECT DISTINCT [Sites Union Controls].Description,
[Sites Union Controls].[Rous Reportable Site], [Sites Union Controls].[Type], Samples.SequenceNumber FROM Jobs INNER JOIN ([Sites Union Controls] INNER JOIN Samples ON [Sites Union Controls].SiteSerial = Samples.SiteSerial) ON Jobs.JobSerial = Samples.JobSerial
WHERE ((Jobs.JobSerial) = " & intJobSerial & ") ORDER BY Samples.SequenceNumber

I am getting an error invalid object name sites union controls

can't we do union of two tables as above in mssql
Please Help

Thanks In Advance

|||Hey, a couple of points...
SQL uses + for concatenation so you need to change your where statement to
((Jobs.JobSerial) = " + @.intJobSerial + ")

JohnK is right, if intJobSerial is a local variable then it needs to be declared and it must begin with an @..

There is nothing wrong with the FROM statement, it's a little unusual to delay the two ON clauses at the end but this gives a different result set because nulls in the 3rd table, in your query SAMPLES, can be handled differently this way. It's more effective if your mixing Left Outer and Inner Joins, however.

The biggest thing I see is that the permissions on the SQL table [Sites Union Controls] may be different than what you expect. You should probably be using a full reference to it as [database].[owner].[table] to at least eliminate the possibility that your error is a security setup problem.

Tom|||Sites Union Controls, was query in old ms access project which was connected to ms access database i am changing connectivity to ms sql 2000 ,none of the above solutions seem to be working ,
please help

control concurrent users?

is it possible to limit the concurrent users in SQL connection string? Now, i m using .net 2003.

regards,

You can change the "max worker threads" option in SQL Server by running the statements below:

sp_configure 'show advanced options',1
reconfigure
go
sp_configure 'max worker threads',255
reconfigure
go

For more information about this option, please take a look at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_config_09wu.asp

|||

the problem is how can i configure my customer's SQL Server configuration. Now, i get one way. is it the right one?

Data Source=XXX;database=XXX;UID=XXX;PWD=XXX;MAX POOL SIZE=5

|||Sorry for misunderstanding. Yes the MAX POOL SIZE property should work in this case.

Context Connection Transaction

Hello Guys,

I need some some clarifications on how Context connections and transactions inter operate in CLR.

The context connection allows for ADO objects to be " running in the same transaction space". So the association to the current transaction is implied. So as long as I set for example my SqlCommand to use the context connection I am going to be running under the same transaction.

SqlConnection sqlConn = new SqlConnection("context connection=true");
SqlCommand sqlComm = new SqlCommand("EXEC myCommand", sqlConn);

I guess my ambiguity comes from the fact that the Transaction is not specifically specified.

In addition what happens upon a trigger that for example watches and insert on a table? If the insert occurs under a transaction, I would assume that I will be also picking up that transaction in the CRL Trigger, thus the whole operation would seem atomic.

Thank you,

Lubomir

Hi Lubomir!

I see that in your post you ask questions and give right answers to them yourself

Thank you,

Context connection and transactions

All -
Facts:
(a) SqlConnection allows only one "Context connection" to be opened at a tim
e.
(b) SqlConnection doesnt support parallel transactions. (calling a second
BeginTransaction before committing the first BeginTransaction throws
exception)
Problem:
Lets say a piece of C# code whose assembly is stored in SqlServer 2005 -
registered as SQLProcedure, is invoked.
Inside this code (which is now in SqlServer context) - i open a
SqlConnection as "context connection".
All the SqlCommand objects that uses this context connection, need the same
transaction (Lets say TransA) except one SqlCommand (LogCmd) that needs a
different transaction. (lets say TransB)
How to do this?
Calling BeginTransaction twice blows up.
Creating 2 seperate instances of SqlConnection with context connection =
true, also blows up.
Remember that somehow i might want to rollback TransA but always committ
TransB. For instance
try
{
// Do some actions
// SqlCommands that use TransA and TransB will be executed here
transA.Committ(); // things are fine - committ A
}
catch
{
transA.Rollback(); // things are incorrect - rollback A
}
finally
{
transB.Committ(); // Always comitt the logs...
}
How to do this when we are in context connection?
Regardz
Grafix."Grafix" <Grafix@.discussions.microsoft.com> wrote in message
news:54F45F4C-4F6B-44D7-8B13-0D55F13BB75C@.microsoft.com...
> All -
> Facts:
> (a) SqlConnection allows only one "Context connection" to be opened at a
> time.
> (b) SqlConnection doesnt support parallel transactions. (calling a second
> BeginTransaction before committing the first BeginTransaction throws
> exception)
> Problem:
> Lets say a piece of C# code whose assembly is stored in SqlServer 2005 -
> registered as SQLProcedure, is invoked.
> Inside this code (which is now in SqlServer context) - i open a
> SqlConnection as "context connection".
> All the SqlCommand objects that uses this context connection, need the
> same
> transaction (Lets say TransA) except one SqlCommand (LogCmd) that needs a
> different transaction. (lets say TransB)
> How to do this?
> Calling BeginTransaction twice blows up.
> Creating 2 seperate instances of SqlConnection with context connection =
> true, also blows up.
> Remember that somehow i might want to rollback TransA but always committ
> TransB. For instance
>
You will have exactly the same issue in TSQL. SQL Server does not support
autonomous transactions.
A common workaround is to write data into a table variable (or List<T> ), and
do something with it after the transaction is rolled back.
David|||I haven't gone into CLR stored procedures too much. But can't you add this
one SqlCommand (LogCmd) that needs a different transaction
into a seperate class and use the transaction option as requiresNew and with
the method haveing an autocommit tag to be true. and then call it from the
parent transaction.|||> one SqlCommand (LogCmd) that needs a different transaction
> into a seperate class and use the transaction option as requiresNew
There is the problem.
I am not able to have 2 active transaction scopes at the same time.
Remember LogCmd will be executed multiple times inbetween other regular
xxxCmd.
Which means i want 2 active transaction scopes (each with Option
"RequiresNew")
Unfortunately when i am in context connection (C# sp), i am not allowed to
create 2 connections that i can enlist with the TransactionScope.
I tried ur suggestion with the new .NET 2.0's
System.Transactions.TransactionScope class (Promotable transaction) for
seeing if i can enlist a single connection with multiple transactions - but
the problem is the same. So bad that they have introduced new Options like
TransactionScopeOption.Suppress and i am not able to use it still.
With one SqlConnection - u cannot associate with 2 different transaction
scopes.
And this C# sps allow only one connection at a time.
Seems i have to follow David Browne's suggestion of in-memory datastructure
to hold the logs and committ at the end.

Consume Connection from Report Explorer Web Part

Hello!
I am writing a custom report viewer that I would like to work with the
Report Explorer web part in SP2.
I created my consumer web part and it is connected to the Report Explorer
but it does not seem to ever receive any data from the Report Explorer. Is
the Report Viewer web part the only web part that can consume the information
provided by the Report Explorer?
TIA!
BrianHi Brian,
Welcome to use MSDN Managed Newsgroup!
From your description, my understanding of this issue is: you want to write
the consumer web part to connect to the Report Explorer Web part. If I
misunderstood your concern, please feel free to point it out.
Based on my knowledge, the Report Explorer Web Part and the Report Viewer
Web Part use the technical "Connectable Web Part to handle the connections
between them. So if you want to customize your web part to connect to the
Report Explorer, you need to implement the ICellConsumer interface.
Here is a article talk about the technical "Connectable Web Part" and there
is the sample code for how to implement the interface.
Creating a Connectable Web Part
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/spptsdk/htm
l/CreateConnectableWP_SV01003714.asp
Using Reporting Services SharePoint Web Parts in SQL Server 2000 Reporting
Services Service Pack 2
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/webrssp2.mspx
#EIAA
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
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.|||Hi Michael.
Thanks for the post.
Let me be a bit more specific. I already wrote a test web part implementing
the ICellConsumer interface.
When I connect it to the Report Explorer, the Cell that I get is an empty
string.
Actually, the CellReady method only seems to be called when the page loads.
Clicking on folders and reports in the Report Explorer does not seem to pass
any information to my web part. It passes something to the Report Viewer when
it is on the same page since instead of opening a new window to display a
report, the Report Viewer displays the report.
--
Take care,
Brian
"Michael Cheng [MSFT]" wrote:
> Hi Brian,
> Welcome to use MSDN Managed Newsgroup!
> From your description, my understanding of this issue is: you want to write
> the consumer web part to connect to the Report Explorer Web part. If I
> misunderstood your concern, please feel free to point it out.
> Based on my knowledge, the Report Explorer Web Part and the Report Viewer
> Web Part use the technical "Connectable Web Part to handle the connections
> between them. So if you want to customize your web part to connect to the
> Report Explorer, you need to implement the ICellConsumer interface.
> Here is a article talk about the technical "Connectable Web Part" and there
> is the sample code for how to implement the interface.
> Creating a Connectable Web Part
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/spptsdk/htm
> l/CreateConnectableWP_SV01003714.asp
> Using Reporting Services SharePoint Web Parts in SQL Server 2000 Reporting
> Services Service Pack 2
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/webrssp2.mspx
> #EIAA
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> 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.
>|||Hi Brain,
In the Report Explorer Web part, it pass the ReportUrl in the cell variable
in the cellReadyArgs passed in the CellReady EventHandler. And the Report
Viewer get it in the CellReady event. This event fire handled all by Web
Part architecture, and in you scenario, it seems the Report Explorer and
your own Web Part do not connected correctly, you may try to debug the SPS
session to see what these 2 Web Part really does.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
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.