Hello,
I am doing some development for a client who has an IIS server running
webmail on the same box as the SQL server 2000 sp3 install. I am trying to connect to
this box but do not know how to set it up to connect. The IIS for SQL server
tool lets me add a virtual directory just fine but will not connect. This
problem is not firewall based, as the 1433 port is open, and the database is
running just fine, as those in the LAN can connect ok. My problem is with
the 'external' face and my concern that the running IIS process is
preventing web-based connection. How do I modify/change the IIS for SQL
virtual directory to respond?
If you need more information, please let me know.
Thanks,
Donald Wheeler
What error do you get when you attempt to connect?
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||I get the standard server not found or access denied error - both via ASP calls and the SQL Server Query analyzer. I don't think that it's an authentication problem because I've tried using both valid and invalid passwords, as well as trying to the connec
t to the Northwind database, and received the same error
Donald
|||Just to clarify, I am trying to enable web access to a SQL Server 2k sp3 database on a machine that already has a set of active IIS directories for webmail. I think the firewall is ok but the connections are getting 'swallowed' by the active IIS and not h
itting the SQL server - does this make sense and if so, how may I remedy it?
Thanks,
Donald Wheeler
sql
Showing posts with label via. Show all posts
Showing posts with label via. Show all posts
Friday, March 30, 2012
Problems installing/accessing SQL server via web
Hello,
I am doing some development for a client who has an IIS server running
webmail on the same box as the SQL server 2000 sp3 install. I am trying to c
onnect to
this box but do not know how to set it up to connect. The IIS for SQL server
tool lets me add a virtual directory just fine but will not connect. This
problem is not firewall based, as the 1433 port is open, and the database is
running just fine, as those in the LAN can connect ok. My problem is with
the 'external' face and my concern that the running IIS process is
preventing web-based connection. How do I modify/change the IIS for SQL
virtual directory to respond?
If you need more information, please let me know.
Thanks,
Donald WheelerWhat error do you get when you attempt to connect?
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I get the standard server not found or access denied error - both via ASP ca
lls and the SQL Server Query analyzer. I don't think that it's an authentica
tion problem because I've tried using both valid and invalid passwords, as w
ell as trying to the connec
t to the Northwind database, and received the same error
Donald|||Just to clarify, I am trying to enable web access to a SQL Server 2k sp3 dat
abase on a machine that already has a set of active IIS directories for webm
ail. I think the firewall is ok but the connections are getting 'swallowed'
by the active IIS and not h
itting the SQL server - does this make sense and if so, how may I remedy it?
Thanks,
Donald Wheeler
I am doing some development for a client who has an IIS server running
webmail on the same box as the SQL server 2000 sp3 install. I am trying to c
onnect to
this box but do not know how to set it up to connect. The IIS for SQL server
tool lets me add a virtual directory just fine but will not connect. This
problem is not firewall based, as the 1433 port is open, and the database is
running just fine, as those in the LAN can connect ok. My problem is with
the 'external' face and my concern that the running IIS process is
preventing web-based connection. How do I modify/change the IIS for SQL
virtual directory to respond?
If you need more information, please let me know.
Thanks,
Donald WheelerWhat error do you get when you attempt to connect?
Rand
This posting is provided "as is" with no warranties and confers no rights.|||I get the standard server not found or access denied error - both via ASP ca
lls and the SQL Server Query analyzer. I don't think that it's an authentica
tion problem because I've tried using both valid and invalid passwords, as w
ell as trying to the connec
t to the Northwind database, and received the same error
Donald|||Just to clarify, I am trying to enable web access to a SQL Server 2k sp3 dat
abase on a machine that already has a set of active IIS directories for webm
ail. I think the firewall is ok but the connections are getting 'swallowed'
by the active IIS and not h
itting the SQL server - does this make sense and if so, how may I remedy it?
Thanks,
Donald Wheeler
Monday, March 26, 2012
Problems getting information out of a SQLDataSource
I'm working in Visual Studio, I've added a SQLDataSource to the webpage which links to a SQL server database via a DSN (created with the wizard) and it brings back data from a stored procedure with no problems.
I want to access the information from this SQLDataSource in my code and can't seem to get anything from it.
help would be most appreciated.
What do you want to do with the data? Put it in a Gridview? Datalist? Here is a article with a GridView accessing a datasource.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaspp/html/GridViewEx02.asp
|||I actually want to put the information into a text field rather than a datagrid.|||gave up on that and did it the easy way, all code rather than using the wizards.|||How would you want to access the information from the SqlDataSource? If you just want to access the database retrieved from database by the Select command, you can do something like this:
SqlDataSource sds = new SqlDataSource(conn.ConnectionString, cmd.CommandText);
DataSourceSelectArguments dssa = new DataSourceSelectArguments();
dssa.AddSupportedCapabilities(DataSourceCapabilities.RetrieveTotalRowCount);
DataView dv = (DataView)sds.Select(dssa);
Problems Exporting To Excel When Using Report Subscription
Hi,
I'm having a problem with Reporting Services report subscriptions.
I have a set of reports that need to be sent via email on a schedule. I need
to send them in Excel format, but the files sent to my email account are
unreadable in Excel. I am using Excel 2003 and can manually export reports
and open them in Excel without any problems, yet when I use a subscription to
email the Excel files they are not valid files. They have the .xls extension,
but Excel can't open them properly, it just opens them as a load of
unintelligible text.
The same thing happens when I set up a subscription to email a report in XML
format, I could export it manually and it worked fine, yet when I run the
subscription to email the XML report to me I get an invalid XML file.
Has anyone else had this problem or does anyone know how to fix it?
If it helps, the report server is stored on a network server, not my local PC.
The server is running Windows Server 2003 Pro, my PC is running Windows XP
Pro and has Office 2003. Office is not installed on the server, but I doubt
that makes any difference, since I can manually export to Excel without any
problems.
Thanks in advance
TabbyWhen you run them "locally" due you mean from VS.NET on your workstation?
If yes... I found that generating reports "locally" can have totally
different results than running them on the report server.
If this is the case...try loading the report via a browser from the report
server.
If this is not the case... try pdf/html or other output formats to narrow
down the problem. I found problems exporting to excel but not other formats
when report objects are overlapping.
Tabby Cool wrote:
>Hi,
>I'm having a problem with Reporting Services report subscriptions.
>I have a set of reports that need to be sent via email on a schedule. I need
>to send them in Excel format, but the files sent to my email account are
>unreadable in Excel. I am using Excel 2003 and can manually export reports
>and open them in Excel without any problems, yet when I use a subscription to
>email the Excel files they are not valid files. They have the .xls extension,
>but Excel can't open them properly, it just opens them as a load of
>unintelligible text.
>The same thing happens when I set up a subscription to email a report in XML
>format, I could export it manually and it worked fine, yet when I run the
>subscription to email the XML report to me I get an invalid XML file.
>Has anyone else had this problem or does anyone know how to fix it?
>If it helps, the report server is stored on a network server, not my local PC.
>The server is running Windows Server 2003 Pro, my PC is running Windows XP
>Pro and has Office 2003. Office is not installed on the server, but I doubt
>that makes any difference, since I can manually export to Excel without any
>problems.
>Thanks in advance
>Tabby
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1|||No, I am not running htem from Visual Studio, I am running them direct from
the Report Manager.
When you run reports in Visual Studio, can you actually export them? I
thought you could only preview them, there is no option to export a report
from the preview screen.
I have tried exporting them to both XML and Excel, I can do this perfectly
from the Report Manager, yet when I use a subscription to email the reports
to me in either of these formats the files are unusable.
William N wrote:
>When you run them "locally" due you mean from VS.NET on your workstation?
>If yes... I found that generating reports "locally" can have totally
>different results than running them on the report server.
>If this is the case...try loading the report via a browser from the report
>server.
>If this is not the case... try pdf/html or other output formats to narrow
>down the problem. I found problems exporting to excel but not other formats
>when report objects are overlapping.
>>Hi,
>[quoted text clipped - 23 lines]
>>Tabby|||I have tried exporting to PDF, again, this works fine when performed manually,
but the email attachments from the subscription are not valid PDFs and can't
be opened by Adobe Acrobat.
It seems there is something different with the export procedure when using a
subscription as opposed to exporting manually, I don't understand why it
would work perfectly one way and not the other.|||Seems like the report is not getting the the data?
Are you saving the credentials in your data connection?
Tabby Cool wrote:
>I have tried exporting to PDF, again, this works fine when performed manually,
>but the email attachments from the subscription are not valid PDFs and can't
>be opened by Adobe Acrobat.
>It seems there is something different with the export procedure when using a
>subscription as opposed to exporting manually, I don't understand why it
>would work perfectly one way and not the other.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1|||Yes, the credentials are stored in the data source.
William N wrote:
>Seems like the report is not getting the the data?
>Are you saving the credentials in your data connection?
>>I have tried exporting to PDF, again, this works fine when performed manually,
>>but the email attachments from the subscription are not valid PDFs and can't
>[quoted text clipped - 3 lines]
>>subscription as opposed to exporting manually, I don't understand why it
>>would work perfectly one way and not the other.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1|||Not sure what else to suggest.
I always try to simplify as much as possible first. Maybe try creating a
report with just a "Hello" text box and see if that works (no data sets or
anything else).
If it doesn't, then it's probably a server/install issue.
Good luck.
Tabby Cool wrote:
>Yes, the credentials are stored in the data source.
>>Seems like the report is not getting the the data?
>[quoted text clipped - 5 lines]
>>subscription as opposed to exporting manually, I don't understand why it
>>would work perfectly one way and not the other.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1
I'm having a problem with Reporting Services report subscriptions.
I have a set of reports that need to be sent via email on a schedule. I need
to send them in Excel format, but the files sent to my email account are
unreadable in Excel. I am using Excel 2003 and can manually export reports
and open them in Excel without any problems, yet when I use a subscription to
email the Excel files they are not valid files. They have the .xls extension,
but Excel can't open them properly, it just opens them as a load of
unintelligible text.
The same thing happens when I set up a subscription to email a report in XML
format, I could export it manually and it worked fine, yet when I run the
subscription to email the XML report to me I get an invalid XML file.
Has anyone else had this problem or does anyone know how to fix it?
If it helps, the report server is stored on a network server, not my local PC.
The server is running Windows Server 2003 Pro, my PC is running Windows XP
Pro and has Office 2003. Office is not installed on the server, but I doubt
that makes any difference, since I can manually export to Excel without any
problems.
Thanks in advance
TabbyWhen you run them "locally" due you mean from VS.NET on your workstation?
If yes... I found that generating reports "locally" can have totally
different results than running them on the report server.
If this is the case...try loading the report via a browser from the report
server.
If this is not the case... try pdf/html or other output formats to narrow
down the problem. I found problems exporting to excel but not other formats
when report objects are overlapping.
Tabby Cool wrote:
>Hi,
>I'm having a problem with Reporting Services report subscriptions.
>I have a set of reports that need to be sent via email on a schedule. I need
>to send them in Excel format, but the files sent to my email account are
>unreadable in Excel. I am using Excel 2003 and can manually export reports
>and open them in Excel without any problems, yet when I use a subscription to
>email the Excel files they are not valid files. They have the .xls extension,
>but Excel can't open them properly, it just opens them as a load of
>unintelligible text.
>The same thing happens when I set up a subscription to email a report in XML
>format, I could export it manually and it worked fine, yet when I run the
>subscription to email the XML report to me I get an invalid XML file.
>Has anyone else had this problem or does anyone know how to fix it?
>If it helps, the report server is stored on a network server, not my local PC.
>The server is running Windows Server 2003 Pro, my PC is running Windows XP
>Pro and has Office 2003. Office is not installed on the server, but I doubt
>that makes any difference, since I can manually export to Excel without any
>problems.
>Thanks in advance
>Tabby
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1|||No, I am not running htem from Visual Studio, I am running them direct from
the Report Manager.
When you run reports in Visual Studio, can you actually export them? I
thought you could only preview them, there is no option to export a report
from the preview screen.
I have tried exporting them to both XML and Excel, I can do this perfectly
from the Report Manager, yet when I use a subscription to email the reports
to me in either of these formats the files are unusable.
William N wrote:
>When you run them "locally" due you mean from VS.NET on your workstation?
>If yes... I found that generating reports "locally" can have totally
>different results than running them on the report server.
>If this is the case...try loading the report via a browser from the report
>server.
>If this is not the case... try pdf/html or other output formats to narrow
>down the problem. I found problems exporting to excel but not other formats
>when report objects are overlapping.
>>Hi,
>[quoted text clipped - 23 lines]
>>Tabby|||I have tried exporting to PDF, again, this works fine when performed manually,
but the email attachments from the subscription are not valid PDFs and can't
be opened by Adobe Acrobat.
It seems there is something different with the export procedure when using a
subscription as opposed to exporting manually, I don't understand why it
would work perfectly one way and not the other.|||Seems like the report is not getting the the data?
Are you saving the credentials in your data connection?
Tabby Cool wrote:
>I have tried exporting to PDF, again, this works fine when performed manually,
>but the email attachments from the subscription are not valid PDFs and can't
>be opened by Adobe Acrobat.
>It seems there is something different with the export procedure when using a
>subscription as opposed to exporting manually, I don't understand why it
>would work perfectly one way and not the other.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1|||Yes, the credentials are stored in the data source.
William N wrote:
>Seems like the report is not getting the the data?
>Are you saving the credentials in your data connection?
>>I have tried exporting to PDF, again, this works fine when performed manually,
>>but the email attachments from the subscription are not valid PDFs and can't
>[quoted text clipped - 3 lines]
>>subscription as opposed to exporting manually, I don't understand why it
>>would work perfectly one way and not the other.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1|||Not sure what else to suggest.
I always try to simplify as much as possible first. Maybe try creating a
report with just a "Hello" text box and see if that works (no data sets or
anything else).
If it doesn't, then it's probably a server/install issue.
Good luck.
Tabby Cool wrote:
>Yes, the credentials are stored in the data source.
>>Seems like the report is not getting the the data?
>[quoted text clipped - 5 lines]
>>subscription as opposed to exporting manually, I don't understand why it
>>would work perfectly one way and not the other.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200509/1
Problems executing DML via linked server
Hey folks..
I have a linked server that connects to a DB2 database through the OLE DB
for ODBC Providers driver. I have a DSN created and can, through the linked
server run SELECT statements to my heart's content. When I want to run a
simple DELETE, for example (DELETE HCEDB.APPLQUE2), I get the following
error.
Server: Msg 7345, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' could not delete from table '"HCEDB"."APPLQUE2"'.
User did not have sufficient permission to delete the row.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.]
[OLE/DB provider returned message: [IBM][CLI Driver] CLI0150E Driver not
capable. SQLSTATE=S1C00]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::DeleteRows
returned 0x80040e21: DBROWSTATUS_E_PERMISSIONDENIED].
I have gone to a colleague's machine to use a DB2 Client and authenticated
using the same user specified in both the DSN and Linked Server and was able
to execute the DELETE. Any ideas as to cause and resolution?
Thanks..
Peace,
Gary HampsonIf you are using a DELETE FROM (Table Name), beware.
I was doing the same thing on a DB2 Database with some test data given to me
, and I would get the same error when I tried to delete using the Linked Ser
ver, where if I tried to do it straight in the DB2 Console, I could execute
without any problems.
So I ran an Event Monitor. Happens out, the Microsoft OLE DB Provider for OD
BC Drivers turns it into count(*) many "DELETE FROM TABLE WHERE field = ?".
So if your table has 1000 rows, you'll have 1000 Delete Statements run in se
quence. Very inefficent, but it gets the job done. The reason for my error w
as that there were 3 rows that were identical to each other, and so it had 3
identical "DELETE FROM" statements.
My solution: Deleted the replicated rows. It apparantly was an oversight whe
n creating the test data.
I still have an issue with it producing all those Delete Statements though.
I'm currently looking into other ways of doing it.
I have a linked server that connects to a DB2 database through the OLE DB
for ODBC Providers driver. I have a DSN created and can, through the linked
server run SELECT statements to my heart's content. When I want to run a
simple DELETE, for example (DELETE HCEDB.APPLQUE2), I get the following
error.
Server: Msg 7345, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' could not delete from table '"HCEDB"."APPLQUE2"'.
User did not have sufficient permission to delete the row.
[OLE/DB provider returned message: Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done.]
[OLE/DB provider returned message: [IBM][CLI Driver] CLI0150E Driver not
capable. SQLSTATE=S1C00]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::DeleteRows
returned 0x80040e21: DBROWSTATUS_E_PERMISSIONDENIED].
I have gone to a colleague's machine to use a DB2 Client and authenticated
using the same user specified in both the DSN and Linked Server and was able
to execute the DELETE. Any ideas as to cause and resolution?
Thanks..
Peace,
Gary HampsonIf you are using a DELETE FROM (Table Name), beware.
I was doing the same thing on a DB2 Database with some test data given to me
, and I would get the same error when I tried to delete using the Linked Ser
ver, where if I tried to do it straight in the DB2 Console, I could execute
without any problems.
So I ran an Event Monitor. Happens out, the Microsoft OLE DB Provider for OD
BC Drivers turns it into count(*) many "DELETE FROM TABLE WHERE field = ?".
So if your table has 1000 rows, you'll have 1000 Delete Statements run in se
quence. Very inefficent, but it gets the job done. The reason for my error w
as that there were 3 rows that were identical to each other, and so it had 3
identical "DELETE FROM" statements.
My solution: Deleted the replicated rows. It apparantly was an oversight whe
n creating the test data.
I still have an issue with it producing all those Delete Statements though.
I'm currently looking into other ways of doing it.
Problems due to @@IDENTITY
I have a database that sits on SQL Server 2000 and the client is Access XP
connecting to SQL via ODBC.
The database has been in use since November 2003 and all has been working
well. However......over the past w
a strange thing has been happening
that, when new customer details were entered and the record was saved, the
displayed record would change to that of another although the new record had
been saved to the table.
At first I thought it was Access playing around (bless it) but have since
discovered the true cause. Whenever a new customer is entered, a SQL
trigger fires that will also create a dummy record in another table
(tbl_MainCaseEntry) ready for the user to enter details. There is an
essential reason for the trigger but it is too long winded to explain why.
The trigger reads:
CREATE TRIGGER trg_NewCustomer
ON tbl_Customer
FOR INSERT
AS
BEGIN
DECLARE @.CustID INT, @.CaseCount INT
SET @.CustID = (SELECT CustomerID FROM Inserted)
INSERT INTO tbl_MainCaseEntry (CustomerID) VALUES(@.CustID)
END
The field CustomerID in tbl_MainCaseEntry is the foreign key with the table
having it's own primary key of MainID (set as an identity field seeded
(1,1)).
I have since discovered via the Query Analyser that @.@.IDENTITY is pulling
back the last identity field for the entire statement (in this case the
MainID in tbl_MainCaseEntry for the new record created by the trigger).
Is there any way that I can stop this from happening by using
IDENT_CURRENT('tbl_Customer') and making sure that @.@.IDENTITY is forced back
to this value? I have tried to use SET @.@.IDENTITY =
IDENT_CURRENT('tbl_Customer') but this does not work.
Any advice would be appreciated as this is driving me mad!!
Regards
DazzaUse SCOPE_IDENTITY, not @.@.IDENTITY. This side effect is well documented.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Dazza" <Post2Group@.Only.com> wrote in message
news:#3683HKMFHA.1096@.tk2msftngp13.phx.gbl...
> I have a database that sits on SQL Server 2000 and the client is Access XP
> connecting to SQL via ODBC.
> The database has been in use since November 2003 and all has been working
> well. However......over the past w a strange thing has been
happening
> that, when new customer details were entered and the record was saved, the
> displayed record would change to that of another although the new record
had
> been saved to the table.
> At first I thought it was Access playing around (bless it) but have since
> discovered the true cause. Whenever a new customer is entered, a SQL
> trigger fires that will also create a dummy record in another table
> (tbl_MainCaseEntry) ready for the user to enter details. There is an
> essential reason for the trigger but it is too long winded to explain why.
> The trigger reads:
> CREATE TRIGGER trg_NewCustomer
> ON tbl_Customer
> FOR INSERT
> AS
> BEGIN
> DECLARE @.CustID INT, @.CaseCount INT
> SET @.CustID = (SELECT CustomerID FROM Inserted)
> INSERT INTO tbl_MainCaseEntry (CustomerID) VALUES(@.CustID)
> END
> The field CustomerID in tbl_MainCaseEntry is the foreign key with the
table
> having it's own primary key of MainID (set as an identity field seeded
> (1,1)).
> I have since discovered via the Query Analyser that @.@.IDENTITY is pulling
> back the last identity field for the entire statement (in this case the
> MainID in tbl_MainCaseEntry for the new record created by the trigger).
> Is there any way that I can stop this from happening by using
> IDENT_CURRENT('tbl_Customer') and making sure that @.@.IDENTITY is forced
back
> to this value? I have tried to use SET @.@.IDENTITY =
> IDENT_CURRENT('tbl_Customer') but this does not work.
> Any advice would be appreciated as this is driving me mad!!
> Regards
> Dazza
>|||Have you tried using SCOPE_IDENTITY already?
Look it up in BOL to learn more.
-Jason
"Dazza" <Post2Group@.Only.com> wrote in message
news:#3683HKMFHA.1096@.tk2msftngp13.phx.gbl...
> I have a database that sits on SQL Server 2000 and the client is Access XP
> connecting to SQL via ODBC.
> The database has been in use since November 2003 and all has been working
> well. However......over the past w a strange thing has been
happening
> that, when new customer details were entered and the record was saved, the
> displayed record would change to that of another although the new record
had
> been saved to the table.
> At first I thought it was Access playing around (bless it) but have since
> discovered the true cause. Whenever a new customer is entered, a SQL
> trigger fires that will also create a dummy record in another table
> (tbl_MainCaseEntry) ready for the user to enter details. There is an
> essential reason for the trigger but it is too long winded to explain why.
> The trigger reads:
> CREATE TRIGGER trg_NewCustomer
> ON tbl_Customer
> FOR INSERT
> AS
> BEGIN
> DECLARE @.CustID INT, @.CaseCount INT
> SET @.CustID = (SELECT CustomerID FROM Inserted)
> INSERT INTO tbl_MainCaseEntry (CustomerID) VALUES(@.CustID)
> END
> The field CustomerID in tbl_MainCaseEntry is the foreign key with the
table
> having it's own primary key of MainID (set as an identity field seeded
> (1,1)).
> I have since discovered via the Query Analyser that @.@.IDENTITY is pulling
> back the last identity field for the entire statement (in this case the
> MainID in tbl_MainCaseEntry for the new record created by the trigger).
> Is there any way that I can stop this from happening by using
> IDENT_CURRENT('tbl_Customer') and making sure that @.@.IDENTITY is forced
back
> to this value? I have tried to use SET @.@.IDENTITY =
> IDENT_CURRENT('tbl_Customer') but this does not work.
> Any advice would be appreciated as this is driving me mad!!
> Regards
> Dazza
>|||look up scope_identity in BOL
"Dazza" <Post2Group@.Only.com> wrote in message
news:%233683HKMFHA.1096@.tk2msftngp13.phx.gbl...
>I have a database that sits on SQL Server 2000 and the client is Access XP
>connecting to SQL via ODBC.
> The database has been in use since November 2003 and all has been working
> well. However......over the past w a strange thing has been
> happening that, when new customer details were entered and the record was
> saved, the displayed record would change to that of another although the
> new record had been saved to the table.
> At first I thought it was Access playing around (bless it) but have since
> discovered the true cause. Whenever a new customer is entered, a SQL
> trigger fires that will also create a dummy record in another table
> (tbl_MainCaseEntry) ready for the user to enter details. There is an
> essential reason for the trigger but it is too long winded to explain why.
> The trigger reads:
> CREATE TRIGGER trg_NewCustomer
> ON tbl_Customer
> FOR INSERT
> AS
> BEGIN
> DECLARE @.CustID INT, @.CaseCount INT
> SET @.CustID = (SELECT CustomerID FROM Inserted)
> INSERT INTO tbl_MainCaseEntry (CustomerID) VALUES(@.CustID)
> END
> The field CustomerID in tbl_MainCaseEntry is the foreign key with the
> table having it's own primary key of MainID (set as an identity field
> seeded (1,1)).
> I have since discovered via the Query Analyser that @.@.IDENTITY is pulling
> back the last identity field for the entire statement (in this case the
> MainID in tbl_MainCaseEntry for the new record created by the trigger).
> Is there any way that I can stop this from happening by using
> IDENT_CURRENT('tbl_Customer') and making sure that @.@.IDENTITY is forced
> back to this value? I have tried to use SET @.@.IDENTITY =
> IDENT_CURRENT('tbl_Customer') but this does not work.
> Any advice would be appreciated as this is driving me mad!!
> Regards
> Dazza
>|||I have looked in the BOL regards this feature but cannot understand it's
exact use. Where abouts in my trigger do I use SCOPE_IDENTITY() and what is
the syntax please?
All BOL seems to show is that it will display the identity of the table
where the focus starts (ie tbl_Customer) by using SELECT SCOPE_IDENTITY() AS
[SCOPE_IDENTITY].
Regards
Dazza
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uoXHLKKMFHA.1176@.TK2MSFTNGP15.phx.gbl...
> Use SCOPE_IDENTITY, not @.@.IDENTITY. This side effect is well documented.
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Dazza" <Post2Group@.Only.com> wrote in message
> news:#3683HKMFHA.1096@.tk2msftngp13.phx.gbl...
> happening
> had
> table
> back
>|||@.@.IDENTITY is fine to use within the trigger.
But if you want the calling code to return the identity value generated by
its INSERT statement (not the INSERT in the trigger), use SCOPE_IDENTITY()
in the calling code. http://www.aspfaq.com/2174
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Dazza" <Post2Group@.Only.com> wrote in message
news:#kky3OKMFHA.2384@.tk2msftngp13.phx.gbl...
> I have looked in the BOL regards this feature but cannot understand it's
> exact use. Where abouts in my trigger do I use SCOPE_IDENTITY() and what
is
> the syntax please?
> All BOL seems to show is that it will display the identity of the table
> where the focus starts (ie tbl_Customer) by using SELECT SCOPE_IDENTITY()
AS
> [SCOPE_IDENTITY].
> Regards
> Dazza
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:uoXHLKKMFHA.1176@.TK2MSFTNGP15.phx.gbl...
documented.
working
record
since
pulling
>|||I also note another problem here.
I guess you assume that inserts always happen in singleton? You need this
code to be multi-row aware. For example, watch what happens when you do
this:
INSERT Customers(col1, ..., colN)
SELECT 'col1', ..., colN
UNION
SELECT 'col1', ..., colN
To correct this, your INSERT statement inside the trigger should simply be:
INSERT tbl_MainCaseEntry (CustomerID)
SELECT CustomerID FROM Inserted
Or, change the calling stored procedure to handle the logging part of this,
and eliminate the need for a trigger at all. You do control access to this
table via stored procedures, right?
In any case, the calling app can't expect to get back a single @.@.IDENTITY or
SCOPE_IDENTITY() in the multi-row insert case.
(I also think you should consider dropping the superfluous tbl_ prefix, but
that's just an opinion.)
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Aaron
Many thanks for the advice.
I do not use SPs for the entry or retrieval of the data in forms as the
front-end uses linked tables. I have been working with SQL Server for about
a year (mostly admin) and still have a lot to learn regards development
methods for front-end access clients. The database in question is my first
real production development project but there are more to come I have been
told !!
Regards
Dazza
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23PMTcUKMFHA.2420@.TK2MSFTNGP12.phx.gbl...
>I also note another problem here.
>
> I guess you assume that inserts always happen in singleton? You need this
> code to be multi-row aware. For example, watch what happens when you do
> this:
> INSERT Customers(col1, ..., colN)
> SELECT 'col1', ..., colN
> UNION
> SELECT 'col1', ..., colN
> To correct this, your INSERT statement inside the trigger should simply
> be:
> INSERT tbl_MainCaseEntry (CustomerID)
> SELECT CustomerID FROM Inserted
> Or, change the calling stored procedure to handle the logging part of
> this,
> and eliminate the need for a trigger at all. You do control access to
> this
> table via stored procedures, right?
> In any case, the calling app can't expect to get back a single @.@.IDENTITY
> or
> SCOPE_IDENTITY() in the multi-row insert case.
> (I also think you should consider dropping the superfluous tbl_ prefix,
> but
> that's just an opinion.)
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
connecting to SQL via ODBC.
The database has been in use since November 2003 and all has been working
well. However......over the past w
a strange thing has been happeningthat, when new customer details were entered and the record was saved, the
displayed record would change to that of another although the new record had
been saved to the table.
At first I thought it was Access playing around (bless it) but have since
discovered the true cause. Whenever a new customer is entered, a SQL
trigger fires that will also create a dummy record in another table
(tbl_MainCaseEntry) ready for the user to enter details. There is an
essential reason for the trigger but it is too long winded to explain why.
The trigger reads:
CREATE TRIGGER trg_NewCustomer
ON tbl_Customer
FOR INSERT
AS
BEGIN
DECLARE @.CustID INT, @.CaseCount INT
SET @.CustID = (SELECT CustomerID FROM Inserted)
INSERT INTO tbl_MainCaseEntry (CustomerID) VALUES(@.CustID)
END
The field CustomerID in tbl_MainCaseEntry is the foreign key with the table
having it's own primary key of MainID (set as an identity field seeded
(1,1)).
I have since discovered via the Query Analyser that @.@.IDENTITY is pulling
back the last identity field for the entire statement (in this case the
MainID in tbl_MainCaseEntry for the new record created by the trigger).
Is there any way that I can stop this from happening by using
IDENT_CURRENT('tbl_Customer') and making sure that @.@.IDENTITY is forced back
to this value? I have tried to use SET @.@.IDENTITY =
IDENT_CURRENT('tbl_Customer') but this does not work.
Any advice would be appreciated as this is driving me mad!!
Regards
DazzaUse SCOPE_IDENTITY, not @.@.IDENTITY. This side effect is well documented.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Dazza" <Post2Group@.Only.com> wrote in message
news:#3683HKMFHA.1096@.tk2msftngp13.phx.gbl...
> I have a database that sits on SQL Server 2000 and the client is Access XP
> connecting to SQL via ODBC.
> The database has been in use since November 2003 and all has been working
> well. However......over the past w
a strange thing has beenhappening
> that, when new customer details were entered and the record was saved, the
> displayed record would change to that of another although the new record
had
> been saved to the table.
> At first I thought it was Access playing around (bless it) but have since
> discovered the true cause. Whenever a new customer is entered, a SQL
> trigger fires that will also create a dummy record in another table
> (tbl_MainCaseEntry) ready for the user to enter details. There is an
> essential reason for the trigger but it is too long winded to explain why.
> The trigger reads:
> CREATE TRIGGER trg_NewCustomer
> ON tbl_Customer
> FOR INSERT
> AS
> BEGIN
> DECLARE @.CustID INT, @.CaseCount INT
> SET @.CustID = (SELECT CustomerID FROM Inserted)
> INSERT INTO tbl_MainCaseEntry (CustomerID) VALUES(@.CustID)
> END
> The field CustomerID in tbl_MainCaseEntry is the foreign key with the
table
> having it's own primary key of MainID (set as an identity field seeded
> (1,1)).
> I have since discovered via the Query Analyser that @.@.IDENTITY is pulling
> back the last identity field for the entire statement (in this case the
> MainID in tbl_MainCaseEntry for the new record created by the trigger).
> Is there any way that I can stop this from happening by using
> IDENT_CURRENT('tbl_Customer') and making sure that @.@.IDENTITY is forced
back
> to this value? I have tried to use SET @.@.IDENTITY =
> IDENT_CURRENT('tbl_Customer') but this does not work.
> Any advice would be appreciated as this is driving me mad!!
> Regards
> Dazza
>|||Have you tried using SCOPE_IDENTITY already?
Look it up in BOL to learn more.
-Jason
"Dazza" <Post2Group@.Only.com> wrote in message
news:#3683HKMFHA.1096@.tk2msftngp13.phx.gbl...
> I have a database that sits on SQL Server 2000 and the client is Access XP
> connecting to SQL via ODBC.
> The database has been in use since November 2003 and all has been working
> well. However......over the past w
a strange thing has beenhappening
> that, when new customer details were entered and the record was saved, the
> displayed record would change to that of another although the new record
had
> been saved to the table.
> At first I thought it was Access playing around (bless it) but have since
> discovered the true cause. Whenever a new customer is entered, a SQL
> trigger fires that will also create a dummy record in another table
> (tbl_MainCaseEntry) ready for the user to enter details. There is an
> essential reason for the trigger but it is too long winded to explain why.
> The trigger reads:
> CREATE TRIGGER trg_NewCustomer
> ON tbl_Customer
> FOR INSERT
> AS
> BEGIN
> DECLARE @.CustID INT, @.CaseCount INT
> SET @.CustID = (SELECT CustomerID FROM Inserted)
> INSERT INTO tbl_MainCaseEntry (CustomerID) VALUES(@.CustID)
> END
> The field CustomerID in tbl_MainCaseEntry is the foreign key with the
table
> having it's own primary key of MainID (set as an identity field seeded
> (1,1)).
> I have since discovered via the Query Analyser that @.@.IDENTITY is pulling
> back the last identity field for the entire statement (in this case the
> MainID in tbl_MainCaseEntry for the new record created by the trigger).
> Is there any way that I can stop this from happening by using
> IDENT_CURRENT('tbl_Customer') and making sure that @.@.IDENTITY is forced
back
> to this value? I have tried to use SET @.@.IDENTITY =
> IDENT_CURRENT('tbl_Customer') but this does not work.
> Any advice would be appreciated as this is driving me mad!!
> Regards
> Dazza
>|||look up scope_identity in BOL
"Dazza" <Post2Group@.Only.com> wrote in message
news:%233683HKMFHA.1096@.tk2msftngp13.phx.gbl...
>I have a database that sits on SQL Server 2000 and the client is Access XP
>connecting to SQL via ODBC.
> The database has been in use since November 2003 and all has been working
> well. However......over the past w
a strange thing has been> happening that, when new customer details were entered and the record was
> saved, the displayed record would change to that of another although the
> new record had been saved to the table.
> At first I thought it was Access playing around (bless it) but have since
> discovered the true cause. Whenever a new customer is entered, a SQL
> trigger fires that will also create a dummy record in another table
> (tbl_MainCaseEntry) ready for the user to enter details. There is an
> essential reason for the trigger but it is too long winded to explain why.
> The trigger reads:
> CREATE TRIGGER trg_NewCustomer
> ON tbl_Customer
> FOR INSERT
> AS
> BEGIN
> DECLARE @.CustID INT, @.CaseCount INT
> SET @.CustID = (SELECT CustomerID FROM Inserted)
> INSERT INTO tbl_MainCaseEntry (CustomerID) VALUES(@.CustID)
> END
> The field CustomerID in tbl_MainCaseEntry is the foreign key with the
> table having it's own primary key of MainID (set as an identity field
> seeded (1,1)).
> I have since discovered via the Query Analyser that @.@.IDENTITY is pulling
> back the last identity field for the entire statement (in this case the
> MainID in tbl_MainCaseEntry for the new record created by the trigger).
> Is there any way that I can stop this from happening by using
> IDENT_CURRENT('tbl_Customer') and making sure that @.@.IDENTITY is forced
> back to this value? I have tried to use SET @.@.IDENTITY =
> IDENT_CURRENT('tbl_Customer') but this does not work.
> Any advice would be appreciated as this is driving me mad!!
> Regards
> Dazza
>|||I have looked in the BOL regards this feature but cannot understand it's
exact use. Where abouts in my trigger do I use SCOPE_IDENTITY() and what is
the syntax please?
All BOL seems to show is that it will display the identity of the table
where the focus starts (ie tbl_Customer) by using SELECT SCOPE_IDENTITY() AS
[SCOPE_IDENTITY].
Regards
Dazza
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uoXHLKKMFHA.1176@.TK2MSFTNGP15.phx.gbl...
> Use SCOPE_IDENTITY, not @.@.IDENTITY. This side effect is well documented.
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Dazza" <Post2Group@.Only.com> wrote in message
> news:#3683HKMFHA.1096@.tk2msftngp13.phx.gbl...
> happening
> had
> table
> back
>|||@.@.IDENTITY is fine to use within the trigger.
But if you want the calling code to return the identity value generated by
its INSERT statement (not the INSERT in the trigger), use SCOPE_IDENTITY()
in the calling code. http://www.aspfaq.com/2174
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Dazza" <Post2Group@.Only.com> wrote in message
news:#kky3OKMFHA.2384@.tk2msftngp13.phx.gbl...
> I have looked in the BOL regards this feature but cannot understand it's
> exact use. Where abouts in my trigger do I use SCOPE_IDENTITY() and what
is
> the syntax please?
> All BOL seems to show is that it will display the identity of the table
> where the focus starts (ie tbl_Customer) by using SELECT SCOPE_IDENTITY()
AS
> [SCOPE_IDENTITY].
> Regards
> Dazza
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:uoXHLKKMFHA.1176@.TK2MSFTNGP15.phx.gbl...
documented.
working
record
since
pulling
>|||I also note another problem here.
I guess you assume that inserts always happen in singleton? You need this
code to be multi-row aware. For example, watch what happens when you do
this:
INSERT Customers(col1, ..., colN)
SELECT 'col1', ..., colN
UNION
SELECT 'col1', ..., colN
To correct this, your INSERT statement inside the trigger should simply be:
INSERT tbl_MainCaseEntry (CustomerID)
SELECT CustomerID FROM Inserted
Or, change the calling stored procedure to handle the logging part of this,
and eliminate the need for a trigger at all. You do control access to this
table via stored procedures, right?
In any case, the calling app can't expect to get back a single @.@.IDENTITY or
SCOPE_IDENTITY() in the multi-row insert case.
(I also think you should consider dropping the superfluous tbl_ prefix, but
that's just an opinion.)
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Aaron
Many thanks for the advice.
I do not use SPs for the entry or retrieval of the data in forms as the
front-end uses linked tables. I have been working with SQL Server for about
a year (mostly admin) and still have a lot to learn regards development
methods for front-end access clients. The database in question is my first
real production development project but there are more to come I have been
told !!
Regards
Dazza
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23PMTcUKMFHA.2420@.TK2MSFTNGP12.phx.gbl...
>I also note another problem here.
>
> I guess you assume that inserts always happen in singleton? You need this
> code to be multi-row aware. For example, watch what happens when you do
> this:
> INSERT Customers(col1, ..., colN)
> SELECT 'col1', ..., colN
> UNION
> SELECT 'col1', ..., colN
> To correct this, your INSERT statement inside the trigger should simply
> be:
> INSERT tbl_MainCaseEntry (CustomerID)
> SELECT CustomerID FROM Inserted
> Or, change the calling stored procedure to handle the logging part of
> this,
> and eliminate the need for a trigger at all. You do control access to
> this
> table via stored procedures, right?
> In any case, the calling app can't expect to get back a single @.@.IDENTITY
> or
> SCOPE_IDENTITY() in the multi-row insert case.
> (I also think you should consider dropping the superfluous tbl_ prefix,
> but
> that's just an opinion.)
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
Friday, March 23, 2012
Problems creating full-text population schedule via Management Studio
I am trying to schedule an hourly incremental update of the FT catalog on an indexed query. I have tried creating the catalog and schedule on both a remote machine (my workstation) and directly on the server. I get different errors on the two machines. I have tried creating the scheduled updates during the catalog creation process as well as seperately. I am able to create the catalog and update it manually in both cases. I cannot get the schedules created. The text of the errors are as follows:
NOTE: We run on an alternate port
From my workstation during the catalog creation process:
NOTE: We run on an alternate port
From my workstation during the catalog creation process:
===================================
Create full-text population schedule failed.
===================================
Apply to target server failed for Job 'Start Incremental View Population on SyllabiDBI.qry_FTSearch'. (Microsoft.SqlServer.Smo)
For help, click:
removed
Program Location:
at Microsoft.SqlServer.Management.Smo.Agent.Job.ApplyToTargetServer(String serverName)
at Microsoft.SqlServer.Management.SqlManagerUI.FullTextIndexPopulationSchedule.ApplyIndexScheduleChanges(Server server, String databaseName, String tableName, String schemaName)
at Microsoft.SqlServer.Management.SqlManagerUI.FullTextIndexScheduleData.ApplyChanges(Server server, ServerConnection sqlConnInfo, FullTextIndexPopulationScheduleList scheduleList)
at Microsoft.SqlServer.Management.SqlManagerUI.FullTextWizardForm.PerformActions()
===================================
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Agent.Job.ApplyToTargetServer(String serverName)
===================================
The specified @.server_name ('XXX.XXX.XXX.XXX,0000') does not exist. (.Net SqlClient Data Provider)
For help, click:
removed
Server Name: XXX.XXX.XXX.XXX,0000
Error Number: 14262
Severity: 16
State: 1
Procedure: sp_add_jobserver
Line Number: 88
Program Location:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
Upon recieveing that error, the catalog is created but contains no schedule population events. Trying to add the population job via the FT properties dialog results in the following (which is almost identical to the previous):
===================================
Cannot execute changes.
===================================
Apply to target server failed for Job 'Start Incremental Table Population on SyllabiDBI.qry_FTSearch'. (Microsoft.SqlServer.Smo)
For help, click:
removed
Program Location:
at Microsoft.SqlServer.Management.Smo.Agent.Job.ApplyToTargetServer(String serverName)
at Microsoft.SqlServer.Management.SqlManagerUI.FullTextIndexPopulationSchedule.ApplyIndexScheduleChanges(Server server, String databaseName, String tableName, String schemaName)
at Microsoft.SqlServer.Management.SqlManagerUI.FullTextIndexScheduleData.ApplyChanges(Server server, ServerConnection sqlConnInfo, FullTextIndexPopulationScheduleList scheduleList)
at Microsoft.SqlServer.Management.SqlManagerUI.FullTextIndexPropertiesSchedule.OnRunNow(Object sender)
===================================
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Agent.Job.ApplyToTargetServer(String serverName)
===================================
The specified @.server_name ('XXX.XXX.XXX.XXX,8081') does not exist. (.Net SqlClient Data Provider)
For help, click:
removed
Server Name: XXX.XXX.XXX.XXX,8081
Error Number: 14262
Severity: 16
State: 1
Procedure: sp_add_jobserver
Line Number: 88
Program Location:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
If I immediately try to creat it again using the same method, it successfully creates the scheduled item and job but the job is malformed and will not run:
===================================
Start failed for Job 'Start Incremental Table Population on SyllabiDBI.qry_FTSearch'. (Microsoft.SqlServer.Smo)
For help, click:
removed
Program Location:
at Microsoft.SqlServer.Management.Smo.Agent.Job.Start()
at Microsoft.SqlServer.Management.SqlManagerUI.StartAgentJobs.StartJobAction.DoAction(ActionCollection actions, Int32 index)
at Microsoft.SqlServer.Management.SqlManagerUI.ActionCollection.DoWorkOnThread()
===================================
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Agent.Job.StartImpl(String jobStepName)
at Microsoft.SqlServer.Management.Smo.Agent.Job.Start()
===================================
Cannot start the job "Start Incremental Table Population on SyllabiDBI.qry_FTSearch" (ID 24C9505F-1388-46AD-AD4B-3024A8D3D154) because it does not have any job server or servers defined. Associate the job with a job server by calling sp_add_jobserver. (.Net SqlClient Data Provider)
For help, click:
removed
Server Name: XXX.XXX.XXX.XXX,0000
Error Number: 14256
Severity: 16
State: 1
Procedure: sp_start_job
Line Number: 51
Program Location:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
Now, on the server itself, I recieve the follwoing when trying to create the schedule via the initial catalog setup:
===================================
Create full-text population schedule failed.
===================================
Object reference not set to an instance of an object. (SqlManagerUI)
Program Location:
at Microsoft.SqlServer.Management.SqlManagerUI.FullTextIndexPopulationSchedule.ApplyIndexScheduleChanges(Server server, String databaseName, String tableName, String schemaName)
at Microsoft.SqlServer.Management.SqlManagerUI.FullTextIndexScheduleData.ApplyChanges(Server server, ServerConnection sqlConnInfo, FullTextIndexPopulationScheduleList scheduleList)
at Microsoft.SqlServer.Management.SqlManagerUI.FullTextWizardForm.PerformActions()
===================================
Object reference not set to an instance of an object. (SqlManagerUI)
Program Location:
at Microsoft.SqlServer.Management.SqlManagerUI.FullTextIndexPopulationSchedule.ApplyIndexScheduleChanges(Server server, String databaseName, String tableName, String schemaName)
at Microsoft.SqlServer.Management.SqlManagerUI.FullTextIndexScheduleData.ApplyChanges(Server server, ServerConnection sqlConnInfo, FullTextIndexPopulationScheduleList scheduleList)
at Microsoft.SqlServer.Management.SqlManagerUI.FullTextIndexPropertiesSchedule.OnRunNow(Object sender)
at Microsoft.SqlServer.Management.SqlMgmt.PanelExecutionHandler.Run(RunType runType, Object sender)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.DoPreProcessExecutionAndRunViews(RunType runType)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.ExecuteForSql(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.Microsoft.SqlServer.Management.SqlMgmt.IExecutionAwareSqlControlCollection.PreProcessExecution(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.RunNow(RunType runType, Object sender)
===================================
Start failed for Job 'Start Incremental View Population on SyllabiDBI.qry_FTSearch'. (Microsoft.SqlServer.Smo)
For help, click:
removed
Program Location:
at Microsoft.SqlServer.Management.Smo.Agent.Job.Start()
at Microsoft.SqlServer.Management.SqlManagerUI.StartAgentJobs.StartJobAction.DoAction(ActionCollection actions, Int32 index)
at Microsoft.SqlServer.Management.SqlManagerUI.ActionCollection.DoWorkOnThread()
===================================
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Agent.Job.StartImpl(String jobStepName)
at Microsoft.SqlServer.Management.Smo.Agent.Job.Start()
===================================
SQLServerAgent Error: Request to run job Start Incremental View Population on SyllabiDBI.qry_FTSearch (from User XXX\Administrator) refused because the job has no job steps. (.Net SqlClient Data Provider)
For help, click:
removed
Server Name: XXX
Error Number: 22022
Severity: 16
State: 1
Program Location:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
I believe it is a problem with the Management Studio IDE, but I can't be sure. So far, I have been unable to find another way to create the schedule or jobs. If I can create the jobs and schedule them via T-SQL I will happily do so.
Any insight is greatly appreciated.
JC
I would like to know how you created this job, that is having this issue. It should work fine if you created under SQLAgent node.
Programatically you can use SMO objects (Look for JobServer in BOL)or agent SPs (look for sp_add_job and other related sps in BOL) to get this done
Thanks,
Gops Dwarak
sqlWednesday, March 21, 2012
Problems Connecting to Database
I am having a hard time getting my registration form to connect to my database via a stored procedure. Any help would be appreciated. The code is listed below
protectedvoid submit_Click(object sender,EventArgs e){
SqlConnection connection =newSqlConnection(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString);
SqlCommand command =newSqlCommand("GE_sp_INSERT_USER", connection);
command.Parameters.Add("@.FirstName",SqlDbType.NVarChar).Value = txtFirstName.Text;command.Parameters.Add("@.LastName",SqlDbType.NVarChar).Value = txtLastName.Text;
command.Parameters.Add("@.UserName",SqlDbType.NVarChar).Value = txtUserName.Text;command.Parameters.Add("@.Password",SqlDbType.NVarChar).Value = txtPassword.Text;
command.Parameters.Add("@.PhoneNumber",SqlDbType.NVarChar).Value = txtPhoneNumber.Text;command.Parameters.Add("@.EmailAddress",SqlDbType.NVarChar).Value = txtEmailAddress.Text;
command.Parameters.Add("@.Address",SqlDbType.NVarChar).Value = txtAddress.Text;command.Parameters.Add("@.City",SqlDbType.NVarChar).Value = txtCity.Text;
command.Parameters.Add("@.State",SqlDbType.NVarChar).Value = txtState.Text;command.Parameters.Add("@.ZipCode",SqlDbType.NVarChar).Value = txtZipCode.Text;connection.Open();
command.ExecuteNonQuery();
connection.Dispose();
}
ALTER PROCEDUREdbo.GE_sp_INSERT_USER
(
@.FirstNamenvarchar(50),@.LastNamenvarchar(50),
@.UserNamenvarchar(50),@.Passwordnvarchar(50),
@.PhoneNumbernvarchar(50),@.EmailAddressnvarchar(50),
@.Addressnvarchar(50),@.Citynvarchar(50),
@.Statenvarchar(50),@.ZipCodenvarchar(50))
AS
INSERT INTOUSERS(FirstName, LastName, UserName, Password, PhoneNumber, EmailAddress, Address, City, State, ZipCode)
VALUES
(@.FirstName, @.LastName, @.UserName, @.Password, @.PhoneNumber, @.EmailAddress, @.Address, @.City, @.State, @.ZipCode)
RETURN
You say you are having a hard time "connecting" to your database. What exactly is the problem you're having? Are you getting an error?
|||You need to set the CommandType:
command.CommandType =CommandType.StoredProcedure;
|||I have added the code below to my code - I placed it right below my sqlCommand. - it still does not work. the error says it cannot find the stored procedure. The webconfig looks correct.
SqlCommand command =newSqlCommand("GE_sp_INSERT_USER", connection);command.CommandType =CommandType.StoredProcedure;
thanks for your help|||That error message has meaning.
Try this:
SqlCommand command =newSqlCommand("dbo.GE_sp_INSERT_USER", connection);
|||
That did not work either. Below is my webconfig - I may be missing something easy.
<connectionStrings>
<addname="myConnectionString"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\gevjen.mdf;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient"/></connectionStrings>
thanks again for sticking with this to help me out...
|||A few things
1) Is this connection string working somewhere else in your application?
2) If you connect to your db manually and execute the command do you get any result?
|||hey i am too getting a hard time in making connection to sqlserver,like i have made a connection object and its working but when i reach the open function nothing works,i had been working over this since last 4 days!please guide me how to do it!
|||The database works elsewhere and the stored procedure works as well. I am missing something easy I believe within the connection or webconfig.|||
gevjen:
The database works elsewhere and the stored procedure works as well. I am missing something easy I believe within the connection or webconfig.
Could you copy and paste the actual error message, and the show the line that causes it?
|||
hey i am too getting a hard time in making connection to sqlserver...
This should help you:http://www.mikesdotnetting.com/Article.aspx?ArticleID=69
sql
Problems connecting to "(LOCAL)" from SSIS
I've recently moved from June CTP to RTM. I'm now having a problem connecting to the (LOCAL) server in SSIS via a Native OLE DB\Microsoft OLE DB Provider for SQL Server connection. The error is:
Test connection failed because of an error in initializing provider. [DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or access denied.
I'm connecting with Windows Authentication, and I can connect to (LOCAL) with Windows Authentication via Management Studio. If I change the server name to the actual name of my development box then it will connect. I'd like to use (LOCAL) though so it's easily portable between different development environments.
Anyone have any ideas?This is not an SSIS problem; the underlying OLE DB provider is evaluating server names.
Have you tried with "localhost" or "."?
Thanks.|||This doesn't appear to work unless you have TCP/IP enabled. It is disabled by default. It seems that "(local)" no longer forces the use of Shared Memory.
-Doug
Monday, March 12, 2012
Problems accessing CreateSubscription via API
Hi,
I'm trying to create a subscription via API and am getting an error. BTW, creating it through ReportManager works great, meaning the Report, the DataSource and its credientials are all working as planned.
I'm following the sample for CreateSubscription() on the books online, http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createsubscription.aspx
I'm hosting the script on a web page and when running, am getting an error:
"One of the extension parameters is not valid for the following reason: The account you are using does not have administrator privileges. A subscription cannot be created fordank@.adventure-works.com"
Well, obvisouly it's not working. Moreover, I don't understand the error, what does the 'TO' email to do with it?
Please help,
Edmund
Oops, I fogot to mention that this occurs only when authoenticated under ASP.NET 2.0's membership forms authentication.
Edmund
Problem: Stored Procedures not completing from Web Application
I have several stored procedures that run fine from my SQL Server
database (via the exec command.), though when I call these procedures
from my web application, they do not complete. I have other
procedures that in fact do run fine through my web application though,
so I do not believe its a front-end problem. The procedures only take
about 30 seconds to run from the back-end, so I know its not a time
out issue as well. Does anyone have any ideas?
database (via the exec command.), though when I call these procedures
from my web application, they do not complete. I have other
procedures that in fact do run fine through my web application though,
so I do not believe its a front-end problem. The procedures only take
about 30 seconds to run from the back-end, so I know its not a time
out issue as well. Does anyone have any ideas?
Thanks in advance."Mike J" <mjewett_2000@.yahoo.com> wrote in message
news:1a9d60fa.0404261152.322ed838@.posting.google.c om...
> I have several stored procedures that run fine from my SQL Server
> database (via the exec command.), though when I call these procedures
> from my web application, they do not complete. I have other
> procedures that in fact do run fine through my web application though,
> so I do not believe its a front-end problem. The procedures only take
> about 30 seconds to run from the back-end, so I know its not a time
> out issue as well. Does anyone have any ideas?
> Thanks in advance.
Can you clarify what you mean by "does not complete"? Do you get an error
message? What version of MSSQL? What's the web platform, eg. ASP, PHP etc.?
If it works fine from Query Analyzer then that suggests an issue on the web
side, but without more information it's hard to say.
Simon|||Mike J (mjewett_2000@.yahoo.com) writes:
> I have several stored procedures that run fine from my SQL Server
> database (via the exec command.), though when I call these procedures
> from my web application, they do not complete. I have other
> procedures that in fact do run fine through my web application though,
> so I do not believe its a front-end problem. The procedures only take
> about 30 seconds to run from the back-end, so I know its not a time
> out issue as well. Does anyone have any ideas?
30 seconds is the default time-out, so timeout problems cannot be ruled
out completely. Do you have proper error handling in your web app?
There are a couple of possible reasons for this, but since you provided
very little information about your code, I can only give some general
comments.
Do you use indexed views or indexes on computed columns? In such case,
you need to issue SET ARITHABORT ON when you connect from your web app
(or make this default for the database with ALTER DATABASE). This setting
is on by default when you run from Query Analyzer.
Even if you don't use indexed views or indexed computed columns, SET
ARITHABORT ON, can still be useful, as you now will get the same plan
as Query Analyzer does.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<408d7223$1_3@.news.bluewin.ch>...
> "Mike J" <mjewett_2000@.yahoo.com> wrote in message
> news:1a9d60fa.0404261152.322ed838@.posting.google.c om...
> > I have several stored procedures that run fine from my SQL Server
> > database (via the exec command.), though when I call these procedures
> > from my web application, they do not complete. I have other
> > procedures that in fact do run fine through my web application though,
> > so I do not believe its a front-end problem. The procedures only take
> > about 30 seconds to run from the back-end, so I know its not a time
> > out issue as well. Does anyone have any ideas?
> > Thanks in advance.
> Can you clarify what you mean by "does not complete"? Do you get an error
> message? What version of MSSQL? What's the web platform, eg. ASP, PHP etc.?
> If it works fine from Query Analyzer then that suggests an issue on the web
> side, but without more information it's hard to say.
> Simon
Simon,
Here's some more elaboration. By "does not complete" I mean that the
procedures simply never finish running. They generate no error
messages, and do not lock any objects -- they just dont finish. I
verify this by manually running the procedures in query analyzer
(where they finish quickly) and viewing the results. The strange
thing is that we use over 50 stored procedures, and almost all of them
work -- from both the web application and query analyzer. The couple
procedures that are "not finishing" are not any more complex than the
others.
The web platform we are using is Microsoft asp .NET. We are using SQL
Server 2000 as well.
Any ideas? Thanks.|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94D7EF10D40A3Yazorman@.127.0.0.1>...
> Mike J (mjewett_2000@.yahoo.com) writes:
> > I have several stored procedures that run fine from my SQL Server
> > database (via the exec command.), though when I call these procedures
> > from my web application, they do not complete. I have other
> > procedures that in fact do run fine through my web application though,
> > so I do not believe its a front-end problem. The procedures only take
> > about 30 seconds to run from the back-end, so I know its not a time
> > out issue as well. Does anyone have any ideas?
> 30 seconds is the default time-out, so timeout problems cannot be ruled
> out completely. Do you have proper error handling in your web app?
> There are a couple of possible reasons for this, but since you provided
> very little information about your code, I can only give some general
> comments.
> Do you use indexed views or indexes on computed columns? In such case,
> you need to issue SET ARITHABORT ON when you connect from your web app
> (or make this default for the database with ALTER DATABASE). This setting
> is on by default when you run from Query Analyzer.
> Even if you don't use indexed views or indexed computed columns, SET
> ARITHABORT ON, can still be useful, as you now will get the same plan
> as Query Analyzer does.
Erland, yes, we have proper error handling in our web application. We
in fact run many (around 50) stored procedures from our web
application and they complete just fine. The couple procedures that
do not run from the application are not any more complex than the
others.
To elaborate some more on our system, we are using Microsoft .NET, SQL
Server 2000. I have run many tests in query analyzer on our database
server with the procedures in question. They complete accurately and
quickly every time, with no errors. Its only when I call them from
the web app where do not finish. We do not use any indexed views or
computed columns, though I will try to ARITHABOR ON property anyhow.
Any other ideas?
Thanks.|||Mike J (mjewett_2000@.yahoo.com) writes:
> To elaborate some more on our system, we are using Microsoft .NET, SQL
> Server 2000. I have run many tests in query analyzer on our database
> server with the procedures in question. They complete accurately and
> quickly every time, with no errors. Its only when I call them from
> the web app where do not finish. We do not use any indexed views or
> computed columns, though I will try to ARITHABOR ON property anyhow.
> Any other ideas?
With that miniscule of information, no. Well, while you ruled out blocking
in another posting, one possibility is that you manage to block yourself
in the app. When you have a procedure which does not complete, execute
sp_who, and see if any process has a non-zero value in the Blk column.
If there is no blocking, use the Profiler to see where the process gets
stuck.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I had experienced issues with a store proc executing very quickly in Query Analyzer, but not from my ASP app against a development database. I noticed recently the same issue with the procedure not executing quickly in either QA or the web app.
After checking my table indexes on the dev database I found one joined table that had no index for the fields I was joining on. The index existed in the production database. Once adding that index. the procedure executed in < 1 second in QA and only a couple seconds from the web app.
Hope this helps anyone who's frustrated with similar issues.
Dave
Problem: Stored Procedures not completing from Web Application
I have several stored procedures that run fine from my SQL Server
database (via the exec command.), though when I call these procedures
from my web application, they do not complete. I have other
procedures that in fact do run fine through my web application though,
so I do not believe its a front-end problem. The procedures only take
about 30 seconds to run from the back-end, so I know its not a time
out issue as well. Does anyone have any ideas?
database (via the exec command.), though when I call these procedures
from my web application, they do not complete. I have other
procedures that in fact do run fine through my web application though,
so I do not believe its a front-end problem. The procedures only take
about 30 seconds to run from the back-end, so I know its not a time
out issue as well. Does anyone have any ideas?
Thanks in advance."Mike J" <mjewett_2000@.yahoo.com> wrote in message
news:1a9d60fa.0404261152.322ed838@.posting.google.c om...
> I have several stored procedures that run fine from my SQL Server
> database (via the exec command.), though when I call these procedures
> from my web application, they do not complete. I have other
> procedures that in fact do run fine through my web application though,
> so I do not believe its a front-end problem. The procedures only take
> about 30 seconds to run from the back-end, so I know its not a time
> out issue as well. Does anyone have any ideas?
> Thanks in advance.
Can you clarify what you mean by "does not complete"? Do you get an error
message? What version of MSSQL? What's the web platform, eg. ASP, PHP etc.?
If it works fine from Query Analyzer then that suggests an issue on the web
side, but without more information it's hard to say.
Simon|||Mike J (mjewett_2000@.yahoo.com) writes:
> I have several stored procedures that run fine from my SQL Server
> database (via the exec command.), though when I call these procedures
> from my web application, they do not complete. I have other
> procedures that in fact do run fine through my web application though,
> so I do not believe its a front-end problem. The procedures only take
> about 30 seconds to run from the back-end, so I know its not a time
> out issue as well. Does anyone have any ideas?
30 seconds is the default time-out, so timeout problems cannot be ruled
out completely. Do you have proper error handling in your web app?
There are a couple of possible reasons for this, but since you provided
very little information about your code, I can only give some general
comments.
Do you use indexed views or indexes on computed columns? In such case,
you need to issue SET ARITHABORT ON when you connect from your web app
(or make this default for the database with ALTER DATABASE). This setting
is on by default when you run from Query Analyzer.
Even if you don't use indexed views or indexed computed columns, SET
ARITHABORT ON, can still be useful, as you now will get the same plan
as Query Analyzer does.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<408d7223$1_3@.news.bluewin.ch>...
> "Mike J" <mjewett_2000@.yahoo.com> wrote in message
> news:1a9d60fa.0404261152.322ed838@.posting.google.c om...
> > I have several stored procedures that run fine from my SQL Server
> > database (via the exec command.), though when I call these procedures
> > from my web application, they do not complete. I have other
> > procedures that in fact do run fine through my web application though,
> > so I do not believe its a front-end problem. The procedures only take
> > about 30 seconds to run from the back-end, so I know its not a time
> > out issue as well. Does anyone have any ideas?
> > Thanks in advance.
> Can you clarify what you mean by "does not complete"? Do you get an error
> message? What version of MSSQL? What's the web platform, eg. ASP, PHP etc.?
> If it works fine from Query Analyzer then that suggests an issue on the web
> side, but without more information it's hard to say.
> Simon
Simon,
Here's some more elaboration. By "does not complete" I mean that the
procedures simply never finish running. They generate no error
messages, and do not lock any objects -- they just dont finish. I
verify this by manually running the procedures in query analyzer
(where they finish quickly) and viewing the results. The strange
thing is that we use over 50 stored procedures, and almost all of them
work -- from both the web application and query analyzer. The couple
procedures that are "not finishing" are not any more complex than the
others.
The web platform we are using is Microsoft asp .NET. We are using SQL
Server 2000 as well.
Any ideas? Thanks.|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94D7EF10D40A3Yazorman@.127.0.0.1>...
> Mike J (mjewett_2000@.yahoo.com) writes:
> > I have several stored procedures that run fine from my SQL Server
> > database (via the exec command.), though when I call these procedures
> > from my web application, they do not complete. I have other
> > procedures that in fact do run fine through my web application though,
> > so I do not believe its a front-end problem. The procedures only take
> > about 30 seconds to run from the back-end, so I know its not a time
> > out issue as well. Does anyone have any ideas?
> 30 seconds is the default time-out, so timeout problems cannot be ruled
> out completely. Do you have proper error handling in your web app?
> There are a couple of possible reasons for this, but since you provided
> very little information about your code, I can only give some general
> comments.
> Do you use indexed views or indexes on computed columns? In such case,
> you need to issue SET ARITHABORT ON when you connect from your web app
> (or make this default for the database with ALTER DATABASE). This setting
> is on by default when you run from Query Analyzer.
> Even if you don't use indexed views or indexed computed columns, SET
> ARITHABORT ON, can still be useful, as you now will get the same plan
> as Query Analyzer does.
Erland, yes, we have proper error handling in our web application. We
in fact run many (around 50) stored procedures from our web
application and they complete just fine. The couple procedures that
do not run from the application are not any more complex than the
others.
To elaborate some more on our system, we are using Microsoft .NET, SQL
Server 2000. I have run many tests in query analyzer on our database
server with the procedures in question. They complete accurately and
quickly every time, with no errors. Its only when I call them from
the web app where do not finish. We do not use any indexed views or
computed columns, though I will try to ARITHABOR ON property anyhow.
Any other ideas?
Thanks.|||Mike J (mjewett_2000@.yahoo.com) writes:
> To elaborate some more on our system, we are using Microsoft .NET, SQL
> Server 2000. I have run many tests in query analyzer on our database
> server with the procedures in question. They complete accurately and
> quickly every time, with no errors. Its only when I call them from
> the web app where do not finish. We do not use any indexed views or
> computed columns, though I will try to ARITHABOR ON property anyhow.
> Any other ideas?
With that miniscule of information, no. Well, while you ruled out blocking
in another posting, one possibility is that you manage to block yourself
in the app. When you have a procedure which does not complete, execute
sp_who, and see if any process has a non-zero value in the Blk column.
If there is no blocking, use the Profiler to see where the process gets
stuck.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Friday, March 9, 2012
Problem: mssql locks several records in a specific table, while no one is editing them
Hi,
I have an Access application with linked tables via ODBC to MSSQL
server 2000.
I have an Access application with linked tables via ODBC to MSSQL
server 2000.
Having a weird problem, probably something i've done while not being
aware of (kinda newbie).
the last 20 records (and growing)of a specific table are locked - cant
change them - ("another user is editing these records ... ").
I know for a fact that no one is editing records and yet no user can
edit these last records in the MDB - including the administrator -
while able to add new records.
Administrator able to edit records in the ADP (mssql server) where the
tables are stored.
Please help, the application is renedred inert .
Thanks for reading,
Oren.Hi
I can't say that I am an expert on access, but this sounds like the
recordset may need refreshing i.e someone has changed the record and access
knows the local copy is out of date.
John
"Oren" <orentini@.hotmail.com> wrote in message
news:b1368c93.0409160909.128bae81@.posting.google.c om...
> Hi,
> I have an Access application with linked tables via ODBC to MSSQL
> server 2000.
> Having a weird problem, probably something i've done while not being
> aware of (kinda newbie).
> the last 20 records (and growing)of a specific table are locked - cant
> change them - ("another user is editing these records ... ").
> I know for a fact that no one is editing records and yet no user can
> edit these last records in the MDB - including the administrator -
> while able to add new records.
> Administrator able to edit records in the ADP (mssql server) where the
> tables are stored.
> Please help, the application is renedred inert .
> Thanks for reading,
> Oren.|||Hi,
thanks for replying.
There is no local and remote copy of the table, the table is only
stored in the sql server and linked to the access MDB.
Simple refresh wont do (tried it, via the access menubars).
have any more suggestions?
Thanks,
Oren,
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<414b53f6$0$27296$afc38c87@.news.easynet.co.uk>...
> Hi
> I can't say that I am an expert on access, but this sounds like the
> recordset may need refreshing i.e someone has changed the record and access
> knows the local copy is out of date.
> John
> "Oren" <orentini@.hotmail.com> wrote in message
> news:b1368c93.0409160909.128bae81@.posting.google.c om...
> > Hi,
> > I have an Access application with linked tables via ODBC to MSSQL
> > server 2000.
> > Having a weird problem, probably something i've done while not being
> > aware of (kinda newbie).
> > the last 20 records (and growing)of a specific table are locked - cant
> > change them - ("another user is editing these records ... ").
> > I know for a fact that no one is editing records and yet no user can
> > edit these last records in the MDB - including the administrator -
> > while able to add new records.
> > Administrator able to edit records in the ADP (mssql server) where the
> > tables are stored.
> > Please help, the application is renedred inert .
> > Thanks for reading,
> > Oren.|||Hi
You may want to post this is an access news group!
John
"Oren" <orentini@.hotmail.com> wrote in message
news:b1368c93.0409180015.718465e6@.posting.google.c om...
> Hi,
> thanks for replying.
> There is no local and remote copy of the table, the table is only
> stored in the sql server and linked to the access MDB.
> Simple refresh wont do (tried it, via the access menubars).
> have any more suggestions?
> Thanks,
> Oren,
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:<414b53f6$0$27296$afc38c87@.news.easynet.co.uk>...
> > Hi
> > I can't say that I am an expert on access, but this sounds like the
> > recordset may need refreshing i.e someone has changed the record and
access
> > knows the local copy is out of date.
> > John
> > "Oren" <orentini@.hotmail.com> wrote in message
> > news:b1368c93.0409160909.128bae81@.posting.google.c om...
> > > Hi,
> > > I have an Access application with linked tables via ODBC to MSSQL
> > > server 2000.
> > > > Having a weird problem, probably something i've done while not being
> > > aware of (kinda newbie).
> > > > the last 20 records (and growing)of a specific table are locked - cant
> > > change them - ("another user is editing these records ... ").
> > > > I know for a fact that no one is editing records and yet no user can
> > > edit these last records in the MDB - including the administrator -
> > > while able to add new records.
> > > > Administrator able to edit records in the ADP (mssql server) where the
> > > tables are stored.
> > > > Please help, the application is renedred inert .
> > > > Thanks for reading,
> > > Oren.
PROBLEM: Database Mainence plan "All user Databases" missing database
If I select "All user Databases" and click T-SQL, 3 our of 4
databases are listed- and the missing database has not been backed up
via the plan. All databases are listed, however, when I select
"Selected Databases"- they are shown in the drop downbox.
This is for SQL 2005 Service Pack 2 with Hotfix 3175.
My guess: The missing database was restored and not restored to
overwrite an existing database. Since the CREATE DATABASE command was
not executed, maybe there is system info in master that doesn't get
set'
Help please. Thanks
Erik> My guess: The missing database was restored and not restored to
> overwrite an existing database. Since the CREATE DATABASE command was
> not executed, maybe there is system info in master that doesn't get
> set'
No, that should not be the reason. The same meta-data would exist regardless of how the database was
created (CREATE DATABASE, attach or by RESTORE). Perhaps the database is in 70 compatibility mode?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<info@.onlyhd.tv> wrote in message
news:25261d77-880b-4b83-9e9a-94866e14af6e@.e6g2000prf.googlegroups.com...
> If I select "All user Databases" and click T-SQL, 3 our of 4
> databases are listed- and the missing database has not been backed up
> via the plan. All databases are listed, however, when I select
> "Selected Databases"- they are shown in the drop downbox.
> This is for SQL 2005 Service Pack 2 with Hotfix 3175.
> My guess: The missing database was restored and not restored to
> overwrite an existing database. Since the CREATE DATABASE command was
> not executed, maybe there is system info in master that doesn't get
> set'
> Help please. Thanks
> Erik|||Thanks. No, i've seent that 7.0 Compat mode issue before, it is
actually set to 9.0.|||Do the plan do both db and log backup? Is this missing backup a db or a log backup? Perhaps there's
a difference in the recovery model setting.
If not, then I'd check other database options, just doing a quick visual compare:
SELECT * FROM sys.databases
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<info@.onlyhd.tv> wrote in message
news:42c70b00-ac98-4d82-b150-69d1dbc3bf29@.i29g2000prf.googlegroups.com...
> Thanks. No, i've seent that 7.0 Compat mode issue before, it is
> actually set to 9.0.|||Thanks for suggstions. I found that Auto Update Statistics was
different and I turned it on and also set to FULL RECOVER MODE and it
got listed in View t-SQL preview !!
THAN I turned everything back to the previous settings for my missing
databses and the All Databases support still was fixed and in tact! t-
SQL Preview showed my previously missing database.
Updating sys.databases seems to fix the problem. maybe there is some
trigger in the background or something.
Thank you for all of your help.
Erik
databases are listed- and the missing database has not been backed up
via the plan. All databases are listed, however, when I select
"Selected Databases"- they are shown in the drop downbox.
This is for SQL 2005 Service Pack 2 with Hotfix 3175.
My guess: The missing database was restored and not restored to
overwrite an existing database. Since the CREATE DATABASE command was
not executed, maybe there is system info in master that doesn't get
set'
Help please. Thanks
Erik> My guess: The missing database was restored and not restored to
> overwrite an existing database. Since the CREATE DATABASE command was
> not executed, maybe there is system info in master that doesn't get
> set'
No, that should not be the reason. The same meta-data would exist regardless of how the database was
created (CREATE DATABASE, attach or by RESTORE). Perhaps the database is in 70 compatibility mode?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<info@.onlyhd.tv> wrote in message
news:25261d77-880b-4b83-9e9a-94866e14af6e@.e6g2000prf.googlegroups.com...
> If I select "All user Databases" and click T-SQL, 3 our of 4
> databases are listed- and the missing database has not been backed up
> via the plan. All databases are listed, however, when I select
> "Selected Databases"- they are shown in the drop downbox.
> This is for SQL 2005 Service Pack 2 with Hotfix 3175.
> My guess: The missing database was restored and not restored to
> overwrite an existing database. Since the CREATE DATABASE command was
> not executed, maybe there is system info in master that doesn't get
> set'
> Help please. Thanks
> Erik|||Thanks. No, i've seent that 7.0 Compat mode issue before, it is
actually set to 9.0.|||Do the plan do both db and log backup? Is this missing backup a db or a log backup? Perhaps there's
a difference in the recovery model setting.
If not, then I'd check other database options, just doing a quick visual compare:
SELECT * FROM sys.databases
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<info@.onlyhd.tv> wrote in message
news:42c70b00-ac98-4d82-b150-69d1dbc3bf29@.i29g2000prf.googlegroups.com...
> Thanks. No, i've seent that 7.0 Compat mode issue before, it is
> actually set to 9.0.|||Thanks for suggstions. I found that Auto Update Statistics was
different and I turned it on and also set to FULL RECOVER MODE and it
got listed in View t-SQL preview !!
THAN I turned everything back to the previous settings for my missing
databses and the All Databases support still was fixed and in tact! t-
SQL Preview showed my previously missing database.
Updating sys.databases seems to fix the problem. maybe there is some
trigger in the background or something.
Thank you for all of your help.
Erik
Subscribe to:
Posts (Atom)