I have a web service, which returns XML to an http request. I need to automate the insert of this XML into SQL server.
I have already designed a procedure which used OPENXML to insert the XML packet, but I need to figure out a way to automate going to the page and retrieving the XML and calling the procedure.
I lack experience on the application development side of SQL Server, and would appriciate any quick & simple ways to perform this. The simpler the better...
Thanks in advance for your ideas.
Michael D.
"sorengi" <sorengi@.discussions.microsoft.com> wrote in message
news:5EEC9F63-7457-4343-A145-5FDCC3615AB3@.microsoft.com...
>I have a web service, which returns XML to an http request. I need to
>automate the insert of this XML into SQL server.
> I have already designed a procedure which used OPENXML to insert the XML
> packet, but I need to figure out a way to automate going to the page and
> retrieving the XML and calling the procedure.
The easiest way to do this would be to create a DTS package in SQL Server
that retrevies the XML and passes it into your procedure. You could then
schedule the package to execute at a specific interval.
See the SQL Server Books Online for information about DTS packages and
scheduling. A simple example of DTS can be found here:
http://sqlxml.org/faqs.aspx?faq=10
Bryant
Showing posts with label web. Show all posts
Showing posts with label web. Show all posts
Thursday, March 29, 2012
Automating MSDE Setup
On microsoft support web site
"http://support.microsoft.com/default.aspx?scid=kb;en-us;234626" I saw the
article 234626 "SAMPLE: Setup1.exe Fully Automating MSDE Setup with the
Package and Deployment Wizard (PDW)" about automation of MSDE setup from PDW
of Visual Basic but only setup1.exe is downloadble. I can't use it because my
setup1.vbp is already customized for my application. I can't find a location
to download a source code of setup1.exe for merge it with my custom
setup1.vbp.
Please help me!
Thanks in advance.
hi Terenzio,
"Lingua Terenzio" <Lingua Terenzio@.discussions.microsoft.com> ha
scritto nel messaggio
news:0954BE29-F0A0-495A-AA4A-B3525941C718@.microsoft.com
> On microsoft support web site
> "http://support.microsoft.com/default.aspx?scid=kb;en-us;234626" I
> saw the article 234626 "SAMPLE: Setup1.exe Fully Automating MSDE
> Setup with the Package and Deployment Wizard (PDW)" about automation
> of MSDE setup from PDW of Visual Basic but only setup1.exe is
> downloadble. I can't use it because my setup1.vbp is already
> customized for my application. I can't find a location to download a
> source code of setup1.exe for merge it with my custom setup1.vbp.
> Please help me!
> Thanks in advance.
have a look at
http://msdn.microsoft.com/vstudio/do...instdwnld.aspx
it provides the full VB6 code for installing MSDE 1.0 ... you have to
customize it a little to support MSDE 2000 in order to manage all MSDE 2000
setup.exe boostrapper installer parameters...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
"http://support.microsoft.com/default.aspx?scid=kb;en-us;234626" I saw the
article 234626 "SAMPLE: Setup1.exe Fully Automating MSDE Setup with the
Package and Deployment Wizard (PDW)" about automation of MSDE setup from PDW
of Visual Basic but only setup1.exe is downloadble. I can't use it because my
setup1.vbp is already customized for my application. I can't find a location
to download a source code of setup1.exe for merge it with my custom
setup1.vbp.
Please help me!
Thanks in advance.
hi Terenzio,
"Lingua Terenzio" <Lingua Terenzio@.discussions.microsoft.com> ha
scritto nel messaggio
news:0954BE29-F0A0-495A-AA4A-B3525941C718@.microsoft.com
> On microsoft support web site
> "http://support.microsoft.com/default.aspx?scid=kb;en-us;234626" I
> saw the article 234626 "SAMPLE: Setup1.exe Fully Automating MSDE
> Setup with the Package and Deployment Wizard (PDW)" about automation
> of MSDE setup from PDW of Visual Basic but only setup1.exe is
> downloadble. I can't use it because my setup1.vbp is already
> customized for my application. I can't find a location to download a
> source code of setup1.exe for merge it with my custom setup1.vbp.
> Please help me!
> Thanks in advance.
have a look at
http://msdn.microsoft.com/vstudio/do...instdwnld.aspx
it provides the full VB6 code for installing MSDE 1.0 ... you have to
customize it a little to support MSDE 2000 in order to manage all MSDE 2000
setup.exe boostrapper installer parameters...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
Sunday, March 25, 2012
Automatically Preview reports
Hi all. Great Forum by the way
I have a question. I have some crystal reports that people open via a hyperlink on a web page. This brings up the crystal report but people have to then "refresh" or "run" the report themselves. They are then prompted for a "start" and "end" date for report.
Is it possible to have the report automically open prompting for the start and end dates? In other words open and run automically?
Regards
Johnu didn't mention what u r using to develop web pages.
If asp.net then use crystalreportviewer control it should automatically prompt for parameters.
Make sure u r not saving data with report.
I Hope this helps..
I have a question. I have some crystal reports that people open via a hyperlink on a web page. This brings up the crystal report but people have to then "refresh" or "run" the report themselves. They are then prompted for a "start" and "end" date for report.
Is it possible to have the report automically open prompting for the start and end dates? In other words open and run automically?
Regards
Johnu didn't mention what u r using to develop web pages.
If asp.net then use crystalreportviewer control it should automatically prompt for parameters.
Make sure u r not saving data with report.
I Hope this helps..
Automatically Generating PDFs With Reports Requiring Parameters
Hi,
I'm working on many reports that are generated using SQL Server
Reporting Services SP 1 from an ASP.NET 1.1 web application. One of the
things the users would like to do is have the report automatically
generate a PDF when they click on the View Report button after entering
the parameters for the report. Parameters could be a date, a city, a
state, etcetera. However, they would like to avoid having to choose the
format and click the Export link. Is there a way to manipulate the
functionality of the View Report button such that it does this
automatically? Thank you for any insights you can provide.
JabooHow are you accessing the reports?
1) Report Manager
2) URL parameters
3) Web Service
Kulgan.sql
I'm working on many reports that are generated using SQL Server
Reporting Services SP 1 from an ASP.NET 1.1 web application. One of the
things the users would like to do is have the report automatically
generate a PDF when they click on the View Report button after entering
the parameters for the report. Parameters could be a date, a city, a
state, etcetera. However, they would like to avoid having to choose the
format and click the Export link. Is there a way to manipulate the
functionality of the View Report button such that it does this
automatically? Thank you for any insights you can provide.
JabooHow are you accessing the reports?
1) Report Manager
2) URL parameters
3) Web Service
Kulgan.sql
Thursday, March 22, 2012
Automatically add permissions on items for users?
Can someone give me a couple of pointers on how to interact with the web
service (?) to automatically add policies for a user to view folders and
reports rather than adding them manually through Report Manager?
thanksThis is a multi-part message in MIME format.
--=_NextPart_000_00C1_01C4BA63.9F970620
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
Rather than authorizing each user in Reporting Services it is
recommended that you create a Windows Group (e.g. Reporting Users),
associate the Group with a Reporting Services Role (e.g. Browser), and
then when you create a new Windows User you make the user a member of
the [Reporting Users] Group.
Garry
--=_NextPart_000_00C1_01C4BA63.9F970620
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
&
Re: Automatically add permissions on items for users?
Rather than authorizing each user in Reporting =Services it is recommended that you create a Windows Group (e.g. =Reporting Users), associate the Group with a Reporting Services Role =(e.g. Browser), and then when you create a new Windows User you make the =user a member of the [Reporting Users] Group.
Garry
--=_NextPart_000_00C1_01C4BA63.9F970620--|||the reason that I need to add each user is that I am using forms
authentication.
how can I call the web service to add policies for each user?
"Garry Lenz" wrote:
> Rather than authorizing each user in Reporting Services it is
> recommended that you create a Windows Group (e.g. Reporting Users),
> associate the Group with a Reporting Services Role (e.g. Browser), and
> then when you create a new Windows User you make the user a member of
> the [Reporting Users] Group.
> Garry
>
service (?) to automatically add policies for a user to view folders and
reports rather than adding them manually through Report Manager?
thanksThis is a multi-part message in MIME format.
--=_NextPart_000_00C1_01C4BA63.9F970620
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
Rather than authorizing each user in Reporting Services it is
recommended that you create a Windows Group (e.g. Reporting Users),
associate the Group with a Reporting Services Role (e.g. Browser), and
then when you create a new Windows User you make the user a member of
the [Reporting Users] Group.
Garry
--=_NextPart_000_00C1_01C4BA63.9F970620
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
&
Re: Automatically add permissions on items for users?
Rather than authorizing each user in Reporting =Services it is recommended that you create a Windows Group (e.g. =Reporting Users), associate the Group with a Reporting Services Role =(e.g. Browser), and then when you create a new Windows User you make the =user a member of the [Reporting Users] Group.
Garry
--=_NextPart_000_00C1_01C4BA63.9F970620--|||the reason that I need to add each user is that I am using forms
authentication.
how can I call the web service to add policies for each user?
"Garry Lenz" wrote:
> Rather than authorizing each user in Reporting Services it is
> recommended that you create a Windows Group (e.g. Reporting Users),
> associate the Group with a Reporting Services Role (e.g. Browser), and
> then when you create a new Windows User you make the user a member of
> the [Reporting Users] Group.
> Garry
>
Monday, March 19, 2012
Automatic PDF output
Normally if I want a report in PDF I have to first view the report in the web browser, then select export as PDF and save the PDF file.
Is there a way to avoid the first step (web browser viewing) and have the report immediatly in PDF as soon as I click the "View Report" button? (without using email/file subscription)
Thank you,
Roberto
use this link
http://www.codeproject.com/sqlrs/PDFUsingSQLRepServices.asp
Sunday, March 11, 2012
Automatic Field Entry
Hi,
I'm creating a database using SQL Server 2005 Express Edition (Comes with Visual Web Developer). The table which I am creating has the following Fields - all don't allow nulls:
ID
UserId
Date
Description
(UserId is a foreign key to asp_net_Users as I am supporting user accounts)
Basically what I need to do is create a page where I as an Administrator can log onto and enter just the text for the field Description. Then once I upload this I wish all users to visit the site and view this Description on a page however with it also listing the Administrator who wrote it along with the Date. I wish both of these fields to be added automatically (UserId to display the User Name and the Date to display the date and time with which the Description was added - However these need to be editable by the Administrator if he/she wishes to change them).
Can anyone point me in the right direction on the steps needed to create this scenario?
Thanks for any help
Daniel
I'm a bit confused on how you plan on getting the UserID? Is this actually coming from the membership provider in asp.net or are you storing it somewhere else? As far as the Date field you can set the default value of that field on the SQL side to getDate(). This will populate the field with the timestamp whenever you enter in a row into the database. Even though you set a default value you can always pass in your own date/time to your UPDATE statement if you want if you need to edit it.
|||
wtroom:
I'm a bit confused on how you plan on getting the UserID? Is this actually coming from the membership provider in asp.net or are you storing it somewhere else? As far as the Date field you can set the default value of that field on the SQL side to getDate(). This will populate the field with the timestamp whenever you enter in a row into the database. Even though you set a default value you can always pass in your own date/time to your UPDATE statement if you want if you need to edit it.
To be fully accurate, getDate() returns a datetime datatype value that is set to the current date and time.
It's dangerous to mix in the word "timestamp" into such a discussion because timestamp is also a sql server datatype. A timestamp datatype does NOT contain a date or a time and so cannot be used for this purpose.
|||Hi,
Thanks for your help. The UserId is coming from the Membership provider in asp.net.
I'm relatively new to ASP.NET and am struggling to implement the getDate() on the SQL side. I have created the SQL Datasource to return the table and have then tried to add a WHERE statement.
I assume that the column needs to Date and the Operator = however I am unsure on the control and parameter properties. I have played around and tried Control and QueryString for the Control property and then added getDate() to the Parameter Property Default Value however this just breaks the Details view which is attached to the SQL Datasource.
Any more help would be greatly appreciated.
Thanks
Daniel
for the username I created a label with the Id UserIdValue and deleted the text and turned visible to false.
I then added code for the label onload
Protected Sub UserIdValue_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles UserIdValue.Load UserIdValue.Text = Membership.GetUser().UserName.ToString() End Sub
This made the label display my username when logged on.
The next part for the detailsview code for iteminserting I added
Protected Sub DetailsView1_ItemInserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertEventArgs) Handles DetailsView1.ItemInserting
e.Values("Writtenby") = Membership.GetUser().UserName
e.Values("Date") = DateTime.Now
End Sub
I then deleted Writtenby and Date fields from the details view.
Now when I insert and enter only title and description the Writtenby and Date fields are automatically updated.
Daniel
Automatic failover failure
Hello,
we're running a mirrored database with High Availability for Automatic failover including a Witness instance for a web application.
When doing a manual failover on the database in Management studio, the roles are switched correctly and the database is in "Principal, Synchronized" and "Mirror, Synchronized/Restoring" mode. The web application has no problems switching servers by using client failover with the jdbc driver. There is no problem accessing the database with Management Studio.
However, if we stop the SQL service on the Principal server the role is automatically failed over to the Mirror server by the Witness. The database is then in the mode "Principal, Disconnected" which should be fine. However, accessing the database from the web application or with Management Studio yields some strange results. It is not possible to write to the database, and reading from the database works inconsistently (the web application seems like it can do it, but not from the Management Studio).
Starting the SQL service on the former Principal server makes the database go into mode "Mirror, Synchronizing/Restoring" and "Principal, Synchronizing". And it will stay that way indefinitely. There are not that many updates/transactions made to the database that can make it stay in this state, especially if you can't write to the database in the first place.
The next step taken after being stuck in this state is to stop the SQL service on the Mirror (former Principal), restart the service on the Principal (former Mirror). Accessing the database now works. The database is in mode "Principal, Disconnected". Starting the SQL service on the Mirror (former Principal) makes the database go into the normal "Principal, Synchronized" and "Mirror, Synchronized/Restoring" mode. Access to database is normal.
The same erroneous behaviour can be observed by unplugging the network cable on the Principal server, so it seems like we can only get a smooth transition by doing a manual failover.
Any ideas on what might be the problem? Has anybody experienced a similar situation?
Can you see any errors or warnings from SQL error log in Principal & mirror servers?|||These are entires I've found on the principal (former mirror) server
2006-11-26 12:42:52.60 spid8s Database mirroring is inactive for database 'db'. This is an informational message only. No user action is required.
2006-11-26 12:42:52.60 spid22s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:42:52.60 spid22s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
2006-11-26 12:42:52.60 spid8s The mirrored database "db" is changing roles from "MIRROR" to "PRINCIPAL" due to Auto Failover.
2006-11-26 12:42:52.75 spid22s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:42:52.75 spid22s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
2006-11-26 12:42:54.52 spid8s Recovery is writing a checkpoint in database 'db' (5). This is an informational message only. No user action is required.
These are entries I've found on the witness server
2006-11-26 12:45:03.36 spid27s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:45:03.36 spid27s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
2006-11-26 12:45:03.50 spid19s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:45:03.50 spid19s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
2006-11-26 12:45:03.82 spid19s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:45:03.82 spid19s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
2006-11-26 12:45:04.03 spid19s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:45:04.03 spid19s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
I guess the errors about receiving data from SERVER01 should be normal since the SQL service is stopped on that server during this time.
Thanks,
Ingmar
|||We are experiencing the same issue.
Some additional information on our situation:
We are running a mirrored database with High Availability for Automatic failover including a Witness instance for a non web application. As in the original post we are stuck (Principal, Synchronizing) and (Mirror Synchronizing / Restoring...).
We are using the new SqlCacheDependency (command based vs. table based polling) cache notification.
We are able to access some of the data in the database via SQL Server Management Studio - query window, however we can not expand the database in the Object Explorer window and if we right click on the database and attempt to access the properties page we receive a 'Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)'.
This may not be pertinent but we also noticed that even though the query from sys.database_mirroring show that the databases are in sync we observe that the Modified and Accessed dates on the new Principal .mdf and .ldf files lags the new Mirror partner by 16 Hrs for the .ldf file and 7 Days for the .mdf file.
|||Check this KBA http://support.microsoft.com/kb/912422 is any help in this regard.|||Hello,
We were able to reproduce the problem in a test environment and it seems that we're only having problems with databases that have fulltext indexes. And, after applying the SP2 CTP in this environment the problem went away. We haven't touched the production servers yet as we wait for the 'full' release of the SP2.
Regards,
Ingmar
|||We are also experiencing the exact same problems with trying to mirror a database containing a Full Text index. Does anybody have an estimate as to when the "full" release of SP2 is to be released?
Thanks,
Jody
Automatic failover failure
Hello,
we're running a mirrored database with High Availability for Automatic failover including a Witness instance for a web application.
When doing a manual failover on the database in Management studio, the roles are switched correctly and the database is in "Principal, Synchronized" and "Mirror, Synchronized/Restoring" mode. The web application has no problems switching servers by using client failover with the jdbc driver. There is no problem accessing the database with Management Studio.
However, if we stop the SQL service on the Principal server the role is automatically failed over to the Mirror server by the Witness. The database is then in the mode "Principal, Disconnected" which should be fine. However, accessing the database from the web application or with Management Studio yields some strange results. It is not possible to write to the database, and reading from the database works inconsistently (the web application seems like it can do it, but not from the Management Studio).
Starting the SQL service on the former Principal server makes the database go into mode "Mirror, Synchronizing/Restoring" and "Principal, Synchronizing". And it will stay that way indefinitely. There are not that many updates/transactions made to the database that can make it stay in this state, especially if you can't write to the database in the first place.
The next step taken after being stuck in this state is to stop the SQL service on the Mirror (former Principal), restart the service on the Principal (former Mirror). Accessing the database now works. The database is in mode "Principal, Disconnected". Starting the SQL service on the Mirror (former Principal) makes the database go into the normal "Principal, Synchronized" and "Mirror, Synchronized/Restoring" mode. Access to database is normal.
The same erroneous behaviour can be observed by unplugging the network cable on the Principal server, so it seems like we can only get a smooth transition by doing a manual failover.
Any ideas on what might be the problem? Has anybody experienced a similar situation?
Can you see any errors or warnings from SQL error log in Principal & mirror servers?|||These are entires I've found on the principal (former mirror) server
2006-11-26 12:42:52.60 spid8s Database mirroring is inactive for database 'db'. This is an informational message only. No user action is required.
2006-11-26 12:42:52.60 spid22s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:42:52.60 spid22s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
2006-11-26 12:42:52.60 spid8s The mirrored database "db" is changing roles from "MIRROR" to "PRINCIPAL" due to Auto Failover.
2006-11-26 12:42:52.75 spid22s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:42:52.75 spid22s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
2006-11-26 12:42:54.52 spid8s Recovery is writing a checkpoint in database 'db' (5). This is an informational message only. No user action is required.
These are entries I've found on the witness server
2006-11-26 12:45:03.36 spid27s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:45:03.36 spid27s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
2006-11-26 12:45:03.50 spid19s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:45:03.50 spid19s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
2006-11-26 12:45:03.82 spid19s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:45:03.82 spid19s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
2006-11-26 12:45:04.03 spid19s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:45:04.03 spid19s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
I guess the errors about receiving data from SERVER01 should be normal since the SQL service is stopped on that server during this time.
Thanks,
Ingmar
|||We are experiencing the same issue.
Some additional information on our situation:
We are running a mirrored database with High Availability for Automatic failover including a Witness instance for a non web application. As in the original post we are stuck (Principal, Synchronizing) and (Mirror Synchronizing / Restoring...).
We are using the new SqlCacheDependency (command based vs. table based polling) cache notification.
We are able to access some of the data in the database via SQL Server Management Studio - query window, however we can not expand the database in the Object Explorer window and if we right click on the database and attempt to access the properties page we receive a 'Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)'.
This may not be pertinent but we also noticed that even though the query from sys.database_mirroring show that the databases are in sync we observe that the Modified and Accessed dates on the new Principal .mdf and .ldf files lags the new Mirror partner by 16 Hrs for the .ldf file and 7 Days for the .mdf file.
|||Check this KBA http://support.microsoft.com/kb/912422 is any help in this regard.|||Hello,
We were able to reproduce the problem in a test environment and it seems that we're only having problems with databases that have fulltext indexes. And, after applying the SP2 CTP in this environment the problem went away. We haven't touched the production servers yet as we wait for the 'full' release of the SP2.
Regards,
Ingmar
|||We are also experiencing the exact same problems with trying to mirror a database containing a Full Text index. Does anybody have an estimate as to when the "full" release of SP2 is to be released?
Thanks,
Jody
Automatic failover failure
Hello,
we're running a mirrored database with High Availability for Automatic failover including a Witness instance for a web application.
When doing a manual failover on the database in Management studio, the roles are switched correctly and the database is in "Principal, Synchronized" and "Mirror, Synchronized/Restoring" mode. The web application has no problems switching servers by using client failover with the jdbc driver. There is no problem accessing the database with Management Studio.
However, if we stop the SQL service on the Principal server the role is automatically failed over to the Mirror server by the Witness. The database is then in the mode "Principal, Disconnected" which should be fine. However, accessing the database from the web application or with Management Studio yields some strange results. It is not possible to write to the database, and reading from the database works inconsistently (the web application seems like it can do it, but not from the Management Studio).
Starting the SQL service on the former Principal server makes the database go into mode "Mirror, Synchronizing/Restoring" and "Principal, Synchronizing". And it will stay that way indefinitely. There are not that many updates/transactions made to the database that can make it stay in this state, especially if you can't write to the database in the first place.
The next step taken after being stuck in this state is to stop the SQL service on the Mirror (former Principal), restart the service on the Principal (former Mirror). Accessing the database now works. The database is in mode "Principal, Disconnected". Starting the SQL service on the Mirror (former Principal) makes the database go into the normal "Principal, Synchronized" and "Mirror, Synchronized/Restoring" mode. Access to database is normal.
The same erroneous behaviour can be observed by unplugging the network cable on the Principal server, so it seems like we can only get a smooth transition by doing a manual failover.
Any ideas on what might be the problem? Has anybody experienced a similar situation?
Can you see any errors or warnings from SQL error log in Principal & mirror servers?|||These are entires I've found on the principal (former mirror) server
2006-11-26 12:42:52.60 spid8s Database mirroring is inactive for database 'db'. This is an informational message only. No user action is required.
2006-11-26 12:42:52.60 spid22s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:42:52.60 spid22s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
2006-11-26 12:42:52.60 spid8s The mirrored database "db" is changing roles from "MIRROR" to "PRINCIPAL" due to Auto Failover.
2006-11-26 12:42:52.75 spid22s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:42:52.75 spid22s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
2006-11-26 12:42:54.52 spid8s Recovery is writing a checkpoint in database 'db' (5). This is an informational message only. No user action is required.
These are entries I've found on the witness server
2006-11-26 12:45:03.36 spid27s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:45:03.36 spid27s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
2006-11-26 12:45:03.50 spid19s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:45:03.50 spid19s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
2006-11-26 12:45:03.82 spid19s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:45:03.82 spid19s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
2006-11-26 12:45:04.03 spid19s Error: 1474, Severity: 16, State: 1.
2006-11-26 12:45:04.03 spid19s Database mirroring connection error 4 'An error occurred while receiving data: '64(error not found)'.' for 'TCP://SERVER01:7024'.
I guess the errors about receiving data from SERVER01 should be normal since the SQL service is stopped on that server during this time.
Thanks,
Ingmar
|||We are experiencing the same issue.
Some additional information on our situation:
We are running a mirrored database with High Availability for Automatic failover including a Witness instance for a non web application. As in the original post we are stuck (Principal, Synchronizing) and (Mirror Synchronizing / Restoring...).
We are using the new SqlCacheDependency (command based vs. table based polling) cache notification.
We are able to access some of the data in the database via SQL Server Management Studio - query window, however we can not expand the database in the Object Explorer window and if we right click on the database and attempt to access the properties page we receive a 'Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)'.
This may not be pertinent but we also noticed that even though the query from sys.database_mirroring show that the databases are in sync we observe that the Modified and Accessed dates on the new Principal .mdf and .ldf files lags the new Mirror partner by 16 Hrs for the .ldf file and 7 Days for the .mdf file.
|||Check this KBA http://support.microsoft.com/kb/912422 is any help in this regard.|||Hello,
We were able to reproduce the problem in a test environment and it seems that we're only having problems with databases that have fulltext indexes. And, after applying the SP2 CTP in this environment the problem went away. We haven't touched the production servers yet as we wait for the 'full' release of the SP2.
Regards,
Ingmar
|||We are also experiencing the exact same problems with trying to mirror a database containing a Full Text index. Does anybody have an estimate as to when the "full" release of SP2 is to be released?
Thanks,
Jody
Automatic client redirection during the hot failover in .NET 1.1 applications and SQL 2005
Hi,
We would like to use SQL Server 2005 with our .NET 1.1 web applications.
We would like to leverage the new mirroring enhancements to achieve higher
server availability.
However, I know that .NET 1.1 doesn't support automatic client redirection
during the hot failover
and the only option is to handle the redirection manually in the code. By
saying that I mean the following:
string connectionString="Trusted_Connection=Yes;Data Source=SERVER1;Initial
Catalog=MirrorTest;Integrated Security=SSPI;";
SqlConnection connection=new SqlConnection(connectionString);
try
{
connection.Open();
}
catch
{
connection.ConnectionString=connectionString.repla ce("Data
Source=SERVER1","Data Source=SERVER2");
connection.Open();
}
I saw that someone has suggested to use the .NET OdbcClient or OleDB client
and have them access the new SQL Native Client library which is
mirroring-aware
([url]http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/90517df320d22f1/cc09960826a31a94?lnk=st&q=sql+server+2005+Failover +.net+1.1&rnum=10&hl=en#cc09960826a31a94)[/url].
So my question is what is the better way to use and how the second way can
be implemented? Does it mean that we need to download SQL Native Client from
SQL
2005 feature pack?
Thank you very much,
Best Regards
Michael
Hi Michael,
My understanding of your issue is that:
You would like to know how to use the OLEDB provider, ODBC.NET or ADO with
SQL Native Client so that you can directly configure the connection string
for the database mirroring.
If I have misunderstood, please let me know.
Yes, you need to install SQL Native Client first on your computer before
further actions. SQL Native Client combines the SQL OLD DB provider and the
SQL ODBC driver into one native DLL while also providing new functionality
above and beyond that supplied by the MDAC, so it is easy to be integrated
to old applications.
You may refer to:
SQL Native Client Programming
http://msdn2.microsoft.com/en-us/library/ms130892.aspx
The connection string for database mirroring is "server=Partner_A; failover
partner=Partner_B; database=AdventureWorks". You may refer to:
Making the Initial Connection to a Database Mirroring Session
http://msdn2.microsoft.com/en-us/library/ms366348.aspx
Using Connection String Keywords with SQL Native Client
http://msdn2.microsoft.com/en-us/library/ms130822.aspx
Hope this helps. Please feel free to let me know if you need further
assistance.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
We would like to use SQL Server 2005 with our .NET 1.1 web applications.
We would like to leverage the new mirroring enhancements to achieve higher
server availability.
However, I know that .NET 1.1 doesn't support automatic client redirection
during the hot failover
and the only option is to handle the redirection manually in the code. By
saying that I mean the following:
string connectionString="Trusted_Connection=Yes;Data Source=SERVER1;Initial
Catalog=MirrorTest;Integrated Security=SSPI;";
SqlConnection connection=new SqlConnection(connectionString);
try
{
connection.Open();
}
catch
{
connection.ConnectionString=connectionString.repla ce("Data
Source=SERVER1","Data Source=SERVER2");
connection.Open();
}
I saw that someone has suggested to use the .NET OdbcClient or OleDB client
and have them access the new SQL Native Client library which is
mirroring-aware
([url]http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/90517df320d22f1/cc09960826a31a94?lnk=st&q=sql+server+2005+Failover +.net+1.1&rnum=10&hl=en#cc09960826a31a94)[/url].
So my question is what is the better way to use and how the second way can
be implemented? Does it mean that we need to download SQL Native Client from
SQL
2005 feature pack?
Thank you very much,
Best Regards
Michael
Hi Michael,
My understanding of your issue is that:
You would like to know how to use the OLEDB provider, ODBC.NET or ADO with
SQL Native Client so that you can directly configure the connection string
for the database mirroring.
If I have misunderstood, please let me know.
Yes, you need to install SQL Native Client first on your computer before
further actions. SQL Native Client combines the SQL OLD DB provider and the
SQL ODBC driver into one native DLL while also providing new functionality
above and beyond that supplied by the MDAC, so it is easy to be integrated
to old applications.
You may refer to:
SQL Native Client Programming
http://msdn2.microsoft.com/en-us/library/ms130892.aspx
The connection string for database mirroring is "server=Partner_A; failover
partner=Partner_B; database=AdventureWorks". You may refer to:
Making the Initial Connection to a Database Mirroring Session
http://msdn2.microsoft.com/en-us/library/ms366348.aspx
Using Connection String Keywords with SQL Native Client
http://msdn2.microsoft.com/en-us/library/ms130822.aspx
Hope this helps. Please feel free to let me know if you need further
assistance.
Charles Wang
Microsoft Online Community Support
================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====
Automatic client redirection during the hot failover in .NET 1.1 applications and SQL 2005
Hi,
We would like to use SQL Server 2005 with our .NET 1.1 web applications.
We would like to leverage the new mirroring enhancements to achieve higher
server availability.
However, I know that .NET 1.1 doesn't support automatic client redirection
during the hot failover
and the only option is to handle the redirection manually in the code. By
saying that I mean the following:
string connectionString="Trusted_Connection=Yes;Data Source=SERVER1;Initial
Catalog=MirrorTest;Integrated Security=SSPI;";
SqlConnection connection=new SqlConnection(connectionString);
try
{
connection.Open();
}
catch
{
connection.ConnectionString=connectionString.replace("Data
Source=SERVER1","Data Source=SERVER2");
connection.Open();
}
I saw that someone has suggested to use the .NET OdbcClient or OleDB client
and have them access the new SQL Native Client library which is
mirroring-aware
(http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/90517df320d22f1/cc09960826a31a94?lnk=st&q=sql+server+2005+Failover+.net+1.1&rnum=10&hl=en#cc09960826a31a94).
So my question is what is the better way to use and how the second way can
be implemented? Does it mean that we need to download SQL Native Client from
SQL
2005 feature pack?
Thank you very much,
Best Regards
MichaelHi Michael,
My understanding of your issue is that:
You would like to know how to use the OLEDB provider, ODBC.NET or ADO with
SQL Native Client so that you can directly configure the connection string
for the database mirroring.
If I have misunderstood, please let me know.
Yes, you need to install SQL Native Client first on your computer before
further actions. SQL Native Client combines the SQL OLD DB provider and the
SQL ODBC driver into one native DLL while also providing new functionality
above and beyond that supplied by the MDAC, so it is easy to be integrated
to old applications.
You may refer to:
SQL Native Client Programming
http://msdn2.microsoft.com/en-us/library/ms130892.aspx
The connection string for database mirroring is "server=Partner_A; failover
partner=Partner_B; database=AdventureWorks". You may refer to:
Making the Initial Connection to a Database Mirroring Session
http://msdn2.microsoft.com/en-us/library/ms366348.aspx
Using Connection String Keywords with SQL Native Client
http://msdn2.microsoft.com/en-us/library/ms130822.aspx
Hope this helps. Please feel free to let me know if you need further
assistance.
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
We would like to use SQL Server 2005 with our .NET 1.1 web applications.
We would like to leverage the new mirroring enhancements to achieve higher
server availability.
However, I know that .NET 1.1 doesn't support automatic client redirection
during the hot failover
and the only option is to handle the redirection manually in the code. By
saying that I mean the following:
string connectionString="Trusted_Connection=Yes;Data Source=SERVER1;Initial
Catalog=MirrorTest;Integrated Security=SSPI;";
SqlConnection connection=new SqlConnection(connectionString);
try
{
connection.Open();
}
catch
{
connection.ConnectionString=connectionString.replace("Data
Source=SERVER1","Data Source=SERVER2");
connection.Open();
}
I saw that someone has suggested to use the .NET OdbcClient or OleDB client
and have them access the new SQL Native Client library which is
mirroring-aware
(http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/90517df320d22f1/cc09960826a31a94?lnk=st&q=sql+server+2005+Failover+.net+1.1&rnum=10&hl=en#cc09960826a31a94).
So my question is what is the better way to use and how the second way can
be implemented? Does it mean that we need to download SQL Native Client from
SQL
2005 feature pack?
Thank you very much,
Best Regards
MichaelHi Michael,
My understanding of your issue is that:
You would like to know how to use the OLEDB provider, ODBC.NET or ADO with
SQL Native Client so that you can directly configure the connection string
for the database mirroring.
If I have misunderstood, please let me know.
Yes, you need to install SQL Native Client first on your computer before
further actions. SQL Native Client combines the SQL OLD DB provider and the
SQL ODBC driver into one native DLL while also providing new functionality
above and beyond that supplied by the MDAC, so it is easy to be integrated
to old applications.
You may refer to:
SQL Native Client Programming
http://msdn2.microsoft.com/en-us/library/ms130892.aspx
The connection string for database mirroring is "server=Partner_A; failover
partner=Partner_B; database=AdventureWorks". You may refer to:
Making the Initial Connection to a Database Mirroring Session
http://msdn2.microsoft.com/en-us/library/ms366348.aspx
Using Connection String Keywords with SQL Native Client
http://msdn2.microsoft.com/en-us/library/ms130822.aspx
Hope this helps. Please feel free to let me know if you need further
assistance.
Charles Wang
Microsoft Online Community Support
======================================================When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================
automatic change of permission on database after install web site
I have Web Site with ASPNETDB database and own database. I was precompiled this site and I created aplication with Web Setup Project. This setup aplication includes SQL 2005 EXPRESS. When I installed and ran an my web site in Internet explorer, that displayed error "Failed to update database ... because the database is read-only". I know, that is a bug with permisson and the fix a bug is solution for this problem but i have installation package and i need automatic fix in setup application. Thanx for comments.
Vitty,
The main focus of this forum is on connectivty, client odbc/oledb drivers and etc. The expert on your issue might not visit this forum often. So, I suggest you to visite sql setup forum instead.
Thursday, March 8, 2012
Automated export of report?
Hello.
Is it possible to have a report automatically export the results to one of the available formats (HTML, Excel, Web Archive, etc) when the report is generated? ...rather than having to select the format and click Export?
Thanks,
Jake
The out of the box endpoints (Reports & ReportServer) will display HTML first. If you want some other behavior you will have to write your own application which uses either the HTTP endpoint or SOAP access to render the report into your desired format.Sunday, February 19, 2012
Auto update to sql server tables
I'm changing data storage for an asp.net project from MS Access to Sql Server. I've got the web site working, but I need to update the sql server tables with data from our Oracle db daily. What is the "best" way to do that?
I've read about DTS, but have never done anything like that. Would it be worth the time and effort to study? (So far I've created a package, with the import wizard, that doesn't work & I don't have the authority to delete :-)
I know I could create a dataset with my Oracle data and use that to update sql server. But is there a way to schedule an aspx to run authomatically? Would this affect performance? The sql server db isn't very big (30-40,000 records), but the Oracle db is & I need to do quite a bit of manipulation to the data.
This is new to me & I'm don't know what I should be searching for to find help. And if there is a more appropriate place to post this question, please let me know.
Thanks.Yes. DTS is one good way to go and it is very easy to do.
Do you need to purge the data in your SQL before each load? Then the account has to have the right.
You can schedule the DTS to run at whatever time you want.
Thursday, February 16, 2012
auto switching a login
Hello Group,
I have a situation where one of my intranet web
sites has this error: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
What happens is that when you open the intranet site,
the user is normally logged in with their AD login.
This AD login is in a group which has permissions to the database.
The user will leave the web page open for say and hour or two. When they
come back to their desk and refresh their browser, they get the error.
is it possible the site logs in correctly but the sleeping process casues
the user to be force over to 'NT AUTHORITY\ANONYMOUS LOGON'? This user is not
a valid user in our database. We do not user this user for any of our
permissions.
RichHere's a guess:
1. Your Intranet site stores the user's login information (i.e.
username) in a session variable
2. Your web server is configured to expire idle sessions after a
specific interval, in IIS the default is 20 minutes. When a session
expires, all variables associated with it are lost.
3. Your user leaves the site open for an hour without doing anything.
The session expires, losing their username from the session variables.
4. When your user refreshes their page, the page isn't checking for a
valid session, it blindly tries to connect to the database using the
username stored in the session variables, in this case nothing.
The proper fix for this is for the Intranet site to check for a valid
session, and throw up a "Session has expired" message, instead of
trying to connect to the database without a username.|||Hello Tracy,
thanks for the ideas. I forwarded them to my web guy but it seems he
already had thought of those ideas!
1. Your Intranet site stores the user's login information (i.e.
username) in a session variable
[me web guy said] I do use the session variable but my program has a
checking that will renew a session when it expires.
2. Your web server is configured to expire idle sessions after a
specific interval, in IIS the default is 20 minutes. When a session
expires, all variables associated with it are lost.
[me web guy said] I believed time is extended to be longer than 1 hour. Not
sure how long but I think itâ's more than an hour.
3. Your user leaves the site open for an hour without doing anything.
The session expires, losing their username from the session variables.
[me web guy said] Like I mentioned in #1, itâ's automatically renewed.
4. When your user refreshes their page, the page isn't checking for a
valid session, it blindly tries to connect to the database using the
username stored in the session variables, in this case nothing.
[me web guy said] Same as #1
...nuts...
Rich
"Tracy McKibben" wrote:
> Here's a guess:
> 1. Your Intranet site stores the user's login information (i.e.
> username) in a session variable
> 2. Your web server is configured to expire idle sessions after a
> specific interval, in IIS the default is 20 minutes. When a session
> expires, all variables associated with it are lost.
> 3. Your user leaves the site open for an hour without doing anything.
> The session expires, losing their username from the session variables.
> 4. When your user refreshes their page, the page isn't checking for a
> valid session, it blindly tries to connect to the database using the
> username stored in the session variables, in this case nothing.
> The proper fix for this is for the Intranet site to check for a valid
> session, and throw up a "Session has expired" message, instead of
> trying to connect to the database without a username.
>
I have a situation where one of my intranet web
sites has this error: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'
What happens is that when you open the intranet site,
the user is normally logged in with their AD login.
This AD login is in a group which has permissions to the database.
The user will leave the web page open for say and hour or two. When they
come back to their desk and refresh their browser, they get the error.
is it possible the site logs in correctly but the sleeping process casues
the user to be force over to 'NT AUTHORITY\ANONYMOUS LOGON'? This user is not
a valid user in our database. We do not user this user for any of our
permissions.
RichHere's a guess:
1. Your Intranet site stores the user's login information (i.e.
username) in a session variable
2. Your web server is configured to expire idle sessions after a
specific interval, in IIS the default is 20 minutes. When a session
expires, all variables associated with it are lost.
3. Your user leaves the site open for an hour without doing anything.
The session expires, losing their username from the session variables.
4. When your user refreshes their page, the page isn't checking for a
valid session, it blindly tries to connect to the database using the
username stored in the session variables, in this case nothing.
The proper fix for this is for the Intranet site to check for a valid
session, and throw up a "Session has expired" message, instead of
trying to connect to the database without a username.|||Hello Tracy,
thanks for the ideas. I forwarded them to my web guy but it seems he
already had thought of those ideas!
1. Your Intranet site stores the user's login information (i.e.
username) in a session variable
[me web guy said] I do use the session variable but my program has a
checking that will renew a session when it expires.
2. Your web server is configured to expire idle sessions after a
specific interval, in IIS the default is 20 minutes. When a session
expires, all variables associated with it are lost.
[me web guy said] I believed time is extended to be longer than 1 hour. Not
sure how long but I think itâ's more than an hour.
3. Your user leaves the site open for an hour without doing anything.
The session expires, losing their username from the session variables.
[me web guy said] Like I mentioned in #1, itâ's automatically renewed.
4. When your user refreshes their page, the page isn't checking for a
valid session, it blindly tries to connect to the database using the
username stored in the session variables, in this case nothing.
[me web guy said] Same as #1
...nuts...
Rich
"Tracy McKibben" wrote:
> Here's a guess:
> 1. Your Intranet site stores the user's login information (i.e.
> username) in a session variable
> 2. Your web server is configured to expire idle sessions after a
> specific interval, in IIS the default is 20 minutes. When a session
> expires, all variables associated with it are lost.
> 3. Your user leaves the site open for an hour without doing anything.
> The session expires, losing their username from the session variables.
> 4. When your user refreshes their page, the page isn't checking for a
> valid session, it blindly tries to connect to the database using the
> username stored in the session variables, in this case nothing.
> The proper fix for this is for the Intranet site to check for a valid
> session, and throw up a "Session has expired" message, instead of
> trying to connect to the database without a username.
>
Subscribe to:
Comments (Atom)