Maintaining SQL Data On A Remote Host.
Oct 29, 2007
Hi,
What is the preferred way to maintain SQL tables on a remote host?
I am a newbie to building ASP.NET websites on a remote host.
A stumbling point has been the maintenance of SQL tables on the remote host.
I understand about doing complete backup and restores,
but I am seeking a quicker way to maintain individual files.
I would like to click and edit but instead am going through the following 30+ clicks.
Is there a easier way?
Thanks.
For example, what I do now to build a new data table for a hosted website.
1) Design table
1a) Name
1b) Fields & Types
2) SQL Server Management Studio Express (assuming existing database)
2a) Select Database & Tables
2b) Add new table
2c) Add fields, Key must be INT for ACCESS
2d) Save as (Name_Table)
3) MS Access (requires ODBC to be setup first through the Windows control panel)
3a) Tables / New / Link / ODBC /Machine_Data_Source
3b) Pick table
3c) Edit data, as needed
4) To transfer data, first:
Select the database in the VWD solution explorer,
then right-click and select the new "Publish to Provider"
4a) Database Publishing Wizard
4b) Choose table to script a backup from
4c) Build script & Copy
5) Start Ipswitch FTP ( this step can be rplaced by 6e below)
5a) locate folder & sql script file and choose destination directory
5b) Transfer file
6) Login to remote host host (1and1)
6a) MS SQL Administration
6b) Admin (MyLittleTools Admin)
6c) Tools
6d) Quey Analyser
6e) Paste script (from step 4)
6f) Submit (Run)
6g) Verify table built
FYI: Script to build and populate the new table "Name_Table"
Built by step 4c above, pasted into remote Hosts Query Analyzer by step 6e above.
/****** Object: Table [dbo].[Name_Table] Script Date: 10/28/2007 18:03:58 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Name_Table]') AND type in (N'U'))
DROP TABLE [dbo].[Name_Table]
GO
/****** Object: Table [dbo].[Name_Table] Script Date: 10/28/2007 18:03:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Name_Table]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Name_Table](
[ID] [int] NOT NULL,
[Name] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Zip] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Acsz] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Phone] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Fax] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Name_Table_1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS =
ON)
)
END
GO
INSERT [dbo].[Name_Table] ([ID], [Name], [Address], [City], [State], [Zip], [Acsz], [Phone], [Fax]) VALUES (1, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[Name_Table] ([ID], [Name], [Address], [City], [State], [Zip], [Acsz], [Phone], [Fax]) VALUES (2, NULL, NULL,
NULL, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[Name_Table] ([ID], [Name], [Address], [City], [State], [Zip], [Acsz], [Phone], [Fax]) VALUES (3, N'Third
name', NULL, NULL, NULL, NULL, NULL, NULL, NULL)
View 1 Replies
ADVERTISEMENT
Jan 3, 2006
What does this error mean? I get this in the transmission_status column of sys.transmission_queue
An error occurred while receiving data: '10053(An established connection was aborted by the software in your host machine.)'.
Also, does the number in this message correspond to error 10053 in sys.messages?
Thanks
View 3 Replies
View Related
Apr 4, 2007
Hey I've been trying to upload a sql express database to my webserver with out much success :(
When I upload it I get permission problems so I think anyway
I've been reading around and it looks like i'm stuck with having to let the server create the database file again
Is there anyway i can upload this file to the webserver and get it working 100%?
View 5 Replies
View Related
Jul 20, 2005
Hi,Somtimes the agent will just die on some computers...I'd like to have a job monitoring the agent every 20 minutes or so..Do you know of a way to tell if the agent is up or no using plain Tsql?ThanksGaspar
View 1 Replies
View Related
Jun 26, 2007
Hi all
I have run a stranger issue:
I use ADO.NET 2.0 to access sql2000 database. during the whole process, I need stop sql server service which the program is connecting. my setup is like following:
1.Open a conection to access table and then close the connection;
2.restart sql server service ;
3.Re-open a connection to access table and then an exception was thrown out:
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed bythe remote host.)
4. try re-open a connection to access table and then no exception was thrown out.
I think this maybe related with connection pool. any guy know the detail, please give me a response. Thank you very much!
View 2 Replies
View Related
Feb 13, 2006
Hi,
I'm running a website using MSSQL 2000. Sometimes (not always) I get this error message on the website:
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
I have also websites running with MySQL and those don't have this problem.I searched google but I couldn't find usefull information on this problem.
Someone who knows what's going on here ?
View 1 Replies
View Related
Jul 22, 2005
I am getting this error in Enterprise Manager:
TCP Provider: An existing connection was forcibly closed by the remote host.
But, my web app can still connect just fine to the db, so it's still in
the same place with the same login/password. And my registration
in EM was working just fine (for years) until yesterday I got this
error. I've tried deleting the registration and re-creating it
but the same thing. What could have gone wrong to cause this to
happen?
Thanks for any pointers, I have no idea where to start looking on this one, the host says they haven't changed anything...
View 1 Replies
View Related
Nov 8, 2005
Hi,
I just downloaded Visual Web Developer Beta 2.0 and the club starter kit. I created a new web project using the kit and it worked fine. I can run the site locally without problems. Howevere, when I ftp my files over to my web hosting site and try to run the site there I get the error message shown below. Is there a configuration file that I missed updating or is this an issue on the web hosting site?
Thanks for your help. Server Error in '/' Application. --------------------------------------------------------------------------------
An error has occurred while 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. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) 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.Data.SqlClient.SqlException: An error has occurred while 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. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): An error has occurred while 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. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734979 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup) +820 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +130 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770 System.Web.UI.WebControls.Repeater.GetData() +50 System.Web.UI.WebControls.Repeater.CreateControlHierarchy(Boolean useDataSource) +232 System.Web.UI.WebControls.Repeater.OnDataBinding(EventArgs e) +53 System.Web.UI.WebControls.Repeater.DataBind() +72 System.Web.UI.WebControls.Repeater.EnsureDataBound() +55 System.Web.UI.WebControls.Repeater.OnPreRender(EventArgs e) +12 System.Web.UI.Control.PreRenderRecursiveInternal() +77 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360
View 1 Replies
View Related
Aug 19, 2015
I have seen several problems posted where an SSIS package writes a file successfully when executed manually, but fails when executed via SQL Agent job..I have the opposite problem. I'll try to lay it out succinctly: SSIS writes to a file on a shared folder, specified as HostAShare, for example. I created the share and gave full control to Everyone (out of frustration).I'm working from HostA via RDP, connected to the DB on DBHost via SSMS.If I kick off a SQL Agent job that executes the package, it works fine. (SQL job is running under SQL Server Agent Service Account).If I execute the job interactively (logged in to SSMS with Windows Auth), it fails with "Error: Cannot open the datafile "HostASharefilename.ext""We did find that if I RDP directly to the DBHost, I am able to execute manually.Also, if I try executing an xp_command shell command to write a file to the share, it works. (When RDP's into HostA with an SSMS connection to DBHost under my windows domain auth, as above.)The problem is the same when I RDP to any remote host.
View 2 Replies
View Related
Apr 29, 2006
We are getting a problem when the below error Log
Server: SQL Server 2005 and SP1.
Then, All the Web Servers will drop all connections and stop the network service. Later, the network service will start up automatically but all the before service is dropped.
We would like to know the error coming from?
Error Log
================
TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host
Event Type: Warning
Event Source: ASP.NET 2.0.50727.0
Event Category: Web Event
Event ID: 1309
Date: 4/29/2006
Time: 10:48:47 AM
User: N/A
Computer: 88DBWEB0
Description:
Event code: 3005
Event message: An unhandled exception has occurred.
Event time: 4/29/2006 10:48:47 AM
Event time (UTC): 4/29/2006 2:48:47 AM
Event ID: 77910e6688484582bb1c29808feef883
Event sequence: 128
Event occurrence: 13
Event detail code: 0
Application information:
Application domain: /LM/W3SVC/24978983/Root/ph-3-127907437121075328
Trust level: Full
Application Virtual Path: /ph
Application Path: D:DB88ph
Machine name: 88DBWEB0
Process information:
Process ID: 1568
Process name: w3wp.exe
Account name: NT AUTHORITYNETWORK SERVICE
Exception information:
Exception type: SqlException
Exception message: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
Request information:
Request URL: http://88db.jobsdb.com/ph/Views/MediaUpload.aspx?PostID=50202&Type=I&panelid=postimagediv
Request path: /ph/Views/MediaUpload.aspx
User host address: 192.168.10.10
User: 50077
Is authenticated: True
Authentication Type: Forms
Thread account name: NT AUTHORITYNETWORK SERVICE
Thread information:
Thread ID: 15
Thread account name: NT AUTHORITYNETWORK SERVICE
Is impersonating: False
Stack trace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.WriteSni()
at System.Data.SqlClient.TdsParserStateObject.ExecuteFlush()
at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at System.Web.SessionState.SqlSessionStateStore.DoGet(HttpContext context, String id, Boolean getExclusive, Boolean& locked, TimeSpan& lockAge, Object& lockId, SessionStateActions& actionFlags)
Custom event details:
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
View 10 Replies
View Related
Nov 14, 2005
If you connect to a server and open a query in SQL Managment studio (2005) and looses connection and then regain the connection, when you try to run the query it gives me a "TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host." This happens against both 2005 and 2000 servers. If I re-run the query it works. However, this happens in our VB.NET app as well for clients with Wireless connections. So, something has changed in Framework 2.0 regarding this, does anyone know how to tell the client to not throw this error and just try connecting the server instead which will work?
View 105 Replies
View Related
Nov 19, 2007
In my scenario I have about a dozen of flat files (Text files),that I have to import in SQL Server 2005.
I am using Flat File connection manager to carry out tha task.Flat files contains data generated from oracle.
When I import data from these text files into SQL ,the main problem lies in converting number(p,s) data type column of Oracle(In text file) to numeric(p,s) data type of sql server 2005.
Number(p,s) data type looses all it's digits after decimal to zero during import process.
For example
1.2434234390 (from text file,number(p,s) type of oracle) converts to 12.0000000000 (numeric(p,s)) of sql server 2005.
Is this this any workaround to this problem.I urgently need help.
View 7 Replies
View Related
Feb 29, 2012
I deleted some records out of an entity, I'd like to keep the Codes as contiguous and incremental, meaning no breaks between the code numbers.I created a business rule and applied it but codes remain the same.
I used the "Default to a generated Value" action, then selected the Code attrib. --Saved.
Then back to the Entity, I applied business rules. But nothing seemed to have happened. As there was no change in codes.
There are no validation errors either.
View 2 Replies
View Related
Feb 2, 2008
A seemlingly common scenario, but one that has proven surprisingly frustrating to implement:
I have a number of clients running an Access application, and I provide a web catalog for them. I'd like to fully automate the process of 1) exporting their updated data, 2) uploading to web server, and 3) import into SQL db (delete existing data; insert new data). 1 and 2 are no problem, of course. Import into SQL is most obviously done manually using web interface to SQL Server (my host does not allow remote SQL connections, or I would do so directly from client's Access apps).
I don't want to have to manually import the uploaded file. I'd love some suggestions on how to automate this from an ASP script (client app would open a URL to initiate the script, which would run the import process itself).
Everything I read about using DTS, SSIS, etc. for this kind of job assumes I'm using SQL Server Management Studio. Any pointers on information on scripting this in ASP instead?
Thanks in advance,
Kirk
View 4 Replies
View Related
Sep 27, 2000
Hello,
I need help in generating a stored proc script (SQL Server 7.0)that will:
document the changes of each field in a dbase (SQL Server 7.0)
appending the deltas (changes) to the dbase on a different, say production server for each affected field
prompt the user (dba, developer, super user) for the fields to involve in the comparing and documenting of changes (deltas).
thanks in advance for your help!
ALSO: Does SQL Server 2000 handle this situation better than SQL Server 7.0
Gunnar
View 1 Replies
View Related
Feb 13, 2007
I am having trouble with BCP. I get the same error with xp_cmdshell as I do with entering bcp as a DOS command. I have checked and rechecked the file names and permissions and even restart the PC.
Does BCP even work when SQL Server 2005 Express on a PC?
declare @sql varchar(8000)
select @sql = 'bcp SYMITAR..ACCOUNT in C: estEXTRACT.ACCOUNT -T -f C: estACCOUNT.FMT -S'+@@servername
exec SYMITAR..xp_cmdshell @sql
GO
Volume in drive C has no label.
Volume Serial Number is 08E5-2414
Directory of C: est
02/13/2007 08:44 AM <DIR> .
02/13/2007 08:44 AM <DIR> ..
08/31/2006 09:11 AM 27,503,161 EXTRACT.ACCOUNT
08/31/2006 09:12 AM 6,879 FMT.ACCOUNT
02/07/2007 08:46 AM 220 ACCTTYPE.FMT
02/13/2007 08:44 AM 0 filelisting.txt
02/07/2007 08:33 AM 220 ACCTTYPE.xml
5 File(s) 27,510,480 bytes
2 Dir(s) 54,344,847,360 bytes free
Could the fact that it's actually pulling BCP from c:Program FilesMicrosoft SQL Server80Toolsinn be a problem? I know this because it doesn't recognize the -x extensionof the bcp command.
PATH:
%SystemRoot%system32;%SystemRoot%;%SystemRoot%System32Wbem;C:Program FilesCommon FilesGTK2.0in;C:Program FilesMicrosoft SQL Server80ToolsBINN;c:Program FilesMicrosoft SQL Server90Toolsinn;c:Program FilesMicrosoft Visual Studio 8Common7IDEPrivateAssemblies;c:Program FilesMicrosoft SQL Server90DTSBinn;c:Program FilesMicrosoft SQL Server90ToolsBinnVSShellCommon7IDE;C:Program FilesQuickTimeQTSystem
...
I added the c: est to the path and removed c:Program FilesMicrosoft
SQL Server80Toolsinn from the path. While it recognizes the -x
extension now it still gets the same error message:
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
NULL
View 11 Replies
View Related
May 10, 2007
Hello,
I have checked file names, and I have checked permissions on the files. I do not see any reason why the BCP should fail. Any ideas how I can debug this further?
Thanks
Tom
View 1 Replies
View Related
Apr 26, 2008
Hi,I'm using a MYSQL database for this project. I installed hte .Net Framework Data Provider on my computer for development, but how can I get it on the host(LunarPages.com) Can I manually call on files in the connectionString or do I need to ask the tech support to install it for me, which will most likely not happen. Gosh, their windows servers lack sooo many things. Edit: I just tried uploading the source files of the data provider into my app_data directory. No change. Help? ThanksNick
View 11 Replies
View Related
Jun 11, 2008
hi ,
i am working on an application using c#, visual studio 2005, sql server 2005.
i have a few tables in sql server 2005.
i need to save the history. (i.e) all the inserts, updates, and deleats performed on the tables.
can any one suggest me how can i achieve that.
should i use triggers and save the changes in another table ???
waiting for your suggestion??
thank you
View 13 Replies
View Related
Oct 22, 2000
Please what is the best way to perform index maintenance. I use 7.0
We have been having slow server performance, and one of the options is to do index maintenance. I have researched but could not get a clear picture of what I should do. Has anybody performed the same task before? Thanks for your help!!!
View 1 Replies
View Related
Apr 5, 2004
Hello Friends,
Iam new to this sql server arena. I have implemented a procedure which does a series of insert and update statements and all of this statements must be implemented all at once or none. But if I got error in some statements , the rest of the statements are been executed. Please suggest me a way or code snippet to achieve atomicity in a sqlserver procedure.
regards,
Ch.Praveen Kumar.
View 4 Replies
View Related
Jan 29, 2008
Scenario:
For the most part we let SQL Server (2005) maintain our statistics for us. However we do have several large processes written in stored procedures. There is one main controller procedure that can call any number of other procedures. This process can take anywhere from 5 minutes to an hour+ to run (based on the size of the client). Back in the day of SQL Server 2000 we found that the performance of this procedure would diminish over time (while it was running). We implemented a queued concept of issuing UPDATE STATISTICS commands. This was done by adding a SQL Server job that ran every 10 minutes looking for new records in a table. Records where inserted at key points in these stored procedures (after large deletes, updates, inserts).
Goal:
Now, with all that background and with 2005, I'd like to review this concept and remove this implementation if possible, or at least remove the close association of maintaining the statistics from the business jobs. In 2005, are there better ways to monitor and maintain statistics at more of an administrative (but automated) way?
View 15 Replies
View Related
Feb 27, 2007
Our database(s) are all over the place - no documentation - lot's ofrubbish and unused stuff.I'm managing a project focusing on data quality that covers codechanges, alterations to DTS packages, schema changes etc etc.What I'd like to do is see where the bit I want to change is beingused.that might mean what stored procs use a field and what sprocs use thatsproc.maybe it's which dts packages use a sproc (and again up thehieararchy)The list is a long one but basically I need to know what the effectsare of changes.Is there a tool out there that lets me navigate a database to thatlevel of detail - I understand something along the same lines isavailable for MS Access but I can't find it for SQL Server.Thanks
View 1 Replies
View Related
May 24, 2006
I am a beginer in SQL Server. I have developed a simple accounting application in VB and SQL. Now I have successfully completed my application. Now I want to deploy it to my client. So I installed SQl Server and required VB components in the clients computer. I also created 'sa' login and secret password only know by me. I thought my data in that clients computer was full safe but later on i found that we can also connect to the sql server using the NT administrative account and easily change the data of the database. So now I am worried that if someone enters and access the clients computer with administrator's password then he/she can change my data resulting the corruption of the data. So is there any way that I can prevent the access the database to the client with the NT administrative account or any way 2 track the way the data changed?
View 5 Replies
View Related
Apr 25, 2008
hi all,
i am working on portal site where i have created 18 tables in on database, i dont know weather i am right or wrong . Should i continue with the same or create two tables one will be master and another will contain common fields.
but if i will create one table for all then what will happend.
please tell me what to do and why?
asap please............
Thanks for spending ur valuable time for me.
View 4 Replies
View Related
Jul 20, 2005
Hello,I am fairly new at stored procedures. I have created some that willgo through a table and return a start date and an end date that isdependent upon the fiscal period you want, but I then need to usethose dates in another stored procedure to retrieve the information Ineed. My stored procedure looks like this.================================================== ====================CREATE PROCEDURE dbo.R920ExtTotal@MthsBack Decimal OUTPUTASDECLARE @sSQL AS NVARCHAR(255), @StartDate as SMALLDATETIME, @EndDateas SMALLDATETIMEExec @StartDate = GetMthStart @MthsBackExec @EndDate = GetMthEnd @MthsBackSET @sSQL = 'Select count(extension) as Total From r920f00 Where([date] BETWEEN "' +CONVERT(nvarchar, @StartDate) +'" and "' +CONVERT(nvarchar, @EndDate) +'")'Select @sSQLEXEC (@sSQL)ReturnGO================================================== ===============The problem is my variables @StartDate and @EndDate do not retaintheir values after the EXEC statement and revert to 01/01/1900. Howcan I get around this problem?Thanks!!!!Chip
View 2 Replies
View Related
Jul 20, 2005
Hello,I have a large set of data that I have set up as a partitioned view.The view is partitioned by a datetime column and the individual tableseach represent one month's worth of data. I need to keep at least twoyear's worth of data at all times, but after two years I can archivethe data. A sample of the code used is below. It is simplified forspace reasons.My question is, how do other people maintain the database in this typeof scenario? I could create all of the tables necessary for the nextyear and then go through that at the end of each year (archive tablesover two years, add new tables, and change the view), but I was alsothinking that I might be able to write a stored procedure that runsonce a month and does all three of those tasks automatically. It seemslike a lot of dynamic SQL code though for something like that.Alternatively, I could write VB code to handle it in a DTS package.So, my question again is, how are others doing it? Any suggestions?Thanks!-Tom.CREATE TABLE [dbo].[Station_Events_200401] ([event_time] [datetime] NOT NULL ,[another_column] [char] (8) NOT NULL )GOCREATE TABLE [dbo].[Station_Events_200402] ([event_time] [datetime] NOT NULL ,[another_column] [char] (8) NOT NULL )GOCREATE VIEW Station_EventsASSELECT event_time,another_columnFROM Station_Events_200401UNION ALLSELECT event_time,another_columnFROM Station_Events_200402GO
View 3 Replies
View Related
Apr 18, 2003
I am wondering how people maintain their SQL Servers which run at several customers sites and disk space is getting smaller and smaller? I want to say that we have tables in SQL dbs which hold a lot of date consisting of statistics, errors, logs etc.
They grow and grow and existing data is not needed anymore as soon as the data get older than let's say for one year. How do you overcome the problem reducing the tables but not charging the system too much as the major application also runs on the same server?
Thanks for any input
mipo
View 1 Replies
View Related
Dec 8, 2005
Hi !
I need to maintain a record such as how many time any user (e.g, sa) connects to the sql server. Means whenever any person is connecting to the database through application or directly, then i need to know that through which sql user(e.g sa), any body connected.
Regards,
Shabber Abbas Rizvi.
View 1 Replies
View Related
Apr 14, 2008
Currently all of our tables in several databases have the following columns:
user_added (this is nvarchar)
host_added (this is nvarchar)
date_added (this is datetime)
user_modified (this is nvarchar)
host_modified (this is nvarchar)
date_modified (this is datetime)
Right now our policy is that (a) the _added columns use defaults to populate the data on INSERTS and triggers are generated to update the _modified fields upon an UPDATE of the table.
Our practice has been (a) to manually create these fields in our scripts as we create new tables in our system and (b) create triggers to perform the update anytime we create a new table.
This practice has been fine until recently where we have been outsourcing some of our development and not all of our standards have been adhered to, including this one. I'd like to look at alternatives for somehow maintaining these concepts outside of our development workflows.
The first thing I'd like to inquire about is regarding options to eliminate having developers include these columns in the CREATE TABLE statements. Is it possible in SQL Server 2005 to capture when an CREATE TABLE statement is executed and override/append to the initial CREATE TABLE statement?
The second thing I'd like to inquire about is regarding options to eliminate having developers write the initial trigger that maintains the _modified fields. I guess if there are options to capture when a CREATE TABLE statement is executed, we could possibly generate a CREATE TRIGGER statement against that object as well?
Another idea I would like thoughts on are using some sort of 'table inheritence' to store this information for all objects in our database? This idea come up when I saw this article - http://www.sqlteam.com/article/implementing-table-inheritance-in-sql-server. Do you think the situation I explained here would fall into this concept?
I'm also open to any other thoughts and/suggestions.
View 2 Replies
View Related
Apr 4, 2007
Hello All!
I have a asp.net website with SQL 2005 DB .
DB size of 1.5GB with ~10 tables in it. The largest table has 200k of records in it (website users table), with 500 new records every day.
I've setup this database 4 months ago and didn't touch it since then.
I really have no knowledge what SQL needs in terms of index maintenances / hard drive maintenances.
Lately , the website searches started to be really slow , and I started to get timeout error and deadlock errors.
I have a few indexes for each table based on the recommendation MS-SQL Database tuning advisor gave me.
Some of the index's are :
Page fullness : 99%
Total Fragmentation: 24%
Other are :
Page fullness : 65%
Total Fragmentation: 99%
I guess I need to start maintaining the DB , defragmenting index or hard-drive?
Can anyone help me and provide me with guide/information on what is needed to be done in order to keep SQL running fast and happily?
or a guide on defragmenting index's and how ofen do i need to defrag?
Thanks,
Shar
View 15 Replies
View Related
Aug 7, 2007
If you have a "Orders" table that is being sync'd to subscribers that are ocassionaly offline, and the subscribers add rows to their local Orders table. When they go online to sync with the published "Orders" table, how do you handle keeping the "OrderId" field unique?
Example:
Both salespeople sync the following data down:
OrderId Desc
1 Order 1
2 Test Order
Both salespeople go offline and add orders
Salesperson 1 adds:
OrderId Desc
3 Joes Order
Salesperson 2 adds:
OrderId Desc
3 Kathys Order
Now, when they go back online, they both will sync their orders up to the main database and they both have the OrderId of 3.
View 3 Replies
View Related
Jan 18, 2007
bab writes "My company is going to host our web application in a host server with the sql server DB needed for the application. what is the best way to transfer the data between host server and main server.
Any thought."
View 1 Replies
View Related