Some Questions About SQLCacheDependency?

Sep 30, 2007

Hi all,
I've been able to successfully use SQLCacheDependency and my test application is using only one database table to test SQLCacheDependency. Now, I was reading "Programming Microsoft ASP.NET 2.0" by Dino Esposito and I've couple of questions.
a)-SQLCache will expire on Insert, Update and Delete queries on the database table? Am I right?
 
b)-Right now I'm using SQLCacheDependency on one table, can I use it on more than one table ? If I can't , can you please give me some ideas on how to use SQLCacheDependency on more than one table?
 
c)-Lastly, If large number of clients are using my application, what about the performance gains/losses if I use SQLCacheDependency? 
 
I will really appreciate your help. 
 
Thanks,
Oltmans 

View 1 Replies


ADVERTISEMENT

Some Questions About SQLCacheDependency?

Oct 1, 2007

Hi All,I've successfully implemented an example of SQLCacheDependency on one database table. I was reading "Programming Microsoft ASP.NET 2.0" by Dino Esposito and I've couple of questions. I will really appreciate your help.a)-Can I use SQLCacheDependency on more than one database tables? If I can't then I will appreciate if you can give me couple of hints as to how to do this.b)-As I understand SQLCache on a table will expire if any Insert and Update SQL operations happen on that table.c)-Our software is being used by many clients. Do you think SQLCacheDependency will have some sort of performance hits?I will really appreciate any help.Thanks,Oltmans

View 1 Replies View Related

SqlCacheDependency Without Polling

Jul 20, 2006

I've been having trouble getting the SqlCacheDependency to work without polling (which is set up through SqlCacheDependencyAdmin or aspnet_regsql). If you check out the documentation it says things like:
Microsoft:

View 18 Replies View Related

Can't Enable SQLCacheDependency

Feb 1, 2007

I have a SQL 2005 database and am running the 2.0 framework. I cannot seem to enable SQL caching on the db. I'm using the command line tool as follows:
C:WINDOWSMicrosoft.NETFrameworkv2.0.50727>aspnet_regsql -S 99.99.999.999 -Uwebteam -P sa -t tblname -d dbname -et
Enabling the table for SQL cache dependency.
.An error has happened.  Details of the exception:The database 'dbname' is not enabled for SQL cache notification.
To enable a database for SQL cache notification, please use the System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications method, or the command line toolaspnet_regsql. To use the tool, please run 'aspnet_regsql.exe -?' for more information.
C:WINDOWSMicrosoft.NETFrameworkv2.0.50727>
 
I've replaced the ip address, dbname and tblname with ficticious names for obvious reasons here in the post.
What command can I enter to enable the database for caching?  

View 4 Replies View Related

SqlCacheDependency With SQLServer2005

May 21, 2007

I have implemented a caching strategy using the sqlcachedependency and sql server 2005 backend using the broker service.This works fine and well when i am connecting to the SQL Server 2005 under service account that is in the role db_owner. In a production enivironment i am reluctant to do this so i created another service account that only has execute permissions on the stored procedures.When i use this limited service account for my ASP.net web application, the broker service does not send any messages to the web app to invalid the cache. When checking the event log and SQL profiler i get errors all relating to the user not having access to the SqlQueryNotificationService queue. So i did a lot of googling and tried running the grant scripts below with no luck using this limited service account. Keep in mind everything works fine if i use an account with db_owner priviledges. These are the grants i have tried based on numerious articles  GRANT CREATE PROCEDURE TO three_d_ss_loginGRANT CREATE QUEUE TO three_d_ss_loginGRANT CREATE SERVICE TO three_d_ss_loginGRANT SUBSCRIBE QUERY NOTIFICATIONS TO three_d_ss_loginGRANT RECEIVE ON QueryNotificationErrorsQueue TO three_d_ss_loginGRANT REFERENCES ON CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO three_d_ss_login These are the grants i have tried that does not work GRANT SEND ON SERVICE::SqlQueryNotificationService TO three_d_ss_loginGRANT RECEIVE on SqlQueryNotificationService_DefaultQueue to three_d_ss_loginCan some one suggest what i need to do to get sqlcacheddependencies to work with a sql2005 backend under a limited priviledged service account?  ThanksJim 

View 4 Replies View Related

SqlCacheDependency, Not Caching

Jun 25, 2007

Hi, I'm trying to set up a SqlCacheDependency using the Query notifications of SQL Server 2005.
I haven't even got to the point of testing the notifications part.  My problem is that my DataTable is not
even getting stored in the cache when I insert it.  The cache seems to be getting invalided as soon
as I add the DataTable.  Here is my code:
(am trying to get a simple example working first)protected void Page_Load(object sender, EventArgs e)
{    DataTable results = (DataTable)HttpRuntime.Cache.Get("supplyFunctions");
      if (results == null)
     {          Response.Write("Cache Invalidated, hitting DB. TIME: " + DateTime.Now.ToString());
          results = getSupplyFunctions();
     }
    else
    {          Response.Write("got from Cache, TIME: " + DateTime.Now);
    }     GridView1.DataSource = results;
     GridView1.DataBind();
}private DataTable getSupplyFunctions()
{     DataTable results = new DataTable();     using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString()) )
     {         using (SqlCommand command = connection.CreateCommand())
          {
                  command.CommandText = "schema.myStoredProc";                  command.CommandType = CommandType.StoredProcedure;
                  SqlCacheDependency dependency = new SqlCacheDependency(command);                  SqlDataAdapter adapter = new SqlDataAdapter();
                 adapter.SelectCommand = command;                 DataSet dataset = new DataSet();
                 adapter.Fill(dataset);
                 results = dataset.Tables[0];
                 HttpRuntime.Cache.Insert("supplyFunctions", results, dependency);
          }
 
  }  return results;
}
 Also, whats weird is that when I keep refreshing the page, after like 5 to 10 minutes it works, and starts caching the data. 
This disturbs me-- I would like to know what is going on.   I am pretty sure my stored procedure doesn't break the rules of the query notifications.  Can someone help me out?!??!
Thanks!
 

View 2 Replies View Related

SqlCacheDependency And PollTime

Oct 10, 2007

I am using SqlCacheDependency to monitor a database table for changes.
Unfortunately, it is polling the table too often and is causing
performance issues. It seems to poll at least once a second. So now I'm
trying to find a way to adjust it so that it polls about once every 10
seconds.The
code that uses the SqlCache Dependency is located in a Class Library
Project that is used by my web application project. The code is as
follows:// in the class constructorSqlDependency.Start("...some connection string");// in the body of a methodusing (SqlConnection connection = new SqlConnection("...same connection..."){     connection.Open();     SqlCommand command = connection.CreateCommand();     command.CommandText = query;     SqlDependency dependency = new SqlDependency(command);     dependency.OnChange += OnDepChange;      SqlDataReader data = command.ExecuteReader();     // code that uses the data goes here}public void OnDepChange(object sender, SqlNotificationEventArgs e){     DoSomething();}I
did a little searching on the web and noticed that some examples showed
that you could adjust the pollTime in the web.config file:<sqlCacheDependency enabled="true" pollTime="30000">  <databases>    <add name="Pubs" connectionStringName="PubsConn" />  </databases></sqlCacheDependency>However,
as I stated earlier, the code that uses the SqlCacheDependency is
located in a Class Library, so this configuration doesn't help me. Is
there a way to adjust the pollTime without getting my connection string
from the web.config?

View 1 Replies View Related

How Can I / Should I Reuse SqlCacheDependency? Thanks

May 1, 2006

I can create a SqlCacheDependency, and link it to a cached item in httpcontext cache. When something change, it will remove the cached item from the cache. I think I have to redo the process when that happens - prepare sql command, create SqlCacheDependency and insert the item into cache. Now I only need a notification from my SQL when something changes in one of my table, I don;t need read anything from db, and I think I should find a way to not recreate the SqlCacheDependency object everytime?
any suggestion?

View 2 Replies View Related

Really Need Some Suggestion On SQLCacheDependency.

May 3, 2006

I have some problem with ASP.NET cache, I found other people has similar problem, but I didn't find real solution.
The one bother me most is the SQLCacheDependency doesn't work stable. I insert object in cache and has SQLCacheDependency linked. After a period of time, it stopped working. That means the the object is still in cache, but change on db side doesn't remove the cache entry. I am not sure if it is ASP side or SQL side, I feel it is ASP side.
I am using 2.0 + SQL 2005.
Once the db command notification stop working, you have to restart IIS or clear all items in cache since you don't kno which one is changed.
Any suggestion?

View 12 Replies View Related

Problem With SqlCacheDependency

Sep 12, 2007

I've set up a SqlCacheDependency in my Asp.Net application, but the dependency invalidates the cache immediately every time the page is hit.

I think the problem may be with my SQL Query, but it seems to me to meet the requirements on the Special Considerations When Using Query Notifications page on MSDN. Could someone take a look at this query and tell me if I've done something wrong? Thank you.

Here's my query:



Code Snippet

USE [chameleon]

GO


SET ANSI_NULLS ON

GO

SET ANSI_PADDING ON

GO

SET ANSI_WARNINGS ON

GO

SET CONCAT_NULL_YIELDS_NULL ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET NUMERIC_ROUNDABORT OFF

GO

SET ARITHABORT ON

GO


ALTER PROCEDURE [dbo].[usp_customers_by_site_id]

@site_id INT

AS


SELECT

customers.customer_id,

customers.name,

customers.po_prefix,

customers.dt_created,

customers.created_by AS auid,

customers.po_required

FROM dbo.customers

WHERE customers.site_id = @site_id

AND customers.is_active = 1




and here's the code in my Asp.Net site where I try to use the SqlCacheDependency:




Code Snippet
public List<Customer> GetCustomersBySite(int siteID)
{
List<Customer> customers = new List<Customer>();

if (HttpRuntime.Cache["CustomersBySite" + siteID] != null){
customers = (List<Customer>)HttpRuntime.Cache["CustomersBySite" + siteID];
}
else
{
using (SqlCommand command = new SqlCommand("usp_customers_by_site_id", Connection)){
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@site_id", SqlDbType.Int).Value = GetDBValue(siteID, false);
SqlCacheDependency dependency = new SqlCacheDependency(command);

try{
Connection.Open();
SqlDataReader reader;
reader = command.ExecuteReader(CommandBehavior.SingleResult);

while (reader.Read()){
customers.Add(PopulateCustomer(reader));
}

HttpRuntime.Cache.Insert("CustomersBySite" + siteID, customers, dependency, DateTime.Now.AddMinutes(Configuration.CacheDuration), System.Web.Caching.Cache.NoSlidingExpiration);
}
finally{
Connection.Close();
}
}
}
return customers;
}


View 1 Replies View Related

Problem With SQLCacheDependency

Jun 15, 2006

I am using a grid to display the data on my webpage, and I have 3 projects in my solution (UI, BLL, DAL). When I load the webpage I am creating the instance of a class written n my BLL project to populate the data, and in BLL I'm creating the object for the class in DAL and returning the datatable.

Now when I refresh the page using F5, the SQLDependency is working fine. But, when I use the Paging or Sorting option's on my grid I see in SQL Profiler that the query is posted back to the SQLServer to get the data.

If this is the senario then how can I make use of the cache object in .NET 2.0.

Please advice,
Ravi

View 1 Replies View Related

SqlCacheDependency + Asp.Net Membership == Not Working

Jul 24, 2006

Decided to try testing SqlCacheDependency and got the following.... thoughts???After using the following code...void Application_Start(object sender, EventArgs e)     {        System.Data.SqlClient.SqlDependency.Start (ConfigurationManager.ConnectionStrings["testConnectionString"].ConnectionString);     }//-------------------------------- ... then more code                    SqlCommand sqlCommand = new SqlCommand("select ApplicationName from dbo.aspnet_Applications", sqlConnection);                    SqlCacheDependency sqlCacheDependency = new SqlCacheDependency(sqlCommand);//-------------------------------- ... then more codeMembershipUser membershipUser =  Membership.CreateUser(defaultUserNameTextbox.Text, defaultUserPasswordTextbox.Text);        if (membershipUser != null)        {//----------------------------- ... more codethe above code no longer works giving the below exception : using a plain database generated using the following statement

"aspnet_regsql -E -S . -d testDatabase -A all"
[SqlException (0x80131904): UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +177
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +68
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2300
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +147
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1021
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +314
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +413
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +115
System.Web.Security.SqlMembershipProvider.CreateUser(String username, String password, String email, String passwordQuestion, String passwordAnswer, Boolean isApproved, Object providerUserKey, MembershipCreateStatus& status) +3325
System.Web.Security.Membership.CreateUser(String username, String password, String email, String passwordQuestion, String passwordAnswer, Boolean isApproved, Object providerUserKey, MembershipCreateStatus& status) +214
System.Web.Security.Membership.CreateUser(String username, String password, String email) +31
System.Web.Security.Membership.CreateUser(String username, String password) +7
testCustomMembership.defaultCreateUserButton_Click(Object sender, EventArgs e) in c:projects
esearchsamplehandler estCustomMembership.aspx.cs:33
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +75
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +97
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4861

View 1 Replies View Related

SqlCacheDependency Without Output Caching

Jul 25, 2006

I have SQL Server 2005 and I am using .Net 2.0.
Is there a way to cache a query, basically I am going to do a SELECT * on a table that is called frequently. 
I then want to query this DataTable that is in cache.  I want to invalidate the cache when the table changes.
Everything I have read tells me to use the output caching directive.  But, much of what I am doing doesn't end up in the output.   Is there a way to invalidate the cache the same way without output caching and using the Service Broker?

View 1 Replies View Related

Fatal Error 682 And SqlCacheDependency

Oct 16, 2007

Hello,

I have tried two ways for executing a query and creating a dependency
on it, one using plain Sql commands, the other using Enterprise
Library wrapped commands.

I keep getting:

Warning: Fatal error 682 occurred at Oct 12 2007 11:01AM. Note the
error and time, and contact your system administrator.

string xml = cmd.ExecuteScalar() as string;

When I execute it. Does anyone know what could cause this.

I have read both of these posts and have not yet been able to
investigate the SQL machine's event viewer though:

http://forums.asp.net/p/959871/1188606.aspx#1188606

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1203630&SiteID=1

The "value" column is an XML datatype field.

Thanks,
Josh



        public static ContentXmlUtilResult GetContentXml(
            string key,
            bool createDependency)
        {
            ContentXmlUtilResult result = new ContentXmlUtilResult();

            string connString = Global.Current.ConnectionString;

            using (SqlConnection conn =
                new SqlConnection(connString))
            {
                SqlCommand cmd =
                    new SqlCommand(
                        "SELECT Value FROM dbo.ContentXml WHERE [Key]
= '" + key + "'",
                        conn);

                if (createDependency)
                {
                    System.Web.Caching.SqlCacheDependency dependency =
                        new SqlCacheDependency(cmd);

                    result.Dependency = dependency;
                }

                conn.Open();

                string xml = cmd.ExecuteScalar() as string;

                conn.Close();

                result.Content = xml;
            }

            return result;

            //SqlCommand cmd = (SqlCommand)
            //    Global.Current.Database.GetSqlStringCommand(
            //    //@"SELECT Value FROM dbo.ContentXml WHERE [Key] =
@Key");
            //    @"SELECT Value FROM dbo.ContentXml WHERE [Key] = '"
+ key + "'");

            ////cmd.Parameters.Add("@Key", SqlDbType.VarChar).Value =
key;

            //if (createDependency)
            //{
            //    System.Web.Caching.SqlCacheDependency dependency =
            //        new System.Web.Caching.SqlCacheDependency(cmd);

            //    result.Dependency = dependency;
            //}

            //string xml = Global.Current.Database.ExecuteScalar(cmd)
as string;

            //result.Content = xml;

            //return result;
        }

View 1 Replies View Related

SqlCacheDependency Works Just For 2-3 Minutes

Jan 31, 2006

I've the following simple code bound to a button on a Web page

string time = (string)Cache["KEY"];
if (time == null)
{

SqlConnection sqlConnection = new SqlConnection(@"Server=BIZYUSUFSQL2005;Database=Deneme;User Id=sa;Password=;");
SqlCommand command = new SqlCommand(@"select KOLON1 from dbo.CACHE", sqlConnection);
sqlConnection.Open();

SqlCacheDependency dependency = new SqlCacheDependency(command);
time = System.DateTime.Now.ToString();
Cache.Insert("KEY", time, dependency);
command.ExecuteNonQuery();
sqlConnection.Close();
}
return time;
This code has to return the time value from cache. And when a record is inserted into the CACHE table, the cache item has to be invalidated and the new time value has to be returned.
The code works properly for 2-3 minutes. But when there is no activity for 5 minutes, the cache invalidation does not work anymore.
 

View 1 Replies View Related

Do We Have To Create Connection Before Creating SqlCacheDependency?

May 23, 2008

do we have to create connection and assign it to command object before creating SqlCacheDepency? chek the code below. SqlServer is not invalidating cache when there is update. When i create Dependency using command object, at that time command object does not have connection. It will assign connection to command object in Excute method
SqlCommand cmd = new SqlCommand(commandText);cmd.CommandType = CommandType.StoredProcedure;SqlCacheDependency cacheItem = new SqlCacheDependency(cmd);DataSet result;result = DBHelper.Execute(cmd);HttpRuntime.Cache.Insert(cacheItemName, result, cacheItem); 

View 4 Replies View Related

Enable Production SQL Server Express For SQLCacheDependency?

Aug 1, 2007

Q1: Can I just open these protocols I need (see web links below) and then close them down again after SQLCacheDependency has been enabled? Note: The website (asp.net 2.0/SQL server Express) operates OK right now with asp.net - but it does not have SQLCacheDependency enabled.
Problem: When I try to do this I keep on getting this error message:
"An error has occurred when establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections."
None of these 3 commands have worked (all give the same message above):
 aspnet_regsql.exe -U user1 -P pwd1 -S server1/SQLEXPRESS -d myDBName -ed aspnet_regsql.exe -E -S server1/SQLEXPRESS -d myDBName -ed aspnet_regsql.exe -E -S .SqlExpress -d myDBName  -ed
I am trying to run these commands via a MSTSC connection. Q2: Is that (terminal server) the problem (the "remote connection") complained of above ?
This is the command that worked on my local dev machine:
 aspnet_regsql.exe -E -S .SqlExpress -d myDBName -ed
Q3: Must I really go through the procedure "How to: Configure Express to accept remote connections" at:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx
I don't really want to have SQL server accepting any connections as the last time it was listening on these ports we had so many attacks being made that it was filling up the logs!
What I can't understand is that every night the database is backed up with the following command line code (operating as a Scheduled Task):
  sqlcmd -s server1/SQLEXPRESS -i MW4BAK.SQL -E
Q4: So if the server is happy receiving commands from 'sqlcmd.exe' why does it have such problems with 'aspnet_regsql.exe'
Q5: If I grouped those statements I need to setup SQLCacheDependency under a Scheduled Task would they work (as there would presumably be no remote connection then) or is it something in the nature of the connection established which allows sqlcmd.exe to work but stops aspnet_regsql.exe?

View 2 Replies View Related

ServiceBroker Error 8490 - SqlCacheDependency Not Invalidated

Sep 22, 2006

SQL Server 2005 / .NET 2.0 Web App - SqlCacheDependency is not invalidated and I see an error sql log that occurs after the specified table changes:



2006-09-22 12:18:51.84 spid21s The query notification dialog on
conversation handle '{8D7EF5BC-544A-DB11-B6E0-000F1FD735BF}.' closed
due to the following error: '<?xml version="1.0"?><Error
xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot
find the remote service
&apos;SqlQueryNotificationService-d61fa581-0567-43ba-991f-37499e87cbf6&apos;
because it does not exist.</Description></Error>'.

I've read all applicable articles that I can find and applied hotfix kb916002. The sqldependency has never worked, it is not a stability issue as described in kb916002. In a trace I can see the sqldependency starts correctly. I have a console app that works fine with SqlDependancy calling same database and login creds. I don't know what to think. Calling sys.dm_qn_subscriptions show no subscriptions when this app starts as does the console app. Please advise as it looks like a broker issue.

Thanks

View 3 Replies View Related

SqlCacheDependency Query Notifications -- Correct Permisisons -non Dbo Owner Account

Jul 5, 2007

 
Hi,
I am using SqlCacheDependency with the query notifications of SQL server 2005.
It is working nicely with the dbo.owner account but not with my web user account,
so I am thinking something is wrong with the permissions. 
After searching around, it seems that the main permissions to enable are these:
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO theAccount
GRANT RECEIVE ON QueryNotificationErrorsQueue TO theAccount
GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to theAccount
GRANT SELECT TO theAccount.
 
I enabled these and it still doesn't work.  When I change my table, my cache is not getting invalidations.
Does anyone know what could be going on?  Or perhaps how can I trace the problem and get some clues?
thanks so much,
-tajmahal
 
 

 
 
 
 
 
 
 
 
 
 
 
 
 

View 3 Replies View Related

SQL License Questions And Other Questions &&>&&>&&>&&>

Mar 3, 2006

1.    Is it legal  and OK to use a MSDN SQL copy on a production environment or is it strickly for test environments ??

2.   If I own a legal copy of SQL 7 with 5 cals, can I legally use SQL MSDE and have more than 5 people access my SQL server or am I also limited to 5 users as my original ??

 Sorry I am a newbie at this SQL thing.

View 1 Replies View Related

T-SQL Questions

Aug 27, 1999

I have some tasks that I need to accomplish within T-SQL but cannot find
a means to accomplish them.

They are..

1. Check for the existance of an external text file.

2. Count the number of rows in an external text file.

3. Be able to run the BCP command from within T-SQL. I am currently using
the BULK INSERT command which works fine but it does not allow the following..

a. Error log for failed inserts.

b. A command output file.

Any information would be greatly appreciated.

Jim

View 1 Replies View Related

BCP Questions ???

Sep 2, 1999

I am tying to call BCP to output the contents of a table to a text file from with in a stored procedure.
The procedure will be called from an ASP page ...
My question is were does the file get created ??? I want to create the text file on server ONE and SQL server is running on server TWO and IIS is running on server THREE... do i have to have a drive letter mapped to server ONE and if so is it mapped on the SQl server or the IIS server ....

ie:
exec master..xp_cmdshell bcp db..table out h:est.out -Uxx -Pxx -Sx

thanks for any help on this subject ...

Rob

View 1 Replies View Related

T-SQL Questions

Aug 27, 1999

I have some tasks that I need to accomplish within T-SQL but cannot find
a means to accomplish them.

They are..

1. Check for the existance of an external text file.

2. Count the number of rows in an external text file.

3. Be able to run the BCP command from within T-SQL. I am currently using
the BULK INSERT command which works fine but it does not allow the following..

a. Error log for failed inserts.

b. A command output file.

Any information would be greatly appreciated.

Jim

View 1 Replies View Related

Two Questions!

Aug 4, 1999

I have two questions,
1) Could anyone please point me in the right direction concerning information pertaining to NT Server Enterprise Edition verses NT Server Workstations. We are having problems running SQL Server 7.0 and the Enterprise Edition together on the same machine and was wanting to find information about compatability issues, if there are any,
2) I, on a SQL Server 6.5 database shrunk it by 2 Gb. When I looked to see if SQL Server released those 2 Gb back to the hard drive, I was amazed that it didn't! Did I miss something or will SQL Server 6.5 not release the space because of the initial set-up. And why did SQL Server 6.5 automatically take the space from the Transaction logs when neither of the devices were specified? Is the Transaction Log the default area for shrinkage?

Thanks in Advance!
Daimon Russell
daimon_r@hotmail.com

View 1 Replies View Related

SQL- TDS - TCP/IP Questions.

Nov 5, 2007

Hi, does anyone know how to explain how SQL transactions get mapped into TDS and TDS gets mapped into TCP/IP packets?


Or can you please point me in the right direction? I need to figure out how TDS and TCP/IP relate?

We're using replication and are having some latency issues and I'm trying to find out how SQL handles TDS and TCP/IP etc...

Thanks for your time.

View 6 Replies View Related

DTS Questions

Feb 7, 2002

1. When we create DTS in SQL Server through DTS designer, where are they stored physically?
2. What would be the best way to modify a DTS without using DTS designer?
3. Is there any other way to create DTS apart from DTS designer and Visual Basic?
4. Is there any website which has detailed information for DTS? (which has more FAQs like above?)

In our production environment, we keep changing the servers frequently, and everytime that happens, I have to change the connection properties in all the DTS going to them one by one.

thanks,
sanjay.

View 2 Replies View Related

SQL Questions

May 4, 2001

I am not too familiar with SQL Server, but my supervisor gave me the task of finding out the difference between SQL Enterprise and SQL Standard. He also asked me to research the difference between processor licences and client access licences. I will use the Internet as a resource, but I would also like to hear the opinions of someone who uses these programs or is knowledgable about them. So please any suggestions or any useful links would be very helpful.

View 3 Replies View Related

Two Questions

Jun 17, 2004

I have two questions.
1) If a database is suspect we can have that trace from sysdatabases.There is a column named status.My question is in case of suspect datatbase what will be value in the field status of sysdatabases?
2) The password of an user login(created by using sp_addlogin stored procedure or any other way) is stored in the table sysxlogins of master database.The password is stored in a varbinary format.How can I get the actual password(means in a char format)?I mean how can I convert the varbinary value to a readable format?

View 1 Replies View Related

Few Questions

Jun 21, 2004

Hi All,
I am new in SQL SERVER 2000.I have few questions -
1) WHAT WILL I DO TO TRUNCATE THE SIZE OF A TRANSACTION LOG?
2) WHAT WILL BE THE STEPS OF BUILDING THE MASTER DATABASE?
3) WHAT WOULD BE THE PLAN OF ACTION WHEN SQL DOES NOT STARTS UP?
4) WHAT WOULD BE MY PLAN OF ACTION WHEN SQL DB GETS CORUPTED OR STARTS IN A SUSPECT MODE?

View 1 Replies View Related

VDI Questions!

Aug 31, 2004

Could I do periodicity backups to another computer(mediaserver) using VDI??

I mean , Could I config a Virtual Device so that I can do backups like disk or tape, I can use
'backup database ...to virtual_device='...' ' to do backup to another computer(mediaserver)?
suppose that I have finished the interface of mediaserver.

If this is impossible,how can I do periodicity backups to another computer??

I have finished a program using VDI that can do backup to another computer,and I know how to do periodicity backups to disk or tape. but I am puzzled about the periodicity backup using VDI.

How should I do?

Thanks,All.

View 4 Replies View Related

I Have A Few Questions Regarding SQL

Sep 9, 2004

I am putting together a proposal for my church, the current DB software (Access) has been outgrown. One proponent of a no name brand software insists that to implement SQL could take a year and a team of programmers, is this true??

View 3 Replies View Related

SQL Questions

Mar 15, 2004

I have to judge the aptitude of a few trainees for SQL DBA training ....

Can the gurus suggest some questions for the same ....

View 14 Replies View Related

Int Questions

Apr 9, 2008

Hi Could any one tell me the answers for these questions.

1. how do we troubleshoot a datbase if it is in suspect mode.What is the reason for a database to be in suspect mode.
2. how can we move a file from C drive to D drive, so that the file location in C drive is completely moved .
3.In a particular primary file group there are many objects.
How can we move some of the objects from this primary file group in to another file group.
4. can we install a sql server on a remote server so that it will not ask any inputs like domain name, authentication modes. .
5.If we want to implement clustering , can we use the virtual ip address on clustering as the ip address of our system or we have to use another ip address for clustering
6. In 2000 we have DTs package.can we run the same DTS package in 2005.

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved