General Network error even good connection and small amount of data

Hy everyone,

I have a problem at pulling data on PDA using Sql Mobile 5 from Sql Server 2000.

It work s for most tables, except for two . Every table of those two has about 1000 records and every record about 150 bytes, so all data is about 150 kbytes.

When I connect to the Sql Server 2000 through LAN, no problem. But when I connect through GPRS - VPN, the error occurs. But, the PDA- phone connection is via Bluetooth and at ping no TimeOut appears, the replies are about 600-700 ms. When connecting with a desktop computer through GPRS no such problem occurs.

So , I don't know what could be the cause: the connection or the Sql Mobile which doesn't retry enough in case of error to transfer all data .

A specialist from the GPRS provider told me to use 3G phone , but , because the GPRS phones have already been bought, this would be a too big investment. So, I don't think this is a good idea in this moment .

I found similar problem on the net and a solution was to use Pooling = False. But , in Sql Mobile there is no pulling parameter available when making the connection string .

Any help is very precious to me ,

Mihai .

Hi Mihai,

Seems like you are using a GPRS phone to act like a bluetooth modem for a PDA. This is a tested scenario, and would work :) A couple of issues i can imagine right-away:

1. From the ping round trip time of 600 ms, i guess the synchronization must be timing-out at the TCP/IP level. Below are some time-out properties (HTTP &TCP/IP) of the merge replication and RDA programming objects, which can be set to optimize synchronization over various network bandwidths.

(HTTP)

ConnectionRetryTimeout: Specifies the time, in seconds, to continue to retry sending requests after an established connection has failed.

(TCP/IP)

ConnectTimeout: Specifies the time, in milliseconds, to wait for a connection to the server.

ReceiveTimeout: Specifies the time, in milliseconds, to wait for the response to a server request.

SendTimeout: Specifies the time, in milliseconds, to wait to send a request to the server.

When data is synchronized over a cellular or mobile network, requests sent to the server, and responses from the server can time out. This occurs when a low-bandwidth connection requires too much time to transmit the data. In your case, you have an overhead of the bluetooth transport as well! These time-outs would cause synchronization to fail, which you can solve by specifying a longer time-out value. Try setting a huge value (60000 ms) in your code for the TCP/IP Connect and Send/Recieve Timeouts on the merge replication/RDA object. This mostly should solve your problem.

2. Bluetooth DUN Profile - I assume you are using the Dial-Up Networking profile for the phone to act as a GPRS modem. Are you sure there are no pairing issues? During our tests, we faced a lot of issues with the bluetooth pairing - especially between WM 5.0 PDA's and third party mobile devices.

Let me know if this works out!

Regards,

Sravanth Aluru

|||

When inserting the parameters you specify for the connection string I get an error that says "Unknown connection option ..." .

And for the SqlConnection object there are no properties like those. Only PacketSize, ConnectionTimeout .

I forgot to say previously that I put in connection string "Packet Size=512" and "ConnectionTimeout=90". I tried even with ConnectionTimeout=180. No change.

Do I miss anything, any assembly declaration ?

Could give me another advice ?

Mihai.

|||

The parameters are available in SQL Mobile 3.1 Beta which shipped with Visual Studio 2005 SP1 and onwards.

Sorry for missing this information out in my earlier response!

Can you specify which version you are on?

/Sravanth

|||

I have Sql Mobile 3.00. So , now I am downloading it . I'll reply as soon as I have news.

Thanks a lot,

Mihai.

|||

I can't get sql mobile 3.1

I just installed VS Professional, then SP1. Only Sql mobile 3.0. Anyway, in VS when I try to add reference to System.Data.Sqlclient it shows 3.0 version .

I tried to connect with SendTimeout parameter in the connection string . But I get error which says it is unknown parameter.

I even installed Sql everywhere because I saw it was 3.1 version. Nothing .

I also installed 3_SQL Server 2005 Mobile Edition Server Tools ...

I don't know what else to do.

Any suggestion ?

Thank you,

Mihai .

|||

Yes, I see. It's TCP/IP level ... sorry .

Of course I won't have those parameters in connection string. But how can I work at stream level and use the SQL commands to get data ? Everywhere in docs, when using TcpClient class, the stream is used for sending/receiving data .

But, how do I do to use these TCP/IP parameters if I work at IP level, with sql connection and then sql commands ?

Please give me some advices , I'm a rookie in working with these protocols.

Thanks a lot,

Mihai.

|||

This is a sample of how I get data:

SqlConnection s2000conn = new SqlConnection("Server=10.1.1.10 ; Connection Timeout=60 ; Packet Size = 512 ; UId=user;Pwd=pwd;Database=GestSC");

try

{

s2000conn.Open();

}

catch (SqlException eroare)

{

MessageBox.Show("Connection error", "Eroare Sql");

return;

}

SqlCommand sqlcoms = new SqlCommand();

sqlcoms.CommandText = "SELECT * FROM part ";

SqlDataAdapter da_part = new SqlDataAdapter(sqlcoms.CommandText, s2000conn);

DataSet ds_part = new DataSet();

DataTable dt_part = ds_part.Tables.Add("downpart");

da_part.Fill(ds_part, "downpart");

.....

Then I insert this data in Sql Mobile database .

But how would I use TcpClient ?

TcpClient tc = new TcpClient();

tc.SendTimeout = 90;

And then ?

Thank you ,

Mihai .

|||

Man ,

Is it that simple that it doesn't really worth the effort or you have no answer ?

Can you say smth ?

Thanks anyway,

Mihaila.

|||

Hy all,

I solved the problem by breaking the results into many smaller packets.

First I counted the total number of records and then Fill-ed the cursor with a for-end loop. Simple, but initially I wanted to work in the default mode, not to write code.

And it works.

Finally I heared from the GPRS provider that the GPRS allows small packets of data (maximum 2048 for Packet Size preffered) and the transport of data is not very reliable, so small amount of data should be transfered at one time.

Thank you anyway for all your replies,

Mihaila.

General Network error even good connection and small amount of data

Hy everyone,

I have a problem at pulling data on PDA using Sql Mobile 5 from Sql Server 2000.

It work s for most tables, except for two . Every table of those two has about 1000 records and every record about 150 bytes, so all data is about 150 kbytes.

When I connect to the Sql Server 2000 through LAN, no problem. But when I connect through GPRS - VPN, the error occurs. But, the PDA- phone connection is via Bluetooth and at ping no TimeOut appears, the replies are about 600-700 ms. When connecting with a desktop computer through GPRS no such problem occurs.

So , I don't know what could be the cause: the connection or the Sql Mobile which doesn't retry enough in case of error to transfer all data .

A specialist from the GPRS provider told me to use 3G phone , but , because the GPRS phones have already been bought, this would be a too big investment. So, I don't think this is a good idea in this moment .

I found similar problem on the net and a solution was to use Pooling = False. But , in Sql Mobile there is no pulling parameter available when making the connection string .

Any help is very precious to me ,

Mihai .

Hi Mihai,

Seems like you are using a GPRS phone to act like a bluetooth modem for a PDA. This is a tested scenario, and would work :) A couple of issues i can imagine right-away:

1. From the ping round trip time of 600 ms, i guess the synchronization must be timing-out at the TCP/IP level. Below are some time-out properties (HTTP &TCP/IP) of the merge replication and RDA programming objects, which can be set to optimize synchronization over various network bandwidths.

(HTTP)

ConnectionRetryTimeout: Specifies the time, in seconds, to continue to retry sending requests after an established connection has failed.

(TCP/IP)

ConnectTimeout: Specifies the time, in milliseconds, to wait for a connection to the server.

ReceiveTimeout: Specifies the time, in milliseconds, to wait for the response to a server request.

SendTimeout: Specifies the time, in milliseconds, to wait to send a request to the server.

When data is synchronized over a cellular or mobile network, requests sent to the server, and responses from the server can time out. This occurs when a low-bandwidth connection requires too much time to transmit the data. In your case, you have an overhead of the bluetooth transport as well! These time-outs would cause synchronization to fail, which you can solve by specifying a longer time-out value. Try setting a huge value (60000 ms) in your code for the TCP/IP Connect and Send/Recieve Timeouts on the merge replication/RDA object. This mostly should solve your problem.

2. Bluetooth DUN Profile - I assume you are using the Dial-Up Networking profile for the phone to act as a GPRS modem. Are you sure there are no pairing issues? During our tests, we faced a lot of issues with the bluetooth pairing - especially between WM 5.0 PDA's and third party mobile devices.

Let me know if this works out!

Regards,

Sravanth Aluru

|||

When inserting the parameters you specify for the connection string I get an error that says "Unknown connection option ..." .

And for the SqlConnection object there are no properties like those. Only PacketSize, ConnectionTimeout .

I forgot to say previously that I put in connection string "Packet Size=512" and "ConnectionTimeout=90". I tried even with ConnectionTimeout=180. No change.

Do I miss anything, any assembly declaration ?

Could give me another advice ?

Mihai.

|||

The parameters are available in SQL Mobile 3.1 Beta which shipped with Visual Studio 2005 SP1 and onwards.

Sorry for missing this information out in my earlier response!

Can you specify which version you are on?

/Sravanth

|||

I have Sql Mobile 3.00. So , now I am downloading it . I'll reply as soon as I have news.

Thanks a lot,

Mihai.

|||

I can't get sql mobile 3.1

I just installed VS Professional, then SP1. Only Sql mobile 3.0. Anyway, in VS when I try to add reference to System.Data.Sqlclient it shows 3.0 version .

I tried to connect with SendTimeout parameter in the connection string . But I get error which says it is unknown parameter.

I even installed Sql everywhere because I saw it was 3.1 version. Nothing .

I also installed 3_SQL Server 2005 Mobile Edition Server Tools ...

I don't know what else to do.

Any suggestion ?

Thank you,

Mihai .

|||

Yes, I see. It's TCP/IP level ... sorry .

Of course I won't have those parameters in connection string. But how can I work at stream level and use the SQL commands to get data ? Everywhere in docs, when using TcpClient class, the stream is used for sending/receiving data .

But, how do I do to use these TCP/IP parameters if I work at IP level, with sql connection and then sql commands ?

Please give me some advices , I'm a rookie in working with these protocols.

Thanks a lot,

Mihai.

|||

This is a sample of how I get data:

SqlConnection s2000conn = new SqlConnection("Server=10.1.1.10 ; Connection Timeout=60 ; Packet Size = 512 ; UId=user;Pwd=pwd;Database=GestSC");

try

{

s2000conn.Open();

}

catch (SqlException eroare)

{

MessageBox.Show("Connection error", "Eroare Sql");

return;

}

SqlCommand sqlcoms = new SqlCommand();

sqlcoms.CommandText = "SELECT * FROM part ";

SqlDataAdapter da_part = new SqlDataAdapter(sqlcoms.CommandText, s2000conn);

DataSet ds_part = new DataSet();

DataTable dt_part = ds_part.Tables.Add("downpart");

da_part.Fill(ds_part, "downpart");

.....

Then I insert this data in Sql Mobile database .

But how would I use TcpClient ?

TcpClient tc = new TcpClient();

tc.SendTimeout = 90;

And then ?

Thank you ,

Mihai .

|||

Man ,

Is it that simple that it doesn't really worth the effort or you have no answer ?

Can you say smth ?

Thanks anyway,

Mihaila.

|||

Hy all,

I solved the problem by breaking the results into many smaller packets.

First I counted the total number of records and then Fill-ed the cursor with a for-end loop. Simple, but initially I wanted to work in the default mode, not to write code.

And it works.

Finally I heared from the GPRS provider that the GPRS allows small packets of data (maximum 2048 for Packet Size preffered) and the transport of data is not very reliable, so small amount of data should be transfered at one time.

Thank you anyway for all your replies,

Mihaila.

sql

General Network Error

Hello,
A User currently having a problem with an Access 2000 application that uses
Linked tables to connect to an SQL 2005 database. The user receives the
following message:
Connection failed:
SQLState: ‘01000’
SQL Server Error: 11004
[Microsoft ][ODBC SQL Server Driver][TCP/IP Sockets]
ConnectionOpen(gethostbyname()()).
Connection failed:
SQLState: ‘08001’
SQL Server Error: 11
[Microsoft ][ODBC SQL Server Driver][TCP/IP Sockets] General net
work error.
Check your network documentation.
The application was working for the user but it stopped working after a help
desk person had done some things to her machine to get another application
working. Anybody have an idea on what may have changed to cause this to
happen?
The Users workstation is Windows 2000 SP4. I can also ping and telnet to the
SQL server from that machine.It can be caused by different things but I've seen MDAC
versions be the cause of such problems before. Make sure the
PC has the latest MDAC version. You can download MDAC
versions and also download component checker to check the
MDAC version from:
http://msdn.microsoft.com/data/ref/mdac/downloads/
You would also want to check the event logs on the PC for
any network related issues.
-Sue
On Wed, 26 Jul 2006 08:46:02 -0700, Bill Klein
<BillKlein@.discussions.microsoft.com> wrote:

>Hello,
>A User currently having a problem with an Access 2000 application that uses
>Linked tables to connect to an SQL 2005 database. The user receives the
>following message:
>Connection failed:
>SQLState: 01000
>SQL Server Error: 11004
>[Microsoft ][ODBC SQL Server Driver][TCP/IP Sockets]
>ConnectionOpen(gethostbyname()()).
>Connection failed:
>SQLState: 08001
>SQL Server Error: 11
>[Microsoft ][ODBC SQL Server Driver][TCP/IP Sockets] General ne
twork error.
>Check your network documentation.
>The application was working for the user but it stopped working after a hel
p
>desk person had done some things to her machine to get another application
>working. Anybody have an idea on what may have changed to cause this to
>happen?
>The Users workstation is Windows 2000 SP4. I can also ping and telnet to th
e
>SQL server from that machine.|||Thanks Sue, the computer had version 2.53 i've upgraded it to 2.8 sp1 and
everything seems to work fine now.
"Sue Hoegemeier" wrote:

> It can be caused by different things but I've seen MDAC
> versions be the cause of such problems before. Make sure the
> PC has the latest MDAC version. You can download MDAC
> versions and also download component checker to check the
> MDAC version from:
> http://msdn.microsoft.com/data/ref/mdac/downloads/
> You would also want to check the event logs on the PC for
> any network related issues.
> -Sue
> On Wed, 26 Jul 2006 08:46:02 -0700, Bill Klein
> <BillKlein@.discussions.microsoft.com> wrote:
>
>sql

General design efficiency question

My general question is whether there is anything to be gained by having 50 tables in one database versus 5 tables each in 10 databases.

I have a number of different databases running on a server (SQL Server 2k). The different databases represent different functional groups, for instance car maintenance, cab reservation/dispatch, cab accounting, limo reservation/dispatch, limo accounting, etc. There is some crossover, for instance the cab dispatch system would look to car maintenance to validate the car number entered.

A friend who happens to be IT Director at the local university suggested that the server would run more efficiently if there was only one database, rather than the roughly 12 I have now. His belief is that each separate database carries a certain amount of overhead, and combining them into one would be advantageous.

Is he all wet, or would there be gains to be made?

TIAThere would be gains. It is just a matter of whether you would notice them. I have never tested the scenario, so I have no actual numbers. Personally, I would favor the single database approach, for permissions administration reasons. A user only needs permissions on a stored procedure and not the underlying tables, IF all of the tables it accesses are in the same database. Same for views.

Now, suppose you want to restore the 10 databases back to a point in time just before your temp deleted a pile of data in "some tables". Do you want to do 10 separate restores, one?

I am sure some of the other folks here can come up with other examples, if they tried.|||He's right for the wrong reasons.

I don't think you would get a performance boost from combining the databases. You could make arguments for either increased efficiency or decreased efficiency either way.

But...from a data management standpoint it makes administrative sense to combine the databases if they reference eachother for lookup values or cross-database queries. From you limited description of the situation, I would recommend combining them.|||Thanks for your thoughts. I guess I'll look at going through the effort.|||I read the first line and fell off my barstoo...um office chair

My general question is whether there is anything to be gained by having 50 tables in one database versus 5 tables each in 10 databases.

Are you kidding? Ever hear of maintenance?

I have a number of different databases running on a server (SQL Server 2k). The different databases represent different functional groups, for instance car maintenance, cab reservation/dispatch, cab accounting, limo reservation/dispatch, limo accounting, etc. There is some crossover, for instance the cab dispatch system would look to car maintenance to validate the car number entered.

A friend who happens to be IT Director at the local university suggested that the server would run more efficiently if there was only one database, rather than the roughly 12 I have now. His belief is that each separate database carries a certain amount of overhead, and combining them into one would be advantageous.

Is he all wet, or would there be gains to be made?

TIA

I like the cut of his gib...are all the tables named differently? Also, what about the apps? Would they be hard to port?|||Putting objects in different databases will allow you more flexibility in terms of allocating data and log files. (IE if you have to use primary for select into reasons, you can more easily manage it with multiple databases). Also, depending on your backup requirements you may be able to set some databases to simple mode, some to full, only do trn backups for certain databases and the list goes on. I think it's more a management thing then a performance thing.

-kilka|||Putting objects in different databases will allow you more flexibility in terms of allocating data and log files. (IE if you have to use primary for select into reasons, you can more easily manage it with multiple databases). Also, depending on your backup requirements you may be able to set some databases to simple mode, some to full, only do trn backups for certain databases and the list goes on. I think it's more a management thing then a performance thing.

-kilka

Nope ... and thank you for playing (at least for the first statement). You can create as many segments as you desire for a single database for both data and log files ... non-clustered indexes on multiple disks, split log files, etc., etc., etc.

Not to mention that referential integrity rules can only be enforced with database, not across database.

Also, IMHO, cross database joins requires the engine to drill down thru the metadata of the other databases to access the index and page structures of the "foreign" database since SQLServer "cooks" it database space when it is allocated.

That being said, it would be interesting to see the results of an empirical test. If Paul Randal is still hanging around, maybe he can comment on this topic!|||What do you mean by "cooks" space when it's allocated?

gatting at my data!

Sql Server 2000, Win 2000
Problem accessing tables in SQL Query Analyser:-
' why can't I access the database connected as Jim '
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', own
er 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
' why can't I access the data connected as Jim '
Jim BuntonHi,
Multiple post.
You have created the table as [JIM.Companies]. This created the table wi
th name JIM.COMPANIES. To avaoid this you have to create table
as [JIM].[Companies]
Thanks
Hari
SQL Server MVP
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:jFt1f.75837$iW
5.12732@.fe3.news.blueyonder.co.uk...
Sql Server 2000, Win 2000
Problem accessing tables in SQL Query Analyser:-
' why can't I access the database connected as Jim '
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', own
er 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
' why can't I access the data connected as Jim '
Jim Bunton|||Hi Hari - thanks for your clear response to my posting
enterprise manager reports that I have created a tabale 'Companies' whose ow
ner is Jim
sqlQueryAnalyser reports that SolutionsNet includes the table Jim.Companies
- I an assuming that this means there is a table called companies whose owne
r is Jim.
When connected as Jim(SqlQueryAnalyser) both
select * from companies
or
select * from jim.companies
return the error:-
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', own
er 'Jim'.
After using dbo to dropt the table
CREATE TABLE
[Jim].[Companies]
(CompanyId INT PRIMARY KEY IDENTITY(1,1),
Companyname VarChar(50)NOT NULL,
. . . .
Nothing has changed! same results as before
Drop the table
connect as Jim and run:-
CREATE TABLE
[Jim].[Companies]
(CompanyId INT PRIMARY KEY IDENTITY(1,1),
Companyname
. . . .
The command(s) completed successfully.
Run SqlEnterprise manager - reports the table Companies owner Jim
as 'Jim' I still can't access the table
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', own
er 'Jim'.
But I can as sa/dbo
BUT if I create a Windows Authentification user [Study/Jim Bunton] and c
onnect then I can access the table!!! - Select user_name informs me that I'm
dbo [I can still access the table whether or not Select . . permission
s are set tick or cross.
I'm totally mystified!
NOW? - Might it be because I am running SQL Server Personal edition'
[I am not running win 2k server - just win 2k]
NB
[The irony is that on an .asp page I have totally failed to get a Truste
d Connection (windows authentification] to work for Northwind and resorted t
o using the datase authentification!
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:ONwZyb2yFHA.
2348@.TK2MSFTNGP15.phx.gbl...
Hi,
Multiple post.
You have created the table as [JIM.Companies]. This created the table wi
th name JIM.COMPANIES. To avaoid this you have to create table
as [JIM].[Companies]
Thanks
Hari
SQL Server MVP
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:jFt1f.75837$iW
5.12732@.fe3.news.blueyonder.co.uk...
Sql Server 2000, Win 2000
Problem accessing tables in SQL Query Analyser:-
' why can't I access the database connected as Jim '
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', own
er 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
' why can't I access the data connected as Jim '
Jim Bunton

gatting at my data!

Sql Server 2000, Win 2000
Problem accessing tables in SQL Query Analyser:-
? why can't I access the database connected as Jim ?
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', owner 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
? why can't I access the data connected as Jim ?
Jim Bunton
Hi,
Multiple post.
You have created the table as [JIM.Companies]. This created the table with name JIM.COMPANIES. To avaoid this you have to create table
as [JIM].[Companies]
Thanks
Hari
SQL Server MVP
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:jFt1f.75837$iW5.12732@.fe3.news.blueyonder.co. uk...
Sql Server 2000, Win 2000
Problem accessing tables in SQL Query Analyser:-
? why can't I access the database connected as Jim ?
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', owner 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
? why can't I access the data connected as Jim ?
Jim Bunton
|||Hi Hari - thanks for your clear response to my posting
enterprise manager reports that I have created a tabale 'Companies' whose owner is Jim
sqlQueryAnalyser reports that SolutionsNet includes the table Jim.Companies - I an assuming that this means there is a table called companies whose owner is Jim.
When connected as Jim(SqlQueryAnalyser) both
select * from companies
or
select * from jim.companies
return the error:-
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', owner 'Jim'.
After using dbo to dropt the table
CREATE TABLE
[Jim].[Companies]
(CompanyId INT PRIMARY KEY IDENTITY(1,1),
Companyname VarChar(50)NOT NULL,
.. . . .
Nothing has changed! same results as before
Drop the table
connect as Jim and run:-
CREATE TABLE
[Jim].[Companies]
(CompanyId INT PRIMARY KEY IDENTITY(1,1),
Companyname
. . . .
The command(s) completed successfully.
Run SqlEnterprise manager - reports the table Companies owner Jim
as 'Jim' I still can't access the table
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', owner 'Jim'.
But I can as sa/dbo
BUT if I create a Windows Authentification user [Study/Jim Bunton] and connect then I can access the table!!! - Select user_name informs me that I'm dbo [I can still access the table whether or not Select . . permissions are set tick or cross.
I'm totally mystified!
NOW? - Might it be because I am running SQL Server Personal edition?
[I am not running win 2k server - just win 2k]
NB
[The irony is that on an .asp page I have totally failed to get a Trusted Connection (windows authentification] to work for Northwind and resorted to using the datase authentification!
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message news:ONwZyb2yFHA.2348@.TK2MSFTNGP15.phx.gbl...
Hi,
Multiple post.
You have created the table as [JIM.Companies]. This created the table with name JIM.COMPANIES. To avaoid this you have to create table
as [JIM].[Companies]
Thanks
Hari
SQL Server MVP
"Jim Bunton" <jBunton@.BlueYonder.co.uk> wrote in message news:jFt1f.75837$iW5.12732@.fe3.news.blueyonder.co. uk...
Sql Server 2000, Win 2000
Problem accessing tables in SQL Query Analyser:-
? why can't I access the database connected as Jim ?
Created Database [SolutionsNet] - Owner Jim
Created Table [Jim.Customers] Owner Jim
Data base access Jim 'permit'
Table - Companies - permissions Jim > Select . . . [the lot]
Jim Permissions - Table Companies > Select . . . [the lot]
Jim - permit in database role > public, db_owners . . [the lot]
BUT
SQL Query Analyser
Connect as Jim
use SolutionsNet
go
select * from jim.companies
go
Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'Companies', database 'SolutionsNet', owner 'Jim'.
[same if table is just 'companies' withot the prefix]
HOWEVER
Connect as sa
use SolutionsNet
go
select * from jim.companies
go
Fine:- displays the empty table!
? why can't I access the data connected as Jim ?
Jim Bunton

Gather aggregate data from two tables

I have three tables:

1) Contract - columns ContractNo and ContractName

2) SalesTransactions - multiple rows for each contract - relevant columns: ContractNo, InvoiceDate, Value

3) CostOfSaleTransactions - multiple rows for each contract - relevant columns: ContractNo, TransactionDate, Cost

How do I write a SELECT statement to produce rows containing:

ContractNo ContractName Sum of Value for ContractNo between @.FromDate and @.ToDate Sum of Cost for ContractNo between @.FromDate and @.ToDate

Not all Contracts have either Sales or CostOfSales Transactions in the relevant date range and so one or both totals can be zero.

I've written something like:

SELECT CT.ContractNo, CT.ContractName, sum(CT.Value) as TotalValue, Sum(CS.Cost) as TotalCost

FROM Contract CT

INNER JOIN SalesTransactions ST ON CT.ContractNo=ST.ContractNo

INNER JOIN CostOfSaleTransactions CS ON CT.ContractNo=CS.ContractNo

WHERE (ST.InvoiceDate BETWEEN @.FromDate AND @.ToDate) AND (CS.TransactionDate BETWEEN @.FromDate AND @.ToDate)

GROUP BY CT.ContractNo, CT.ContractName

The TotalValue and TotalCost figures I get are much higher than expected. I presume this is something to do with the JOINs or WHERE clause. Please can you advise how I get the correct values?

You can do the following:

SELECT CT.ContractNo, CT.ContractName

, (SELECT sum(ST.Value)

FROM SalesTransactions ST

WHERE CT.ContractNo=ST.ContractNo

AND ST.InvoiceDate BETWEEN @.FromDate AND @.ToDate) as TotalValue

, (SELECT Sum(CS.Cost)

FROM CostOfSaleTransactions CS

WHERE CT.ContractNo=CS.ContractNo

AND CS.TransactionDate BETWEEN @.FromDate AND @.ToDate) as TotalCost

FROM Contract CT

Fuzzy Lookup problems

Hi everyone,

Ive just started looking at the Fuzzy Lookup feature and i think i must be getting something fundamentally wrong. I have two tables - each contain different meta data representations for a set of potentially similar documents. The only chance i have of matching a document in table A to a document in table B is a common title field. However, manual input means that the titles may differ in both tables although they are potentially quite similar in most cases.

In the lookup i get to specify the output columns from table B (Reference) which is fine, but i don't seem to get to choose the columns from table A that i would also like to see. So my output shows me all the documents from table B that it thinks are similar to ones in table A...but not identifying which record it's similar to.

I initially thought that the "pass through" columns that i identified would appear in the output - but this does not seem to be the case.

I must be using it incorrectly, but i have no idea how to progress with this apart from creating a new source table (C) which is a full outer join of table A and B - and then also using table C as the reference table, but that seems madness.

any help would be appreciated - ta

Andrew

I may have spoken too soon.

It's strange, but if i've been using the 'Advanced Edit' dialog to setup the lookup - which i had assumed contained the same functionality (plus more) as the 'Edit' dialog. It looks like i was wrong. If i setup the Lookup through the Edit page, I can specify the Pass Through columns (which it doesn't look like you can do from inside the Advanced Edit box). Then once i have finished the basic edit i can go in and perform any additional advanced editings.

I suppose it kind of makes sense - my intial attempts at setting Pass Throughs must have been flawed in some other way :)

Hope this helps someone else!

Andrew :)

|||

Perhaps have a look at: http://msdn.microsoft.com/sql/bi/integration/default.aspx?pull=/library/en-us/dnsql90/html/datasol.asp

Although primarily about Master Data Management (MDM) it also covers some of the interesting Fuzzy techniques that can be used with SSIS.

Donald

Further Question on American Date Format

Thanks Scott and Aron for the info on American date formatting. However I'm
still slightly confused.
recap of problem:
One of my table's in SQL Server contains a field of a field type 'datetime'
and recording of dates are stored in American format and not Irish/Uk
format. As sugessted by previous posting that the client application control
this, I used Enterprise Manager to insert a record into the table and the
date format is still storing as American format. i.e." 8/16/2004 4:34:52PM"
not as "16/08/2004 4:34:52PM"
This is causing me major proiblems as I'm trying to run simple reporting
form a web base client base on dates. The only way the reporting will work if
I change the date format on my machine to american format.
Any ideas ?
Thanks,
Liam
Hi
SQL does not store the date in an human readable format and does not persist
the formatting you used to enter it. It is up to you, when you select the
data, to format it to what you want.
Look at CAST or CONVERT in BOL for formatting information.
Regards
Mike
"Liam Mac" wrote:

> Thanks Scott and Aron for the info on American date formatting. However I'm
> still slightly confused.
> recap of problem:
> One of my table's in SQL Server contains a field of a field type 'datetime'
> and recording of dates are stored in American format and not Irish/Uk
> format. As sugessted by previous posting that the client application control
> this, I used Enterprise Manager to insert a record into the table and the
> date format is still storing as American format. i.e." 8/16/2004 4:34:52PM"
> not as "16/08/2004 4:34:52PM"
> This is causing me major proiblems as I'm trying to run simple reporting
> form a web base client base on dates. The only way the reporting will work if
> I change the date format on my machine to american format.
> Any ideas ?
> Thanks,
> Liam
sql

function to return table variable

my question has to do with the performance of user-defined function that
returns a table variable vs. traditional temp tables. i have a function
that consists of a select statement that populates and returns a table
variable. the select is fairly complex and takes a siginificant amount of
time to run mainly due to the size of the tables being queried. the
resultset, is not that large (usually around 1000 rows in 10 columns). the
procedure that uses the data in the table variable makes reference to this
table variable in more than one location using something like:
select * from dbo.fTblVar(param1,param2,param3)
does this mean that each time the calling procedure makes reference to the
function, the complex (and long-running) select statement will be executed?
if this is the case, it seems that i would be better off using a traditional
temp table that is created and populated once, then acted on as needed. or
does this so somehow get cached sql server's memory for the duration of the
procedure? what if the parameter values change?Well if you call the UDF every time, then yes, it willrun every time... But
if the contents are not different then just run the UDF Once, outside your
query, and dump the valeusinto a local table variable, and use that table
variable in your query, then it won;t be running everytime
Declare @.T Table (<column Defintions> )
Insert @.T
select * from dbo.fTblVar(param1,param2,param3)
-- Now the @.T variable can be used throughout the rest of your SP Exactly
like a temp table would be used...
Which is better depends on how much data is in it, and what you need to do
with it. You can't put additional indexes (Other than Primary Key COnstrain
t
Index) on table variables, so if you need to really manipulate the data in
the table, a temp table is more flexible, but if all you need is a temporar
y
list of keys, say, for joining in another query, then table variables are
100% in memory, and should be much faster. If you use them, however, keep
them narrow.
"JT" wrote:

> my question has to do with the performance of user-defined function that
> returns a table variable vs. traditional temp tables. i have a function
> that consists of a select statement that populates and returns a table
> variable. the select is fairly complex and takes a siginificant amount of
> time to run mainly due to the size of the tables being queried. the
> resultset, is not that large (usually around 1000 rows in 10 columns). th
e
> procedure that uses the data in the table variable makes reference to this
> table variable in more than one location using something like:
> select * from dbo.fTblVar(param1,param2,param3)
> does this mean that each time the calling procedure makes reference to the
> function, the complex (and long-running) select statement will be executed
?
> if this is the case, it seems that i would be better off using a tradition
al
> temp table that is created and populated once, then acted on as needed. o
r
> does this so somehow get cached sql server's memory for the duration of th
e
> procedure? what if the parameter values change?
>
>

Function performances

I've made a user defined frunction that calculate a percentage reading data
from tables in db.
If i run the function with that code
set dateformat dmy
declare @.aaa DECIMAL(5,2)
set @.aaa=dbo.fn_name()
print @.aaa
or with that code
select dbo.fn_name()
it takes 21 seconds...too long
If i copy the code of the function and execute it in query analyzer, it
takes less than 1 second.
How is it possible?Function is not necessarily good for all kind of operation especially
for manipulating data in sets.
If your need to manipulate data in sets, you might as well do it in
stored proc to give you better performance and try to avoid cursor as
much as possible.
Roberto Lo Baido wrote:
> I've made a user defined frunction that calculate a percentage reading data
> from tables in db.
> If i run the function with that code
> set dateformat dmy
> declare @.aaa DECIMAL(5,2)
> set @.aaa=dbo.fn_name()
> print @.aaa
> or with that code
> select dbo.fn_name()
> it takes 21 seconds...too long
> If i copy the code of the function and execute it in query analyzer, it
> takes less than 1 second.
> How is it possible?

Function performances

I've made a user defined frunction that calculate a percentage reading data
from tables in db.
If i run the function with that code
set dateformat dmy
declare @.aaa DECIMAL(5,2)
set @.aaa=dbo.fn_name()
print @.aaa
or with that code
select dbo.fn_name()
it takes 21 seconds...too long
If i copy the code of the function and execute it in query analyzer, it
takes less than 1 second.
How is it possible?
Function is not necessarily good for all kind of operation especially
for manipulating data in sets.
If your need to manipulate data in sets, you might as well do it in
stored proc to give you better performance and try to avoid cursor as
much as possible.
Roberto Lo Baido wrote:
> I've made a user defined frunction that calculate a percentage reading data
> from tables in db.
> If i run the function with that code
> set dateformat dmy
> declare @.aaa DECIMAL(5,2)
> set @.aaa=dbo.fn_name()
> print @.aaa
> or with that code
> select dbo.fn_name()
> it takes 21 seconds...too long
> If i copy the code of the function and execute it in query analyzer, it
> takes less than 1 second.
> How is it possible?

function on heirarchy nodes

Hi
i have 3 master tables 1)RoleDetails(Roleid(PK),name,masterroleid(fk) ref:RoleDetails roleid)
2) PositionDetails(positionid(PK), name,MasterPositionid(FK) ref:PostionDetails postionid,Roleid(fk) ref:Roledetails roleid)
3) Userdetails(userid(pk), loginid,pwd,roleid(fk) ref:roledetails roleid,positionid(fk)ref:postionDetails positionid,fname,address)
how to Create two functions one return child nodes as per Case 1 and another one is return Parent Nodes
as per case 2
(Manager) a -- r1 (roledetails)
/ \

(ROL)a1 (ROL) a2 -- r2
/ | \ / | | \
(RO)b1 b2 b3 b4 b5 b6 -- r3
Case 1:
On passing the User ID of (a) , should get the Output as User id of ( b1,b2,b3,b4,b5,b6) along with their Role ID.

On passing the User ID of (a1), should get the Output as User ID of (b1,b2,b3)along with their Role ID.

Case 2:
On passing the Role ID of (R3), should get the User ID of all the Parent roles( a1 and a2 (R2), a(R1)long with their Role id
Case 3: on passing role id of child node , should get only all particular parent userid and roleid's.

thanks in adv.,
chakriPost the DDL of the tables.it is lot easier to help u.|||sorry i was unable to understand as i am also new to sql. dont mind, can u gve me script for 3 seperate functions as per my requierment. or just gve me seperate queries which can produce to my requirement.
chakri|||sorry i was unable to understand as i am also new to sql. dont mind, can u gve me script for 3 seperate functions as per my requierment. or just gve me seperate queries which can produce to my requirement.
chakri

read this post first.http://www.dbforums.com/showthread.php?t=1196943|||CREATE TABLE [dbo].[PositionMaster] (
[PositionID] [bigint] NOT NULL ,
[Name] [varchar] (20) COLLATE Latin1_General_CS_AS NULL ,
[Desc] [varchar] (200) COLLATE Latin1_General_CS_AS NULL ,
[ParentPositionID] [bigint] NULL ,
[RoleID] [bigint] NULL ,
[Status] [bit] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[RoleMaster] (
[RoleID] [bigint] NOT NULL ,
[Name] [varchar] (20) COLLATE Latin1_General_CS_AS NULL ,
[Desc] [varchar] (200) COLLATE Latin1_General_CS_AS NULL ,
[ParentRoleID] [bigint] NOT NULL ,
[Status] [bit] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[UserMaster] (
[UserID] [bigint] NOT NULL ,
[LoginID] [varchar] (100) COLLATE Latin1_General_CS_AS NOT NULL ,
[Password] [varchar] (50) COLLATE Latin1_General_CS_AS NOT NULL ,
[RoleID] [bigint] NOT NULL ,
[PositionID] [bigint] NOT NULL ,
[Status] [bit] NULL ,
[FirstName] [varchar] (50) COLLATE Latin1_General_CS_AS NULL ,
[LastName] [varchar] (50) COLLATE Latin1_General_CS_AS NULL ,
[Gender] [varchar] (6) COLLATE Latin1_General_CS_AS NULL ,
[ContactNum] [bigint] NULL ,
[Address] [varchar] (200) COLLATE Latin1_General_CS_AS NULL ,
[Email] [varchar] (50) COLLATE Latin1_General_CS_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PositionMaster] ADD
CONSTRAINT [PK_PositionMaster] PRIMARY KEY CLUSTERED
(
[PositionID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[RoleMaster] ADD
CONSTRAINT [PK_RoleMaster] PRIMARY KEY CLUSTERED
(
[RoleID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[UserMaster] ADD
CONSTRAINT [PK_UserMaster] PRIMARY KEY CLUSTERED
(
[UserID]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PositionMaster] ADD
CONSTRAINT [FK_PositionMaster_PositionMaster] FOREIGN KEY
(
[ParentPositionID]
) REFERENCES [dbo].[PositionMaster] (
[PositionID]
),
CONSTRAINT [FK_PositionMaster_RoleMaster] FOREIGN KEY
(
[RoleID]
) REFERENCES [dbo].[RoleMaster] (
[RoleID]
)
GO

ALTER TABLE [dbo].[RoleMaster] ADD
CONSTRAINT [FK_RoleMaster_RoleMaster] FOREIGN KEY
(
[ParentRoleID]
) REFERENCES [dbo].[RoleMaster] (
[RoleID]
)
GO

ALTER TABLE [dbo].[UserMaster] ADD
CONSTRAINT [FK_UserMaster_PositionMaster] FOREIGN KEY
(
[PositionID]
) REFERENCES [dbo].[PositionMaster] (
[PositionID]
),
CONSTRAINT [FK_UserMaster_RoleMaster] FOREIGN KEY
(
[RoleID]
) REFERENCES [dbo].[RoleMaster] (
[RoleID]
)
GO|||if u ask for DML here is it..

Insert into RoleMaster values (1,'Admin','Adminstrator',1,1)
Insert into RoleMaster values (2,'Mgr','Manager',1,2)
Insert into RoleMaster values (3,'RoL','Recovery Operator Lead',2,3)
Insert into RoleMaster values (4,'RO','Recovery Operator',3,4)
select * from RoleMaster
go

Insert into PositionMaster values (1,'Mgr','Manager',Null,1,1)
Insert into PositionMaster values (2,'ROL1','Recovery Operator Lead 1',1,2,1)
Insert into PositionMaster values (3,'ROL2','Recovery Operator Lead 2',1,2,1)
Insert into PositionMaster values (4,'RO1','Recovery Operator 1',2,3,1)
Insert into PositionMaster values (5,'RO2','Recovery Operator 2',2,3,1)
Insert into PositionMaster values (6,'RO3','Recovery Operator 3',3,3,1)
Insert into PositionMaster values (7,'RO4','Recovery Operator 4',3,3,1)
Insert into PositionMaster values (8,'RO5','Recovery Operator 5',3,3,1)
Insert into PositionMaster values (9,'RO6','Recovery Operator 6',3,3,1)

go
select * from PositionMaster

Insert into UserMaster values (1,'Tom','Tom',2,1,1,'Tom','Hanks','m',Null,'Null' ,'Null')
Insert into UserMaster values (2,'Jim','Jim',3,2,1,'Jim','Ward','m',Null,'Null', 'Null')
Insert into UserMaster values (3,'Sandra','Sandra',3,3,1,'Sandra','Bullock','m', Null,'Null','Null')
Insert into UserMaster values (4,'Ross','Ross',4,4,1,'Ross','Magan','m',Null,'Nu ll','Null')
Insert into UserMaster values (5,'Joe','Joe',4,5,1,'Joe','Vester','m',Null,'Null ','Null')
Insert into UserMaster values (6,'Bryan','Bryan',4,6,1,'Byran','Adam','m',Null,' Null','Null')
Insert into UserMaster values (7,'John','John',4,7,1,'Jhon','Abraham','m',Null,' Null','Null')
Insert into UserMaster values (8,'Adam','Adam',4,8,1,'Adam','Core','m',Null,'Nul l','Null')
Insert into UserMaster values (9,'Jobin','Jobin',4,9,1,'Jobin','Thomas','m',Null ,'Null','Null')
select * from UserMaster|||Now give me a sample output of ur result u r looking|||Case 1: (Retrieval of the child nodes)

Example !: On passing the User ID of (Tom) , should get the Output as User id of ( Ross, Joe, Bryan , John, Adam and Jobin) along with their Role ID.

Example 2: On passing the User ID of (Jim) , should get the Output as User ID of (Ross, Joe ) along with their Role ID.

Case 2: (Retrieval of Parent Nodes)

On passing the Role ID of (R3), should get the User ID of all the Parent roles( Jim and Sadra (R2) , Tom (R1)) along with their Role id

case 3: on passing the Role Id of child node, should get the User ID's of all specific parent roles along with Role id.
Note that i may increase the rows and position id's|||case 1,2

--Case Tom--
declare @.UserID int
set @.UserID=1
select

u2.UserID ,
u2.LoginID,
u2.RoleID
from UserMaster u1 join RoleMaster r1
on u1.RoleID=r1.ParentRoleID
join
UserMaster u2
on r1.RoleID=u2.RoleID
where u1.UserID=@.UserID
go
--case Jim---
declare @.UserID int
set @.UserID=2
select

u2.UserID ,
u2.LoginID,
u2.RoleID
from UserMaster u1 join RoleMaster r1
on u1.RoleID=r1.ParentRoleID
join
UserMaster u2
on r1.RoleID=u2.RoleID
where u1.UserID=@.UserID|||i gave as example 1 and example 2 for case 1 as tom and jim. anyway by passing @.userid =1 its not displaying all the nodes.. its just retriving
Userid Loginid Roleid
2 Jim 3
3 Sandra 3
but as per my requirement On passing the User ID of (Tom) , should get the Output of Ross, Joe, Bryan , John, Adam and Jobin.

and for jim which ur query is displaying
userid loginid roleid
4 Ross 4
5 Joe 4
6 Bryan 4
7 John 4
8 Adam 4
9 Jobin 4
but it need to disply only for Ross and Joe details..|||i gave as example 1 and example 2 for case 1 as tom and jim. anyway by passing @.userid =1 its not displaying all the nodes.. its just retriving
Userid Loginid Roleid
2 Jim 3
3 Sandra 3
but as per my requirement On passing the User ID of (Tom) , should get the Output of Ross, Joe, Bryan , John, Adam and Jobin.

I will explain what i undestand,

1.based on userid I got the roleID
2.select RoleID from RoleMaster by equating roleID from 1 step with ParentRoleID.
3.select userID from PostionMaster based whoever having that roleID I got from 2 step.
---

based on userid Tom's role id is 2
roleid 3 has parentRoleID 2(tom's roleid)
jim and sandra have roleid 3 in usermaster|||i will explain my scenario.
when i gve userid of Tom, condition is all the child nodes should be retrived, unconditionally how many may be the child nodes. all child nodes should be retrived. this is in case 1.
case 2. is when i gve roleid of anychild node, all the parent nodes should be retrived. it means if child node is not in the hand of parent node also all the parents should be output.
case 3: if i gve roleid of anychild node only the corresponding all the parents should be retived. it means it is hand based. if child is said to particular parent and that parent is in hand of another parent.. all related parents should be retrived.
i hope u can understand by this. i was unable to get this solution from last 2 days.|||mallier i am waiting for ur reply.. can i expect ur further help-hand.|||mallier i am waiting for ur reply.. can i expect ur further help-hand.

I forgot ur post itself.let me try now .|||Here is an article I wrote on a method of returning data from hierarchical structures. You should be able to adapt this to your situation.
-------------------
The most flexible and robust method of storing hierarchical data in a database is to use a table with a recursive relationship. In this design, each record has an associated parent record ID that indicates its relative place in the hierarchy. Here is an example:

CREATE TABLE [YourTable]
([RecordID] [int] IDENTITY (1, 1) NOT NULL ,
[ParentID] [int] NULL)

The challenge is to find a way to return all the child records and descendants for any given parent record.

While recursion is supported within SQL Server, it is limited to 32 nested levels and it tends to be ineffecient because it does not take full advantage of SQL Server's set-based operations.

A better algorithm is a method I call the "Accumulator Table".

In this method, a temporary table is declared that accumulates the result set. The table is seeded with the initial key of the parent record, and then a loop is entered which inserts the immediate descendants of all the records accumulated so far which have not already been added to the table.

Here is some skeleton code to show how it works:

--This variable will hold the parent record ID who's children we want to find.
declare @.RecordID int
set @.RecordID = 13

--This table will accumulate our output set.
declare @.RecordList table (RecordID int)

--Seed the table with the @.RecordID value, assuming it exists in the database.
insert into @.RecordList (RecordID)
select RecordID
from YourTable
where YourTable.RecordID = @.RecordID

--Add new child records until exhausted.
while @.@.RowCount > 0
insert into @.RecordList (RecordID)
select YourTable.RecordID
from YourTable
inner join @.RecordList RecordList on YourTable.ParentID = RecordList.RecordID
where not exists (select * from @.RecordList CurrentRecords where CurrentRecords.RecordID = YourTable.RecordID)

--Return the result set
select RecordID
from @.RecordList

This method is both flexible and efficient, and the concept is adaptable to other hierarchical data challenges.

For a completely different method of storing and manipulating hierarchical data, check out Celko's Nested Set model, which stores relationships as loops of records.

http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=145525%5D|||ya i had a look at that site also, but can u explain how in Personnel table , fo albert and bert... got 12 and 3.. as rgt. as lft=2*(SELECT COUNT(*) FROM TreeTable) ..and sorry for asking u directly .. could u give me direct procedure for my requirement, as i am working for these, last week days . i am trying in different ways and moved to another. plz do if possible. thanks for what u r doing.|||--case 1

create procedure dbo.retrieveUserid (
@.roleid int
)
as
if object_id('tempdb.dbo.##temp','u') is null
begin
create table ##temp(userid int,roleid int)
print('create table')
end
declare @.roleid1 int
select
@.roleid1=r1.RoleID
from UserMaster u1 join RoleMaster r1
on u1.RoleID=r1.ParentRoleID
join
UserMaster u2
on r1.RoleID=u2.RoleID
where u1.RoleID=@.roleid
if (@.@.rowcount>0)
begin
insert into ##temp
select distinct
u2.UserID ,
r1.RoleID
from UserMaster u1 join RoleMaster r1
on u1.RoleID=r1.ParentRoleID
join
UserMaster u2
on r1.RoleID=u2.RoleID
where u1. RoleID= @.roleid

exec retrieveUserid @.roleid1
end
else
begin
select * from ##temp
drop table ##temp
end

--Call procedure--
declare @.roleid int,@.userid int
set @.userid=1
select @.roleid=RoleID from UserMaster where UserID= @.userid
exec retrieveUserid @.roleid

function index

I have implemented function based indexes on tables in Oracle database. I want to implement same in SQL Server. Is there something similar to function index in SQL Server also? If yes, please explain as how I can do this.
Thank,
dcExplain more clearly what you want to do.|||You have to create a computed column with the function you want indexed, then index the computed column. Then you have to change all of your applications to query the new column. Simple, eh?

In short, Function Based Indexes don't really exist in SQL Server, but they are pretty nice things to have.

create index ind on table (upper(name))|||Function based indicies (where the indexed value is the result of one or more function calls) were the most dearly missed feature of Clipper when I converted to SQL Server. They are lovely things when you don't have to deal with most of the complexities that a real relational database engine is intended to solve!

Unfortunately, they don't scale, introduce all kinds of "functional dependancies", lead to really interesting collation questions, etc. A Function Based Index is certainly convenient in many ways, but it is a scaling/supportability nightmare in search of its next victim!

-PatP

Function doesn't use indexes

Hello!
I have a function on SQL 2000 sp3a that executes a simple select statement.
It takes input parameter and joins two tables based on that parameter and
then returns the result as a table.
Problem is that the function does not use any indexes. Select is performed
by using full scans on both tables.
If I then execute that same select statement not using that function just
select statement with the same input parameter,
execution plan changes and it uses the right indexes. Sure it's a lot
faster...
Why the function doesn't use indexes?
Why would select statement use indexes correctly and the function that
executes the same select statement would't?
Tom
It would help a lot if we could see this "function". Is it really a
function or a stored procedure?
Andrew J. Kelly SQL MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:Oio7zth7FHA.4076@.tk2msftngp13.phx.gbl...
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select
> statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>
|||HI,
yeah as said if you can post a query and function it would be great for us
to resolve issue , have you check it with index hint !?
Regards
Andy Davis
Active Crypt Team
---SQL Server Encryption
Decryption Software
http://www.activecrypt.com
"Tom" wrote:

> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>

Function doesn't use indexes

Hello!
I have a function on SQL 2000 sp3a that executes a simple select statement.
It takes input parameter and joins two tables based on that parameter and
then returns the result as a table.
Problem is that the function does not use any indexes. Select is performed
by using full scans on both tables.
If I then execute that same select statement not using that function just
select statement with the same input parameter,
execution plan changes and it uses the right indexes. Sure it's a lot
faster...
Why the function doesn't use indexes?
Why would select statement use indexes correctly and the function that
executes the same select statement would't?
TomIt would help a lot if we could see this "function". Is it really a
function or a stored procedure?
Andrew J. Kelly SQL MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:Oio7zth7FHA.4076@.tk2msftngp13.phx.gbl...
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select
> statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>|||HI,
yeah as said if you can post a query and function it would be great for us
to resolve issue , have you check it with index hint !?
Regards
--
Andy Davis
Active Crypt Team
---SQL Server Encryption
Decryption Software
http://www.activecrypt.com
"Tom" wrote:

> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select statement
.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>

Function doesn't use indexes

Hello!
I have a function on SQL 2000 sp3a that executes a simple select statement.
It takes input parameter and joins two tables based on that parameter and
then returns the result as a table.
Problem is that the function does not use any indexes. Select is performed
by using full scans on both tables.
If I then execute that same select statement not using that function just
select statement with the same input parameter,
execution plan changes and it uses the right indexes. Sure it's a lot
faster...
Why the function doesn't use indexes?
Why would select statement use indexes correctly and the function that
executes the same select statement would't?
TomIt would help a lot if we could see this "function". Is it really a
function or a stored procedure?
--
Andrew J. Kelly SQL MVP
"Tom" <mcseman2002@.hotmail.com> wrote in message
news:Oio7zth7FHA.4076@.tk2msftngp13.phx.gbl...
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select
> statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>|||HI,
yeah as said if you can post a query and function it would be great for us
to resolve issue , have you check it with index hint !?
Regards
--
Andy Davis
Active Crypt Team
---SQL Server Encryption
Decryption Software
http://www.activecrypt.com
"Tom" wrote:
> Hello!
> I have a function on SQL 2000 sp3a that executes a simple select statement.
> It takes input parameter and joins two tables based on that parameter and
> then returns the result as a table.
> Problem is that the function does not use any indexes. Select is performed
> by using full scans on both tables.
> If I then execute that same select statement not using that function just
> select statement with the same input parameter,
> execution plan changes and it uses the right indexes. Sure it's a lot
> faster...
> Why the function doesn't use indexes?
> Why would select statement use indexes correctly and the function that
> executes the same select statement would't?
> Tom
>
>

Fulltextsearch Error in Userinstance

I've enabled the Fulltextsearch in some Tables of my DB.
In SQL Server Management Studio the querys are all have results and running free of errors.

If i will use the same querys in VWD, they're running in a SqlException. Translated from german:
The Fulltextsearch cant be used in the Userinstance.

But a SelectCommand from the WebSite to the IndexState:
SELECT crawl_end_date FROM sys.fulltext_indexes WHERE fulltext_catalog_id = 10
works fine.

What i'm wrong?

The ConnectionString i'm using:
="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Support.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"

The FulltextService (msftesql.exe) is running under a UserAccount.

Thanx

Klaus

Hi Klaus,

Full Text Search can not be used in a User Instance. If you need the functionality of FTS, you'll need to attach your database to the main instance and connect to that.

Regards,

Mike Wachal
SQL Express team

-
Check out my tips for getting your answer faster and how to ask a good question: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=307712&SiteID=1

|||

Hi Mike,

User Instance is the magic word.

Change my connectionstring like connectionstring.com says and all works fine.

Thanks for the hint to the right way.

Klaus

FullTexted table

Hi,
I have a database that has about 100 tables. How can I find out which
tables are full-text indexed?
Thanks
Ed
Ed,
You can use the following system stored procedures to determine what tables
are FT Indexed:
sp_help_fulltext_tables
You can also use the following metadata function to gather more FT info:
select
ObjectProperty(object_id(N'<table_name>'),N'TableH asActiveFulltextIndex')
select
objectproperty(object_id(N'<table_name>'),N'TableF ulltextChangeTrackingOn')
select
objectproperty(object_id(N'<table_name>'),N'TableF ullTextBackgroundUpdateInd
exOn')
select
objectproperty(object_id(N'<table_name>'),N'TableF ullTextPopulateStatus')
Regards,
John
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:8E4A8EDC-C5E3-4C75-A200-5412B0818CE3@.microsoft.com...
> Hi,
> I have a database that has about 100 tables. How can I find out which
> tables are full-text indexed?
> Thanks
> Ed

Full-text searches

BASIC QUESTION here:

Can you do JOINS with tables in a catalog?What catalog? I'm not sure I understand your question. Do you mean the SQL Server System tables? In that case, yes you can.|||I have 'enabled' full text search capability for 3 tables in my SQL Server database and defined which columns I want indexed.

Of those tables I have enabled, can I JOIN them in queries?|||You can join them in queries regardless of whether they are indexed.|||Ok, thanks for the info.