Tag Info

New answers tagged

0

Try: WITH Numbered AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY UID ORDER BY UID) AS DupeNum FROM theTable ) UPDATE Numbered SET UID = 'DUPE' + CAST(DupeNum AS VARCHAR(10)) + UID WHERE DupeNum > 1;


1

Generally, downstream tools will reference your data in some kind of intermediate form, like SSAS cubes, QlikView QVD files, or simply CSV. But if your downstream tool is hitting the database directly, you can simply create a series of views, one for each roleplaying dimension. Each can be defined as SELECT * FROM Dates. Have Tableau and Excel reference ...


3

The problem is that you are missing a way to tie the outer table to the data inside of the STUFF. You can do this by adding a WHERE clause to the subquery: SELECT x.SurveyQuestionID, PossibleAnswers = STUFF(( SELECT ', ' + Answer FROM SurveyQuestionPossibleAnswer sqpa INNER JOIN SurveyQuestion sq ...


-1

It can be SQL related, not just OS and hardware: https://support.microsoft.com/en-us/kb/2491214


5

To answer your first question : If you are not sure, then just manually launch the setup.exe and verify. When you launch SQL Server 2008 installation using setup.exe, you will see the Below is for SQL Server 2008R2 : To answer your second question : From SQL Server 2008 and up, Microsoft has changed as the edition depends on the PID that can be ...


1

The logical file names returned by RESTORE HEADERONLY are those of the source database. You cannot change these logical names during the backup or restore. The backup file always contains the both the original logical and physical file names. The original logical names may be specified with the MOVE option of RESTORE to relocate/rename the physical files ...


0

If you run this: select dbname, AVG(usage), STDEV(usage) from <your table> group by dbname You'll notice that while the average is the same for both databases, the one you'd be interested is the one where the standard deviation is lower. So what you'd want to do is then filter this by a certain threshold average usage or higher and a certain ...


-1

on pluralsight.com Paul Randal has a course that I can recommend: http://www.pluralsight.com/courses/sqlserver-database-corruption you can get a free trial subscription and see the video, and then figure out what to do with your database.


0

One way would be a check constraint, but this will not give meaningful error messages create table Table1 (PaymentType varchar(5) not null, CCNumber varchar(16), PONumber varchar(30), CheckNumber varchar(10)); GO ALTER Table Table1 add CONSTRAINT CHK_PAYMENTTYPE CHECK (CASE WHEN PaymentType = 'CC' and CCNumber is NOT NULL and PONumber is NULL and ...


0

This can be done but you have to have the exact same build number as the old database server. If you have the old sql server binary folder you can check the version on the sqlservr.exe binary or just check an old ERRORLOG file which states the version. You can find links to all builds on http://sqlserverbuilds.blogspot.com When you have the correct version ...


1

[Can't add comments yet so "commenting" here and a moderator can change it to a comment.] I would recommend checking the network adapter properties on all systems involved and disabling auto-detect network speed/duplex. If it's set to auto-detect and you don't want to change it without knowing it's causing a problem, copy some large files and see if you're ...


0

Well I figured it out. Log4Net's AdoAdapter was keeping a prepared statement saved through the duration of it's connection, and my plan cache was flushing them out sometimes.


1

I got this same error a few weeks ago, despite the fact the connection was fine; it was actually due to an authentication issue. If you don't have domain connectivity between the two machines, you can use certificates to create credentials that can be used for mirroring endpoints. To do this, you create a certificate on the database on each end, create an ...


1

No, there is no means of partitioning either an IDENTITY or SEQUENCE. In order to do this you would have to create a table to manage TenantID and RecordID for this table. Then you would need a function that accepts TenantID and will return RecordID. This means that you need to insert via a stored procedure that can grab that value and then do the INSERT, or ...


1

Are you just looking for a complete copy of the data to completely replace what already lives on the destination instance? In that case, a full backup of the source database and a restore into the destination instance would be the cleanest and easiest route. If you are looking at select data copying (i.e. you don't want all data, or you don't want to ...


0

I want to call attention to one of your business rules: Representative is Customer and is Company. This sounds like a superclass/subclass situation to me, and I think you have the EER diagram well in hand, as far as ER modeling goes. Where things start to get interesting (and a little messy) is when you try to switch over from an ER model (which is ...


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 don't think that any of these suggestions are particularly improvements in performance compared to the CASE as that's already very basic. Another possible option (although again, probably not worth the effort as it's just a short-hand way of writing your CASE) for SQL Server 2012 and above. I'd be interested in seeing what the reaction was of the person ...


2

The !! <command> syntax lets you shell out. The external command is handled synchronously (i.e. the next line of the SQL script will not execute until this command finishes). This is why the documentation has a note in a few places stating: When you use the Query Editor in SQLCMD Mode, you must be careful not to execute interactive statements. The ...


5

This should do what you need SELECT ISNULL(ta.[tag-value], 'N/A') AS [tag-value] FROM @AgentTableFilt ta Or you could use the following SELECT COALESCE(ta.[tag-value], 'N/A') AS [tag-value] FROM @AgentTableFilt ta


5

SQL Server 2008 R2 service packs are cumulative and can be used to upgrade all releases of SQL Server 2008 R2 to Service Pack 3. From http://www.microsoft.com/en-US/download/details.aspx?id=44271 Given the documentation, you should be able to go directly from SP0 to SP3.


1

You could use the RML utilities to capture a workload trace before and after the index rebuild and compare execution times. RML utilities come with a reporter application that can compare the workload analysis of two trace files. You can download it from here: X64: ...


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

I remember learning three semesters of databases theory during the Computer Science faculty that did not help me much. Mostly, the hands-on experiences during the practical laboratories were more helpful. To have a first start, you can just play around with a SQL tutorial such the SQLZOO that teaches you step-by-step how to do the basics of database ...


0

I recommend Itzik Ben Gan's books: TSQL Fundamentals or 70-461 exam guide (they are specific to Microsoft SQL Server, but SQL implementations in other RDBMS are similar). The second one has a chapter introducing the mathematical concepts of relational databases - you should like it. I wouldn't recommend learning databases without getting familiar with SQL. ...


1

Your file is probably large but empty. In SSMS, right-click on your database and go into Tasks, Shrink Files. DO NOT HIT OK IN THIS DIALOG - but it's the easiest way of seeing how empty your files are. Choose Log in the dropdown, and I imagine you'll see that your log file is actually 99% empty. You probably had a large process that grew the file to ...


2

Start by making a column like CASE WHEN MinutesSinceLastRequest < 20 THEN 0 ELSE 1 END AS IsOver20 Then wrap that in a rolling sum - this will change every time there's a 1 for IsOver20: SUM(IsOver20) OVER (ORDER BY [TimeStamp] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS GroupNum So now you can group by this easily. Include PARTITION BY UserId ...


0

You can run a query similar to the following that will show the number of reads and writes for files since SQL Server started. Change the number in line 6 to get stats for different databases. 1 = master, 2 = tempdb, 3 = model, etc. 5 and up will be for your user databases. SELECT files.physical_name, files.name, stats.num_of_writes, ...


6

When a view is created it is bound to the metadata it needs in order to execute. Note: https://msdn.microsoft.com/en-us/library/ms187821.aspx That says that sp_refreshview: "Updates the metadata for the specified non-schema-bound view. Persistent metadata for a view can become outdated because of changes to the underlying objects upon which the view ...


0

I have instead edited my PHP code that handles this data: //Show the location in the truck description if ($mainrow[24] == "0") { $mainrow[16] = $mainrow[16] . " Location: Edmonton"; } else if ($mainrow[24]=="1") { $mainrow[16] = $mainrow[16] . " Location: Vancouver"; } where $mainrow[24] is the "Inventory" column, and $mainrow[16] is the ...


4

No, the option is there for you so that you can decide to either: Kick all users out right now and restore immediately; or, let the restore operation wait until it can get exclusive access to the database before it begins. You can't restore and keep connections active, sorry. This is like changing the keg without interrupting someone actively pouring a ...


0

If you have a lot of CXPACKET waits it is possible that reducing setting the MaxDOP is appropriate and will help. You should not change the cost threshold for parallelism setting assuming you have a NUMA machine (which you most likely do). MaxDOP: https://support.microsoft.com/en-us/kb/2806535 ...and I read somewhere that cost threshold needn't be changed ...


0

Hint: instead of adding define _END = ""; to each template, it can be added into dialect (netezza.tpl for example). This has fixed compilation of all the templates on the latest tpc-ds 1.3.1.


0

If the crux of the question is, "How do I get correct data to size my storage subsystem?" and you want to it fairly repeatedly across many servers, use windows performance counters. Here is the list I would minimally have: Logical Disk(*)\Current Disk Queue Length Logical Disk(*)\Disk Read Bytes/Sec Logical Disk(*)\Disk Reads/Sec Logical Disk(*)\Disk Write ...


0

You will need to reinstall the service pack, it will only update the feature you installed from source. (and I second Aarons humble option)


2

I would always err on the side of just installing the service pack again (it will know which components, if any, to update). Especially when adding client tools to a workstation - where this is not a major disruption. (And if you're adding BIDS to a production server, you should reconsider IMHO.)


1

Many NoSQL products have sharding built-in. The DBMS itself looks after storing a particular key range on a certain server and keeping redundant copies for high availability. Client connections are routed within the DBMS rather than in the application. Multi-server scale out becomes easy, at the cost of CAP compromises.


0

IMHO you MUST take transaction log backup before swithching to simple recovery it would help you in PIT recovery as well as its documented and recommended in BOL Article. It wont do any harm and you will have a failsafe. Please also note its advisable to change recovery model when load on database is relatively less. Although you can change it in peak hour ...


1

Once you switch to Simple the Trans Log will no longer maintain a log of operations since your last (database files) backup - at the point of the switch all transactions will have been committed to the main database files so will not be discarded - they are already effective in the database files. Your point 1 is not (in my opinion) worth doing - once ...


4

You CANNOT do this - you cannot attach/detach or backup/restore a database from a newer version of SQL Server (2014) down to an older version (2012) - the internal file structures are just too different to support backwards compatibility. You can either get around this problem by using the same version of SQL Server on all your machines - then you can ...


0

The database has log and data files that are stored on the file system. If the sys admin has a process of regularly backing up the location where the database files are stored then you might be able to get some of the data back. Assuming that you can get these files from before you overwrote them - then create a new database and attach the db and log file to ...


0

it seems like your only option is a super long shot hail mary. WITH REPLACE replaces the old DB but I'm not sure if it explicitly searches for those DB pages on disk and tries to overwrite the pages even if the DB is different. If the data is only 'marked' for deletion from the OS then you can have a very expensive data recovery center look at it. ...


0

In SQL 2008 you're looking for minimum downtime vs no downtime in these cases unless you're willing to shell out a lot of money/time for a few seconds. This sounds like hopefully it's not too complex of an environment so you have several options. How about SQL Mirroring? If your data isn't coming in too fast, it'll sync up and you can 'failover mirror' ...


0

Couple of options: Adding a third node might be possible but as you are running on SQL 2008 you have to figure out a way to replicate the storage between the two locations (https://msdn.microsoft.com/en-us/library/ff878716.aspx) In most cases this would be done with SAN replication. If you can do that you're good to go. If SAN replication is not an option ...


0

Look to see if the data is stored somewhere else. Perhaps the system sends email confirmations. If so, retrieve the emails (from sent items or from auditing on the email system) and hire some temps to retype the information. Perhaps the system prints out reports. If so, obtain the printouts and get the temps typing. Perhaps the system exports data and ...


1

Since the secondary in a log-shipping configuration is either READ ONLY or RESTORING, you cannot make changes to the database (i.e. create a USER) until a ROLE CHANGE happens (i.e. when you failover from primary to secondary). You can create a LOGIN on the secondary server. You can use this script - How to transfer logins and passwords between instances of ...


5

A couple of feasible options, in order of my preference: Option 1 Create a new, empty database locally - maybe make the log file large enough, at least temporarily, to accommodate the entire set of data you are moving without growth Use SELECT INTO, the Import/Export "Wizard", or Red Gate SQL Data Compare to copy this table to the new database (note that ...


0

Assuming you are using SQL Server 2008 and up, A better way of doing is to create a role in the database and grant that role permissions. You can add users to the role, so they will inherit the permissions of the role. -- to grant CREATE, ALTER, DROP OBJECTS (tables, procs, functions, views) with ALTER permissions on the schema. You can obviously fine tune ...


1

Without an internet connection, you would be looking at merge or peer-to-peer replication. Neither of which can be implemented easily (google I seem to remember seeing a workaround somewhere) how you want with Express edition. If you had standard or better edition I would personally go with Peer-To-Peer as it negates the need for an additional column per ...


2

Differential backups are backups of any changes to the DB since the last full backup. So in theory, yes, you could have it do a diff backup since some date (if you did a full backup on that date). Otherwise, no. It'll always be since the last full backup.



Top 50 recent answers are included