InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 09 Feb 2012 19:19:05 GMT20http://www.sqlservercentral.com/Forums/Topic1250013-146-1.aspxI'm getting the following error when I run this[b]Error:[/b](15 row(s) affected)Msg 214, Level 16, State 3, Procedure sp_trace_setstatus, Line 1Procedure expects parameter '@traceid' of type 'int'.[b]Script:[/b]declare @traceid intdeclare @maxfilesize bigintdeclare @on bitset @on = 1set @maxfilesize = 50 --size in MB'sexec sp_trace_create @traceid output, 2, N'C:\myTrace\mytrace', @maxfilesize, NULLexec sp_trace_setevent @TraceID, 114, 14, @onexec sp_trace_setevent @TraceID, 114, 27, @onexec sp_trace_setevent @TraceID, 114, 21, @onexec sp_trace_setevent @TraceID, 114, 3, @onexec sp_trace_setevent @TraceID, 114, 35, @onexec sp_trace_setevent @TraceID, 114, 12, @onexec sp_trace_setevent @TraceID, 114, 11, @onexec sp_trace_setevent @TraceID, 114, 40, @onexec sp_trace_setevent @TraceID, 114, 26, @onexec sp_trace_setevent @TraceID, 114, 28, @onexec sp_trace_setevent @TraceID, 114, 22, @onexec sp_trace_setevent @TraceID, 114, 34, @onexec sp_trace_setevent @TraceID, 114, 1, @on-- Set the Filtersdeclare @intfilter intdeclare @bigintfilter bigintexec sp_trace_setfilter @TraceID, 1, 0, 7, N'DBCC USEROPTIONS'exec sp_trace_setfilter @TraceID, 1, 0, 7, N'-- network%'exec sp_trace_setfilter @TraceID, 1, 0, 7, N'-- sp_who2%'--exclude System Databasesset @intfilter = 4exec sp_trace_setfilter @TraceID, 3, 0, 2, @intfilter -- modified 3/22/2006exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'exec sp_trace_setfilter @TraceID, 11, 0, 7, N'%dbaudit'-- exec sp_trace_setfilter @TraceID, 11, 0, 7, N'%bbrooks'-- exec sp_trace_setfilter @TraceID, 11, 0, 7, N'%argent%'-- exec sp_trace_setfilter @TraceID, 11, 0, 7, N'%dss_sql_service'set @intfilter = 100exec sp_trace_setfilter @TraceID, 22, 0, 4, @intfilterset @intfilter = 17exec sp_trace_setfilter @TraceID, 28, 1, 0, @intfilterset @intfilter = 18exec sp_trace_setfilter @TraceID, 28, 1, 0, @intfilterexec sp_trace_setfilter @TraceID, 34, 0, 7, N'spt%'exec sp_trace_setfilter @TraceID, 34, 0, 7, N'#tbl_sys%'select * from ::fn_trace_getinfo(default)exec sp_trace_setstatus TRACEID, 1 --start traceThu, 09 Feb 2012 16:00:55 GMTsunny.tjkhttp://www.sqlservercentral.com/Forums/Topic1249827-146-1.aspxHi,I have a DB Server with 64 GB RAM. the HDD is partitioned as C: 26GB and D:108GB. I have SAN with 1TB (clustered). many times C drive is going out of space. my temp DB is getting full, and causing bad performance in queries.what could be the ideal size C:is there a possibility to add more HDD to C drive?how can i avoid tempDB filling up?thanks,amiThu, 09 Feb 2012 10:12:24 GMTAnamikahttp://www.sqlservercentral.com/Forums/Topic1249515-146-1.aspxExperts,One of our database log grows to huge size, which is unexpected.I would like to run a server side trace and find what causes the log file growth.1. What are the minimum events and fields I need to capture ?2. shouldn't it be OK, if I run the "Server side trace" for whole night ?Thanks in advance.Smith.Thu, 09 Feb 2012 01:06:18 GMTJoy Smith Sanhttp://www.sqlservercentral.com/Forums/Topic1249611-146-1.aspxI got a query from friend.He wants to Apply SP4 of SQL 2005 server on Windows 2003 server in VERITAS Cluster Environment ( Active – Active).Does Anybody has any specific instruction on this.Thu, 09 Feb 2012 04:48:55 GMTchetanr.jainhttp://www.sqlservercentral.com/Forums/Topic937194-146-1.aspxMy Transaction Log backup has 'MaximumErrorCount' error as below:SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (%1!d!) reached the maximum allowed (%2!d!); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. Question: How to get or reset this maximum number before DtsContainer stops running?ThanksMon, 14 Jun 2010 17:56:08 GMTirene.leunghttp://www.sqlservercentral.com/Forums/Topic1249411-146-1.aspxI detached some SQL server databases on a SQL 2005.Then I later tried to reattach it using management Studio, when I browse to the directory I can only see those mdf files for online databases, those detached ones are not showing.But it exists in the same path. I can browe using windows explorer, but not in ssms.I have sysadmin server role. How to fix?ThanksWed, 08 Feb 2012 17:25:22 GMTsqlfriendshttp://www.sqlservercentral.com/Forums/Topic1248825-146-1.aspxI created a simple cube in SQL Servrer 2005.When clicked on the "Browser", it is showing "Loading....." but nothing is coming up.It's showing like that for long time.Can anyone guide me on this pls.Thanks in advance.Smith.Wed, 08 Feb 2012 03:31:52 GMTJoy Smith Sanhttp://www.sqlservercentral.com/Forums/Topic782715-146-1.aspxHi I am getting the following error message while running a transactional replication:Agent message code 20084. The process could not connect to Distributor Any Idea?Thu, 03 Sep 2009 22:26:33 GMTjeet_personalhttp://www.sqlservercentral.com/Forums/Topic1249192-146-1.aspxCould someone please tell me if the following query is correct:Begin TransactionDelete from tableabcd where starttime < '1/1/2011'Rollback TransactionWed, 08 Feb 2012 10:40:19 GMTsunny.tjkhttp://www.sqlservercentral.com/Forums/Topic984751-146-1.aspxHello guysI was trying to run a script which calls sp_msforeachdb proc, but the system (2005 express edition) gave the following error message:Msg 2812, Level 16, State 62, Line 18Could not find stored procedure 'sp_msforeachdb'.If I run "select object_id('sp_msforeachdb')", it returns null. But "select object_id('sp_who')" does return a negative integer.Also, the sp_msforeachdb does not exist in the sysobject table.However, from the system stored procedure list in master datbase, I can still see this procedure there.I tried to use its definition script generated from the "modify" option and modify/recreate this proc again, but only bumped into another error message:for modify: alter proc [sys].[sp_MSforeachdb]Msg 208, Level 16, State 6, Procedure sp_MSforeachdb, Line 46Invalid object name 'sys.sp_MSforeachdb'.for create: create proc [sys].[sp_MSforeachdb]Msg 2760, Level 16, State 1, Procedure sp_MSforeachdb, Line 46The specified schema name "sys" either does not exist or you do not have permission to use it.BTW, I am in the sysadmin role on this server instance.How can I receover this import system stored procedure then?Thanks in advance for your help!regards,NingMon, 13 Sep 2010 07:28:14 GMTsqlapprenticehttp://www.sqlservercentral.com/Forums/Topic1239700-146-1.aspxI need to move system databases in a 2005 (Enterprise SP3 64bit) 2-node cluster.The instance uses 4 SAN drives, R:,S:,T:,U: for database files. I need to movethe system databases from R: and S: to T: and/or U: because the R: and S: aregoing away (to reclaim SAN space no longer used). My question is: Are there any special considerations in the process for clustering? I've tested the moveprocess on an unclustered instance and am comfortable with that. [b]When it comestime to start the instance from a command prompt with /f and /T3608 flags, are thereany special steps?[/b] Since T: and U: are already used by SQL I figure there should be no cluster resource permission issues. Any thing else special to think about?I don't have a clustered sandbox to test this on.Fri, 20 Jan 2012 12:58:14 GMThayden_joneshttp://www.sqlservercentral.com/Forums/Topic1248584-146-1.aspxHi, I am planning to move an existing large table on SQL Server 2005 to a new file group. Can you guys tell me if it is possible..if yes, could you please provide me the steps.Thanks!Siva.Tue, 07 Feb 2012 15:31:18 GMTSiva Ramasamyhttp://www.sqlservercentral.com/Forums/Topic1249572-146-1.aspxI have installed MSQL 2005 on Windows 2003. We have enabled SNMP service on server and MSSQL.MIB is also installed. I have added added some required MSSQL events in Window Events (by using cmd evntwin) but not getting SNMP traps while start/stop the services by SQL Server Service Manager.Please provide the steps that how to enable SNMP alerts?Thanks!!Thu, 09 Feb 2012 03:18:22 GMTer.mukuljainhttp://www.sqlservercentral.com/Forums/Topic1249462-146-1.aspxHI,we have installed SQL 2008 on server 2 and application is installed on server 1.We are able to create DB using our application using SQL credentials on remote server, but if we try to create DB on remote server using windows authentication we are encountering following error.Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.the windows credentials are domain Admin users, also added as the machine admin group.We are able to created DB with our application using windows authentication (domain user) on same server.Wed, 08 Feb 2012 22:48:28 GMTshahid_jambagihttp://www.sqlservercentral.com/Forums/Topic1249260-146-1.aspxI am trying to wrap my head around this whole index fragmentation subject.I am running the following query:SELECT object_id, index_id, avg_fragmentation_in_percent, avg_page_space_used_in_percentFROM sys.dm_db_index_physical_stats(DB_ID(N'NNWareIncoming'), NULL, NULL, NULL, 'DETAILED') as fragMicrosoft documentation says:Examine the value in avg_page_space_used_in_percent to determine whether the index contains internal fragmentation. Internal fragmentation is indicated when this value falls below 75.Problem is, nearly all values I get are MUCH lower 75. For example, I get:6.983.343.0814.03etc...So, this means that all my indexes are fragmented? I didn't believe it because the indexes were recently build, but still I rebuilt all indexes - no difference.Please explain if you can. Very confused.Thank you,Oksana.Wed, 08 Feb 2012 12:20:26 GMTOksana Marchhttp://www.sqlservercentral.com/Forums/Topic989595-146-1.aspxHi there,I was wondering if I can get the SQL-Server 2005 (32bit - Standard Edition) to use more the 4 GB of RAM on a Windows 2003 Server (64bit - Enterprise Edition)? :unsure:Can I enable the AWE switch, or does it only work in a 32bit environment? Thanks in advance.Regards, MrAkkiMon, 20 Sep 2010 11:16:06 GMTMrAkkihttp://www.sqlservercentral.com/Forums/Topic1249284-146-1.aspxCould someone please help me with the size estimations of a database??I need to do size estimations on a database which is currently residing on a server. We send the trace data to a table which exists in this database.This database is very small as the table has following columns: dbatraceID, StartTime, EventClass, EventSubClass, DatabaseId, DatabaseName,SPID ,LoginName , DBUserName, ServerName, ObjectType, ObjectID, ObjectNameBut the new requirement is that I need to add TextData column to this table. I'm expecting the database to grow rapidly.How can I estimate the growth of this database?Wed, 08 Feb 2012 12:57:20 GMTsunny.tjkhttp://www.sqlservercentral.com/Forums/Topic442391-146-1.aspxDear SQL Server Central,So far it has been a nightmare, if you got your database collation wrong.One of my colleagues has written a script that will do most of the hard work for you, and report where it fails.Would it be possible for someone to test (and/or review) this script for us?Best regards,Henrik Staun Poulsenwww.sql.udstyr.dk--*******************************************************SET NOCOUNT ON/*Description: Code to change collation on a database.Pre_condition: Post_condition: Input: Output: Written by: Karsten Laursen, Vestas Wind Systems A/S, DenmarkChange History: $Date: 6-07-07 9:12 $ , $Author: KLAUR $, $Revision: 1 $ */DECLARE @NewCollation sysname, @CurrentCollation sysname, @DatabaseName sysname, @SQLString nvarchar(max), @TableName sysname, @ColumnName sysname, @Nullable varchar(3), @DataType nvarchar(128), @CharacterLength int, @DashString varchar(100), @ErrorMessage nvarchar(max), @ErrorMessageLength int, @ErrorCount int, @PrintString varchar(100), @i intSET @NewCollation = 'Latin1_General_CI_AS' --SET @NewCollation = 'Danish_Norwegian_CI_AS'SET @CurrentCollation = CAST( DATABASEPROPERTYEX ( db_name(), 'Collation' ) AS sysname)SET @DatabaseName = db_name()SET @SQLString = ''SET @DashString = '---------------------------------------------------------------------'SET @ErrorMessage = ''PRINT 'Current database: ' + @DatabaseNamePRINT 'Current collation: ' + @CurrentCollationPRINT 'New collation: ' + @NewCollationPRINT ''IF EXISTS (SELECT 1 FROM master.dbo.sysprocesses WHERE dbid = db_id() AND spid <> @@spid )BEGIN PRINT 'Database ' + @DatabaseName + ' could not be locked exclusively' RETURNENDIF @NewCollation NOT IN (SELECT name FROM master.dbo.fn_helpcollations())BEGIN PRINT @NewCollation + ' is not a valid collation' RETURNENDIF @NewCollation <> @CurrentCollationBEGIN SELECT @SQLString = 'ALTER DATABASE ' + @DatabaseName + ' COLLATE ' + @NewCollation BEGIN TRY EXEC (@SQLString) PRINT 'Database collation has been changed' END TRY BEGIN CATCH PRINT 'Database collation could not be changed due to the follwing errors:' PRINT @DashString EXEC (@SQLString) RETURN END CATCH PRINT ''ENDDECLARE @Columns TABLE( TableName sysname, ColumnName sysname, Nullable varchar(3), DataType nvarchar(128), CharacterLength int, Changed bit, Error bit, ErrorMessage nvarchar(2048))INSERT @Columns( TableName, ColumnName, DataType, CharacterLength, Nullable, Changed) SELECT T.TABLE_NAME, C.COLUMN_NAME, C.DATA_TYPE, C.CHARACTER_MAXIMUM_LENGTH, C.IS_NULLABLE, 0 FROM INFORMATION_SCHEMA.COLUMNS C JOIN INFORMATION_SCHEMA.TABLES T ON C.TABLE_NAME = T.TABLE_NAME WHERE C.DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar', 'text', 'ntext', 'sysname') AND C.COLLATION_NAME <> @NewCollation AND T.TABLE_TYPE = 'BASE TABLE'IF EXISTS (SELECT 1 FROM @Columns)BEGIN PRINT 'The following columns has been changed:' PRINT @DashStringENDELSE BEGIN PRINT 'No changes needed' RETURNENDWHILE EXISTS (SELECT 1 FROM @Columns WHERE Changed = 0 AND Error IS NULL)BEGIN SELECT TOP 1 @TableName = TableName, @ColumnName = ColumnName, @DataType = DataType, @CharacterLength = CharacterLength, @Nullable = Nullable FROM @Columns WHERE Changed = 0 AND Error IS NULL ORDER BY TableName, ColumnName SELECT @SQLString = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @ColumnName + ' ' + @DataType + CASE WHEN @CharacterLength = -1 THEN '(max)' WHEN @DataType IN ('text', 'ntext') THEN '' ELSE '(' + CAST (@CharacterLength AS nvarchar(10)) + ')' END + ' COLLATE ' + @NewCollation + ' ' + CASE @Nullable WHEN 'NO' THEN 'NOT NULL' ELSE 'NULL' END BEGIN TRY EXEC (@SQLString) UPDATE @Columns SET Changed = 1 WHERE TableName = @TableName AND ColumnName = @ColumnName PRINT @TableName + ', ' + @ColumnName END TRY BEGIN CATCH UPDATE @Columns SET Error = 1, ErrorMessage = ERROR_MESSAGE() WHERE TableName = @TableName AND ColumnName = @ColumnName SELECT @ErrorMessage = @ErrorMessage + @TableName + ', ' + @ColumnName + ': ' + ErrorMessage + char(10)-- + char(13) FROM @Columns WHERE TableName = @TableName AND ColumnName = @ColumnName END CATCHENDIF @ErrorMessage <> ''BEGIN SELECT @ErrorCount = COUNT(*) FROM @Columns WHERE Error = 1 SELECT @PrintString = 'The following errors occured (' + CONVERT(varchar, @ErrorCount) + '):' PRINT '' PRINT @PrintString PRINT @DashString SELECT @ErrorMessageLength = LEN(@ErrorMessage) SET @i = 1 WHILE @i <= @ErrorMessageLength BEGIN PRINT SUBSTRING(@ErrorMessage, @i, 4000) SELECT @i = @i + 4000 ENDENDMon, 14 Jan 2008 05:01:52 GMThenrik staun poulsenhttp://www.sqlservercentral.com/Forums/Topic1249179-146-1.aspxOur company has a vender package that has a table that contains a float data type. The system populates the value with in the code. Today we found out what is populated and what we see are different then what is being stored. I am confused on how I can see one thing and what is stored is something different. For instance. SELECT org -- Float (53)FROM TableResults.org----------------------200100100000000(1 row(s) affected)You see 200100100000000 as the resultbut this where clause does not work.SELECT orgFROM TABLEWHERE org = 200100100000000 Resultsorg----------------------(0 row(s) affected)If I cast(org as real) SELECT orgFROM TABLEWHERECAST( org AS REAL) = 200100100000000 Result org----------------------200100100000000(1 row(s) affected)So I'm having a hard time grasping why the data is not really what you see. The precision is 15 digits and the value takes up all of it, so there is no rounding that is taking place. Can someone help me understand why or how this happens.ThanksWed, 08 Feb 2012 10:16:47 GMTHarold Bucknerhttp://www.sqlservercentral.com/Forums/Topic1248863-146-1.aspxI just wanted a little bit more information on parameter sniffing as we have a web application that utilises some search functionality, so parameters are passed via a stored procedure.Now we have had speed issues, and we [i]may[/i] have narrowed it down to the sproc but we are still looking at a number of possibilities :-)Anyway, according to some documentation it is an option to alter procedure parameters to be local variables, this would make query execution better. I read that if a procedure was to take in 'varying' parameters then this is a good option.I dont understand what it means by varying parameters, is it purely by value, or by how many rows they have etc? I am aware of query plans and the underlying execution contexts, is there a limit to how many contexts a plan can have?One point I suggested that by making the change, could that actually reduce performance as contexts in theory would no longer exist, is this right? I know the best way will be to analyse the plans in both scenarios but I just wondered if there was a general rule.As always, any help would be massively appreciated.Wed, 08 Feb 2012 04:34:14 GMTdavid.alcockhttp://www.sqlservercentral.com/Forums/Topic1249017-146-1.aspxHi,I have this four tables:Name: Location_type Columns: (Id;Type) Records: 1 ; CAR 2; TruCK 3; WhareHouseName: Cars Columns: (Id;Brand) Records: 1;bmw 2 ; audiName: trucks Columns: (Id;brand) Records: 1;MercedesName: WareHousesColumns: (Id;local) Records: 1;PalmI what to create a New table that will relate the types of locations where stock can be in , with all the possible physical location (car 1, car 2, truck, warehouse)I have created a new table named Physical_Location as follow: Columns: (Id;Locationtype;Physical) Records: 1;1;1 2 ;1;2 3;2;1 4;3;1I whant that column physical can only have values from the cars table id or the trucks table id or the warehouse table id.How can i do this?ThanklsWed, 08 Feb 2012 08:15:46 GMTriver1http://www.sqlservercentral.com/Forums/Topic1248457-146-1.aspxHi All;We have an application that uses JDBC adapters to connect to our SQL Server 2008R2 Standard databases. We added a Development application environment, and added a new SQL instance (servername\DEV) on the same physical box. When I point the JDBC adapter at the DEV SQL instance and try to connect, I get an error: 'Cannot open database "databasename" requested by the login. The login failed'. The DEV instance has the same database names and user as the default instance. If I change the JDBC connection in the DEV environment to point to the default instance, it connects properly. The port for the default instance is 1433, and the named instance is using the default TCP Dynamic Ports. Do I need to change to a static port to allow the JDBC adapter to be able to login?ThanksTue, 07 Feb 2012 12:59:57 GMTDoug Beershttp://www.sqlservercentral.com/Forums/Topic733014-146-1.aspxIs it normal to have higher (around 30%) % Privileged Time on VM?If no, what could be causing it?Also following entries are found in the event log of that server:The operating system returned error incorrect pageid (expected 1:175598; actual 0:0) to SQL Server.The operating system returned error stale page (a page read returned a log sequence number (LSN) (1863:84729:1) that is older than the last one that was written (0:0:0)) to SQL ServerHow to fix this?Thu, 11 Jun 2009 07:31:50 GMTSuresh B.http://www.sqlservercentral.com/Forums/Topic1247863-146-1.aspxHi All,I connected to one of the server and and detached one database.After that I was not able to do anything is SQL Server as the detached database was my default database.I couldn't even reattach it also.My question is,How can we find out if a particular database is the defualt database for any login ?What are the things we need to check when we detach a database ?.What are the things we need to check when we overwrite a database ?Thanks in advance.Smith.Tue, 07 Feb 2012 00:40:48 GMTJoy Smith Sanhttp://www.sqlservercentral.com/Forums/Topic1247774-146-1.aspxSorry, I am rather junior in my SQL Server knowledge.I have a maintenance stored procedure which calls a separate procedure to reindex tables. The reindex procedure uses two threshold values to determine whether to reorganize the indexes or rebuild them.This all runs within a transaction, and for certain clients the transaction log grows from a few megabytes to several gigabytes after the scripts run.The recovery model on the database is set to Simple.Questions:1) Should I be running the reindexing stored procedure within a transaction?2) What is the best way to remedy this situation?2) If I cannot prevent the transaction log growth, is it safe to shrink the file at the end of the maintenance script?Thank you.Mon, 06 Feb 2012 18:18:32 GMTcheetahkatsuhttp://www.sqlservercentral.com/Forums/Topic1248772-146-1.aspxI am getting SQL Server Error 21854 The Error Message says : Could not add new article to publication output because of active schema change activities or a snapshot is being generated. Any suggestions [ Solution | TIPS ] to resolve [FIX] this ? This is SQL Server Severity 10 MessageWed, 08 Feb 2012 01:54:13 GMTsolomon.jernashttp://www.sqlservercentral.com/Forums/Topic466590-146-1.aspxI am trying to move the system databases of a 2000 server to a new location, soundssimple enough!Got the instructions from http://support.microsoft.com/kb/224071Sought out and got confirmation from a less 'wordy' source.Both instruction sets agreed on a procedure (adding parameter -c -m -T3608 etc).Tried it and keep getting the same message:-Server: Msg 7940, Level 16, State 1, Line 1System databases master, model, msdb and tempdb cannot be detached.What am I missing!!Mon, 10 Mar 2008 05:53:48 GMTdobrien-670718http://www.sqlservercentral.com/Forums/Topic1248638-146-1.aspxI have setup a database mail profile and account. I am able to send test emails to my work email by right clicking the database mail.But I am getting any emails via SQL Server agent(I have given the same email id for the operator) for job completion or failure...I tried to restart SQL SERVER AGENT to see if it fixes the problem...no luck...have you guys come across this kind of a problem before..??Tue, 07 Feb 2012 17:09:35 GMTSiva Ramasamyhttp://www.sqlservercentral.com/Forums/Topic1247597-146-1.aspxIs there a script or any third party tool which can help in deploying service packs and CU's across multiple sql servers in parallel?Mon, 06 Feb 2012 11:45:13 GMTsqldba_newbiehttp://www.sqlservercentral.com/Forums/Topic1247679-146-1.aspxHi Folks,I have a server installed SQL 2005 Express, I have no problem remote into that machine and start SSMS there, however, when I try to use SSMS to remotely connect to the SQL Express server, it throws the following error:[color="red"]Cannot connect to TISMJPVV62\SQLEXPRESS.===================================A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)[/color]I already checked SQL browing and TCP/IP connection, both enabled and running.What could be wrong?I am using VPN to connect to my company's network, TISMJPVV62 is the machine's name. I have no issue when connecting to other SQL server (non-express) same way as I did to expressMon, 06 Feb 2012 14:01:32 GMThalifaxdalhttp://www.sqlservercentral.com/Forums/Topic514155-146-1.aspxI need Sql tables for all countries States and CitiesMon, 09 Jun 2008 23:29:33 GMTmanaprimehttp://www.sqlservercentral.com/Forums/Topic1248389-146-1.aspxHi everyone,Lets say I schedule a maintenance plan X to run every 2 minutes.Lets say the first time the related job is run, it last 5 minutes before ending.What will happen the next two time this job is supposed to run since the first occurence is still running? Will I have many occurences of this job running simultaneously?Best regards.CarlTue, 07 Feb 2012 11:46:45 GMTCarl B.http://www.sqlservercentral.com/Forums/Topic1247492-146-1.aspxHey everyone, need some help here.We have 2 servers. Server A and Server B. These 2 servers are setup in a automatic failover H.A. mirroring configuration with database "FraggleRockKids". Both servers are completely different machines. They are in the same domain. SQL Authentication is used for mirroring and database failovers. Two weeks ago, we had an unplanned failover of the database. Everything appeared to have worked correctly and for a week, everyone was happy. As it currently stands the Principal is on Server A nad the Mirrored database is on Server B. Now, since SQL Agent jobs are not mirrored, we have the same agent jobs running on each server and the job does a check to see if the FraggleRockKids database is Online or in a Mirrored State to ensure that we don't get all those lovely errors for the database not being accessable. However, last week we started getting calles from customers complaining of issues that were tied to the SQL Agent Jobs and on the Server A, specifically some of the larger daily automatted processes for generating reports and such were deadlocking or reporting bad data. Come to find out that the SQL Agent Jobs on Server B, which is where the Mirrored database currently resides, appeared to be running their jobs on Server A. Now, I had a very difficult time believing this as I didn't think it was possible. So I created some of my own tests.I created a table on the FraggleRockKids Principal database that would store a NVCHAR(1000) and a DATETIME. Basically a message and timestamp. I did the same inside the master database on Server A and B. I then created a job on Server A and a Job on Server B. Both jobs have a default database pointing to the mirrored database in question. The job INSERTS into each of the tables above. One inside the Principal database and one inside the master database. I did not setup any check to see if the database was online, so it should throw an error on Server B where the mirror resides, when it tries to insert into the table. My insert contains a message stating which server I am on (principal/mirror) and a concatination of the @@ServerName. What I found is that rather than getting an error on Server B as I was expecting, I actually get 2 records in the table I created inside of the principal database showing the messages from each of the servers. Additionally, the table in the master database on Server A has 2 records, 1 from each server. The table in Server B has 0 records. Needless to say, I was a bit shocked. To confirm that this was indeed happening, I setup a profile trace where in I only looked for connectsion to Server A from Server B. Guess what, My code is captured as running on Server A.So my first fear is that everything on the server is doing this. This isn't the case. When I create a job that points directly to the master database on Server B, I get a record in the test table as expected. So it appears that for some reason, any job that is running on Server B that is pointing directly to the FraggleRockKid database that is in a mirrored state is running successfully on Server A.Does anyone have any idea on what is going on here. I was under the impression that SQL Agent jobs can't run cross server and can't be mirrored. However, it very much appears that it is happening here. Thoughts, suggestions, requests for additional information, anything?Thanks,FraggleMon, 06 Feb 2012 09:30:17 GMTFraggle-805517http://www.sqlservercentral.com/Forums/Topic1248030-146-1.aspxHi, In production server I have a table which has a text field. There was an application upgrade recently and we include this table in replication. But due to this replication, our application is throwing an error as below'Msg 7137, Level 16, State 1, Procedure usp_%, Line 7UPDATETEXT is not allowed because the column is being processed by a concurrent snapshot and is being replicated to a non-SQL Server Subscriber or Published in a publication allowing Data Transformation Services (DTS).'In the properties of the table we have the column as 1. Not for replication.2. Replicated as 'Yes'3. Sql Server Subscriber 'Yes'We have removed the table from replication but still cannot create a procedure which uses updatetext.Please can someone help in this .This is on our production server .Thanks,HarshaTue, 07 Feb 2012 05:50:31 GMTSQL_DBA_Newhttp://www.sqlservercentral.com/Forums/Topic583656-146-1.aspxI had a massive outage a couple weeks ago on SQL 2005 x64 sp2 running on 64 bit Clustered SQL 2005 sp2 on Windows 2003 R2 x64 sp2.Long story short, I had to use the ClusterRecovery.exe utility to restore a checkpoint registry to get the 3 instances of SQL I have on the server up and running.After a long and frustrating day I got the 3 instances up and running and the SQL Server Browser service working, so the clients could connect and all the apps were working. It's been a few weeks now and the only issue seems to be something related to SQL's associated .exe's not being able to establish connectivity to the server. The Maintenance Plans (using sqlmaint.exe) and Database Mail (using DatabaseMail90.exe) no longer work.The error I get from the failed Maintenance Plans (and I have created new, very simple plans for testing/verifying the failure) is:MessageExecuted as user: AMC\mssql. ... Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 4:49:16 PM Error: 2008-10-09 16:49:33.48 Code: 0xC00291EC Source: {230CB310-C98D-44D1-A40D-280DE97DE0EB} Execute SQL Task Description: Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Warning: 2008-10-09 16:49:33.48 Code: 0x80019002 Source: OnPreExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning Error: 2008-10-09 16:49:48.06 Code: 0xC0024104 Source: Back Up Database Task ... The package execution fa... The step failed.I know that the account has the right permissions (the maintenance plans ran fine before the outtage), it is an admin on the box and the SQL Agent runs under that account and other jobs all work fine (I have manually scripted the backups until I can get this issue resolved). I have restarted all services and rebooted boxes numerous times and the error remains. If I create a maintenance plan with a connection ot another server, I can see the databases on the other server while creating the plan, but get the same error.If I go to another server and create a maintenance plan to run against the one that's failing, that works fine, so it looks like the issue is confined to just the SQL cluster and the .exe's for SQL that run on that server, anyone have any insight on what might be causing this??Thanks,ChrisThu, 09 Oct 2008 17:23:20 GMTChris Mackin-166947http://www.sqlservercentral.com/Forums/Topic1248017-146-1.aspxI am a DBA, my quy gave one query to execute , i have run that query it taken 54 seconds, then i anay clustered index scan 46% ? what is meaning ? ThanksTue, 07 Feb 2012 05:37:59 GMTsolomon.jernashttp://www.sqlservercentral.com/Forums/Topic1247599-146-1.aspxI have two transaction logs for my main production database. one is 95 gigs and the other is 10gig I would like to remove the 10gig. What would be the safest way to do this?Mon, 06 Feb 2012 11:48:39 GMTtwdavis-893252http://www.sqlservercentral.com/Forums/Topic1247850-146-1.aspxLoshipping all Restore job faiedI don't know why it happen.All the backup and copy jobs done successfully but restore job failedplease giv suggestion........Tue, 07 Feb 2012 00:15:36 GMTsunilkumar.patra92http://www.sqlservercentral.com/Forums/Topic533184-146-1.aspxhello all.i have sql server enterprise 2005 on cluster server.my windows user is sysadmin on the sql server, when am trying to use sql configuration manager i get "access denied wmi provider"also when i login as the sql server account am still getting the same msg.can u tell me whats wrong? and how to fix this.Sun, 13 Jul 2008 13:53:18 GMTmobashahttp://www.sqlservercentral.com/Forums/Topic1248010-146-1.aspxExperts,SQL Server 2005 Analysis Service.A connection cannot be made to redirector analysis services. Ensure that 'SQL Browser' service is running.(Microsoft.AnalysisServices.AdomdClient).No connection could be made because the target machine actively refused it IP:2382 (System)Browser Service is running for sure.Anlysis Service is running under "Local System" account only. Searched in google, Reinstalled. But nothing works.Can anyone help please.Thanks in advance,Smith.Tue, 07 Feb 2012 05:26:16 GMTJoy Smith San