detect replication erros

Hi,
I'm running SQL Sever 2000 SP4 and am replicating a database.
This is the scenario:
Server1 : holds the source database to be replicated (publisher)
Server2: is the one that receives the replicated data (suscriptor)
Some times it happens that the replication goes down and we do not realise
but until an user calls us.
We cannot implemet email notifications on the server2 as due to some
technical restrictions, MS Outlook cannot be installed on that server
What we want is a way that from server1 we are able to detect replication
failures and as this server has got SLQ Mail we'd (via a store procedure)
sent a notification off to the DBAs.
Any ideas?you can send mail without outlook
insert your message into "message" table,
create job in vbscript using CDO.Message and send mail over any smtp server
--
Aleksandar Grbic
MCDBA
"FJC" wrote:
> Hi,
> I'm running SQL Sever 2000 SP4 and am replicating a database.
> This is the scenario:
> Server1 : holds the source database to be replicated (publisher)
> Server2: is the one that receives the replicated data (suscriptor)
> Some times it happens that the replication goes down and we do not realise
> but until an user calls us.
> We cannot implemet email notifications on the server2 as due to some
> technical restrictions, MS Outlook cannot be installed on that server
> What we want is a way that from server1 we are able to detect replication
> failures and as this server has got SLQ Mail we'd (via a store procedure)
> sent a notification off to the DBAs.
> Any ideas?

detect replication erros

Hi,
I'm running SQL Sever 2000 SP4 and am replicating a database.
This is the scenario:
Server1 : holds the source database to be replicated (publisher)
Server2: is the one that receives the replicated data (suscriptor)
Some times it happens that the replication goes down and we do not realise
but until an user calls us.
We cannot implemet email notifications on the server2 as due to some
technical restrictions, MS Outlook cannot be installed on that server
What we want is a way that from server1 we are able to detect replication
failures and as this server has got SLQ Mail we'd (via a store procedure)
sent a notification off to the DBAs.
Any ideas?you can send mail without outlook
insert your message into "message" table,
create job in vbscript using CDO.Message and send mail over any smtp server
Aleksandar Grbic
MCDBA
"FJC" wrote:

> Hi,
> I'm running SQL Sever 2000 SP4 and am replicating a database.
> This is the scenario:
> Server1 : holds the source database to be replicated (publisher)
> Server2: is the one that receives the replicated data (suscriptor)
> Some times it happens that the replication goes down and we do not realise
> but until an user calls us.
> We cannot implemet email notifications on the server2 as due to some
> technical restrictions, MS Outlook cannot be installed on that server
> What we want is a way that from server1 we are able to detect replication
> failures and as this server has got SLQ Mail we'd (via a store procedure)
> sent a notification off to the DBAs.
> Any ideas?sql

detach-copy-attach vs Copy Database Wizard

Instead of running the Copy Database Wizard, if I
1 detached the source database
2 copied the files from the source to the destination server
3 attached the files to the destination server
does it accomplish the same thing? The source will be SQL 7 and the
destination will be SQL 2000, so a database upgrade is involved.
I want to be able to get a copy of the detached .mdf and .ldf files copied
to the destination server that I can use to test the upgrade from SQL 7 to
2000
numerous times if needed. I don't want to use the wizard repeatedly, since
it
requires that the source db be in single user mode or have no users
connected to it.
Thanks,
johnYour method works. You might want to look into BACKUP and RESTORE as =another method to "move" databases. One benefit with this method: you =can use the Transact-SQL command 'BACKUP' to backup your database =without having to take it offline (as you do with detach_db). Another =method is that you can simply come along with your favorite backup =utility and simply backup a file (instead of trying to backup an open =database).
-- Keith
"john" <jgorman@.humanitees.com> wrote in message =news:%23e3gJhZmDHA.2444@.TK2MSFTNGP09.phx.gbl...
> Instead of running the Copy Database Wizard, if I
> > 1 detached the source database
> 2 copied the files from the source to the destination server
> 3 attached the files to the destination server
> > does it accomplish the same thing? The source will be SQL 7 and the
> destination will be SQL 2000, so a database upgrade is involved.
> > I want to be able to get a copy of the detached .mdf and .ldf files =copied
> to the destination server that I can use to test the upgrade from SQL =7 to
> 2000
> numerous times if needed. I don't want to use the wizard repeatedly, =since
> it
> requires that the source db be in single user mode or have no users
> connected to it.
> > Thanks,
> john
> >|||Will a restore of a SQL 7 database to a SQL 2000 database result in an
upgrade of that database to SQL 2000?
john
Keith Kratochvil <sqlguy.back2u@.comcast.net> wrote in message
news:ufNP2uZmDHA.3700@.TK2MSFTNGP11.phx.gbl...
Your method works. You might want to look into BACKUP and RESTORE as
another method to "move" databases. One benefit with this method: you can
use the Transact-SQL command 'BACKUP' to backup your database without having
to take it offline (as you do with detach_db). Another method is that you
can simply come along with your favorite backup utility and simply backup a
file (instead of trying to backup an open database).
--
Keith
"john" <jgorman@.humanitees.com> wrote in message
news:%23e3gJhZmDHA.2444@.TK2MSFTNGP09.phx.gbl...
> Instead of running the Copy Database Wizard, if I
> 1 detached the source database
> 2 copied the files from the source to the destination server
> 3 attached the files to the destination server
> does it accomplish the same thing? The source will be SQL 7 and the
> destination will be SQL 2000, so a database upgrade is involved.
> I want to be able to get a copy of the detached .mdf and .ldf files copied
> to the destination server that I can use to test the upgrade from SQL 7 to
> 2000
> numerous times if needed. I don't want to use the wizard repeatedly,
since
> it
> requires that the source db be in single user mode or have no users
> connected to it.
> Thanks,
> john
>|||Yes.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"john" <jgorman@.humanitees.com> wrote in message
news:%23tOae2ZmDHA.1740@.TK2MSFTNGP12.phx.gbl...
> Will a restore of a SQL 7 database to a SQL 2000 database result in an
> upgrade of that database to SQL 2000?
> john
>
> Keith Kratochvil <sqlguy.back2u@.comcast.net> wrote in message
> news:ufNP2uZmDHA.3700@.TK2MSFTNGP11.phx.gbl...
> Your method works. You might want to look into BACKUP and RESTORE as
> another method to "move" databases. One benefit with this method: you
can
> use the Transact-SQL command 'BACKUP' to backup your database without
having
> to take it offline (as you do with detach_db). Another method is that
you
> can simply come along with your favorite backup utility and simply
backup a
> file (instead of trying to backup an open database).
> --
> Keith
>
> "john" <jgorman@.humanitees.com> wrote in message
> news:%23e3gJhZmDHA.2444@.TK2MSFTNGP09.phx.gbl...
> > Instead of running the Copy Database Wizard, if I
> >
> > 1 detached the source database
> > 2 copied the files from the source to the destination server
> > 3 attached the files to the destination server
> >
> > does it accomplish the same thing? The source will be SQL 7 and the
> > destination will be SQL 2000, so a database upgrade is involved.
> >
> > I want to be able to get a copy of the detached .mdf and .ldf files
copied
> > to the destination server that I can use to test the upgrade from
SQL 7 to
> > 2000
> > numerous times if needed. I don't want to use the wizard
repeatedly,
> since
> > it
> > requires that the source db be in single user mode or have no users
> > connected to it.
> >
> > Thanks,
> > john
> >
> >
>

detach problem

I'm using following connection string in my application in (C# 2.0 windows )

Data Source=.\SQLEXPRESS; Trusted_Connection=yes;AttachDbFilename=|DataDirectory|\myDB.mdf"

My application attaches the file successfully when the first connection is established, but after closing my application the database is remained in the sql server express and it does not detached automatically. According to this article the database shall be detached and I need it in my application because of the security.

http://technet.microsoft.com/en-us/library/ms165716.aspx

Please let me know what shall I do ?

Thanks in advance,

Saeideh

You should always close the conenction to the database as far as you don′t use it anymore.Othwerwise the database will be closed (and detached after a certain period of time). If you have enabled connection pooling it "can take a while" after the pool will be released and the connection will be closed.

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

User instance databases have the Auto Close option set so that if there are no connections to a database for 8-10 minutes, the database shuts down and the file is closed. This happens automatically, but it can take a while, especially if connection pooling is enabled for your connections.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Destination InputColumnCollection is empty

I am creating and running a package programmatically. I have the source component set up fine, and the destination component seems good, but when the package is run, it gets the error message: "Excel destination failed validation and returned validation status "VS_NEEDSNEWMETADATA" ". This would lead me to believe that I need a ReinitializeMetaData() call, but I already have that (see below). How do I fix this? Thanks for your help.

' Create and configure an OLE DB destination.

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

conDest.Name = "Excel File"

conDest.Description = "Excel File"

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' The ProvideComponentProperties method creates a default input.

destDesignTime.ProvideComponentProperties()

destination.RuntimeConnectionCollection(0).ConnectionManagerID = conDest.ID

destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(conDest)

destDesignTime.SetComponentProperty("AccessMode", 0)

destDesignTime.SetComponentProperty("OpenRowset", Dts.Variables("User::gsSheetName").Value.ToString)

destDesignTime.AcquireConnections(Nothing)

destDesignTime.ReinitializeMetaData()

destDesignTime.ReleaseConnections()

' Create the path from source to destination.

Dim path As IDTSPath90 = dataFlowTask.PathCollection.New

path.AttachPathAndPropagateNotifications(source.OutputCollection(0), _

destination.InputCollection(0))

' Get the destination's default input and virtual input.

Dim input As IDTSInput90 = destination.InputCollection(0)

Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput

' Iterate through the virtual input column collection.

For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection

' Call the SetUsageType method of the destination

' to add each available virtual input column as an input column.

destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)

Next

You have already called ReinitializeMetaData, but for a destination it should be done after you have connected the input. ReinitializeMetaData for destination is all about creating input columns and mapping them to external columns. If you have no input, then it will no do much. Subsequently I woudl expect validate to fail saying to call RMD again, as no columns is ivalid in my book.|||

I just tried moving the ReinitializeMetaData call to after the SetUsageType loop. There is no validation error now, but the package still fails. It appears that the source output was not mapped to the dest input. I get warning messages that

"the source columns are not subsequently being used in the data flow", and also an error message on the destination that "the number of columns is incorrect", and

"Cannot create OLEDB accessor. Verify that the column metadata is valid.", and finally a return error code of 0xc0202025

Is there something else I am missing, or something else in my code that is incorrect, out of order? Thanks...

|||

Have a think about the process required, and what each method does. For a destination, it is usual that you have an input and some external metadata columns. You need to first select columns. This makes them into input columns, and available to the component. You select columns from the virtual input, which represents all columns potentially available. Once you have input columns, you map them to the external metadata columns. These will have been generated during ReinitializeMetaData, and represent the external destination itself.

If you have a look at components with the Advanced Editor, things like how inputs, outputs and external columns are all used, and related.

Put simply, you need to select colums (from the virtual input) and then map what are now input columns, to the external columns. Here is a snippet from MS supplied CreatePackage sample. You can download new and updated samples from MS Downloads.

Code Snippet

#region MapFlatFileDestination Columns
private void MapFlatFileDestinationColumns()
{
CManagedComponentWrapper wrp = this.flatfileDestination.Instantiate();

IDTSVirtualInput90 vInput = this.flatfileDestination.InputCollection[0].GetVirtualInput();
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
wrp.SetUsageType(this.flatfileDestination.InputCollection[0].ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
}

// For each column in the input collection
// find the corresponding external metadata column.
foreach (IDTSInputColumn90 col in this.flatfileDestination.InputCollection[0].InputColumnCollection)
{
IDTSExternalMetadataColumn90 exCol = this.flatfileDestination.InputCollection[0].ExternalMetadataColumnCollection[col.Name];
wrp.MapInputColumn(this.flatfileDestination.InputCollection[0].ID, col.ID, exCol.ID);
}
}
#endregion

|||

Thanks for the info, Darren. Sorry if I seem a little dense, I didn't see any example of MapInputColumn in the BOL, so that was new to me I still have a problem, though. If you look at my code below, you'll see that I added the MapInputColumn to the end. However, when I run it, the destination.InputCollection(0).InputColumnCollection set is empty. I specified an existing file with a table defined. The SetUsageType works, so there are the correct number of columns (4) in the VirtualInputColumnCollection. Again, sorry to have so many questions, but we're really close on this one and I just want to solve it.

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

conDest.Name = "Excel File"

conDest.Description = "Excel File"

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' The ProvideComponentProperties method creates a default input.

destDesignTime.ProvideComponentProperties()

destination.RuntimeConnectionCollection(0).ConnectionManagerID = conDest.ID

destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(conDest)

destDesignTime.SetComponentProperty("AccessMode", 0)

destDesignTime.SetComponentProperty("OpenRowset", Dts.Variables("User::gsSheetName").Value.ToString)

' Create the path from source to destination.

Dim path As IDTSPath90 = dataFlowTask.PathCollection.New

path.AttachPathAndPropagateNotifications(source.OutputCollection(0), _

destination.InputCollection(0))

' Get the destination's default input and virtual input.

Dim input As IDTSInput90 = destination.InputCollection(0)

Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput

MsgBox(input.InputColumnCollection.Count)

' Iterate through the virtual input column collection.

For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection

' Call the SetUsageType method of the destination

' to add each available virtual input column as an input column.

destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)

Next

destDesignTime.AcquireConnections(Nothing)

destDesignTime.ReinitializeMetaData()

destDesignTime.ReleaseConnections()

Dim exCol As IDTSExternalMetadataColumn90

For Each column As IDTSInputColumn90 In input.InputColumnCollection

exCol = destination.InputCollection(0).ExternalMetadataColumnCollection(column.Name)

destDesignTime.MapInputColumn(destination.InputCollection(0).ID, column.ID, exCol.ID)

Next

|||

I am creating/running a package programmatically. I have the source component defined, and am trying to map the destination's input columns. The problem is that the destination.InputCollection(0).InputColumnCollection is empty, even though I have specified a destination Excel file that exists and has the specified table. The VirtualInputColumnCollection does have the correct columns, so I don't know why the InpuColumnCollection would be empty.

Am I missing a step in my code, or is there a way to create the columns manually? Thanks for your help.

Code Snippet

' Create and configure an OLE DB destination.

Dim conDest As ConnectionManager = package.Connections.Add("Excel")

conDest.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _

Dts.Variables("User::gsExcelFile").Value.ToString & ";Extended Properties=""Excel 8.0;HDR=YES"""

conDest.Name = "Excel File"

conDest.Description = "Excel File"

Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New

destination.ComponentClassID = "DTSAdapter.ExcelDestination"

' Create the design-time instance of the destination.

Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate

' The ProvideComponentProperties method creates a default input.

destDesignTime.ProvideComponentProperties()

destination.RuntimeConnectionCollection(0).ConnectionManagerID = conDest.ID

destination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(conDest)

destDesignTime.SetComponentProperty("AccessMode", 0)

destDesignTime.SetComponentProperty("OpenRowset", Dts.Variables("User::gsSheetName").Value.ToString)

' Create the path from source to destination.

Dim path As IDTSPath90 = dataFlowTask.PathCollection.New

path.AttachPathAndPropagateNotifications(source.OutputCollection(0), _

destination.InputCollection(0))

' Get the destination's default input and virtual input.

Dim input As IDTSInput90 = destination.InputCollection(0)

Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput

' Iterate through the virtual input column collection.

For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection

' to add each available virtual input column as an input column.

destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY)

Next

destDesignTime.AcquireConnections(Nothing)

destDesignTime.ReinitializeMetaData()

destDesignTime.ReleaseConnections()

Dim exCol As IDTSExternalMetadataColumn90

' This for loop does not get executed because the collection is empty

For Each column As IDTSInputColumn90 In destination.InputCollection(0).InputColumnCollection

exCol = destination.InputCollection(0).ExternalMetadataColumnCollection(column.Name)

destDesignTime.MapInputColumn(destination.InputCollection(0).ID, column.ID, exCol.ID)

Next

app.SaveToXml("c:\newpackage.dtsx", package, Nothing)

Dim ret As DTSExecResult

ret = package.Execute()

Console.WriteLine(ret.ToString)

MsgBox(ret.ToString)

|||

Hi

Have a look at this post where i had a similar problem.

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2181121&SiteID=17

Try to delegate the input column generation to the destination component.

Manuel Bauer

|||

I'll guess that you should call RMD before you select the columns. The SetUsageType method selects the column, which makes it avilable in the Input Column Collection.

I have written a complete sample. It assumes you have a workbook in the write location, with a sheet. We will leave creating workbooks and sheets for another day. Assuming the file is there and you have a local SQL server default instance, it will work I assure you! Pick what you need -

Code Block

namespace SSISExcelExport

{

class SimplePackage

{

public void CreatePackage()

{

Package package = new Package();

// Add the SQL connection

ConnectionManager sqlConnection = AddSqlConnection(package, "localhost", "master");

// Add the Excel connection

ConnectionManager excelConnection = AddExcelConnection(package, @."C:\Temp\Export.xls");

// Add the Data Flow task

package.Executables.Add("DTS.Pipeline.1");

// Get the pipeline

TaskHost dataFlowTask = package.Executables[0] as TaskHost;

MainPipe pipeline = dataFlowTask.InnerObject as MainPipe;

// Add the SQL Server source

string query = "SELECT id, name FROM sysobjects";

IDTSComponentMetaData90 source = pipeline.ComponentMetaDataCollection.New();

source.ComponentClassID = "DTSAdapter.OleDbSource.1";

source.Name = "SQL Source";

CManagedComponentWrapper sourceInstance = source.Instantiate();

sourceInstance.ProvideComponentProperties();

source.RuntimeConnectionCollection[0].ConnectionManagerID = sqlConnection.ID;

source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(sqlConnection);

sourceInstance.SetComponentProperty("AccessMode", 2);

sourceInstance.SetComponentProperty("SqlCommand", query);

sourceInstance.AcquireConnections(null);

sourceInstance.ReinitializeMetaData();

sourceInstance.ReleaseConnections();

// Add Excel destination

string sheetName = "Sheet";

IDTSComponentMetaData90 destination = pipeline.ComponentMetaDataCollection.New();

destination.ComponentClassID = "DTSAdapter.ExcelDestination.1";

destination.Name = "Excel Destination";

CManagedComponentWrapper destinationInstance = destination.Instantiate();

destinationInstance.ProvideComponentProperties();

destination.RuntimeConnectionCollection[0].ConnectionManagerID = excelConnection.ID;

destination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(excelConnection);

destinationInstance.SetComponentProperty("AccessMode", 0);

destinationInstance.SetComponentProperty("OpenRowset", sheetName);

destinationInstance.AcquireConnections(null);

destinationInstance.ReinitializeMetaData();

destinationInstance.ReleaseConnections();

IDTSInput90 destinationInput = destination.InputCollection[0];

// Connect the source to the destination

IDTSPath90 path = pipeline.PathCollection.New();

path.AttachPathAndPropagateNotifications(source.OutputCollection[0], destinationInput);

// Select destination input columns

IDTSVirtualInput90 virtualInput = destinationInput.GetVirtualInput();

foreach (IDTSVirtualInputColumn90 column in virtualInput.VirtualInputColumnCollection)

{

destinationInstance.SetUsageType(destinationInput.ID, virtualInput, column.LineageID, DTSUsageType.UT_READONLY);

}

// Map input column to external metadata column.

for (int index = 0; index < destinationInput.InputColumnCollection.Count; index++)

{

destinationInstance.MapInputColumn(destinationInput.ID, destinationInput.InputColumnCollection[index].ID, destinationInput.ExternalMetadataColumnCollection[index].ID);

}

#if DEBUG

// Save package to disk, DEBUG only

new Application().SaveToXml(@."C:\Temp\" + package.Name, package, null);

#endif

package.Execute();

}

#region Add Connections

private static ConnectionManager AddExcelConnection(Package package, string filename)

{

return AddConnection(package, "EXCEL", String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=YES\"", filename));

}

private static ConnectionManager AddSqlConnection(Package package, string server, string database)

{

return AddConnection(package, "OLEDB", String.Format("Provider=SQLOLEDB.1;Data Source={0};Persist Security Info=False;Initial Catalog={1};Integrated Security=SSPI;", server, database));

}

private static ConnectionManager AddConnection(Package package, string type, string connectionString)

{

ConnectionManager manager = package.Connections.Add(type);

manager.ConnectionString = connectionString;

manager.Name = String.Format("{0} Connection", type);

return manager;

}

#endregion

}

}

Design Time XSD Metadata refresh

My SSIS package has 19 XML Source inputs constructed of 4 different (XSD) Schemas. I'm trying to find a convenient way to refresh the underlying metadata at design time so that I don't have to open each XML Source, change the XSD reference, click on the columns display, change the XSD back to the original but updated XSD just to get say a new default column width for string data. Does anyone have a quicker way to force an SSIS package to pick up changes in a referenced XSD?

Unfortunately, there is no an easy way to do this. Maybe the easiest would be to do this programmatically.

See the following thread for more details:

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

Thanks,

Bob

design question / confirmation

I have a fact table that has several fields that are Y/N in the source system.

Originally, I created the fact table converting the data from Y to 1 and N to zero, with the measure aggregate function property set to "sum".

However after reading some of the posts by Mosha, I think I should be converting these N's to NULLs instead of Zero's to take advantage of NON EMPTY.

Is my logic correct?

It really depends on what you want to do with this data. It's hard to give advice without more details. Typically, in the questionarie type of models, the Y/N columns are converted to attributes, not to measures, and the measure is count. This way it is easy to get how many people answered Yes and how many people answered No. By doing this as measure - you will have easy way to find out how many said Yes, but you will lose info about how many said No.|||

In this case the yes / no indicate if a particular production step was performed. So the values will get summed up for a total.

the no's mean nothing to me.

that total may then also be used to determine a percent of all products that had that production step performed.

|||Then it is possible to model it the way you proposed (although I still think that making a Y/N attribute is a good idea).

design question / confirmation

I have a fact table that has several fields that are Y/N in the source system.

Originally, I created the fact table converting the data from Y to 1 and N to zero, with the measure aggregate function property set to "sum".

However after reading some of the posts by Mosha, I think I should be converting these N's to NULLs instead of Zero's to take advantage of NON EMPTY.

Is my logic correct?

It really depends on what you want to do with this data. It's hard to give advice without more details. Typically, in the questionarie type of models, the Y/N columns are converted to attributes, not to measures, and the measure is count. This way it is easy to get how many people answered Yes and how many people answered No. By doing this as measure - you will have easy way to find out how many said Yes, but you will lose info about how many said No.|||

In this case the yes / no indicate if a particular production step was performed. So the values will get summed up for a total.

the no's mean nothing to me.

that total may then also be used to determine a percent of all products that had that production step performed.

|||Then it is possible to model it the way you proposed (although I still think that making a Y/N attribute is a good idea).

Design For Loading Data Without Knowlged of Datatype or Column Count

I am loading data from an external source into SQL Server via ASP.NET/C#. The problem is that I do not necessarily know the data types of each column coming in, perhaps until a user tells the application, which might not occur until after the data is loaded. Also, I cannot anticipate the number of columns coming in. What would table design look like?

Would you use a large table with enough columns (e.g. Column1, Column2, etc.) reasonable enough to accomodate all the columns that the source might have (32?), and use nchar as the datatype with the plan to convert/cast when I use the data? Isn't the cast kind of expensive?

Does this make sense? Surely other foplks have run into this...

My thanks!

If you're doing what I think you're doing, you might want to use an EAV (Entity/Attribute/Value) table to store your data. The attribute table stores the column definitions. The entity table store the "row id"s. The value table stores the actual values and is of the form

Entity_id int

Attribute_id int

Value (string/whatever)

You can have multiple value tables for different data types, but I find that it's more trouble than it's worth. I've used this on several projects where the data being captured is set by the users at run time.