Do I need multiple versions of Northwind?

I am learning SQL Server 2005 Express, and want to use the Northwind data base as used in many code examples. I wanted to attach the Northwind data base via SQL Management Studio, but cannot find a Northwind.mdf file.

When I do a search using 'Northwind', I see there are already a couple of different versions of Northwind already loaded. One appears to be associated with SQL Server Mobile Edition (Samples folder) and another seems to be associated with Visual Studio 8\SDK\v2.0\Quickstart.

Can I use one of these existing Northwind databases (none have .mdf extention) for SQL Server 2005 or do I need to download yet another version?

You can either download a copy of the Northwind database here:

http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&displaylang=en

It says it's for 2000, but it works fine for express. I use pubs a lot to this day, myself.

Buck Woody

|||

Here are links to the Northwind, Pubs, and AdventureWorks sample databases. It is worth having all three of them since many books, magazine articles, and web code is based upon them.

Databases -AdventureWorks
http://msdn2.microsoft.com/en-us/library/ms124659.aspx

Databases -Northwind and Pubs
http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034

Do drag and drop controls create DataSets

When I drag a GridView from the toolbox onto a Webform, and then configure its DataSource is a true DataSet created that I can access in the code behind? When my results return I want to be able to access individual rows and cells, taking their values, assiging them to variables and then using the newly equated variable to perform calculations.

Thank you,

Your GridView is bound to dataset It is simple to access values in dataset: your_DataTable.Rows[rowindex][colunmindex]

Or use GridView1.Rows[rowid].Cells[cellindex] to access the control (or findControl(id) if there are many).

DMX to read cluster characteristics

Hi,

Can you send me or point me to a DMX code to read cluster characteristics programatically ?

Thanks,

The information displayed in the Cluster Characteristics viewer is available through a stored procedure call.

CALL System.Microsoft.AnalysisServices.System.DataMining.Clustering.GetClusterCharacteristics('Table1_CL','001',0.0005)

The first parameter is the model name, the second is the NODE_UNIQUE_NAME for the cluster whose characteristics are to be rendered and the last parameter is a threshold.

You can get the list of all clusters and their associated NODE_UNIQUE_NAME with a query like:

SELECT NODE_UNIQUE_NAME, NODE_CAPTION, NODE_DESCRIPTION, NODE_TYPE FROM [Table1_CL].CONTENT WHERE NODE_TYPE=5

(NODE_TYPE=5 identifies a Cluster node)

Hope this helps

|||

Hi Bogdan,

This worked greart. Thanks.

Can you also let me know the online material or book where I can get some more information of this kind about DMX to access information from SSAS DataMining model ? We are looking forward to write more of such codes to get information from the mining model programatically.

Thanks,

Vikas

|||

There isn't a lot of documentation on the stored procedures that retrieve summarized/derived information from the algorithms. All of the information is derived from the content (i.e. SELECT * FROM <model>.CONTENT). You can use SQL Profiler to inspect the queries that are sent from the viewers to the server to access the syntax and how the sprocs are called. Also, you can look at the sample thin client viewer code that ships with the product to see the characteristic and discrimination calls against clustering and naive bayes models.

To get a better idea of how the content looks per algorithm, you can download a sample plug-in viewer we wrote from http://www.sqlserverdatamining.com/DMCommunity/Downloads/Links_LinkRedirector.aspx?id=1349. This viewer provides a nicer interface for navigating generic content, decoding node types and value types and such.

|||

Here's one way of getting the characteristics that distinguish a cluster. Feedback welcome:

http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=ClusterNaming&referringTitle=Home

DMX to read cluster characteristics

Hi,

Can you send me or point me to a DMX code to read cluster characteristics programatically ?

Thanks,

The information displayed in the Cluster Characteristics viewer is available through a stored procedure call.

CALL System.Microsoft.AnalysisServices.System.DataMining.Clustering.GetClusterCharacteristics('Table1_CL','001',0.0005)

The first parameter is the model name, the second is the NODE_UNIQUE_NAME for the cluster whose characteristics are to be rendered and the last parameter is a threshold.

You can get the list of all clusters and their associated NODE_UNIQUE_NAME with a query like:

SELECT NODE_UNIQUE_NAME, NODE_CAPTION, NODE_DESCRIPTION, NODE_TYPE FROM [Table1_CL].CONTENT WHERE NODE_TYPE=5

(NODE_TYPE=5 identifies a Cluster node)

Hope this helps

|||

Hi Bogdan,

This worked greart. Thanks.

Can you also let me know the online material or book where I can get some more information of this kind about DMX to access information from SSAS DataMining model ? We are looking forward to write more of such codes to get information from the mining model programatically.

Thanks,

Vikas

|||

There isn't a lot of documentation on the stored procedures that retrieve summarized/derived information from the algorithms. All of the information is derived from the content (i.e. SELECT * FROM <model>.CONTENT). You can use SQL Profiler to inspect the queries that are sent from the viewers to the server to access the syntax and how the sprocs are called. Also, you can look at the sample thin client viewer code that ships with the product to see the characteristic and discrimination calls against clustering and naive bayes models.

To get a better idea of how the content looks per algorithm, you can download a sample plug-in viewer we wrote from http://www.sqlserverdatamining.com/DMCommunity/Downloads/Links_LinkRedirector.aspx?id=1349. This viewer provides a nicer interface for navigating generic content, decoding node types and value types and such.

|||

Here's one way of getting the characteristics that distinguish a cluster. Feedback welcome:

http://www.codeplex.com/ASStoredProcedures/Wiki/View.aspx?title=ClusterNaming&referringTitle=Home

DMO Anonymous Merge Pull

I adapted Hilary's DMO Example, and have tried ReplCtrl\VB\ReplSamp.vbp ...
WHAT CODE DO I NEED to .Initialize .Run .Terminate the SQLMerge?
Subscription was created using Windows Synchronization Manager,
Administrator Account on WinXPPro Laptop. VB is running in MS Access XP
Runtime .ade
CODE looks good, but is still missing 'something'
DMO Example -- last line is
objMergePullSubscriptions.Add objMergPullSubscription
Obviously FAILS because 'The subscription already exists.'
AIRCODE; Run via VPN to namedserver
Option Compare Database
Option Explicit
Private Sub cmdMergePublication_Click()
'On Error GoTo Err_cmdMergePublication_Click
Const SQLDMOSubscription_Anonymous = 2
Const SQLDMOReplSecurity_Normal = 0
Const SQLDMOReplSecurity_Integrated = 1
Const SQLDMOSubscription_All = 3
Const SQLDMOMergeSubscriber_Default = 2
Dim objServer, objReplication, objReplicationDatabases, objReplicationDatabase
Dim objMergePullSubscription, objMergePullSubscriptions,
objReplicationSecurity
Set objServer = CreateObject("SQLDMO.SQLServer")
objServer.Connect ".", "sa", "nyrv%fa"
Set objReplication = objServer.Replication
Set objReplicationDatabases = objReplication.ReplicationDatabases
Set objReplicationDatabase = objReplicationDatabases("CareSQL50207")
Set objMergePullSubscription = CreateObject("SQLDMO.MergePullSubscription2")
Set objReplicationSecurity = objMergePullSubscription.DistributorSecurity
objReplicationSecurity.SecurityMode = SQLDMOReplSecurity_Normal
objReplicationSecurity.StandardLogin = "sa"
objReplicationSecurity.StandardPassword = "d23&tmv"
With objMergePullSubscription
.AltSnapshotFolder = "C:\temp\"
.Distributor = "GSHSBS2000"
.DistributorSecurity.SecurityMode = SQLDMOReplSecurity_Normal
.DistributorSecurity.StandardLogin = "sa"
.DistributorSecurity.StandardPassword = "nyrv%fa"
.Publisher = "GSHSBS2000"
.PublicationDB = "CareSQL50207"
.Publication = "CareSQL50207"
.PublisherSecurity.SecurityMode = SQLDMOReplSecurity_Normal
.PublisherSecurity.StandardLogin = "sa"
.PublisherSecurity.StandardPassword = "nyrv%fa"
.SubscriberType = SQLDMOMergeSubscriber_Default
.SubscriptionType = SQLDMOSubscription_Anonymous
.SubscriberSecurityMode = SQLDMOReplSecurity_Normal
.SubscriberLogin = "sa"
.SubscriberPassword = "d23&tmv"
.UseFTP = False
End With
Set objMergePullSubscriptions = objReplicationDatabase.MergePullSubscriptions
'$$$$$ STOPS here: [MS][ODBC][SQLServer] The subscription already exists.
objMergePullSubscriptions.Add objMergePullSubscription
Set objMergePullSubscription = Nothing
Set objMergePullSubscriptions = Nothing
Set objReplicationDatabases = Nothing
Set objReplication = Nothing
Set objServer = Nothing
Exit_cmdMergePublication_Click:
Exit Sub
Err_cmdMergePublication_Click:
MsgBox Err.Description
Resume Exit_cmdMergePublication_Click
End Sub
TIA
Aubrey Kelley
this is to create the job, not to start it. I've been puzzling for a good
long while on trying to figure out how to start it.
Hilary Cotter
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
"Aubrey" <Aubrey@.discussions.microsoft.com> wrote in message
news:9D3A8AF9-7A89-4BCA-B3A4-0D564D00C217@.microsoft.com...
> I adapted Hilary's DMO Example, and have tried ReplCtrl\VB\ReplSamp.vbp
...
> WHAT CODE DO I NEED to .Initialize .Run .Terminate the SQLMerge?
>
> Subscription was created using Windows Synchronization Manager,
> Administrator Account on WinXPPro Laptop. VB is running in MS Access XP
> Runtime .ade
> CODE looks good, but is still missing 'something'
> DMO Example -- last line is
> objMergePullSubscriptions.Add objMergPullSubscription
> Obviously FAILS because 'The subscription already exists.'
>
> AIRCODE; Run via VPN to namedserver
> Option Compare Database
> Option Explicit
> Private Sub cmdMergePublication_Click()
> 'On Error GoTo Err_cmdMergePublication_Click
> Const SQLDMOSubscription_Anonymous = 2
> Const SQLDMOReplSecurity_Normal = 0
> Const SQLDMOReplSecurity_Integrated = 1
> Const SQLDMOSubscription_All = 3
> Const SQLDMOMergeSubscriber_Default = 2
> Dim objServer, objReplication, objReplicationDatabases,
objReplicationDatabase
> Dim objMergePullSubscription, objMergePullSubscriptions,
> objReplicationSecurity
> Set objServer = CreateObject("SQLDMO.SQLServer")
> objServer.Connect ".", "sa", "nyrv%fa"
> Set objReplication = objServer.Replication
> Set objReplicationDatabases = objReplication.ReplicationDatabases
> Set objReplicationDatabase = objReplicationDatabases("CareSQL50207")
> Set objMergePullSubscription =
CreateObject("SQLDMO.MergePullSubscription2")
> Set objReplicationSecurity = objMergePullSubscription.DistributorSecurity
> objReplicationSecurity.SecurityMode = SQLDMOReplSecurity_Normal
> objReplicationSecurity.StandardLogin = "sa"
> objReplicationSecurity.StandardPassword = "d23&tmv"
> With objMergePullSubscription
> .AltSnapshotFolder = "C:\temp\"
> .Distributor = "GSHSBS2000"
> .DistributorSecurity.SecurityMode = SQLDMOReplSecurity_Normal
> .DistributorSecurity.StandardLogin = "sa"
> .DistributorSecurity.StandardPassword = "nyrv%fa"
> .Publisher = "GSHSBS2000"
> .PublicationDB = "CareSQL50207"
> .Publication = "CareSQL50207"
> .PublisherSecurity.SecurityMode = SQLDMOReplSecurity_Normal
> .PublisherSecurity.StandardLogin = "sa"
> .PublisherSecurity.StandardPassword = "nyrv%fa"
> .SubscriberType = SQLDMOMergeSubscriber_Default
> .SubscriptionType = SQLDMOSubscription_Anonymous
> .SubscriberSecurityMode = SQLDMOReplSecurity_Normal
> .SubscriberLogin = "sa"
> .SubscriberPassword = "d23&tmv"
> .UseFTP = False
> End With
> Set objMergePullSubscriptions =
objReplicationDatabase.MergePullSubscriptions
> '$$$$$ STOPS here: [MS][ODBC][SQLServer] The subscription already exists.
> objMergePullSubscriptions.Add objMergePullSubscription
> Set objMergePullSubscription = Nothing
> Set objMergePullSubscriptions = Nothing
> Set objReplicationDatabases = Nothing
> Set objReplication = Nothing
> Set objServer = Nothing
> Exit_cmdMergePublication_Click:
> Exit Sub
> Err_cmdMergePublication_Click:
> MsgBox Err.Description
> Resume Exit_cmdMergePublication_Click
> End Sub
> --
> TIA
> Aubrey Kelley
|||Ouch! At least now I do not feel 'so dumb'. Maybe PSS will be helpful now
that we are so far. Nothing but dead ends in the past ...
Aubrey
"Hilary Cotter" wrote:

> this is to create the job, not to start it. I've been puzzling for a good
> long while on trying to figure out how to start it.
> --
> Hilary Cotter
> 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
> "Aubrey" <Aubrey@.discussions.microsoft.com> wrote in message
> news:9D3A8AF9-7A89-4BCA-B3A4-0D564D00C217@.microsoft.com...
> ...
> objReplicationDatabase
> CreateObject("SQLDMO.MergePullSubscription2")
> objReplicationDatabase.MergePullSubscriptions
>
>
|||here is what I have so far - this will spill the anmes of the anonymous pull
subscriptions in the database Northwindsub.
Can't seem to get it to resolve to the job_id's for the merge pull though.
set objSQLServer=CreateObject("SQLDMO.SQLServer")
objSQLServer.loginSecure=True
objSQLServer.Connect "Publisher"
set objReplication=objSQLServer.Replication
wscript.echo objReplication.Distributor.DistributionServer
set objReplicationDatabase =
objReplication.ReplicationDatabases("Northwindsub" )
for each objMergepullSubscription in
objReplicationDatabase.MergepullSubscriptions
wscript.echo "objMergepullSubscription.Name"
wscript.echo objMergepullSubscription.Name
wscript.echo "objMergepullSubscription.MergeJObID"
wscript.echo objMergepullSubscription.MergeJObID
set QueryResults=objMergepullSubscription.EnumJobInfo
for b=1 to QueryResults.Rows
for a=1 to QueryResults.Columns
wscript.echo QueryResults.ColumnName(A)
wscript.echo QueryResults.GetColumnString(b,A)
next
next
wscript.echo objReplication.Distributor.DistributionServer
set QueryResults=objReplication.Distributor.EnumMergeA gentViews()
for b=1 to QueryResults.Rows
for a=1 to QueryResults.Columns
wscript.echo QueryResults.ColumnName(A)
wscript.echo QueryResults.GetColumnString(b,A)
next
next
set objDistributor=Nothing
next
Hilary Cotter
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
"Aubrey" <Aubrey@.discussions.microsoft.com> wrote in message
news:2F4F23EF-3F58-44CD-8620-4BBA107F649F@.microsoft.com...[vbcol=seagreen]
> Ouch! At least now I do not feel 'so dumb'. Maybe PSS will be helpful now
> that we are so far. Nothing but dead ends in the past ...
> Aubrey
> "Hilary Cotter" wrote:
good[vbcol=seagreen]
ReplCtrl\VB\ReplSamp.vbp[vbcol=seagreen]
XP[vbcol=seagreen]
objMergePullSubscription.DistributorSecurity[vbcol=seagreen]
exists.[vbcol=seagreen]

DML Statements in code vs. Stored Procedures

Hi,
We're having a big discussion with a customer about where to store the SQL and DML statements. (We're talking about SQL Server 2000)
We're convinced that having all statements in the code (data access layer) is a good manner, because all logic is in the "same place" and it's easier to debug. Also you can only have more problems in the deployment if you use the stored procedures. The customer says they want everything in seperate stored procedures because "they always did it that way".
What i mean by using seperate stored procedures is:
- Creating a stored procedure for each DML operation and for each table (Insert, update or delete)
- It should accept a parameter for each column of the table you want to manipulate (delete statement: id only)
- The body contains a DML statement that uses the parameters
- In code you use the name of the stored procedure instead of the statement, and the parameters remain... (we are using microsoft's enterprise library for data access btw)
For select statements they think our approach is best...
I know stored procedures are compiled and thus should be faster, but I guess that is not a good argument as it is a for an ASP.NET application and you would not notice any difference in terms of speed anyway. We are not anti-stored-procedures, eg for large operations on a lot of records they probably will be a lot better.
Anyone knows what other pro's are related to stored procedures? Or to our way? Please tell me what you think...
ThanksHere was the previous big discussion on stored procs vs. dynamic sql:
Rob Howard:
http://weblogs.asp.net/rhoward/archive/2003/11/17/38095.aspx
Then Frans Bouma:
http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
The Rob Howard rebuttal:
http://weblogs.asp.net/rhoward/archive/2003/11/18/38446.aspx
That should be a good start.

DISTRIBUTED TRANSACTION gives off ITransactionJoin error when using SQLOLEDB -

I've been trying to encapsulate this code into a transaction, but I get this error when I try to run it...

SET XACT_ABORT ON
GO
BEGIN TRANSACTION -- Also tried as a DISTRIBUTED TRAN

DELETE
FROM item

INSERT INTO item
([field1], [field2])
SELECT [field1], [field2]
FROM [LINKED_SERVER].[DBASE].[DBO].[ITEM]

If @.@.ERROR <> 0
ROLLBACK TRANSACTION

COMMIT TRANSACTION
GO
SET XACT_ABORT OFF
GO

Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

Thanks in advance for any help you can give me!!!Make sure RPC server did not post any errors in Event Viewer log, and that your previous attempt is not in ROLLBACK state on the linked server.|||I checked the event log and didn't see anything mentioned about that.

Thanks for you thoughts though!|||Do you have a firewall or any port restrictions in place?|||There is a firewall installed, what ports do I need to open up for this to take place?|||See
http://support.microsoft.com/default.aspx?scid=kb;en-us;250367

Also check that the remote server has the servername defined
select @.@.servername
I think this can upset things. It may be lost if you have changed the machine name.|||I'll check that and get back to you...sorry about the delay.|||I checked the servername properties for both servers and they do have names. I will be trying to get the firewall setup for the correct ports to be used with DTC now, but I know this will take a day or two since this i a dedicated server we are using at a hosting provider. So I will let you know how it goes...