reading a text file as it is written

A remote office developed a PC-based app that writes its data to a text
file. We want to maintain this data at the main office, and in SQL Server,
due to the significance of the data. A typical day's file may have a couple
hundred records, written over an eight hour shift. Rather than changing the
application to connect to our SQL Server, and worrying about network
connectivity and impact on productivity, is there a reasonable means to have
SQL Server detect when this text file is updated, and add the most current
record(s) to our SQL Server table? Please also reply to my email address,
gregstigers@.spamcop.net. Thanks.
Greg Stigers, MCSA
remember to vote for the answers you like
NT has file change notifications. You can write some C# to receive these
notifications and then kick off the script which updates the database.
Check out System.IO.FileSystemWatcher
(http://msdn.microsoft.com/library/de...classtopic.asp)
However, reading the file while it is still open and being written to is
tricky... it actually depends on how the application doing the writing
opened the file. It can specify whether it wants to allow people to read it
while it is being written. If it said that it doesn't want to share with
anybody, then there isn't much you can do other than change that
application.
John Gallardo
SQL Server Engine
Microsoft Corp
[This posting is provided "AS IS" with no warranties, and confers no
rights.]
"Greg Stigers, MCSA" <gregstigers+wmsn@.spamcop.net> wrote in message
news:Ot6Ibr0$EHA.608@.TK2MSFTNGP15.phx.gbl...
>A remote office developed a PC-based app that writes its data to a text
>file. We want to maintain this data at the main office, and in SQL Server,
>due to the significance of the data. A typical day's file may have a couple
>hundred records, written over an eight hour shift. Rather than changing the
>application to connect to our SQL Server, and worrying about network
>connectivity and impact on productivity, is there a reasonable means to
>have SQL Server detect when this text file is updated, and add the most
>current record(s) to our SQL Server table? Please also reply to my email
>address, gregstigers@.spamcop.net. Thanks.
> --
> Greg Stigers, MCSA
> remember to vote for the answers you like
>

reading a text file as it is written

A remote office developed a PC-based app that writes its data to a text
file. We want to maintain this data at the main office, and in SQL Server,
due to the significance of the data. A typical day's file may have a couple
hundred records, written over an eight hour shift. Rather than changing the
application to connect to our SQL Server, and worrying about network
connectivity and impact on productivity, is there a reasonable means to have
SQL Server detect when this text file is updated, and add the most current
record(s) to our SQL Server table? Please also reply to my email address,
gregstigers@.spamcop.net. Thanks.
--
Greg Stigers, MCSA
remember to vote for the answers you likeNT has file change notifications. You can write some C# to receive these
notifications and then kick off the script which updates the database.
Check out System.IO.FileSystemWatcher
(http://msdn.microsoft.com/library/d...rclasstopic.asp)
However, reading the file while it is still open and being written to is
tricky... it actually depends on how the application doing the writing
opened the file. It can specify whether it wants to allow people to read it
while it is being written. If it said that it doesn't want to share with
anybody, then there isn't much you can do other than change that
application.
John Gallardo
SQL Server Engine
Microsoft Corp
[This posting is provided "AS IS" with no warranties, and confers no
rights.]
"Greg Stigers, MCSA" <gregstigers+wmsn@.spamcop.net> wrote in message
news:Ot6Ibr0$EHA.608@.TK2MSFTNGP15.phx.gbl...
>A remote office developed a PC-based app that writes its data to a text
>file. We want to maintain this data at the main office, and in SQL Server,
>due to the significance of the data. A typical day's file may have a couple
>hundred records, written over an eight hour shift. Rather than changing the
>application to connect to our SQL Server, and worrying about network
>connectivity and impact on productivity, is there a reasonable means to
>have SQL Server detect when this text file is updated, and add the most
>current record(s) to our SQL Server table? Please also reply to my email
>address, gregstigers@.spamcop.net. Thanks.
> --
> Greg Stigers, MCSA
> remember to vote for the answers you like
>sql

Read a remote web file and parse it

As subject, I need to read a remote web file, csv or xml isn't important,
from a t-sql sp scheduled in a job.
I don't know how to write the remote read steps. Any helps?
AndreaHi
Can you FTP the file? In which case you could use the DTS "File Transfer
Protocol" task to do this although if you used FTP.EXE from the "Execute
Process" or a xp_cmdshell call in a "Execute SQL" task" you may have more
control.
John
Andrea Moro" <moroandrea@.tiscali.it> wrote in message
news:4274c427$0$20669$5fc30a8@.news.tiscali.it...
> As subject, I need to read a remote web file, csv or xml isn't important,
> from a t-sql sp scheduled in a job.
> I don't know how to write the remote read steps. Any helps?
> Andrea
>

RDO Connection through VB

Hi,

I am using VB to connect to the database and I am using RDO (Remote Data Objects) for this connectivity.

For security reasons, I am using the userid and password retrieved from a different database to establish the connection to the production server. The password we retrieve is encrypted and the connection program decrypts that and connects.

All is fine so far. But after I connect to the database, in the VB project, in the debug mode, I can see the connection string by accessing the property RdoConnection.Connect.

Is there a way, by which, I could establish the rdoconnection and immediately after that, change the 'Connect' property of that to something else, so that it does not have the actual connection information?

Thanks in advance
RajI don't think so. When you change the connect property, I think you will lose the connection.

You could make the class that holds the RDO object private so that it is not remotely accessible. You should also make the RDO object private to the class, and provide write-only access to the connection string by creating a Let property with no Get property.

rda.pull exception

Hi,

I have followed the technical artical "Remote Data Access Synchronization with SQL Server 2005 Compact Edition and Visual Basic.NET" to create a sample application.

But when I run debug mode and get an exception unexpected as following as doing rad.pull :

Immediate Window:

A first chance exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll

Message Box

[Contacts]

Code:

Private Sub RADPullButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RADPullButton.Click

Try
VerifyDatabaseExists()
Dim rda As SqlCeRemoteDataAccess
rda = GetRDAObject()
rda.Pull("Contacts", _
"SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers", _
My.Settings.ServerOleDBNorthwindConnectionString, _
RdaTrackOption.TrackingOnWithIndexes, _
"Contacts_Errors")

Catch ex As Exception
MessageBox.Show(ex.ToString())
Finally
Windows.Forms.Cursor.Current = Cursors.Default
End Try

End Sub

Thanks in advance,

JD

You need to grab all the error information, by following the guidelines in this topic from SQL CE Books online - Handling Errors in Managed Applications http://msdn2.microsoft.com/en-us/library/ms174079.aspx

|||

HI ErikEJ,

I have following the link to catch real sql server compact edition error exception

Here is what I got and I still don't know what this meas:

Error Code:80004005
Message :[Contacts]
Minor Err: 28573
Source: Microsoft SQL Server Compact Edition
Err. Par.: Contacts

Thanks a lot,

JD

|||

There already is a local table with the same name as used in your RDA definition. See this very helpful post by laxmi at http://blogs.msdn.com/sqlservercompact/archive/2007/01/23/rda-subscriptions.aspx

|||

I am having a similar problem. When I pull the tables, I also create an error table (tablename_err). If I have to pull a table again, I first drop the table if it exists and then attempt to pull it again. I cannot do this though because when I drop the table, the error table does not get dropped and it will not let me pull a another error table (error 28573). I have attempted to explicitly drop the error table, but I am not allowed to do that either as that generates the 28605 error: "DDL operations on the table are restricted." I did not have this problem with SQLCE 2.0, only since I migrated to SQLMobile (3.0). How do I get rid of the original error tables so that I can pull again?

|||

One doesn't has to drop the error tables explicitly. These will be dropped automatically when the associated RDA tracked table is dropped. For e.g.:

rda.pull(@."pulltable", @."select * from foo", <conn string to SQL server>, RDATrackOption.TrackingOn, @."err_pull");

when one drops pulltable, err_pull automatically gets dropped since it is associated with pulltable which is being tracked. Please refer to "Maintaining Error Tables" in http://msdn2.microsoft.com/en-us/library/ms171782.aspx

Could you verify if you are indeed dropping all the local data tables into which you are attempting to pull data?

rda.pull exception

Hi,

I have followed the technical artical "Remote Data Access Synchronization with SQL Server 2005 Compact Edition and Visual Basic.NET" to create a sample application.

But when I run debug mode and get an exception unexpected as following as doing rad.pull :

Immediate Window:

A first chance exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll

Message Box

[Contacts]

Code:

Private Sub RADPullButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RADPullButton.Click

Try
VerifyDatabaseExists()
Dim rda As SqlCeRemoteDataAccess
rda = GetRDAObject()
rda.Pull("Contacts", _
"SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers", _
My.Settings.ServerOleDBNorthwindConnectionString, _
RdaTrackOption.TrackingOnWithIndexes, _
"Contacts_Errors")

Catch ex As Exception
MessageBox.Show(ex.ToString())
Finally
Windows.Forms.Cursor.Current = Cursors.Default
End Try

End Sub

Thanks in advance,

JD

You need to grab all the error information, by following the guidelines in this topic from SQL CE Books online - Handling Errors in Managed Applications http://msdn2.microsoft.com/en-us/library/ms174079.aspx

|||

HI ErikEJ,

I have following the link to catch real sql server compact edition error exception

Here is what I got and I still don't know what this meas:

Error Code:80004005
Message :[Contacts]
Minor Err: 28573
Source: Microsoft SQL Server Compact Edition
Err. Par.: Contacts

Thanks a lot,

JD

|||

There already is a local table with the same name as used in your RDA definition. See this very helpful post by laxmi at http://blogs.msdn.com/sqlservercompact/archive/2007/01/23/rda-subscriptions.aspx

|||

I am having a similar problem. When I pull the tables, I also create an error table (tablename_err). If I have to pull a table again, I first drop the table if it exists and then attempt to pull it again. I cannot do this though because when I drop the table, the error table does not get dropped and it will not let me pull a another error table (error 28573). I have attempted to explicitly drop the error table, but I am not allowed to do that either as that generates the 28605 error: "DDL operations on the table are restricted." I did not have this problem with SQLCE 2.0, only since I migrated to SQLMobile (3.0). How do I get rid of the original error tables so that I can pull again?

|||

One doesn't has to drop the error tables explicitly. These will be dropped automatically when the associated RDA tracked table is dropped. For e.g.:

rda.pull(@."pulltable", @."select * from foo", <conn string to SQL server>, RDATrackOption.TrackingOn, @."err_pull");

when one drops pulltable, err_pull automatically gets dropped since it is associated with pulltable which is being tracked. Please refer to "Maintaining Error Tables" in http://msdn2.microsoft.com/en-us/library/ms171782.aspx

Could you verify if you are indeed dropping all the local data tables into which you are attempting to pull data?

rda.pull exception

Hi,

I have followed the technical artical "Remote Data Access Synchronization with SQL Server 2005 Compact Edition and Visual Basic.NET" to create a sample application.

But when I run debug mode and get an exception unexpected as following as doing rad.pull :

Immediate Window:

A first chance exception of type 'System.Data.SqlServerCe.SqlCeException' occurred in System.Data.SqlServerCe.dll

Message Box

[Contacts]

Code:

Private Sub RADPullButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles RADPullButton.Click

Try
VerifyDatabaseExists()
Dim rda As SqlCeRemoteDataAccess
rda = GetRDAObject()
rda.Pull("Contacts", _
"SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax FROM Customers", _
My.Settings.ServerOleDBNorthwindConnectionString, _
RdaTrackOption.TrackingOnWithIndexes, _
"Contacts_Errors")

Catch ex As Exception
MessageBox.Show(ex.ToString())
Finally
Windows.Forms.Cursor.Current = Cursors.Default
End Try

End Sub

Thanks in advance,

JD

You need to grab all the error information, by following the guidelines in this topic from SQL CE Books online - Handling Errors in Managed Applications http://msdn2.microsoft.com/en-us/library/ms174079.aspx

|||

HI ErikEJ,

I have following the link to catch real sql server compact edition error exception

Here is what I got and I still don't know what this meas:

Error Code:80004005
Message :[Contacts]
Minor Err: 28573
Source: Microsoft SQL Server Compact Edition
Err. Par.: Contacts

Thanks a lot,

JD

|||

There already is a local table with the same name as used in your RDA definition. See this very helpful post by laxmi at http://blogs.msdn.com/sqlservercompact/archive/2007/01/23/rda-subscriptions.aspx

|||

I am having a similar problem. When I pull the tables, I also create an error table (tablename_err). If I have to pull a table again, I first drop the table if it exists and then attempt to pull it again. I cannot do this though because when I drop the table, the error table does not get dropped and it will not let me pull a another error table (error 28573). I have attempted to explicitly drop the error table, but I am not allowed to do that either as that generates the 28605 error: "DDL operations on the table are restricted." I did not have this problem with SQLCE 2.0, only since I migrated to SQLMobile (3.0). How do I get rid of the original error tables so that I can pull again?

|||

One doesn't has to drop the error tables explicitly. These will be dropped automatically when the associated RDA tracked table is dropped. For e.g.:

rda.pull(@."pulltable", @."select * from foo", <conn string to SQL server>, RDATrackOption.TrackingOn, @."err_pull");

when one drops pulltable, err_pull automatically gets dropped since it is associated with pulltable which is being tracked. Please refer to "Maintaining Error Tables" in http://msdn2.microsoft.com/en-us/library/ms171782.aspx

Could you verify if you are indeed dropping all the local data tables into which you are attempting to pull data?

RAS error no 756

RAS error no 756

If i m trying to connect my remote server from the dos prompt
by giving the foll syntax ie

rasdial <connection> <user> <password>

its getting connected to my remote server.
but i m facing a problem while i m giving the syntax in qa

xp_cmdshell 'rasdial <connection> <user> <password>'

error detail i m getting is

'Remote Access error 756 - This connection is already being dialed.'
even though there is no connection existing.

could u pl let me know what could be the reason.

On the other side
If i m trying to disconnect after a manual connect from the query analyzer
its working fine ie

xp_cmdshell 'connectionname/disconnect'

immidiatly after this , if i m trying to connect through qa i m getting
the same error no ie 756.
even though there is no connection existing.

is there any command or syntax to know through qa
if there is any existing connection disconnect (RAS Connection) ?In your place, I would be inclined to modularize the approach more (at least to identify your issues, as RAS has been changed between different OS and service packs).

In other words, rather than:

xp_cmdshell 'rasdial <connection> <user> <password>'
xp_cmdshell 'connectionname/disconnect'

Implement something like:

xp_cmdshell 'c:\MyConnectBatchFile.cmd'
xp_cmdshell 'c:\MyDisconnectBatchFile.cmd'

That should allow you to manipulate your OS commands separately. Once you have the OS components operating as desired for your requirements, you can look at imbedding everything directly into the xp_cmdshell execution string again.|||Thanx for the mail.

MyConnectBatchFile.cmd how to use this command can u pl explain me better.

I m using win-2k server.

xp_cmdshell 'c:\MyConnectBatchFile.cmd'
xp_cmdshell 'c:\MyDisconnectBatchFile.cmd'|||Originally posted by ranjan
Thanx for the mail.

Q1 MyConnectBatchFile.cmd how to use this command can u pl explain me better.

I m using win-2k server.

xp_cmdshell 'c:\MyConnectBatchFile.cmd'
xp_cmdshell 'c:\MyDisconnectBatchFile.cmd'

A1 Yes, I think so (see below, later).

Use Notepad to create a new text file. Copy and paste (or retype) everything normally typed into a "DOSprompt command line window" into the text file. Rename the file something like 'c:\MyConnectBatchFile.cmd'.

Verify that the MyConnectBatchFile.cmd functions correctly (as you expect) by running it interactively (double clicking on it).

Note: A pause or other interactive dos command left in a .cmd file (for testing, etc.) will cause exec xp_cmdshell 'c:\MyConnectBatchFile.cmd' to appear to 'hang'. It will never get input to continue, and the process will then have to be killed (in taskmanager)!!

Once MyConnectBatchFile.cmd functions as expected interactively; call it from QA, using xp_cmdshell. For example:
exec xp_cmdshell 'c:\MyConnectBatchFile.cmd'