Tag Info

New answers tagged

1

I know this is an older post, and I have personally had the same problem. Managing multiple SQL agent jobs across multiple instances, so I decided to create SQL Agent Insight, found here > http://brentec.ca. It is an evolving product and will get out of beta in the near future and it is continually being updated and suggestions are welcome. This is a project ...


0

to grant use privliges to create database you can use the following command: use [master] GO GRANT CREATE ANY DATABASE TO [LoginName] GO To drop it you need to make sure the user has owner privileges on the database, you can use the command ALTER AUTHORIZATION ON DATABASE::db TO [LoginName];


1

I found this answer on mssqltips and this is the link to the article I found there. I am also posting the TSQL for possible link-rot in future. T-SQL Script to Drop All SQL Server Indexes DECLARE @SchemaName VARCHAR(256)DECLARE @TableName VARCHAR(256) DECLARE @IndexName VARCHAR(256) DECLARE @TSQLDropIndex VARCHAR(MAX) DECLARE CursorIndexes CURSOR FOR ...


0

If you want the same result on both systems, your choices are: If you always want to know the server-level Windows account in use, change your code to use ORIGINAL_LOGIN() in both places instead of relying on database mapping (or use SUSER_SNAME()). Add your login to the sysadmin role on the production system. Create a new login on your test system (or at ...


0

CURRENT_USER will give you the name of the current security context., which if you are member of the db_owner group is dbo. If you want your NT login to be displayed try using SYSTEM_USER or SUSER_NAME() instead: SELECT SYSTEM_USER, SUSER_NAME(), CURRENT_USER CPSDOM\mconnors| CPSDOM\mconnors| dbo


0

There is the possibility that you are getting lock escalations onto the table level, or page locks on the clustered index page. A deadlock graph will show that, try starting a SQL Server trace grabbing just the Deadlock graphs from the server and check those You can also delete in batches which will improve concurrency and if the delete is handling large ...


-1

please check user rights for that drive. May be problem is with access rights for particular drive. Give full rights for that drive with 'everyone' user. Your problem will sort out. Restart Sql server instance and check database status, your recovery pending error is solved. Thanks


-2

you can also run to this query to get accurate details of when was the table last updated. If WHERE condition is entirely removed it will provide details of the entire database. SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,* FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID( 'YourDatabaseName') AND ...


0

You can use something like SELECT [name] ,create_date ,modify_date FROM sys.tables you can add a WHERE clause to limit the results to only those changes that have occurred in the past week, month, year, whatever. Something like SELECT [name] ,create_date ,modify_date FROM sys.views WHERE ...


1

In real world environments the situations where a keyword is encountered and then removed and will never be encountered again is quite low. The process for updating a record in the full text index process probably removes all previous references to the record then updates all new references. As such when the references to a keyword are removed it does not ...


1

One risk mitigation is to use a signed stored procedure. Here's an example of this in action. In summary, you package all the sensitive action (table access etc.) inside a stored procedure. You create a certificate in SQL server to sign that SP. You create a new login who's only ability is to execute the SP and you send the password to this low-privilege ...


1

Frankly, I wouldn't use either. You can find your biggest tables immediately - with more flexibility - and not worry about where @updateusage has been set. CREATE PROCEDURE dbo.TopTables @NumberOfObjects INT = 100, @MinimumSizeInMB INT = 10 AS BEGIN SET NOCOUNT ON; SELECT TOP (@NumberOfObjects) [object] = QUOTENAME(s.name) + N'.' + ...


0

This post might give you an alternative approach. I have found sys.database_files is pretty much reliable. http://stackoverflow.com/questions/9630279/listing-information-about-all-database-files-in-sql-server Select DB_NAME() AS [DatabaseName], Name, physical_name, Cast(Cast(Round(cast(size as decimal) * 8.0/1024.0,2) as ...


3

You need to have a distinction between varchar and nvarchar. There is a big difference. If I try to create varbinary from your "should work out to" value: SELECT CONVERT(VARBINARY(64), '201407240005688309') I get this value, which looks kind of like the value you're converting, but not quite: 0x323031343037323430303035363838333039 So now if I change ...


2

@AaronBertrand's answer is great. Here's a variation with a bit more info from here SELECT (physical_memory_in_use_kb/1024) AS Memory_usedby_Sqlserver_MB, (locked_page_allocations_kb/1024) AS Locked_pages_used_Sqlserver_MB, (total_virtual_address_space_kb/1024) AS Total_VAS_in_MB, process_physical_memory_low, process_virtual_memory_low FROM ...


1

How about a rewrite of it? ;with eMK_Mileage AS (SELECT 15755 AS ID, '8E79F' AS FK_CarID, 69646 AS KM , '20140716' AS KM_Date UNION ALL SELECT 15756,'8E79F' , 70393 , '20140726' UNION ALL SELECT 15757,'8E79F' , 58258 , '20140822' UNION ALL SELECT 15758,'8E79F' , 72055 , '20140822') ,FullList AS ( SELECT * FROM eMK_Mileage as Mileage ) -- FINAL SELECT ...


0

You cannot upgrade from SQL Server 2008 SP4 to SQL Server 2014 evaluation edition. Its not supported and wont allow you please see Supported Version and edition upgarde You should not worry much about upgrading from 2008 Sp4 to 2014. The upgrade would not be a much of problem but how would application behave is thing on which you should focus. You can ...


0

You've got corruption and DBCC CHECKDB is recommending you run DBCC CHECKDB (Database, REPAIR_ALLOW_DATA_LOSS) as noted on the third line from the bottom of your error text. That being said, you may want to try REPAIR_REBUILD first since it's less intrusive than REPAIR_ALLOW_DATA_LOSS--for which you may lose data. '...ID 241435934). ...


0

I think I got it..... SET hdIssues.IssueDate = COALESCE(DATEADD(day,@NumberOfDays,hdIssues.IssueDate),null), hdIssues.StartDate = COALESCE( DATEADD(day,@NumberOfDays,hdIssues.StartDate ),null), hdIssues.ResolvedDate = COALESCE(DATEADD(day,@NumberOfDays,hdIssues.ResolvedDate ),null), hdIssues.LastUpdated = COALESCE( ...


0

Try something like this: -Install SQL 2012 feature pack to get invoke-sqlcmd or change it to use sqlcmd. -You can just pass in the raw TSQL but I would turn it into a stored procedure and just pass that in. Something like: CREATE PROCEDURE [GetDeleteDate] AS select ArquivoServidor from tblExchange where DATEDIFF(day,LogDownload,GETDATE())>= 7 or ...


0

I believe the log file is seeing that large size because during the backup from production the size there itself was 3.6 GB approx. As mentioned below by experts, excluding the part of my answer recommending a fix size. Therefore,to analyse more on how to decide the size you may want to keep, check on the autogrow size of data and log file: select ...


0

Do you actually need to persist the prev and next columns in the data? Why not use Window functions on retrieval of data to get what you need from your base table? I've made a bit of a guess on what your schema looks like in it's most basic form but this should help you. If not post CREATE statements for all tables involved and I'll edit the answer. SELECT ...


-2

SELECT s.name, t.name FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id] WHERE t.name LIKE 'LG_001%'; Run above query and save results into a csv. Then open that CSV in a notepad. Then do Ctrl+H to replace schema with DROP TABLE SCHEMA that will give you all the drop queries, copy and paste this big sql into your ...


2

Since the two parts are identical except for the WHERE conditions where the first is just a restriction of the second, you can use one query, with the 2nd condition and an altered condition for the 1st sum, i.e. a CASE expression: SELECT T3.AcctCode, T3.AcctName, SUM(CASE WHEN T0.TaxDate BETWEEN '2014-09-01' AND '2014-09-30' THEN ...


0

In case this helps anyone. I had similar trouble with a native format BCP export/import of a table containing DATE data type (new in SQL Server 2008). The fix required use of a generated format file (format nul -x) and using -f rather than -n on both the export and import. Even though the schema for both the export and the import were identical there was a ...


0

Assuming your full backups are always done WITH INIT: SELECT TOP (1) @disk = f.physical_device_name FROM msdb.dbo.backupmediafamily AS f INNER JOIN msdb.dbo.backupset AS s ON f.media_set_id = s.media_set_id WHERE s.database_name = @db AND s.[type] = N'D' ORDER BY backup_finish_date;


1

The desired output can be achieved using a union of recursive joins on the same table. It's a bit ugly, but the following query will do it. The first SELECT returns Purchase transactions, the second SELECT returns Redemption transactions, and the third SELECT returns the rows that aren't part of a transaction pair. SELECT fact.[System] ...


1

You have to use the GUI management tool to create a script and then run that - you cannot do it directly - take a look here (or here) for relatively clear explanations.


2

You will want to use dynamic SQL to change the DB you are executing the query in: DECLARE @dbname AS VARCHAR(100) DECLARE @query AS VARCHAR(MAX) SELECT @dbname = name FROM sys.databases d WHERE /*enter criteria here to get DB*/ SET @query = ' use ' + @dbname + ' /*enter your query here*/ ' PRINT @query EXEC(@query)


0

didn't know your table name so I created my own name. change the table name and it should work. This gives the difference in minutes. select A.rec_number,A.tank, CONVERT(varchar(5), DATEADD(minute, datediff (minute,A.start_ts,B.end_ts), 0), 114) as differenceTime from ( select a.rec_number,a.tank, start_ts from dbo.TEST a inner join( select ...


2

Make this your SQL Statement. select * from tbldatasets where dataset = 104 and ADDEDTIMESTAMP BETWEEN Convert(datetime, DateAdd(month, -2, Convert(date, GETUTCDATE()))) AND GETUTCDATE() UNION ALL select * from tbldatasets where dataset = 106 and ADDEDTIMESTAMP BETWEEN Convert(datetime, DateAdd(DAY, -10, Convert(date, GETUTCDATE()))) AND GETUTCDATE() ...


-4

For tutorial you can watch the video series on youtube. https://www.youtube.com/watch?v=3cPq9FXk-RA&list=PLNIs-AWhQzcmPg_uV2BZi_KRG4LKs6cRs For query you have to union your queries into a single resultset.


1

I ended up writing a tool myself. It's available for free download -http://www.gitsql.net I hope it helps other people who want to achieve the same end goal. Here is an article which describes how to source control SQL Server. http://gitsql.net/documentation-04_SQL_Server_and_GIT I've tried to make it as easy as possible. (3 screens) Connect to SQL ...


0

;WITH cteSample AS ( SELECT 'ZB01' AS EmployeeID,'2014-09-22 08:01:01.000' AS DateAndTime, 1 AS [Type],0 AS AbsenceID ,'2014-09-22 08:01:21.245' AS DateEntered,'2014-09-22 08:01:21.245' AS LastModifiedDate , 443 AS [MINUTES] ,30 AS DN UNION ALL SELECT 'ZB01','2014-09-22 16:24:01.000',2,0,'2014-09-22 16:24:37.522','2014-09-22 16:24:37.522',443,30 UNION ...


0

There is a way to do this. Assume that, like the OP, you have two SQL Server instances, one of which has SSIS installed and one of which does not (probably cannot, e.g. if it is SQL Server Web Edition). Write a stored procedure which copies the user maintenance plan rows from the hobbled server to the non-hobbled one. The relevant rows are: SELECT name ...


0

Was trying to solve the same problem and stumbled here... It's a year late, but better late than never... OSX uses iODBC as ODBC layer, so FreeTDS must be compiled against iODBC for MySQL Workbench to connect. Since I struggled with iODBC as well, what I ended up doing was to create a custom "library installation" to run this. Steps are as follows: ...


2

Would you like to try this? DECLARE @cmd1 nvarchar(2000) SET @cmd1 = 'IF ''?'' NOT IN(''Name of your database'')' + 'BEGIN ' + 'drop DATABASE [?] ' + 'END' EXEC sp_MSForEachdb @command1 = @cmd1 GO it worked here. PAY ATTENTION TO NOT DROP MASTER AND ETC. Put then on that list.


3

How about something like below : change your output in SSMS to text and once you are happy run the generated sql statements in another query window : use master go select 'Alter database '+ quotename(name) + char(10) +'set single user with rollback immediate' +char(10)+ 'go'+ char(10)+ 'Drop database '+quotename(name) + char(10)+ 'go' from ...


2

After setting the db to singleuser you have to switch back to master. I added a use master statement but I don't want to test this.... EXEC sp_MSforeachdb ' USE [?] IF (DB_ID(''?'') > 4 AND DB_NAME()!=''ABC'') BEGIN ALTER DATABASE ''?'' SET SINGLE_USER WITH ROLLBACK IMMEDIATE **use master;** DROP DATABASE ''?'' END


0

I do not see this as the most desirable implementation. Using a Foreign Key constraint to enforce correctness is (in my opinion) much better than writing code to handle such issues. However, regarding your question, Yes, TableA can have both an INSTEAD OF trigger and an AFTER trigger. (In fact it can have several AFTER triggers.) ...


1

Instead of having the trigger rely on the linked server being available, use a queue table. Have a background job that can use sp_testlinkedserver and process all of the items in the queue only when the server is available. It can use TRY/CATCH as well, and only mark those as processed when they are successfully updated, otherwise it will continue trying ...


3

Unless an index is filtered, they should all be written to the same number of times under normal DML activity. If a row is updated or added, any columns in any index must also be added/updated. So you should not expect some indexes to be written to a different number of times, unless you are reorganizing or rebuilding individual indexes specifically. ...


0

From SQL 2012 onwards, single_pages_kb got replaced by pages_kb in that DMV. https://msdn.microsoft.com/en-us/library/ms175019.aspx?f=255&MSPPError=-2147217396 So if you want to run the query included in the question, on a 2012+ server, remove the single_ string.


0

About CMS versioning; for drupal it makes a special table for every field of the entity that stores the old value ; such a concept alllows you a fine manipulation of your data but i think it's expensive, my own solution is to convert my object to xml format and store it as string with the other fields( changetime, id...)


0

in order to release the space you first need to change the recovery model from full to simple, then you can release the space as you have mentioned.After it is finished you can revert from simple to full anyway this is a temporary fix.. the log will eventually grow again sooner or later..


0

@Fulproof: Based on what @RicardoBalda says (which all works as advertised), it seems clear that the vast majority of SSIS is installed and used by SQL Server Maintenance Plans, even if SSIS is not officially 'installed' as such, on a given server. (Which might sway someone's decision as to the perceived overhead of installing the rest of SSIS, to get at ...


1

I have used 2 tools to perform Oracle to SQL data migrations: The first is the Oracle to SQL Server Migration Assistant (SSMA). It is a fairly basic tool and if you don't have any binary objects to migrate it is ok. It allows you to create the schema and migrate the data, but it isn't the fastest tool in the world and the OLE drivers wont work with binary ...


2

You can connect to Oracle from SSIS. You have to install Oracle client on your development and also on your production environments. After doing proper tnsnames configurations you can use odbc source or ado.net source.


1

The Snapshot Agent process account used to connect to the Distributor must be db_owner in the distribution database and have read, write, and modify permissions on the snapshot share. The account used to connect to the Publisher must be db_owner in the publication database. The Log Reader Agent process account used to connect to the Distributor must be ...



Top 50 recent answers are included