Tag Info

New answers tagged

-2

I know cross references between schemas/databases are commonly used in Oracle and now in MS-SQL as well :) DBmaestro TeamWork (where I work) provides database enforced change management which consists of: Database enforced version control – enforcing the version control process on the database objects. Database baseline impact analysis – generating delta ...


2

Here how can you create HTML body part of your mail. Step 1: DECLARE @Body NVARCHAR(MAX), @TableHead VARCHAR(1000), @TableTail VARCHAR(1000) SET @TableTail = '</table></body></html>' ; SET @TableHead = '<html><head>' + '<style>' + 'td {border: solid black;border-width: ...


0

A good place to start would be to identify and disable (remove at a later stage) unused indexes, you can do this by using the standard SQL Server reports in SSMS. Another option is to investigate if the Server have enough memory (RAM), a qualitatively cheap improvement now a day. Like Brent Ozar said; the correct amount of memory for SQL Server is MORE!!!


1

This will list explicitly granted permissions on table types, but not those granted implicitly through role or group membership, or permissions granted against the containing schema. SELECT [schema] = s.name, [type] = t.name, [user] = u.name, p.permission_name, p.state_desc FROM sys.database_permissions AS p INNER JOIN sys.database_principals ...


2

There is no magic; if you have 8 columns to output then you'll need to have 8 expressions to change the output in any way. (Have you considered normalization?) A slightly less verbose approach would be: SELECT Hail_N = COALESCE(NULLIF(hail_n, ''),'0'), Hail_S = COALESCE(NULLIF(hail_s, ''),'0'), ... FROM dbo.vw_hail_wind_directions; -- always use ...


8

There are two key differences between EXCEPT and NOT IN. EXCEPT EXCEPT filters the DISTINCT values from the left-hand table that do not appear in the right-hand table. It's essentially the same as doing a NOT EXISTS with a DISTINCT clause. It also expects the two tables (or subset of columns from the tables) to have the same number of columns in the ...


0

Check this: DECLARE @jobId BINARY(16) SELECT @jobId = CONVERT(uniqueidentifier, job_id) FROM msdb.dbo.sysjobs WHERE name = 'Your_Job_Name'


1

There are a few causes of this, the top ones are: storage subsystem, bad t-sql, missing/bad indexes. Paul Randall talks about this on his blog and at the sqlperformance blog. I like the scripts from Glenn Berry to find missing indexes and also SP_BlitzIndex. How do you know if you can trust the recommendations? Well I examine the existing indexes first ...


1

In SSMS 2008, I believe you need to delete the file C:\Users\%username%\AppData\Roaming\Microsoft\SQL Server Management Studio\%Version%\SqlStudio.bin This will, however, clear ALL of the logins and saved servers. You may be able to edit the file using an editor, but I believe it would just be easier to start from scratch. If you are using SSMS 2012 or ...


9

If you restore a database as a new database, the dbi_crdate field is the restore date/time. Here's how to reproduce it: Create a new database. Run DBCC CHECKDB() on it. Look at DBCC DBINFO() for the creation date and DBCC dates. Back up the database. Delete the database. Restore the database (if you want to get fancy, use a different database name.) Look ...


1

Dirty pages are written directly to the data files. There is no need to write them to the log because their modifications have been logged already. A dirty page never hits disk until all log records that record its modification have been written already. After the log has been hardened SQL Server is at leisure to write dirty data pages whenever it wants (or ...


0

Rght click on SSMS, Run as administrator, sometimes this error appears. Once i couln't attach mdf file to database due this error.


0

It might be worth looking at XACT_STATE instead of @@TRANCOUNT. From BOL: Both the XACT_STATE and @@TRANCOUNT functions can be used to detect whether the current request has an active user transaction. @@TRANCOUNT cannot be used to determine whether that transaction has been classified as an uncommittable transaction. So there may be some cases ...


0

The following will work: sqlcmd -U user -P pass -S Server -Q "sp_who" -r1 1> test.log The output from the query will be put into the log and nothing is printed on the screen. In the case of an error, it will run like this: sqlcmd -U user -P pass -S Server -Q "sp_notreal" -r1 1> test.log Msg 2812, Level 16, State 62, Server XXXX, Line 1 Could ...


1

As Kin noted above, it is not possible to get this information since it is not stored by SQL Server. Please refer to this article on technet. Since you are using Ola's scripts, unless you changed the defaults, the output of the IndexOptimize job should go to the same location as your error log.


0

I write this query and this work. USE DestinationDatabase DELETE sysDiagrams WHERE name IN ( SELECT name FROM SourceDatabase.dbo.sysDiagrams ) SET IDENTITY_INSERT sysDiagrams ON INSERT sysDiagrams ( name , principal_id , diagram_id , version , definition ) SELECT name , ...


4

SQL Server does not maintain when an Index was last rebuild, instead it keeps information when stats were last updated. That can be found using the STATS_DATE function. You can use Ola's Index maintenance solution or Michelle Ufford's - Index Defrag Script. These scripts are widely tested in the community and are much flexible so that you can adapt as per ...


8

No. This hard-coded restriction exists for good reasons related to possible excessive query plan compilation time. You can workaround it by listing the VALUES clauses in a CTE, then INSERTing from the CTE, but I do not recommend it. Break the INSERT statement up into VALUES clauses of <= 1,000 lines each as a workaround, or use an alternative data ...


2

From the SQL Server query optimizer's point of view, there is not much to choose between the parallel and serial execution plans in this case. In general, the optimizer's cost model reduces the CPU cost (not the I/O cost) of operators in a parallel plan in proportion to the estimated degree of parallelism available. This CPU adjustment explains why the ...


1

I believe you are running into the issue described in Subqueries that you define on merge articles are not re-evaluated. From the article the cause is: This behavior occurs because the query is not re-evaluated and the row is not propagated as part of replication when you update a row in a table that is referenced by a subquery. Although you can ...


2

The process is simply to launch SQL Server setup from your DVD or ISO, go to the Maintenance tab, and perform an "Edition Upgrade" (please ignore the fact that this screen shot is from SQL Server 2014): You'll need to enter a different product key. Please also make sure you install the latest service pack (SP3; 10.50.6000). As for impact, you ...


3

Just following on from @usr's answer (and similar question here), indexed views could work for this, or more (precisely a covering non-clustered index on the index view), as long as you're aware they can destroy INSERT / UPDATE / DELETE performance on the underlying tables and (from experience) can contribute to deadlocks. If neither table is that dynamic ...


1

select * from ( select SubBrand,Month,Saless,Variable from (select * from DataTable )as p unpivot (Saless for Month in(January,February)) as u ) as p pivot ( min(Saless) for Variable in ([Sales],[Promotion]) )as PP order by SubBrand,Month


3

This is a classic performance problem. You are ordering on the columns of one table and are filtering on the columns of another. SQL Server can either scan Data and filter out rows by seeking into the other table (the current plan). Or it can access Permission to determine the Data rows that pass the filter, and then sort this set. Both plans require a lot ...


2

Your log backups are being done by the Log shipping process, that is after all what is there to do. So, you will generally not include log backups in your backup strategy but take into account how often your log shipping process runs. You would only worry about taking full and/or differential backups against the databases involved in log shipping.


0

Just make sure you are opening the right SQL Server configuration manager software, if you're opening the wrong SQL Config Manager on your machine it will not show the SQL server service, because you are looking at a viewer and not the actual config manager. I had the same problem with SQL 2014 and when browsing again for the Config Manager software on my ...


5

IDENTITY is not a column type in its own right, it is a property that can be apply to any integer column type. It is usually applied to an INT and in that case there is no difference (the column is an INT column, with the IDENTITY property defined) though it can be applied to any type that is internally an integer (BIGINT, DECIMAL, MONEY, ...). While your ...


1

You have a few options, some of which were already pointed out. First, though, if these events haven't been captured so far there is no "historic" way of obtaining the data. There may be ways to get some of the data but chances are you won't get all or even most of it. Going forward, however, it seems you only want those who successfully connect to the ...


0

As an addition to the above answer. Another solution that was easier to use is this : Importing registered servers On the SQL Server 2005 system, shut down SSMS and copy out the files in this location: “%appdata%\Microsoft\Microsoft SQL Server\90\Tools\Shell\RegSrvr.xml” Now copy those files to the same location where you installed SQL Server 2008. Open ...


1

As Cougar9000 found out himself the usage of ephemeral (dynamic) ports is not managed by the application but by the operating system. You can only change it for the whole server. The answer to this question does provide you with the information and links you need. In short you could do netsh int ipv4 set dynamicport tcp start=50000 num=1000 Please note ...


0

My first question where are you looking for SQL Server memory consumption. Please don't use task manager or process monitor, these do not show correct value when SQL Server service account has Locked pages in memory privilege. just use below query to see memory consumed by SQL Server select (physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB, ...


0

Start by looking at the min server memory and max server memory configuration options detailed at http://msdn.microsoft.com/en-us/library/ms178067.aspx. You can pull them up by running: exec sp_configure 'min server memory' exec sp_configure 'max server memory' Ultimately, you have to decide if performance is being affected by these settings or not.


0

For a default instance (MSSQLSERVER), you only specify the computer name when connecting; the instance name (which is actually a ficticious placeholder) is implied. Also, if you're connecting from another machine now, you may need to configure the Windows Firewall to allow connections through. The error message you get will be the same as the one you're ...


0

Open SQL Server Configuration Manager. That will likely show you the answer to your problem. In SSCM you should be able to see what database instances are installed, what their instance names are, what ports they run on, and whether the services are up. There could be a number of different reasons you're getting this such as the server is not started or ...


0

If you are trying to restore db from a server to another server and db is not exist on restore server, you can try method below. That problem may occur depends on this reason; DB files could be locate on different disk names which is not exist on restore server. To solve the problem on restore server; create a db and db files which are attached with the ...


2

You don't say what DB you are using, but that syntax looks like MS SQL Server. Also you do not properly state the table structure - the function refers to VehicleID which doesn't appear in you sample data and you don't say what the data types or what indexes and keys are present. All of this could be significant. You are pulling a bunch if data into a ...


0

I experienced the same error recently with upgrading SQL 2012 to SP2: 2014-11-08 23:49:55.49 spid5s Checking the size of MSDB... 2014-11-08 23:49:56.13 spid5s Error: 5041, Severity: 16, State: 1. 2014-11-08 23:49:56.13 spid5s MODIFY FILE failed. File 'MSDBLog' does not exist. 2014-11-08 23:49:56.13 spid5s Error: 912, Severity: 21, State: ...


3

Use module signing. Sign your procedures with a certificate and then grant the required permissions to a certificate-derived user. The link contains a full example.


4

NO, With just Execute permission doesn't allow a user to execute a stored procedure successfully if it contains some DDL (CREATE, ALTER, DROP, TRUNCATE, RENAME) statements.


9

Don't. While is possible to use context_info, that is a really really really bad choice for a password. All users with VIEW SERVER STATE will see it in sys.dm_exec_sessions. SQL Profiler will not know what you're doing when you set it and will not obfuscate it in produced events. And more similar badness. Instead use proper encryption hierarchy. Do not ...


0

SQL Server has no global variables. If you want to make something available to all sessions, use a table and write the data there. Another option is persisting the data in a global temporary table (prefixed with two # signs). It could get tricky to detect whether the table got out of scope though. If you want to make a value available throughout the whole ...


0

Without commenting on your encryption solution per se, storing a string or password in the database can be as trivial as creating a single-row, single-column table and properly restricting the permissions on that table. Another approach would be to create an encrypted view with a hard-coded column. CREATE VIEW dbo.mySecret WITH ENCRYPTION AS SELECT ...


0

Context_info acts like a global variable. It's binary so will require some work to extract the password each time it is used. BOL has details and examples.


0

Consider this as answer SP3 is new and thoroughly tested its highly unlikely it would create any issue but again I would recommend you not to proceed without any testing and that too when Cluster is involved. Also asking whether anybody faced issue will lead you in problem, what if somebody writes 'Yes I have done and its working fine' yes they are correct ...


3

Yes, this is possible by creating a user with 'securityadmin' server role and then if required Deny his access to SQL Server Query Window through LOGON Trigger USE [master] GO ----Create Login CREATE LOGIN [DBA_ErrorLogUser] WITH PASSWORD=N'123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO --- ...


1

Yes, you can query the database to find out the version via sqlcmd. The actual SQL you'll want to use is just SELECT @@VERSION but to do it via sqlcmd, you can use: sqlcmd -S[server] -U[user] -P[password] -q "SELECT @@VERSION" Also check the MS sqlcmd reference for further info on flags you can use with sqlcmd.


1

The package created by the copy wizard can be modified to bring a little better performance possibly but your log growth is expected. Every bit of data changed for the table and indexes is being modified, that has to be logged no matter what recovery model you are in. I would go with the restore from backup as this will be a bit cleaner. You can pull the ...


1

CHAR(0) appears to be converted to a space, or CHAR(32). The following demonstrates the problem: DECLARE @Data NVARCHAR(255); SELECT @Data = 'this is a test' + CHAR(0) + 'of null'; DECLARE @i INT; SET @i = 1; DECLARE @txt NVARCHAR(255); WHILE @i < LEN(@Data) BEGIN IF SUBSTRING(@Data, @i, 1) = CHAR(0) BEGIN SET @txt = 'found a null char ...


0

Microsoft gives Downgrade Rights to Volume Licensing customers and, to some extent, even to OEM and retail customers. However, in general, if you don't have a Volume License, you can't have the bits and the product key, which are necessary to excercise your downgrade rights. Long story short, if you want 2008 R2, you'll have to buy it. Here's a blog ...


2

Since you stopped the index creation, the index was not created. However, the space it had reserved already (10GB) was still added to the database size. SQL Server will not automatically shrink the database file size back down unless you explicitly instruct it to (which is not recommended unless you are about to run out of space). You can add up to 10GB of ...



Top 50 recent answers are included