Checking Linked Server Status
Oct 26, 2007
Is there a quick way to determine linked server availability. I've got some select statements wrapped up in TRY/Catch blocks which will re-direct any failures to retreive data from a linked server to the catch block and I can get my data from elsewhere, but I have to wait for the statement to fail before I can jump into the catch block. I hoped to speed the process up by using an if exists check on the status of the linked server . Is there such a thing as a quick check on whether or not a linked server is online?
THX
View 1 Replies
ADVERTISEMENT
Jun 27, 2004
I have a question I hope someone can help me with.
My situation:
I have a single-step job in SQL Server, which runs a stored procedure, A. This stored procedure invokes another stored procedure, B. In B, one of the statements is a 'BACKUP DATABASE' command, and a database is backed up to a file.
The job is started by an application. Once started, the application then uses the SQL-DMO property CurrentRunStatus to periodically check the status of the job. When the property returns the value SQLDMOJobExecution_Idle (indicating the job has completed), the application code then continues processing, and attempts to access the .dat file produced by the 'BACKUP DATABASE' command.
My problem:
On occasion, the application will hit the problem where either the backup file cannot be located, or the file is still being locked by another process (Error=The process cannot access the file because it is being used by another process).
Is anyone able to shed some light on this?
I assume that the job will only return a completed status after:
(a) both A and B have completed execution, and
(b) the BACKUP operation has completed
Is it possible that even though SQL Server indicates the job has finished, that the BACKUP operation still hasn't completely ended?
Thanks for any help,
Andrew
View 2 Replies
View Related
Dec 19, 2006
Hi.
How can i check the job status using SQL Query?
View 25 Replies
View Related
Nov 7, 2001
Does anyone know how to check when a distribution job has finished
distributing a snapshot programatically?
I am writing an app that adds subscribers programatically. It first add the subscription (which creates a new snapshot) and after the snapshot is done, the distributor gets invoked automatically. Because I need to access the subscriber database after the publication is pushed I need to wait until
the distributor has finished its work.
Any help would be greatly apreciated! Thanks.
Kurt
View 1 Replies
View Related
Oct 27, 2006
I am a Junior DBA and i have to checks the various jobs on different servers.Please help me with a T-SQL way by which i can check the Job status through a Query.
Thanks in Advance
Jacx
View 3 Replies
View Related
Mar 25, 2002
Hi ,
On my Desktop i registered Production Server in Enterprise Manager
on that Server if i go to SecurityLinked Servers
There is another Server is already mapped, when i am trying to see the Tables under that one of the
Linked Server i am getting the Error message saying that
"Error 17 SQL Server does not exist or access denied"
if i went to Production Server location and if i try to see the tables i am able to see properly, no problems
why i am not able to see from my Desk top
i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER)
And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem
What might the Problem how can i see the Tables in Linked Server from my DESKTOP
Thanks
View 5 Replies
View Related
Apr 24, 2015
I am using Linked Server in SQL Server 2008R2 connecting to a couple of Linked Servers.
I was able to connect Linked Servers, but I cannot point to a specific database in a Linked Server, also, I cannot rename Linked Server's name.
How to point the linked server to a specific database? How to rename the Linked Server?
The following is the code that I am using right now:
USE [master]
GO
EXEC master.dbo.sp_addlinkedserver
   @server = N'Machine123Instance456',
   @srvproduct=N'SQL Server' ;
GO
EXEC sp_addlinkedsrvlogin 'Machine123Instance456', 'false', NULL, 'username', 'password'Â Â
View 6 Replies
View Related
Jul 18, 2006
Is there a way to bypass the syntax checking when adding a stored procedure via a script?
I have a script that has a LINKed server reference (see below) .
INSERT
INTO ACTDMSLINKED.ACTDMS.DBO.COILS ..etc.
ACTDMSLINKED does not exist at the time I need to add the stored procedure that references it.
PLEASE to not tell me to add the LINK and then run the script. This is not an option in this scenerio.
Thanks,
Terry
View 4 Replies
View Related
Feb 20, 2007
Hi
Will seem to be a basic question.
How can I check the number of CPU's used by a SQL Server.
Thanks
View 1 Replies
View Related
Oct 1, 2001
Hello,
I connect from SQL Server on Windows 2000 to Progress
Database on UNIX.
The database name of SQL Server is cstarsql and the
name is cstarint on UNIX.
I would like to schedule to copy data from
cstarint(Progress) to cstarsql(SQLServer). I did for
one time, but I want to control if the data has
already copied or not. If not, it will copy.
In DTS Query Builder to copy from Progress to SQL
Server,
SELECT * from calls WHERE call_date = TODAY
The name of table of calls is the same for both
database.The above calls is cstarint(Progress
Database)
This command is enough for one time. But I need to
control if the record has already copied.
In SQL Server, I control if the record is available or
not with @@FETCH_STATUS .
Now I would like to mix two queries, but I couldn't.
Can anybody do this?
DECLARE calls_cursor SCROLL CURSOR
FOR SELECT * FROM calls
WHERE call_date = TODAY
OPEN calls_cursor
-- Perform the first fetch.
FETCH NEXT FROM calls_cursor
-- Check @@FETCH_STATUS to see if there are any more
rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch
succeeds.
FETCH NEXT FROM calls_cursor
END
CLOSE calls_cursor
DEALLOCATE calls_cursor
Alice
View 1 Replies
View Related
May 1, 2001
Dear All,
We have recently moved our NT boxes with SQL Server to
a new computer room 10 minutes walk away.
Is it possible to examine the NT error logs from a SQL
Server client using perhaps an xp command?
Many Thanks in Advance,
Andrew
View 1 Replies
View Related
Jul 23, 2005
I would like to determine if a particular user has sysadmin serverrole. Is there a way to do this via the connection string? Currentlyour code checks if a login is valid using SQLDriverConnect, however weneed to be certain that the user can login and modify the schema.Is it possible to fetch a user's server role to determine if it has asysadmin server role?
View 2 Replies
View Related
Apr 9, 2008
I have a list of servers on a table, I take this list and pass to a for each loop container and that will grab some system information from each server that we give for our internal auditors.
The issue that I'm currently having with this package is that some times, 1 or 2 servers from the list are down and/or extremelly busy and it times out, either case causes the whole package to fail.
What I'm trying to do is, test the connection to the server prior passing the server name to the For Each Loop Container and log that information somewhere (Maybe a boolean field in a table), so then I check after the package finish and validate that the server was actually up or down.
Any ideas or suggestions?
Thanks,
Igor Santos
View 8 Replies
View Related
Oct 29, 2007
Hello guys,
Using SSIS, I want to check if a specific file exists on an FTP server or not. If yes, then i'll go with a flow, and if not, i'll go with another flow. Any help with that?
Thanks in advance
SHIKO
View 1 Replies
View Related
Apr 15, 2008
I am building an SSIS package that is hitting about 50 remote servers. I have a for each loop container that i am using to build the list of servers to run.
The problem is...sometimes networks go down, sometimes connections time out and this causes my entire package to fail. I inserted a task that will run select @@ServerName on the remote server inside the loop. My thought was, if this returns an error I could ignore it and move on to the next server in the list and log somewhere that server x threw an error. Logging is easy, the problem is, how do I then tell it to move on to the next server in the loop?
If anyone has any suggestions on how better to do this, I am open for alternatives...
View 4 Replies
View Related
Jul 9, 2001
Hello,
Can anyone guide me to monitor whether Production Sql server is up and running and would e-mail or page me if by chance it has stopped.
Any help is appreciated.
Thanks.
Priti
View 5 Replies
View Related
Jun 3, 2008
Hi Gurus,
While iam connecting to servers through management studio iam facing
iam seeing like few servers are connected with Green colour (like>)and few are connected with circle as white.Frm these how can i decide the server status.
Is there any comand to view server status ?
Thanks,
ServerTeam
View 3 Replies
View Related
Jun 6, 2006
This probably isn€™t a T-SQL question but couldn€™t find an appropriate place in the forum for it.
I€™m building a statistics page for our customers and i want to show how long the SQL server has been up since the last restart, I€™ve got the windows uptime from elsewhere but wondering if there€™s a way to get the SQL Server uptime.
Thanks
Chris
Dwebs Ltd
View 4 Replies
View Related
Aug 22, 2007
Here is my situation. I have a table in my application that pairs users with cars they like. We'll call this table Favorites. A user can browse the site and they can designate as many cars they want as favorites. For example, a user can go to the Honda Accord page and add that as a favorite car and then go to the Toyota Camry page and add that as a favorite car. However, if he/she goes to that Honda Accord page and tries to click the "Add to Favorites" button again, at the present state of my application, it will just add another entry into the Favorites table with a duplicate pairing. So, if I were to datalist the table to generate a listing of all favorites belonging to a certain user, he/she may potentially be returned with superfluous duplicate entries. Not to mention, taking up valuable database space and not looking very professional.
In my Favorites table, the 3 fields are.....favoriteId (set as primary key)userIdcarId
I've been thinking about this for awhile and I've come up with 2 solutions. I'm a newbie to ASP.NET/programming so I don't have enough insight to make a decision or to even think up of other alternatives.
1) Check proactively by doing a.....SELECT favoriteID FROM Favorites WHERE userId = x and carId = y (where x and y are variables)If I get a null return, it means I can go ahead and let the user add the car as a favorite in the database. If I get a valid value, then it means there already exists the same pairing, so I exit out without updating the table.
2) Check reactively by forcing an exception whenever a user tries to enter a duplicate pairing. I'm not sure how to do this, but perhaps, instead of making "favoriteId" a primary key, perhaps, I can make a primary key pairing of "userId" and "carId". And by trying to do an insert with a primary key that already exists, we know it won't work since primary keys by definition are unique.
Now, I expect some concurrent users on my site, so I must take into consideration pros and cons of each and determine which is more efficient. Checking proactively will force a check even if the table does not contain a duplicate pairing of user and car. However, having a duplicate primary key may be more expensive from a database point of view and may slow down lookups, etc. Or maybe neither has significant benefits, in which case, I rather go with proactive, since I've already coded it and it works fine. Or maybe there is a third alternative, which I did not think. Which method do programmers usually take and which is a better practice?
TIA for your help.
View 5 Replies
View Related
Oct 12, 2006
Is it possible to get the latest inserted Identity value on a remote server?
(The following gives a NULL result:
select ident_current('[my_linked_server].thedatabase.dbo.thetable')
)
View 4 Replies
View Related
Oct 30, 2007
Hi All,
I'm a relative novice on SQL Server and am a complete beginner at SQL, so am looking for a little help.
I currently use a DTS package to perform inserts / updates to a "production" table.
The DTS package transforms a comma separated file into a "temporary" table that is truncated / cleared before the load starts.
The temporary table has a column denoting Insert or Update. The production table is almost identical, however, doesn't contain the Insert / Update column. The DTS package then, depending upon the Insert / Update flag, either inserts data into the production table or updates data in the production table.
When the DTS package has completed, I'd like to be able to run an SQL Query that validates everything in the "temporary" table is identical to that in the "production" table, which it should be.
I have managed to do some queries to verify that everything has loaded / updated i.e. select primary_key from temporary table where primary_key not in (select * from production table), however, what I haven't been able to do is verify that all the columns on the temporary table match the values in the production table (excluding the Insert / Update flag).
I tried concatenating the columns in each table and comparing the concatenated values, however, this failed due to the different data-types, i.e. decimal, text etc.
Any help will be greatly appreciated.
Many thanks.
Cheers,
David
View 8 Replies
View Related
Oct 30, 2007
Short of doing an INSERT and subsequent DELETE on a table, how do I figure out if a user has INSERT/UPDATE/DELETE access to a particular table?
View 11 Replies
View Related
Jan 14, 2007
Hi
I have an VB.NET application connected to a SQL Server Express. I want to let the application to run in either "Normal mode" or "Holiday mode" according to current weekday is normal day or state public holiday.
My approach is to find out all the public holidays in a year and enter them into a Holiday Table. Then some code in my application constantly check the current weekday against the one in the holiday table, if matches, the application goes into holiday mode.
This approach is not perfect as "State public holidays" are confirmed by the state government in the current year and the coming year. So state public holidays are unconfirmed for the third year afterward. The system is required by client to support public holiday in the next 10 years.
I wonder what is the best approach to this problem?
Thanks
View 5 Replies
View Related
Feb 21, 2007
I'm trying to
get reports and report server site aveable through user certificate.
Web site is
under default web site. Under default web site is certificate server web page,
it is aveable and configured the same way and it works if I use certificate.
If I type https://FQDN/Reports
, i get error The request failed with HTTP status 403: Forbidden. But if I type
https://FQDN/reportserver i can access.
Both sites are
aveable if I use only https. But if I but if i use Require client certificate,
than I get 403 error.
In rsreportserver.config,
there is <UrlRoot>https://NETBIOSServerName/reportserver</UrlRoot>,
does not help if I use FQDN.
If I enable anonymous
access, I can not access Reports site, an I get error 403, but on Reportserver
site I get following error :
Reporting
Services Error
--------------------------------------------------------------------------------
The permissions
granted to user 'MSVEPOIUSR_WEPO-DC' are insufficient for performing this
operation. (rsAccessDenied) Get Online Help
--------------------------------------------------------------------------------
SQL Server
Reporting Services
SQL is 2005 SP1
, and OS is Windows server 2003 SP1
View 1 Replies
View Related
Apr 23, 2014
I want to update table2 by checking table1.
if trauma has atleast 1 then clm2 in table2 would be 1
if infec has atleast 1 then clm2 in table2 would be 2
table1
Clm1 Clm2
Trauma 1
Trauma 1
Trauma 1
Infec 2
Infec 2
Trauma Null
table2
clm1 clm2
Trauma 1
infec 2
I have to update table2 based on table one ny checking multiple columns.
View 2 Replies
View Related
Mar 16, 2015
I have a table with EmployeeID, StartDate, and EndDate with a PK of EmployeeID, StartDate. How can I check to see that there's no overlap for StartDate and EndDate for a given employee? That is, on any given day there must only be 1 row for an employee where Getdate() is Between StartDate and EndDate. For an active employee their EndDate is set to 06/06/2079.
I've tried it using Row_Number() with Over() but am returning too many rows indicating overlap when none exists.
View 7 Replies
View Related
Feb 18, 2008
I am running a SQL Server procedure from ADO in visual C++, sometimes I get return -6 as return code from procedure.
From SQL Server documentation:
"SQL Server currently uses return values 0 through -14 to indicate the execution status of stored procedures. Values from -15 through -99 are reserved for future use."
I cannot find a description for the returns codes from SQL Server for error conditions (from -1 to -14), does anyone here knows where can I find this information?
Best regards,
Pedro Chau
View 1 Replies
View Related
Sep 17, 2015
I want to do something with error checking in my company. For this we have a selection of different tables and the data needs to meet various validation rules else it is classed as an error.
To deal with this I'm currently thinking of this approach:
1. Create a view pulling all of the various data together from the multiple tables.
2. Create an empty 'errors' data table.
3. Create an Excel file with a button to call a Check for Errors Script
Then in the the script:
1. Clear the 'errors' data table
2. Call multiple scripts, each of which uses the new view, applies the checks for that specific error and writes any erroring data into the 'errors' data table (along with a text string with the unique error code for filtering / sorting purposes).
3. After calling all the scripts, the table can be refreshed in excel when when used with a pivot table can show the various errors, and let us drill down into all the data so we can fix them.
Also.. Ideally, I'd like some way to write comments in an excel column for each entry and error code and be able to write that back into a comment table.
View 5 Replies
View Related
Oct 3, 2007
Hello,
is there a way to find out that SQL Server 2005 (Express) engine is performing startup checked of a selected database?
The problem we are faced to is that our users shutdown brutally their laptops running their database. When they start laptops again, they impatiently start the application that tries to connect to the database. However, they get an error message:
Cannot open database XXXXX requested by the login. The login failed.
When they try after a minute or two, it works.
What we would like to do is provide an information message that the SQL server is checking the database and that they must wait for a while. Is there is a specific error code for this, can anyone help? We are using .NET 2.0.
Thank you,
Jan
View 5 Replies
View Related
Jul 24, 2003
Hi,
I ahve attached the bmp doc that will show my screen snapshot.
My SQL server Agent service indicator is not showing green as like other server. I have started the serice and running fine. I don't have any problem running the job.
My curious is why that indicator is not showing up.
Thanks,
Ravi
View 5 Replies
View Related
Mar 26, 2015
I have a requirement in a payroll system to show the status of an employee on the first and last day of each pay period. The pay periods are pretty simple, 24 per year starting on the 1st and 16th of each month. The status effective date, however, can occur on any day, not just the first or last day of the pay period. Here's my DDL.
SET NOCOUNT ON;
declare @EmployeeStatus TABLE (
[EmployeeID] [int] NOT NULL,
[EmployeeStatus] [varchar](24) NOT NULL,
[EffectiveDate] [date] NOT NULL
[Code] .....
View 9 Replies
View Related
Oct 28, 2015
I get records from a query whose status is "Marked". I need another column to be added to result set which just says "Marked" for every record. Is that possible in query?
View 6 Replies
View Related
Jan 7, 2008
How to query the database to see if a job is still running?
View 1 Replies
View Related
