I have an SSIS Package stored in the MSDB database which works fine when I execute it. It will run under SA account or will use Windows security. I want to schedule this job to run with SQL Agent, but when I do I get a strange error and whatever configuration I try it fails with the following error: Executed as user: SERVER\SYSTEM. on ...
In SQL Server 2000/2005, I want to get the names of all connected servers. How do I do this? ...
Our SQL 2005 server has a Maintenance Plan that cannot be deleted. When I try and delete it via Management Studio I get the following error: An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) -> The SELECT permission was denied on the object 'sysmaintplan_plans', da...
Hi, I have asked a similar question previously but it was never resolved so here I am again! I have an unattended installation of SQL Server 2005 that works great when installing SQL Server on a machine that does not have it already installed. I use the following parameters when I perform the installation (this is installed automatic...
Hi, I want to add a record/s from two tables to a third table. It is something like the following: table1: pln taskName plnHec pinDate(mm/dd/yyyy) xx 10 3/1/2008 yy 20 4/1/2008 zz 10 3/1/2008 zz 10 4/1/2008 xx 10 4/1/2008 tabl...
4060 "Server rejected the connection; Access to selected database has been denied". How can I troubleshoot this error? ...
I have the following issue: when a stored proc is called from my application, every now and then (like 1 time out of 1000 calls), it takes 10-30 seconds to finish. Typically, the sproc runs in under a second. It's a fairly simply proc with a single select that ties together a couple of tables. All the table names are set with a (NOLOC...
I have the following table in my database: CREATE TABLE [dbo].[XmlData]( [id] [int] IDENTITY(1,1) NOT NULL, [xmldata] [xml] NOT NULL, CONSTRAINT [PK_XmlData] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMA...
I have a SQL Server 2005 database that is backed up nightly. There backup consists of: 1 FULL backup of the database. 1 backup of the transaction log. These are currently two separate jobs. The log is huge and I'd like to set things up so that: 1) the database is backed up in full nightly 2) the log is set such that I can recover the...
I'm trying to determine the percent of null items for all fields of a table. I have to run this on several tables with a ton of fields, and I was looking for an automated way of doing this. I know I can query "information_schema.columns" and get a nice clean list of field names ala: select Column_name from information_schema.columns ...
I have previously asked a question about a stored proc that was executing too slowly on a sql server box, however, if I ran the sproc in Query Analyzer, it would return under one second. The client is a .NET 1.1 winforms app. I was able to VNC into the user's box and, of course, they did not have SQL tools installed, so I cranked up ...
Am trying to write a query that would output something similar to the last batch of records below. For sure it works in Reporting Services and Crystal Reports, but just throwing it out here to see if it is possible in SSMS/QA: Table: id name amount -- ---- ------ 1 rob 23.00 2 rob 34.00 3 dan 45.00 4 dan 56.00 select name...
I have a table with an XML column. The documents are pretty large and numerous, and I've been trying various ways to improve the performance of basic queries. Because the documentation indicates that applying an XML Schema to the column can help SQL Server to optimize queries — and because one was available — I created a schema collect...
I'm looking the way to temporarily turn off all DB's constraints (eg table relationships) I need to copy (using INSERTs) one DBs tables to another DB I know I can archive that by executing commands in proper order (to do not break relationships) But it would be easier if I could turn off checking constraints temporarily and turn on it...
I have a table with primary key in my MS SQL Server 2005 table. I would like to disable it. Now i get error: Violation of PRIMARY KEY constraint 'PK_Name'. Cannot insert duplicate key in object 'dbo.Table'. I would like this error not to occur and to work with PRIMARY KEY like with normal column without constraint and than restore this...
The text could be stored in the database using either markdown, bbcode, html, etc. Should I remove any allowed tags from the search terms? Does your markup parser have any method to assist in that task? I was going to use like '%searchword%' queries. Does full text search offer any advantages for such a simple text search? Update: It s...
Scenario In our replication scheme we replicate a number of tables, including a photos table that contains binary image data. All other tables replicate as expected, but the photos table does not. I suspect this is because of the larger amount of data in the photos table or perhaps because the image data is a varbinary field. However...
I'm trying to set up transactional pull replication on 2 SQL Server 2005 instances, through a 3rd as a distributor. When the subscription is being initialized, it bulk inserts properly, giving the message that the snapshot was successfully loaded. Then it makes primary key indexes as usual. At this point the job starts over, dropping al...
i have columns i.e. theory marks, pratical marks, student's Id ,course Id ,& subject Id i need to add up the vaules present in columns theory marks & practical marks in order to get the aggregate, i dont know how to add the column values though. I am using sql server 2005 please help ...
I have a very large table with a fulltext indexed column. If I partition this table sensibly (for me, sensibly is by date), will it speed up queries? Or will the full text clause still search through the whole table, even if the query limits to a single partition? From what I've seen so far, I think the answer is partitioning won't help...