Tag Info

New answers tagged

3

If I understand what you are asking then like this. WHERE DateModified BETWEEN @BeginDate AND @EndDate AND (@Troop IS NULL OR d.CreatorLEA = @Troop)


0

I'm not sure how you what the output to display this. DECLARE @vtExp TABLE ( ACCCODE CHAR(5) NOT NULL, ACCName VARCHAR(50) NOT NULL, ACCParentCode CHAR(50) NULL, ACCExpense FLOAT NULL ) INSERT INTO @vtExp (ACCCODE,ACCName,ACCParentCode,ACCExpense) VALUES('00000','Account 00000',NULL,0) ,('00001','Account 00001','00000',0) ...


1

No. Committed transactions modify the contents of the database tables first in the transaction log, then in the data files. Unless triggers or other techniques are explicitly set up upfront, there is no way to recover the values stored in the database before the transaction was committed. In order to do that, you have to restore a copy of the database ...


1

sorry to hear that. Well if u havent trimmed transaction log you can do it. Nice articles how to achieve it: http://www.mssqltips.com/sqlservertip/3160/recover-deleted-sql-server-data-and-tables-with-the-help-of-transaction-log-and-lsns/ How do I rollback a dropped table using log?


2

SSIS code behind is actually a XML value and all components of package will be represented as XML node values within it.This XML can be parsed to identify if it has a reference for our searched table or column. Finding SSIS Packages having References to a Table or Column will help you.


1

Know this is an old question but we've just been working with this, Last month we had to move our distribution server to a brand new machine, (Setup is two machines in AG stand alone distributor and a reporting server) The only real way we found to move distributor server is to completely tear down and rebuild replication, this can be done with right ...


3

You can use the following approach in SQL Server to pivot the data into the format you are looking for. You may also be able to use the PIVOT operator, but I tend to prefer to write out the CASE statements since I find that syntax more clear (they have always resolved to the same query plan in all cases that I have checked). Updated answer based on Aaron ...


3

Here's one way. Assuming this table and sample data: USE tempdb; GO CREATE TABLE dbo.splunge(UserID CHAR(5), Name VARCHAR(32), Date DATE); INSERT dbo.splunge(UserID, Name, Date) VALUES('t0001','Tod','20150629'), ('t0001','Tod','20150629'),('t0001','Tod','20150629'),('t0001','Tod','20150629'), ...


1

I don't know if is equal on SQL Server, I did this in MySQL using the WEEKDAY function. Table UserRecords: Query: SELECT user, count(case when WEEKDAY(qDate)=6 THEN qDate END) as S, count(case when WEEKDAY(qDate)=0 THEN qDate END) as M, count(case when WEEKDAY(qDate)=1 THEN qDate END) as T, count(case when ...


0

I had the same problem. Eventually I worked out that I needed to change the db recovery model from "Full" to "Simple", and then I could delete my filestream files.


0

PARSENAME was mentioned as a solution if the string did not contain a period. If it does use this modification to change the periods to something else, then parse the value, and then put the periods back Select REPLACE(PARSENAME(REPLACE(REPLACE('Jacky_1_B2.00_23','.','~'), '_', '.'), 2),'~','.')


-1

It is quite easy. If your not firm with the tsql-commands, you can use Management Studio for this task. Right click on the database click on tasks backup your database to disc on the other instanz (or maybe the same) right click on databases select restore use the fresh backup file and restore it under a new name


0

Well I have some ideas which could cause this behavior. Do you monitor your memory pressure? Maybe your queries raise a certain limit which will cause the flush of the plan cache. I don't know your application, but does this correspondent with your logs from your frontend servers? Is there pressure too during this time? Do you have a dedicated SQL Server ...


0

I received this error because of another reason which I didn't see online, so I'm posting the problem and solution here. This can happen when trying to modify an xml column by inserting a very large text. For example... update MyTable set XmlColumn.modify(' insert Very large text here... after (/RootNode/Node)[1]') where Id = 1 To fix it, you can use ...


1

HeavenCore's reply answers the question. However there is another way to get around this without security flaws. Return output to a table, query the table with DBmail.


1

SQL does not allow to backup a database when in offline mode. You can also create maintenance plans in order to automate the job. So the first step of you job can be setting the offline databases to online mode and then the maintenance plan will do is work and then the third step will be to set your databases back to online.


2

I write Minion Backup and this kind of thing would be very easy to do. We allow you to run Pre and Post code before each DB, so for your SharePoint DB, you can just put it into single-user mode and change it back when it's done. However, we can't guarantee that you'll be the one to get that connection. But you can download Minion Backup here and give it a ...


1

Two things. First if you are going to do that then make your script this: ALTER DATABASE [TEST_1] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO USE [TEST_1] GO BACKUP DATABASE [TEST_1] TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\test\test_1.bak' WITH NOINIT, STATS = 10 GO ALTER DATABASE [TEST_1] SET MULTI_USER WITH ROLLBACK ...


-1

One thing to keep in mind is that a clustered index is your entire table. It includes every column. Dropping your existing clustered index to rebuild a new one is going to results in a lot of IO. With that in mind, the best way to make this go quicker is anything you can do to increase your IO speeds. Dropping nonclustered indexes and rebuilding those ...


2

It is not possible, because you cannot create a backup of an offline database. From MSDN: Without the NO_TRUNCATE option, the database must be in the ONLINE state. If the database is in the SUSPENDED state, you might be able to create a backup by specifying NO_TRUNCATE. But if the database is in the OFFLINE or EMERGENCY state, BACKUP is not ...


0

You'll simply have to build that format manually. SELECT t.TravelTime, t.TotalProductiveTime, TotalServiceTime = CONVERT( NVARCHAR( 16 ), FLOOR( ( t.TravelTime + t.TotalProductiveTime ) / 60.0 ) ) + N':' + CONVERT( NVARCHAR( 5 ), CONVERT( INTEGER, FLOOR( ( t.TravelTime + t.TotalProductiveTime ) % 60.0 ) ) ) ...


7

I doubt you would have a database in production whose name you do not know or not have documented it somewhere. If incase it happens in production, You could look up list of existing backups using RESTORE HEADERONLY FROM DISK = '<backuplocation>' Or use dbo.backupset from msdb. SELECT DISTINCT database_name FROM msdb.dbo.backupset


10

Right click on the database, go to files. You can see the original file names. This will help you to find the correct database name easily. The file names won't be changed on a rename. You can also try to take a look at your fn_dblog. It's undocumented but you can see (and filter) for the latest actions. SELECT * FROM fn_dblog(NULL,NULL)


4

You could look at the SQL Server Logs for the last time SQL Server started up and look out for each instance of 'Starting up database 'DBName'. You could then compare this list against the results of sys.databases. Any new databases and the one you changed will not be in the SQL Server log list. Another, perhaps better way would be to query the default ...


0

Have you considered using plan guides to freeze the better execution plan? After you've rebuilt your plan, you can freeze that plan in cache using plan guides. From MSDN: Plan guides let you optimize the performance of queries when you cannot or do not want to directly change the text of the actual query Here is a process that I often use to save ...


5

The order of the columns in indexes is critical. If your AdministratorUserId is the first column of the index, then separating the indexes will not improve anything. The index works like an order by clause, meaning that it is sorted by the first column, then the second, then the third and so on. Because of that, an Index on AdministratorUserId is ...


4

Run this script in your database: SELECT obj.*,usage.* FROM sys.dm_db_index_usage_stats AS usage INNER JOIN sys.indexes as obj ON usage.object_id = obj.object_id WHERE database_id = db_id() It will provide you with the index usage of your indexes. You can take a look (or filter on it) to see how often your index is used for scans or for seeks. ...


2

Check out my answer on what is probably a duplicate question: Find out beforehand how many records a query has There is a link in that answer to another answer I posted on a related question on Stack Overflow. So for the moment you have a few options, and I am researching another option as well.


2

You want to PIVOT the StoreName. Try something like this: SELECT * FROM ( SELECT Stock_TBL.StockID, Stock_TBL.ItemCode, Stock_TBL.ItemDescription, InvoiceItems_TBL.StoreName, InvoiceItems_TBL.Quantity FROM Stock_TBL INNER JOIN InvoiceItems_TBL ON Stock_TBL.StockID = InvoiceItems_TBL.StockID INNER JOIN Invoice_TBL ON ...


0

SQL Server will always use as much as memory as he can gets. Even on a server which is designed just for SQL Server, you should define a max memory usage on the instance. Otherwise your windows can run in a memory pressure if you try to connect to the machine as admin.


0

Can I restore msdb/master databases at different server as regular user databases and make sense of those mentioned queries? Couple of options you can use without restoring system database : GUI - Registered Servers PowerShell


2

Would this work. I just had the query give the totals for each year. You could easily filter by date if you wanted to restrict it for just this year, or a specific year you are looking at. Also, this assumes the final record in the table is an up time. If you end with the site being down, then that last downtime isn't included. You should get your site ...


0

--First Attempt SELECT Site, SiteStatus, datetime, timestarteddown = ( SELECT MAX(datetime) FROM [table] table1 WHERE table1.datetime < table.datetime ) INTO #Downtimes FROM table WHERE SiteStatus='Down' --This gets you your downtimes SELECT SUM(DATEDIFF(seconds,timestarteddown,datetime) FROM #Downtimes -- See if this works for ...


0

Here is a very simple solution. There is one, very important, assumption here. Which is, that your polling interval is static and never misses a beat. This is a limiting assumption which is why I'm calling this the simple solution. Code: DECLARE @SiteStats TABLE ([site] NVARCHAR(50), sitestatus NVARCHAR(4), [datetime] datetime) INSERT INTO @SiteStats ...


1

First observation is that your update statement has no WHERE clause. This means that it will update all rows of the table. Are you sure you want that? From the comments, it seems that all rows have year_period = 2016 and you do want to update all of them. In my opinion, it wouldn't hurt to explicitly add this condition in the statement. The opposite, it ...


1

Here is an example. Declare @PriceGroup TABLE ( id INT ,Catalog_Code INT ,Name VARCHAR(50) ) INSERT INTO @PriceGroup (id,Catalog_Code,Name) VALUES (1,1,'PriceGroup1'),(2,1,'PriceGroup2'),(3,1,'PriceGroup3') Declare @Price TABLE ( id INT ,articol_id INT ,catalog_code INT ...


1

I will try something like this: select substring(PostOfficeBox,1,4) as 'Postcode' , COUNT(CASE WHEN New_accounttype = 1 THEN New_AccountType ELSE NULL END) as 'New Connections' , COUNT(CASE WHEN New_accounttype = 2 THEN New_AccountType ELSE NULL END) as 'Domestic Metered' , COUNT(CASE WHEN New_accounttype = 3 THEN New_AccountType ELSE NULL END) ...


4

Skipping the backup altogether is not a good idea in my opinion. You should probably keep on taking differential backups instead. They will probably be very small and not a concern at all. Detecting the percentage of data that changed since last FULL backup can be done using this function by Paul Randal.


2

The values '0000-00-00' are causing this error. You could for example use NULL instead.


0

The path for .mdf and .ldf will be Root drive(where Os is installed )-> Programfiles(for 64bit applications) or programfiles(x86) for 32 but applications-> Microsoft SQL Server-> MSSQL11.SQL2012-> MSSQL-> DATA MSSQL11.SQL2012 This is for users having using SQL 2012... So you may easily recognize what's the folder name for you as you are using SQL ...


1

Even though this is a pretty old thread, Microsoft has officially published the list of Trace Flags that can be used when running SQL Server 2012 and up for high performance workloads : KB - 2964518 - Recommended updates and configuration options for SQL Server 2012 and SQL Server 2014 with high-performance workloads. Its highly advisable to do a thorough ...


2

If you mean that you are trying to restore a database backup from a more recent version, such as SQL Server 2008 R2, to a previous version such as SQL Server 2008, then you cannot do such a restore. You could restore a SQL Server 2008 database to a SQL Server 2008 R2, since the backup can be promoted to the new level. If you need to move the data to a ...


7

This is actually part of the DOUBLE PRECISION type, synonym of float(53). https://msdn.microsoft.com/en-us/library/ms173773.aspx


4

Yes, SQL Server can be configured to listen on multiple IPs and multiple ports. Read Configure the Database Engine to Listen on Multiple TCP Ports.


2

Adding to what @Aaron said : Where can I find logs that detail the cause of the failure? You have to have sql agent log output to a file as below or use T-SQL to find out from jobhistory table: Also, you should understand that there is a cost to reinventing the wheel, why not use existing - well tested worldwide Solution Ola's - SQL Server Index and ...


3

You should follow the rules for identifiers as documented in Books Online: The first character must be one of the following: A letter as defined by the Unicode Standard 3.2. The Unicode definition of letters includes Latin characters from a through z, from A through Z, and also letter characters from other languages. The underscore (_), at sign ...


3

SELECT [object] = QUOTENAME(s.name) + N'.' + QUOTENAME(t.name), [disabled_constraint] = c.name FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id INNER JOIN sys.check_constraints AS c ON t.object_id = c.parent_object_id WHERE c.is_disabled = 1;


3

Use the system views for this: select * from sys.check_constraints where is_disabled = 1;


-1

Another option is possible if you have a database backup readily available. (For example, you know that an automated backup process backs up the database to a file that you can access.) Take that backup and restore it to a SQL instance where you have sysadmin privileges. In my case I utilize a locally installed version of SQL Server Developer Edition. ...



Top 50 recent answers are included