MDB via Linked Server

I have a view names WSDetailJobList.
It contains one field and some joined tables from an MDB file for which we
have set up a linked server.
Here is a snippet of the view
select
...
EA.EmailAddress,
...
join
EZWEBPOST...WebPost WP
on
WP.ReqID = R.RequirementID
join
EZWEBPOST...tblEmailAddresses EA
on
EA.EmailAddressID = WP.ReplyEmailAddressID
...
It works perfectly from QA and from EM.
We have a consultant trying to use the view.
He's getting...
nativecode=7405 - Heterogeneous queries require the ANSI_NULLS and
ANSI_WARNINGS options to be set for the connection. This ensures consistent
query semantics.
Can someone point me in the right direction?
Kyle!
Read about the command SET ANSI_NULLS and SET ANSI_WARNINGS in Books Online. Ask you programmer from
what type of environment he/she it accessing your SQL Server. Also, if you are using stored
procedures, you set ANSI WARNINGS inside the procedure. But ANSI_NULLS you need to set when you
create the procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:enFB50CpFHA.3084@.TK2MSFTNGP09.phx.gbl...
>I have a view names WSDetailJobList.
> It contains one field and some joined tables from an MDB file for which we have set up a linked
> server.
> Here is a snippet of the view
> select
> ...
> EA.EmailAddress,
> ...
> join
> EZWEBPOST...WebPost WP
> on
> WP.ReqID = R.RequirementID
> join
> EZWEBPOST...tblEmailAddresses EA
> on
> EA.EmailAddressID = WP.ReplyEmailAddressID
> ...
> It works perfectly from QA and from EM.
> We have a consultant trying to use the view.
> He's getting...
> nativecode=7405 - Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set
> for the connection. This ensures consistent query semantics.
>
> Can someone point me in the right direction?
> Kyle!
>

MDB via Linked Server

I have a view names WSDetailJobList.
It contains one field and some joined tables from an MDB file for which we
have set up a linked server.
Here is a snippet of the view
select
...
EA.EmailAddress,
...
join
EZWEBPOST...WebPost WP
on
WP.ReqID = R.RequirementID
join
EZWEBPOST...tblEmailAddresses EA
on
EA.EmailAddressID = WP.ReplyEmailAddressID
...
It works perfectly from QA and from EM.
We have a consultant trying to use the view.
He's getting...
nativecode=7405 - Heterogeneous queries require the ANSI_NULLS and
ANSI_WARNINGS options to be set for the connection. This ensures consistent
query semantics.
Can someone point me in the right direction?
Kyle!Read about the command SET ANSI_NULLS and SET ANSI_WARNINGS in Books Online. Ask you programmer from
what type of environment he/she it accessing your SQL Server. Also, if you are using stored
procedures, you set ANSI WARNINGS inside the procedure. But ANSI_NULLS you need to set when you
create the procedure.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:enFB50CpFHA.3084@.TK2MSFTNGP09.phx.gbl...
>I have a view names WSDetailJobList.
> It contains one field and some joined tables from an MDB file for which we have set up a linked
> server.
> Here is a snippet of the view
> select
> ...
> EA.EmailAddress,
> ...
> join
> EZWEBPOST...WebPost WP
> on
> WP.ReqID = R.RequirementID
> join
> EZWEBPOST...tblEmailAddresses EA
> on
> EA.EmailAddressID = WP.ReplyEmailAddressID
> ...
> It works perfectly from QA and from EM.
> We have a consultant trying to use the view.
> He's getting...
> nativecode=7405 - Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set
> for the connection. This ensures consistent query semantics.
>
> Can someone point me in the right direction?
> Kyle!
>

MDB via Linked Server

I have a view names WSDetailJobList.
It contains one field and some joined tables from an MDB file for which we
have set up a linked server.
Here is a snippet of the view
select
...
EA.EmailAddress,
...
join
EZWEBPOST...WebPost WP
on
WP.ReqID = R.RequirementID
join
EZWEBPOST...tblEmailAddresses EA
on
EA.EmailAddressID = WP.ReplyEmailAddressID
...
It works perfectly from QA and from EM.
We have a consultant trying to use the view.
He's getting...
nativecode=7405 - Heterogeneous queries require the ANSI_NULLS and
ANSI_WARNINGS options to be set for the connection. This ensures consistent
query semantics.
Can someone point me in the right direction?
Kyle!Read about the command SET ANSI_NULLS and SET ANSI_WARNINGS in Books Online.
Ask you programmer from
what type of environment he/she it accessing your SQL Server. Also, if you a
re using stored
procedures, you set ANSI WARNINGS inside the procedure. But ANSI_NULLS you n
eed to set when you
create the procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Kyle Jedrusiak" <kjedrusiak@.princetoninformation.com> wrote in message
news:enFB50CpFHA.3084@.TK2MSFTNGP09.phx.gbl...
>I have a view names WSDetailJobList.
> It contains one field and some joined tables from an MDB file for which we
have set up a linked
> server.
> Here is a snippet of the view
> select
> ...
> EA.EmailAddress,
> ...
> join
> EZWEBPOST...WebPost WP
> on
> WP.ReqID = R.RequirementID
> join
> EZWEBPOST...tblEmailAddresses EA
> on
> EA.EmailAddressID = WP.ReplyEmailAddressID
> ...
> It works perfectly from QA and from EM.
> We have a consultant trying to use the view.
> He's getting...
> nativecode=7405 - Heterogeneous queries require the ANSI_NULLS and ANSI_WA
RNINGS options to be set
> for the connection. This ensures consistent query semantics.
>
> Can someone point me in the right direction?
> Kyle!
>sql

MDB to SDF

Hello, everyone.

I am a new developer of .NET CF. Currently, I have a project that needs transferring tables from a existing Access database to SQL Server 2005 Mobile database. Does anybody know how to do it?

Thanks in advance.

Are you talking about MS Access Database or MS Pocket Access Database. In any case, we dont have any tools for doing that automatically.

However, you can always open two cursors (Datasets) against two DBs and transfer the data one row by row. I know its a slow operation but it is all design time.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Thanks for your help.

It looks like that I have to develop a tool myself.

|||

We are really sorry that we could not help you much in this regard. But we would surely take this as feedback and work on this seriously.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Raise an issue (as a suggestion) in the public Feedback Database if you'd like such a tool to be included in a future SQL Server release:

http://lab.msdn.microsoft.com/productfeedback/

|||

Are you talking about MS Access Database or MS Pocket Access Database. In any case, we dont have any tools for doing that automatically.

However, you can always open two cursors (Datasets) against two DBs and transfer the data one row by row. I know its a slow operation but it is all design time.

Is there any walkthru that I can perview on exactly how to do that?

MDB to SDF

Hello, everyone.

I am a new developer of .NET CF. Currently, I have a project that needs transferring tables from a existing Access database to SQL Server 2005 Mobile database. Does anybody know how to do it?

Thanks in advance.

Are you talking about MS Access Database or MS Pocket Access Database. In any case, we dont have any tools for doing that automatically.

However, you can always open two cursors (Datasets) against two DBs and transfer the data one row by row. I know its a slow operation but it is all design time.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Thanks for your help.

It looks like that I have to develop a tool myself.

|||

We are really sorry that we could not help you much in this regard. But we would surely take this as feedback and work on this seriously.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Raise an issue (as a suggestion) in the public Feedback Database if you'd like such a tool to be included in a future SQL Server release:

http://lab.msdn.microsoft.com/productfeedback/

|||

Are

you talking about MS Access Database or MS Pocket Access Database. In

any case, we dont have any tools for doing that automatically.

However, you can always open two cursors (Datasets) against two DBs

and transfer the data one row by row. I know its a slow operation but

it is all design time.

Is there any walkthru that I can perview on exactly how to do that?

MDAC error on VFP linked server

I have a linked server pointing to a folder where foxpro tables exist.
The tables are listed when I click on the linked server/tables.
However I get this error when in query analyer I say: select * from
vfplinked...clients
Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.
OLE DB error trace [Non-interface error: Invalid schema or catalog
specified for the provider.].
Any help? What am I missing? I got this working before.
This is on SQLServer 2000
Thanks everyone!Hi Michael,
Have you tried using the latest Visual FoxPro OLE DB data provider? It's
downloadable from
http://msdn.microsoft.com/vfoxpro/d...es/default.aspx and works
with all versions of FoxPro tables.
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Michael" <Michael.Rodriguez@.nospam> wrote in message
news:etkRi$bBFHA.2180@.TK2MSFTNGP10.phx.gbl...
> I have a linked server pointing to a folder where foxpro tables exist.
> The tables are listed when I click on the linked server/tables.
> However I get this error when in query analyer I say: select * from
> vfplinked...clients
> Server: Msg 7313, Level 16, State 1, Line 1
> Invalid schema or catalog specified for provider 'MSDASQL'.
> OLE DB error trace [Non-interface error: Invalid schema or catalog
> specified for the provider.].
>
> Any help? What am I missing? I got this working before.
> This is on SQLServer 2000
> Thanks everyone!
>

MDAC error on VFP linked server

I have a linked server pointing to a folder where foxpro tables exist.
The tables are listed when I click on the linked server/tables.
However I get this error when in query analyer I say: select * from
vfplinked...clients
Server: Msg 7313, Level 16, State 1, Line 1
Invalid schema or catalog specified for provider 'MSDASQL'.
OLE DB error trace [Non-interface error: Invalid schema or catalog
specified for the provider.].
Any help? What am I missing? I got this working before.
This is on SQLServer 2000
Thanks everyone!
Hi Michael,
Have you tried using the latest Visual FoxPro OLE DB data provider? It's
downloadable from
http://msdn.microsoft.com/vfoxpro/do...s/default.aspx and works
with all versions of FoxPro tables.
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Michael" <Michael.Rodriguez@.nospam> wrote in message
news:etkRi$bBFHA.2180@.TK2MSFTNGP10.phx.gbl...
> I have a linked server pointing to a folder where foxpro tables exist.
> The tables are listed when I click on the linked server/tables.
> However I get this error when in query analyer I say: select * from
> vfplinked...clients
> Server: Msg 7313, Level 16, State 1, Line 1
> Invalid schema or catalog specified for provider 'MSDASQL'.
> OLE DB error trace [Non-interface error: Invalid schema or catalog
> specified for the provider.].
>
> Any help? What am I missing? I got this working before.
> This is on SQLServer 2000
> Thanks everyone!
>
sql

May I have your purmisshun?

Hi;

I'm a novice, and I need to set permissions in SQL Server 2005 (with Server Mgmt Studio)
so that I can create, modify, and delete tables in a database through my ASP.NET application.
I can't seem to find anything that spells this out - step-by-step - so that any idiot can follow it.

This is my error message:
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
### Exception Number : 262
### (CREATE TABLE permission denied in database "MyDBase''.)
### Message: CREATE TABLE permission denied in database "MyDBase''. Number: 262
### Procedure: Server: SYS-EML8V3\SQL2005D01
### Source: .Net SqlClient Data Provider State: 1 Severity: 14 LineNumber: 1
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

Does anyone have a link to something that might give me a clue?

THANKS!

Well, use some kind of tool, for example,Microsoft SQL Server Management Studio Express, and give to your user used in connection string appropriate right:

(under Databases -> Your Database -> Security -> Users)

|||

Dejan Vesic:

Well, use some kind of tool, for example,Microsoft SQL Server Management Studio Express, and give to your user used in connection string appropriate right:

(under Databases -> Your Database -> Security -> Users)

Hi Dejan;

I tried this with the ASPNET user, but it didn't work - I got the same error.

I must be missing something. Is there another user that I'm supposed to be setting this for?

THANKS!

|||

wASP:

Dejan Vesic:

Well, use some kind of tool, for example,Microsoft SQL Server Management Studio Express, and give to your user used in connection string appropriate right:

(under Databases -> Your Database -> Security -> Users)

Hi Dejan;

I tried this with the ASPNET user, but it didn't work - I got the same error.

I must be missing something. Is there another user that I'm supposed to be setting this for?

THANKS!

UPDATE ...

I've given the ASPNET user the "sysadmin" Server role - and it seems to have worked.

My next question is: Have I done something stupid (in terms of compromising security)?

THANKS!

|||

The answer is yes you can use either DBO(database owner) which is still risky but less so than Sysdamin or DDL admin. Try the link below for details. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms189612.aspx

|||

Caddre:

The answer is yes you can use either DBO(database owner) which is still risky but less so than Sysdamin or DDL admin. Try the link below for details. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms189612.aspx

Hi Caddre;

I think I've gotten everything set OK now - thanks to yourself and Dejan - and the link to that article (which I will save).

THANKS AGAIN!

|||

wASP:

Caddre:

The answer is yes you can use either DBO(database owner) which is still risky but less so than Sysdamin or DDL admin. Try the link below for details. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms189612.aspx

Hi Caddre;

I think I've gotten everything set OK now - thanks to yourself and Dejan - and the link to that article (which I will save).

THANKS AGAIN!

I am glad I could help.

Maximum value for Unique ID

Hi,
Am a newbie to sql server so apologies if this question seems a bit
simple but here goes anyway. All our tables are defined with a unique
id column with the identity attribute. What is the maximum value for
this identity column and what happens if this maximum value is reached?
Many thanks in advance
Lee.It depends what type the column was declared as. IDENTITY is a property,
not a type. There is an underlying column type - it could be an integer
type (tinyint, smallint, int, bigint) or a decimal type with scale 0
(decimal(1,0), decimal(2,0), ..., decimal(38,0)). If you exceed the range
of the declared type, you will get an overflow error.
SK
"monkey" <monkey@.email.com> wrote in message
news:bv3ftf$sg7$1$8300dec7@.news.demon.co.uk...

quote:

---

> Hi,
> Am a newbie to sql server so apologies if this question seems a bit
> simple but here goes anyway. All our tables are defined with a unique
> id column with the identity attribute. What is the maximum value for
> this identity column and what happens if this maximum value is reached?
>
> Many thanks in advance
> Lee.
>

---

|||Many thanks for the response steve.
Just to make sure I understand then, if our column that has the identity
attribute is declared as int and the value tries to go above
2,147,483,647 an overflow error will be returned, rather than sql going
back through using any gaps.
Many thanks
Lee.
Steve Kass wrote:

quote:

---

> It depends what type the column was declared as. IDENTITY is a property,
> not a type. There is an underlying column type - it could be an integer
> type (tinyint, smallint, int, bigint) or a decimal type with scale 0
> (decimal(1,0), decimal(2,0), ..., decimal(38,0)). If you exceed the range
> of the declared type, you will get an overflow error.
> SK
>
> "monkey" <monkey@.email.com> wrote in message
> news:bv3ftf$sg7$1$8300dec7@.news.demon.co.uk...
>
>
>

---

|||correct - if you want to see it in action, use dbcc checkident and reseed
with the max value. Note, however, that you can use negative values (in
case you start with 1 as the original seed)
"monkey" <monkey@.email.com> wrote in message
news:bv3ho6$16$1$8302bc10@.news.demon.co.uk...

quote:

---

> Many thanks for the response steve.
> Just to make sure I understand then, if our column that has the identity
> attribute is declared as int and the value tries to go above
> 2,147,483,647 an overflow error will be returned, rather than sql going
> back through using any gaps.
> Many thanks
> Lee.
> Steve Kass wrote:
>
property,[QUOTE]
range[QUOTE]
>

---

|||Yes. The identity property simply generates sequential values. Whether a
value generated ends up in the table or not is irrelevant, as is the actual
data in the table. The identity property does guarantee uniqueness or that
there will be no gaps.
SK
"monkey" <monkey@.email.com> wrote in message
news:bv3ho6$16$1$8302bc10@.news.demon.co.uk...

quote:

---

> Many thanks for the response steve.
> Just to make sure I understand then, if our column that has the identity
> attribute is declared as int and the value tries to go above
> 2,147,483,647 an overflow error will be returned, rather than sql going
> back through using any gaps.
> Many thanks
> Lee.
> Steve Kass wrote:
>
property,[QUOTE]
range[QUOTE]
>

---

|||Many thanks for all the responses, with your help I think I have a
pretty good understanding now.
Thanks again
lee.
monkey wrote:

quote:

---

> Many thanks for the response steve.
> Just to make sure I understand then, if our column that has the identity
> attribute is declared as int and the value tries to go above
> 2,147,483,647 an overflow error will be returned, rather than sql going
> back through using any gaps.
> Many thanks
> Lee.
> Steve Kass wrote:
>
>

---

Maximum value for Unique ID

Hi,
Am a newbie to sql server so apologies if this question seems a bit
simple but here goes anyway. All our tables are defined with a unique
id column with the identity attribute. What is the maximum value for
this identity column and what happens if this maximum value is reached?
Many thanks in advance
Lee.It depends what type the column was declared as. IDENTITY is a property,
not a type. There is an underlying column type - it could be an integer
type (tinyint, smallint, int, bigint) or a decimal type with scale 0
(decimal(1,0), decimal(2,0), ..., decimal(38,0)). If you exceed the range
of the declared type, you will get an overflow error.
SK
"monkey" <monkey@.email.com> wrote in message
news:bv3ftf$sg7$1$8300dec7@.news.demon.co.uk...
> Hi,
> Am a newbie to sql server so apologies if this question seems a bit
> simple but here goes anyway. All our tables are defined with a unique
> id column with the identity attribute. What is the maximum value for
> this identity column and what happens if this maximum value is reached?
>
> Many thanks in advance
> Lee.
>|||Many thanks for the response steve.
Just to make sure I understand then, if our column that has the identity
attribute is declared as int and the value tries to go above
2,147,483,647 an overflow error will be returned, rather than sql going
back through using any gaps.
Many thanks
Lee.
Steve Kass wrote:
> It depends what type the column was declared as. IDENTITY is a property,
> not a type. There is an underlying column type - it could be an integer
> type (tinyint, smallint, int, bigint) or a decimal type with scale 0
> (decimal(1,0), decimal(2,0), ..., decimal(38,0)). If you exceed the range
> of the declared type, you will get an overflow error.
> SK
>
> "monkey" <monkey@.email.com> wrote in message
> news:bv3ftf$sg7$1$8300dec7@.news.demon.co.uk...
>>Hi,
>>Am a newbie to sql server so apologies if this question seems a bit
>>simple but here goes anyway. All our tables are defined with a unique
>>id column with the identity attribute. What is the maximum value for
>>this identity column and what happens if this maximum value is reached?
>>
>>Many thanks in advance
>>Lee.
>
>|||correct - if you want to see it in action, use dbcc checkident and reseed
with the max value. Note, however, that you can use negative values (in
case you start with 1 as the original seed)
"monkey" <monkey@.email.com> wrote in message
news:bv3ho6$16$1$8302bc10@.news.demon.co.uk...
> Many thanks for the response steve.
> Just to make sure I understand then, if our column that has the identity
> attribute is declared as int and the value tries to go above
> 2,147,483,647 an overflow error will be returned, rather than sql going
> back through using any gaps.
> Many thanks
> Lee.
> Steve Kass wrote:
> > It depends what type the column was declared as. IDENTITY is a
property,
> > not a type. There is an underlying column type - it could be an integer
> > type (tinyint, smallint, int, bigint) or a decimal type with scale 0
> > (decimal(1,0), decimal(2,0), ..., decimal(38,0)). If you exceed the
range
> > of the declared type, you will get an overflow error.
> >
> > SK
> >
> >
> > "monkey" <monkey@.email.com> wrote in message
> > news:bv3ftf$sg7$1$8300dec7@.news.demon.co.uk...
> >
> >>Hi,
> >>
> >>Am a newbie to sql server so apologies if this question seems a bit
> >>simple but here goes anyway. All our tables are defined with a unique
> >>id column with the identity attribute. What is the maximum value for
> >>this identity column and what happens if this maximum value is reached?
> >>
> >>
> >>Many thanks in advance
> >>
> >>Lee.
> >>
> >
> >
> >
>|||Yes. The identity property simply generates sequential values. Whether a
value generated ends up in the table or not is irrelevant, as is the actual
data in the table. The identity property does guarantee uniqueness or that
there will be no gaps.
SK
"monkey" <monkey@.email.com> wrote in message
news:bv3ho6$16$1$8302bc10@.news.demon.co.uk...
> Many thanks for the response steve.
> Just to make sure I understand then, if our column that has the identity
> attribute is declared as int and the value tries to go above
> 2,147,483,647 an overflow error will be returned, rather than sql going
> back through using any gaps.
> Many thanks
> Lee.
> Steve Kass wrote:
> > It depends what type the column was declared as. IDENTITY is a
property,
> > not a type. There is an underlying column type - it could be an integer
> > type (tinyint, smallint, int, bigint) or a decimal type with scale 0
> > (decimal(1,0), decimal(2,0), ..., decimal(38,0)). If you exceed the
range
> > of the declared type, you will get an overflow error.
> >
> > SK
> >
> >
> > "monkey" <monkey@.email.com> wrote in message
> > news:bv3ftf$sg7$1$8300dec7@.news.demon.co.uk...
> >
> >>Hi,
> >>
> >>Am a newbie to sql server so apologies if this question seems a bit
> >>simple but here goes anyway. All our tables are defined with a unique
> >>id column with the identity attribute. What is the maximum value for
> >>this identity column and what happens if this maximum value is reached?
> >>
> >>
> >>Many thanks in advance
> >>
> >>Lee.
> >>
> >
> >
> >
>|||Many thanks for all the responses, with your help I think I have a
pretty good understanding now.
Thanks again
lee.
monkey wrote:
> Many thanks for the response steve.
> Just to make sure I understand then, if our column that has the identity
> attribute is declared as int and the value tries to go above
> 2,147,483,647 an overflow error will be returned, rather than sql going
> back through using any gaps.
> Many thanks
> Lee.
> Steve Kass wrote:
>> It depends what type the column was declared as. IDENTITY is a property,
>> not a type. There is an underlying column type - it could be an integer
>> type (tinyint, smallint, int, bigint) or a decimal type with scale 0
>> (decimal(1,0), decimal(2,0), ..., decimal(38,0)). If you exceed the
>> range
>> of the declared type, you will get an overflow error.
>> SK
>>
>> "monkey" <monkey@.email.com> wrote in message
>> news:bv3ftf$sg7$1$8300dec7@.news.demon.co.uk...
>> Hi,
>> Am a newbie to sql server so apologies if this question seems a bit
>> simple but here goes anyway. All our tables are defined with a unique
>> id column with the identity attribute. What is the maximum value for
>> this identity column and what happens if this maximum value is reached?
>>
>> Many thanks in advance
>> Lee.
>>
>>
>

Maximum Numbers of Tables and colums to access in select statement

Hi

I want to get the
Maximum Numbers of Tables and colums to access in select query statement

Regards
AruneshCitations from SQL Books Online:columns: The maximum number of expressions that can be specified in the select list is 4096

Maximum number of updates within a Transaction

Good day to all

SQL Server 2000.

I have a problem with a piece of code, which updates some tables using transaction. This process brings the program to a halt when updating large files.

With smaller files, the process finishes without problems.

I have noticed that, if a comment out the "begin transaction" and respective "commit", the same code executes without problems, even when updating large files.

I suspect that there is a limit on the number of records a transaction can hold before a commit is issued. I am surprised however, that SQL Server halts, without messages or warnings.

Is this a configuration issue? If there is a limit on the number of records a transaction can hold, what is this limit:? Anything I can do to have a warning form SQL Server when a situation like this is reached (or indeed to avoid this situation) ?

Thank you in advance for any help

Cecil Ricardo

hi cecil,

my guess is that the transaction is being involved in a recursive operation which means it triggers something and then it triggers back the transaction in a continous loop. thats why it halts your system

With out the begin transaction and commit transaction clause recursive operations end until 32 layers deep. Which cause your batch or sp to commit sucessfully

regards,

joey

|||

There are no limits to the number of rows you can commit in a transaction. It is bound by the size of your transaction log file(s). Did you check for any error messages in the errorlog? Note that the delay you may have observed was probably due to the commit operation itself. Depending on the number of changes you did within the transaction a commit/rollback can take a long time. And the semantics for using begin / commit for say 100 insert statements is different from executing 100 individual insert statements. So you cannot really compare the two approaches. By default, if you do not specify a begin/commit transaction the statement runs in auto-commit mode meaning each statement is a transaction by itself. Often, you will get better performance by using begin transaction/commit due to buffering of log writes. The batch size however depends on lot of factors. So do the following:

1. If you really need to use a transaction for updating a large table then ensure that you have enough space in your drives to account for log growth. Time for commit/rollback depends on the changes

2. If you just need to update a large number of rows then you can use a batch mechanims where you commit only say 1000 rows at a time and use a simple loop. You can use SET ROWCOUNT to restrict the rows affected by DML statements in older versions of SQL Server and use TOP clause in SQL Server 2005

3. Transactional consistency and semantics is different if you run a bunch of DML statements within a transaction vs running each DML statement separately. They are not the same so you need to be aware of the differences. See Books Online topics on auto-commit transactions and user specified transactions.

|||

I have checked that. It is not the case.

But thank you for the valuable information about the end of recursive operations ending at 32 layers deep.

Thank you Joey

|||

Thank you so much for the comprehensive explanation, Umachandar.

I have applyied your suggestion aof commiting smaller chunks of rows. It worked fine.

Maximum number of tables?

What is the maximum number of tables that SQL Server 2000 can cope with?Joe wrote:
> What is the maximum number of tables that SQL Server 2000 can cope with?
No specification for tables only, but it can contain 2,147,483,647
objects overall.|||From BOL:
"Database objects include all tables, views, stored procedures, extended
stored procedures, triggers, rules, defaults, and constraints. The sum of
the number of all these objects in a database cannot exceed 2,147,483,647."
... But you said, "cope with". That's a different question. The question
isn't how many tables--but what you're doing with those tables. I can bring
a big server to its knees with a single large, very badly designed table.
So coping is not about number of objects, but how they're used.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Joe" <joe@.hotmail.com> wrote in message
news:%23cEOzRcoFHA.3960@.TK2MSFTNGP12.phx.gbl...
> What is the maximum number of tables that SQL Server 2000 can cope with?
>

Maximum number of tables?

What is the maximum number of tables that SQL Server 2000 can cope with?
Joe wrote:
> What is the maximum number of tables that SQL Server 2000 can cope with?
No specification for tables only, but it can contain 2,147,483,647
objects overall.
|||From BOL:
"Database objects include all tables, views, stored procedures, extended
stored procedures, triggers, rules, defaults, and constraints. The sum of
the number of all these objects in a database cannot exceed 2,147,483,647."
... But you said, "cope with". That's a different question. The question
isn't how many tables--but what you're doing with those tables. I can bring
a big server to its knees with a single large, very badly designed table.
So coping is not about number of objects, but how they're used.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Joe" <joe@.hotmail.com> wrote in message
news:%23cEOzRcoFHA.3960@.TK2MSFTNGP12.phx.gbl...
> What is the maximum number of tables that SQL Server 2000 can cope with?
>

Maximum number of tables?

What is the maximum number of tables that SQL Server 2000 can cope with?Joe wrote:
> What is the maximum number of tables that SQL Server 2000 can cope with?
No specification for tables only, but it can contain 2,147,483,647
objects overall.|||From BOL:
"Database objects include all tables, views, stored procedures, extended
stored procedures, triggers, rules, defaults, and constraints. The sum of
the number of all these objects in a database cannot exceed 2,147,483,647."
... But you said, "cope with". That's a different question. The question
isn't how many tables--but what you're doing with those tables. I can bring
a big server to its knees with a single large, very badly designed table.
So coping is not about number of objects, but how they're used.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Joe" <joe@.hotmail.com> wrote in message
news:%23cEOzRcoFHA.3960@.TK2MSFTNGP12.phx.gbl...
> What is the maximum number of tables that SQL Server 2000 can cope with?
>

Maximum number of tables?

What is the maximum number of tables that SQL Server 2000 can cope with?Joe wrote:
> What is the maximum number of tables that SQL Server 2000 can cope with?
No specification for tables only, but it can contain 2,147,483,647
objects overall.|||From BOL:
"Database objects include all tables, views, stored procedures, extended
stored procedures, triggers, rules, defaults, and constraints. The sum of
the number of all these objects in a database cannot exceed 2,147,483,647."
... But you said, "cope with". That's a different question. The question
isn't how many tables--but what you're doing with those tables. I can bring
a big server to its knees with a single large, very badly designed table.
So coping is not about number of objects, but how they're used.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Joe" <joe@.hotmail.com> wrote in message
news:%23cEOzRcoFHA.3960@.TK2MSFTNGP12.phx.gbl...
> What is the maximum number of tables that SQL Server 2000 can cope with?
>

maximum number of tables per select?

Does anyone know what is the maximum number of tables per select statement
in Oracle and SQL Server 2000?
Thanks a lot,
Lixin
I don't know it for Oracle, but for SQL Server 2000 it's 256.
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>
|||I don't know it for Oracle, but for SQL Server 2000 it's 256.
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>
|||SQL Server 2000: 256
For Oracle: you probably want to check in an Oracle group for that one.
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>
|||SQL Server 2000: 256
For Oracle: you probably want to check in an Oracle group for that one.
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>
|||That would be 256. Here are all the max capacities for SQL Server.
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>
|||That would be 256. Here are all the max capacities for SQL Server.
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>
|||Thanks all of you guys.
256 limitation caused me problem. I will post separately.
Lixin
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eb%23SMoVXEHA.1656@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> I don't know it for Oracle, but for SQL Server 2000 it's 256.
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
statement
>
|||Thanks all of you guys.
256 limitation caused me problem. I will post separately.
Lixin
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eb%23SMoVXEHA.1656@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> I don't know it for Oracle, but for SQL Server 2000 it's 256.
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
statement
>

maximum number of tables per select?

Does anyone know what is the maximum number of tables per select statement
in Oracle and SQL Server 2000?
Thanks a lot,
LixinI don't know it for Oracle, but for SQL Server 2000 it's 256.
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>|||SQL Server 2000: 256
For Oracle: you probably want to check in an Oracle group for that one.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>|||That would be 256. Here are all the max capacities for SQL Server.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>|||Thanks all of you guys.
256 limitation caused me problem. I will post separately.
Lixin
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eb%23SMoVXEHA.1656@.TK2MSFTNGP09.phx.gbl...
> I don't know it for Oracle, but for SQL Server 2000 it's 256.
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> > Does anyone know what is the maximum number of tables per select
statement
> > in Oracle and SQL Server 2000?
> >
> > Thanks a lot,
> > Lixin
> >
> >
>

maximum number of tables per select?

Does anyone know what is the maximum number of tables per select statement
in Oracle and SQL Server 2000?
Thanks a lot,
LixinI don't know it for Oracle, but for SQL Server 2000 it's 256.
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>|||SQL Server 2000: 256
For Oracle: you probably want to check in an Oracle group for that one.
http://www.aspfaq.com/
(Reverse address to reply.)
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>|||That would be 256. Here are all the max capacities for SQL Server.
8dbn.asp" target="_blank">http://msdn.microsoft.com/library/d...br />
8dbn.asp
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"FLX" <nospam@.hotmail.com> wrote in message
news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
> Does anyone know what is the maximum number of tables per select statement
> in Oracle and SQL Server 2000?
> Thanks a lot,
> Lixin
>|||Thanks all of you guys.
256 limitation caused me problem. I will post separately.
Lixin
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:eb%23SMoVXEHA.1656@.TK2MSFTNGP09.phx.gbl...
> I don't know it for Oracle, but for SQL Server 2000 it's 256.
>
> "FLX" <nospam@.hotmail.com> wrote in message
> news:O2iItbVXEHA.2844@.TK2MSFTNGP12.phx.gbl...
statement[vbcol=seagreen]
>

Maximum number of tables in a sql-server database

What is the maximum number of tables in a sql-server database?There is nothing specific for tables but the tables would
fall under the maximum number of objects allowed in a
database which is 2,147,483,647
-Sue
On Tue, 23 Mar 2004 00:42:23 +0100, "twh" <thorwhammer - @. -
hotmail.com> wrote:

>What is the maximum number of tables in a sql-server database?
>