"transfering" data to test database

What I need to do:
Copy database from one server to another.
Problem:
3rd party application requires the owner of the tables to be different.
Eg in the source db the owner is A so the name of table1 is A.Table1 on
the destination db the owner is supposed to be B so the name of table1 will
be B.Table1.
I have used a DTS package using the Copy SQL Server object task due to the
fact that I have a lot of tables however this also copies the owner accross
so it is named A.Table1 and not the required B.Table1.
Any Ideas?
Thanks.
JonasYou could try this:
1. For each table in your database
(e.g., get all tables
SELECT * FROM INFORMATION_SCHEMA.TABLES)
2. Change owner with sp_changeobjectowner [ @.objname = ] 'object' , [ @.newowner = ] 'owner'
You could run a query similar to the one below, to return all "sp_changeobjectowner" statements as a recordset which you can then execute as a batch to change the ownership of all your tables at once:
e.g.,
Select 'exec sp_changeobjectowner @.objname = [youroldowner].[' + table_name + '] , @.newowner = ''yournewowner'''
from INFORMATION_SCHEMA.TABLES
Tea C.
"Jonas Larsen" wrote:
> What I need to do:
> Copy database from one server to another.
> Problem:
> 3rd party application requires the owner of the tables to be different.
> Eg in the source db the owner is A so the name of table1 is A.Table1 on
> the destination db the owner is supposed to be B so the name of table1 will
> be B.Table1.
> I have used a DTS package using the Copy SQL Server object task due to the
> fact that I have a lot of tables however this also copies the owner accross
> so it is named A.Table1 and not the required B.Table1.
> Any Ideas?
> Thanks.
> Jonas
>
>|||Hi,
You can change the object owner using the system stored procedure
sp_changeobjectowner.
sp_changeobjectowner 'Table_name', 'B_USER'
Instead of doing one by one you could query the sysobjects table for all
objects ownerd by A_user.
set quoted_identifier off
select 'sp_changeobjectowner '+"'"+name+"',"+"'"+'b_user'+"'" +char(10)+'go'
from sysobjects
where user_name(uid)='A_user' and type='u'
Execute the above query in Query Analyzer in with Text result. (Query menu
result in text)
Copy the result in Query screen and execute to change the object owner to
B_USER.
Thanks
Hari
MCDBA
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:ONo5LDEdEHA.3896@.TK2MSFTNGP10.phx.gbl...
> What I need to do:
> Copy database from one server to another.
> Problem:
> 3rd party application requires the owner of the tables to be
different.
> Eg in the source db the owner is A so the name of table1 is A.Table1
on
> the destination db the owner is supposed to be B so the name of table1
will
> be B.Table1.
> I have used a DTS package using the Copy SQL Server object task due to the
> fact that I have a lot of tables however this also copies the owner
accross
> so it is named A.Table1 and not the required B.Table1.
> Any Ideas?
> Thanks.
> Jonas
>|||I beleive DTS can copy tables between different owners directly instead of
having to to do all ownership changing...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:ONo5LDEdEHA.3896@.TK2MSFTNGP10.phx.gbl...
> What I need to do:
> Copy database from one server to another.
> Problem:
> 3rd party application requires the owner of the tables to be
different.
> Eg in the source db the owner is A so the name of table1 is A.Table1
on
> the destination db the owner is supposed to be B so the name of table1
will
> be B.Table1.
> I have used a DTS package using the Copy SQL Server object task due to the
> fact that I have a lot of tables however this also copies the owner
accross
> so it is named A.Table1 and not the required B.Table1.
> Any Ideas?
> Thanks.
> Jonas
>|||Ok. Do you know how this is done?
Thanks.
Regards
Jonas
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:uhug4pJdEHA.3216@.TK2MSFTNGP11.phx.gbl...
> I beleive DTS can copy tables between different owners directly instead of
> having to to do all ownership changing...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
> news:ONo5LDEdEHA.3896@.TK2MSFTNGP10.phx.gbl...
> > What I need to do:
> >
> > Copy database from one server to another.
> >
> > Problem:
> > 3rd party application requires the owner of the tables to be
> different.
> > Eg in the source db the owner is A so the name of table1 is A.Table1
> on
> > the destination db the owner is supposed to be B so the name of table1
> will
> > be B.Table1.
> >
> > I have used a DTS package using the Copy SQL Server object task due to
the
> > fact that I have a lot of tables however this also copies the owner
> accross
> > so it is named A.Table1 and not the required B.Table1.
> >
> > Any Ideas?
> >
> > Thanks.
> >
> > Jonas
> >
> >
>

"Timeout expired" error occuring while fetching the data with SQL Server 2000

Hello,
I have one application which is having written inasp.net & plainasp.
I am having one button on asp page,
when i will click on that button, then itwill execute one other asp page.
And after the execution of that second asp page, I redirect it to some
ASPX page with some values.
On the ASPX page, it will connect to the Database, and insert the values.
Thus, sometime, the following error is occuring :
"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

Main thing is that i am testing it on my local machine, then also it is giving me this error ...
Insert into statement is also not much complicated :
just I inserted three values in one table.
Is this occuring due to the mixer of asp & aspx??
Plz give me some proper solution.
I want it in efficient manner, because this application will be accessed by number of users at the same time.
Plz help me.


Thanks,
Sandy
Have you been able to pointpint exactly where the bottleneck is occuring? Is the INSERT statement definitely the culprit?
|||Yes...
for insert statement.
But I think this is not creating any matter.
matter is the connection with DB.
This is not occuring everytime.
It's frequancy is 30%.
But for number of users concurrent access, i am not sure is it same or may be increase.
Currently I am using the following connection string :
ConnectionString="Data Source=localhost;Initial Catalog=myDataBase;User ID=test;Password=test;min pool size=1; max pool size=300"

If there is any issue in this string, plz let me know.
Plz give me the feasible solution ASAP.
Thanks,
Sandy
|||I'd use Performance Monitor to check how well your connection pooling is working for you. See this article:Tuning Up ADO.NET Connection Pooling in ASP.NET Applications, in particular the Monitoring Connection Pooling Behavior section

"Syntax error or access violation" with new (July 22 2003) driver

Hi,
I'm having troubles with the SQL Server ODBC driver 2000.81.9001.40 dated on
July 22 2003. It is incompatible with our custom application developed with
Centura SQLWindows/32 Version 1.5.1-PFT2. When running some queries, the
error message reported by the driver is
Error No: 20035
Error Text: [Microsoft][ODBC SQL Server Driver]Syntax error or access
violation
The only suspicious fact is that the command is broken by a CR-LF (new line)
combination. This code had worked for years without any problem, sending
such broken line commands
The SQL command sent is:
{call spEjeSelectRem ('vCtePedTem_ChecaPoliticas
20,20,159,1,935502,17700024,1200148,null,
1')}
Note there is a new-line sequence behind the last parameter
My questions are:
Can It be the cause of the above mentioned error?
Wat can I do, install the previous version?
The ODBC driver is dated July 22 2003. It was automatically downloaded by
Microsoft Automatic Update. How can I protect specifically from ODBC driver
version updates?
Thanks in advance
FaustinoOk,
I did the following:
-"Uninstall" MDAC by
dasetup.exe /u
(actually the uninstall procedure didn't work, but at least it leave the
ODBC in such inconsistent state that I could install a previous version)
-Installed the MDAC v.2.7 that I had before the bug appeared. The bug didn't
appear again.
However yesterday the Microsoft Update suggested to install the security
patch 823718 related to MDAC. I crossed my fingers and applied it. No
problem. SQL Server driver is now 2000.81.9041.40 and the bug didn't show
again.
Hope this helps somebody
Faustino
"faustino Dina" <fdina@.matusa.com.mx> wrote in message
news:eo%23$lJ%23aDHA.2016@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm having troubles with the SQL Server ODBC driver 2000.81.9001.40 dated
on
> July 22 2003. It is incompatible with our custom application developed
with
> Centura SQLWindows/32 Version 1.5.1-PFT2. When running some queries, the
> error message reported by the driver is
> Error No: 20035
> Error Text: [Microsoft][ODBC SQL Server Driver]Syntax error or access
> violation
> The only suspicious fact is that the command is broken by a CR-LF (new
line)
> combination. This code had worked for years without any problem, sending
> such broken line commands
> The SQL command sent is:
> {call spEjeSelectRem ('vCtePedTem_ChecaPoliticas
> 20,20,159,1,935502,17700024,1200148,null,
> 1')}
> Note there is a new-line sequence behind the last parameter
> My questions are:
> Can It be the cause of the above mentioned error?
> Wat can I do, install the previous version?
> The ODBC driver is dated July 22 2003. It was automatically downloaded by
> Microsoft Automatic Update. How can I protect specifically from ODBC
driver
> version updates?
> Thanks in advance
> Faustino
>

"SQL Server does not exist or access denied" error

I'm trying to create to set up connection between my web application on the server and
the database on another box and getting "SQL Server does not exist or access denied" error.
Both the web server and the database machine are Windows 2003 Server, there's no firewall
enabled, there's mixed authentication enabled on the sql server. The machines are not on the
same domain so I'm using SQL Server authentication from web.config. Here's my connection string:
<add key="ConnectionString" value="Server=sqlboxip;User id=sqlaccountname;Pwd=sqlpass;Initial catalog=dbname">
I've also got <identity impersonate="true"> set in web.config.
Had it the connection working at some stage but then with more tweaking and app reinstall
back square one... Please help!</identity></add>Hi,
Are you able to connect to the SQL Server Database using the same credentials from SQL Server (Enterprise Manager or Query Analyzer?)
Thanks.|||Yes, I can connect to it from my dev box in ASP.NET
|||Did you set the connection string of your connection on that page of the app?

I put this in the page load.
this.cnMainConnection.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionsString"].ToString();

|||Hi,
Ok, can you post the code which you are trying to execute.
Thanks.

"SQL Server does not exist or access denied"

Hi everyone!

I receive error "SQL Server does not exist or access denied" when I try to run a web application from my notebook. (when I'm working at home) The weird thing is that it occurs sporadically. Sometimes the connection works just fine. Other times it fails. When I run the application on my workstation at the office everything works perfectly.

Here's the stack trace:

[SqlException: SQL Server does not exist or access denied.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +484
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState) +44
System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +304
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +77
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +38
Torris.Common.Data.GetDataSet(String ConnectionString, String sql) in c:\_\Manager\common\data.cs:14
Torris.Manager.Sizes.ListSizes() in C:\_\Manager\Sizes.ascx.cs:145
Torris.Manager.Sizes.Page_Load(Object sender, EventArgs e) in C:\_\Manager\Sizes.ascx.cs:77
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Control.LoadRecursive() +98
System.Web.UI.Control.LoadRecursive() +98
System.Web.UI.Control.LoadRecursive() +98
System.Web.UI.Page.ProcessRequestMain() +731

-----------------

Of course, I make sure to change the connection strings in both environments.

Anyone have an idea of what I can look at to try to troubleshoot?

Thanks as always!!

Emi ^_^Hi Emi,

This is virtually always an access issue. How are you trying to connect to the db? IOW, what is your connection string? Are you sure your laptop is setup properly?

The other thing that strikes me is connection pooling. Do you do anything with pooling--change defaults, etc.--in the app?

Don|||Hi!

Thanks for responding. Still having the issue - it's so frustrating. I'm storing my connection string in the web.config which is then read and used in my Data.cs (database class)

Here's the connection string. In this case, the database is on the same machine as the web application. (similar to the how the client's production server is setup):

<add key="ConnectionString" value="server=MyComputerName;database=DatabaseName;uid=sa;pwd=NonBlankPassword;" />

An example of the actual data access - nothing complex here.

public static void ExecuteSql(string ConnectionString, string sql)
{
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand cmd = new SqlCommand(sql,conn);
conn.Open();
cmd.ExecuteNonQuery();
cmd.Dispose();
conn.Close();
conn.Dispose();
}

Like I mentioned before, all works great on my workstation at the office. This might totally be a hardware - issue. Is there anything wrong with the above code that would cause any pooling problems.

Thanks again!

Emi ^_^|||Hi Emi,

Okay, assuming that your sa password in the actual connection string is right, everything else looks okay.

One thing to check is whether the SS2K server is set up to use SQL Server authentication rather than Windows integrated authentication only. You can do that in Enterprise Manager, right-clicking on the server, and selecting Properties. Under the Security tab make sure that "SQL Server and Windows" is selected.

Sigh. But never mind about that. This doesn't explain the sporadic nature of the error. There is something going on with the connection pool. You're not doing anything with the connection pool in your code, but it has a problem when it goes to the pool for a connection. Hmm. And it's happening deep within the .NET framework.

Have you monitored the pool to make sure that connections are still available? It's a weird error to get for a depleted pool, though.

This will take more research. Anyone else have any ideas?

Don

"Server Application Unavailable" error on generating PDF reports

Hi,
I'm using ASP.NET with SQL Reporting Service to generate PDF reports.
Most of the time it works well.
But sometimes PDF doesn't come out (esp. if the report is complicated),
with the error message at below.
I use 2 separate servers for Web Server / Reporting Service.
And i suspect a cause is not enough memory?
Thanks,
Azul
-- error message --
Server Application Unavailable
The web application you are attempting to access on this web server is
currently unavailable. Please hit the "Refresh" button in your web browser
to retry your request.
Administrator Note: An error message detailing the cause of this specific
request failure can be found in the application event log of the web server.
Please review this log entry to discover what caused this error to occur.
--.: at
System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(...)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(...)
at LYNX.ReportServices.ReportingService.Render(...) in ...
at LYNX.BLL.reportServices.getCustomReport(...) in ...
at LYNX.BLL.reportServices.getPDFReport(...) in ...
at LYNX.Components.Report.RunningAverage.PreviewGraph() in ...
-- end of error message --I found the solution from another post in this newsgroup!
Just downloaded and installed Reporting Services SP1, everything works fine
Although the earlier post talked about long long table,
the solution also applies in my case of generating complicated graphs.
"Azul" <a@.b.c> wrote in message
news:ebcX1F1qEHA.3324@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm using ASP.NET with SQL Reporting Service to generate PDF reports.
> Most of the time it works well.
> But sometimes PDF doesn't come out (esp. if the report is complicated),
> with the error message at below.
> I use 2 separate servers for Web Server / Reporting Service.
> And i suspect a cause is not enough memory?
> Thanks,
> Azul
>
> -- error message --
> Server Application Unavailable
> The web application you are attempting to access on this web server is
> currently unavailable. Please hit the "Refresh" button in your web
> browser to retry your request.
> Administrator Note: An error message detailing the cause of this specific
> request failure can be found in the application event log of the web
> server. Please review this log entry to discover what caused this error to
> occur.
> --.: at
> System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(...)
> at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(...)
> at LYNX.ReportServices.ReportingService.Render(...) in ...
> at LYNX.BLL.reportServices.getCustomReport(...) in ...
> at LYNX.BLL.reportServices.getPDFReport(...) in ...
> at LYNX.Components.Report.RunningAverage.PreviewGraph() in ...
> -- end of error message --
>

"Provider not found" error

I have an application that works just fine on several computers.
{Delphi6 with ADO Components }
We have a MSSQL2K DB with MDAC 2.81.1117 on EVERY machine.
However, on one of them, when I try to run the program, I get a
"Provider not found. It may not be properly installed" error,
then an "Error in the DLL" message box.
We can access database through Enterprise manager AND I can create an
ODBC DSN to the database in question on the problem machine just fine.
Let me explain here what we've attempted.
1. Reinstalled SQL Server2K
2. Reinstalled XP SP2
3. Installed Delphi fully
4. Tried MDAC 2.8 install, won't because of XP SP2,
says already installed
5. tried using DSN file .vs connectionstring
6. tried clearing the provider from connectionstring
7. tried creating ODBC DSN and connecting that way.
All of these result in same "Provider not found" error. When I opened
the source on this machine in Delphi and tried to build the
connectionstring there, I got an error stating that the provider was not
installed at all.
I can verify that sqloledb.dll IS installed in the
C:\Program Files\Common Files\System\Ole DB directory and registered
I can verify that mdasql.dll IS installed in the
C:\Program Files\Common Files\System\Ole DB directory
I can verify that mdatl3.dll IS installed in the
C:\Program Files\Common Files\System\Ole DB directory
I can verify that registry entires are correct for SQLOLEDB provider.
I can also verify that the directory
C:\Program Files\Common Files\System\Ole DB is in the search path.
This is a critical time accounting appplication for our employees
that works on dozens of other machines and for it to not work on one
certain machine is very puzzling. We can not understand what is so
"special" about this one.
Any ideas?
The Cleaning Wonder Boy wrote:
> *I have an application that works just fine on several computers.
> {Delphi6 with ADO Components }
> We have a MSSQL2K DB with MDAC 2.81.1117 on EVERY machine.
> However, on one of them, when I try to run the program, I get a
> "Provider not found. It may not be properly installed" error,
> then an "Error in the DLL" message box.
> We can access database through Enterprise manager AND I can create
> an
> ODBC DSN to the database in question on the problem machine just
> fine.
> Let me explain here what we've attempted.
> 1. Reinstalled SQL Server2K
> 2. Reinstalled XP SP2
> 3. Installed Delphi fully
> 4. Tried MDAC 2.8 install, won't because of XP SP2,
> says already installed
> 5. tried using DSN file .vs connectionstring
> 6. tried clearing the provider from connectionstring
> 7. tried creating ODBC DSN and connecting that way.
> All of these result in same "Provider not found" error. When I
> opened
> the source on this machine in Delphi and tried to build the
> connectionstring there, I got an error stating that the provider was
> not
> installed at all.
> I can verify that sqloledb.dll IS installed in the
> C:\Program Files\Common Files\System\Ole DB directory and registered
> I can verify that mdasql.dll IS installed in the
> C:\Program Files\Common Files\System\Ole DB directory
> I can verify that mdatl3.dll IS installed in the
> C:\Program Files\Common Files\System\Ole DB directory
> I can verify that registry entires are correct for SQLOLEDB
> provider.
> I can also verify that the directory
> C:\Program Files\Common Files\System\Ole DB is in the search path.
> This is a critical time accounting appplication for our employees
> that works on dozens of other machines and for it to not work on one
> certain machine is very puzzling. We can not understand what is so
> "special" about this one.
> Any ideas? *
Hi
I too have faced the same problem on one of my clients machine
i have treid all that you have described above but nothing seems to
work.
But the solution is simple
You first need to delete the folder
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\Curr entVersion\Setup\ExceptionComponents
in the Registry and then try re-installing MDAC2.6SP2
Reboot and the problem is gone.
It has worked for me on Win 2000 OS, i hope this works for you
Thanks
Ganta.
Ganta
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message974581.html

"Provider not found" error

I have an application that works just fine on several computers.
{Delphi6 with ADO Components }
We have a MSSQL2K DB with MDAC 2.81.1117 on EVERY machine.
However, on one of them, when I try to run the program, I get a
"Provider not found. It may not be properly installed" error,
then an "Error in the DLL" message box.
We can access database through Enterprise manager AND I can create an
ODBC DSN to the database in question on the problem machine just fine.
Let me explain here what we've attempted.
1. Reinstalled SQL Server2K
2. Reinstalled XP SP2
3. Installed Delphi fully
4. Tried MDAC 2.8 install, won't because of XP SP2,
says already installed
5. tried using DSN file .vs connectionstring
6. tried clearing the provider from connectionstring
7. tried creating ODBC DSN and connecting that way.
All of these result in same "Provider not found" error. When I opened
the source on this machine in Delphi and tried to build the
connectionstring there, I got an error stating that the provider was not
installed at all.
I can verify that sqloledb.dll IS installed in the
C:\Program Files\Common Files\System\Ole DB directory and registered
I can verify that mdasql.dll IS installed in the
C:\Program Files\Common Files\System\Ole DB directory
I can verify that mdatl3.dll IS installed in the
C:\Program Files\Common Files\System\Ole DB directory
I can verify that registry entires are correct for SQLOLEDB provider.
I can also verify that the directory
C:\Program Files\Common Files\System\Ole DB is in the search path.
This is a critical time accounting appplication for our employees
that works on dozens of other machines and for it to not work on one
certain machine is very puzzling. We can not understand what is so
"special" about this one.
Any ideas'

quote:

---

Originally posted by The Cleaning Wonder Boy
I have an application that works just fine on several computers.
{Delphi6 with ADO Components }
We have a MSSQL2K DB with MDAC 2.81.1117 on EVERY machine.
However, on one of them, when I try to run the program, I get a
"Provider not found. It may not be properly installed" error,
then an "Error in the DLL" message box.
We can access database through Enterprise manager AND I can create an
ODBC DSN to the database in question on the problem machine just fine.
Let me explain here what we've attempted.
1. Reinstalled SQL Server2K
2. Reinstalled XP SP2
3. Installed Delphi fully
4. Tried MDAC 2.8 install, won't because of XP SP2,
says already installed
5. tried using DSN file .vs connectionstring
6. tried clearing the provider from connectionstring
7. tried creating ODBC DSN and connecting that way.
All of these result in same "Provider not found" error. When I opened
the source on this machine in Delphi and tried to build the
connectionstring there, I got an error stating that the provider was not
installed at all.
I can verify that sqloledb.dll IS installed in the
C:\Program Files\Common Files\System\Ole DB directory and registered
I can verify that mdasql.dll IS installed in the
C:\Program Files\Common Files\System\Ole DB directory
I can verify that mdatl3.dll IS installed in the
C:\Program Files\Common Files\System\Ole DB directory
I can verify that registry entires are correct for SQLOLEDB provider.
I can also verify that the directory
C:\Program Files\Common Files\System\Ole DB is in the search path.
This is a critical time accounting appplication for our employees
that works on dozens of other machines and for it to not work on one
certain machine is very puzzling. We can not understand what is so
"special" about this one.
Any ideas'

---

Hi
I too have faced the same problem on one of my clients machine
i have treid all that you have described above but nothing seems to work.
But the solution is simple
You first need to delete the folder HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\W
indows\CurrentVersion\Setup\ExceptionCom
ponents
in the Registry and then try re-installing MDAC2.6SP2
Reboot and the problem is gone.
It has worked for me on Win 2000 OS, i hope this works for you
Thanks
Ganta.

"OutOfMemory" exception when listing subscriptions in "Report Manager"

Hi There,
We are having a problem in listing subscriptions in Report manager and
getting "OutOfMemory" exception.
Our Application has an event-based subscription management system, and
every-time an event gets fired on the Application-side, it creates an
one-off subscription on the Reporting Services. So overtime the number of
Subscriptions created on the reporting services has grown, and a particular
report has about 8000+ subscriptions to date now.
So when we try to list the subscriptions (for the purpose of management of
subscriptions) in the RS "Report Manager",
for that particular report, System gives an OutOfMemory exception. I think
Report Manager Calls internally
"ListSubscriptions" method (as explained in 840709) and couldn't cope up
with.
And i looked at MSDN Knowledgebase Article:840709, and increased the
"MemoryLimit" setting in RSReportServer.config, but there was no use.
By the way our Server has 2GB of RAM and we use Custom Authentication on
Reporting Services.
Any Suggestions are appreciated.
Regards
Raj ChidipudiI suggest you remove a number of the subscriptions via SQl Server Enterprise
Manager - remove a number of the relevant entries for the report from the
Subscriptions table.
Not tried it myself so I would backup before trying!!
You could also create an rss script to run at the end of day to remove all
subscriptions for a report, somthing like:
Public Sub Main()
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim subs As Subscription() = rs.ListSubscriptions(report, "")
If Not subs Is Nothing OrElse subs.Length > 0 Then
For Each subscript As Subscription In subs
rs.DeleteSubscription(subscript.SubscriptionID)
Next
End If
End Sub
Run it using the rs util with:
rs -i scriptname.rss -s http://ServerName/reportserver
Alan
"Raj Chidipudi" wrote:
> Hi There,
> We are having a problem in listing subscriptions in Report manager and
> getting "OutOfMemory" exception.
> Our Application has an event-based subscription management system, and
> every-time an event gets fired on the Application-side, it creates an
> one-off subscription on the Reporting Services. So overtime the number of
> Subscriptions created on the reporting services has grown, and a particular
> report has about 8000+ subscriptions to date now.
> So when we try to list the subscriptions (for the purpose of management of
> subscriptions) in the RS "Report Manager",
> for that particular report, System gives an OutOfMemory exception. I think
> Report Manager Calls internally
> "ListSubscriptions" method (as explained in 840709) and couldn't cope up
> with.
> And i looked at MSDN Knowledgebase Article:840709, and increased the
> "MemoryLimit" setting in RSReportServer.config, but there was no use.
> By the way our Server has 2GB of RAM and we use Custom Authentication on
> Reporting Services.
> Any Suggestions are appreciated.
> Regards
> Raj Chidipudi
>
>|||I think rs.DeleteSubscription("") would be the best bet.
We don't want to really mess with Reporting Services Database directly.
Anyway thanks for the suggestion.
Thanks
Raj Chidipudi
"Alan Byrne" <AlanByrne@.discussions.microsoft.com> wrote in message
news:116EB34B-1BD4-4FDC-ADB8-18BE795D3F22@.microsoft.com...
> I suggest you remove a number of the subscriptions via SQl Server
Enterprise
> Manager - remove a number of the relevant entries for the report from the
> Subscriptions table.
> Not tried it myself so I would backup before trying!!
> You could also create an rss script to run at the end of day to remove all
> subscriptions for a report, somthing like:
> Public Sub Main()
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> Dim subs As Subscription() = rs.ListSubscriptions(report, "")
> If Not subs Is Nothing OrElse subs.Length > 0 Then
> For Each subscript As Subscription In subs
> rs.DeleteSubscription(subscript.SubscriptionID)
> Next
> End If
> End Sub
> Run it using the rs util with:
> rs -i scriptname.rss -s http://ServerName/reportserver
> Alan
>
> "Raj Chidipudi" wrote:
> > Hi There,
> >
> > We are having a problem in listing subscriptions in Report manager and
> > getting "OutOfMemory" exception.
> >
> > Our Application has an event-based subscription management system, and
> > every-time an event gets fired on the Application-side, it creates an
> > one-off subscription on the Reporting Services. So overtime the number
of
> > Subscriptions created on the reporting services has grown, and a
particular
> > report has about 8000+ subscriptions to date now.
> >
> > So when we try to list the subscriptions (for the purpose of management
of
> > subscriptions) in the RS "Report Manager",
> > for that particular report, System gives an OutOfMemory exception. I
think
> > Report Manager Calls internally
> > "ListSubscriptions" method (as explained in 840709) and couldn't cope up
> > with.
> >
> > And i looked at MSDN Knowledgebase Article:840709, and increased the
> > "MemoryLimit" setting in RSReportServer.config, but there was no use.
> > By the way our Server has 2GB of RAM and we use Custom Authentication on
> > Reporting Services.
> >
> > Any Suggestions are appreciated.
> >
> > Regards
> > Raj Chidipudi
> >
> >
> >

"Invalid object format name" Error

Hi there,

I am trying to upgrade a crystal application from VS.Net 2003 to VS.Net 2005. Some of the reports work just fine in the new enviroment. But one of the reports does give me a hard time.

It is a little complicated, so please bare with me. The report uses a c# derived DataSet class as its Database Fields source. It also uses a DataTable as its data source. When I try to bind the report with the data source, for example, myReport.setDataSource(myDataTable);. It throws the following exception:

CrystalDecisions.CrystalReports.Engine.InternalException was unhandled by user code
Message="\rError in File C:\\WINDOWS\\TEMP\\temp_c2eb4661-c3c0-429a-9d9f-c0b280e112bc {645E2563-B9C6-4A6F-9714-618DF7CB1EE2}.rpt:\nInvalid object format name."
Source="CrystalDecisions.ReportAppServer.DataSetConversion"
StackTrace:
at CrystalDecisions.ReportAppServer.ConvertDotNetToErom.ThrowDotNetException(Exception e)
at CrystalDecisions.CrystalReports.Engine.ReportDocument.SetDataSourceInternal(Object val, Type type)
at CrystalDecisions.CrystalReports.Engine.ReportDocument.SetDataSource(DataTable dataTable)
at NL2.Web.GlRptFsWModule.getReport(Boolean sendToPrinter, exportTo eExport) in c:\Inetpub\wwwroot\NL2\Web\reports\GlRptFsWModule.ascx.cs:line 900
at NL2.Web.GlRptFsWModule.cViewButton_Click(Object sender, EventArgs e) in c:\Inetpub\wwwroot\NL2\Web\reports\GlRptFsWModule.ascx.cs:line 1246
at System.Web.UI.WebControls.LinkButton.OnClick(EventArgs e)
at System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

I traced the program and realized that once I change the format of the FieldObject and call the report object's refresh() method, the same exception is thrown. For example:

myReport.Refresh(); // ok here
myFiledObject.Width = 0;
myReport.Refresh(); // throw exception

I also tried to reset all the FieldFormat for the FieldObject, but the same thing happen. Once I change the FieldFormat and refresh, it throws an exception. I am so confused now!

myReport.Refresh(); // ok here
myFiledObject.FieldFormat.BooleanFormat.OutputType = BooleanOutputType.TrueOrFalse;
myReport.Refresh(); // throw exception

Can any crystal report expert/genius give me some hints on what did I do wrong or what could be the problem? Thanks in Advance!If possible, use verify database option and check whether it is connected to the server properly

"Invalid authorization specification"

Hello,

I have a web application (ISAPI) that accesses a SQL Server 2000 (sp3) database in the same machine as the Web Server (IIS 6). Windows 2003 Standard Edition is the operating System. So, when users access the aplication through the web it runs ok until display the error message "Invalid authorization specification" or, sometimes, another message about failure to inform ODBC DSN, although I dont use ODBC.

When the error occurs, the user press F5 in the browser and the operation goes ok. It's an intermitent and curious error.

Can somebody help me?

Thanks in advance.

Ado.I have no clue

http://www.easysoft.com/products/9999/faq_answer.phtml?ID=123&product=2002

ok, now with the pot shots...

"INSERT INTO" - Question

Hello,

if I have a row full of Data, lets call it Row "A". Now i have the "Insert Into" Command in my Application. So, when the User executes the Command again, will it update Row "A" or will it produce just a double?

This is important for my further Work with SQL.

I am using SQL Server 2006 Express or something like this. The Table in which Row "A" is has no primary key!

Greetz,

Eroli

Unless you have constraints/triggers or your INSERT violates the defaults/constraints, it will add a NEW ROW to the table irrespective of the existing data.

|||

Hi,

but when i put it all into a for-command like this one

for(int i = 0; i != 5;i++)

{

//All the Commands

}

I get only one row.

So, whats right now?

Greetz,

Eroli

|||Depends on what the commands are. Post all the relevant code and that might help.|||

System.Data.SqlClient.SqlCommand SqlCommand = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlConnection SqlConnection = new System.Data.SqlClient.SqlConnection();
SqlConnection.ConnectionString = (string)System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlCommand.Connection = SqlConnection;
SqlCommand.CommandText = "INSERT INTO UsersTable([User], [FirstStart], [Commercial], [Name], [MailAddresse], [Addresse], [Addresse2], [Location], [PostCode], [Country], [PhoneNumber], [PhoneNumber2], [Sex], [ShowLastBoughts], [StartPage], [Notifications], [RemainingTime], [HomepageUrl], [Employees], [Owner], [Evaluations]) VALUES (@.User,@.FirstStart,@.Commercial, @.Name,@.MailAddresse,@.Addresse,@.Addresse2,@.Location,@.PostCode,@.Country,@.PhoneNumber,@.PhoneNumber2,@.Sex,@.ShowLastBoughts,@.StartPage,@.Notifications,@.RemainingTime,@.HomepageUrl,@.Employees,@.Owner,@.Evaluations)";
SqlCommand.Parameters.AddWithValue("@.User", Context.User.Identity.Name);
SqlCommand.Parameters.AddWithValue("@.FirstStart", -1);
SqlCommand.Parameters.AddWithValue("@.Commercial", -1);
SqlCommand.Parameters.AddWithValue("@.Name", "");
SqlCommand.Parameters.AddWithValue("@.MailAddresse", "");
SqlCommand.Parameters.AddWithValue("@.Addresse", "");
SqlCommand.Parameters.AddWithValue("@.Addresse2", "");
SqlCommand.Parameters.AddWithValue("@.Location", "");
SqlCommand.Parameters.AddWithValue("@.PostCode", -1);
SqlCommand.Parameters.AddWithValue("@.Country", "");
SqlCommand.Parameters.AddWithValue("@.PhoneNumber", -1);
SqlCommand.Parameters.AddWithValue("@.PhoneNumber2", -1);
SqlCommand.Parameters.AddWithValue("@.Sex", "");
SqlCommand.Parameters.AddWithValue("@.ShowLastBoughts", "");
SqlCommand.Parameters.AddWithValue("@.StartPage", -1);
SqlCommand.Parameters.AddWithValue("@.Notifications", -1);
SqlCommand.Parameters.AddWithValue("@.RemainingTime", -1);
SqlCommand.Parameters.AddWithValue("@.HomepageUrl", "");
SqlCommand.Parameters.AddWithValue("@.Employees", -1);
SqlCommand.Parameters.AddWithValue("@.Owner", "");
SqlCommand.Parameters.AddWithValue("@.Evaluations", "");
SqlConnection.Open();
SqlCommand.ExecuteNonQuery();
SqlConnection.Close();
SqlConnection.Dispose();

I've got only one Row.

|||

(1) Do you have any constraints/triggers on the table?

(2) Are you changing the values of the parameters for each loop or are youe xpecting to insert 5 rows with same values?

|||

The User Column will be everytime the same, but maybe some other colums will change.

Is the UPDATE-Command better? When yes, so how can i use this?

Im confused, because i have now more rows when i tried my application again. Seems your are right.

|||You need to explain more clearly what you are trying to do and what you intend to do. INSERT and UPDATE are 2 different commands that accomplish 2 different things. so there's no question of one "better" over another. You need to use either one depending on what how application needs to behave. Simply, INSERT inserts data into the table (meaning rows will increase), UPDATE updates te existing data (and how many rows affected depends on the WHERE condition in the query).|||

First my Application should create one row for one user. This can be done by using the INSERT Command, or not?

Then, when the user changes his data, it have to be updated. Here the UPDATE-Command should be useful, should'nt it?

|||

Eroli:

First my Application should create one row for one user. This can be done by using the INSERT Command, or not?

Yes.

Eroli:

Then, when the user changes his data, it have to be updated. Here the UPDATE-Command should be useful, should'nt it?

Yes. You need to do put your logic accordingly.

|||Ok, thanks for your advice!|||

Ok, i changed my Commands.

This is my UPDATE Command:

System.Data.SqlClient.SqlCommand SqlCommand = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlConnection SqlConnection = new System.Data.SqlClient.SqlConnection();
SqlConnection.ConnectionString = (string)System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlCommand.Connection = SqlConnection;
SqlCommand.CommandText = "UPDATE UsersTable SET FirstStart=@.FirstStart,Commercial=@.Commercial,Name=@.Name,MailAddress=@.MailAddress,Address=@.Address,Address2=@.Address2,Location=@.Location,PostCode=@.PostCode,Country=@.Country,PhoneNumber=@.PhoneNumber,PhoneNumber2=@.PhoneNumber2,Sex=@.Sex WHERE User=@.User;";
SqlCommand.Parameters.AddWithValue("@.User", Context.User.Identity.Name);
SqlCommand.Parameters.AddWithValue("@.FirstStart", 0);
if (AccountTypeDropDownList.SelectedIndex == 0)
SqlCommand.Parameters.AddWithValue("@.Commercial", 0);
else if (AccountTypeDropDownList.SelectedIndex == 1)
SqlCommand.Parameters.AddWithValue("@.Commercial", 1);
SqlCommand.Parameters.AddWithValue("@.Name", NameTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.MailAddress", EmailTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.Address", AddressTextBox1.Text);
SqlCommand.Parameters.AddWithValue("@.Address2", AddressTextBox2.Text);
SqlCommand.Parameters.AddWithValue("@.Location", LocationTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.PostCode", Convert.ToInt32(PostCodeTextBox.Text));
SqlCommand.Parameters.AddWithValue("@.Country", CountryTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.PhoneNumber", PhoneNumberTextBox1.Text);
SqlCommand.Parameters.AddWithValue("@.PhoneNumber2", PhoneNumberTextBox2.Text);
switch (SexDropDownList.SelectedIndex)
{
case 0:
SqlCommand.Parameters.AddWithValue("@.Sex", -1);
break;

case 1:
SqlCommand.Parameters.AddWithValue("@.Sex", 0);
break;

case 2:
SqlCommand.Parameters.AddWithValue("@.Sex", 1);
break;
}
SqlConnection.Open();
SqlCommand.ExecuteNonQuery();
SqlConnection.Close();
SqlConnection.Dispose();</p><p>

It is in an ASP.NET Webapplication in C#! I get no failure, but nothing gets updated...

The obversely Row is filled with Dummy-Values before starting this Command.(MS SQL Server 2005 Express or so)

Greetz,

Eroli

|||

I hope this looks better...

System.Data.SqlClient.SqlCommand SqlCommand = new System.Data.SqlClient.SqlCommand();
System.Data.SqlClient.SqlConnection SqlConnection = new System.Data.SqlClient.SqlConnection();
SqlConnection.ConnectionString = (string)System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlCommand.Connection = SqlConnection;
SqlCommand.CommandText = "UPDATE UsersTable SET FirstStart=@.FirstStart,Commercial=@.Commercial,Name=@.Name,MailAddress=@.MailAddress,Address=@.Address,Address2=@.Address2,Location=@.Location,PostCode=@.PostCode,Country=@.Country,PhoneNumber=@.PhoneNumber,PhoneNumber2=@.PhoneNumber2,Sex=@.Sex WHERE User=@.User;";
SqlCommand.Parameters.AddWithValue("@.User", Context.User.Identity.Name);
SqlCommand.Parameters.AddWithValue("@.FirstStart", 0);
if (AccountTypeDropDownList.SelectedIndex == 0)
SqlCommand.Parameters.AddWithValue("@.Commercial", 0);
else if (AccountTypeDropDownList.SelectedIndex == 1)
SqlCommand.Parameters.AddWithValue("@.Commercial", 1);
SqlCommand.Parameters.AddWithValue("@.Name", NameTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.MailAddress", EmailTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.Address", AddressTextBox1.Text);
SqlCommand.Parameters.AddWithValue("@.Address2", AddressTextBox2.Text);
SqlCommand.Parameters.AddWithValue("@.Location", LocationTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.PostCode", Convert.ToInt32(PostCodeTextBox.Text));
SqlCommand.Parameters.AddWithValue("@.Country", CountryTextBox.Text);
SqlCommand.Parameters.AddWithValue("@.PhoneNumber", PhoneNumberTextBox1.Text);
SqlCommand.Parameters.AddWithValue("@.PhoneNumber2", PhoneNumberTextBox2.Text);
switch (SexDropDownList.SelectedIndex)
{
case 0:
SqlCommand.Parameters.AddWithValue("@.Sex", -1);
break;

case 1:
SqlCommand.Parameters.AddWithValue("@.Sex", 0);
break;

case 2:
SqlCommand.Parameters.AddWithValue("@.Sex", 1);
break;
}
SqlConnection.Open();
SqlCommand.ExecuteNonQuery();
SqlConnection.Close();
SqlConnection.Dispose();

|||

Ok, i solved it: I have just forgotten to put the [ ] round my Columns.

But there is another Problem: THe Condition does not work, because the User Column is no Identify-Column. How to solve this?

|||I did not understand your question. Can you rephrase?

"Ghost" database for testing purposes. Copying all tables, views,

hello.
I have a LIVE database. At the same time i am still working on my app. It's
a .Net application. Now, i need to constantly makes changes in both tables.
Not to the data, but to the design. Some fields might be added, some deleted,
new views, new sp.
My question is... How can i automatically update the Live database with any
change in the development database. I would rather make the changes in the
Live database to assure no data loss occur, but i want those changes to be
"replicated" back to my development. is there any system's stored proc that
would help me do that?
Thank you.Automating it is asking for trouble...in case you get someting wrong.
If you insist, use Red Gate SQL Compare to reveal differences between the
two for you and generate a script to run against the live db AFTER you BACK
IT UP :)
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Jose Fernandez" <Jose Fernandez@.discussions.microsoft.com> wrote in message
news:0997C63F-78D0-4618-8D07-5A9DE461107A@.microsoft.com...
> hello.
> I have a LIVE database. At the same time i am still working on my app.
> It's
> a .Net application. Now, i need to constantly makes changes in both
> tables.
> Not to the data, but to the design. Some fields might be added, some
> deleted,
> new views, new sp.
> My question is... How can i automatically update the Live database with
> any
> change in the development database. I would rather make the changes in the
> Live database to assure no data loss occur, but i want those changes to be
> "replicated" back to my development. is there any system's stored proc
> that
> would help me do that?
> Thank you.|||=?Utf-8?B?Sm9zZSBGZXJuYW5kZXo=?= <Jose
Fernandez@.discussions.microsoft.com> wrote in
news:0997C63F-78D0-4618-8D07-5A9DE461107A@.microsoft.com:
> hello.
> I have a LIVE database. At the same time i am still working on my app.
> My question is... How can i automatically update the Live database
> with any change in the development database. I would rather make the
Ditto. www.red-gate.com. It is awesome in a dev/test/prod environment.
Costs about $400.

"Ghost" database for testing purposes. Copying all tables, views,

hello.
I have a LIVE database. At the same time i am still working on my app. It's
a .Net application. Now, i need to constantly makes changes in both tables.
Not to the data, but to the design. Some fields might be added, some deleted,
new views, new sp.
My question is... How can i automatically update the Live database with any
change in the development database. I would rather make the changes in the
Live database to assure no data loss occur, but i want those changes to be
"replicated" back to my development. is there any system's stored proc that
would help me do that?
Thank you.
Automating it is asking for trouble...in case you get someting wrong.
If you insist, use Red Gate SQL Compare to reveal differences between the
two for you and generate a script to run against the live db AFTER you BACK
IT UP
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"Jose Fernandez" <Jose Fernandez@.discussions.microsoft.com> wrote in message
news:0997C63F-78D0-4618-8D07-5A9DE461107A@.microsoft.com...
> hello.
> I have a LIVE database. At the same time i am still working on my app.
> It's
> a .Net application. Now, i need to constantly makes changes in both
> tables.
> Not to the data, but to the design. Some fields might be added, some
> deleted,
> new views, new sp.
> My question is... How can i automatically update the Live database with
> any
> change in the development database. I would rather make the changes in the
> Live database to assure no data loss occur, but i want those changes to be
> "replicated" back to my development. is there any system's stored proc
> that
> would help me do that?
> Thank you.
|||=?Utf-8?B?Sm9zZSBGZXJuYW5kZXo=?= <Jose
Fernandez@.discussions.microsoft.com> wrote in
news:0997C63F-78D0-4618-8D07-5A9DE461107A@.microsoft.com:

> hello.
> I have a LIVE database. At the same time i am still working on my app.
> My question is... How can i automatically update the Live database
> with any change in the development database. I would rather make the
Ditto. www.red-gate.com. It is awesome in a dev/test/prod environment.
Costs about $400.

"General network error. Check your network documentation." After Detach

Hello.
In our application we have to detach a database using a call from our
C# application. The problem is that after that we get random "General
Network Error" messages afterwards. This is all in a local call, both
the application server and the SqlServer are on the same machine.
I read about the Pooling = False switch but this is not an option for
us because this could have major repercusions on the rest of our
application.
Does anyone know how to get rid of this error without using the Pooling
= False?
Using .NET 1.1 and SqlServer 2000
ThanksAre you saying that you are killing database connections before the detach
and that causes the issues with your pooled connections? Is the detached
database specified as the 'Initial Catalog' specification in your connection
string?
If either or both are the case, I suggest you specify a different database
as the 'Initial Catalog' and change the database context to a different
database before closing the connection. That way, the polled connection can
remain open for reuse after the detach.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<escristian@.hotmail.com> wrote in message
news:1148589276.174672.171190@.y43g2000cwc.googlegroups.com...
> Hello.
> In our application we have to detach a database using a call from our
> C# application. The problem is that after that we get random "General
> Network Error" messages afterwards. This is all in a local call, both
> the application server and the SqlServer are on the same machine.
> I read about the Pooling = False switch but this is not an option for
> us because this could have major repercusions on the rest of our
> application.
> Does anyone know how to get rid of this error without using the Pooling
> = False?
> Using .NET 1.1 and SqlServer 2000
> Thanks
>|||The architecture I'm working with basically creates a new SqlConnection
everytime we make a call to the SqlService object (only way to access
database). To detach the database I put the database to offline mode
and then detach, here's the sql syntax I use:
USE master ALTER DATABASE <<dbName>> SET OFFLINE WITH ROLLBACK
IMMEDIATE
USE master EXEC sp_detach_db '<<dbName>>', 'true'
So our architecture will create a new connection before the 1st line of
sql, destroy that connection and then create another connection before
the 2nd call.
I will start reading on the connection pooling cause that seems to be
the cause of all these problems.
Thanks for your help|||Have you tried changing the database context to another database (e.g.
master) before closing the connection as I previously suggested? That way,
pooled connections won't be killed when you execute the ALTER
DATABASE...ROLLBACK.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<escristian@.hotmail.com> wrote in message
news:1148654269.887685.237720@.i39g2000cwa.googlegroups.com...
> The architecture I'm working with basically creates a new SqlConnection
> everytime we make a call to the SqlService object (only way to access
> database). To detach the database I put the database to offline mode
> and then detach, here's the sql syntax I use:
> USE master ALTER DATABASE <<dbName>> SET OFFLINE WITH ROLLBACK
> IMMEDIATE
> USE master EXEC sp_detach_db '<<dbName>>', 'true'
> So our architecture will create a new connection before the 1st line of
> sql, destroy that connection and then create another connection before
> the 2nd call.
> I will start reading on the connection pooling cause that seems to be
> the cause of all these problems.
> Thanks for your help
>

"General network error. Check your network documentation." After Detach

Hello.
In our application we have to detach a database using a call from our
C# application. The problem is that after that we get random "General
Network Error" messages afterwards. This is all in a local call, both
the application server and the SqlServer are on the same machine.
I read about the Pooling = False switch but this is not an option for
us because this could have major repercusions on the rest of our
application.
Does anyone know how to get rid of this error without using the Pooling
= False?
Using .NET 1.1 and SqlServer 2000
ThanksAre you saying that you are killing database connections before the detach
and that causes the issues with your pooled connections? Is the detached
database specified as the 'Initial Catalog' specification in your connection
string?
If either or both are the case, I suggest you specify a different database
as the 'Initial Catalog' and change the database context to a different
database before closing the connection. That way, the polled connection can
remain open for reuse after the detach.
Hope this helps.
Dan Guzman
SQL Server MVP
<escristian@.hotmail.com> wrote in message
news:1148589276.174672.171190@.y43g2000cwc.googlegroups.com...
> Hello.
> In our application we have to detach a database using a call from our
> C# application. The problem is that after that we get random "General
> Network Error" messages afterwards. This is all in a local call, both
> the application server and the SqlServer are on the same machine.
> I read about the Pooling = False switch but this is not an option for
> us because this could have major repercusions on the rest of our
> application.
> Does anyone know how to get rid of this error without using the Pooling
> = False?
> Using .NET 1.1 and SqlServer 2000
> Thanks
>

"Failed to open a rowset"

Help me... I have a report which I'm loading using the following code. When I deploy the application in a client's pc "Failed to open a rowset" is the error that appears. I am using an ODBC connection. Any help would be appreciated...

Dim cr As New crptCMR

Dim crtableLogoninfos As New TableLogOnInfos
Dim crtableLogoninfo As New TableLogOnInfo
Dim crConnectionInfo As New ConnectionInfo
Dim CrTables As Tables
Dim CrTable As Table
Dim TableCounter

Dim crReportDocument As New crptCMR

With crConnectionInfo
.ServerName = sname
.DatabaseName = dbname
.UserID = uid
.Password = pwd
End With

CrTables = crReportDocument.Database.Tables

For Each CrTable In CrTables
crtableLogoninfo = CrTable.LogOnInfo
crtableLogoninfo.ConnectionInfo = crConnectionInfo
CrTable.ApplyLogOnInfo(crtableLogoninfo)
Next

CRVcmr.ReportSource = crReportDocumentOpen the report and do verify database

"exclusive access could not be obtained.." while restoring

Hello
I am using MSDE with my application and providing our users UI to backup/restore the database. My app has just 1 database and 1 login mapped to 1 user (MyAppUser). Backup and restore functionality is using inline sql commands. Backup works fine with something like this
Private Sub Backup(
Dim cn As New SqlConnection(MyConnectionString
Tr
cn.Open(
Dim cm As New SqlComman
With c
.Connection = c
.CommandType = CommandType.Tex
.CommandText = "BACKUP DATABASE MyDB TO DISK = 'D:\Backup\a.bak' WITH INIT
.ExecuteNonQuery(
End Wit
MsgBox("Database backed up successfully!"
Catch ex As Exceptio
MsgBox(ex.Message
Finall
cn.Close(
End Tr
End Su
but when I do restore using something like this
Private Sub Restore(
Dim cn As New SqlConnection(MyConnectionString
Tr
cn.Open(
Dim cm As New SqlComman
With c
.Connection = c
.CommandType = CommandType.Tex
.CommandText = "RESTORE DATABASE MyDB FROM DISK = 'D:\Backup\a.bak' WITH RECOVERY
.ExecuteNonQuery(
End Wit
MsgBox("Database restored successfully!"
Catch ex As Exceptio
MsgBox(ex.Message
Finall
cn.Close(
End Tr
End Su
I get the "exclusive access could not be obtained.. .. database is in use" error message
I did "Use Master" in query analyser and ran the same restore sql command and it worked fine. I do not know how to use "Use master" here in ado.net. I know it has something to do with sp_Who but not sure how the syntax will fit in.
Note MyConnectionString is something like
"data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ; Password = MyPassword"
Please help. What should I do so that this works in my vb.net app.You can use cn.ChangeDatabase("master") to change database
If you are restoring over an existing database you will also need specify
WITH REPLACE.
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:C800D8D0-0D15-4352-8545-9CA47C9B4710@.microsoft.com...
> Hello,
> I am using MSDE with my application and providing our users UI to
backup/restore the database. My app has just 1 database and 1 login mapped
to 1 user (MyAppUser). Backup and restore functionality is using inline sql
commands. Backup works fine with something like this:
> Private Sub Backup()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "BACKUP DATABASE MyDB TO DISK ='D:\Backup\a.bak' WITH INIT"
> .ExecuteNonQuery()
> End With
> MsgBox("Database backed up successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> but when I do restore using something like this:
> Private Sub Restore()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "RESTORE DATABASE MyDB FROM DISK ='D:\Backup\a.bak' WITH RECOVERY"
> .ExecuteNonQuery()
> End With
> MsgBox("Database restored successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> I get the "exclusive access could not be obtained.. .. database is in use"
error message.
> I did "Use Master" in query analyser and ran the same restore sql command
and it worked fine. I do not know how to use "Use master" here in ado.net.
I know it has something to do with sp_Who but not sure how the syntax will
fit in.
> Note MyConnectionString is something like:
> "data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ;
Password = MyPassword"
> Please help. What should I do so that this works in my vb.net app.

"exclusive access could not be obtained.." while restoring

Hello,
I am using MSDE with my application and providing our users UI to backup/res
tore the database. My app has just 1 database and 1 login mapped to 1 user
(MyAppUser). Backup and restore functionality is using inline sql commands.
Backup works fine with so
mething like this:
Private Sub Backup()
Dim cn As New SqlConnection(MyConnectionString)
Try
cn.Open()
Dim cm As New SqlCommand
With cm
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "BACKUP DATABASE MyDB TO DISK = 'D:\Backup\a.bak' WITH INIT"
.ExecuteNonQuery()
End With
MsgBox("Database backed up successfully!")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cn.Close()
End Try
End Sub
but when I do restore using something like this:
Private Sub Restore()
Dim cn As New SqlConnection(MyConnectionString)
Try
cn.Open()
Dim cm As New SqlCommand
With cm
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "RESTORE DATABASE MyDB FROM DISK = 'D:\Backup\a.bak' WITH RE
COVERY"
.ExecuteNonQuery()
End With
MsgBox("Database restored successfully!")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cn.Close()
End Try
End Sub
I get the "exclusive access could not be obtained.. .. database is in use" e
rror message.
I did "Use Master" in query analyser and ran the same restore sql command an
d it worked fine. I do not know how to use "Use master" here in ado.net. I
know it has something to do with sp_Who but not sure how the syntax will fi
t in.
Note MyConnectionString is something like:
"data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ; Pa
ssword = MyPassword"
Please help. What should I do so that this works in my vb.net app.You can use cn.ChangeDatabase("master") to change database
If you are restoring over an existing database you will also need specify
WITH REPLACE.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:C800D8D0-0D15-4352-8545-9CA47C9B4710@.microsoft.com...
> Hello,
> I am using MSDE with my application and providing our users UI to
backup/restore the database. My app has just 1 database and 1 login mapped
to 1 user (MyAppUser). Backup and restore functionality is using inline sql
commands. Backup works fine with something like this:
> Private Sub Backup()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "BACKUP DATABASE MyDB TO DISK =
'D:\Backup\a.bak' WITH INIT"
> .ExecuteNonQuery()
> End With
> MsgBox("Database backed up successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> but when I do restore using something like this:
> Private Sub Restore()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "RESTORE DATABASE MyDB FROM DISK =
'D:\Backup\a.bak' WITH RECOVERY"
> .ExecuteNonQuery()
> End With
> MsgBox("Database restored successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> I get the "exclusive access could not be obtained.. .. database is in use"
error message.
> I did "Use Master" in query analyser and ran the same restore sql command
and it worked fine. I do not know how to use "Use master" here in ado.net.
I know it has something to do with sp_Who but not sure how the syntax will
fit in.
> Note MyConnectionString is something like:
> "data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ;
Password = MyPassword"
> Please help. What should I do so that this works in my vb.net app.

"exclusive access could not be obtained.." while restoring

Hello,
I am using MSDE with my application and providing our users UI to backup/restore the database. My app has just 1 database and 1 login mapped to 1 user (MyAppUser). Backup and restore functionality is using inline sql commands. Backup works fine with so
mething like this:
Private Sub Backup()
Dim cn As New SqlConnection(MyConnectionString)
Try
cn.Open()
Dim cm As New SqlCommand
With cm
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "BACKUP DATABASE MyDB TO DISK = 'D:\Backup\a.bak' WITH INIT"
.ExecuteNonQuery()
End With
MsgBox("Database backed up successfully!")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cn.Close()
End Try
End Sub
but when I do restore using something like this:
Private Sub Restore()
Dim cn As New SqlConnection(MyConnectionString)
Try
cn.Open()
Dim cm As New SqlCommand
With cm
.Connection = cn
.CommandType = CommandType.Text
.CommandText = "RESTORE DATABASE MyDB FROM DISK = 'D:\Backup\a.bak' WITH RECOVERY"
.ExecuteNonQuery()
End With
MsgBox("Database restored successfully!")
Catch ex As Exception
MsgBox(ex.Message)
Finally
cn.Close()
End Try
End Sub
I get the "exclusive access could not be obtained.. .. database is in use" error message.
I did "Use Master" in query analyser and ran the same restore sql command and it worked fine. I do not know how to use "Use master" here in ado.net. I know it has something to do with sp_Who but not sure how the syntax will fit in.
Note MyConnectionString is something like:
"data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ; Password = MyPassword"
Please help. What should I do so that this works in my vb.net app.
You can use cn.ChangeDatabase("master") to change database
If you are restoring over an existing database you will also need specify
WITH REPLACE.
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"newbie" <anonymous@.discussions.microsoft.com> wrote in message
news:C800D8D0-0D15-4352-8545-9CA47C9B4710@.microsoft.com...
> Hello,
> I am using MSDE with my application and providing our users UI to
backup/restore the database. My app has just 1 database and 1 login mapped
to 1 user (MyAppUser). Backup and restore functionality is using inline sql
commands. Backup works fine with something like this:
> Private Sub Backup()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "BACKUP DATABASE MyDB TO DISK =
'D:\Backup\a.bak' WITH INIT"
> .ExecuteNonQuery()
> End With
> MsgBox("Database backed up successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> but when I do restore using something like this:
> Private Sub Restore()
> Dim cn As New SqlConnection(MyConnectionString)
> Try
> cn.Open()
> Dim cm As New SqlCommand
> With cm
> .Connection = cn
> .CommandType = CommandType.Text
> .CommandText = "RESTORE DATABASE MyDB FROM DISK =
'D:\Backup\a.bak' WITH RECOVERY"
> .ExecuteNonQuery()
> End With
> MsgBox("Database restored successfully!")
> Catch ex As Exception
> MsgBox(ex.Message)
> Finally
> cn.Close()
> End Try
> End Sub
> I get the "exclusive access could not be obtained.. .. database is in use"
error message.
> I did "Use Master" in query analyser and ran the same restore sql command
and it worked fine. I do not know how to use "Use master" here in ado.net.
I know it has something to do with sp_Who but not sure how the syntax will
fit in.
> Note MyConnectionString is something like:
> "data source=(local)\MyCompany;initial catalog=MyDB;User ID = MyAppUser ;
Password = MyPassword"
> Please help. What should I do so that this works in my vb.net app.