I'm running an SSIS package and I want to be able to skip instances that are stopped. Is there anyway that I can do a SELECT on some SERVERPROPERTY that would tell me if the instance is stopped?
Also, I was wondering about a delete from each table. I have each row timestamped in multiple tables. What could I do to set up a job to delete from each table rows after a certain timestamp?
Thanks,
Kyle
Hi
I just start to learn SQL server now. I am trying to migrate data from oracle to SQL server 2005 using SQL server migrant assistant for Oracle. But I don't know how to fiill this connection details
SQL Server host:
<ServerHost>
SQL Server port
(leave empty if default):
<8888>
SQL Server instance name
(leave empty if default):
<your_instance>
Target database:
<your_db_name>
Target schema:
dbo (by default)
User name:
<your_username>
Password:
<your_password>
How to find these properties? Anyone can help?
Thanks
Li
Is there anyway that I can find out the time an instance went offline? Does the last entry in the error logs show this? One on my instances went down during the night and I need to know the time this happened if possible.
View 9 Replies View RelatedHello All, please be gentle im very new to this stuff..
So if i add a record via a sql datasource using the ConnectDB connection string it adds it the the sql 2005 database called intranet.... If i try to add to it using an onbuttonlick(Code Below) event it comes up with the "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) "I have set remote connections on and tryed it with TCP/IP and TCP/IP and Named Pipes. SQL is installed as the default instance.
If both ways are using the same connection string surely it should work? Im not to sure where i am going wrong here...Here is the code for the on button click event.. 'INSERT NEW EMAIL INTO NEWSLETTER TABLE
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
Protected Sub AddEmailButton_Click(ByVal sender As Object, ByVal e As EventArgs) Handles AddEmailButton.Click
Dim ConSTRING As String = ConfigurationManager.ConnectionStrings("ConnectDB").ConnectionString
'INSERT NEW EMAIL INTO NEWSLETTER TABLE
Dim cn As SqlConnection = New SqlConnection(ConSTRING)
Dim InsertSQL As String = "INSERT INTO Newsletter (Name, Email) VALUES (@Name, @Email)"
Dim cmd As SqlCommand = New SqlCommand(InsertSQL, cn)
cmd.Parameters.AddWithValue("@Name", (TBname.Text))
cmd.Parameters.AddWithValue("@Email", (TBemail.Text))
cn.Open()
cmd.ExecuteNonQuery()
LBLstatus.Text = "Success, Email is now on Mailing List"
cn.Close()
Below is my web.config connection string<connectionStrings> <remove name="LocalSqlServer" /> <add name="ConnectDB" connectionString="Data Source=10.130.0.11;Initial Catalog=intranet;User ID=XXXXX;Password=XXXXX;" providerName="System.Data.SqlClient" /> </connectionStrings>
Any help on this would be great as im stuck..
Best Regards
Leigh
I am running Win XP SP2 and MSDE 2000 SP3a. The user is logging on asa POWERUSER. When the user logs on the MSSQLSERVER service starts butthe SQL Server Service Manager, that runs in the system tray, showsthat the instance has not started. The field that displays theinstance name is blank and it is not listed in the drop down list.The MSSQLSERVER service is set to start with a Windows domainadministrator account. The SQL server can be accessed by a remotecomputer through Enterprise Manager. The local programs cannontinteract with the server though.Does anyone know if I could grant some user access rights to allow MSDEto work under a POWERUSER login?
View 2 Replies View RelatedFind unused databases in a instance or when last used or accessed?
I'm on SQL SERVER 2008 R2 64bit -enterprize
I need to find when the databse is last accessed.
I usually go for the largest datafile and then query in-there...But now I need to automate it for several instances... I need to be able with one script quickly retrieve the top 5 largest tables for the entire instance,not by database...
View 3 Replies View RelatedHow to find last login date/time for user DML acitivity on databases on Instance?
Is there any way we can find our the last login date/time for databases?
Note: 1. We can find if the SQL Trace is running and store.This is not good solution
2. Audit logins off/on is also not good solution.
3. Using DMV's also not good option, if reboot sql server instance then historical values can not see.
dear all;
please i need your help on that sudenly me SQLserveragent stopped and when i try to start it again it give me that error:
Event Type:Error
Event Source:SQLSERVERAGENT
Event Category:Service Control
Event ID:103
Date:2/27/2006
Time:11:00:19 AM
User:N/A
Computer:EGDC2
Description:
SQLServerAgent could not be started (reason: Unable to connect to server '(local)'; SQLServerAgent cannot start).
please help
thank you
It appears that posting of messages has stopped for at least an hour. I've never seen that happen in this forum during US daytime hours!
-PatP
Dear Experts,
i've tried to add two new articles to the publisher.i failed there. now i've dropped the articles, but still replication is not working. please let me know what should i do
Arnav
Even you learn 1%, Learn it with 100% confidence.
Hi ,
I have sql server installed on my laptop..It is a student evaluation version. Today morning,the sql server did not start and when attempted to start..I get the following message:
" SQL server started and stopped immediately. Some services stop automatically if they have no work to do , for example the performance logs and alerts."
Friends, can u help out!!
This might be kinda long so please bear with me. I have a SSIS package that creates 2 other packages to pull in data from our as400. To do this I have a SQL 2005 table setup with as400 table names and columns and the matching sql tables names (I reuse the column names). I also use a flag to determine if the table name should be added to the data retreival that way if there is a need I can just run the procedure for a specific as400 table instead of all of them. The main SSIS package loops through the table to create the package to pull in the as400 data and put it into staging database. Then the main package creates another package that pushes the data from the "staging" sql to the "live" tables. This has been working great for about 5 months now. Yesterday I went to create a new job that would allow me to run the same jobs just with making change to the flags so that only certain tables are pulled in at certain times. This works very well in our test environment. For some reason it appears that it has corrupted the production packages. I can not get them to run at all now. The untouched jobs all of a sudden quit. I changed the new job to run as operating system and not SSIS and it says that it can not find it. I can see the file, I am using the SSIS on the file system. I can open the file and see all of the properties, I can copy and paste the file. I just can not run it. I was able to finally get the package to run using the command line but now there is something odd that it is sending to the as400. The kicker to me is that nothing changed with the SSIS package and nothing changed with the existing jobs I just added new ones and now it does not work. Could this be some corruption? Anyone else seen similar behaviors?
Thanks for your time,
Rob
SQL Server 7.0
SQL mail has stopped functioning. I have checked the profile in exchnage. All is ok. I have tested the MAPI profiles in SQLAgent and SQLMail they all give success. When I run xp_sendmail the process just runs and runs without sending a mail. I end up having to kill the prorcess. Any ideas?
Thanks,
Nev.
I tried to stop SQLServer romotely through Enterprice Manager. It used to work. But, this time I got a message "a error 5 - (Access is denied) occurred while perfrming this service oetation on the MSSQLServer service." and it could not be stopped.
Do you know why and how to stop it.
Thank you ahead of time
Now and then gets stopped the Sql Server 2000 that has been installed in our server. In the event viewer I notice the error number "C0000005".
I do not understand the reason. Can any one help me?
Thanks.
will it happen any thing wrong if i suddenly stopped index rebuild script while running?
Arnav
Even you learn 1%, Learn it with 100% confidence.
Hi,
TableDiff Stop Functioning on 2million records with 1.8milion changes on a particular row. Only happen if "-f" is specified. Anyway to work around this?
Regards,
Ben
Hi!
I have an dbcc shrinkfile (emptyfile) running for more than one day now.
But soon I have to restart db service.
Would it give trouble, if I stop service?
I assume, I must run shrinkfile command again?
Thank your for help ;-)
I'm hoping someone can explain to me exactly what the SQLConnection.Open() method does, especially when the database is stopped. I'm trying to include some error processing in my program, specifically to ensure the database is up and running. But even with the database stopped, the Open() statement works fine. Later, when I try to read from the database, I get the error. I'd like to stop it before it gets any further. Why is the Open() statement "working" even with the database stopped?
View 6 Replies View RelatedHi Everybody !!
I have a problem with SQL mail on SQL 6.5 with SP 5a . I have created a job called as mailtest and is scheduled to run every morning .
The task was running successfully till last week when suddenly it stopped responding on some of the SQL 6.5 Servers .
When i stop the SQL Mail and start again through the Enterprise Manager , and then run the task , it shows as successful .
Every morning i have to do it to check whether the mailtest is O.K .
But the automated mailtest task has stopped suddenly . I have checked the MAPI profile and is correct as the same is configured on other servers on which my mailtest runs successful.
Can somebody guess what the problem could be ?
Thanks for your help in advance .
Regards
Ashish.
Our stored procedure error handling is not working now but did before.
When a severity level 16 (Miscellaneous user error) occurs the batch (stored procedure) exits without processing the next statement which is the error check routine. This is a simple error processing routine and used to work. What could have changed?
I have searched books online and have found nothing on abnormal termination of a SQL batch. I would appreciate some help here.
Loren
I am having a real problem opening a database on a SQL server through EM. The DB is on the machine as I can use EM on another machine and login from there. However, when I try to login to the DB via EM on the actual server I cannot and get an error saying 'Connection failed. Check SQL Server registration properties'.
Even if I change the reg properties it still will not let me access the database, it just keeps giving the same error.
Has anyone seen something like this before?
The following query has been working for months and the other day it just stopped. I get no error, it just never finishes. It used to take 20 minutes. Nothing has changed that I know of.
The query is designed to insert the new records from the t_DTM_DATA_STAGING into t_DTM_DATA_STAGING2 using the t_DTM_DATA_1 as the outer join.
Average record count for t_DTM_DATA_STAGING is 2 Million
Current record count in t_DTM_DATA_1 - 267 Million
Both tables have clustered indexes made up of the 10 fields in the join below.
Any Ideas??
SET QUOTED_IDENTIFIER ON
INSERT INTO
[DTM].[dbo].[t_DTM_DATA_STAGING2]
([CP]
,
,[MAJ]
,[MINR]
,[LOCN]
,[DPT]
,[YEAR]
,[PD]
,[WK]
,[TRDT]
,[SYSTEM]
,[AMOUNT]
,[DESCRIPTION]
,[GROUP]
,[VENDOR]
,[INVOICE]
,[IDAT]
,[PO_NUMBER]
,[DDAT]
,[RCV#]
,[RDAT]
,[RSP]
,[EXPLANATION]
,[UPLOAD_DATE]
,[UPLOAD_USER]
,[UPLOAD_NAME]
,[RELEASE_DATE]
,[RELEASE_USER]
,[RELEASE_NAME]
,[TRTM])
SELECT
t_DTM_DATA_STAGING.CP,
t_DTM_DATA_STAGING.CO,
t_DTM_DATA_STAGING.MAJ,
t_DTM_DATA_STAGING.MINR,
t_DTM_DATA_STAGING.LOCN,
t_DTM_DATA_STAGING.DPT,
t_DTM_DATA_STAGING.YEAR,
t_DTM_DATA_STAGING.PD,
t_DTM_DATA_STAGING.WK,
t_DTM_DATA_STAGING.TRDT,
t_DTM_DATA_STAGING.SYSTEM,
t_DTM_DATA_STAGING.AMOUNT,
t_DTM_DATA_STAGING.DESCRIPTION,
t_DTM_DATA_STAGING.[GROUP],
t_DTM_DATA_STAGING.VENDOR,
t_DTM_DATA_STAGING.INVOICE,
t_DTM_DATA_STAGING.IDAT,
t_DTM_DATA_STAGING.PO_NUMBER,
t_DTM_DATA_STAGING.DDAT,
t_DTM_DATA_STAGING.RCV#,
t_DTM_DATA_STAGING.RDAT,
t_DTM_DATA_STAGING.RSP,
t_DTM_DATA_STAGING.EXPLANATION, t_DTM_DATA_STAGING.UPLOAD_DATE, t_DTM_DATA_STAGING.UPLOAD_USER, t_DTM_DATA_STAGING.UPLOAD_NAME,
t_DTM_DATA_STAGING.RELEASE_DATE, t_DTM_DATA_STAGING.RELEASE_USER, t_DTM_DATA_STAGING.RELEASE_NAME,
t_DTM_DATA_STAGING.TRTM
FROM
t_DTM_DATA_STAGING
LEFT OUTER JOIN
t_DTM_DATA AS t_DTM_DATA_1
ON
t_DTM_DATA_STAGING.TRTM = t_DTM_DATA_1.TRTM
AND
t_DTM_DATA_STAGING.TRDT = t_DTM_DATA_1.TRDT
AND
t_DTM_DATA_STAGING.PD = t_DTM_DATA_1.PD
AND
t_DTM_DATA_STAGING.YEAR = t_DTM_DATA_1.YEAR
AND
t_DTM_DATA_STAGING.DPT = t_DTM_DATA_1.DPT
AND
t_DTM_DATA_STAGING.LOCN = t_DTM_DATA_1.LOCN
AND
t_DTM_DATA_STAGING.MINR = t_DTM_DATA_1.MINR
AND
t_DTM_DATA_STAGING.MAJ = t_DTM_DATA_1.MAJ
AND
t_DTM_DATA_STAGING.CO = t_DTM_DATA_1.CO
AND
t_DTM_DATA_STAGING.CP = t_DTM_DATA_1.CP
WHERE
(t_DTM_DATA_1.CP IS NULL)
Hi,
I have inherited a nice problem when starting a new role.
My SQL server has stopped and i get the 17051 error when i try to start the services, It is the evaluation copy. I do have a registered full copy on another box.
I really want to keep some of the databases i have been using in the test environment. They have not been backing up as the msdb DB became corrupt some time ago.
Is there any way i can get them moved to my other box.
CAn i reinstall SQL server (another eval copy) onto the duff box (keeping the db's) back them up and then move them
in a pickle
Cheers
Allan.
I have a merge publication that I always create with a script but has now stopped working with what appears to be a complete server problem.
The actual command i am executing is a simple
sp_addpublication_snapshot
but it fails with
Msg 50000, Level 16, State 1, Procedure sp_add_jobstep_internal, Line 253
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Msg 3931, Level 16, State 1, Procedure sp_MSadd_repl_job_unsafe, Line 376
The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.
I have tried recreating the database from scratch, renaming it, renaming the publication, even disabling the publications altogether but it makes no difference. There is nothing in the sql or event logs and I cant do any work until Ive got past it - help!
I am using SQL 2005 SP2 if that makes any difference.
Thanks
Andy
I have an SQL Job whose sole step is to run an SSIS Package via the File System mode. The .dtsx file itself is located on the server where SQL resides.
The SSIS task looks inside a specific directory and if a qualifying file exists, archives the file, unzips the file, rename the contents and, eventually, processess the files.
Invariably, if i Stop the Job before it has completed successfully, i notice that the underlying file (in this case, a .zip file) remains 'in use'. If i try and move the file or rename the file, the OS says "no can do; the file is in use".
but no matter where i look on the network when I go to Manage (right-clicking on My Computer), i cannot fine any reference under Shared Folders | Open Files to "close" the file.
can anyone shed some light on this? ie, how to close, etc.
thx very much.
seth j hersh
Dear All,
I am using SQL Server 2005 on Windows 2003. For some reasons from time to time, every 2 or 3 weeks, the SQL Server does not allow users to login anymore. It just does not work and all connections are dropped. After re-starting the service, everything is back working as normal. Please advise how this problem can be solved?
Another serious problem, after failing to stop the service, I decided to re-start the server. The SQL Server looks like just dead but looking at the service is stop and in the configuration manager SQL Server State "Change Pending....". Don't know what is wrong. Please kindly advise!
Regards,
LG
Hi,
I had a Database maintenance plan setup to do complete backup of my SQL Server 2000 database. Same thing was done for the transaction logs as well. And they had resulted in successful backups sometime ago.
But I have noticed that Backups are no longer happening. I cannot find the backup files where they are supposed to land. Some how, I cannot find any error messages relating why the backups are not getting created.
I do not know where I can look up the logs/reports of what possibly is going wrong. I have looked at the usual places and they are not there, for the times I have deliberately tried to submit the jobs.
Any help will be appreciated.
Thanks
Sam
I just stopped sql server 2000.
Right click server->stop
and closed...when i am trying to connect the server i have a message saying cannot connect.
How to get that back running
thanks
I recently restored my database from a backup and it seems that something in my service broker setup has stopped working, no messages now appear.
I checked that the broker was enabled (it wasnt and i had to use NEW_BROKER because of the 'same id' message)
The database is still set to trustyworthy.
There are also no messages in sys.transmission_queue
I used profiler with all the broker events enabled and the result was as follows:
Broker:Conversation Group
Broker:Conversation STARTED_OUTBOUND
Broker:Conversation CONVERSING
Broker:Message Classify
Broker:Conversation CLOSED
Broker:Conversation Group
Broker:Message Classify
But i'm not sure whether this highlights a problem or not? Any help gratefully appreciated!!
Thanks,
Andy
Publisher: SQL Server 2005 SP2
Subscriber: SQLExpress SP2
Merge pull replication
Merge process stopped uploading changes to the publisher. Downloading
changes to subscriber works fine. When record inserted/updated, reference is
saved into MSmerge_contents, generation incremented. Merge process on run
detects in which article change has been made. But when it stats to enumerate
changes the result is 0. Download part works. No errors and no data moving up
to the publisher.
I've checked the thread 'merge agent doesn't upload again!' In my case no errors been reported.
Please help. Thank you.
Hi,
My SQLEXPRESS service suddenly gives me this error.
ERROR: The SQL Server(SQLEXPRESS) service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service.
My OS is Microsoft Windows Server 2003, Enterprise Edition, SP2.
It all started when I restarted the system prompted by the Windows updates.
Please help. Its urgent as this is one of the important servers to be managed.
Thanks and Regards
Madhu