"Refresh" an SQLDataSource object programmatically

Background - I have a page that uses a numeric value stored in a Session object variable as the parameter for three different SQLDataSource objects, which provide data to two asp:Repeaters and an asp:DataList. Also, in the Page_Load, I use this value to to seed a stored procedure and an SQLDataReader to populate several unbound Labels. This works fine. In addition, I have a collection of 6 TextBoxes, an unbound Listbox, and two Buttons to allow the user to do searching and selection of potential matches. This basically identifies a new numeric value that I store in the Session variable and PostBack the page (via one of the buttons). This also works fine.

Problem - I have been tasked with taking a different page and adding six textboxes to collect the search values, but to post over to this page, populate the existing search-oriented TextBoxes, adn programmatically triggering the search. Furthermore, I have to detect the number of matching records and, if only 1, have the Repeaters and DataList display the results based on the newly selected record's key numeric value, as well as populating the unbound Labels. I have managed to get all of this accomplished except for programmatically triggering the Repeaters and DataList "refresh". These controls only populate as expected if a button is clicked a subsequent time, which makes sense, since that would trigger a PostBack and the Page_Load uses the new saved numeric key value from the Session.

My history in app development is largely from Windows Forms development (VB6), this is my second foray into Web Form dev with ASP.NET 2.0. I am willing to acceptthat what I am trying to do does not fit into the ASP environment, but I have to think that this is something that has been done before, and (hopefully) there is a way to do what I need.

Any ideas, oh great and wise Forum readers? *smile*

I don't think I have fully understood your problem. But when the user clicks search button you need to refresh the sqldatasource and call your_repeaters_or_datalist.DataBind() to refresh repeaters or datalist. If you have code in Post_Back you don't want to trigger you can include it in
if (!Page.IsPostBack)
{
////your code
}

|||

Thanks for the response. Hopefully this helps clarify my situation: The source page has several text boxes that mirror those on the target page. When a specific button on the source page is clicked, the contents of the source text boxes is written to the Session object and a redirect cross-posts to the target page. The Page_Load on the target page checks IsPostback and if it is a cross-page post, the code checks the origin of the action (via a Session variable), and updates the textboxes on the target page with the search parameters. In code, I poppulate a listbox (with results of a SQL stored procedure) with potential matches. If more than one match is returned, I want it to stop and await user interaction (And I have it at that point.) However, if only one match is returned, I have tried to save the key record value back to the Session object, so it can be available to the SQLDataSources that use it as its source value. This is not occurring.

IIn your response, you mentioned "you need to refresh the sqldatasource and call your_repeaters_or_datalist.DataBind() to refresh". That (refreshing the sqldatasource) is what I am trying to do. How can I get them to refresh without issuing an additional postback to the target page? Is there a method that I need to invoke on the sqldatasource objects to do that? It sounds like I am close, but not quite sure what to do at this point.

|||

Is there a method that I need to invoke on the sqldatasource objects to do that?

yes. Assume DataList control yourdatalist is bound to sqldatasource camDS,call following code to refresh:

camDS.SelectCommand ="searchcam";
camDS.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
camDS.SelectParameters.Add("keyword1","para1 ");
yourdatalist.DataBind();

How can I get them to refresh without issuing an additional postback to the target page?

I don't think this can be achieved without post back since data is saved at the server.

"Object reference not set to an instance of an object. " and connection string

Hi, I am deploying my web through a web hosting services which provides SQL database support. I got following errors whenever I try to open my webpage,which never happen when I run my web on the local machine.

I have my connection string configured in my web.config as below:

<addname="ArtHouseConnection"connectionString="Server=serveripaddress; Integrated Security=True; Database=arteh3_database;User Id=username;Password=password;"providerName="System.Data.SqlClient"/>

<addname="ASPNETDBConnectionString1"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient"/>

This is the source code where error was generated:

public

staticclass ArtHouseConfiguration

{

//cache connection stringprivatereadonlystaticstring dbConnectionString;//cache data provider nameprivatereadonlystaticstring dbProviderName;

privatereadonlystaticstring siteName;

//initialize constructor propertiesstatic ArtHouseConfiguration()

{

dbConnectionString = ConfigurationManager.ConnectionStrings[

"ArtHouseConnection"].ConnectionString;

dbProviderName = ConfigurationManager.ConnectionStrings[

"ArtHouseConnection"].ProviderName;

siteName = ConfigurationManager.AppSettings["SiteName"];

}

and finally this is the error mg I got:

Object reference not set to an instance of an object.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

Line 30: static ArtHouseConfiguration()Line 31: {Line 32: dbConnectionString = ConfigurationManager.ConnectionStrings["ArtHouseConnection"].ConnectionString;Line 33: dbProviderName = ConfigurationManager.ConnectionStrings["ArtHouseConnection"].ProviderName;Line 34:

Source File:d:\inetpub\vhosts\artehouse.org\httpdocs\App_Code\ArtHouseConfiguration.cs Line:32

Stack Trace:

[NullReferenceException: Object reference not set to an instance of an object.] ArtHouseConfiguration..cctor() in d:\inetpub\vhosts\artehouse.org\httpdocs\App_Code\ArtHouseConfiguration.cs:32[TypeInitializationException: The type initializer for 'ArtHouseConfiguration' threw an exception.] ArtHouseConfiguration.get_EnableErrorLogEmail() in d:\inetpub\vhosts\artehouse.org\httpdocs\App_Code\ArtHouseConfiguration.cs:74 Utilities.LogError(Exception ex) in d:\inetpub\vhosts\artehouse.org\httpdocs\App_Code\Utilities.cs:100 ASP.global_asax.Application_Error(Object sender, EventArgs e) in d:\inetpub\vhosts\artehouse.org\httpdocs\Global.asax:20 System.EventHandler.Invoke(Object sender, EventArgs e) +0 System.Web.HttpApplication.RaiseOnError() +182

Anyone got any ideas? thank you!

Hi genjioo,

This seems to have something to do with the web.config file.

Please check if the connections strings have been put under the configuration\connectionStrings section. Here is a typical sample

<?xml version="1.0" encoding="utf-8"?>
<configuration>
<connectionStrings>
<add name="ConnStr1" connectionString="LocalSqlServer: data source=127.0.0.1;Integrated Security=SSPI;Initial Catalog=aspnetdb"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>

If it is not put under this, the ConfigurationManager.ConnectionStrings will not be able to find it.

"Object reference not set to an instance of an object" When Retrieving Data/Schema in Desi

Hi There,

This is related to a ms access database but since I use the SqlDataSource control I thought I should post here.
I have a project that I was working on with this ms access db and using sql controls, everything was working just fine
since one day I started getting "Object reference not set to an instance of an object" messages when I try to design
a query or retrieve a schema, nothing works at design time anymore but at runtime everything is perfect, its a lot
of work for me now to create columns,schemas and everything manually, I've tried reinstalling visualstudio, ado components
but nothing seems to fix it, did this ever happen to any of you guys?

any tip is really appreciated

thanks a lot

Hi faguiar,

You wouldn't have to use DataSet Typed. You should have to use BusinessEntities.

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||

but it used to work fine before, I'm pretty sure this is a nasty ide bug that needs a hack but I can't find a solution myself.

thank you

"Object is required" (DMO) ?

Dear all,
are you seeing something weird here?
Dim oServer As SQLDMO.SQLServer2
'Dim oServer As SQLDMO.SQLServer2
For Each oServer In oApplication.ListAvailableSQLServers
Debug.Print oServer.Name
Next
This snippet fails.
Let me know where failing is any clue...
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)sorry for bothering
For i = 1 To oApplication.ListAvailableSQLServers.Count
Debug.Print oApplication.ListAvailableSQLServers.Item(i)
Next
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"Enric" wrote:

> Dear all,
> are you seeing something weird here?
> Dim oServer As SQLDMO.SQLServer2
> 'Dim oServer As SQLDMO.SQLServer2
> For Each oServer In oApplication.ListAvailableSQLServers
> Debug.Print oServer.Name
> Next
> This snippet fails.
> Let me know where failing is any clue...
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''''s hard to provide informati
on
> without seeing the code. location: Alicante (ES)|||ListAvailableSQLServers returns a NaneList object, not a SQLServer
collection. Try something like:
Dim Name As String
For Each name in oApplication.ListAvailableSQLServers
Debug.Print name
Next name
Hope this helps.
Dan Guzman
SQL Server MVP
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:FE7AF93D-0E90-4C6F-AF11-FC5DDD846BDB@.microsoft.com...
> Dear all,
> are you seeing something weird here?
> Dim oServer As SQLDMO.SQLServer2
> 'Dim oServer As SQLDMO.SQLServer2
> For Each oServer In oApplication.ListAvailableSQLServers
> Debug.Print oServer.Name
> Next
> This snippet fails.
> Let me know where failing is any clue...
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''''s hard to provide
> information
> without seeing the code. location: Alicante (ES)

"Invalid Object.." error when using Copy SQL Server Objects task

SQL Server 2000. I have a production database, of which, I want to
make a copy. I created a DTS with the Copy SQL Server Obj Task to copy
all objects from MyDbName to MyDbName_Test (on the same instance of SQL
Server). I've used this method on other databases many times and it
completes just fine. On this db, however, I get "Invalid object name
'dbo.vwWhereUsed'" when trying to complete the task. vwWhereUsed is, of
course, one of my views. This view functions properly when ran. I
later found that the error seemed to be happening when the DTS attempts
to copy another view that calls vwWhereUsed. This other view does not
get copied to MyDbName_Test before the DTS fails, but vwWhereUsed is
copied.
My thought was that the other view is being copied to the new db before
vwWhereUsed and blowing up because vwWhereUsed is not there yet... but
from what I just explained, that doesn't seem to be the case.
On another DTS attempt to move a few tables,that have nothing to do
with vwWhereUsed, into a new clean database (MyDBName_Test) I got the
same error message (Invalid object name 'dbo.vwWhereUsed').
Interestingly enough, if I UNcheck the "Drop Destination Objects First"
option before moving those tables, the error does not occur. This does
not make sense, because it was a NEW db.. there was nothing to DROP.
Is there something I'm not seeing here? or..
Is there techniques to help me discover exactly whats happening.. or
any tips on making copies of databases using the Copy SQL Server Object
Task that may help correct this issue?
TIALindsey,
Seems like a lot of unnecessary work. Why not use a BACKUP/RESTORE
approach? Or if the DB can be offline (say afterhours)
sp_detach_db/sp_attach_db?
HTH
Jerry
"lindseyhansen" <lindsey.hansen@.fmc-na.com> wrote in message
news:1128614409.695658.177570@.o13g2000cwo.googlegroups.com...
> SQL Server 2000. I have a production database, of which, I want to
> make a copy. I created a DTS with the Copy SQL Server Obj Task to copy
> all objects from MyDbName to MyDbName_Test (on the same instance of SQL
> Server). I've used this method on other databases many times and it
> completes just fine. On this db, however, I get "Invalid object name
> 'dbo.vwWhereUsed'" when trying to complete the task. vwWhereUsed is, of
> course, one of my views. This view functions properly when ran. I
> later found that the error seemed to be happening when the DTS attempts
> to copy another view that calls vwWhereUsed. This other view does not
> get copied to MyDbName_Test before the DTS fails, but vwWhereUsed is
> copied.
> My thought was that the other view is being copied to the new db before
> vwWhereUsed and blowing up because vwWhereUsed is not there yet... but
> from what I just explained, that doesn't seem to be the case.
> On another DTS attempt to move a few tables,that have nothing to do
> with vwWhereUsed, into a new clean database (MyDBName_Test) I got the
> same error message (Invalid object name 'dbo.vwWhereUsed').
> Interestingly enough, if I UNcheck the "Drop Destination Objects First"
> option before moving those tables, the error does not occur. This does
> not make sense, because it was a NEW db.. there was nothing to DROP.
>
> Is there something I'm not seeing here? or..
> Is there techniques to help me discover exactly whats happening.. or
> any tips on making copies of databases using the Copy SQL Server Object
> Task that may help correct this issue?
> TIA
>|||Thanks Jerry.. I haven't tried that before.. I'll give it a shot. It
still makes me nervous, however, that I can't get the dts to complete
successfully. I've run the dbcc check on the database and everything
is fine. I'm not sure what else to look at.
Thanks, though.

"Invalid Object.." error when using Copy SQL Server Objects task

SQL Server 2000. I have a production database, of which, I want to
make a copy. I created a DTS with the Copy SQL Server Obj Task to copy
all objects from MyDbName to MyDbName_Test (on the same instance of SQL
Server). I've used this method on other databases many times and it
completes just fine. On this db, however, I get "Invalid object name
'dbo.vwWhereUsed'" when trying to complete the task. vwWhereUsed is, of
course, one of my views. This view functions properly when ran. I
later found that the error seemed to be happening when the DTS attempts
to copy another view that calls vwWhereUsed. This other view does not
get copied to MyDbName_Test before the DTS fails, but vwWhereUsed is
copied.
My thought was that the other view is being copied to the new db before
vwWhereUsed and blowing up because vwWhereUsed is not there yet... but
from what I just explained, that doesn't seem to be the case.
On another DTS attempt to move a few tables,that have nothing to do
with vwWhereUsed, into a new clean database (MyDBName_Test) I got the
same error message (Invalid object name 'dbo.vwWhereUsed').
Interestingly enough, if I UNcheck the "Drop Destination Objects First"
option before moving those tables, the error does not occur. This does
not make sense, because it was a NEW db.. there was nothing to DROP.
Is there something I'm not seeing here? or..
Is there techniques to help me discover exactly whats happening.. or
any tips on making copies of databases using the Copy SQL Server Object
Task that may help correct this issue?
TIA
Lindsey,
Seems like a lot of unnecessary work. Why not use a BACKUP/RESTORE
approach? Or if the DB can be offline (say afterhours)
sp_detach_db/sp_attach_db?
HTH
Jerry
"lindseyhansen" <lindsey.hansen@.fmc-na.com> wrote in message
news:1128614409.695658.177570@.o13g2000cwo.googlegr oups.com...
> SQL Server 2000. I have a production database, of which, I want to
> make a copy. I created a DTS with the Copy SQL Server Obj Task to copy
> all objects from MyDbName to MyDbName_Test (on the same instance of SQL
> Server). I've used this method on other databases many times and it
> completes just fine. On this db, however, I get "Invalid object name
> 'dbo.vwWhereUsed'" when trying to complete the task. vwWhereUsed is, of
> course, one of my views. This view functions properly when ran. I
> later found that the error seemed to be happening when the DTS attempts
> to copy another view that calls vwWhereUsed. This other view does not
> get copied to MyDbName_Test before the DTS fails, but vwWhereUsed is
> copied.
> My thought was that the other view is being copied to the new db before
> vwWhereUsed and blowing up because vwWhereUsed is not there yet... but
> from what I just explained, that doesn't seem to be the case.
> On another DTS attempt to move a few tables,that have nothing to do
> with vwWhereUsed, into a new clean database (MyDBName_Test) I got the
> same error message (Invalid object name 'dbo.vwWhereUsed').
> Interestingly enough, if I UNcheck the "Drop Destination Objects First"
> option before moving those tables, the error does not occur. This does
> not make sense, because it was a NEW db.. there was nothing to DROP.
>
> Is there something I'm not seeing here? or..
> Is there techniques to help me discover exactly whats happening.. or
> any tips on making copies of databases using the Copy SQL Server Object
> Task that may help correct this issue?
> TIA
>
|||Thanks Jerry.. I haven't tried that before.. I'll give it a shot. It
still makes me nervous, however, that I can't get the dts to complete
successfully. I've run the dbcc check on the database and everything
is fine. I'm not sure what else to look at.
Thanks, though.

"Invalid Object.." error when using Copy SQL Server Objects task

SQL Server 2000. I have a production database, of which, I want to
make a copy. I created a DTS with the Copy SQL Server Obj Task to copy
all objects from MyDbName to MyDbName_Test (on the same instance of SQL
Server). I've used this method on other databases many times and it
completes just fine. On this db, however, I get "Invalid object name
'dbo.vwWhereUsed'" when trying to complete the task. vwWhereUsed is, of
course, one of my views. This view functions properly when ran. I
later found that the error seemed to be happening when the DTS attempts
to copy another view that calls vwWhereUsed. This other view does not
get copied to MyDbName_Test before the DTS fails, but vwWhereUsed is
copied.
My thought was that the other view is being copied to the new db before
vwWhereUsed and blowing up because vwWhereUsed is not there yet... but
from what I just explained, that doesn't seem to be the case.
On another DTS attempt to move a few tables,that have nothing to do
with vwWhereUsed, into a new clean database (MyDBName_Test) I got the
same error message (Invalid object name 'dbo.vwWhereUsed').
Interestingly enough, if I UNcheck the "Drop Destination Objects First"
option before moving those tables, the error does not occur. This does
not make sense, because it was a NEW db.. there was nothing to DROP.
Is there something I'm not seeing here? or..
Is there techniques to help me discover exactly whats happening.. or
any tips on making copies of databases using the Copy SQL Server Object
Task that may help correct this issue?
TIALindsey,
Seems like a lot of unnecessary work. Why not use a BACKUP/RESTORE
approach? Or if the DB can be offline (say afterhours)
sp_detach_db/sp_attach_db?
HTH
Jerry
"lindseyhansen" <lindsey.hansen@.fmc-na.com> wrote in message
news:1128614409.695658.177570@.o13g2000cwo.googlegroups.com...
> SQL Server 2000. I have a production database, of which, I want to
> make a copy. I created a DTS with the Copy SQL Server Obj Task to copy
> all objects from MyDbName to MyDbName_Test (on the same instance of SQL
> Server). I've used this method on other databases many times and it
> completes just fine. On this db, however, I get "Invalid object name
> 'dbo.vwWhereUsed'" when trying to complete the task. vwWhereUsed is, of
> course, one of my views. This view functions properly when ran. I
> later found that the error seemed to be happening when the DTS attempts
> to copy another view that calls vwWhereUsed. This other view does not
> get copied to MyDbName_Test before the DTS fails, but vwWhereUsed is
> copied.
> My thought was that the other view is being copied to the new db before
> vwWhereUsed and blowing up because vwWhereUsed is not there yet... but
> from what I just explained, that doesn't seem to be the case.
> On another DTS attempt to move a few tables,that have nothing to do
> with vwWhereUsed, into a new clean database (MyDBName_Test) I got the
> same error message (Invalid object name 'dbo.vwWhereUsed').
> Interestingly enough, if I UNcheck the "Drop Destination Objects First"
> option before moving those tables, the error does not occur. This does
> not make sense, because it was a NEW db.. there was nothing to DROP.
>
> Is there something I'm not seeing here? or..
> Is there techniques to help me discover exactly whats happening.. or
> any tips on making copies of databases using the Copy SQL Server Object
> Task that may help correct this issue?
> TIA
>|||Thanks Jerry.. I haven't tried that before.. I'll give it a shot. It
still makes me nervous, however, that I can't get the dts to complete
successfully. I've run the dbcc check on the database and everything
is fine. I'm not sure what else to look at.
Thanks, though.

"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

"ImpersonateClient" property in Yukon

Hi,
Does anybody knows how to get the value for "ImpersonateClient" property
which is on IntegratedSecurity Object in SQL - DMO for SQL Server 2005
(Yukon).
Documentation says that the functionality has been moved to "Server
Connection" class of SMO but I couldnt find the property that will return
the result for the above.
TIA
PraThe documentation states that the ImpersonnateClient property is a Settings
class property but I don't see it implemented there. As a workaround,
another method to retrieve the value:
string proxyAccount = (string) ServerConn.ExecuteScalar(
"SELECT credential_identity" +
" FROM sys.credentials" +
" WHERE name = N'##xp_cmdshell_proxy_account##'");
I'm not sure if this a documentation or product issue. I submitted feedback
at http://lab.msdn.microsoft.com/productfeedback/.
Hope this helps.
Dan Guzman
SQL Server MVP
"Pra" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:uTPjvE8KGHA.500@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Does anybody knows how to get the value for "ImpersonateClient"
> property which is on IntegratedSecurity Object in SQL - DMO for SQL Server
> 2005 (Yukon).
> Documentation says that the functionality has been moved to "Server
> Connection" class of SMO but I couldnt find the property that will return
> the result for the above.
>
> TIA
> Pra
>

"ImpersonateClient" property in Yukon

Hi,
Does anybody knows how to get the value for "ImpersonateClient" property
which is on IntegratedSecurity Object in SQL - DMO for SQL Server 2005
(Yukon).
Documentation says that the functionality has been moved to "Server
Connection" class of SMO but I couldnt find the property that will return
the result for the above.
TIA
Prasad
The documentation states that the ImpersonnateClient property is a Settings
class property but I don't see it implemented there. As a workaround,
another method to retrieve the value:
string proxyAccount = (string) ServerConn.ExecuteScalar(
"SELECT credential_identity" +
" FROM sys.credentials" +
" WHERE name = N'##xp_cmdshell_proxy_account##'");
I'm not sure if this a documentation or product issue. I submitted feedback
at http://lab.msdn.microsoft.com/productfeedback/.
Hope this helps.
Dan Guzman
SQL Server MVP
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:uTPjvE8KGHA.500@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Does anybody knows how to get the value for "ImpersonateClient"
> property which is on IntegratedSecurity Object in SQL - DMO for SQL Server
> 2005 (Yukon).
> Documentation says that the functionality has been moved to "Server
> Connection" class of SMO but I couldnt find the property that will return
> the result for the above.
>
> TIA
> Prasad
>

"ImpersonateClient" property in Yukon

Hi,
Does anybody knows how to get the value for "ImpersonateClient" property
which is on IntegratedSecurity Object in SQL - DMO for SQL Server 2005
(Yukon).
Documentation says that the functionality has been moved to "Server
Connection" class of SMO but I couldnt find the property that will return
the result for the above.
TIA
Prasad
The documentation states that the ImpersonnateClient property is a Settings
class property but I don't see it implemented there. As a workaround,
another method to retrieve the value:
string proxyAccount = (string) ServerConn.ExecuteScalar(
"SELECT credential_identity" +
" FROM sys.credentials" +
" WHERE name = N'##xp_cmdshell_proxy_account##'");
I'm not sure if this a documentation or product issue. I submitted feedback
at http://lab.msdn.microsoft.com/productfeedback/.
Hope this helps.
Dan Guzman
SQL Server MVP
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:uTPjvE8KGHA.500@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Does anybody knows how to get the value for "ImpersonateClient"
> property which is on IntegratedSecurity Object in SQL - DMO for SQL Server
> 2005 (Yukon).
> Documentation says that the functionality has been moved to "Server
> Connection" class of SMO but I couldnt find the property that will return
> the result for the above.
>
> TIA
> Prasad
>

"ImpersonateClient" property in Yukon

Hi,
Does anybody knows how to get the value for "ImpersonateClient" property
which is on IntegratedSecurity Object in SQL - DMO for SQL Server 2005
(Yukon).
Documentation says that the functionality has been moved to "Server
Connection" class of SMO but I couldnt find the property that will return
the result for the above.
TIA
PrasadThe documentation states that the ImpersonnateClient property is a Settings
class property but I don't see it implemented there. As a workaround,
another method to retrieve the value:
string proxyAccount = (string) ServerConn.ExecuteScalar(
"SELECT credential_identity" +
" FROM sys.credentials" +
" WHERE name = N'##xp_cmdshell_proxy_account##'");
I'm not sure if this a documentation or product issue. I submitted feedback
at http://lab.msdn.microsoft.com/productfeedback/.
Hope this helps.
Dan Guzman
SQL Server MVP
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:uTPjvE8KGHA.500@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Does anybody knows how to get the value for "ImpersonateClient"
> property which is on IntegratedSecurity Object in SQL - DMO for SQL Server
> 2005 (Yukon).
> Documentation says that the functionality has been moved to "Server
> Connection" class of SMO but I couldnt find the property that will return
> the result for the above.
>
> TIA
> Prasad
>

"ImpersonateClient" property in Yukon

Hi,
Does anybody knows how to get the value for "ImpersonateClient" property
which is on IntegratedSecurity Object in SQL - DMO for SQL Server 2005
(Yukon).
Documentation says that the functionality has been moved to "Server
Connection" class of SMO but I couldnt find the property that will return
the result for the above.
TIA
Prasad
The documentation states that the ImpersonnateClient property is a Settings
class property but I don't see it implemented there. As a workaround,
another method to retrieve the value:
string proxyAccount = (string) ServerConn.ExecuteScalar(
"SELECT credential_identity" +
" FROM sys.credentials" +
" WHERE name = N'##xp_cmdshell_proxy_account##'");
I'm not sure if this a documentation or product issue. I submitted feedback
at http://lab.msdn.microsoft.com/productfeedback/.
Hope this helps.
Dan Guzman
SQL Server MVP
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:uTPjvE8KGHA.500@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Does anybody knows how to get the value for "ImpersonateClient"
> property which is on IntegratedSecurity Object in SQL - DMO for SQL Server
> 2005 (Yukon).
> Documentation says that the functionality has been moved to "Server
> Connection" class of SMO but I couldnt find the property that will return
> the result for the above.
>
> TIA
> Prasad
>

"ImpersonateClient" property in Yukon

Hi,
Does anybody knows how to get the value for "ImpersonateClient" property
which is on IntegratedSecurity Object in SQL - DMO for SQL Server 2005
(Yukon).
Documentation says that the functionality has been moved to "Server
Connection" class of SMO but I couldnt find the property that will return
the result for the above.
TIA
PrasadThe documentation states that the ImpersonnateClient property is a Settings
class property but I don't see it implemented there. As a workaround,
another method to retrieve the value:
string proxyAccount = (string) ServerConn.ExecuteScalar(
"SELECT credential_identity" +
" FROM sys.credentials" +
" WHERE name = N'##xp_cmdshell_proxy_account##'");
I'm not sure if this a documentation or product issue. I submitted feedback
at http://lab.msdn.microsoft.com/productfeedback/.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:uTPjvE8KGHA.500@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Does anybody knows how to get the value for "ImpersonateClient"
> property which is on IntegratedSecurity Object in SQL - DMO for SQL Server
> 2005 (Yukon).
> Documentation says that the functionality has been moved to "Server
> Connection" class of SMO but I couldnt find the property that will return
> the result for the above.
>
> TIA
> Prasad
>

"Display Dependencies" not showing all dependencies

Within Sql Server Enterprise, I click on an object to show dependencies, and
there is something I am not seeing here.
For example, I want to see all dependencies for a given table. It shows a
view and a couple of stored procedures that perform SELECT statements.
However, there is a stored procedure that performs UPDATE and INSERT against
that particular table, but I am not seeing on in the Display Dependencies
screen.
When I attempt to see the Dependencies screen on the stored procedure that
does UPDATE and INSERT, I am not seeing the table listed.
There seems to be a disconnect some place either in my head or in Enterprise
Manager. What am I missing? How can I display, all, I mean ALL
dependencies? For a given table, I want stored procedures listed if they do
ANYTHING on the table. And views. And functions. All dependencies,
period. How can I do that?
Because of deferred name resolution and other factors, then you won't
necessarily see all dependencies (e.g. depending on the order the objects
were created). I think one way to solve would be to recompile the stored
procedures, but I still don't think that will guarantee all dependencies
will show up... there are other things that aren't captured by sp_depends,
e.g. dynamic SQL.
http://www.aspfaq.com/
(Reverse address to reply.)
"David C" <nospam@.nospam.com> wrote in message
news:0oh6d.21182$OB2.1947@.twister.socal.rr.com...
> Within Sql Server Enterprise, I click on an object to show dependencies,
and
> there is something I am not seeing here.
> For example, I want to see all dependencies for a given table. It shows a
> view and a couple of stored procedures that perform SELECT statements.
> However, there is a stored procedure that performs UPDATE and INSERT
against
> that particular table, but I am not seeing on in the Display Dependencies
> screen.
> When I attempt to see the Dependencies screen on the stored procedure that
> does UPDATE and INSERT, I am not seeing the table listed.
> There seems to be a disconnect some place either in my head or in
Enterprise
> Manager. What am I missing? How can I display, all, I mean ALL
> dependencies? For a given table, I want stored procedures listed if they
do
> ANYTHING on the table. And views. And functions. All dependencies,
> period. How can I do that?
>
|||So do you have a suggestion?
I would like to re-engineer a table (changing a column type), so I need to
find all objects that talk to this table.
If "Display Dependencies" does not do what it's supposed to, then what
purpose does it serve other than giving partial answers?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uZaO$RYpEHA.1960@.TK2MSFTNGP10.phx.gbl...
> Because of deferred name resolution and other factors, then you won't
> necessarily see all dependencies (e.g. depending on the order the objects
> were created). I think one way to solve would be to recompile the stored
> procedures, but I still don't think that will guarantee all dependencies
> will show up... there are other things that aren't captured by sp_depends,
> e.g. dynamic SQL.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "David C" <nospam@.nospam.com> wrote in message
> news:0oh6d.21182$OB2.1947@.twister.socal.rr.com...
> and
> against
> Enterprise
> do
>
|||> If "Display Dependencies" does not do what it's supposed to, then what
> purpose does it serve other than giving partial answers?
In some scenarios, it does give the right answer (e.g. if you compile all of
your stored procedures after all their dependent objects have been created,
don't alter anything, and don't use dynamic SQL).
For all other scenarios, my only suggestion is to parse the text from
syscomments or INFORMATION_SCHEMA.ROUTINES for the name of your table.
And please remember, the people here are merely trying to help you. We did
not design SQL Server, have no control over the dependencies functionality,
and can't explain why they bothered putting it into the product.
http://www.aspfaq.com/
(Reverse address to reply.)
|||
> In some scenarios, it does give the right answer (e.g. if you compile all
> of
> your stored procedures after all their dependent objects have been
> created,
> don't alter anything, and don't use dynamic SQL).
>
How does one recompile all the stored procedures with one command? Is there
a way to do that? How about Views and User functions?
> For all other scenarios, my only suggestion is to parse the text from
> syscomments or INFORMATION_SCHEMA.ROUTINES for the name of your table.
> And please remember, the people here are merely trying to help you. We
> did
> not design SQL Server, have no control over the dependencies
> functionality,
> and can't explain why they bothered putting it into the product.
Your point is well taken.
|||> How does one recompile all the stored procedures with one command?
If you alter one table, do you have to recompile all stored procedures?
Unlikely.

> How does one recompile all the stored procedures with one command? Is
there
> a way to do that? How about Views and User functions?
This will generate the command for procs and functions, but not run it.
SELECT
CHAR(13)+CHAR(10)+'EXEC sp_recompile '''+ROUTINE_NAME+''';'
+CHAR(13)+CHAR(10)+'EXEC '+ROUTINE_NAME+';'
+CHAR(13)+CHAR(10)+'GO;'
FROM
INFORMATION_SCHEMA.ROUTINES
For views,
SELECT
CHAR(13)+CHAR(10)+'EXEC sp_recompile '''+TABLE_NAME+''';'
+CHAR(13)+CHAR(10)+'EXEC '+TABLE_NAME+';'
+CHAR(13)+CHAR(10)+'GO;'
INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='view'
In both cases, use Query Analyzer with results to text. Run the code, and
copy the output from the bottom pane to the upper pane or a new window. You
may want to add filters to leave out system or other specific objects.
Keep in mind that this still does not guarantee that sp_depends or some GUI
red herring like "Display Dependencies" will work flawlessly, because of
other factors I mentioned earlier.
http://www.aspfaq.com/
(Reverse address to reply.)

"Display Dependencies" not showing all dependencies

Within Sql Server Enterprise, I click on an object to show dependencies, and
there is something I am not seeing here.
For example, I want to see all dependencies for a given table. It shows a
view and a couple of stored procedures that perform SELECT statements.
However, there is a stored procedure that performs UPDATE and INSERT against
that particular table, but I am not seeing on in the Display Dependencies
screen.
When I attempt to see the Dependencies screen on the stored procedure that
does UPDATE and INSERT, I am not seeing the table listed.
There seems to be a disconnect some place either in my head or in Enterprise
Manager. What am I missing? How can I display, all, I mean ALL
dependencies? For a given table, I want stored procedures listed if they do
ANYTHING on the table. And views. And functions. All dependencies,
period. How can I do that?Because of deferred name resolution and other factors, then you won't
necessarily see all dependencies (e.g. depending on the order the objects
were created). I think one way to solve would be to recompile the stored
procedures, but I still don't think that will guarantee all dependencies
will show up... there are other things that aren't captured by sp_depends,
e.g. dynamic SQL.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"David C" <nospam@.nospam.com> wrote in message
news:0oh6d.21182$OB2.1947@.twister.socal.rr.com...
> Within Sql Server Enterprise, I click on an object to show dependencies,
and
> there is something I am not seeing here.
> For example, I want to see all dependencies for a given table. It shows a
> view and a couple of stored procedures that perform SELECT statements.
> However, there is a stored procedure that performs UPDATE and INSERT
against
> that particular table, but I am not seeing on in the Display Dependencies
> screen.
> When I attempt to see the Dependencies screen on the stored procedure that
> does UPDATE and INSERT, I am not seeing the table listed.
> There seems to be a disconnect some place either in my head or in
Enterprise
> Manager. What am I missing? How can I display, all, I mean ALL
> dependencies? For a given table, I want stored procedures listed if they
do
> ANYTHING on the table. And views. And functions. All dependencies,
> period. How can I do that?
>|||So do you have a suggestion?
I would like to re-engineer a table (changing a column type), so I need to
find all objects that talk to this table.
If "Display Dependencies" does not do what it's supposed to, then what
purpose does it serve other than giving partial answers?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uZaO$RYpEHA.1960@.TK2MSFTNGP10.phx.gbl...
> Because of deferred name resolution and other factors, then you won't
> necessarily see all dependencies (e.g. depending on the order the objects
> were created). I think one way to solve would be to recompile the stored
> procedures, but I still don't think that will guarantee all dependencies
> will show up... there are other things that aren't captured by sp_depends,
> e.g. dynamic SQL.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "David C" <nospam@.nospam.com> wrote in message
> news:0oh6d.21182$OB2.1947@.twister.socal.rr.com...
>> Within Sql Server Enterprise, I click on an object to show dependencies,
> and
>> there is something I am not seeing here.
>> For example, I want to see all dependencies for a given table. It shows
>> a
>> view and a couple of stored procedures that perform SELECT statements.
>> However, there is a stored procedure that performs UPDATE and INSERT
> against
>> that particular table, but I am not seeing on in the Display Dependencies
>> screen.
>> When I attempt to see the Dependencies screen on the stored procedure
>> that
>> does UPDATE and INSERT, I am not seeing the table listed.
>> There seems to be a disconnect some place either in my head or in
> Enterprise
>> Manager. What am I missing? How can I display, all, I mean ALL
>> dependencies? For a given table, I want stored procedures listed if they
> do
>> ANYTHING on the table. And views. And functions. All dependencies,
>> period. How can I do that?
>>
>|||> If "Display Dependencies" does not do what it's supposed to, then what
> purpose does it serve other than giving partial answers?
In some scenarios, it does give the right answer (e.g. if you compile all of
your stored procedures after all their dependent objects have been created,
don't alter anything, and don't use dynamic SQL).
For all other scenarios, my only suggestion is to parse the text from
syscomments or INFORMATION_SCHEMA.ROUTINES for the name of your table.
And please remember, the people here are merely trying to help you. We did
not design SQL Server, have no control over the dependencies functionality,
and can't explain why they bothered putting it into the product.
--
http://www.aspfaq.com/
(Reverse address to reply.)|||> In some scenarios, it does give the right answer (e.g. if you compile all
> of
> your stored procedures after all their dependent objects have been
> created,
> don't alter anything, and don't use dynamic SQL).
>
How does one recompile all the stored procedures with one command? Is there
a way to do that? How about Views and User functions?
> For all other scenarios, my only suggestion is to parse the text from
> syscomments or INFORMATION_SCHEMA.ROUTINES for the name of your table.
> And please remember, the people here are merely trying to help you. We
> did
> not design SQL Server, have no control over the dependencies
> functionality,
> and can't explain why they bothered putting it into the product.
Your point is well taken.|||> How does one recompile all the stored procedures with one command?
If you alter one table, do you have to recompile all stored procedures?
Unlikely.
> How does one recompile all the stored procedures with one command? Is
there
> a way to do that? How about Views and User functions?
This will generate the command for procs and functions, but not run it.
SELECT
CHAR(13)+CHAR(10)+'EXEC sp_recompile '''+ROUTINE_NAME+''';'
+CHAR(13)+CHAR(10)+'EXEC '+ROUTINE_NAME+';'
+CHAR(13)+CHAR(10)+'GO;'
FROM
INFORMATION_SCHEMA.ROUTINES
For views,
SELECT
CHAR(13)+CHAR(10)+'EXEC sp_recompile '''+TABLE_NAME+''';'
+CHAR(13)+CHAR(10)+'EXEC '+TABLE_NAME+';'
+CHAR(13)+CHAR(10)+'GO;'
INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='view'
In both cases, use Query Analyzer with results to text. Run the code, and
copy the output from the bottom pane to the upper pane or a new window. You
may want to add filters to leave out system or other specific objects.
Keep in mind that this still does not guarantee that sp_depends or some GUI
red herring like "Display Dependencies" will work flawlessly, because of
other factors I mentioned earlier.
--
http://www.aspfaq.com/
(Reverse address to reply.)

"Database diagram support object cannot be installed...

...because this database does not have a valid owner. To continue, first use
the Files page of the Database Properties dialog box or the ALTER
AUTHORIZATION statement to set the database owner to a valid login, then add
the database diagram support objects. "
I am getting the previous error when trying to create diagrams in a
database. The database was originally created with a domain user account, but
I ran the script "EXEC sp_changedbowner 'sa'" to change the owner to sa.
Still having problems.
Does anyone know how I could resolve this issue?Are you on SP1? If not, check the compatability mode of the database, it
needs to be set to 90 in RTM for diagrams to work
select compatibility_level
from sys.databases
where name = 'database name'
To alter it use sp_dbcmptlevel e.g.
EXEC sp_dbcmptlevel 'pubs', 90
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:100CF59A-E171-46B7-969A-18DF27AF8AEE@.microsoft.com...
> ...because this database does not have a valid owner. To continue, first
> use
> the Files page of the Database Properties dialog box or the ALTER
> AUTHORIZATION statement to set the database owner to a valid login, then
> add
> the database diagram support objects. "
> I am getting the previous error when trying to create diagrams in a
> database. The database was originally created with a domain user account,
> but
> I ran the script "EXEC sp_changedbowner 'sa'" to change the owner to sa.
> Still having problems.
> Does anyone know how I could resolve this issue?|||That was it! Thank you!
"Jasper Smith" wrote:
> Are you on SP1? If not, check the compatability mode of the database, it
> needs to be set to 90 in RTM for diagrams to work
> select compatibility_level
> from sys.databases
> where name = 'database name'
> To alter it use sp_dbcmptlevel e.g.
> EXEC sp_dbcmptlevel 'pubs', 90
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:100CF59A-E171-46B7-969A-18DF27AF8AEE@.microsoft.com...
> > ...because this database does not have a valid owner. To continue, first
> > use
> > the Files page of the Database Properties dialog box or the ALTER
> > AUTHORIZATION statement to set the database owner to a valid login, then
> > add
> > the database diagram support objects. "
> >
> > I am getting the previous error when trying to create diagrams in a
> > database. The database was originally created with a domain user account,
> > but
> > I ran the script "EXEC sp_changedbowner 'sa'" to change the owner to sa.
> > Still having problems.
> >
> > Does anyone know how I could resolve this issue?
>
>

"Database diagram support object cannot be installed...

...because this database does not have a valid owner. To continue, first us
e
the Files page of the Database Properties dialog box or the ALTER
AUTHORIZATION statement to set the database owner to a valid login, then add
the database diagram support objects. "
I am getting the previous error when trying to create diagrams in a
database. The database was originally created with a domain user account, bu
t
I ran the script "EXEC sp_changedbowner 'sa'" to change the owner to sa.
Still having problems.
Does anyone know how I could resolve this issue?Are you on SP1? If not, check the compatability mode of the database, it
needs to be set to 90 in RTM for diagrams to work
select compatibility_level
from sys.databases
where name = 'database name'
To alter it use sp_dbcmptlevel e.g.
EXEC sp_dbcmptlevel 'pubs', 90
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:100CF59A-E171-46B7-969A-18DF27AF8AEE@.microsoft.com...
> ...because this database does not have a valid owner. To continue, first
> use
> the Files page of the Database Properties dialog box or the ALTER
> AUTHORIZATION statement to set the database owner to a valid login, then
> add
> the database diagram support objects. "
> I am getting the previous error when trying to create diagrams in a
> database. The database was originally created with a domain user account,
> but
> I ran the script "EXEC sp_changedbowner 'sa'" to change the owner to sa.
> Still having problems.
> Does anyone know how I could resolve this issue?|||That was it! Thank you!
"Jasper Smith" wrote:

> Are you on SP1? If not, check the compatability mode of the database, it
> needs to be set to 90 in RTM for diagrams to work
> select compatibility_level
> from sys.databases
> where name = 'database name'
> To alter it use sp_dbcmptlevel e.g.
> EXEC sp_dbcmptlevel 'pubs', 90
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:100CF59A-E171-46B7-969A-18DF27AF8AEE@.microsoft.com...
>
>

"Data Source=%?SERVER%"... ?

Hi all...

I have a data source string being passed to a SqlConnection object in the format "Data Source=%?SERVER%[...]". Now, to me this kind of looks like an environment variable query, but I'm not sure (and I can't find any system environment variables with that name on the server. Can anyone tell me, specifically, what the "?" is for?

This project uses VS2005, C# 2.0

Thanks in advance.

It looks to me like it is meant to be a value that is prompted for. Where are you getting the connection string from?

|||

There's no prompting going on - that's just the value in the string. Here's a sample:

1string strCon ="Data Source=%?SERVER%;Initial Catalog=MyDatabase;Integrated Security=False;Persist Security Info=True;User ID=sa;Password=%?SAPWD%;Encrypt=False";2SqlConnection con =new SqlConnection(strCon);

Thanks again.|||Data Source is the name of your sqlserver instance. like connectionString="Data Source= MSHOME\SQLEXPRESS;Initial Catalog=....|||

Well if you don't know of any reason why that is there and you know the connection information put the connection string in the web.config and call it a day. :)

|||

Guess that's what I'm going to have to go with (web.config) :P

Was just hoping somebody knew if there was something along the lines of a search-&-replace-before-compile feature in VS2005...

Thanks

" the object invoked has disconnected from its clients"

I′m configuing a transacional replicationand after concluding it does not function, it is in the status initiating and later it gives the message: " the object invoked has disconnected from its clients"
Can you help me?
I have never heard of this error wrt replication.
The following article indicates it is a windows issue and the resolutions hopefully will help: http://www.kbalertz.com/Feedback_293631.aspx
Another poster upgraded his MDAC version to 2.8 and claims this solved his issue, although upgrading MDAC would not seem to be connected to the above article. Anyway, please let us know if you go down either route and how it works out.
Regards,
Paul Ibison