Any way to obtain changes in tables?

Hi
I'm Using Merge Replication to replicate info from a Pocket PC app. This
info must be integrated with legacy systems in FOXPRO. Is there a way to
obtain changes (new rows and updated rows) in tables to pass it by textfile
to these legacy systems?
For now, i'm experimenting with a status Column in each table. When
inserting or updating in SQLCE i update this column to 0. Once i replicate I
update it to 1(in the pocket). In the server, a memory resident program is
running every two minutes checking for new records (status=0) and exporting
them to a textfile. This, even when functional, i don't think is the best way
to do it. So I ask you if this can be done by a native way in SQL.
Thank you
Regards!
Omar Rojas
It has been a little while since I've used Merge Replication from a CE-based
device, and I'm a little vague on the restrictions but:
Although ugly and possibly undesirable, a trigger on each table to be
tracked could be used to "catch" changes. it could possible update a
"control" table of table name and key values to be processed later by a
formatter that writes the text file, or other, really quick and simple
processing.
You could get trickier and delve into the replication metadata to capture
this information and avoid the triggers, but that requires a relatively
in-depth understanding of the internals of replication.
Cheers.
"Omar Rojas" wrote:

> Hi
> I'm Using Merge Replication to replicate info from a Pocket PC app. This
> info must be integrated with legacy systems in FOXPRO. Is there a way to
> obtain changes (new rows and updated rows) in tables to pass it by textfile
> to these legacy systems?
> For now, i'm experimenting with a status Column in each table. When
> inserting or updating in SQLCE i update this column to 0. Once i replicate I
> update it to 1(in the pocket). In the server, a memory resident program is
> running every two minutes checking for new records (status=0) and exporting
> them to a textfile. This, even when functional, i don't think is the best way
> to do it. So I ask you if this can be done by a native way in SQL.
> Thank you
> Regards!
> Omar Rojas

Any way to force collation/sort-order as part of the ODBC connection?

In the situation where the ODBC client app assumes a specific collation or, more specifically, a specific sort-order (case-sensitive vs. case-insensitive) but the collation/sort-order of the SQL Server Database and/or Table is unknown, is there a way to coerce the ODBC connection to always use a case-sensitive/case-insensitive sort order?

I believe case-sensitivity is a characteristic of the database you're connecting to. I don't know of a client setting for this.|||Collation is specified during server installation or db/table creation time. I don't believe you can change it on the fly via ODBC API.|||

Thanks Warren and Ricky. I suspected as much, just wasn't sure.

Any way to force collation/sort-order as part of the ODBC connection?

In the situation where the ODBC client app assumes a specific collation or, more specifically, a specific sort-order (case-sensitive vs. case-insensitive) but the collation/sort-order of the SQL Server Database and/or Table is unknown, is there a way to coerce the ODBC connection to always use a case-sensitive/case-insensitive sort order?

I believe case-sensitivity is a characteristic of the database you're connecting to. I don't know of a client setting for this.|||Collation is specified during server installation or db/table creation time. I don't believe you can change it on the fly via ODBC API.|||

Thanks Warren and Ricky. I suspected as much, just wasn't sure.

Any way to disable SQL logins after failed tries?

We're using a canned app that only uses SQL Server logins (it will NOT use Windows logins to access SQL Server)

SQL Server logins seem to lack even the most rudimentary security features such as expiring passwords and automatic disabling after a set number of failed logins. Bad. Bad Microsoft.

Has anyone figured out a way to graft this on after-the-fact?

I can do it in an awkward fashion by auditing failed logins and going back to read the error log, but this isn't real time by any stretch.To the best of my knowledge, there is no innate feature in MS SQL that will allow you manage SQL Server logins as you wish. This lack has been noted before (cross your fingers, MS will deal with this in Yukon).

We wrote a custom app and created this kind of failed-login checking, but that does not sound like an option for you.

Can you do something with a scheduled job at the SQL error log (you'd have to enable logging of failed login attempts)?

I think there is a way to load the error log as a table. Then you could search for failed logins and (if the number exceeded your threshold) disable it.

I think it's doable, but I have not had the need to accomplish this particular task.

Regards,

hmscott|||Originally posted by hmscott
To the best of my knowledge, there is no innate feature in MS SQL that will allow you manage SQL Server logins as you wish. This lack has been noted before (cross your fingers, MS will deal with this in Yukon).

We wrote a custom app and created this kind of failed-login checking, but that does not sound like an option for you.

Can you do something with a scheduled job at the SQL error log (you'd have to enable logging of failed login attempts)?

I think there is a way to load the error log as a table. Then you could search for failed logins and (if the number exceeded your threshold) disable it.

I think it's doable, but I have not had the need to accomplish this particular task.

Regards,

hmscott

I doubt it...since SQL Server "security" is not the way to go...you can "See" passwords as plain as day...oh, I forgot...M$ describes this as a feature....

Any way around this error

I get the error (in red) below when I run this stored procedure (I'm not running in my app but in Query Analyzer) -- Please help me fix this

CREATE PROCEDURE dbo.sp_Employee_GetEmployeeLNameFNameEmpID
(
@.facilityID nvarchar(2),
@.companyID nvarchar(2),
@.deptID nvarchar(20),
@.Period int
)
AS
SELECT e.LastName + ',' + e.FirstName + ' - ' + e.EmployeeID AS ListBoxText, e.EmployeeID, e.LastName + ',' + e.FirstName AS FullName FROM Employee e
WHERE e.DeptID = @.deptID AND e.FacilityID = @.facilityID AND e.CompanyID = @.companyID AND e.EmployeeID <> (SELECT ev.EmployeeID FROM EmployeeEval ev
WHERE PeriodID= @.Period)
GO

Server: Msg 512, Level 16, State 1, Procedure sp_Employee_GetEmployeeLNameFNameEmpID, Line 9
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

WHICH employee do you want from the ev table?
It looks to me like you want a NOT IN instead:
SELECT
e.LastName + ','+ e.FirstName + ' - ' + e.EmployeeID AS ListBoxText,
e.EmployeeID,
e.LastName + ',' + e.FirstName AS FullName
FROM Employee e
WHEREe.DeptID = @.deptID
AND e.FacilityID = @.facilityID
AND e.CompanyID =@.companyID
AND e.EmployeeID NOT IN
(SELECT ev.EmployeeID
FROMEmployeeEval ev
WHERE PeriodID= @.Period)
|||

Sweet worked perfectly -- my book showed the IN part but I didn't know about the NOT IN.
Thanks a million.

Any video/other tutorials for ASP.Net and ReportViewer control?

I have figured out how to build a report for reportserver - I even have a
video that tells me how to use the reportviewer in a Windows app, but I need
to learn how to do the basics in an ASP.Net page, with the ReportViewer
control.
Are there any basic, step by step web pages that show this, or even better -
any videos to checkout on the web?On Jan 18, 1:02 pm, "Elmo Watson" <s...@.here.com> wrote:
> I have figured out how to build a report for reportserver - I even have a
> video that tells me how to use the reportviewer in a Windows app, but I need
> to learn how to do the basics in an ASP.Net page, with the ReportViewer
> control.
> Are there any basic, step by step web pages that show this, or even better -
> any videos to checkout on the web?
These articles might be helpful.
http://www.aspfree.com/c/a/ASP.NET/Binding-Data-to-the-ReportViewer-Control-Dynamically-in-ASPNET-20/
http://msdn2.microsoft.com/en-us/library/ms252104(VS.80).aspx
Regards,
Enrique Martinez
Sr. Software Consultant

Any report runs once and then I have to log back in to my app

We have an asp.net app that calls the report server for our reports. We are
running into an issue we don't even understand why it's happening let alone
fixing it...
You can only run one report and then you have to logout of the app and log
back in to run another report. This is with any of our reports! They all
work but only the first report runs the first time. After that all of the
reports don't work. Logout and log back in and choose another report and it
works...?
Here's our code that is basically the same for every report...
dstDataSet = New DataSet
Dim strSPName As String = "spSRS_Appeal_TaxAgents"
'Passing the parameter to the SQL stored procedure
Dim aryParams(1) As SqlParameter
aryParams(0) = New SqlClient.SqlParameter("@.TaxYear", SqlDbType.Int)
aryParams(0).Value = CType(strTaxYear, Integer)
aryParams(1) = New SqlClient.SqlParameter("@.Cycle", SqlDbType.Int)
aryParams(1).Value = CType(strCycle, Integer)
Try
dstDataSet = SqlHelper.ExecuteDataset(cnnConn, strSPName,
aryParams)
Dim intCount As Integer = dstDataSet.Tables(0).Rows.Count
If intCount < 1 Then
lblInfo.Text = "Report records not found! Canceled operation."
Exit Sub
End If
Catch exn As Exception
lblInfo.Text = "Error. Failed to retrieve records! " & exn.Message
End Try
'Create the URL string to render this report from the SQL report
server
'SPParam is the parameter which is submitted to the SQL sotred
procedures
Dim str1, str2, str3, str4, strURL As String
str1 = "http://tennessee/reportserver?/astransc/AppealTaxAgents"
str2 = "&rs:Command=Render&rs:Format=PDF"
str3 = strTaxYear
str4 = strCycle
Dim strPath As New System.Text.StringBuilder
strPath.Append(str1)
strPath.Append(str2)
strPath.Append("&TaxYear=").Append(str3)
strPath.Append("&Cycle=").Append(str4)
strURL = strPath.ToString
Response.Redirect(strURL)
The report is in PDF format and when the report runs it pops up a form
asking if we want to open, save, or cancel the PDF file...
The programmer who wrote this doesn't have this issue on his machine.
However, on the test server this is occuring to us. In fact the programmer
asked for assistance because on his machine it on a rare occasion would not
create the PDF file but he couldn't find a cause for it. On our test server
it occurs like clock work. Log in, run one report (any of them) and then no
other report including the one you just called won't work until you log back
in....
In looking at the report server logs the subsequent requests are not even
making it to the report server? Any ideas? We were wondering if there was
something about the response.redirect? I can take the string created by
the code type it into a url address and it works every time?
Thanks,
KevinI have somewhat figured out what is occuring...
The viewstate is being clobbered when this is occuring (not entirely sure
how but suspect it is occuring because the redirect does not physically
change the page I am on, only creates a do you want to open, save, cancel
this PDF file dialog box). I can only assume that the response.redirect is
the cause now. I am going to figure out a way to accomplish this task.
Kevin
"Kevin" wrote:
> We have an asp.net app that calls the report server for our reports. We are
> running into an issue we don't even understand why it's happening let alone
> fixing it...
> You can only run one report and then you have to logout of the app and log
> back in to run another report. This is with any of our reports! They all
> work but only the first report runs the first time. After that all of the
> reports don't work. Logout and log back in and choose another report and it
> works...?
> Here's our code that is basically the same for every report...
> dstDataSet = New DataSet
> Dim strSPName As String = "spSRS_Appeal_TaxAgents"
> 'Passing the parameter to the SQL stored procedure
> Dim aryParams(1) As SqlParameter
> aryParams(0) = New SqlClient.SqlParameter("@.TaxYear", SqlDbType.Int)
> aryParams(0).Value = CType(strTaxYear, Integer)
> aryParams(1) = New SqlClient.SqlParameter("@.Cycle", SqlDbType.Int)
> aryParams(1).Value = CType(strCycle, Integer)
> Try
> dstDataSet = SqlHelper.ExecuteDataset(cnnConn, strSPName,
> aryParams)
> Dim intCount As Integer = dstDataSet.Tables(0).Rows.Count
> If intCount < 1 Then
> lblInfo.Text = "Report records not found! Canceled operation."
> Exit Sub
> End If
> Catch exn As Exception
> lblInfo.Text = "Error. Failed to retrieve records! " & exn.Message
> End Try
> 'Create the URL string to render this report from the SQL report
> server
> 'SPParam is the parameter which is submitted to the SQL sotred
> procedures
> Dim str1, str2, str3, str4, strURL As String
> str1 = "http://tennessee/reportserver?/astransc/AppealTaxAgents"
> str2 = "&rs:Command=Render&rs:Format=PDF"
> str3 = strTaxYear
> str4 = strCycle
> Dim strPath As New System.Text.StringBuilder
> strPath.Append(str1)
> strPath.Append(str2)
> strPath.Append("&TaxYear=").Append(str3)
> strPath.Append("&Cycle=").Append(str4)
> strURL = strPath.ToString
> Response.Redirect(strURL)
> The report is in PDF format and when the report runs it pops up a form
> asking if we want to open, save, or cancel the PDF file...
> The programmer who wrote this doesn't have this issue on his machine.
> However, on the test server this is occuring to us. In fact the programmer
> asked for assistance because on his machine it on a rare occasion would not
> create the PDF file but he couldn't find a cause for it. On our test server
> it occurs like clock work. Log in, run one report (any of them) and then no
> other report including the one you just called won't work until you log back
> in....
> In looking at the report server logs the subsequent requests are not even
> making it to the report server? Any ideas? We were wondering if there was
> something about the response.redirect? I can take the string created by
> the code type it into a url address and it works every time?
> Thanks,
> Kevin

Any other way of data transfere?

Hey everyone,
I have more of a general problem that I need some advice on. In the app I am writing, I am pulling data from a backend Sql Server 2k to a PDA using PocketPC2002. (after long hours and lots of blood, sweat and tears my app is working and almost finished.) Tthe problem I and my team is facing is the fact that because I have to create a merge publication to enable data transfere, the tables used are locked up and no one can make any changes to those tables, unless the publication is deleted, changes made and the publication is recreated again.
Does anyone know of any other way of accessing data from a sql server 2000 db other than the merge replication or RDA methods that will not lock up the tables used? Or, is there a way that I am overlooking in using RDA that will not lock the tables in the database.
Any comments or suggestions?

Thanks in advance,
EricI'm sorry, perhaps I am being thick. If I may, can I try to restate your situation?

You have an App written for PocketPC. You are using the PocketPC version of SQL Server (dunno the actual product name). To enable data to then transfer to your main SQL server (on a network), you use Merge Replication.

The problem is that because it's Merge Replication, to make any schema changes to any of the articles included in the publication, you must delete and re-initialize the replication.

Have I correctly re-stated the problem, or did I miss something?

Regards,

hmscott|||You're not being thick my friend. Yes the problem as you stated it is correct. Forgive me as I am not a DBA, just an ordinary programmer who is dealing with databases right now. (Though I am learning).

So far the best solution we (I) have come up with is that I have created a Sql Script to delete the publication, then make the changes, and then recreate the publication. But, is this the only(or best) way to make any changes to the schema? The tables(articles) used in the publication are constantly locked up and the only other way I have been able to figure out to make changes is to do it through the properties form of the publication (for each individual article effected).
Is there another way I am missing?

Thanks,
Eric

Any fix for internal server error on SQLXML 3.0 and server 2003?

After developing a SQLXML SOAP app using XP pro, I've gone to deploy
it and discovered that SQLXML's SOAP doesn't seem to work on Windows
server 2003. Research on Google shows tons of people having the same
problem over the past year or so, but no solutions that seem to work.
Here's where I'm at:
- Installed Windows server 2003 + IIS 6
- Installed Soap Toolkit 3.0
- Installed SQLXML 3.0 SP2
The virtual directory is created, along with the soap virtual names
and methods. Gettings the WSDL works fine using
http://server/soap/name?WSDL , and if I allow sql= queries, the DB
connection clearly is working because
http://server/soap?sql=select+@.@.version works fine.
However, any attempt to use the actual SOAP resource results in the
infamous (and misspelled) "Internal server error occured. Request
aborted.".
The soap DLL is definitely registered; the ISAPI extension shows up in
the IIS web services extensions as allowed, and the isapi DLL is set
as the wildcard extension for the virtual directory. I've tried
putting quotes around the executable name in the configuration of the
virtual dir.
Has anyone gotten this to work, or is there definitive word that it
cannot work?
Thanks
-Brooks
I have had this exact same problem with Windows 2003. However, I have not found a solution yet. Like you, I have tried everything that has been posted in newsgroup articles but still no luck.
The use of SQLXML with Windows 2003 Server seems to be a bit middled since the instalation instructions state that the SOAP Toolkit is a pre-requisite, yet there is a Microsoft KB article (KB811215) stating that the SOAP Toolkit is only supported on Windo
ws 2003 Server for client-side components and client-side applications.
I think that Micosoft need to refresh their SQLXML information in light of Windows 2003 and the imminent withdrawl of the SOAP Toolkit.
Mark.
|||We're working on this as we speak. SqlXml included in Sql Server 2005 will
not have this requirement. We're also working on plans to release it
sooner. As soon as I have details this group will be the frist to know, I
promise!
Thanks,
Irwin
Irwin Dolobowsky
Program Manager - SqlXml
http://blogs.msdn.com/irwando
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark Abrams" <anonymous@.discussions.microsoft.com> wrote in message
news:6019E7CE-3AB3-4B24-AB9B-EB7AF4D85871@.microsoft.com...
>I have had this exact same problem with Windows 2003. However, I have not
>found a solution yet. Like you, I have tried everything that has been
>posted in newsgroup articles but still no luck.
> The use of SQLXML with Windows 2003 Server seems to be a bit middled since
> the instalation instructions state that the SOAP Toolkit is a
> pre-requisite, yet there is a Microsoft KB article (KB811215) stating that
> the SOAP Toolkit is only supported on Windows 2003 Server for client-side
> components and client-side applications.
> I think that Micosoft need to refresh their SQLXML information in light of
> Windows 2003 and the imminent withdrawl of the SOAP Toolkit.
> Mark.
>

Ansi_Padding - how to get rid of

Hello -
I copied and pasted a database (and log) from Sql Server 2000 to Sql Server
2005 (and attached it).
Everything appeared okay until my VB 6 app had problems with its comboboxes.
I realized that Ansi_Padding was automatically put in by Sql Server 2005 (it
wasn't there in Sql Server 2000), thus making the text in the textbox of the
combo display strangely (e.g. not showing the first few letters of a
particular item, etc).
My question is: How do I get the Ansi_Padding out? It is a varchar field.
Any help will be greatly appreciated!
--
SandySandy (Sandy@.discussions.microsoft.com) writes:
> I copied and pasted a database (and log) from Sql Server 2000 to Sql
> Server 2005 (and attached it).
> Everything appeared okay until my VB 6 app had problems with its
> comboboxes.
> I realized that Ansi_Padding was automatically put in by Sql Server
> 2005 (it wasn't there in Sql Server 2000), thus making the text in the
> textbox of the combo display strangely (e.g. not showing the first few
> letters of a particular item, etc).
> My question is: How do I get the Ansi_Padding out? It is a varchar
> field.
If you attached the database file from SQL 2000, the setting of ANSI_PADDING
should not change, as it saved with the column.
You can verify this by running:
select name, is_ansi_padded
from sys.columns
where object_id('usrdictwords') = object_id
Generally I would recommend that you stick with ANSI_PADDING on, since
there are features that require this setting. In SQL 2000 it was
indexed views and indexed computed columns. In SQL 2005 this require-
ment also applies when you use XQuery.
As for the behaviour of your VB app, it does not sound like ANSI_PADDING
to me. What ANSI_PARDDING is about is what happens to trailing blanks
in varchar when you insert it. With ANSI_PADDING off, they are trimmed,
with ANSI_PADDING on, they are retained.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx