Tag Info

New answers tagged

0

Given the way this question has changed, it is difficult to determine exactly what you need. The current data and query in the question do not match the result you have posted. That said, assuming the data is the way I think it is, this should provide the result you want INSERT INTO T3 (name,number,fin) SELECT T1.name, T1.Rollno as number, ...


0

I very seriously question the use of a trigger here. Your trigger is going to dramatically slow down the updates of your table. That being said your query should look more like this: INSERT INTO zzuomupdate(date_cu, Query_r) SELECT top(15) getdate(), dest.text AS [Query] FROM master.sys.dm_exec_query_stats AS deqs CROSS APPLY ...


4

It looks like you'll need to change the user's default database, as it appears this is currently set to DB1, which no longer exists. exec sp_defaultdb @loginame='user1', @defdb='DB2' OR ALTER LOGIN <login_name> WITH DEFAULT_DATABASE = <default_database>


0

I'd recommend having a look at this blog:- http://sqldbawithabeard.com/2014/03/31/how-i-check-hundreds-of-sql-agent-jobs-in-60-seconds-with-powershell/ There's step by step guides on how to use powershell to check jobs on multiple SQL Server instances. Hope it helps. Andrew EDIT - Adding in part of powershell script The most essential part of the ...


5

sp_settriggerorder only allows you to set first/last, nothing in between. How SQL Server will fire the middle ones (or all, if you haven't used that) is completely up to SQL Server, it may not even be deterministic, and how it will fire them the next time the table is touched is not stored anywhere. Even the documentation states that the order is ...


0

this structure is incorrect, use M:N relationship with 3 Table Student Table StudentId FirstName LastName 1 John Sampo 2 Ann Rolls 3 Martin Blake School Table SchoolId SchoolName 1 ABC Language ...


0

Ref: Justin Dearing post. Posted here as need 50+ points to post as comment. In windows install vim elsewhere to get around path dramas like "C:\Program Files (x86)\Vim\vim74\gvim.exe" for ie in sqlcmdinit :setvar SQLCMDEDITOR C:\Users\\Vim\vim74\gvim.exe


0

You can see the information how SQL tables are linked. Keep the mouse pointer on the link table. You can see all the information like DSN = (Data Source Name) Name of the connection UID = UserID APP = Applicaton DATABASE = name of the database in which the linked table is available NETWORK = SQL Server network protocol ADDRESS = Server IP,Port TABLE = ...


0

Here's an example data dictionary script that makes heavy use of table and column descriptions that shows how useful they can be for self documenting, http://www.csvreader.com/posts/data_dictionary.php .


2

It is possible to detect such errors using backup checksums. If you use the CHECKSUM option, then yes it should be found but only when using a RESTORE or RESTORE ... WITH VERIFYONLY. If you don't check it, you'll never know. But what do you do when an error is found? That depends on what is wrong and where it happened. The action to be taken first ...


13

You've got a bunch of different questions in here, so let's break 'em out individually. Q: If I join two tables in the same database with the above query, why is it slow? A: For starters, you're not using a WHERE clause, so SQL Server has to build the complete result set, merging both tables together. If you only need a subset of the data, consider using a ...


5

They are identical in practice, however (from SQL 2003 standard): 21) NUMERIC specifies the data type exact numeric, with the decimal precision and scale specified by the precision and scale. 22) DECIMAL specifies the data type exact numeric, with the decimal scale specified by the scale and the implementation-defined decimal ...


13

They are actually equivalent, but they are independent types, and not technically synonyms, like ROWVERSION and TIMESTAMP - though they may have been referred to as synonyms in the documentation at one time. That is a slightly different meaning of synonym (e.g. they are indistinguishable except in name, not one is an alias for the other). Ironic, right? ...


6

Mike is right that the error message you're receiving is from the Management Studio application itself, and not from SQL Server. It is the memory on your local workstation that has been exhausted, likely due to trying to pull 16 billion rows into the client application (rendering that much data in a grid is quite expensive in terms of memory, so try to limit ...


7

This error indicates that Management Studio is running out of memory, not the SQL Server service. Even if you installed 64 bit SQL Server, the SQL Server Management Studio executable is a 32 bit application. This is likely caused by the size of the result set that you are returning to Management Studio. Are you executing something like SELECT * FROM ...


0

Well if there is active transaction you have to wait for it to finish. You cannot do much. If you have multiple disks in your environment add a log file on other drive and allo wtransaction log to grow and let active transaction complete. Add log file to database After the transaction finishes you can try shrinking the log file. Please dont make shrinking ...


0

You can use the following DMV to view any transactions that might be causing your log file to be bloated: sys.dm_tran_database_transactions. Both your queries are limited to your current connection, meaning they give you no information about other connections made to the SQL server. @@Trancount; XACT_STATE


1

If you just need to replace the whole piece of XML in once, then you can do a normal UPDATE, eg something like this: UPDATE yourTable SET yourXML = '<yourNewValidXML/>' WHERE rowId = 1 If you need to edit individual attributes or elements then you can use the .modify method of the XML data-type in SQL Server to update single values. Here's a ...


2

I'd have a table sit between users and messages with a one-to-one many relationship to both. Call it users_messages for example. Each row in users_messages would describe all the users who have an involvement in any particular message. For example if a message had 3 users involved then the table would have 3 rows for this. Each row linking to the same ...


2

A simple way to do this would be to create a new field in the Messages table called MESSAGE_VISIBLE_TO. This field should be of the same type as the USER_ID field and have a default NULL value. When User1 hits the "Clear History" button, the application would need some logic to update all of the messages between the two people, setting the MESSAGE_VISIBLE_TO ...


0

Incase you need it to deactivate it for a single user, you can add a column named something like DEACTIVE_USER_ID in message table with default value as NULL and make a update for message table when a single user dectaivate it.


0

You have to create 3 jobs as weekly, 1 set weekly for Sun-Fri 12am-11:59pm and another 2 to set weekly to run on saturday only, one for in the morning and one for after.


1

It seems to me that which db is better is not your primary concern here and instead you have two different considerations which cut against eachother, namely business knowledge vs customer desires. Ultimately that will be a business decision, not a technical decision. Obviously there is opportunity cost as Max has noted in a comment. There is no way ...


2

We are also in the process of installing this EMC product at the large insurance company where I work. We are currently using it only for file backups to replace our TSM file backups but will eventually install the SQL Agent. I definitely encourage you to test this in a non-production environment to understand its quirks prior to a production deployment. ...


2

If you have a clustered (or covering) index starting with (Key1, Key2, Key3, ...) on the table that the CTE is querying, this should be a well-performing query. Can you add a "Key4", preferably an identity column, in the source table to your non-unique clustered index, so you perhaps can make the index unique? That way, you could also set "Key4" as your ...


1

Any column in the SELECT clause that is not part of an aggregate function (SUM, AVG etc) must be listed in the GROUP BY clause. Create PROCEDURE [dbo].[spMerrShenimetBazePerPagese] @PunetoriId int AS Select p.PunetoriId, p.Emri, p.Mbiemri,p.Vendlindja,pg.Muaji, Sum(p.Paga) as Paga, Sum(pg.Bonusi) as Bonusi, Sum(gj.Vlera) as Gjoba, ...


0

Should we re-create / refresh all the indexes in the Database after such a restore ? If so then is there a good way to refresh all indexes with ease (as opposed to one index at a time). Its always a good idea to do a reorg/rebuild and update stats after a database refresh to a different server. This is one of the post restore step that I do when I ...


0

I would make sure you update stats with the paranoid option. USE db_name; GO EXEC sp_MSforeachtable @command1='UPDATE STATISTICS ? WITH FULLSCAN'; Outside of that definitely go back and check what the differences are, and discuss them with your virtualization guys


1

Normalization is a great concept and essential for well performing relational database. However it can become counter productive. For example you almost never see addresses broken up like this: StateRegionTable CityTable ZipTable StateId CityId ZipId StateDesc CityDesc ZipDesc AddressTable ...


1

The following logins will be created on installation. They are containers for the accounts that the SQL Server Database Engine & Agent run under. [NT SERVICE\MSSQLSERVER] [NT SERVICE\SQLSERVERAGENT] The other accounts are groups that have been manually added into your SQL instance. BUILTIN\Administrators was installed by default in SQL Server 2005 ...


2

After you deleted / dropped tables, you have to shrink the database to release unused space. Then do a backup and ask the client to restore. Note: If your database is in full recovery and you have never taken a log backup, then take a full backup followed by a log backup and then shrink the database. Caution: Using SHRINK is not a best and recommended ...


4

The sum of the file size for all data files has to be < 10 GB, not the amount of data in the file. So, deleting data from some tables, or even dropping some tables, does not solve problem. You need to shrink the file, something like this: ALTER DATABASE mydb MODIFY FILE (name = 'logical name', size = 2048MB); Then take a backup, then restore on SQL ...


2

You are not using the /Server:<string> /S Server name, default = (local) Failed to connect to server (local). ---> System.Data.SqlClient.SqlException: 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 ...


0

Looks like a connectivity issue, nothing to do with portioning per se. It's saying it can't find the server, is the SQL Service running, is it running on a default port, is the browser service running etc? It appears to be trying to connect to the default instance on the local machine, is that correct? If so can you try connecting with sqlcmd.exe to test? ...


2

You can do this sort of thing by creating what are essentially property bags of these values. This is not an approach that I am a huge fan of, but as with everything in SQL Server it has its place and your scenario might well be that case. This is essentially the EAV approach, and Aaron Bertrand has a great writeup on this here to describe the pros and ...


0

Looks like a case of Parameter Sniffing! Check google on it. Running these commands will have the same effect: DBCC FREEPROCCACHE This command removes all of the cached query plans and execution contexts from the plan cache. It is not advisable to run this command on a production server because it can adversely affect performance of running applications. ...


1

The following updates column 'cn' with sequence number starting from 1 DECLARE @id INT SET @id = 0 UPDATE X SET @id = cn = @id + 1 GO http://www.mssqltips.com/sqlservertip/1467/populate-a-sql-server-column-with-a-sequential-number-not-using-an-identity/


1

From SQL Server 2008 SP2 and SQL Server 2008 R2 SP1 there is support for 15,000 partitions so to be honest, you don't really need to do that much dynamically. Instead of having a complex daily process (Dynamically add filegroup and boundaries) with an opportunity to fail, simply create the partitions up-front from now to the year 2020, and you're well ...


1

To add a new partition, use SPLIT RANGE. Assuming you have the following partition: CREATE PARTITION FUNCTION pfTest(int) AS RANGE LEFT FOR VALUES (10, 20, 30); CREATE PARTITION SCHEME psTest AS PARTITION pfTest TO ([GRP1], [GRP2], [GRP3]); .. you can add a new partition by "splitting" the last range from (30 to infinity) to (30-39) and (40 to infinity). ...


5

Cursors are loops, but they can be more complex for the database engine to implement depending on the options you chose. There are many options for cursors in TSQL that can have a significant impact on performance depending on the SELECT that feeds the cursor with information (see the TSQL docs for detail). The options will change what locks and other ...


3

Your solution, although a WHILEloop, is essentially still a cursor in the sense that you loop over a number of records and execute one or more statements once for each record. It's not the WHILE loop in itself, but rather the coding pattern where you loop over rows in a table and execute a statement for each row. Typically, "traditional" programming ...


3

If the SQL server table is modified will it be automatically reflected in the [linked] table and when? Yes, on next refresh or requery. If I edit the linked Access table will it be reflected in the SQL server table? Yes; as before, these updates will be visible on the other end on next refresh or requery. Trusted Connection only controls how the connection ...


5

The .value method must always return a single value so you normally wrap it in brackets and move the ordinal ( eg [1] ) to the end. Here's an example: DECLARE @xml AS XML = '<DATA> <ROW RowNumber="1"> <COLUMN Name="ProdID" Value="1234" /> <COLUMN Name="AppID" Value="20144" /> <COLUMN Name="ConsentDesc" Value="My ...


3

One thing to remember is that an ID does not have to be generated by the IDENTITY property. Using IDENTITY has challenges, since as soon as rows are moved from the standby server to the current server (using IDENTITY_INSERT of course) with an ID value higher than the current identity value it becomes the new current identity value. Which can complicate ...


6

You could do this by running something like declare @newid int; select @newid = IDENT_CURRENT('YourTable') + 2000; dbcc checkident('YourTable', reseed, @newid); This would reseed your table identity to be 2000 higher than it currently is and seems like it should resolve what you are looking to do.


1

Certainly appears you can do that in Visual Studio 2012. I just created a Database Project "database1", then added another database project to the same Solution, named "database2". I then right-clicked the "Database1" project, clicked "Add Database Reference" and selected the "Database2" project. Then, under "Database Location" I chose "Same Database". ...


1

You could use one of the free SQL benchmarking tools such as Dell's Benchmark Factory for Databases (BFD) or Hammerora. Benchmark Factory has a free trial and Hammerora is opensource. They both run industry-known TPC benchmarks, and you can do custom workloads with BFD. I have only used BFD and each test 'run' can be saved as a report which I found really ...


2

It looks like you have no traces running on your server. The function mentioned in your post title is: SELECT * FROM sys.fn_trace_getinfo(0) Using either a 0 or a NULL as the parameter will produce a listing of all running traces. That list could look like this: traceid property value 1 1 2 1 2 ...


2

Try: LEFT OUTER JOIN TaskItems AS TI ON O.OwnerId = TI.OwnerId AND LastOperationTime IS NOT NULL The engine could be grabbing the Null row separately, as it's a slightly different scenario to not finding one. But as you essentially want the same behaviour, just out the explicit filter in there, within the ON clause of your LEFT JOIN. Then the only NULLs ...


0

I've spent most of today bashing my head against this, and didn't come up with anything. I tried: Messing with msdb roles on the CMS Messing with msdb roles on the target servers Messing with server roles on the CMS and target servers Petitioning the gods of old Trying something convoluted involving credentials and proxies In the end, nothing worked. I ...



Top 50 recent answers are included