Showing posts with label table. Show all posts
Showing posts with label table. Show all posts

Thursday, March 22, 2012

Ater table transactional replication

I have SQL Server 2000 transactional replication and it
works fine.
I need to break the replication to apply the vendor script
to the database. The vendor will modify database
objects.
I delete the transaction replication subscription.
I tried to alter a table and received an error stating
that the table could not be altered because it was
replicated.
What is the easiest procedure so that can apply the vendor
script.
Thanks,
Mark
Mark,
it depends on your particular modification, but in most cases you could use
sp_repladdcolumn and sp_repldropcolumn. Using these procedures there's no
need to drop the subscription. For more radical changes, you could also drop
the article from the publication and add it back in.
HTH,
Paul Ibison
|||And for your specific one, run sp_removedbreplication. If for some reason
it still gives you that error, turn on allow updates, update the replinfo
column in sysobjects and set it to 0, turn off allow updates.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||This seems a little drastic just to modify the schema. I think Paul's
response is more appropriate.
Rand
This posting is provided "as is" with no warranties and confers no rights.
sql

At my wits' end: LIKE

(SQL Server 2005, express edition)

I have a list of table names that I need to translate according to a
naming convention. I'm doing this using pattern matching in a LIKE
clause.

In one specific case I get no match where I believe that there should
be one. I must be missing something obvious here, but what?

I have boiled it down to this example (the real one is more complex):

Matching on the first four characters I get a match:

select 'yes'
where 'TBAAA243_D_AFTBEL' like 'TBAA%';

--
yes

(1 row(s) affected)

That is fine, just as I would have expected. But if I try to match
only on the first 3 characters, I get this:

select 'yes'
where 'TBAAA243_D_AFTBEL' like 'TBA%';

(0 row(s) affected)

I have also tried the same on enterprise edition and get the same
strange result. Language is set to us_english

What am I missing here?

Any help appreciated, before I tear out the very last of my remaining
hair

Bo BrunsgaardHave you applied any service packs? If not, try installing Express SP2
(http://www.microsoft.com/downloads/...displaylang=en).
I get the correct results on my SP2 Developer Edition instance:

select 'yes'
where 'TBAAA243_D_AFTBEL' like 'TBA%';

--
yes

(1 row(s) affected)

--
Hope this helps.

Dan Guzman
SQL Server MVP

<bbcworldtour@.hotmail.comwrote in message
news:1176718533.092593.280820@.b75g2000hsg.googlegr oups.com...

Quote:

Originally Posted by

(SQL Server 2005, express edition)
>
I have a list of table names that I need to translate according to a
naming convention. I'm doing this using pattern matching in a LIKE
clause.
>
In one specific case I get no match where I believe that there should
be one. I must be missing something obvious here, but what?
>
I have boiled it down to this example (the real one is more complex):
>
Matching on the first four characters I get a match:
>
select 'yes'
where 'TBAAA243_D_AFTBEL' like 'TBAA%';
>
--
yes
>
(1 row(s) affected)
>
>
That is fine, just as I would have expected. But if I try to match
only on the first 3 characters, I get this:
>
select 'yes'
where 'TBAAA243_D_AFTBEL' like 'TBA%';
>
(0 row(s) affected)
>
I have also tried the same on enterprise edition and get the same
strange result. Language is set to us_english
>
What am I missing here?
>
Any help appreciated, before I tear out the very last of my remaining
hair
>
Bo Brunsgaard
>

|||On 16 Apr., 13:46, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:

Quote:

Originally Posted by

Have you applied any service packs? If not, try installing Express SP2
(http://www.microsoft.com/downloads/...711d5d-725...).
I get the correct results on my SP2 Developer Edition instance:


I upgraded to SP2, but the problem persisted. It turns out that it is
hidden deep inside the finer points of the database collation. I
thought this was kind of interesting in a low-intense way, so here's
the story:

Our databases are running a collation of Danish_Norwegian_CS_AS (we
are a Danish company).

In Danish we have three special phonemes that are represented in
writing as the letters , and . These three letters are
alphabetically placed as the last three letters of the alphabet.

The last one turns out to the culprit (if it doesn't show up proper
imagine an upper-cased A with a small circle superimposed on it).

Using the letter for the phoneme [] is a fairly recent addition to
Danish (around 1950's). Traditionally it was written as "AA". For
instance, my surname can be written as either "Brunsgrd" or
"Brunsgaard", but is still considered the same name.

So in Danish, "AA" can be either the traditional writing of the
phoneme [] OR just two "A"s which happen to be consecutive.

Danish_Norwegian_CS_AS collation recognizes "AA" as "". This is
usually real neat for sorting. Consider the lastnames "grd" and
"Aagaard" - these should be sorted together at the end of a list, and
using any Danish_Norwegian collation will ensure just that.

Consider:

create table taDanishDemo
(
nameInDanish varchar(30)
collate Danish_Norwegian_CS_AS

, nameInEnglish varchar(30)
collate Latin1_General_CS_AS
)
;

Let us insert a couple of rows which contain a case of consecutive
"A"s:

insert
into taDanishDemo (nameInDanish,nameInEnglish)
select 'TBAAA','TBAAA'
union all
select 'TBABA','TBABA'
;

Retrieving the rows ordered will now yield different results depending
on whether we order on the Danish or the Latin1 collated column:

select nameInEnglish
from taDanishDemo
order by nameInEnglish;

nameInEnglish
----------
TBAAA
TBABA

Under Latin1 collation the "AA" is considered just two concecutive
"A"'s and ordered at the beginning of the list.
But, under Danish collation, the "AA" is considered the traditional
writing of [], and placed at the end of the list:

select nameInDanish
from taDanishDemo
order by nameInDanish;

nameInDanish
----------
TBABA
TBAAA

So far, so good.

What threw me completely is that this also affect how the string "AA"
is interpreted by the LIKE operator.

select nameInDanish
from taDanishDemo
where nameInDanish like 'TBA%'

nameInDanish
----------
TBABA

The row containing "TBAAA" isn't returned Trying to match "AA" with an
"A" plus a wildcard will yield no match under Danish collation, since
SQL Serve interprets this as trying to match "" with "A"!

But under Latin1 collation "AA" does match "A" and a wildcard, as "AA"
is just two "A"'s

select nameInEnglish
from taDanishDemo
where nameInEnglish like 'TBA%'

nameInEnglish
----------
TBAAA
TBABA

I'm still not really sure whether this is a useful feature, an
unintended side effect or a bug :-)

Bo Brunsgaard|||Our databases are running a collation of Danish_Norwegian_CS_AS (we

Quote:

Originally Posted by

are a Danish company).


I'm glad you were able to identify the root cause. I briefly considered a
possible collation issue but didn't think that would explain your symptoms
since I didn't know that collation rules considered consecutive characters.
Thanks a lot for the detailed analysis.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<bbcworldtour@.hotmail.comwrote in message
news:1176883896.875730.239770@.n76g2000hsh.googlegr oups.com...
On 16 Apr., 13:46, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:

Quote:

Originally Posted by

Have you applied any service packs? If not, try installing Express SP2
(http://www.microsoft.com/downloads/...1711d5d-725...).
I get the correct results on my SP2 Developer Edition instance:


I upgraded to SP2, but the problem persisted. It turns out that it is
hidden deep inside the finer points of the database collation. I
thought this was kind of interesting in a low-intense way, so here's
the story:

Our databases are running a collation of Danish_Norwegian_CS_AS (we
are a Danish company).

In Danish we have three special phonemes that are represented in
writing as the letters , and . These three letters are
alphabetically placed as the last three letters of the alphabet.

The last one turns out to the culprit (if it doesn't show up proper
imagine an upper-cased A with a small circle superimposed on it).

Using the letter for the phoneme [] is a fairly recent addition to
Danish (around 1950's). Traditionally it was written as "AA". For
instance, my surname can be written as either "Brunsgrd" or
"Brunsgaard", but is still considered the same name.

So in Danish, "AA" can be either the traditional writing of the
phoneme [] OR just two "A"s which happen to be consecutive.

Danish_Norwegian_CS_AS collation recognizes "AA" as "". This is
usually real neat for sorting. Consider the lastnames "grd" and
"Aagaard" - these should be sorted together at the end of a list, and
using any Danish_Norwegian collation will ensure just that.

Consider:

create table taDanishDemo
(
nameInDanish varchar(30)
collate Danish_Norwegian_CS_AS

, nameInEnglish varchar(30)
collate Latin1_General_CS_AS
)
;

Let us insert a couple of rows which contain a case of consecutive
"A"s:

insert
into taDanishDemo (nameInDanish,nameInEnglish)
select 'TBAAA','TBAAA'
union all
select 'TBABA','TBABA'
;

Retrieving the rows ordered will now yield different results depending
on whether we order on the Danish or the Latin1 collated column:

select nameInEnglish
from taDanishDemo
order by nameInEnglish;

nameInEnglish
----------
TBAAA
TBABA

Under Latin1 collation the "AA" is considered just two concecutive
"A"'s and ordered at the beginning of the list.
But, under Danish collation, the "AA" is considered the traditional
writing of [], and placed at the end of the list:

select nameInDanish
from taDanishDemo
order by nameInDanish;

nameInDanish
----------
TBABA
TBAAA

So far, so good.

What threw me completely is that this also affect how the string "AA"
is interpreted by the LIKE operator.

select nameInDanish
from taDanishDemo
where nameInDanish like 'TBA%'

nameInDanish
----------
TBABA

The row containing "TBAAA" isn't returned Trying to match "AA" with an
"A" plus a wildcard will yield no match under Danish collation, since
SQL Serve interprets this as trying to match "" with "A"!

But under Latin1 collation "AA" does match "A" and a wildcard, as "AA"
is just two "A"'s

select nameInEnglish
from taDanishDemo
where nameInEnglish like 'TBA%'

nameInEnglish
----------
TBAAA
TBABA

I'm still not really sure whether this is a useful feature, an
unintended side effect or a bug :-)

Bo Brunsgaard

Asynchronous Script Component

Hi--done some searching, but I am not finding exactly what I need. I am using an asynchronous script component as a lookup since my table I am looking up on requires an ODBC connection. Here is what my data looks like:

From an Excel connection:

Order Number

123

234

345

The table I want to do a lookup on has multiple rows for each order number, as well as a lot of rows that aren't in my first table:

Order Number Description

123 Upgrade to System

123 Freight

123 Spare Parts

234 Upgrade to System

234 Freight

234 Spare Parts

778 Another thing

889 Yet more stuff

etc. My desired result would be to pull all the items from table two that match on Order Number from table one. My actual results from the script I have is a single (random) row from table two for each item in table one.....So my current results look like:

Order Number Description

123 Freight

234 Freight

345 Null

And I want:

Order Number Description

123 Upgrade to System

123 Freight

123 Spare Parts

234 Upgrade to System

234 Freight

234 Spare Parts

345 Null

etc.... Here is my code, courtesy of half a dozen samples found here and elsewhere...

Code Snippet

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Data.Odbc


Public Class ScriptMain
Inherits UserComponent

Dim connMgr As IDTSConnectionManager90
Dim odbcConn As OdbcConnection
Dim odbcCmd As OdbcCommand
Dim odbcParam As OdbcParameter


Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

connMgr = Me.Connections.JDEConnection
odbcConn = CType(connMgr.AcquireConnection(Nothing), OdbcConnection)

End Sub

Public Overrides Sub PreExecute()

odbcCmd = New OdbcCommand("SELECT F4211.SDDSC1, F4211.SDDOCO FROM DB.F4211 F4211 Where F4201.SHDOCO = ?", odbcConn)

odbcParam = New OdbcParameter("1", OdbcType.Int)
odbcCmd.Parameters.Add(odbcParam)


End Sub


Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim reader As Odbc.OdbcDataReader
odbcCmd.Parameters("1").Value = Row.SO
odbcCmd.ExecuteNonQuery()
reader = odbcCmd.ExecuteReader()
If reader.Read() Then

With Output0Buffer
.AddRow()
.SDDSC1 = reader("SDDSC1").ToString
.SONumb = Row.SO
.SOJDE = CDec(reader("SDDOCO"))
End With


End If

reader.Close()

End Sub

Public Overrides Sub ReleaseConnections()
connMgr.ReleaseConnection(odbcConn)
End Sub


End Class

I just don't know what I need to do to get every row from F4211 where SDDOCO matches Row.SO instead of a single row...... Any ideas or help? Oh, the reason I am starting with my Excel connection is that sheet lists the Orders I need detailed data for, and is only a few hundred rows....F4211 is really really really big.

I have also worked out an alternate way to do this using merge join tasks...but then my datareader source goes off and fetches 300,000 rows from F4211 before my final result set of about 1200 rows. That just feels like a bad approach to me...or am I being over-cautious? I'm a newb (if you couldn't already tell)...so guidence is appreciated.

Thank you....

In a first data flow, you could load a staging table in SQL server with the contents of your ODBC source table. Then in a second data flow, you can use that staging table as the source for your lookup component. Might be a bit less work for you.|||So, to make sure I understand--add another data flow. Have it write the records from my F4211 table to a SQL table, then, in my original data flow, do a lookup on my newly created table in SQL...then, I suppose, add an Execute SQL task to blow all those records away? And, I suppose, just to be tidy about it....I could add a shrink database task to clean up afterwards.....?|||

In general and when possible it is a good idea to use staging tables to put all data pieces on the SQL Server side. Besides simplify the dataflow; it improves performance.

I think you are undeestanding Phil's sugestion pretty well; but I am not shure is I would bother with the shrink database step; if you are going to execute this process in a regular basis then you would need that space anyway.

|||Yep, you got it. Now have fun!|||

hilaryjade wrote:

I just don't know what I need to do to get every row from F4211 where SDDOCO matches Row.SO instead of a single row...... Any ideas or help? Oh, the reason I am starting with my Excel connection is that sheet lists the Orders I need detailed data for, and is only a few hundred rows....F4211 is really really really big.

So, to go back to the original question.... You want all the rows from the recordset, right? Don't you just need to change that If to a While loop?

Code Snippet


While reader.Read()

With Output0Buffer
.AddRow()
.SDDSC1 = reader("SDDSC1").ToString
.SONumb = Row.SO
.SOJDE = CDec(reader("SDDOCO"))
End With


End While



|||Told ya I was a newb....Thanks so much!!! While a staging table and lookup off it is an interesting idea (and I appreciate it...) the script runs so much faster.|||

hilaryjade wrote:

Told ya I was a newb....Thanks so much!!! While a staging table and lookup off it is an interesting idea (and I appreciate it...) the script runs so much faster.

Did you test it? Can you share the timing results and row counts?

|||I haven't finished adding my full data set I need to pull to my script component yet, but after I do, I can run and time them. I did try adding and using a staging table last night--but ran into a bit of a data type mismatch roadblock on the lookup--I tried a handful of conversions to see if I could get my DT_R8 from Excel to play nicely with my Numeric from the staging table in SQL, but got a bit frustrated and went off to work on my third alternative...using merge joins (which works nicely and runs in 2.2 minutes, but 2 minutes starts feeling a bit long, you know?). At any rate, just creating the staging table took longer than the script takes (but, again, that was without my full data set)....After I get the script component complete and pulling all my data, I'll run, time, and post results. Again, thanks for all the help!|||

Well, I'm not really comparing apples to apples with this, since my script component is part of a data flow that starts with a connection to an excel file, does a lookup on a table with an odbc connection via the script component and then writes to a recordset and the data flow for the staging table concept uses a data reader to collect my records from a table with an odbc connection and then writes them to a SQL db table...

The dataflow with the script component ran in 00:06.844 and wrote 953 rows to my recordset (just writing the rows I needed, selected via script component)

The dataflow to create a staging table ran in 01:16.313 and wrote 219,155 rows to a table in a SQL db, where I could then do a lookup to grab the records I need (953 rows)

I think for this instance, where I need so few records from such a large table, it makes sense to use the asynchronous script component rather than create a staging table.

Again, thanks to all for the help and suggestions. I really appreciate it.

|||Did you use the Fast Load option in the OLE DB Destination when using the staging table approach?|||Yes, on the Connection Manager page for the destination editor, Data access mode is set to Table or View - fast load.

Asynchronous Script Component

Hi--done some searching, but I am not finding exactly what I need. I am using an asynchronous script component as a lookup since my table I am looking up on requires an ODBC connection. Here is what my data looks like:

From an Excel connection:

Order Number

123

234

345

The table I want to do a lookup on has multiple rows for each order number, as well as a lot of rows that aren't in my first table:

Order Number Description

123 Upgrade to System

123 Freight

123 Spare Parts

234 Upgrade to System

234 Freight

234 Spare Parts

778 Another thing

889 Yet more stuff

etc. My desired result would be to pull all the items from table two that match on Order Number from table one. My actual results from the script I have is a single (random) row from table two for each item in table one.....So my current results look like:

Order Number Description

123 Freight

234 Freight

345 Null

And I want:

Order Number Description

123 Upgrade to System

123 Freight

123 Spare Parts

234 Upgrade to System

234 Freight

234 Spare Parts

345 Null

etc.... Here is my code, courtesy of half a dozen samples found here and elsewhere...

Code Snippet

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports System.Data.Odbc


Public Class ScriptMain
Inherits UserComponent

Dim connMgr As IDTSConnectionManager90
Dim odbcConn As OdbcConnection
Dim odbcCmd As OdbcCommand
Dim odbcParam As OdbcParameter


Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

connMgr = Me.Connections.JDEConnection
odbcConn = CType(connMgr.AcquireConnection(Nothing), OdbcConnection)

End Sub

Public Overrides Sub PreExecute()

odbcCmd = New OdbcCommand("SELECT F4211.SDDSC1, F4211.SDDOCO FROM DB.F4211 F4211 Where F4201.SHDOCO = ?", odbcConn)

odbcParam = New OdbcParameter("1", OdbcType.Int)
odbcCmd.Parameters.Add(odbcParam)


End Sub


Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim reader As Odbc.OdbcDataReader
odbcCmd.Parameters("1").Value = Row.SO
odbcCmd.ExecuteNonQuery()
reader = odbcCmd.ExecuteReader()
If reader.Read() Then

With Output0Buffer
.AddRow()
.SDDSC1 = reader("SDDSC1").ToString
.SONumb = Row.SO
.SOJDE = CDec(reader("SDDOCO"))
End With


End If

reader.Close()

End Sub

Public Overrides Sub ReleaseConnections()
connMgr.ReleaseConnection(odbcConn)
End Sub


End Class

I just don't know what I need to do to get every row from F4211 where SDDOCO matches Row.SO instead of a single row...... Any ideas or help? Oh, the reason I am starting with my Excel connection is that sheet lists the Orders I need detailed data for, and is only a few hundred rows....F4211 is really really really big.

I have also worked out an alternate way to do this using merge join tasks...but then my datareader source goes off and fetches 300,000 rows from F4211 before my final result set of about 1200 rows. That just feels like a bad approach to me...or am I being over-cautious? I'm a newb (if you couldn't already tell)...so guidence is appreciated.

Thank you....

In a first data flow, you could load a staging table in SQL server with the contents of your ODBC source table. Then in a second data flow, you can use that staging table as the source for your lookup component. Might be a bit less work for you.|||So, to make sure I understand--add another data flow. Have it write the records from my F4211 table to a SQL table, then, in my original data flow, do a lookup on my newly created table in SQL...then, I suppose, add an Execute SQL task to blow all those records away? And, I suppose, just to be tidy about it....I could add a shrink database task to clean up afterwards.....?|||

In general and when possible it is a good idea to use staging tables to put all data pieces on the SQL Server side. Besides simplify the dataflow; it improves performance.

I think you are undeestanding Phil's sugestion pretty well; but I am not shure is I would bother with the shrink database step; if you are going to execute this process in a regular basis then you would need that space anyway.

|||Yep, you got it. Now have fun!|||

hilaryjade wrote:

I just don't know what I need to do to get every row from F4211 where SDDOCO matches Row.SO instead of a single row...... Any ideas or help? Oh, the reason I am starting with my Excel connection is that sheet lists the Orders I need detailed data for, and is only a few hundred rows....F4211 is really really really big.

So, to go back to the original question.... You want all the rows from the recordset, right? Don't you just need to change that If to a While loop?

Code Snippet


While reader.Read()

With Output0Buffer
.AddRow()
.SDDSC1 = reader("SDDSC1").ToString
.SONumb = Row.SO
.SOJDE = CDec(reader("SDDOCO"))
End With


End While



|||Told ya I was a newb....Thanks so much!!! While a staging table and lookup off it is an interesting idea (and I appreciate it...) the script runs so much faster.|||

hilaryjade wrote:

Told ya I was a newb....Thanks so much!!! While a staging table and lookup off it is an interesting idea (and I appreciate it...) the script runs so much faster.

Did you test it? Can you share the timing results and row counts?

|||I haven't finished adding my full data set I need to pull to my script component yet, but after I do, I can run and time them. I did try adding and using a staging table last night--but ran into a bit of a data type mismatch roadblock on the lookup--I tried a handful of conversions to see if I could get my DT_R8 from Excel to play nicely with my Numeric from the staging table in SQL, but got a bit frustrated and went off to work on my third alternative...using merge joins (which works nicely and runs in 2.2 minutes, but 2 minutes starts feeling a bit long, you know?). At any rate, just creating the staging table took longer than the script takes (but, again, that was without my full data set)....After I get the script component complete and pulling all my data, I'll run, time, and post results. Again, thanks for all the help!|||

Well, I'm not really comparing apples to apples with this, since my script component is part of a data flow that starts with a connection to an excel file, does a lookup on a table with an odbc connection via the script component and then writes to a recordset and the data flow for the staging table concept uses a data reader to collect my records from a table with an odbc connection and then writes them to a SQL db table...

The dataflow with the script component ran in 00:06.844 and wrote 953 rows to my recordset (just writing the rows I needed, selected via script component)

The dataflow to create a staging table ran in 01:16.313 and wrote 219,155 rows to a table in a SQL db, where I could then do a lookup to grab the records I need (953 rows)

I think for this instance, where I need so few records from such a large table, it makes sense to use the asynchronous script component rather than create a staging table.

Again, thanks to all for the help and suggestions. I really appreciate it.

|||Did you use the Fast Load option in the OLE DB Destination when using the staging table approach?|||Yes, on the Connection Manager page for the destination editor, Data access mode is set to Table or View - fast load.sql

Asynchronous events to database clients via DAL?

Greetings, I have a requirement for a SQL Server 2005 database to

notify 3rd Party applications of a table change. As is stands now, 3rd

Party applications access the database via a Data Access Layer (DAL)

dll (C#). I'd like to somehow implement an asychronous event

notification scheme via the same DAL to these clients.

Can someone offer a clever way to implement such events?

Broker Services? I am under the impression the SSBS is typically deployed when databases are to communicate with one another.

Triggers to call some CLR code?

Other?

Thanks in advance,
Loopsludge

In SQL 2005 there is a buil-in solution for 'table change' notifications, namely Query Notifications and the technologies based on it (SqlNotifications, SqlDependency, SqlCacheDependency). See this post for a discussion on them: http://blogs.msdn.com/remusrusanu/archive/2006/06/17/635608.aspx

If your goal is a generic notification platform, then some questions arrise:
What kind of events would you notify (data changes, dirty cache notifications, arbitrary events)?
Where are the subscribers located (same appdomain, same machine, different machines)?
What scale do you need (tens, hundreds or millions of subscribers)?
What kind of reliability is desired for the notifications (what is the cost of loosing one notification, what happens if the subscriber or publisher are recycled)?
Are the subscriptions and/or notifications persistent or transient?

Service Broker offers persisted, reliable communication channel between services hosted on SQL 2005 databases.

HTH,
~ Remus

|||Greetings Remus,

Sorry it has taken me so long to respond. I have been exploring Query Notifications. I'm still not certain this is the correct way to go.

In answer to your questions:

What kind of events would you notify (data changes, dirty cache notifications, arbitrary events)?
I am interested in notifying clients of data changes, specifically new records.

Where are the subscribers located (same appdomain, same machine, different machines)?
The DAL will be consumed by 3rd Party applications located on a different machine.

What scale do you need (tens, hundreds or millions of subscribers)?
The number of subscribers will be very small. In most cases only 1 but potentially there could be more but not many, five max.

What

kind of reliability is desired for the notifications (what is the cost

of loosing one notification, what happens if the subscriber or

publisher are recycled)?
Now this is a very interesting question. I was reading up on the SqlDependency and noticed that one has to re-submit for each notification from the database. What happens if an event occurs inbetween the time the last notification is being processed and the event subscription? Is it lost? If so then that's a bummer. How might I get around this? Service Broker seems like serious overkill.

Are the subscriptions and/or notifications persistent or transient?
I'm not quite sure I understand completely. Could you please explain? (Sorry)

Best Regards,
Loopsludgesql

AsyncExecute problem

Hi there!
I've got a problem using ADO to execute a stored procedure (sp) on sql
server, which gets two dates and fills a table with some data on that
interval. Typical execution time is about a minute for a 30days
interval. Since the sp takes potentialy a long time to execute, I
executes it async., and give the user the option to cancel it. The C++
code below does not include that, but it doesn't work anyway:

mdbConnection->BeginTrans();
_CommandPtr cmd;
cmd.CreateInstance( __uuidof( Command ) );
cmd->ActiveConnection = mdbConnection;
cmd->CommandType = adCmdStoredProc;
cmd->CommandText = _bstr_t( SP_DDPOM );
cmd->Parameters->Append( cmd->CreateParameter( "@.0", adVarChar,
adParamInput, 10, _bstr_t( dateod ) ) );
cmd->Parameters->Append( cmd->CreateParameter( "@.1", adVarChar,
adParamInput, 10, _bstr_t( datedo ) ) );
cmd->Execute( &vtEmpty, &vtEmpty2, adAsyncExecute );
int state;
while((state=cmd->State) & adStateExecuting) {
TRACE("State = %ld\n", state );
// if cancel then mdb->RollbackTrans(); return;
}
mdbConnection->CommitTrans();

The thing is that the state is adStateExecuting for about 16 seconds
(30days interval), and afterwards drops to 0 (adStateClosed), while the
sp is still executing (which usually takes about a minute). The process
waits at the CommitTrans() until the sp is done. There aren't any errors
reported or exceptions thrown.

BUT: NO DATA IS FILLED IN THE TABLE.

I've tested that begin-commit works by adding a call to another sp, and
it works fine.

The weird thing is that the sp works if the execution takes less then
this 16 or so second limit. E.g. for 3days interval, it works fine.

The weirdest thing is that the whole thing worked at first, i.e. did not
produce erroneous state and filled the table, and that for 5 minutes
execution and more. Then I changed something in the sp (commented out a
single line!!), and ever since then it doesn't work anymore. I've got
the database backup from when it worked, but doesn't anymore.

The sp works fine when executed from Query Analyzer.

Has anyone got any ideas? Am I missing something?
Thanks,
Juhu

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!juhu (juhu@.bruhu.com) writes:
> The thing is that the state is adStateExecuting for about 16 seconds
> (30days interval), and afterwards drops to 0 (adStateClosed), while the
> sp is still executing (which usually takes about a minute). The process
> waits at the CommitTrans() until the sp is done. There aren't any errors
> reported or exceptions thrown.

I don't have any experience of asynchronous queries. But it could be
that the command timeout strikes. This timeout is by default 30 seconds.
Set it to 0 to get rid of it:

cmd->CommandTimeout = 0;

My first thought was that you have a pseudo-recordset in form of a
rowcount from an INSERT into a temp table. But since it works with
smaller amount of data it may not be that. Nevertheless, add a SET
NOCOUNT ON in the stored procedure if you don't have one.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

ASYNC_NETWORK_IO issue

I am running a Stored procedure which select from a table and returns approx 800000 records. When calling from any client machine it takes long time to return the result (90 sec). It waits for ASYNC_NETWORK_IO which is pushing the result to client. If select statement is used with TOP operator to return less number of records it executes faster. When calling from the server the stored proc returns data in 13 sec with all records. In another machine of identical HW and configuration this problem is not there. Can anyone help how to improve ASYNC_NETWORK_IO issue?

SQL-2005 SP1 64 bit Standard on Active/Passive cluster
Windows -2003 Ent.


Thanks
-Ashis

Hi, Ashis,

Did you encounter this problem during replication? If not, can you please post your question to "SQL Server Database Engine " or "Transact SQL" alias? That way, you have a better chance of getting an answer.

Thanks,

Zhiqiang Feng

Tuesday, March 20, 2012

Assumptions on Indexing

I create the following:
CREATE TABLE t1(c1 INT, c2 INT, c3 INT)
CREATE CLUSTERED INDEX icl ON t1(c3)
CREATE NONCLUSTERED INDEX incl ON t1(C1, c2)
When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
the following columns:
c1, c2, c3
ASSUMPTION #1: I assume the reason for this is that clustered index columns
(in this case, c3) are appended to the end of nonclustered index columns as
a
row locator.
Why then, if the above assumption [ASSUMPTION #1] is true, when I perfor
m the
following:
CREATE NONCLUSTERED INDEX incl ON t1(C1, c3, c2) --Take note of the non-
sequential column order
WITH DROP_EXISTING
When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
the following columns:
c1, c3, c2
ASSUMPTION #2: Based upon Assumption #1, I would expect to see the clustered
index column of c3 appended to the end of the nonclustered index column,
meaning I expected to see c1, c3, c2, c3. Since that was not the case, I
assume the reason the clustered index was not appended was due to its [t
he
clustered index] being included in the nonclustered index, and appending
column c3 [the clustered index column] to the nonclustered index in
DBCC_SHOW_STATISTICS would be redundant.
Are the above assumptions true?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200604/1> Are the above assumptions true?
Yes and yes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message news:5e86871f6f516@.uwe...[vbcol
=seagreen]
>I create the following:
> CREATE TABLE t1(c1 INT, c2 INT, c3 INT)
> CREATE CLUSTERED INDEX icl ON t1(c3)
> CREATE NONCLUSTERED INDEX incl ON t1(C1, c2)
> When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
> the following columns:
> c1, c2, c3
> ASSUMPTION #1: I assume the reason for this is that clustered index column
s
> (in this case, c3) are appended to the end of nonclustered index columns a
s a
> row locator.
> Why then, if the above assumption [ASSUMPTION #1] is true, when I perf
orm the
> following:
> CREATE NONCLUSTERED INDEX incl ON t1(C1, c3, c2) --Take note of the non-
> sequential column order
> WITH DROP_EXISTING
> When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
> the following columns:
> c1, c3, c2
> ASSUMPTION #2: Based upon Assumption #1, I would expect to see the cluster
ed
> index column of c3 appended to the end of the nonclustered index column,
> meaning I expected to see c1, c3, c2, c3. Since that was not the case, I
> assume the reason the clustered index was not appended was due to its [
;the
> clustered index] being included in the nonclustered index, and appending
> column c3 [the clustered index column] to the nonclustered index in
> DBCC_SHOW_STATISTICS would be redundant.
> Are the above assumptions true?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200604/1[/vbcol]|||Yep. The clustered key(s) is always included in the nonclustered, but it
doesn't always have to be the right-most column.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:5e86871f6f516@.uwe...
>I create the following:
> CREATE TABLE t1(c1 INT, c2 INT, c3 INT)
> CREATE CLUSTERED INDEX icl ON t1(c3)
> CREATE NONCLUSTERED INDEX incl ON t1(C1, c2)
> When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
> the following columns:
> c1, c2, c3
> ASSUMPTION #1: I assume the reason for this is that clustered index
> columns
> (in this case, c3) are appended to the end of nonclustered index columns
> as a
> row locator.
> Why then, if the above assumption [ASSUMPTION #1] is true, when I perf
orm
> the
> following:
> CREATE NONCLUSTERED INDEX incl ON t1(C1, c3, c2) --Take note of the non-
> sequential column order
> WITH DROP_EXISTING
> When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
> the following columns:
> c1, c3, c2
> ASSUMPTION #2: Based upon Assumption #1, I would expect to see the
> clustered
> index column of c3 appended to the end of the nonclustered index column,
> meaning I expected to see c1, c3, c2, c3. Since that was not the case, I
> assume the reason the clustered index was not appended was due to its [
;the
> clustered index] being included in the nonclustered index, and appending
> column c3 [the clustered index column] to the nonclustered index in
> DBCC_SHOW_STATISTICS would be redundant.
> Are the above assumptions true?
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200604/1|||sort of.
clustered indexes are weird. they physically sort the data, and then
use the columns as a key to find the relevant rows. all of this is
inherently inefficient.
non clustered indexes use data positions in the table to locate rows.
well, if there is a clustered index on the table, then those data
positions are determined by the clustered column.
so, if we create a non clustered index on the table, the engine MUST
HAVE the data elements from the clustered column in order to find the
correct row.
to see how this all works, and make it real in your head, right down
some 5 rows of examples, then create how the index would really work.

Assumptions on Indexing

I create the following:
CREATE TABLE t1(c1 INT, c2 INT, c3 INT)
CREATE CLUSTERED INDEX icl ON t1(c3)
CREATE NONCLUSTERED INDEX incl ON t1(C1, c2)
When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
the following columns:
c1, c2, c3
ASSUMPTION #1: I assume the reason for this is that clustered index columns
(in this case, c3) are appended to the end of nonclustered index columns as a
row locator.
Why then, if the above assumption [ASSUMPTION #1] is true, when I perform the
following:
CREATE NONCLUSTERED INDEX incl ON t1(C1, c3, c2) --Take note of the non-
sequential column order
WITH DROP_EXISTING
When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
the following columns:
c1, c3, c2
ASSUMPTION #2: Based upon Assumption #1, I would expect to see the clustered
index column of c3 appended to the end of the nonclustered index column,
meaning I expected to see c1, c3, c2, c3. Since that was not the case, I
assume the reason the clustered index was not appended was due to its [the
clustered index] being included in the nonclustered index, and appending
column c3 [the clustered index column] to the nonclustered index in
DBCC_SHOW_STATISTICS would be redundant.
Are the above assumptions true?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1> Are the above assumptions true?
Yes and yes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:5e86871f6f516@.uwe...
>I create the following:
> CREATE TABLE t1(c1 INT, c2 INT, c3 INT)
> CREATE CLUSTERED INDEX icl ON t1(c3)
> CREATE NONCLUSTERED INDEX incl ON t1(C1, c2)
> When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
> the following columns:
> c1, c2, c3
> ASSUMPTION #1: I assume the reason for this is that clustered index columns
> (in this case, c3) are appended to the end of nonclustered index columns as a
> row locator.
> Why then, if the above assumption [ASSUMPTION #1] is true, when I perform the
> following:
> CREATE NONCLUSTERED INDEX incl ON t1(C1, c3, c2) --Take note of the non-
> sequential column order
> WITH DROP_EXISTING
> When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
> the following columns:
> c1, c3, c2
> ASSUMPTION #2: Based upon Assumption #1, I would expect to see the clustered
> index column of c3 appended to the end of the nonclustered index column,
> meaning I expected to see c1, c3, c2, c3. Since that was not the case, I
> assume the reason the clustered index was not appended was due to its [the
> clustered index] being included in the nonclustered index, and appending
> column c3 [the clustered index column] to the nonclustered index in
> DBCC_SHOW_STATISTICS would be redundant.
> Are the above assumptions true?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1|||Yep. The clustered key(s) is always included in the nonclustered, but it
doesn't always have to be the right-most column.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:5e86871f6f516@.uwe...
>I create the following:
> CREATE TABLE t1(c1 INT, c2 INT, c3 INT)
> CREATE CLUSTERED INDEX icl ON t1(c3)
> CREATE NONCLUSTERED INDEX incl ON t1(C1, c2)
> When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
> the following columns:
> c1, c2, c3
> ASSUMPTION #1: I assume the reason for this is that clustered index
> columns
> (in this case, c3) are appended to the end of nonclustered index columns
> as a
> row locator.
> Why then, if the above assumption [ASSUMPTION #1] is true, when I perform
> the
> following:
> CREATE NONCLUSTERED INDEX incl ON t1(C1, c3, c2) --Take note of the non-
> sequential column order
> WITH DROP_EXISTING
> When I perform a DBCC_SHOW_STATISTICS(t1, incl) the "Columns" column shows
> the following columns:
> c1, c3, c2
> ASSUMPTION #2: Based upon Assumption #1, I would expect to see the
> clustered
> index column of c3 appended to the end of the nonclustered index column,
> meaning I expected to see c1, c3, c2, c3. Since that was not the case, I
> assume the reason the clustered index was not appended was due to its [the
> clustered index] being included in the nonclustered index, and appending
> column c3 [the clustered index column] to the nonclustered index in
> DBCC_SHOW_STATISTICS would be redundant.
> Are the above assumptions true?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200604/1|||sort of.
clustered indexes are weird. they physically sort the data, and then
use the columns as a key to find the relevant rows. all of this is
inherently inefficient.
non clustered indexes use data positions in the table to locate rows.
well, if there is a clustered index on the table, then those data
positions are determined by the clustered column.
so, if we create a non clustered index on the table, the engine MUST
HAVE the data elements from the clustered column in order to find the
correct row.
to see how this all works, and make it real in your head, right down
some 5 rows of examples, then create how the index would really work.sql

asssigning values to multiple vars in a SP in one go (without temp table)

I have to select several field values from a table and need to assign them to different variables in my SP.

Here's what I do now:

declare

@.ReceiverEmailnvarchar(50)

SET

@.ReceiverEmail=(SELECT EmailFROM UsersWHERE UserCode=@.UserCodeOwner)

declare

@.UsernameSendernvarchar(50)

SET

@.UsernameSender=(SELECT UsernameFROM UsersWHERE UserCode=@.UserCodeOwner)
As you can see I have to search the Users table twice: once for the Email and a second time for the Username...and all that based on the SAME usercode...:S
So, is there an option where I only have to search the table once and return the Email and UserName fields and assign them to my variables (without using a temp table...)?

Peter,

i dont know off the top of my head a way to get around the 2X search without the temptable, unless you use a table variable instead which in principle is still the same thing as your temp table. if the result set of email and usernames is not that big, then the table variable may save you a bit since it is being run in memory. I know this is not the answer your probably looking for, but its all i have...good luck!

|||the reason I dont want to use a temp table is because i've read that it might cause concurrency conflicts amongst others...
Is that still true in SQL Server 2005?
Otherwise I might as well go with the temp table..|||

Hi there,

try with this code it works

DECLARE @.RECEIVEREMAILNVARCHAR(50)DECLARE @.USERNAMESENDERNVARCHAR(50)
SELECT
@.RECEIVEREMAIL = EMAIL,
@.USERNAMESENDER = USERNAME
FROM USERS
WHERE USERCODE = @.USERCODEOWNER


Regards,

Fernando

|||

It sude did!
Thanks!

Associative array

I have a variable declared as this:
type ltype_Queue is table of NUMBER index by binary_integer;
l_Queue ltype_Queue;
How can I accomplish the equivalent to this:
select *
from Items
where ID in l_Queue
Thanks,
Layneselect *
from Items
where ID in (select * from TABLE(cast(l_Queue as myTableType)));

where myTableType is a data type defined as below :
create type myTableType as table of varchar2(255)
/

Originally posted by lrobin3
I have a variable declared as this:
type ltype_Queue is table of NUMBER index by binary_integer;
l_Queue ltype_Queue;

How can I accomplish the equivalent to this:

select *
from Items
where ID in l_Queue

Thanks,
Layne

Association Rules Model for problem

What would be the right design approach for the following problem?

I have a single table called SelectionFactors, which has the following columns and sample data:

ProjectID Factor FactorValue 1000 Countries USA 1000 Countries Canada 1000 Countries France 1000 Languages English 1000 Languages French 1000 Company Type Consulting 1000 Company Type Software 2000 Countries India 2000 Countries China 2000 Countries USA 2000 Languages English 2000 Languages Chinese (Simplified) 2000 Languages Chinese (Traditional) 2000 Languages Spanish 2000 Company Type Retail 2000 Company Type Dairy Products

The problem is to allow a descriptive analysis of the data to find patterns in the users selections. For instance,

if Languages->English is selected, what are the counts of projects for other Factor->Factor Value combinations?

Countries->USA = 2, Countries->Canada=1, Company Type->Consulting=1 and so on.

Since all the data is in this single table, are both the case and nested tables the same? What are the keys and inputs? I only need a descriptive analysis (no prediction) and ALL possible combinations MUST be part of the results; how should the model be designed?

Thank you,

Anna.

This is a good problem. What you want to do is to create a model with three nested tables all marked predict and input. Event though you want descriptive analysis, you need to mark them "predict" such that rules will be formed (only items marked predict will show on the right hand side of rules).

The way you will create such a model is to use the "Named Query" feature of the data source view(DSV). You can right-click on the DSV and select "New Named Query" to create these.

The named queries you will want to create are these:

Projects: SELECT DISTINCT ProjectID FROM SelectionFactors

Companies: SELECT ProjectID, FactorValue FROM SelectionFactors WHERE Factor='Companies'

Languages: SELECT ProjectID, FactorValue FROM SelectionFactors WHERE Factor='Languages'

Company Type: SELECT ProjectID, FactorValue FROM SelectionFactors WHERE Factor='Company Type'

You will then need to relate the ProjectID in each of the transaction named queries (Companies, etc) to the ProjectID in the Projects named query. The system will prompt you to set ProjectID as the key of the Projects named query . At this point, the named queries are equivalent to tables as far as Analysis Services is concerned.

When you create the model, you will make the Projects table the case table and the other tables nested tables. ProjectID will be the key of the Projects table and FactorValue will be the key of the other tables. Make sure to mark FactorValue as Key, Input, and Output.

When you process your model you will see rules relating projects, companies, and company types (both between factos and in the same factor).

Note that you may need to adjust the MINIMUM_SUPPORT and MINIMUM_PROBABILITY parameters to get the results you need (in fact, it's recommended)

HTH

-Jamie

|||

Thanks for your response.

However, I should have mentioned that there is an open-ended number of "Factors". New factors are added often and the number of factors is nearly 100.

The type of questions I am trying to answer using this model is:

"Among projects that have Languages-> English, how many (distinct projects) have Company Type->Consulting, Countries->USA" and so on.

Ideally, I would like to provide this analysis in a free, OLAP environment where the user can select one factor-> factor value combination and then see all the other related combinations.

I currently have a Cube with SelectionFactors serving as both the Fact and the dimension table with a single measure - DISTINCT COUNT of projectIDs.

Thanks once again.

|||

Given the information in my previous post, what are my design options?

Thanks.

|||

My question for you, then, is if you have a cube for this, what are you not getting that you need?

If the factor/factorvalue can be viewed as a complete unit, and you don't need to provide analysis between factors - e.g. given Language X, which Countries and Company Types are prevalent, you could create a calculated column in the DSV that combines the factor and factor value.

|||

Perhaps you can help me understand why I am unable to get the expected results. I am fairly new to OLAP and Data Mining and would appreciate any pointers and guidance.

The full picture of my problem is:

Dimension Project Profile - Project ID, (Other Project related attributes)

Dimension Selection Factors - Factor, Factor Value

Fact Selection Factors - Project ID, Factor, Factor Value

Project Cube Measure - DISTINCT COUNT of Project IDs

My understanding is that this is a problem of intersection of factors, which I cannot achieve with a single dimension. In fact there is this other thread http://forums.microsoft.com/msdn/showpost.aspx?postid=856304&siteid=1 that seems to come very close to my problem.

In my case, the following MDX returns the projects that have the intersection of specific factor values.

SELECT {[Measures].[Project Count]} ON COLUMNS,

INTERSECT

(

NONEMPTY

(

[Project Profile].[Project ID].Children,

(

[Measures].[Project Count],

[Selection Factor].[Selection Factors].[Factor].&[Annual Revenue].&[$500 million - $3 billion in revenues]

)

),

NONEMPTY

(

[Project Profile].[Project ID].Children,

(

[Measures].[Selection Project Count],

[Selection Factor].[Selection Factors].[Factor].&[Concurrent Users].&[1,000–10,000 users]

)

)

)

ON ROWS

FROM PROJECTCUBE

The questions are:

1. How do I get all the other Factor->Factor Values that are part of the selection factors for the projects returned by this MDX. This, if I understand correctly, is Basket analysis.

and

2. Is there a generic way to perform this analysis through cube design rather than writing MDX for each case?

Thanks once more.|||

The following MDX gives me the contents of the "baskets" of projects that have the selected items. However, the counts of the projects is not restricted to the subset retrieved through the Intersect. Is there a way to get the counts to only be within the subset returned by the Intersect?

With this MDX, I can get very close to what I need. The only thing I am missing is to get the counts (perhaps through a calculated measure). Help on this will be much appreciated!

__

SELECT [Measures].[Project Count] ON COLUMNS,

NONEMPTY

(

([Selection Factor].[Factor].Children, [Selection Factor].[Factor Value].Children),

INTERSECT

(

NONEMPTY

(

[Project Profile].[Project ID].Children,

(

[Measures].[Selection Project Count],

[Selection Factor].[Selection Factors].[Factor].&[Annual Revenue].&[$500 million - $3 billion in revenues]

)

),

NONEMPTY

(

[Project Profile].[Project ID].Children,

(

[Measures].[Selection Project Count],

[Selection Factor].[Selection Factors].[Factor].&[Concurrent Users].&[1,000–10,000 users]

)

)

)

)

ON ROWS

FROM PROJECTCUBE

|||

In order to obtain counts restricted to the subset returned by the Intersect, this MDX takes the constraint out of the ROWS into the WHERE clause.

I would still like to know how to design an Association rules model that can "automate" the generation of results by case. Hopefully, the MDX can point directly to the expected results.

SELECT [Measures].[Project Count] ON COLUMNS,

NONEMPTYCROSSJOIN([Selection Factor].[Factor].Children, [Selection Factor].[Factor Value].Children)

ON ROWS

FROM PROJECTCUBE

WHERE

NONEMPTY(

INTERSECT

(

NONEMPTY

(

[Project Profile].[Project ID].Children,

(

[Measures].[Project Count],

[Selection Factor].[Selection Factors].[Factor].&[Annual Revenue].&[$500 million - $3 billion in revenues]

)

),

NONEMPTY

(

[Project Profile].[Project ID].Children,

(

[Measures].[Project Count],

[Selection Factor].[Selection Factors].[Factor].&[Concurrent Users].&[1,000–10,000 users]

)

)

)

)

sql

Association Rules

1) I use the identifier of transaction and attribute in one table.

Do I can to build a association rules structure without the use of the nested tables?

I tried - did not turn out...

2) As it is necessary to use a main and child table, can not build a prediction query.

When I try to add the predict column in a criteria/argument (Field=PredictSupport), i'll given message:

"Nested table column cannot be used as an argument in a data mining function."

I can not use other columns, because they are not predicable.

You have to have a nested table, but you can use the same table as the case and nested tables. Just mark the transaction key as the key for the "case" table, and the transaction item as the key for the "nested" table.

In your prediction query you would predict the nested table ( you are essentially predicting which rows will be there). For example, if your model was like this:

TransID LONG KEY,
Products TABLE PREDICT
(
ProductName TEXT KEY
)

You would issue a statement like

SELECT Predict(Products, 5) FROM MyModel PREDICTION JOIN ....

|||

Yes, turned out to build a model! Thanks!
But at an attempt to take advantage of builder of queries, there is an error:

Unable to retrieve the list of distinct states for mining model column Utp Doc Id.

ADDITIONAL INFORMATION:

Error (Data mining): The specified DMX column was not found in the context at line 1, column 17. (Microsoft SQL Server 2005 Analysis Services)


Without a builder - a query works :)
And yet question - as possible to apply a function, as though, PredictSupport? She needs a column, and for me does not turn out to use her from a table...

P.S.: I am sorry for my English =)

Monday, March 19, 2012

Assistance please using txt

I have received a table of data that has a field containing date
information. Unfortunately it was derived from a MainFrame dump and
originated as a txt file and was then ported into an Access MDB file
before it became an SQL table. The date format is vchar(50) and
actually is comprised of 6 charecters ie: 010104 for Jan 1 2004. I
need to run a select statement for a range of dates such as 010104
thru 030104. Unfortunately being a charecter field this returns
incorrect results under a majority of cases. Back in my dBase days
there was a VAL() that could be used in this case but I have been
unable to find anything comperable in SQL. Can anyone help me please?

Thanks in advance

Steve"Steve" wrote:
> I have received a table of data that has a field containing date
> information. Unfortunately it was derived from a MainFrame dump and
> originated as a txt file and was then ported into an Access MDB file
> before it became an SQL table. The date format is vchar(50) and
> actually is comprised of 6 charecters ie: 010104 for Jan 1 2004. I
> need to run a select statement for a range of dates such as 010104
> thru 030104. Unfortunately being a charecter field this returns
> incorrect results under a majority of cases. Back in my dBase days
> there was a VAL() that could be used in this case but I have been
> unable to find anything comperable in SQL. Can anyone help me please?
> Thanks in advance
> Steve

Steve,

I've never worked with dBase, but I assume that VAL() would convert to an
integral data type, so that wouldn't work either: 123103 would be greater
than 010104. Also, if you have any dates before Y2K, you'll have issues
there.

One more issue: you mentioned big iron... if the date field in the mainframe
file was PIC 9(6), you might want to verify that somewhere between the file,
Access, and SQL Server, you still have all leading and trailing zeroes...

select min(len(somefld)), max(len(somefld))
from sometable

...Anyhoo, I would recommend using string functions (like LEFT, RIGHT, and
SUBSTRING) and the CONVERT function to create a real datetime column.
Barring that, you could convert the data to a datetime for the query.

Assuming that every field is 6 characters in the format mmddyy, here's a
little test I cobbled together. Note that I'm assuming you're using SQL
Server 2K: otherwise the table variable won't work. Also, I chose what SQL
Server BOL calls the ANSI date format, but YMMV depending on your regional
date/time settings...

set nocount on

declare @.test table (
src varchar(50),
dest datetime
)

insert @.test values ('123198', null)
insert @.test values ('010199', null)
insert @.test values ('123103', null)
insert @.test values ('010104', null)

--Use 2: the ANSI (yy.mm.dd) style for conversion (the
--final parameter in the convert call)
update @.test
set dest = convert(
datetime,
right(src, 2) + '.' +
left(src, 2) + '.' +
substring(src, 3, 2),
2
)

--Out of whack
select * from @.test order by src

--In whack
select * from @.test order by dest

Craig|||Steve,
Datetime fields (there are no Date fields) in SQL Server need to be in
the format yyyymmdd, so you need to format the field first before inserting
it into your database. Do that like this:

declare @.myDate as varchar(50)

set @.myDate = '010104'

select cast(
'20' +
right(@.myDate,2) +
substring(@.myDate,3,2) +
left(@.myDate,2) as datetime)

I hard-coded '20' as the century.
Also, the way to import the file would probably to create a DTS package and
load the data into a temp table, then create a procedure using the above
statement as part of the stored procedure.
OK, now for the pitch. If you are new to SQL Server 2000, a great way to
get up to speed in just a few hours is with our video series on SQL Server
2000 at www.TechnicalVideos.net. Our videos give tips and tricks from
experts in the field, while they show you on the screen just how to do them.

Best regards,
Chuck Conover
www.TechnicalVideos.net

"Steve" <shull@.dpd.dallascityhall.com> wrote in message
news:6318603c.0403041803.7a69d4f4@.posting.google.c om...
> I have received a table of data that has a field containing date
> information. Unfortunately it was derived from a MainFrame dump and
> originated as a txt file and was then ported into an Access MDB file
> before it became an SQL table. The date format is vchar(50) and
> actually is comprised of 6 charecters ie: 010104 for Jan 1 2004. I
> need to run a select statement for a range of dates such as 010104
> thru 030104. Unfortunately being a charecter field this returns
> incorrect results under a majority of cases. Back in my dBase days
> there was a VAL() that could be used in this case but I have been
> unable to find anything comperable in SQL. Can anyone help me please?
> Thanks in advance
> Steve

Assistance on SUM() statement

I have a table #changes with two columns: familyID, Versiontime

For each record in the #changes table I need to compute the income from another table FamilyIncome with colunms: familyID, IncomeType, EffectiveDate, Amt. Example Below:

FamID, IncType, EffDate, Amt
100, 10, 01/01/2003, $50.00
100, 20, 01/01/2003, $50.00
100, 30, 01/01/2003, $50.00
100, 10, 02/02/3003, $100.00
100, 20, 02/02/3003, $100.00
100, 30, 02/02/3003, $100.00
100, 40, 02/02/3003, $100.00
100, 20, 03/03/3003, $75.00
100, 30, 03/03/3003, $75.00
100, 40, 03/03/3003, $75.00

So if I'm looking for the Incomes on the following dates (which are in the #changes table) it should be:

01/02/2003 - $150.00 (The three records effective on 01/01/2003)
02/10/2003 - $400.00 (The four records effective on 02/02/2003)
04/01/2003 - $325.00 (One record from 02/02/2003 is still effective (Type 10) plus the three records effective on 03/03/2003)

Any help is greatly appreciated,

BrentCan you post the DDL?

But it seems like a join between the two and a GROUP by with a SUM

something like

SELECT EFF_DATE, SUM(AMT)
FROM myTable1 a myTable2 b
ON a.key = b.key
GROUP BY EFF_DATE|||Brett,
I have tried a couple variations on this theme and so far they do not deliver the desired results:

1. Select familyID, VersionTime, (Select Sum(Amt) from FamilyIncome where FamilyIncome.familyid = #changes.familyid and EFFECTIVEDATE < cast(#changes.versiontime as datetime)+1) as Income
from #changes
group by familyid, cast(versiontime as datetime),#changes.versiontime
order by familyid, versiontime desc
THIS CODE WORKS FOR THE FIRST DATE AND RETURNS THE $150.00 DESIRED, BUT ON ANY FUTURE DATES IT ADDS THE NEW INCOME AND KEEPS A RUNNING TOTAL (I.E. $550.00 INSTEAD OF $400.00)

2. Select familyID, VersionTime, (Select Sum(Amt) from FamilyIncome where FamilyIncome.familyid = #changes.familyid
HAVING EFFECTIVEDATE < cast(#changes.versiontime as datetime)+1) as Income
from #changes
group by familyid, cast(versiontime as datetime),#changes.versiontime
order by familyid, versiontime desc
THIS CODE ONLY RETURNS A RESULT SET FOR THE LAST DATE IN THE SEQUENCE AND THEN IT RETURNS $225.00 (ONLY THE RECORDS WITH A 03/03/2003) DATE)
At this time I'm just trying to isolate the effdate calculations. Also whoever reads this all the dates should be 2003 the 3003 for the year is a typo.

Thanks,

Brent
Originally posted by Brett Kaiser
Can you post the DDL?

But it seems like a join between the two and a GROUP by with a SUM

something like

SELECT EFF_DATE, SUM(AMT)
FROM myTable1 a myTable2 b
ON a.key = b.key
GROUP BY EFF_DATE|||I don't understand your results...where do these dates come from:

01/02/2003 - $150.00 (The three records effective on 01/01/2003)
02/10/2003 - $400.00 (The four records effective on 02/02/2003)
04/01/2003 - $325.00 (One record from 02/02/2003

They don't exists in your data...|||Originally posted by Brett Kaiser
I don't understand your results...where do these dates come from:

They don't exists in your data...

Those dates come out of the #changes table which I only included the fields not an example. What I have is 43,000 records in the #changes table that are familyID's and Dates on which I need to perform several calculations (size of the family, income, fee schedule, etc) right now I'm hung up on getting the income which I need in order to determine the fee (fee is based on family size and income)

Brent

Assistance developing Query

Good Day;

I would appreciate assistance developing a query that I haven't been
able to develop without using a second table. I wish to count the
number of records that are still open on the first of each month.
Each record has an open date and a close date or the close date is
null i.e., the record is not yet closed. I've previously beaten this
by building a table, simply a list of the dates for the first of each
month for the next ten years or so, and then selecting values based
upon a date selected from that table. However I'd be happier if I
could do it without the second table. I'd be prepared to accept the
Min(Date) for each month as being the first of the month.

I've included some DDL statements to build and populate the table if
that helps. Since the selection is rather small and all the open
dates are very close together I think the result will be simply a
decreasing count from the month the first record is opened till today.

A pseudo code select statement might look like

Select Min(DateOpened) As DateOfInterest, Count(*) as [Qty Still Open]
FROM DetailT
Where DateReceived > DateOfInterest or DateReceived is Null and
DateOpened < DateOfInterest
Group by Min(DateOpened)
Order by Min(DateOpened)

I hope I've explained it sufficiently well.

CREATE TABLE [dbo].[DetailT] (
[Autonum] [int] IDENTITY (1, 1) NOT NULL ,
[QDNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateOpened] [smalldatetime] NOT NULL ,
[DateReceived] [smalldatetime] NULL ,

)
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('C15788', '06/04/2005 9:35', 07/04/2005)
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('B16091', '06/04/2005 9:36', '07/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('B15001', '06/04/2005 9:51', '08/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('M18696', '06/04/2005 9:56', '06/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('C14969', '06/04/2005 10:05', '10/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('O10091', '06/04/2005 10:08', '12/04/2005')
Insert into DetailT (QDNumber, DateOpened)
VALUES('D01197', '06/04/2005 10:13')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('H15001', '06/04/2005 10:15', '08/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('J15090', '06/04/2005 10:24', '08/04/2005')
Insert into DetailT (QDNumber, DateOpened)
VALUES('J01202', '06/04/2005 10:31')
Insert into DetailT (QDNumber, DateOpened)
VALUES('G01193', '06/04/2005 10:32')
Insert into DetailT (QDNumber, DateOpened)
VALUES('K01164', '06/04/2005 10:35')
Insert into DetailT (QDNumber, DateOpened)
VALUES('K01162', '06/04/2005 10:48')
Insert into DetailT (QDNumber, DateOpened)
VALUES('F01124', '06/04/2005 10:59')
Insert into DetailT (QDNumber, DateOpened)
VALUES('H01147', '06/04/2005 11:01')
Insert into DetailT (QDNumber, DateOpened)
VALUES('S15068', '06/04/2005 11:10')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('E12322', '06/04/2005 11:32', '07/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('A12205', '06/04/2005 11:37', '06/04/2005')
Insert into DetailT (QDNumber, DateOpened, DateReceived)
VALUES('D12259', '06/04/2005 11:40', '07/04/2005')
Insert into DetailT (QDNumber, DateOpened)
VALUES('C03394', '06/04/2005 11:51')

If you made it this far thank you for your patience. Any help would be
appreciated.

Thank you.

BillBill wrote:

> Good Day;
> I would appreciate assistance developing a query that I haven't been
> able to develop without using a second table. I wish to count the
> number of records that are still open on the first of each month.
> Each record has an open date and a close date or the close date is
> null i.e., the record is not yet closed. I've previously beaten this
> by building a table, simply a list of the dates for the first of each
> month for the next ten years or so, and then selecting values based
> upon a date selected from that table. However I'd be happier if I
> could do it without the second table. I'd be prepared to accept the
> Min(Date) for each month as being the first of the month.
> I've included some DDL statements to build and populate the table if
> that helps. Since the selection is rather small and all the open
> dates are very close together I think the result will be simply a
> decreasing count from the month the first record is opened till today.
> A pseudo code select statement might look like
> Select Min(DateOpened) As DateOfInterest, Count(*) as [Qty Still Open]
> FROM DetailT
> Where DateReceived > DateOfInterest or DateReceived is Null and
> DateOpened < DateOfInterest
> Group by Min(DateOpened)
> Order by Min(DateOpened)
> I hope I've explained it sufficiently well.
> CREATE TABLE [dbo].[DetailT] (
> [Autonum] [int] IDENTITY (1, 1) NOT NULL ,
> [QDNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DateOpened] [smalldatetime] NOT NULL ,
> [DateReceived] [smalldatetime] NULL ,
> )
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('C15788', '06/04/2005 9:35', 07/04/2005)
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('B16091', '06/04/2005 9:36', '07/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('B15001', '06/04/2005 9:51', '08/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('M18696', '06/04/2005 9:56', '06/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('C14969', '06/04/2005 10:05', '10/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('O10091', '06/04/2005 10:08', '12/04/2005')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('D01197', '06/04/2005 10:13')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('H15001', '06/04/2005 10:15', '08/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('J15090', '06/04/2005 10:24', '08/04/2005')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('J01202', '06/04/2005 10:31')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('G01193', '06/04/2005 10:32')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('K01164', '06/04/2005 10:35')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('K01162', '06/04/2005 10:48')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('F01124', '06/04/2005 10:59')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('H01147', '06/04/2005 11:01')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('S15068', '06/04/2005 11:10')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('E12322', '06/04/2005 11:32', '07/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('A12205', '06/04/2005 11:37', '06/04/2005')
> Insert into DetailT (QDNumber, DateOpened, DateReceived)
> VALUES('D12259', '06/04/2005 11:40', '07/04/2005')
> Insert into DetailT (QDNumber, DateOpened)
> VALUES('C03394', '06/04/2005 11:51')
> If you made it this far thank you for your patience. Any help would be
> appreciated.
> Thank you.
> Bill

Where the SQL statement you would use to load the table?
Put parentheses around it.
Go from there.
There may be far more elegant solutions but your the one getting
the paycheck. ;-)
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace 'x' with 'u' to respond)|||On 18 Apr 2005 16:36:38 -0700, Bill wrote:

>I would appreciate assistance developing a query that I haven't been
>able to develop without using a second table.
(snip)
>I've previously beaten this
>by building a table, simply a list of the dates for the first of each
>month for the next ten years or so, and then selecting values based
>upon a date selected from that table. However I'd be happier if I
>could do it without the second table.

Hi Bill,

Why do you want to do it wothout a second table? Having a permanent
auxiliary calendar table in your database is actually quite useful and I
think that no database should ever be without one.
Here's a link to an article that shows how to create a general
all-purpose calendar table, how to fill it with data and several
examples of how to use it: http://www.aspfaq.com/show.asp?id=2519.

And here's how I'd write your query, using the table described above:

SELECT c.dt,
COUNT(*) as "Qty Still Open"
FROM Calendar AS c
INNER JOIN DetailT
ON ( DateReceived > c.dt OR DateReceived IS NULL )
AND DateOpened < c.dt
WHERE c.D = 1
AND c.dt BETWEEN (SELECT MIN(DateOpened)
FROM DetailT)
AND DATEADD(month, 1, (SELECT MAX(DateReceived)
FROM DetailT))
GROUP BY c.dt
ORDER BY c.dt

This one cuts off the listing at the lowest number. If you increase the
number (1) in the DATEADD expression, you can see that the number of
open cases remains constant after the last month listed.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||DA Morgan <damorgan@.x.washington.edu> wrote in message news:<1113891730.819997@.yasure>...
> Bill wrote:
> > Good Day;
> > I would appreciate assistance developing a query that I haven't been
> > able to develop without using a second table.

> > Thank you.
> > Bill
> Where the SQL statement you would use to load the table?
> Put parentheses around it.
> Go from there.
> There may be far more elegant solutions but your the one getting
> the paycheck. ;-)

Dan;

I'm sorry but I don't understand what you're trying to tell me. If
your first question is "Where is the SQL statement you would use to
load the table?" I don't have one, the data base is interactively
updated through an ASP based HTML form over the web. I did go to the
work of providing insert statements to assist anyone who might try to
help.

I don't understand what putting parentheses around it would do?

I hope there is a more elegant solution but unfortuantely I haven't
figured it out and was simply asking for assistance. If you don't
wish to help thats ok with me. Yes, I'm getting paid for the work I
do, but developing this solution is not what I was trained or educated
to do an so I'm learning as I go. I appologize if I've bothered you
by asking for help.

I will continue to explore the solution.

Cheers;

Bill|||Hugo Kornelis <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message news:<8oqa61p4rgmhtcfh7rgsc8dup9pj1a9d1t@.4ax.com>...
> On 18 Apr 2005 16:36:38 -0700, Bill wrote:
> >I would appreciate assistance developing a query that I haven't been
> >able to develop without using a second table.
> (snip)
> >I've previously beaten this
> >by building a table, simply a list of the dates for the first of each
> >month for the next ten years or so, and then selecting values based
> >upon a date selected from that table. However I'd be happier if I
> >could do it without the second table.
> Hi Bill,
> Why do you want to do it wothout a second table? Having a permanent
> auxiliary calendar table in your database is actually quite useful and I
> think that no database should ever be without one.
...
> This one cuts off the listing at the lowest number. If you increase the
> number (1) in the DATEADD expression, you can see that the number of
> open cases remains constant after the last month listed.
> Best, Hugo

Hugo;

Thank you for your assistance. I've reviewed the article you pointed
me to and have spent my spare time today building a number table and a
date table. I was reluctant to duplicate my earlier date table, which
only contained the dates for the first of the month into the SQL
Server environment since I felt that it was both cheating and
confusing. However the article showed that this can be a very useful
table and since it's been published I don't feel too bad about
emulating someone's work who know more about application development
than I do.

I do have one question, based mainly on my lack of formal training in
SQL Server and my experience this morning building the calendar table.
I was copying the code from the article and pasting it into SQL Query
Analyzer and running it, as I'm not certain where or how this code
should be executed. Most things ran very quickly after I modified
them properly to meet my environment. However the adding of row's to
the Calendar table (4,096) took several hours (2-3) I was really
surprised by this and wondered if I was doing something wrong but
since it finished successfully and subsequent code samples executed
quickly I moved on. My question is am I using the right part of the
SQL Server environment for this sort of work?

Thank you once again for your time and your assistance. Now that I've
overcome my reluctance to using the calendar table I'm comfortable
enough to go on and using you sample query to get what I was looking
for.

Thank you.

Cheers;

Bill|||On 20 Apr 2005 16:06:36 -0700, Bill wrote:

(snip)
>the article showed that this can be a very useful
>table and since it's been published I don't feel too bad about
>emulating someone's work who know more about application development
>than I do.

Hi Bill,

You certainly should not feel bad about it - sharing the code is exactly
the reason why Aaron has published it on his site.

Copying work from others may be a sin in artistic creative work, but in
software development, it's a sin NOT to copy and adapt proven solutions.

(snip)
>Most things ran very quickly after I modified
>them properly to meet my environment. However the adding of row's to
>the Calendar table (4,096) took several hours (2-3) I was really
>surprised by this

And so am I. Okay, the server does have a bit of work to do when
populating the table, but under normal circumstances, I would not expect
it to run for so long! A couple of minutes, maybe. Not hours.

I'm not sure if you still care to investigate this (since you now have
the table, and it's a one-time job after all), but if you do, then could
you please post the exact code you used to create and popultae the
tables?

>My question is am I using the right part of the
>SQL Server environment for this sort of work?

Yes, Query Analyzer is exactly the tool to use for these jobs.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Assing rows to a table

I have a table where the columns are products and the
rows are customers. It stores sales info by customer and
product. I would like to add a row at the bottom af my
customer list that would be used for totaling each
column. Does anyone know how I can do this?
hi john,
if i understand you correctly you are looking for COMPUTE BY clause of
SELECT statement.
you can check the resultset of following query on northwind database.
use northwind
go
select b.customerid, a.unitprice
from [order details]a join orders b
on a.orderid =b.orderid
order by b.customerid
compute sum (a.unitprice ) by b.customerid
go
if this is not what you want pls post relevent table structure, sample
records, and expected result set out of it.Also specify whether you can
looking for in INSERT /UPDATE/SELECT statment.
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

assigning weight to columns with fulltext (is it possible?)

Hello all,
I have a table which has several fulltext enabled columns.
Title, Summary, Description
I was wondering whether it is possible to assign some kind of weight
to the columns? For example, I find it more relevant if the keywords
are in the title than in the description. Therefore I would like
records that have the matching keywords in the title to come up first.
I'm thinking the way to achieve this is by assigning weight to the
columns, but am not sure how to go about doing this.
Any advice is much appreciated.
PS. I did have something going a while ago where I used CONTAINSTABLE
and severals join plus some variables which contained the weight and
used that on the RANK value. Thing was, it was REALLY really slow on
10,000+ records.
On Dec 13, 7:09 pm, Pacific Fox - Web Design Brisbane
<tacofl...@.gmail.com> wrote:
> Hello all,
> I have a table which has several fulltext enabled columns.
> Title, Summary, Description
> I was wondering whether it is possible to assign some kind of weight
> to the columns? For example, I find it more relevant if the keywords
> are in the title than in the description. Therefore I would like
> records that have the matching keywords in the title to come up first.
> I'm thinking the way to achieve this is by assigning weight to the
> columns, but am not sure how to go about doing this.
> Any advice is much appreciated.
|||Unfortunately the join approach is the only solution to something like this.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Pacific Fox - Web Design Brisbane" <tacofleur@.gmail.com> wrote in message
news:facf10c4-82e5-4fac-9752-3921c1504650@.d4g2000prg.googlegroups.com...
> PS. I did have something going a while ago where I used CONTAINSTABLE
> and severals join plus some variables which contained the weight and
> used that on the RANK value. Thing was, it was REALLY really slow on
> 10,000+ records.
> On Dec 13, 7:09 pm, Pacific Fox - Web Design Brisbane
> <tacofl...@.gmail.com> wrote:
>