General NetWork Error Check network configuration

Hi to all
i have two sql servers
the first one is a win 2000 sever serverA
and the second one is a win xp ServerB
..i have created a push subscription at sql server ServerA
..i had many problems finally i installed the sp3 on both machine
..the first time i ran the replication at serverA via a dial up connection
with modem
it took 45 mins for 11135 inserts.
It had also certain conficts.
So i re run the replication but this time i have an error
"General network error. Check your network documentation.
(Source: MAILVXE (Data source); Error number: 11)"
I restarted the mahine but in vain the same error is repeated.
Please help me .
Thanks in advance
Soobrassen
General network error means there is a problem with your link. It could be
name resolution (unlikely as it was resolving ok for 45 minutes), bandwidth,
or a problem with the connection.
I am not sure where you are in deploying your snapshot, it sounds like it is
deployed. If so, you should wrap your distribution agent in an infinite
loop, have step 3 on failure, return to step 1.
To check name resolution problems issue a ping -a subscriber ip from the
publisher if you are doing a push, or a ping -a publisher ip from the
subscriber. To check to bandwidth copy a 1 Mg file and see how long it
takes. Copying smaller files will not necessarily give meaningful results.
You can also run keep alive scripts, like ping -t subscriber name to
discover when your connection fails, or to determine how lossy or unstable
your connection is.
There is another possibility where your publisher or subscriber gets so
bogged down that it can't respond in the time window allotted to your
distribution agent. Running perform on the subcriber or publisher and
monitoring cpu usage can help you here.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Soobrassen Thoplan" <Soobrassen Thoplan@.discussions.microsoft.com> wrote in
message news:AFA9A029-80FC-44CA-A830-04A604D612EC@.microsoft.com...
> Hi to all
> i have two sql servers
> the first one is a win 2000 sever serverA
> and the second one is a win xp ServerB
> .i have created a push subscription at sql server ServerA
> .i had many problems finally i installed the sp3 on both machine
> .the first time i ran the replication at serverA via a dial up connection
> with modem
> it took 45 mins for 11135 inserts.
> It had also certain conficts.
> So i re run the replication but this time i have an error
> "General network error. Check your network documentation.
> (Source: MAILVXE (Data source); Error number: 11)"
> I restarted the mahine but in vain the same error is repeated.
> Please help me .
> Thanks in advance
> Soobrassen
>

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

Gaps in my key ID field

Greetings,

I am new to SQL Server. I've created a database with a key ID field that is set to automatically increment. Well, after adding records I've got some gaps in my numbering and want to renumber from 1 to eof.

What is the best way to do this in SQL Server 2005?

Thank you.

I have covered it in the thread below. Hope this helps.

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

|||Thank you!|||

Elizabeth Davis wrote:

Thank you!

I am glad I could help.

Fyi

Hi everyone I have discovered half of my problems were due to Access 2000 apparently Access 2000 was created before SQL Server 2000 so when you try to execute a Stored Procedure through a command button and all you get are error messages its due to Access not understanding the commands. Alot of the problems I was having was due to Access 2000 now that I have Access XP I'm WALKIN ON SUNSHINE WEEELLLLLL!!! SQL Tells it to sit up, Beg, Fetch and XP just does it no questions asked...WOOHOOO

Just a little FYIand don't it feel good?

FWIW: Database Space Used (stored proc)

For what it's worth, I hacked up MS' sp_spacedused and created a new stored procedure called sp_dbspaceused. I made the following modifications:

1. It returns a single resultset (instead of multiple resultsets);
2. I eliminated the options that were specfically geared towards sizing of individual objects (no object name parameter and no update statistics parameter);
3. I eliminated the formatting from the result set (the numbers are expressed in KB)

Place the code into an admin database or (more risky and less "best practice") directly into your master database.

Usage:
USE MyDatabase
GO

EXEC AdminDatabase.dbo.sp_dbspaceused
GO

CREATE PROCEDURE sp_dbspaceused

as

declare @.id int -- The object id of @.objname.
declare @.pages int -- Working variable for size calc.
declare @.dbname sysname
declare @.dbsize dec(15,0)
declare @.logsize dec(15)
declare @.bytesperpage dec(15,0)
declare @.pagesperMB dec(15,0)

/*Create temp tables before any DML to ensure dynamic
** We need to create a temp table to do the calculation.
** reserved: sum(reserved) where indid in (0, 1, 255)
** data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)
** indexp: sum(used) where indid in (0, 1, 255) - data
** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)
*/
create table #spt_space
(
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)

set nocount on

/*
** If @.id is null, then we want summary data.
*/
/* Space used calculated in the following way
** @.dbsize = Pages used
** @.bytesperpage = d.low (where d = master.dbo.spt_values) is
** the # of bytes per page when d.type = 'E' and
** d.number = 1.
** Size = @.dbsize * d.low / (1048576 (OR 1 MB))
*/
begin
select @.dbsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 = 0)

select @.logsize = sum(convert(dec(15),size))
from dbo.sysfiles
where (status & 64 <> 0)

select @.bytesperpage = low
from master.dbo.spt_values
where number = 1
and type = 'E'
select @.pagesperMB = 1048576 / @.bytesperpage
/*
select database_name = db_name(),
database_size =
ltrim(str((@.dbsize + @.logsize) / @.pagesperMB,15,2) + ' MB'),
'unallocated space' =
ltrim(str((@.dbsize -
(select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)
)) / @.pagesperMB,15,2)+ ' MB')
*/
print ' '
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
insert into #spt_space (reserved)
select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255)

/*
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
select @.pages = sum(convert(dec(15),dpages))
from sysindexes
where indid < 2
select @.pages = @.pages + isnull(sum(convert(dec(15),used)), 0)
from sysindexes
where indid = 255
update #spt_space
set data = @.pages

/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
- data

/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))

select reserved = cast((reserved * d.low / 1024.) as bigint) ,
data = cast((data * d.low / 1024.) as bigint) ,
index_size = cast((indexp * d.low / 1024.) as bigint) ,
unused = cast((unused * d.low / 1024.) as bigint)
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
end

return (0) -- sp_spaceused

GOI think this one is shorter ;):

select
reserved=(
select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255))*8,
index_size = ((
select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255))
- (
select (select sum(convert(dec(15),dpages))
from sysindexes
where indid < 2) + isnull(sum(convert(dec(15),used)), 0)
from sysindexes
where indid = 255))*8,
data=(
select (select sum(convert(dec(15),dpages))
from sysindexes
where indid < 2) + isnull(sum(convert(dec(15),used)), 0)
from sysindexes
where indid = 255)*8,
unused=((
select sum(convert(dec(15),reserved))
from sysindexes
where indid in (0, 1, 255))
- (
select sum(convert(dec(15),used))
from sysindexes
where indid in (0, 1, 255)))*8|||Yes it is. No one ever accused me of having an overabundance of imagination.

Thanks for the nice re-write.

Regards,

hmscott|||With compliments to rdjabarov and apologies to those who do this for a living , I offer up this version which will pull the results for each database...

Regards,

hmscott

ALTER PROC sp_dbSpaceUsed

AS

CREATE TABLE #TempSpace (
[Database] varchar(255),
Reserved dec(15),
Index_Size dec(15),
Data dec(15),
Unused dec(15)
)

DECLARE @.sSQL varchar(1000)

SELECT @.sSQL = 'INSERT INTO #TempSpace ([Database], Reserved, Index_Size, Data, Unused)
SELECT
''?'' as [Database],
reserved=(
select sum(convert(dec(15),reserved))
from [?]..sysindexes
where indid in (0, 1, 255))*8,
index_size = ((
select sum(convert(dec(15),used))
from [?]..sysindexes
where indid in (0, 1, 255))
- (
select (select sum(convert(dec(15),dpages))
from [?]..sysindexes
where indid < 2) + isnull(sum(convert(dec(15),used)), 0)
from [?]..sysindexes
where indid = 255))*8,
data=(
select (select sum(convert(dec(15),dpages))
from [?]..sysindexes
where indid < 2) + isnull(sum(convert(dec(15),used)), 0)
from [?]..sysindexes
where indid = 255)*8,
unused=((
select sum(convert(dec(15),reserved))
from [?]..sysindexes
where indid in (0, 1, 255))
- (
select sum(convert(dec(15),used))
from [?]..sysindexes
where indid in (0, 1, 255)))*8'

EXEC sp_MSforeachdb @.command1=@.sSQL

SELECT * FROM #TempSpace

DROP TABLE #TempSpace

Fuzzy Grouping in parallel

Hello,

I have created a project to do de-dupification of addresses.

I understand that Fuzzy Grouping will take less time if it has lesser data volume to process.

My source feed file is sometimes huge. So I am splitting the input into multiple branches based on

the first letter of the city. There are 7 branches in the process.

Source File Feed

|

Split data into 7 groups

|

| | | | | | |

FzGrpg FzGrpg FzGrpg FzGrpg FzGrpg FzGrpg FzGrpg

| | | | | | |

Split Split Split Split Split Split Split

| | | | | | |

- -- -- -- -- -- --

| | | | | | | | | | | | | |

<- - - - - - - Write the Canonicals and Dupes from each of these splits into database - - - - - - - - ->

When I designed this I was hoping that each of the Fuzzy Grouping tasks will execute in parallel.

But in reality they are processing one after the other.

Is there anyway to make them execute in parallel?

Appreciate your help.

Thanks

KM

How do you know they are not going in parallel and what kind of machine are you using?

I haven't use Fuzzy groups before, but parallel processing would depend on the hardware you are using. If the machine you use is a single processor, I doubt you can see parallelism on the process.

|||

Its a 4 dual core CPU machine with 8 GB RAM.

When I run in the debug mode you could see the data flowing in the pipelines at runtime.

|||

Which component are you using right before to fuzzy groups? I *think*, you should be using a multicast in order to get the parallelism you want; as it would generate 7 identical data sets to be consumed by each fuzzy grouping component. See if this article gives you some tips (Parallelism section):

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx

[Microsoft follow-up] perhaps somebody at MSFT can give you a better explanation

|||

Yes, I believe the current pipeline engine will not do a great job in optimizing this one. It would most probably end up using a single thread as Conditional Split is synchronous. You could try to artificially break synchronicity of the fuzzy grouping branches by adding a fake asynchronous transform (Union All with one input and one output should be good).

The next version of the pipeline scheduler should be able to better optimize distribution of threads.

Thanks.

|||

Bob and Rafael, thanks a lot for your input. So you are saying it should be like this?

Source File Feed

|

Split data into 7 groups

|

| | | | | | |

UnionAll UnionAll UnionAll UnionAll UnionAll UnionAll UnionAll

| | | | | | |

FzGrpg FzGrpg FzGrpg FzGrpg FzGrpg FzGrpg FzGrpg

| | | | | | |

Split Split Split Split Split Split Split

| | | | | | |

- -- -- -- -- -- --

| | | | | | | | | | | | | |

<- - - - - - - Write the Canonicals and Dupes from each of these splits into database - - - - - - - - ->

|||

As an alternative, could you do the split in one data flow, dumping each branch to a raw file, and then use a seperate data flow (or 7 data flows) to read in the raw files and do the Fuzzy Grouping?

The engine seems to optimize better with multiple data flows, than with multiple paths in the same data flow.

|||

Yes, that is how I meant to use Union Alls.

The idea of using raw files sounds good too. I would try both and see which one works better for youe scenario.

Thanks.

|||

Thanks a lot guys.

I tried the UnionAll approach and it works perfectly fine. I am able to see that all the threads go in parallel.

A run that used to take around 10 hours got completed in 2.5 hours and that's a lot of saving.

And utilitzation of the CPUs was 100%.

As and when I tweak this more I will keep you all posted.

Thanks again.

|||

KM68 wrote:

Thanks a lot guys.

I tried the UnionAll approach and it works perfectly fine. I am able to see that all the threads go in parallel.

A run that used to take around 10 hours got completed in 2.5 hours and that's a lot of saving.

And utilitzation of the CPUs was 100%.

As and when I tweak this more I will keep you all posted.

Thanks again.

[Microsoft follow-up] This sounds like a possitive feedback.

KM68,

We all will appreciate your updates.

|||

I am glad it worked.

Hopefully, with the next version of the data flow engine you will not need this workaround and the execution time can be trimmed even more.

Thanks.

|||

Thanks.

Any idea when the next version is scheduled for release?

Fuzzy Grouping in parallel

Hello,

I have created a project to do de-dupification of addresses.

I understand that Fuzzy Grouping will take less time if it has lesser data volume to process.

My source feed file is sometimes huge. So I am splitting the input into multiple branches based on

the first letter of the city. There are 7 branches in the process.

Source File Feed

|

Split data into 7 groups

|

| | | | | | |

FzGrpg FzGrpg FzGrpg FzGrpg FzGrpg FzGrpg FzGrpg

| | | | | | |

Split Split Split Split Split Split Split

| | | | | | |

- -- -- -- -- -- --

| | | | | | | | | | | | | |

<- - - - - - - Write the Canonicals and Dupes from each of these splits into database - - - - - - - - ->

When I designed this I was hoping that each of the Fuzzy Grouping tasks will execute in parallel.

But in reality they are processing one after the other.

Is there anyway to make them execute in parallel?

Appreciate your help.

Thanks

KM

How do you know they are not going in parallel and what kind of machine are you using?

I haven't use Fuzzy groups before, but parallel processing would depend on the hardware you are using. If the machine you use is a single processor, I doubt you can see parallelism on the process.

|||

Its a 4 dual core CPU machine with 8 GB RAM.

When I run in the debug mode you could see the data flowing in the pipelines at runtime.

|||

Which component are you using right before to fuzzy groups? I *think*, you should be using a multicast in order to get the parallelism you want; as it would generate 7 identical data sets to be consumed by each fuzzy grouping component. See if this article gives you some tips (Parallelism section):

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx

[Microsoft follow-up] perhaps somebody at MSFT can give you a better explanation

|||

Yes, I believe the current pipeline engine will not do a great job in optimizing this one. It would most probably end up using a single thread as Conditional Split is synchronous. You could try to artificially break synchronicity of the fuzzy grouping branches by adding a fake asynchronous transform (Union All with one input and one output should be good).

The next version of the pipeline scheduler should be able to better optimize distribution of threads.

Thanks.

|||

Bob and Rafael, thanks a lot for your input. So you are saying it should be like this?

Source File Feed

|

Split data into 7 groups

|

| | | | | | |

UnionAll UnionAll UnionAll UnionAll UnionAll UnionAll UnionAll

| | | | | | |

FzGrpg FzGrpg FzGrpg FzGrpg FzGrpg FzGrpg FzGrpg

| | | | | | |

Split Split Split Split Split Split Split

| | | | | | |

- -- -- -- -- -- --

| | | | | | | | | | | | | |

<- - - - - - - Write the Canonicals and Dupes from each of these splits into database - - - - - - - - ->

|||

As an alternative, could you do the split in one data flow, dumping each branch to a raw file, and then use a seperate data flow (or 7 data flows) to read in the raw files and do the Fuzzy Grouping?

The engine seems to optimize better with multiple data flows, than with multiple paths in the same data flow.

|||

Yes, that is how I meant to use Union Alls.

The idea of using raw files sounds good too. I would try both and see which one works better for youe scenario.

Thanks.

|||

Thanks a lot guys.

I tried the UnionAll approach and it works perfectly fine. I am able to see that all the threads go in parallel.

A run that used to take around 10 hours got completed in 2.5 hours and that's a lot of saving.

And utilitzation of the CPUs was 100%.

As and when I tweak this more I will keep you all posted.

Thanks again.

|||

KM68 wrote:

Thanks a lot guys.

I tried the UnionAll approach and it works perfectly fine. I am able to see that all the threads go in parallel.

A run that used to take around 10 hours got completed in 2.5 hours and that's a lot of saving.

And utilitzation of the CPUs was 100%.

As and when I tweak this more I will keep you all posted.

Thanks again.

[Microsoft follow-up] This sounds like a possitive feedback.

KM68,

We all will appreciate your updates.

|||

I am glad it worked.

Hopefully, with the next version of the data flow engine you will not need this workaround and the execution time can be trimmed even more.

Thanks.

|||

Thanks.

Any idea when the next version is scheduled for release?

Fusion Date and Time in a new DataTime field

Hello all
I have two fields Date and Time. I've just created a new filed called
Date_Time, I need put the old information in the new field. All fields are
DateTime type data.
Thanks a lot.
Carlos A. wrote:
> Hello all
> I have two fields Date and Time. I've just created a new filed called
> Date_Time, I need put the old information in the new field. All fields are
> DateTime type data.
> Thanks a lot.
>
Something like this:
SELECT CONVERT(DATETIME, '09/21/2006' + ' ' + '09:23am')
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||See update statement at end of the script. The code is not dependent on any language settings
(http://www.karaszi.com/SQLServer/info_datetime.asp):
DROP TABLE dt
CREATE TABLE dt(c1 datetime NULL , c2 datetime NULL, c3 datetime NULL)
GO
INSERT INTO dt (c2, c3) VALUES('20060921', '20:44:57')
SELECT * FROM dt
SELECT CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
FROM dt
UPDATE dt
SET c1 = CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
SELECT * FROM dt
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Carlos A." <carlin445@.gmail.com> wrote in message news:usgTaeY3GHA.3344@.TK2MSFTNGP05.phx.gbl...
> Hello all
> I have two fields Date and Time. I've just created a new filed called Date_Time, I need put the
> old information in the new field. All fields are DateTime type data.
> Thanks a lot.
>
|||Thanks both for your answers...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:evhIm5a3GHA.600@.TK2MSFTNGP05.phx.gbl...
> See update statement at end of the script. The code is not dependent on
> any language settings
> (http://www.karaszi.com/SQLServer/info_datetime.asp):
> DROP TABLE dt
> CREATE TABLE dt(c1 datetime NULL , c2 datetime NULL, c3 datetime NULL)
> GO
> INSERT INTO dt (c2, c3) VALUES('20060921', '20:44:57')
>
> SELECT * FROM dt
> SELECT CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
> FROM dt
> UPDATE dt
> SET c1 = CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
> SELECT * FROM dt
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Carlos A." <carlin445@.gmail.com> wrote in message
> news:usgTaeY3GHA.3344@.TK2MSFTNGP05.phx.gbl...
>

Fusion Date and Time in a new DataTime field

Hello all
I have two fields Date and Time. I've just created a new filed called
Date_Time, I need put the old information in the new field. All fields are
DateTime type data.
Thanks a lot.Carlos A. wrote:
> Hello all
> I have two fields Date and Time. I've just created a new filed called
> Date_Time, I need put the old information in the new field. All fields ar
e
> DateTime type data.
> Thanks a lot.
>
Something like this:
SELECT CONVERT(DATETIME, '09/21/2006' + ' ' + '09:23am')
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||See update statement at end of the script. The code is not dependent on any
language settings
(http://www.karaszi.com/SQLServer/info_datetime.asp):
DROP TABLE dt
CREATE TABLE dt(c1 datetime NULL , c2 datetime NULL, c3 datetime NULL)
GO
INSERT INTO dt (c2, c3) VALUES('20060921', '20:44:57')
SELECT * FROM dt
SELECT CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
FROM dt
UPDATE dt
SET c1 = CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
SELECT * FROM dt
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Carlos A." <carlin445@.gmail.com> wrote in message news:usgTaeY3GHA.3344@.TK2MSFTNGP05.phx.gb
l...
> Hello all
> I have two fields Date and Time. I've just created a new filed called Dat
e_Time, I need put the
> old information in the new field. All fields are DateTime type data.
> Thanks a lot.
>|||Thanks both for your answers...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:evhIm5a3GHA.600@.TK2MSFTNGP05.phx.gbl...
> See update statement at end of the script. The code is not dependent on
> any language settings
> (http://www.karaszi.com/SQLServer/info_datetime.asp):
> DROP TABLE dt
> CREATE TABLE dt(c1 datetime NULL , c2 datetime NULL, c3 datetime NULL)
> GO
> INSERT INTO dt (c2, c3) VALUES('20060921', '20:44:57')
>
> SELECT * FROM dt
> SELECT CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
> FROM dt
> UPDATE dt
> SET c1 = CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
> SELECT * FROM dt
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Carlos A." <carlin445@.gmail.com> wrote in message
> news:usgTaeY3GHA.3344@.TK2MSFTNGP05.phx.gbl...
>

Fusion Date and Time in a new DataTime field

Hello all
I have two fields Date and Time. I've just created a new filed called
Date_Time, I need put the old information in the new field. All fields are
DateTime type data.
Thanks a lot.Carlos A. wrote:
> Hello all
> I have two fields Date and Time. I've just created a new filed called
> Date_Time, I need put the old information in the new field. All fields are
> DateTime type data.
> Thanks a lot.
>
Something like this:
SELECT CONVERT(DATETIME, '09/21/2006' + ' ' + '09:23am')
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||See update statement at end of the script. The code is not dependent on any language settings
(http://www.karaszi.com/SQLServer/info_datetime.asp):
DROP TABLE dt
CREATE TABLE dt(c1 datetime NULL , c2 datetime NULL, c3 datetime NULL)
GO
INSERT INTO dt (c2, c3) VALUES('20060921', '20:44:57')
SELECT * FROM dt
SELECT CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
FROM dt
UPDATE dt
SET c1 = CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
SELECT * FROM dt
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Carlos A." <carlin445@.gmail.com> wrote in message news:usgTaeY3GHA.3344@.TK2MSFTNGP05.phx.gbl...
> Hello all
> I have two fields Date and Time. I've just created a new filed called Date_Time, I need put the
> old information in the new field. All fields are DateTime type data.
> Thanks a lot.
>|||Thanks both for your answers...
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:evhIm5a3GHA.600@.TK2MSFTNGP05.phx.gbl...
> See update statement at end of the script. The code is not dependent on
> any language settings
> (http://www.karaszi.com/SQLServer/info_datetime.asp):
> DROP TABLE dt
> CREATE TABLE dt(c1 datetime NULL , c2 datetime NULL, c3 datetime NULL)
> GO
> INSERT INTO dt (c2, c3) VALUES('20060921', '20:44:57')
>
> SELECT * FROM dt
> SELECT CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
> FROM dt
> UPDATE dt
> SET c1 = CONVERT(char(8), c2, 112) + ' ' + CONVERT(char(8), c3, 114)
> SELECT * FROM dt
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Carlos A." <carlin445@.gmail.com> wrote in message
> news:usgTaeY3GHA.3344@.TK2MSFTNGP05.phx.gbl...
>> Hello all
>> I have two fields Date and Time. I've just created a new filed called
>> Date_Time, I need put the old information in the new field. All fields
>> are DateTime type data.
>> Thanks a lot.
>

Funky formatting of percentages in Excel export

I've created a report that has cells in it that I want to be formatted as percentages. I set the format code in the cell properties to "P1" and everything appears to work fine until I export to Excel. Some cells show up with only one digit after the decimal and some show up with two digits after the decimal.

I looked at the formatting in Excel (right click, Format Cells...) and it is set to "[$-1010409]#,##0.0#%". I don't have the first clue what that is "supposed" to do but what it does is this:

If I enter 100.00 in the cell it shows up as 100.0%. Expected

If I enter 100.10 in the cell it shows up as 100.1%. Expected

If I enter 100.15 in the cell it is displayed as 100.15%. Not expected

I have formatted the report to only show one digit after the decimal, but RS exports to Excel with some funky formatting that sometimes shows one digit and sometimes two digits after the decimal. Why wouldn't it simply format the cell as a percent? How can I get it to only show a single digit after the decimal?

This is Excel 2007 but Excel 2003 has the same behavior.

Thanks

--John

John T. Williams wrote:

"[$-1010409]#,##0.0%"

Try that in Excel.

|||

I'm not sure what that reply is supposed to be suggesting. The format code [$-1010409]#,##0.0% already exists in an Excel spreadsheet that was created when exporting a report.

The problem is that the format code in Excel (created by Reporting Services) is causing inconsistent formatting of percentages.

|||

You said that excel formated the cell to this:

[$-1010409]#,##0.0#%

I'm saying to try this:

[$-1010409]#,##0.0%

I'm thinking the last # tells it to display the second decimal place if the number has one.

|||

Sorry, I didn't see that anything was different in the code you included. Yes, that does work. However, the problem is that I don't know of a way to control what Reporting Services tells Excel to use as a formatting code. So, the question remains, why does Reporting Services create an Excel formatting code that causes inconsistent behavior? Is there a workaround for this? If this is a bug, how can I report it so that it gets looked at?

This report runs and exports using a subscription and is emailed out automatically to a group of people. The whole point of automating this was that nobody would have to go in and change anything. This certainly isn't a critical issue, it just looks unprofessional.

Funky formatting of percentages in Excel export

I've created a report that has cells in it that I want to be formatted as percentages. I set the format code in the cell properties to "P1" and everything appears to work fine until I export to Excel. Some cells show up with only one digit after the decimal and some show up with two digits after the decimal.

I looked at the formatting in Excel (right click, Format Cells...) and it is set to "[$-1010409]#,##0.0#%". I don't have the first clue what that is "supposed" to do but what it does is this:

If I enter 100.00 in the cell it shows up as 100.0%. Expected

If I enter 100.10 in the cell it shows up as 100.1%. Expected

If I enter 100.15 in the cell it is displayed as 100.15%. Not expected

I have formatted the report to only show one digit after the decimal, but RS exports to Excel with some funky formatting that sometimes shows one digit and sometimes two digits after the decimal. Why wouldn't it simply format the cell as a percent? How can I get it to only show a single digit after the decimal?

This is Excel 2007 but Excel 2003 has the same behavior.

Thanks

--John

John T. Williams wrote:

"[$-1010409]#,##0.0%"

Try that in Excel.

|||

I'm not sure what that reply is supposed to be suggesting. The format code [$-1010409]#,##0.0% already exists in an Excel spreadsheet that was created when exporting a report.

The problem is that the format code in Excel (created by Reporting Services) is causing inconsistent formatting of percentages.

|||

You said that excel formated the cell to this:

[$-1010409]#,##0.0#%

I'm saying to try this:

[$-1010409]#,##0.0%

I'm thinking the last # tells it to display the second decimal place if the number has one.

|||

Sorry, I didn't see that anything was different in the code you included. Yes, that does work. However, the problem is that I don't know of a way to control what Reporting Services tells Excel to use as a formatting code. So, the question remains, why does Reporting Services create an Excel formatting code that causes inconsistent behavior? Is there a workaround for this? If this is a bug, how can I report it so that it gets looked at?

This report runs and exports using a subscription and is emailed out automatically to a group of people. The whole point of automating this was that nobody would have to go in and change anything. This certainly isn't a critical issue, it just looks unprofessional.

Function with CASE Statements

Hi,

Uses: SQL Server 2000 + Winxp PRO;

I Created a Function as below:

<CODE>
CREATE FUNCTION [GetDestinationOperator] (@.Dest VARCHAR(24))
RETURNS VARCHAR(15)
AS
BEGIN

DECLARE @.Op VARCHAR(15)

SET @.Dest = RTRIM(@.Dest)

CASE LEN(@.Dest)
WHEN 13 THEN SET @.Op = 'IDD'
WHEN 10 THEN
CASE SUBSTRING (@.Dest , 1 , 3 )
WHEN '071' THEN SET @.Op = 'MOBITEL'
WHEN '072' THEN SET @.Op = 'CELTEL'
WHEN '077' THEN SET @.Op = 'DIALOG'
WHEN '078' THEN SET @.Op = 'HUTCH'
ELSE SET @.Op = 'NATIONAL'
END
WHEN 7 THEN
CASE SUBSTRING (@.Dest , 1 , 1 )
WHEN '2' THEN SET @.Op = 'SLT'
WHEN '4' THEN SET @.Op = 'SUNTEL'
WHEN '5' THEN SET @.Op = 'LANKA BELL'
ELSE SET @.Op = 'LOCAL'
END
WHEN 3 THEN SET @.Op = 'INTERNAL'
ELSE SET @.Op = 'UNKNOWN'
END

RETRUN @.Op

END
<\CODE>

However when I checked the Code I written I get error Message as saying:

Error = 156:
Incorrect Syntax near the Keyword CASE
Incorrect Syntax near the Keyword WHEN
.
.
.

However when I executed the Query which I used with my database mentioned below, I get no errors:

<CODE>

SELECT CASE LEN(CalledNo)
WHEN 13 THEN 'IDD'
WHEN 10 THEN
CASE SUBSTRING (CalledNo , 1 , 3 )
WHEN '071' THEN 'MOBITEL'
WHEN '072' THEN 'CELTEL'
WHEN '077' THEN 'DIALOG'
WHEN '078' THEN 'HUTCH'
ELSE 'NATIONAL'
END
WHEN 7 THEN
CASE SUBSTRING (CalledNo , 1 , 1 )
WHEN '2' THEN 'SLT'
WHEN '4' THEN 'SUNTEL'
WHEN '5' THEN 'LANKA BELL'
ELSE 'LOCAL'
END
WHEN 3 THEN 'INTERNAL'
ELSE 'UNKNOWN'
END, CalledNo
FROM PABX
WHERE ExtNo = 204

<\CODE>

What might be the problem here?

Regards,

Hifni

Can you try this
CREATE FUNCTION GetDestinationOperator (@.Dest VARCHAR(24))
RETURNS VARCHAR(15)
AS
BEGIN

DECLARE @.Op VARCHAR(15)

SET @.Dest = RTRIM(@.Dest)
select @.op=
CASE LEN(@.Dest)
WHEN 13 THEN 'IDD'
WHEN 10 THEN
CASE SUBSTRING (@.Dest , 1 , 3 )
WHEN '071' THEN 'MOBITEL'
WHEN '072' THEN 'CELTEL'
WHEN '077' THEN 'DIALOG'
WHEN '078' THEN 'HUTCH'
ELSE 'NATIONAL'
END
WHEN 7 THEN
CASE SUBSTRING (@.Dest , 1 , 1 )
WHEN '2' THEN 'SLT'
WHEN '4' THEN 'SUNTEL'
WHEN '5' THEN 'LANKA BELL'
ELSE 'LOCAL'
END
WHEN 3 THEN 'INTERNAL'
ELSE 'UNKNOWN'
END

RETURN @.Op

END|||The case statement cannot stand alone even within a fuction. Create a variable called @.result and modify your fuction to Select @.result = <your case statement> then return the result.|||

Replace the CASE statment with a series of

IF condition

BEGIN

...

END

That should work

|||CASE is an expression not a control of flow statement. So you need to either assign the variable @.op using the value of the CASE expression or use IF...ELSE statements. Also, you should avoid writing scalar UDFs like this which perform lookup operations and use it in SELECT statements. You will get sub-optimal performance. It is based to model the lookup in a table and then perform a simple SELECT operation. This also gives added flexibility and performs better. Or you can inline the expression in the UDF in a view or TVF and use that instead.|||

Hi Everyone,
Thanks for all of your responses. For Eisa, I tried your sql and it did work wery well. Thanks for it and Umarchandra who gave a technical detail of it, thanks as well and for the rest.

Regards,

Function Decryption

I would highly appreciate if any one can let me know the Decryption of one
of mine function. One of the database developer created that functions and
he is no more in my organization and now I want to decrypt that function. I
don't have a source, can any one give me any way ?
Thanks
Roger wrote:
> I would highly appreciate if any one can let me know the Decryption
> of one of mine function. One of the database developer created that
> functions and he is no more in my organization and now I want to
> decrypt that function. I don't have a source, can any one give me any
> way ?
Google?
David G.

Function can be used in one server and not another

I have created a Function and I can call it in one server and i created
it in another server in my database and I get the error that object
does not exists in master.dbo
how do i get this funtion to work
i can see the function in the list.Hi,
You can use four part naming covention to call object from diffrent server
i.e. server.database.owner.objet
But before this you need to add that server as linked server using
sp_addlinkserver
vishal.sql@.gmail.com wrote:
>I have created a Function and I can call it in one server and i created
>it in another server in my database and I get the error that object
>does not exists in master.dbo
>how do i get this funtion to work
>i can see the function in the list.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1

Function can be used in one server and not another

I have created a Function and I can call it in one server and i created
it in another server in my database and I get the error that object
does not exists in master.dbo
how do i get this funtion to work
i can see the function in the list.Hi,
You can use four part naming covention to call object from diffrent server
i.e. server.database.owner.objet
But before this you need to add that server as linked server using
sp_addlinkserver
vishal.sql@.gmail.com wrote:
>I have created a Function and I can call it in one server and i created
>it in another server in my database and I get the error that object
>does not exists in master.dbo
>how do i get this funtion to work
>i can see the function in the list.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1

Full-text searching

I created a catalog by:
Use Pubs
EXEC sp_fulltext_catalog 'Cat_Test', 'create'
EXEC sp_fulltext_table 'publishers', 'create', 'Cat_Test', 'upkcl_pubind'
EXEC sp_fulltext_column 'publishers','pub_name','add'
EXEC sp_fulltext_column 'publishers','city','add'
EXEC sp_fulltext_column 'publishers','state','add'
EXEC sp_fulltext_table 'publishers','activate'
EXEC sp_fulltext_catalog 'Cat_Test', 'start_full'
EXEC sp_fulltext_table 'publishers','start_change_tracking'
EXEC sp_fulltext_table 'publishers','start_background_updateind
ex'
If I use the SQL
select city
from publishers
where contains (city, '"Paris"')
It return no record.
However, if I use
select *
from publishers
where city = 'Paris'
returned one record.The code you sent is working perfect for me.
Make sure that the Windows account used by the full-text service has
permissions on the SQL Server instance. I have seen that issue a few times
before.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Alan" wrote:

> I created a catalog by:
> Use Pubs
> EXEC sp_fulltext_catalog 'Cat_Test', 'create'
> EXEC sp_fulltext_table 'publishers', 'create', 'Cat_Test', 'upkcl_pubind'
> EXEC sp_fulltext_column 'publishers','pub_name','add'
> EXEC sp_fulltext_column 'publishers','city','add'
> EXEC sp_fulltext_column 'publishers','state','add'
> EXEC sp_fulltext_table 'publishers','activate'
> EXEC sp_fulltext_catalog 'Cat_Test', 'start_full'
> EXEC sp_fulltext_table 'publishers','start_change_tracking'
> EXEC sp_fulltext_table 'publishers','start_background_updateind
ex'
> If I use the SQL
> select city
> from publishers
> where contains (city, '"Paris"')
> It return no record.
> However, if I use
> select *
> from publishers
> where city = 'Paris'
> returned one record.
>
>|||Are you saying you can use
select city
from publishers
where contains (city, '"Paris"')
returns a record ?
Can you elaborate what do you mean by
> Make sure that the Windows account used by the full-text service has
> permissions on the SQL Server instance.
I am using Developer Edition.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:797F3FC7-2D47-4340-9683-CA11235E74E4@.microsoft.com...[vbcol=seagreen]
> The code you sent is working perfect for me.
> Make sure that the Windows account used by the full-text service has
> permissions on the SQL Server instance. I have seen that issue a few times
> before.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Alan" wrote:
>
'upkcl_pubind'[vbcol=seagreen]|||You better try this
select city
from publishers
where contains (city, 'Paris')
Go to Services on your computer and see which acount your full-text service
is using. The service name could be something like Microsoft Search or SQL
Server FullText Search. Then verify or give permissions to this account on
SQL Server.
I have seen before this service running as Local System. So, in this case NT
AUTHORITY\SYSTEM must be given permissions on the SQL Server instance.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Alan" wrote:

> Are you saying you can use
> select city
> from publishers
> where contains (city, '"Paris"')
> returns a record ?
> Can you elaborate what do you mean by
> I am using Developer Edition.
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:797F3FC7-2D47-4340-9683-CA11235E74E4@.microsoft.com...
> 'upkcl_pubind'
>
>|||Hi Ben,
Yes, the service is running as Local System.
Can you tell me how do I give permission to the NTAUTHORITY/SYSTEM please ?
Thanks
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:8868199F-1F62-4528-A783-D54A28875697@.microsoft.com...
> You better try this
> select city
> from publishers
> where contains (city, 'Paris')
> Go to Services on your computer and see which acount your full-text
service
> is using. The service name could be something like Microsoft Search or SQL
> Server FullText Search. Then verify or give permissions to this account on
> SQL Server.
> I have seen before this service running as Local System. So, in this case
NT[vbcol=seagreen]
> AUTHORITY\SYSTEM must be given permissions on the SQL Server instance.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Alan" wrote:
>
times[vbcol=seagreen]|||I checked in EM that the BUILTIN\Administrator has all permissions on all
databases.
And the database roles are public and db_owner.
I tried to add more permission on the role for the databases but got an
error:
"Error 15404: Cannot use the reserved user or role name 'dbo'"
So that I cannot add more permission to the BUILTIN\Administrator.
"Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:uHGuzK7dGHA.3948@.TK2MSFTNGP03.phx.gbl...
> Hi Ben,
> Yes, the service is running as Local System.
> Can you tell me how do I give permission to the NTAUTHORITY/SYSTEM please
?
> Thanks
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:8868199F-1F62-4528-A783-D54A28875697@.microsoft.com...
> service
SQL[vbcol=seagreen]
on[vbcol=seagreen]
case[vbcol=seagreen]
> NT
> times
>|||Hi Ben,
I am still in the trouble of not getting the full-text search working.
Could you direct me how do I , as you said, to give permission to my login
account on the SQL Server ?
My situation is:
1) Windows XP
2) Windows login is 'Alan', password 'Alan'
3) SQL Server 2000 Developer edition use Windows authentication
4) Run EM
5) Open QA inside EM
6) Run the T-SQL to create catalog and include tables from pubs database
7) Checked the catalog status is idle in EM and also got no result from the
query using CONTAINS in QA.
8) I had populate the catalog but the status is still idle
I am really worry about because I really need to get that done very soon but
I get stuck here.
Thank you for your time
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:8868199F-1F62-4528-A783-D54A28875697@.microsoft.com...
> You better try this
> select city
> from publishers
> where contains (city, 'Paris')
> Go to Services on your computer and see which acount your full-text
service
> is using. The service name could be something like Microsoft Search or SQL
> Server FullText Search. Then verify or give permissions to this account on
> SQL Server.
> I have seen before this service running as Local System. So, in this case
NT[vbcol=seagreen]
> AUTHORITY\SYSTEM must be given permissions on the SQL Server instance.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Alan" wrote:
>
times[vbcol=seagreen]

Full-text searching

I created a catalog by:
Use Pubs
EXEC sp_fulltext_catalog 'Cat_Test', 'create'
EXEC sp_fulltext_table 'publishers', 'create', 'Cat_Test', 'upkcl_pubind'
EXEC sp_fulltext_column 'publishers','pub_name','add'
EXEC sp_fulltext_column 'publishers','city','add'
EXEC sp_fulltext_column 'publishers','state','add'
EXEC sp_fulltext_table 'publishers','activate'
EXEC sp_fulltext_catalog 'Cat_Test', 'start_full'
EXEC sp_fulltext_table 'publishers','start_change_tracking'
EXEC sp_fulltext_table 'publishers','start_background_updateindex'
If I use the SQL
select city
from publishers
where contains (city, '"Paris"')
It return no record.
However, if I use
select *
from publishers
where city = 'Paris'
returned one record.The code you sent is working perfect for me.
Make sure that the Windows account used by the full-text service has
permissions on the SQL Server instance. I have seen that issue a few times
before.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Alan" wrote:
> I created a catalog by:
> Use Pubs
> EXEC sp_fulltext_catalog 'Cat_Test', 'create'
> EXEC sp_fulltext_table 'publishers', 'create', 'Cat_Test', 'upkcl_pubind'
> EXEC sp_fulltext_column 'publishers','pub_name','add'
> EXEC sp_fulltext_column 'publishers','city','add'
> EXEC sp_fulltext_column 'publishers','state','add'
> EXEC sp_fulltext_table 'publishers','activate'
> EXEC sp_fulltext_catalog 'Cat_Test', 'start_full'
> EXEC sp_fulltext_table 'publishers','start_change_tracking'
> EXEC sp_fulltext_table 'publishers','start_background_updateindex'
> If I use the SQL
> select city
> from publishers
> where contains (city, '"Paris"')
> It return no record.
> However, if I use
> select *
> from publishers
> where city = 'Paris'
> returned one record.
>
>|||Are you saying you can use
select city
from publishers
where contains (city, '"Paris"')
returns a record ?
Can you elaborate what do you mean by
> Make sure that the Windows account used by the full-text service has
> permissions on the SQL Server instance.
I am using Developer Edition.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:797F3FC7-2D47-4340-9683-CA11235E74E4@.microsoft.com...
> The code you sent is working perfect for me.
> Make sure that the Windows account used by the full-text service has
> permissions on the SQL Server instance. I have seen that issue a few times
> before.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Alan" wrote:
> > I created a catalog by:
> >
> > Use Pubs
> >
> > EXEC sp_fulltext_catalog 'Cat_Test', 'create'
> >
> > EXEC sp_fulltext_table 'publishers', 'create', 'Cat_Test',
'upkcl_pubind'
> >
> > EXEC sp_fulltext_column 'publishers','pub_name','add'
> >
> > EXEC sp_fulltext_column 'publishers','city','add'
> >
> > EXEC sp_fulltext_column 'publishers','state','add'
> >
> > EXEC sp_fulltext_table 'publishers','activate'
> >
> > EXEC sp_fulltext_catalog 'Cat_Test', 'start_full'
> >
> > EXEC sp_fulltext_table 'publishers','start_change_tracking'
> >
> > EXEC sp_fulltext_table 'publishers','start_background_updateindex'
> >
> > If I use the SQL
> >
> > select city
> > from publishers
> > where contains (city, '"Paris"')
> >
> > It return no record.
> >
> > However, if I use
> >
> > select *
> > from publishers
> > where city = 'Paris'
> >
> > returned one record.
> >
> >
> >
> >|||You better try this
select city
from publishers
where contains (city, 'Paris')
Go to Services on your computer and see which acount your full-text service
is using. The service name could be something like Microsoft Search or SQL
Server FullText Search. Then verify or give permissions to this account on
SQL Server.
I have seen before this service running as Local System. So, in this case NT
AUTHORITY\SYSTEM must be given permissions on the SQL Server instance.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Alan" wrote:
> Are you saying you can use
> select city
> from publishers
> where contains (city, '"Paris"')
> returns a record ?
> Can you elaborate what do you mean by
> > Make sure that the Windows account used by the full-text service has
> > permissions on the SQL Server instance.
> I am using Developer Edition.
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:797F3FC7-2D47-4340-9683-CA11235E74E4@.microsoft.com...
> >
> > The code you sent is working perfect for me.
> >
> > Make sure that the Windows account used by the full-text service has
> > permissions on the SQL Server instance. I have seen that issue a few times
> > before.
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "Alan" wrote:
> >
> > > I created a catalog by:
> > >
> > > Use Pubs
> > >
> > > EXEC sp_fulltext_catalog 'Cat_Test', 'create'
> > >
> > > EXEC sp_fulltext_table 'publishers', 'create', 'Cat_Test',
> 'upkcl_pubind'
> > >
> > > EXEC sp_fulltext_column 'publishers','pub_name','add'
> > >
> > > EXEC sp_fulltext_column 'publishers','city','add'
> > >
> > > EXEC sp_fulltext_column 'publishers','state','add'
> > >
> > > EXEC sp_fulltext_table 'publishers','activate'
> > >
> > > EXEC sp_fulltext_catalog 'Cat_Test', 'start_full'
> > >
> > > EXEC sp_fulltext_table 'publishers','start_change_tracking'
> > >
> > > EXEC sp_fulltext_table 'publishers','start_background_updateindex'
> > >
> > > If I use the SQL
> > >
> > > select city
> > > from publishers
> > > where contains (city, '"Paris"')
> > >
> > > It return no record.
> > >
> > > However, if I use
> > >
> > > select *
> > > from publishers
> > > where city = 'Paris'
> > >
> > > returned one record.
> > >
> > >
> > >
> > >
>
>|||Hi Ben,
Yes, the service is running as Local System.
Can you tell me how do I give permission to the NTAUTHORITY/SYSTEM please ?
Thanks
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:8868199F-1F62-4528-A783-D54A28875697@.microsoft.com...
> You better try this
> select city
> from publishers
> where contains (city, 'Paris')
> Go to Services on your computer and see which acount your full-text
service
> is using. The service name could be something like Microsoft Search or SQL
> Server FullText Search. Then verify or give permissions to this account on
> SQL Server.
> I have seen before this service running as Local System. So, in this case
NT
> AUTHORITY\SYSTEM must be given permissions on the SQL Server instance.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Alan" wrote:
> > Are you saying you can use
> > select city
> > from publishers
> > where contains (city, '"Paris"')
> >
> > returns a record ?
> >
> > Can you elaborate what do you mean by
> > > Make sure that the Windows account used by the full-text service has
> > > permissions on the SQL Server instance.
> >
> > I am using Developer Edition.
> >
> >
> > "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> > news:797F3FC7-2D47-4340-9683-CA11235E74E4@.microsoft.com...
> > >
> > > The code you sent is working perfect for me.
> > >
> > > Make sure that the Windows account used by the full-text service has
> > > permissions on the SQL Server instance. I have seen that issue a few
times
> > > before.
> > >
> > > Ben Nevarez, MCDBA, OCP
> > > Database Administrator
> > >
> > >
> > > "Alan" wrote:
> > >
> > > > I created a catalog by:
> > > >
> > > > Use Pubs
> > > >
> > > > EXEC sp_fulltext_catalog 'Cat_Test', 'create'
> > > >
> > > > EXEC sp_fulltext_table 'publishers', 'create', 'Cat_Test',
> > 'upkcl_pubind'
> > > >
> > > > EXEC sp_fulltext_column 'publishers','pub_name','add'
> > > >
> > > > EXEC sp_fulltext_column 'publishers','city','add'
> > > >
> > > > EXEC sp_fulltext_column 'publishers','state','add'
> > > >
> > > > EXEC sp_fulltext_table 'publishers','activate'
> > > >
> > > > EXEC sp_fulltext_catalog 'Cat_Test', 'start_full'
> > > >
> > > > EXEC sp_fulltext_table 'publishers','start_change_tracking'
> > > >
> > > > EXEC sp_fulltext_table 'publishers','start_background_updateindex'
> > > >
> > > > If I use the SQL
> > > >
> > > > select city
> > > > from publishers
> > > > where contains (city, '"Paris"')
> > > >
> > > > It return no record.
> > > >
> > > > However, if I use
> > > >
> > > > select *
> > > > from publishers
> > > > where city = 'Paris'
> > > >
> > > > returned one record.
> > > >
> > > >
> > > >
> > > >
> >
> >
> >|||I checked in EM that the BUILTIN\Administrator has all permissions on all
databases.
And the database roles are public and db_owner.
I tried to add more permission on the role for the databases but got an
error:
"Error 15404: Cannot use the reserved user or role name 'dbo'"
So that I cannot add more permission to the BUILTIN\Administrator.
"Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:uHGuzK7dGHA.3948@.TK2MSFTNGP03.phx.gbl...
> Hi Ben,
> Yes, the service is running as Local System.
> Can you tell me how do I give permission to the NTAUTHORITY/SYSTEM please
?
> Thanks
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:8868199F-1F62-4528-A783-D54A28875697@.microsoft.com...
> >
> > You better try this
> > select city
> > from publishers
> > where contains (city, 'Paris')
> >
> > Go to Services on your computer and see which acount your full-text
> service
> > is using. The service name could be something like Microsoft Search or
SQL
> > Server FullText Search. Then verify or give permissions to this account
on
> > SQL Server.
> >
> > I have seen before this service running as Local System. So, in this
case
> NT
> > AUTHORITY\SYSTEM must be given permissions on the SQL Server instance.
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "Alan" wrote:
> >
> > > Are you saying you can use
> > > select city
> > > from publishers
> > > where contains (city, '"Paris"')
> > >
> > > returns a record ?
> > >
> > > Can you elaborate what do you mean by
> > > > Make sure that the Windows account used by the full-text service has
> > > > permissions on the SQL Server instance.
> > >
> > > I am using Developer Edition.
> > >
> > >
> > > "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> > > news:797F3FC7-2D47-4340-9683-CA11235E74E4@.microsoft.com...
> > > >
> > > > The code you sent is working perfect for me.
> > > >
> > > > Make sure that the Windows account used by the full-text service has
> > > > permissions on the SQL Server instance. I have seen that issue a few
> times
> > > > before.
> > > >
> > > > Ben Nevarez, MCDBA, OCP
> > > > Database Administrator
> > > >
> > > >
> > > > "Alan" wrote:
> > > >
> > > > > I created a catalog by:
> > > > >
> > > > > Use Pubs
> > > > >
> > > > > EXEC sp_fulltext_catalog 'Cat_Test', 'create'
> > > > >
> > > > > EXEC sp_fulltext_table 'publishers', 'create', 'Cat_Test',
> > > 'upkcl_pubind'
> > > > >
> > > > > EXEC sp_fulltext_column 'publishers','pub_name','add'
> > > > >
> > > > > EXEC sp_fulltext_column 'publishers','city','add'
> > > > >
> > > > > EXEC sp_fulltext_column 'publishers','state','add'
> > > > >
> > > > > EXEC sp_fulltext_table 'publishers','activate'
> > > > >
> > > > > EXEC sp_fulltext_catalog 'Cat_Test', 'start_full'
> > > > >
> > > > > EXEC sp_fulltext_table 'publishers','start_change_tracking'
> > > > >
> > > > > EXEC sp_fulltext_table 'publishers','start_background_updateindex'
> > > > >
> > > > > If I use the SQL
> > > > >
> > > > > select city
> > > > > from publishers
> > > > > where contains (city, '"Paris"')
> > > > >
> > > > > It return no record.
> > > > >
> > > > > However, if I use
> > > > >
> > > > > select *
> > > > > from publishers
> > > > > where city = 'Paris'
> > > > >
> > > > > returned one record.
> > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>|||Hi Ben,
I am still in the trouble of not getting the full-text search working.
Could you direct me how do I , as you said, to give permission to my login
account on the SQL Server ?
My situation is:
1) Windows XP
2) Windows login is 'Alan', password 'Alan'
3) SQL Server 2000 Developer edition use Windows authentication
4) Run EM
5) Open QA inside EM
6) Run the T-SQL to create catalog and include tables from pubs database
7) Checked the catalog status is idle in EM and also got no result from the
query using CONTAINS in QA.
8) I had populate the catalog but the status is still idle
I am really worry about because I really need to get that done very soon but
I get stuck here.
Thank you for your time
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:8868199F-1F62-4528-A783-D54A28875697@.microsoft.com...
> You better try this
> select city
> from publishers
> where contains (city, 'Paris')
> Go to Services on your computer and see which acount your full-text
service
> is using. The service name could be something like Microsoft Search or SQL
> Server FullText Search. Then verify or give permissions to this account on
> SQL Server.
> I have seen before this service running as Local System. So, in this case
NT
> AUTHORITY\SYSTEM must be given permissions on the SQL Server instance.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Alan" wrote:
> > Are you saying you can use
> > select city
> > from publishers
> > where contains (city, '"Paris"')
> >
> > returns a record ?
> >
> > Can you elaborate what do you mean by
> > > Make sure that the Windows account used by the full-text service has
> > > permissions on the SQL Server instance.
> >
> > I am using Developer Edition.
> >
> >
> > "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> > news:797F3FC7-2D47-4340-9683-CA11235E74E4@.microsoft.com...
> > >
> > > The code you sent is working perfect for me.
> > >
> > > Make sure that the Windows account used by the full-text service has
> > > permissions on the SQL Server instance. I have seen that issue a few
times
> > > before.
> > >
> > > Ben Nevarez, MCDBA, OCP
> > > Database Administrator
> > >
> > >
> > > "Alan" wrote:
> > >
> > > > I created a catalog by:
> > > >
> > > > Use Pubs
> > > >
> > > > EXEC sp_fulltext_catalog 'Cat_Test', 'create'
> > > >
> > > > EXEC sp_fulltext_table 'publishers', 'create', 'Cat_Test',
> > 'upkcl_pubind'
> > > >
> > > > EXEC sp_fulltext_column 'publishers','pub_name','add'
> > > >
> > > > EXEC sp_fulltext_column 'publishers','city','add'
> > > >
> > > > EXEC sp_fulltext_column 'publishers','state','add'
> > > >
> > > > EXEC sp_fulltext_table 'publishers','activate'
> > > >
> > > > EXEC sp_fulltext_catalog 'Cat_Test', 'start_full'
> > > >
> > > > EXEC sp_fulltext_table 'publishers','start_change_tracking'
> > > >
> > > > EXEC sp_fulltext_table 'publishers','start_background_updateindex'
> > > >
> > > > If I use the SQL
> > > >
> > > > select city
> > > > from publishers
> > > > where contains (city, '"Paris"')
> > > >
> > > > It return no record.
> > > >
> > > > However, if I use
> > > >
> > > > select *
> > > > from publishers
> > > > where city = 'Paris'
> > > >
> > > > returned one record.
> > > >
> > > >
> > > >
> > > >
> >
> >
> >

FullText Search

I have create a table called tblcatalog with colums id(identity,primary key) and contents(varchar(100))

I have then created a full text catalog on that table and populated it.

Then i wrote the following query
"select contents from tblcatalog where contains(contents,'sample data')"
It is fetching 0 records even though u have 5 records with entries "sample data"

Can anyone tell me the solution immediately

bye
shankyImmediately? Have you tried everything you could think of before asking for help? It'll take me 20 minutes or so to setup FullText Search, and will take you probably much less to experiment with it.|||when you say you populated it did you create an initial full population?|||Forgive me if this is basic to you. Just trying to be thorough. :)
In Enterprise Manager, right click on your table. Choose 'Full Text Index Table' (MS Search service must be started for this to appear.) Choose 'Start full population.' Rerun your query. If it works, you must not have really done a full population yet. In that case you may want to set up incremental or full populations using the 'Schedules..' option on the same context menu...
If not, I have no clue what the problem is! :)