Tag Info

New answers tagged

0

just enter select db_name(),name from sys.tables; Copy and paste the list.


3

There are a couple of possibilities. First if the orphaned user is a windows login/group (type U or G) then no problem. -- If the login doesn't currently exist on the server CREATE LOGIN [Windows Account] FROM WINDOWS; -- Fix the user USE dbname ALTER USER [user name] WITH LOGIN = [Windows Account]; If it's a "SQL Login" (type S) and the login exists ...


0

I had run CheckDB against a 32GB db, and got this error. There were plenty space on the HD. So I increased the size of the tempdb log file to 8MB from 1MB, and closed some applications. Reran CheckDB, and it worked.


4

A non-clustered index scan may be chosen in this scenario: the optimizer determines that it is cheaper to scan all rows rather than perform seeks/range scans the non-clustered index is "skinnier" than the clustered index the non-clustered still covers the columns needed by the query (or it covers enough of them and a lookup for the remainder is still ...


1

A restore failing on the secondary will have no impact on the primary. You want to monitor your secondary DBs to make sure this hasn't happened.


1

You can calculate a running total in SQL 2008 R2 by using the ROW_NUMBER() window function along with a correlated sub-query to calculate the running total column. Here is an example using some data I have available. You would need to amend the query accordingly to suit your tables/data and would likely need to use a PARTITION BY within the ROW_NUMBER ...


1

The link provided by Martin Smith to an existing SO question provided me with the answer: The main restrictions are that foreign key relationships must be based on a single key to contribute to the simplification process


0

It has a very easy way to have everything on one page! Simply right click on the diagram area, and choose "Copy Diagram to Clipboard"! Then you can paste it in Paint and save it as any picture type you want...


2

From SQL 2012 onwards, there is the WITH RESULT SETS option, which makes it easy to use the output from the OUTPUT clause with no sticky issues in SSIS not understanding the metadata for the resultset, eg you can use the output from an OUTPUT clause in SSIS Source components, eg OLE DB Source. I set up a simple example using a stored procedure with MERGE ...


4

This happens because the msdb log file name has been changed from the default which is "MSDBLog". In my case, it was called "msdb_log". The name "MSDBLog" is hard-coded into the upgrade script so it fails if the name has been changed. To fix the problem, we need to rename the log file to 'MSDBLog'. We have to start SQL with trace flag 902 to stop it from ...


0

It turns out this issue was due to a restore of a database from an incorrect media set. Since the databases were not exact copies, the package was performing differently on the second instance. After performing a backup with INIT and then restoring that database to the second instance, the package now performs the same on both instances.


5

You can't, ish, is the short answer. You have 3 ish components in a data flow task that can modify data. OLE DB/ADO.NET Destination, OLE DB Command and a Script Component. I'm ignoring Script Component for this answer as it's the swiss army knife of components. If you want to do something badly enough, the Script Component will allow you to. Whether ...


-1

I am running 2008 R2 with 46GB of Ram. No virtual machines. SQL Server 2008. Databases are about 300GB. I put the databases on a solid state drive recently and tripled my data output. The server is currently using 45GB of Ram and running good. FC Sata Raids and SAS SCSI Raids. 26 logical drives in all. 144 Spinning disks. Yesterday I was only using 24GB ...


0

I think during installation, you have missed to select the Management Tools-Complete from shared features list. You can open SQL Server Installation Center again New installation or add features to an existing installation After afew screens on "SQL Server Feature Installation" Check "Shared Features -> Management Tools-Complete" Follow the rest ...


0

Probably you want to try PowerShell scripting for this. Previous discussion here Example


1

Just looking at your definitions (thank you very much for that) I see: No index on to support WHERE dbo.EntryTypes.EntryType = 'Error'. (But this may not be needed if it is a small table with just a few entries.) Your fk_* columns in MAIN have check constraints, but no indexes. You should create some indexes since they are used to join to several ...


3

Is it possible to restore this to a different file group No. possibly even on a different database) No.


0

I know that Red Gate SQL compare has a command line interface so you could write and schedule a batch file to generate the scripts. Details here You could then use SQLcmd to execute the scripts.


1

The following code will restore the database to the desired location: RESTORE DATABASE x FROM DISK = 'Filepathofbackupfile.bak' WITH RECOVERY, MOVE 'logicalDatafilename' TO 'Newfilepath.mdf', MOVE 'logicalLogfilename' TO 'Newfilepath.ldf'


0

There are a variety of ways you can do this. If you only need a portion of the database, you can have copies of the tables which you refresh on the destinations periodically. The idea is that you have your users reading one copy of the data, then you have an identical table in a different schema, and you reload that copy while the users are reading. Then in ...


2

If you are going to use custom collations for specific databases then yes, you'll need to make the collations match whenever you are joining or unioning data from the two databases. In fact you will need to do this with many metadata queries anyway. Just look at catalog views like sys.tables: SELECT c.name, c.collation_name FROM sys.all_columns AS c INNER ...


1

From Books Online database_default - Causes the COLLATE clause to inherit the collation of the current database. If you are executing your second query from the common database then the value from master.dbo.sysdatabases is being coerced into common's collation, not the other way around as you suppose.


0

Try simply restarting the SQL Server Agent and testing again. Your results from sysjobhistory lead me to believe that isn't because of a PowerShell error. You have a success record but you also have an in progress record. I've seen the SQL Agent act very odd sometimes and restarting it was the only thing I've figured out to fix it. For example, I've had jobs ...


1

To improve performance you may create a copy of data from server A on server C and update it on a schedule or use replication, if possible. Sometimes it works, if you don't need too 'live' data. With OPENQUERY whole dataset is transferred each time the query is executed.


3

object_id() is returning a value fine; the problem is that object_name() (which is not the documentation you're looking at) by default looks in sys.objects for the current database context. Don't know why you would need to use object_name() here when you obviously already know the name (#t). Or are you trying to get #t___________acde4521? What exactly are ...


4

You have to tell object_name that it needs to look in the metadata of tempdb. select object_name(object_id('tempdb..#t'), db_id('tempdb')) as from_beta1


5

Another approach, which still uses dynamic SQL but no ugly cursor scaffolding (and allows you to examine the step which failed and the error number generated, without bubbling the error to the caller): DECLARE @step INT = 0, @result INT = 0, @sql NVARCHAR(MAX) = N''; DECLARE @tbl TABLE([step] INT PRIMARY KEY, [pname] NVARCHAR(513)); INSERT ...


4

If you don't need the result values later you can do it shorter this way: -- procedures to test with create proc proc1 as print '1' return 0 GO create proc proc2 as print '2' return 1 GO create proc proc3 as print '3' return 0 GO if object_id('dbo.testproc') is null exec('create procedure dbo.testproc as return(0)') GO alter PROCEDURE dbo.testproc AS ...


-1

Try using exec sp_executesql @sql,'@result int output', @result=@result output. This way you can pass variables in and out to your dynamic query.


1

You can't go wrong with Ola Hallengren's award winning solution. It's pretty awesome ; https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html You can schedule this to run once a day to begin with, and then you should re-evaluate your needs based on how busy the solution actually becomes regarding the needed index maintenance on your ...


1

I would suggest that you use Ola Hallengren's scripts, freely available and widely used. His website is: https://ola.hallengren.com/ •DatabaseBackup: SQL Server Backup •DatabaseIntegrityCheck: SQL Server Integrity Check •IndexOptimize: SQL Server Index and Statistics Maintenance He has the best practices coded into his scripts, so it should serve you ...


5

Don't re-invent the wheel; just get a solution like Ola's in place now: Ola Hallengren's SQL Server Backup, Integrity Check, and Index and Statistics Maintenance You can tweak the settings and learn how it works over time.


2

You can't create an Indexed view on linked server table. According to BOL http://msdn.microsoft.com/en-us/library/ms191432.aspx "The view must reference only base tables that are in the same database as the view."


2

Violation of PRIMARY KEY constraint 'PK__backupse__21F79AAB0E391C95'. Cannot insert duplicate key in object 'dbo.backupset'. The duplicate key value is (60979). This might happen if someone have done a reseed on the dbo.backupset. From table definition : PK__backupse* --> clustered, unique, primary key located on PRIMARY --> backup_set_id To fix ...


-1

Good day to all, In my case I did an IP address change on a production server due to a migration to a better server (more memory, SSD, several cores, etc..). When the change was applied none of the users or myself were able to connect remotely; The way I fixed it was by adding an exception on the firewall for inbound/outbound connections under port 1433 and ...


0

Do you have actually a big difference in running time two different plans? Optimizer just choses cheaper plan based on estimated number of rows for CostType. In case of serial plan it's >5 times bigger. That could be really caused by the fact you've added more records to the CostType table or your stats is stale. As a result, you have table spool which is ...


0

20,000/60 = 333 records per minute is not that rate where you worry about inserts, even if they are burst. Of course it depends on your hardware, but since your table is rather huge, you need something big. So I would defenitely create 1 or 2 indexes (you can omit clustered) to speed up your queries. At a higher insert rate you probably go with partitioning ...


0

How is it possible different databases are using different query plans? It all depends on the options, statistics, queries, indexes, etc that are setup. I couldn't begin to explain to you. What I would recommend you do is if you know the plan it should use, you can give query hints (usually a bad idea) or you can rewrite your query. First of all, you do ...


0

Looks to be a post-completion check failure as there is no evidence elsewhere stating otherwise.


1

Database mirroring involves two copies of a single database that reside on different computers. At any given time, only one copy of the database is available to clients. This copy is known as the principal database. Unlike log shipping which works by applying full transaction log backups to a warm standby database, database mirroring works by transferring ...


1

select * from dbo.Sales s cross apply ( select top (1) * from dbo.PriceHistory ph where ph.itemId = s.ItemId and ph.Date <= s.Date order by ph.Date desc ) ca;


1

Even though you can use xp_cmdshell to copy the directory, I would not recommend to do it using SQL Server. You were right in thinking of using PowerShell and/or robocopy to copy files or directory to wherever you want. Powershell will even allow you to send email once the task is completed. Currently, in my company, we use native sql server backups with ...


2

For the sake of completeness, you can inspect the contents of your backup file to determine the appropriate FILE=x parameter using the following T-SQL: RESTORE HEADERONLY FROM DISK = N'G:\BI_Staging.bak'; This will return some pertinent fields (among many others), such as: BackupStartDate - you can use this to determine the most recent backup stored ...


11

You've used NOINIT in your backup, and then FILE = 1 in your restore. So it seems like you have multiple backups in the .bak file, and you aren't pulling the right one for the restore. In fact you can see that in your RESTORE VERIFYONLY script you specify the FILE = @backupSetId but in the actual restore you just hard-code FILE = 1. This is one of many ...


0

You are grouping by b.Start_Date and for each row aggregate MAX(B.Start_Date) always the same as b.Start_Date. Just replace b.Start_Date with a.Start_Date SELECT a.JobCode ,MAX(b.ID) ID ,MAX(B.Start_Date) Start_Date ,a.EndDate ,a.Start_Date FROM [Stage].[dbo].[JobCode] a INNER JOIN [operations].[dbo].[Job_Code] b ON a.JobCode = ...


2

You can index a view that contains an XML column. You cannot index a view that contains any of the XML datatype methods ( eg .nodes, .query, .value, .exist ). I would say your greatest issue is always casting the VARCHAR column to XML. Is there are a particular reason you are not using the XML datatype? Although it can alter XML slightly ( eg whitespace ...


1

You can do some limited adjustments to the schema with ALTER XML SCHEMA COLLECTION. Here's a simple example: USE tempdb GO IF OBJECT_ID('dbo.users') IS NOT NULL DROP TABLE dbo.users GO IF EXISTS ( SELECT * FROM sys.xml_schema_collections WHERE name = N'xsd_test') DROP XML SCHEMA COLLECTION xsd_test GO -- Create a simple schema CREATE XML SCHEMA ...


0

We've just released our Database Enforced Change Management solution to MS-SQL. Yes - I'm biased as I work in DBmaestro I believe our product answer your needs and I would encourage you to read a comprehensive, unbiased review on Database Enforced Management Solution by veteran Database expert Ben Taylor which he posted on LinkedIn ...


1

Instead of restoring a daily full backup, you could: Take weekly full backups of production, restoring them to the D-1 server the next day. Take daily differential backups of production, restoring them to the D-1 server the following day. The daily restores from the differential backup will be much faster than restoring the entire database each day. I'd ...


0

Does it need to be done in real time? I would engineer the solution differently. The client uses a stored procedure to write to a queue table with what needs to be compressed. A script running on the server periodically checks the queue table and zips up everything that needs to be zipped up, marks it as complete and does whatever else needs to be done.



Top 50 recent answers are included