Programming SQL Express to SQL 2005 replication

Hi Everyone

I have a huge problem after I noticed that SQL Server Agent has been removed

and need your advice about how to program replication between SQL Server Express and SQL Server 2005

So far I have encountered the following problems

SNAPSHOT

1. When I created my snapshot Irealized it wasnt running. I then read from the documentation that i had to resort to the Windows Synchronization Manager as there was no SQL Server AGENT within SQL Server Express to accomplish this function

I did this and it worked but wouldlike to control this programmatically as well

REPLICATION

2. Now I have a standalone SQL Server Express which is a subsciber to the SQL Server 2005 Database

How can I force the merge replication programmatically ie Vb.NET

Your kind advice is appreciated

Regards

Touraj

To learn how to use replication with SQL Express, see topic "Replicating Data to SQL Express" in Books Online.

|||

Thankyou very much

Programming Reports against using Report Builder

I am trying to find some reasons why it would be beneficial to program a report through .NET 2.0 by Generating Report Definition Language to enable users to create dynamic reports, which then can be published to the report server using reporting services web service INSTEAD of just simply using report builder for external users.

There must be situations when report builder is restricted to provide certain functionality which could be accomplished instead programming the report with RDL.

I would appreciate a quick response. Thank you

I a very astract view, the report builder does nothing else than composing the RDL which is created by the graphical modifications of the user in the GUI.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Thanks,

But what can I do with programming RDL using Visual Studio 2005 which I cannot do with Report Builder, i.e. what functionality does report builder not support which can be acheived by programming RDL to create dynamic reports?

Programming databaseconnections?

Hi!

I want some tutorials on how to actually program database applications. Everything seems so bound with the data controls provided by Visual Studio and coming from PHP I don't like to not have full control over my applications. So where can I learn about creating my own database applications without using repeater, gridview or formview?

Thank you in advance!

yes data controls can be over-welming and I see many instances where the pursuit of them consumes more effort that a more procedural approach. Coming from PHO, have you looked at http://www.learn2asp.net/php/Campaign.aspx - these should help your transition to ASP.NET from PHP.

There are also some good tutorials on programming database applications at http://www.asp.net/learn/videos/default.aspx?tabid=63

Programmatically developing an entire package..

Hi all,

I am trying to write a program that creates packages on the fly depending on the variables you pass. for eg. It should create connection managers on the fly specific to a certain file in the variable (eg. sample.csv). the package has a dataflow task and it has flat file source and oledb destination.The problem I am facing is the flat file source when assigned to a flat file connection manager(dynamically), it is not giving any source output columns. i.e, the value for DFSource.OutputCollection(0).OutputColumnCollection.Count is Zero. But when I use the same code and reverse the source and destination(oledb as source and flatfile as destination), it is working fine. I searched everywhere for resources on how to develop packages programmatically, but could not find any except one example on msdn. If anyone knows about this prob or any useful resources on this subject, it would be really helpful.

Thanks,

Prithvi.

This is the code for creating connections and adding dataflow task. I need to add a script component transformation, but initially I wanted to see if a one-to-one mapping works fine.

Public Sub CreateConnections()

'Add the OLE DB and Flat File Connection Managers

Console.WriteLine("Creating the MyOLEDBConnection")

Dim cnOLEDB As ConnectionManager = myPackage.Connections.Add("OLEDB")

cnOLEDB.Name = "MyOLEDBConnection"

cnOLEDB.ConnectionString = "<connection string>"

Console.WriteLine("Creating the MyFlatFileConnection")

Dim cnFile As ConnectionManager = myPackage.Connections.Add("FLATFILE")

cnFile.Name = "MyFlatFileConnection"

cnFile.Properties("ConnectionString").SetValue(cnFile, "C:\sample.csv")

cnFile.Properties("Format").SetValue(cnFile, "Delimited")

cnFile.Properties("ColumnNamesInFirstDataRow").SetValue(cnFile, False)

cnFile.Properties("DataRowsToSkip").SetValue(cnFile, 0)

cnFile.Properties("RowDelimiter").SetValue(cnFile, vbCrLf)

cnFile.Properties("TextQualifier").SetValue(cnFile, """")

End Sub

Public Sub AddDataFlowTask()

'Add a Data Flow Task

Console.WriteLine("Adding a Data Flow Task")

Dim e As Executable = myPackage.Executables.Add("DTS.Pipeline")

Dim taskDF As TaskHost = CType(e, TaskHost)

taskDF.Name = "DataFlow"

Dim DTP As MainPipe

DTP = CType(taskDF.InnerObject, MainPipe)

' Add the FLAT FILE Source

Console.WriteLine("Adding the File Source")

Dim DFSource As IDTSComponentMetaData90

DFSource = DTP.ComponentMetaDataCollection.New()

DFSource.ComponentClassID = "DTSAdapter.FlatFileSource.1"

DFSource.Name = "FlatFileSource"

' Connect, populate the Input collections and disconnect

Dim SourceInst As CManagedComponentWrapper = DFSource.Instantiate()

SourceInst.ProvideComponentProperties()

DFSource.RuntimeConnectionCollection(0).ConnectionManagerID = myPackage.Connections("MyFlatFileConnection").ID

DFSource.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(myPackage.Connections("MyFlatFileConnection"))

SourceInst.AcquireConnections(vbNull)

SourceInst.ReinitializeMetaData()

SourceInst.ReleaseConnections()

'checking If the external metadata columns are available as source output columns and the output is 0 which means no columns are being passed as output from source

Dim column As IDTSOutputColumn90

Try

Console.WriteLine("connection name: " & DFSource.RuntimeConnectionCollection(0).Name.ToString)

Console.WriteLine("output collection name: " & DFSource.OutputCollection(0).Name)

Console.WriteLine("output collection description :" & DFSource.OutputCollection(0).Description)

Console.WriteLine("source output columns count :" & DFSource.OutputCollection(0).OutputColumnCollection.Count.ToString

Catch ex As Exception

Console.WriteLine(ex.InnerException.Message.ToString)

End Try

'tried to print col names but it does not print any

For Each column In DFSource.OutputCollection(0).OutputColumnCollection

Console.WriteLine(column.Name.ToString)

Console.WriteLine(column.DataType.ToString)

Next

' Add the OLEDB Destination

Console.WriteLine("Adding OLEDB Destination")

Dim DFDestination As IDTSComponentMetaData90

DFDestination = DTP.ComponentMetaDataCollection.New()

DFDestination.ComponentClassID = "DTSAdapter.OLEDBDestination"

DFDestination.Name = "OLEDBDestination"

' Create an instance of the component

Dim DestInst As CManagedComponentWrapper = DFDestination.Instantiate()

DestInst.ProvideComponentProperties()

If DFDestination.RuntimeConnectionCollection.Count > 0 Then

DFDestination.RuntimeConnectionCollection(0).ConnectionManagerID = myPackage.Connections("MyOLEDBConnection").ID

DFDestination.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(myPackage.Connections("MyOLEDBConnection"))

End If

DestInst.SetComponentProperty("AccessMode", 0)

DestInst.SetComponentProperty("OpenRowset", "tempSSIS")

DestInst.SetComponentProperty("FastLoadKeepNulls", True)

' Map a connection between the source and destination

Dim path As IDTSPath90 = DTP.PathCollection.New()

path.AttachPathAndPropagateNotifications(DFSource.OutputCollection(0), DFDestination.InputCollection(0))

Dim InColumns As IDTSVirtualInputColumnCollection90 = DFDestination.InputCollection(0).GetVirtualInput().VirtualInputColumnCollection()

' the number of input columns to destination is zero

Console.WriteLine("input columns : " & InColumns.Count.ToString)

Try

DestInst.AcquireConnections(vbNull)

Catch ex As Exception

Console.WriteLine(ex.InnerException.Message)

End Try

DestInst.ReinitializeMetaData()

'Console.WriteLine("input columns : " & DFDestination.InputCollection(0).InputColumnCollection.Count.ToString)

For Each input As IDTSInput90 In DFDestination.InputCollection

' Get the virtual input column collection for the input.

Dim vInput As IDTSVirtualInput90 = input.GetVirtualInput()

' Iterate through the virtual column collection.

For Each vColumn As IDTSVirtualInputColumn90 In vInput.VirtualInputColumnCollection

' Call the SetUsageType method of the design time instance of the component.

Console.WriteLine(vColumn.Name.ToString)

Console.WriteLine(vColumn.DataType)

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

Next

Dim exCol As IDTSExternalMetadataColumn90

For Each col As IDTSInputColumn90 In DFDestination.InputCollection(0).InputColumnCollection

exCol = DFDestination.InputCollection(0).ExternalMetadataColumnCollection(col.Name)

DestInst.MapInputColumn(DFDestination.InputCollection(0).ID, col.ID, exCol.ID)

Next

DestInst.ReleaseConnections()

End Sub

Please see if i did wrong any where(which I always happen to do). But based on the msdn material, the above code should work, i guess.

Programmatically creating SSIS package

Hi guys,

I was intended to write a program that will create a SSIS package which will import data from a CSV file to the SQL server 2005. But I did not find any good example for this into the internet. I found some example which exports data from SQL server 2005 to CSV files. And following those examples I have tried to write my own. But I am facing some problem with that. What I am doing here is creating two connection manager objects, one for Flat file and another for OLEDB. And create a data flow task that has two data flow component, one for reading source and another for writing to destination. While debugging I can see that after invoking the ReinitializedMetaData() for the flat file source data flow component, there is not output column found. Why it is not fetching the output columns from the CSV file? And after that when it invokes the ReinitializedMetaData() for the destination data flow component it simply throws exception.

Can any body help me to get around this problem? Even can anyone give me any link where I can find some useful article to accomplish this goal?

I am giving my code here too.

I will appreciate any kind of suggestion on this.

Code snippet:

public void CreatePackage()

{

string executeSqlTask = typeof(ExecuteSQLTask).AssemblyQualifiedName;

Package pkg = new Package();

pkg.PackageType = DTSPackageType.DTSDesigner90;

ConnectionManager oledbConnectionManager = CreateOLEDBConnection(pkg);

ConnectionManager flatfileConnectionManager =

CreateFileConnection(pkg);

// creating the SQL Task for table creation

Executable sqlTaskExecutable = pkg.Executables.Add(executeSqlTask);

ExecuteSQLTask execSqlTask = (sqlTaskExecutable as Microsoft.SqlServer.Dts.Runtime.TaskHost).InnerObject as ExecuteSQLTask;

execSqlTask.Connection = oledbConnectionManager.Name;

execSqlTask.SqlStatementSource =

"CREATE TABLE [MYDATABASE].[dbo].[MYTABLE] \n ([NAME] NVARCHAR(50),[AGE] NVARCHAR(50),[GENDER] NVARCHAR(50)) \nGO";

// creating the Data flow task

Executable dataFlowExecutable = pkg.Executables.Add("DTS.Pipeline.1");

TaskHost pipeLineTaskHost = (TaskHost)dataFlowExecutable;

MainPipe dataFlowTask = (MainPipe)pipeLineTaskHost.InnerObject;

// Put a precedence constraint between the tasks.

PrecedenceConstraint pcTasks = pkg.PrecedenceConstraints.Add(sqlTaskExecutable, dataFlowExecutable);

pcTasks.Value = DTSExecResult.Success;

pcTasks.EvalOp = DTSPrecedenceEvalOp.Constraint;

// Now adding the data flow components

IDTSComponentMetaData90 sourceDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();

sourceDataFlowComponent.Name = "Source Data from Flat file";

// Here is the component class id for flat file source data

sourceDataFlowComponent.ComponentClassID = "{90C7770B-DE7C-435E-880E-E718C92C0573}";

CManagedComponentWrapper managedInstance = sourceDataFlowComponent.Instantiate();

managedInstance.ProvideComponentProperties();

sourceDataFlowComponent.

RuntimeConnectionCollection[0].ConnectionManagerID = flatfileConnectionManager.ID;

sourceDataFlowComponent.

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

managedInstance.AcquireConnections(null);

managedInstance.ReinitializeMetaData();

managedInstance.ReleaseConnections();

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

IDTSOutput90 output = sourceDataFlowComponent.OutputCollection[0];

// Here I dont find any columns at all..why?

// Now adding the data flow components

IDTSComponentMetaData90 destinationDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();

destinationDataFlowComponent.Name =

"Destination Oledb compoenent";

// Here is the component class id for Oledvb data

destinationDataFlowComponent.ComponentClassID = "{E2568105-9550-4F71-A638-B7FE42E66922}";

CManagedComponentWrapper managedOleInstance = destinationDataFlowComponent.Instantiate();

managedOleInstance.ProvideComponentProperties();

destinationDataFlowComponent.

RuntimeConnectionCollection[0].ConnectionManagerID = oledbConnectionManager.ID;

destinationDataFlowComponent.

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

// Set the custom properties.

managedOleInstance.SetComponentProperty("AccessMode", 2);

managedOleInstance.SetComponentProperty("OpenRowset", "[MYDATABASE].[dbo].[MYTABLE]");

managedOleInstance.AcquireConnections(null);

managedOleInstance.ReinitializeMetaData(); // Throws exception

managedOleInstance.ReleaseConnections();

// Create the path.

IDTSPath90 path = dataFlowTask.PathCollection.New(); path.AttachPathAndPropagateNotifications(sourceDataFlowComponent.OutputCollection[0],

destinationDataFlowComponent.InputCollection[0]);

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

IDTSInput90 input = destinationDataFlowComponent.InputCollection[0];

IDTSVirtualInput90 vInput = input.GetVirtualInput();

// Iterate through the virtual input column collection.

foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)

{

managedOleInstance.SetUsageType(

input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);

}

DTSExecResult res = pkg.Execute();

}

public ConnectionManager CreateOLEDBConnection(Package p)

{

ConnectionManager ConMgr;

ConMgr = p.Connections.Add("OLEDB");

ConMgr.ConnectionString =

"Data Source=VSTS;Initial Catalog=MYDATABASE;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;";

ConMgr.Name = "SSIS Connection Manager for Oledb";

ConMgr.Description = "OLE DB connection to the Test database.";

return ConMgr;

}

public ConnectionManager CreateFileConnection(Package p)

{

ConnectionManager connMgr;

connMgr = p.Connections.Add("FLATFILE");

connMgr.ConnectionString = @."D:\MyCSVFile.csv";

connMgr.Name = "SSIS Connection Manager for Files";

connMgr.Description = "Flat File connection";

connMgr.Properties["Format"].SetValue(connMgr, "Delimited");

connMgr.Properties["HeaderRowDelimiter"].SetValue(connMgr, Environment.NewLine);

return connMgr;

}

And my CSV files is as follows

NAME, AGE, GENDER

Jon,52,MALE

Linda, 26, FEMALE

Thats all. Thanks.

As mentioned earlier, this CSV to OLEDB package generator is not far from working.

First, the AccessMode on the OLEDB destination is set to the constant 2, which the "Sql Command" constant. Change that back to 0 ("Table or View").

FYI, the OLEDB destination's Access Mode enumeration is undocumented, but nevertheless can be found in the following dll: Microsoft SQL Server\DTS\PipelineComponents\OleDbDest.dll using a program like "PE Explorer".

Enum AccessMode;

AM_OPENROWSET = 0
AM_OPENROWSET_VARIABLE = 1
AM_SQLCOMMAND = 2
AM_OPENROWSET_FASTLOAD = 3
AM_OPENROWSET_FASTLOAD_VARIABLE = 4

Next, the target table doesn't exist , yet ReinitializeMetadata() for the OLEDB destination component will by default attempt to retrieve the target table's meta data. The table isn't there, so an exception is thrown. Therefore, as part of the driving program, you may wish to create the table temporarily (this table create is separate from the create in the Execute SQL Task), so that there is meta-data to reinitialize.

That should eliminate exceptions, but it doesn't mean the package will validate or execute successfully, just that you could save it to xml.

Then, add source columns for your flat file. SSIS does not materialize them for you (at least as far as I know), so the user needs to do perform that programmatically.|||

Hello jaegd,

At last I made it work today . Your tips tremendously helped me. After implementing your suggestions I had to do very little coding to make it working. Thanks a lot .

Anyway, as I am creating the source columns programmatically for the Flat file connection, I had to read the columns from the CSV file using .net IO functionality. Is there any way to accomplish this through SSIS APIs?

Thank you very much again.

Regards

Moim

|||

Hello,

How did you create the metadata for the destination?

Thanks in advance.

Cheers,

kix

Programmatically creating SSIS package

Hi guys,

I was intended to write a program that will create a SSIS package which will import data from a CSV file to the SQL server 2005. But I did not find any good example for this into the internet. I found some example which exports data from SQL server 2005 to CSV files. And following those examples I have tried to write my own. But I am facing some problem with that. What I am doing here is creating two connection manager objects, one for Flat file and another for OLEDB. And create a data flow task that has two data flow component, one for reading source and another for writing to destination. While debugging I can see that after invoking the ReinitializedMetaData() for the flat file source data flow component, there is not output column found. Why it is not fetching the output columns from the CSV file? And after that when it invokes the ReinitializedMetaData() for the destination data flow component it simply throws exception.

Can any body help me to get around this problem? Even can anyone give me any link where I can find some useful article to accomplish this goal?

I am giving my code here too.

I will appreciate any kind of suggestion on this.

Code snippet:

public void CreatePackage()

{

string executeSqlTask = typeof(ExecuteSQLTask).AssemblyQualifiedName;

Package pkg = new Package();

pkg.PackageType = DTSPackageType.DTSDesigner90;

ConnectionManager oledbConnectionManager = CreateOLEDBConnection(pkg);

ConnectionManager flatfileConnectionManager =

CreateFileConnection(pkg);

// creating the SQL Task for table creation

Executable sqlTaskExecutable = pkg.Executables.Add(executeSqlTask);

ExecuteSQLTask execSqlTask = (sqlTaskExecutable as Microsoft.SqlServer.Dts.Runtime.TaskHost).InnerObject as ExecuteSQLTask;

execSqlTask.Connection = oledbConnectionManager.Name;

execSqlTask.SqlStatementSource =

"CREATE TABLE [MYDATABASE].[dbo].[MYTABLE] \n ([NAME] NVARCHAR(50),[AGE] NVARCHAR(50),[GENDER] NVARCHAR(50)) \nGO";

// creating the Data flow task

Executable dataFlowExecutable = pkg.Executables.Add("DTS.Pipeline.1");

TaskHost pipeLineTaskHost = (TaskHost)dataFlowExecutable;

MainPipe dataFlowTask = (MainPipe)pipeLineTaskHost.InnerObject;

// Put a precedence constraint between the tasks.

PrecedenceConstraint pcTasks = pkg.PrecedenceConstraints.Add(sqlTaskExecutable, dataFlowExecutable);

pcTasks.Value = DTSExecResult.Success;

pcTasks.EvalOp = DTSPrecedenceEvalOp.Constraint;

// Now adding the data flow components

IDTSComponentMetaData90 sourceDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();

sourceDataFlowComponent.Name = "Source Data from Flat file";

// Here is the component class id for flat file source data

sourceDataFlowComponent.ComponentClassID = "{90C7770B-DE7C-435E-880E-E718C92C0573}";

CManagedComponentWrapper managedInstance = sourceDataFlowComponent.Instantiate();

managedInstance.ProvideComponentProperties();

sourceDataFlowComponent.

RuntimeConnectionCollection[0].ConnectionManagerID = flatfileConnectionManager.ID;

sourceDataFlowComponent.

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

managedInstance.AcquireConnections(null);

managedInstance.ReinitializeMetaData();

managedInstance.ReleaseConnections();

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

IDTSOutput90 output = sourceDataFlowComponent.OutputCollection[0];

// Here I dont find any columns at all..why?

// Now adding the data flow components

IDTSComponentMetaData90 destinationDataFlowComponent = dataFlowTask.ComponentMetaDataCollection.New();

destinationDataFlowComponent.Name =

"Destination Oledb compoenent";

// Here is the component class id for Oledvb data

destinationDataFlowComponent.ComponentClassID = "{E2568105-9550-4F71-A638-B7FE42E66922}";

CManagedComponentWrapper managedOleInstance = destinationDataFlowComponent.Instantiate();

managedOleInstance.ProvideComponentProperties();

destinationDataFlowComponent.

RuntimeConnectionCollection[0].ConnectionManagerID = oledbConnectionManager.ID;

destinationDataFlowComponent.

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

// Set the custom properties.

managedOleInstance.SetComponentProperty("AccessMode", 2);

managedOleInstance.SetComponentProperty("OpenRowset", "[MYDATABASE].[dbo].[MYTABLE]");

managedOleInstance.AcquireConnections(null);

managedOleInstance.ReinitializeMetaData(); // Throws exception

managedOleInstance.ReleaseConnections();

// Create the path.

IDTSPath90 path = dataFlowTask.PathCollection.New(); path.AttachPathAndPropagateNotifications(sourceDataFlowComponent.OutputCollection[0],

destinationDataFlowComponent.InputCollection[0]);

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

IDTSInput90 input = destinationDataFlowComponent.InputCollection[0];

IDTSVirtualInput90 vInput = input.GetVirtualInput();

// Iterate through the virtual input column collection.

foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)

{

managedOleInstance.SetUsageType(

input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);

}

DTSExecResult res = pkg.Execute();

}

public ConnectionManager CreateOLEDBConnection(Package p)

{

ConnectionManager ConMgr;

ConMgr = p.Connections.Add("OLEDB");

ConMgr.ConnectionString =

"Data Source=VSTS;Initial Catalog=MYDATABASE;Provider=SQLNCLI;Integrated Security=SSPI;Auto Translate=false;";

ConMgr.Name = "SSIS Connection Manager for Oledb";

ConMgr.Description = "OLE DB connection to the Test database.";

return ConMgr;

}

public ConnectionManager CreateFileConnection(Package p)

{

ConnectionManager connMgr;

connMgr = p.Connections.Add("FLATFILE");

connMgr.ConnectionString = @."D:\MyCSVFile.csv";

connMgr.Name = "SSIS Connection Manager for Files";

connMgr.Description = "Flat File connection";

connMgr.Properties["Format"].SetValue(connMgr, "Delimited");

connMgr.Properties["HeaderRowDelimiter"].SetValue(connMgr, Environment.NewLine);

return connMgr;

}

And my CSV files is as follows

NAME, AGE, GENDER

Jon,52,MALE

Linda, 26, FEMALE

Thats all. Thanks.

As mentioned earlier, this CSV to OLEDB package generator is not far from working.

First, the AccessMode on the OLEDB destination is set to the constant 2, which the "Sql Command" constant. Change that back to 0 ("Table or View").

FYI, the OLEDB destination's Access Mode enumeration is undocumented, but nevertheless can be found in the following dll: Microsoft SQL Server\DTS\PipelineComponents\OleDbDest.dll using a program like "PE Explorer".

Enum AccessMode;

AM_OPENROWSET = 0
AM_OPENROWSET_VARIABLE = 1
AM_SQLCOMMAND = 2
AM_OPENROWSET_FASTLOAD = 3
AM_OPENROWSET_FASTLOAD_VARIABLE = 4

Next, the target table doesn't exist , yet ReinitializeMetadata() for the OLEDB destination component will by default attempt to retrieve the target table's meta data. The table isn't there, so an exception is thrown. Therefore, as part of the driving program, you may wish to create the table temporarily (this table create is separate from the create in the Execute SQL Task), so that there is meta-data to reinitialize.

That should eliminate exceptions, but it doesn't mean the package will validate or execute successfully, just that you could save it to xml.

Then, add source columns for your flat file. SSIS does not materialize them for you (at least as far as I know), so the user needs to do perform that programmatically.

|||

Hello jaegd,

At last I made it work today . Your tips tremendously helped me. After implementing your suggestions I had to do very little coding to make it working. Thanks a lot .

Anyway, as I am creating the source columns programmatically for the Flat file connection, I had to read the columns from the CSV file using .net IO functionality. Is there any way to accomplish this through SSIS APIs?

Thank you very much again.

Regards

Moim

|||

Hello,

How did you create the metadata for the destination?

Thanks in advance.

Cheers,

kix

Programmatic access to Enabled Protocols in SQL2005?

My C++ program is able to read the "Enabled Protocols" for SQL2005 by
reading the registry at:
HKLM\Software\Microsoft\Microsoft SQL Server\<instance
name>\MSSQLServer\SuperSocketLib\<protocol alias> and then reading the
"Enabled" value to see if it is enabled or not.
This works fine for named instances, but I can't find the registry values
that tell me which Enabled Protocols are defined for the default instance in
SQL2005.
Can anyone assist?
Many thanks!Hi,
Thanks for your post!
From your description, I understand that:
Your application need to search for the SQL Server 2005 enabled protocols
information in registry.
You managed to get the "Enable" value if the SQL Server was installed as a
named instance;
however the "Enable" value was not found at that location if the SQL Server
was installed as a default instance.
If I have misunderstood, please let me know.
From my research, I found the "Enable" value was under the directory
"HKLM\Software\Microsoft\Microsoft SQL
Server\MSSQL.1\MSSQLServer\SuperSocketNetLib\<protocol alias>",
which was different from yours. My SQL Server 2005 was installed as a named
instance as well.
The directory as you mentioned is also existed in my registry, however I
couldn't find the "Enable" value under that.
In fact, if your SQL Server 2005 was installed as a default instance, you
may found the default value "MSSQLServer" under
HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.x\MSSQLServer\
Both default instance and named instance have the related registry key
MSSQL.x. The registry directory "MSSQL.1" references the first instance of
your SQL Server 2005, so I recommend you check that registry directory
firstly.
Also, you may try to search for the key value "SuperSocketNetLib" under the
registry directory "HKLM\Software\Microsoft\".
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a w to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
Just checking in to see if the suggestions were helpful. Please let us know
if you would like further assistance.
Have a great day!
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a w to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Program type out of range - S1003

We have a Powerbuilder 4.0 application that used to run on SQL Server 7.0. Recently, we migrated our database to SQL Server 2000. We can do ODBC and can connect to the database from any tools such as Access, Excel and register the new SQL Server 2000 to other SQL Server, but when we try to use the client application, the user can not connect to the database. We receive the error message as the subject line. The user interface opens up but there is no data. The orginal program was written in Powerbuilder 4.0 by our vendor who probably does not have a copy of the source codes in proper state as I understand from talking to them. My question is, is there something that I can do to help my user connect to the database by using the client interface? This is getting a bit frustrating as no one really has an answer for it. I tried to install Powerbuilder 9.0 just to see if that updates any driver file that my machine has and that would be recongnize by the client programs, but it will not.

What can we do or what suggestions anyone can extend? Thanks for any inputs.What is the error message ? Look in the sql server log file as well as the event viewer for additional error messages.|||Originally posted by rnealejr
What is the error message ? Look in the sql server log file as well as the event viewer for additional error messages.

Actually, in the SQL Server log there is nothing since no connection was established from the client side.|||Originally posted by wellsound
Actually, in the SQL Server log there is nothing since no connection was established from the client side.

Well then it's not a sql server problem or issue...|||Yes I knew that, but since this is a Powerbuilder-client app with SQL Server backend, I have known that there are problems with the 16 bit ODBC driver that Powerbuilder used in their earlier version which is what the client app is built on, that is Powerbuilder Version 4.0 and that is exactly why I thought of asking in this forum to see if someone may have a related answer to that. But thanks for your reply.|||Is it using a dsn connection ? If so, what are the properties - and have you tried updating the driver and use that dsn ?|||I think I've seen this error before when a variable or column is unable toa accept a value submitted to it. Is there a procedure that runs when the user logs in, and are there any parameters passed to the procedure?

blindman|||Originally posted by rnealejr
Is it using a dsn connection ? If so, what are the properties - and have you tried updating the driver and use that dsn ? This is what we have in the configuration file
[<name of the sytem]
DBMS=SYB SQL Server v4.x
ServerName=, server name>
Database= <database name>
DbParm=appname='name of the app>',release='4.2'

We made a number of changes to this such as change the SYB SQL Server v4.x to DSN and on the DbParm we have used = dsn=<data source name> database = <database> name

None of these seem to work. The only thing we had was the client user interface opens up but no data comes up.

Program to save the blocking on a table

I need save the blocking or deadlock occurrences on a certain table to a
file.
How to write a program (any kind, SQL, Perl, C, Java, etc) to do it? I don't
want to use SQL profiler. I hope a program can do it for me.
Thanks,
LixinHi Lixin,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
From your description, I understand that you are handling some blocking or
deadlock issues. For monitoring the blocking issue and save it to a file,
you can refer to the following article to use the stored procedure to
collect the blocking information and use Osql utility to output and save
the information to a file:
271509 INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
For more information, these articles may be also helpful:
283725 INF: How to View SQL Server 2000 Blocking Data
http://support.microsoft.com/?id=283725
283696 INF: Job to Monitor SQL Server 2000 Performance and Activity
http://support.microsoft.com/?id=283696
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||The recommened articles are very helpful. Thanks a lot,
Lixin
""Billy Yao [MSFT]"" <v-binyao@.online.microsoft.com> wrote in message
news:%23aCiUKxuDHA.2520@.cpmsftngxa07.phx.gbl...
> Hi Lixin,
> Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
> your issue.
> From your description, I understand that you are handling some blocking or
> deadlock issues. For monitoring the blocking issue and save it to a file,
> you can refer to the following article to use the stored procedure to
> collect the blocking information and use Osql utility to output and save
> the information to a file:
> 271509 INF: How to Monitor SQL Server 2000 Blocking
> http://support.microsoft.com/?id=271509
> For more information, these articles may be also helpful:
> 283725 INF: How to View SQL Server 2000 Blocking Data
> http://support.microsoft.com/?id=283725
> 283696 INF: Job to Monitor SQL Server 2000 Performance and Activity
> http://support.microsoft.com/?id=283696
>
> Best regards,
> Billy Yao
> Microsoft Online Support
> ----
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only. Thanks.
>

Program to monitor Sql server service

Can anyone help me with a program that can monitor my sql
server agent that will return an alert if the service
stops. I am using netiq and it only monitors sql server
service.H
If you have NetIQ'a Diagnostic Mangager it will notify you if the status of the SQLAgent changes (i.e. if it stops)
Otherwise, there are lots of little utilities that you can download for free to monitor a Windows service and notify you if it goes down. (for e.g. watchdog)

program to insert the csv file to SQL server

Dear professional
I am writing a program to insert the csv file to SQL server.
However, the data source contains different charater like that:
ABC-001
ABC-002
BDE_001
BDE_002
have there is any SQL to convert the data as below:
ABC-001-->ABC001
ABC-002-->ABC002
BDE_001-->BDE001
BDE_002-->BDE002
ThanksBasically you need to concatenate. like
left(fieldname, 3) + right(fieldname,3)
Or you can use substring also
Amarnath
"Anton" wrote:
> Dear professional
> I am writing a program to insert the csv file to SQL server.
> However, the data source contains different charater like that:
> ABC-001
> ABC-002
> BDE_001
> BDE_002
> have there is any SQL to convert the data as below:
> ABC-001-->ABC001
> ABC-002-->ABC002
> BDE_001-->BDE001
> BDE_002-->BDE002
> Thanks
>
>|||"Amarnath" wrote:
> Basically you need to concatenate. like
> left(fieldname, 3) + right(fieldname,3)
> Or you can use substring also
> Amarnath
> "Anton" wrote:
> > Dear professional
> >
> > I am writing a program to insert the csv file to SQL server.
> > However, the data source contains different charater like that:
> >
> > ABC-001
> > ABC-002
> > BDE_001
> > BDE_002
> >
> > have there is any SQL to convert the data as below:
> >
> > ABC-001-->ABC001
> > ABC-002-->ABC002
> > BDE_001-->BDE001
> > BDE_002-->BDE002
> >
> > Thanks
> >
> >
> >
> >|||YOu can use Replace function. Replace '-' with ''

Program Speed

I was rushed into putting live a new program for our datawarehouse load last
night without adequate testing.
The program took over 3 hours to run in approx 44000 records last night as
opposed to about 1 hour normally. The program uses a loop.
This is as a direct result of a change we put in for Vehicle Make Model code
s.
The program previously only ever ran into an Add vehicle Id program for non
existing vehicle_id's but due to the restructuring of the make model setup i
t
needed to run into this program for each record in the loop.
Due to no testing on our parrallel box, I could not evaluate the time impact
on the program.
I wonder could we talk about any methods I could use to speed up the process
.
It goes a little something like this...
-- IF @.vehicle_id IS NULL or @.vehicle_id = 0
EXECUTE usp_dim_vh_add_id
@.parms in @.vh_make_model, @.vh_group etc...
@.vehicle_id out
-- usp_dim_vh_add_id
SELECT @.make_id = a.Vh_Make_id, @.model_id = Vh_Model_id
FROM DB1..vht_lu_Make a, DB1..vht_lu_Model b
WHERE a.Vh_Make_Id = b.Vh_Make_Id
AND b.Vh_Make_Model_id = @.vh_make_model
UPDATE VHt_lu_Vehicle
SET Vh_make_model_code = @.vh_make_model,
Vh_make_id = @.make_id, Vh_model_id = @.model_id, WHERE vehicle_id =
@.vehicle_id
....
Any input here greatly appreciated.If any of the _id or _code are varchar, and the original value was NULL,
then updating the value to something not NULL may increase the length of the
row in such a way that it results in chaning the rows position in the page
or perhaps even page splitting. This results in more database I/O and
transaction logging.
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:0DF8E237-32BD-4D2C-9D49-74092F1613CD@.microsoft.com...
>I was rushed into putting live a new program for our datawarehouse load
>last
> night without adequate testing.
> The program took over 3 hours to run in approx 44000 records last night as
> opposed to about 1 hour normally. The program uses a loop.
> This is as a direct result of a change we put in for Vehicle Make Model
> codes.
> The program previously only ever ran into an Add vehicle Id program for
> non
> existing vehicle_id's but due to the restructuring of the make model setup
> it
> needed to run into this program for each record in the loop.
> Due to no testing on our parrallel box, I could not evaluate the time
> impact
> on the program.
> I wonder could we talk about any methods I could use to speed up the
> process.
> It goes a little something like this...
> -- IF @.vehicle_id IS NULL or @.vehicle_id = 0
> EXECUTE usp_dim_vh_add_id
> @.parms in @.vh_make_model, @.vh_group etc...
> @.vehicle_id out
> -- usp_dim_vh_add_id
> SELECT @.make_id = a.Vh_Make_id, @.model_id = Vh_Model_id
> FROM DB1..vht_lu_Make a, DB1..vht_lu_Model b
> WHERE a.Vh_Make_Id = b.Vh_Make_Id
> AND b.Vh_Make_Model_id = @.vh_make_model
> UPDATE VHt_lu_Vehicle
> SET Vh_make_model_code = @.vh_make_model,
> Vh_make_id = @.make_id, Vh_model_id = @.model_id, WHERE vehicle_id =
> @.vehicle_id
> ....
> Any input here greatly appreciated.
>
>|||examnotes <marcmc@.discussions.microsoft.com> wrote in
news:0DF8E237-32BD-4D2C-9D49-74092F1613CD@.microsoft.com:

> I was rushed into putting live a new program for our datawarehouse
> load last night without adequate testing.
> The program took over 3 hours to run in approx 44000 records last
> night as opposed to about 1 hour normally. The program uses a loop.
> This is as a direct result of a change we put in for Vehicle Make
> Model codes.
> The program previously only ever ran into an Add vehicle Id program
> for non existing vehicle_id's but due to the restructuring of the make
> model setup it needed to run into this program for each record in the
> loop.
> Due to no testing on our parrallel box, I could not evaluate the time
> impact on the program.
You could try tuning the sql statements. As datasets get better the
performance of a SQL and even the execution plan can change. So you'll need
to modify the SQL to increase the speed. If you want a tool that can do
this for you automatically, look at SQL Optimizer for Visual Studio. You
can download it from
http://www.extensibles.com/modules...=Products&op=NS

> I wonder could we talk about any methods I could use to speed up the
> process.
> It goes a little something like this...
> Any input here greatly appreciated.
>
>
The Relentless One
Debugging is a state of mind
http://www.extensibles.com/|||Not trying to pick this apart, but 44,000 records in an hour sounds
like an incredibly long time; 3 hours sounds like an eternity. Our
data warehouse processes 40,000 rows in about 2 minutes, and it's not a
massive server; I'm curious as to why it takes so long for you as well.
This is the first data warehouse I've designed; maybe I'm missing
something <G>
Just based off the sample code you sent, I'm curious as to why you are
doing row-at-a-time updates; can you not batch this?
DDL would help us give better hints.
Stu

program return blank set

Hi NGs,
I have a program connecting to SQL Server as the back-
end, recently, our company change to use FDDI instead of
128K network line; this causes the program return blank
Set from the SQL Server. We don't modify the program, the
only change is the networking situation. From 128k line
to FDDI; the program worked fine for about 1 month before
we made the change; FDDI also works great except this
program. Do you have any idea?
TIA.Are you able to make a connection to the SQL Server outside of the
application using tools SQL Query Analyser or OSQL.
HTH
Ryan Waight, MCDBA, MCSE
"Matt" <spam@.spam.com> wrote in message
news:079801c3ae80$22a1ecb0$a301280a@.phx.gbl...
> Hi NGs,
> I have a program connecting to SQL Server as the back-
> end, recently, our company change to use FDDI instead of
> 128K network line; this causes the program return blank
> Set from the SQL Server. We don't modify the program, the
> only change is the networking situation. From 128k line
> to FDDI; the program worked fine for about 1 month before
> we made the change; FDDI also works great except this
> program. Do you have any idea?
> TIA.
>
>
>|||thanks, currently I don't know, I will try it, if it
connects successfully, how do I process in next step?
>--Original Message--
>Are you able to make a connection to the SQL Server
outside of the
>application using tools SQL Query Analyser or OSQL.
>
>--
>HTH
>Ryan Waight, MCDBA, MCSE
>"Matt" <spam@.spam.com> wrote in message
>news:079801c3ae80$22a1ecb0$a301280a@.phx.gbl...
>> Hi NGs,
>> I have a program connecting to SQL Server as the back-
>> end, recently, our company change to use FDDI instead
of
>> 128K network line; this causes the program return blank
>> Set from the SQL Server. We don't modify the program,
the
>> only change is the networking situation. From 128k line
>> to FDDI; the program worked fine for about 1 month
before
>> we made the change; FDDI also works great except this
>> program. Do you have any idea?
>> TIA.
>>
>>
>>
>
>.
>|||Hi Matt,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
I am a little confused about the statement "blank set" you mentioned, it
will be more helpful if you could provide more specific description about
it. I assume that your application cannot get any data from the SQL Server
through the network of your company that used to work well. From the
information you provided, the only change that happened in you application
environment is the FDDI network took place of the formal 128K network,
right? If I have misunderstood, please feel free to let me know.
From my experience, you should confirm the connectivity of your computer
and the remote SQL Server first. You'd better use the Query Analyzer on
your computer and execute a simple query statement to see if the Query
Analyzer could retrieve any data from the remote SQL Server. This will be
helpful to confirm that both the remote SQL Server and the communication
between you computer and the remote SQL Server are working well.
Please let me know if the Query Analyzer could get data from the remote SQL
Server and I will provide further support based on the information you
provided. Looking forward to your response
Sincerely Yours
Baisong, Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
--
| Content-Class: urn:content-classes:message
| From: "Matt" <spam@.spam.com>
| Sender: "Matt" <spam@.spam.com>
| Subject: program return blank set
| Date: Wed, 19 Nov 2003 01:33:05 -0800
| Lines: 18
| Message-ID: <079801c3ae80$22a1ecb0$a301280a@.phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcOugCKhWxDbVERgQq6h62CYlPL2YA==| Newsgroups: microsoft.public.sqlserver.server
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:317195
| NNTP-Posting-Host: tk2msftngxa11.phx.gbl 10.40.1.163
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Hi NGs,
|
| I have a program connecting to SQL Server as the back-
| end, recently, our company change to use FDDI instead of
| 128K network line; this causes the program return blank
| Set from the SQL Server. We don't modify the program, the
| only change is the networking situation. From 128k line
| to FDDI; the program worked fine for about 1 month before
| we made the change; FDDI also works great except this
| program. Do you have any idea?
|
| TIA.
|
|
|
|
|
|
|

Program icons in "Start menu"

Hello,

I′ve made a mistake on my part. I have deleted my complete program icons for "Microsoft SQL Server" in my start menu.

Is there a simple way to just renew all the icons in my start menu without reinstalling the complete sql server.

Thank you very much for any tips.

Hi,

do you have a colleague with SQL Server installed? You might copy his structure of the SQL Server start menu over to your machine and check if the pathes of the links are correct.

Otherwise you will have to browse the Microsoft SQL Server directory for the executables and create short cuts in the start menu manually.

You might also try if a repair of your installation brings the icons back.

--
SvenC

Program from executing Procedure

I liked of susbistution the QueryAnalyser by other program, somebody know ??:confused:I have just recently downloaded WinSQL (http://www.indus-soft.com/winsql) which lets you connect to any ODBC-datasource and execute sql towards it... the lite-version is free and it works like a bliss

Program for scripting data

There are free program for scripting data in sql tables?
Thankscheccouno wrote:
> There are free program for scripting data in sql tables?
>
I'm not sure I understand what you are asking for, but maybe these will
help?
http://www.aspfaq.com/etiquette.asp?id=5006
and particularly this:
http://vyaskn.tripod.com/code.htm#inserts
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

program flow question

I am trying to figure out how to make a report stop at a certain value. For example say I have five categories that I can assign an item. I would like to be able to have the report pull just the first category that applies to the item. For instance I have a bike that falls under categories 1 and 3 and a car the falls under 2, 3, and 5. How can I make it so the report will only show the details that apply to the first category the item falls under. Thanks.

So do you have a category field coming in the dataset agianst your items, and user is gonna select one category or may be more than one.

If i got it correct this can be done by using filter the dataset for that parameter may be using where clause or may be using dataset filter or even at table filter.

|||Actually I was able to solve this problem. I changed the view I was pulling from so the categories would be assigned numbers. Then I declare a varable and set it to Min(category) with the same from and where clauses. Then I just add to the where clause to have the catergory equal the varable set to min.

Program Connection Problem

Hi there,
I really hope that someone recognises this problem and can point me in the
direction of a solution.
We have SQL Server 2000 running on a server at our head office. We have a
program that runs in other sites that connect to the server via a VPN
connection HO. The program has been running perfectly for about a year now
without any problems. A few weeks ago, one machine in one of our sites
stopped opening a connection and has refused to open one ever since. We
hadn't had any problems anywhere else so we swapped the machine with a newer
one and thought nothing else about it. However, a couple of days ago another
machine in another site did the same thing. It simply refuses to open a
connection to the server. The error it throws is "SQL Server does not exist
or access denied". This strikes me as strange because he machine can ping
the sql server happily and it has no problems with the rest of its network
connectivity and the connection string uses the server admin user and
password. I've tried changing the machine IP address and HostName but that
has had no effect. The machine is running Windows 98 SE.
Now, I am pretty sure that there is nothing wrong with the program because
it has been running perfectly for ages and hasn't changed for nearly a year.
The program is running on about 20 identical 98 boxes in 15 sites. The first
machine to go was probably the busiest user of the program and this second
one is probably next.
So I'm thinking that this must be a SQL issue but we really don't have
anyone that knows that much about it. What I do know is that, in general,
the server is running ok. The program in question is probably the smallest
data-wise and has very little traffic, we have other much more complex
databases and programs running 24/7 on the server and they are all ok.
So, I guess the leading question is - does anyone have any ideas? Is there
some kind of connection log that has maxed out or something like that?
Any pointers would be fantastic.
Cheers
C-Ya Aly
Do client tools like UDL, osql and isql work from this problem machine? Can
this problem client machine ping any other sql servers in your network?
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Program Connection Problem

Hi there,
I really hope that someone recognises this problem and can point me in the
direction of a solution.
We have SQL Server 2000 running on a server at our head office. We have a
program that runs in other sites that connect to the server via a VPN
connection HO. The program has been running perfectly for about a year now
without any problems. A few weeks ago, one machine in one of our sites
stopped opening a connection and has refused to open one ever since. We
hadn't had any problems anywhere else so we swapped the machine with a newer
one and thought nothing else about it. However, a couple of days ago another
machine in another site did the same thing. It simply refuses to open a
connection to the server. The error it throws is "SQL Server does not exist
or access denied". This strikes me as strange because he machine can ping
the sql server happily and it has no problems with the rest of its network
connectivity and the connection string uses the server admin user and
password. I've tried changing the machine IP address and HostName but that
has had no effect. The machine is running Windows 98 SE.
Now, I am pretty sure that there is nothing wrong with the program because
it has been running perfectly for ages and hasn't changed for nearly a year.
The program is running on about 20 identical 98 boxes in 15 sites. The first
machine to go was probably the busiest user of the program and this second
one is probably next.
So I'm thinking that this must be a SQL issue but we really don't have
anyone that knows that much about it. What I do know is that, in general,
the server is running ok. The program in question is probably the smallest
data-wise and has very little traffic, we have other much more complex
databases and programs running 24/7 on the server and they are all ok.
So, I guess the leading question is - does anyone have any ideas? Is there
some kind of connection log that has maxed out or something like that?
Any pointers would be fantastic.
Cheers
C-Ya AlyDo client tools like UDL, osql and isql work from this problem machine? Can
this problem client machine ping any other sql servers in your network?
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.

Program Connection Problem

Hi there,
I really hope that someone recognises this problem and can point me in the
direction of a solution.
We have SQL Server 2000 running on a server at our head office. We have a
program that runs in other sites that connect to the server via a VPN
connection HO. The program has been running perfectly for about a year now
without any problems. A few weeks ago, one machine in one of our sites
stopped opening a connection and has refused to open one ever since. We
hadn't had any problems anywhere else so we swapped the machine with a newer
one and thought nothing else about it. However, a couple of days ago another
machine in another site did the same thing. It simply refuses to open a
connection to the server. The error it throws is "SQL Server does not exist
or access denied". This strikes me as strange because he machine can ping
the sql server happily and it has no problems with the rest of its network
connectivity and the connection string uses the server admin user and
password. I've tried changing the machine IP address and HostName but that
has had no effect. The machine is running Windows 98 SE.
Now, I am pretty sure that there is nothing wrong with the program because
it has been running perfectly for ages and hasn't changed for nearly a year.
The program is running on about 20 identical 98 boxes in 15 sites. The first
machine to go was probably the busiest user of the program and this second
one is probably next.
So I'm thinking that this must be a SQL issue but we really don't have
anyone that knows that much about it. What I do know is that, in general,
the server is running ok. The program in question is probably the smallest
data-wise and has very little traffic, we have other much more complex
databases and programs running 24/7 on the server and they are all ok.
So, I guess the leading question is - does anyone have any ideas? Is there
some kind of connection log that has maxed out or something like that?
Any pointers would be fantastic.
Cheers
C-Ya AlyDo client tools like UDL, osql and isql work from this problem machine? Can
this problem client machine ping any other sql servers in your network?
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.