Tag Info

New answers tagged

0

Just a wild guess, but my first look would be at Sql Server Configuration Manager to see if the "Force Encryption" flag is on for that instance. Just because they've "disabled SSL" it doesn't mean they've told SQL Server that non-SSL connections are ok. Otherwise see the comments above about the SQL Server error log.


2

Something has happened such that the source column doesn't exist any more and the mapping is broken. In the bottom drop down, "spaltzenzuodnungsoption..." there should be 3? options. You want the one that says delete. This will remove all the mappings so you can start over.


4

Well, you shouldn't ever be using DBCC SHRINKDATABASE, IMHO - if you need to shrink files at all, you should think twice, maybe even three times, and even in the odd case where it really is warranted (hint: this should be rare), you should target each file individually using DBCC SHRINKFILE. Please read every single word on this page. Anyway, you can see ...


2

No need to reinvent the wheel, Adam Machanic has written a usefull stored procedure to replace SP_Who and SP_Who2. You can find it here for free. (http://sqlblog.com/blogs/adam_machanic/archive/2011/04/27/who-is-active-v11-00-a-month-of-activity-monitoring-part-27-of-30.aspx) This stored procedure has a column “Percent_Complete” which gives you how much ...


0

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 ...


0

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

@JeremieGrund - If the database physical file architecture is same( data & log file drive names & locations), then if you shrink the log file on the principal, the shrink command will be sent to mirror and do the same on mirror. So in this way your mirror log file can be maintained.. If the files architecture is not identical then you should follow ...


0

The question is not very clear but I think you need s simple correlated subquery: SELECT [CaseNumber] ,[CenterCode] ,[YearCode] ,[SubVerbatim] FROM [dbo].[ToxExpSub] AS a WHERE EXISTS ( SELECT 1 FROM [dbo].[ToxExpSub] AS b WHERE b.SubPoisindexCode = 6931087 AND b.[CaseNumber] = a.[CaseNumber] ) ;


0

For me the issue was slightly different. For certain accounts the IT Administrators had prevented log on to all Workstations. The server I was setting up was not included in the allowed log on computers, hence it was failing to log on. Adding the server in AD meant the account could now run SQL Server.


1

Got a SQL database and the log file is massive and keeps growing every day. Log files growth depends on Recovery Model of a database and transaction log backups. if database is not critical and data loss is not an issue in any case then it should be in SIMPLE recovery mode. Which allows SQL Server to reuse log space when a transaction is committed and ...


0

This sounds like a classic case of stale statistics. At 500 rows per hour for 30 days, we are only talking about 360,000 rows. I know you said you ran Sp_updatestats, but that only updates a sampling. Instead, run EXEC sp_MSForEachTable 'UPDATE STATISTICS ? WITH FULLSCAN;' If that doesn't improve the performance, then you probably need to consider ...


3

Ok, so when you ran SELECT * FROM sys.columns WHERE object_id = object_id('tb1') for each of the tables you found that the collation was not the same as the database default. At this point you have two options. Change the collation in the query All you have to do here is use the COLLATE phrase after the column that needs it. select distinct Number, ...


0

I believe that if you run native SQL Server backups (using T-SQL BACKUP) instead of DPM protection agent-based SQL backups, the SQL Server VSS Writer service must not be used on your SQL Server database folders. (The same problem is true for other third-party backup tools.) This DPM/VSS service is not used by native SQL backups. If you prevent the DPM ...


1

If you are just doing some maintenance scripts (not stored procedures) then you can do this type of thing using SQLCMD Mode ( Query > SQLCMD Mode ), eg :setvar databaseName yourDbName EXEC msdb.dbo.sp_help_schedule @schedule_name = 'FullBackup_$(databaseName)' Variables have a batch scope and can even pass through to other scripts called with the :r ...


0

I am talking about database mirroring as I am not sure whether this discussion holds same for AG which I assume it will be. I am talking about Synchronous mirroring and as per my knowledge actually following happens Following happens when suppose DML is started on Database principal 1.The transaction Log record from DML transaction would be inserted into ...


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...


0

It's almost certainly a matter of the data for the first run are not in the cache and have to be read from disk. After that first run then the data is in cache and the query can run much more quickly. After several minutes other data needs to be in the cache and so the data for this query get's pushed out. There is a simple way to test this. Run the ...


1

I would not make modifications like adding primary/unique or foreign key constraints to a legacy database that you haven't built yourself. Chances are that the original developer, given the problems and design issues you mention, may have built logic that breaks with such constraints. For instance, if the app uses a 0 in a key column instead of NULL to ...


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 ...


0

Zip codes should be stored as text, as some start with 0 (screwing up formatting/sorting) and there is no reason to do math on them. Also, if you want to store global postal codes, they often contain letters. Phone numbers are a maybe for text, especially if there might be extensions. Or you want to store numbers like 1-800-GOT-JUNK. There's no reason to do ...


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 ...


5

Yes, this is a limitation in T-SQL. There are several scenarios where you can't build up a string dynamically, but rather have to do so beforehand. For example: RAISERROR('String' + @variable,1,0); EXEC dbo.procedure @param = 'String' + @variable; There are other cases where it is valid, but usually just for assignment, not for passing or doing anything ...


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 ...


3

Your colleague is correct that it is easier to simply not think about it and just store everything as a varchar. But this comes at a large cost in terms of space requirements, performance, flexibility in querying data, and most importantly, lack of data integrity. This is not just a one-time cost; it is paid repeatedly over the lifecycle of the ...


2

No, the DMVs track aggregates, not individual executions, so you can't pinpoint things like that. Want to drill in, you'll need to capture queries using server-side trace*, extended events, management data warehouse or one of many 3rd party monitoring tools. The latter cost money but, IMHO, you get what you pay for. We have one I could recommend... * Note: ...


2

There are several situations in which it's better to represent numbers using some kind of numeric data-type. It's a little more efficient, but that's just the beginning. You get support for built-in arithmetic using SQL operators without performing type conversions at run time. Not only do type conversions slow things down, but they can result in numerous ...


3

Size is one consideration. An int can hold up to -2,147,483,648 in four bytes. A char will need 11 bytes to hold the same value. There are built-in functions to manipulate the various data types. DATEADD() and DATEDIFF() are two examples. This will not be possible with date-stored-as-text. Constantly CASTing back and forth will not make for efficient ...


0

There are different data types in sql to store the data. if the all data are same then varchar is good to store. but in the future, you need the operation on the data, you can't because of limited functionality. better to go with the data type of data.


1

First - stop using the phrase "Null value", it will just lead you astray. Instead, use the phrase "null marker" - a marker in a column indicating that the actual value in this column is either missing or inapplicable (but note that the marker does not say which of those options is actually the case¹). Now, imagine the following (where the database does not ...


1

Tested on SQL 2005 only: declare @testTime DateTime set @testTime = '2013-04-23 13:55:06' select cast(substring(cast(getdate()as binary(8)),1,4) + cast(@testTime as binary(4)) as datetime) Why, how? -- These two have the same last 8 hexadecimal digits, -- and vary one digit before that -- This shows that the date and the time ...


1

UPDATE statements in PDW are only partially parallel rather than fully parallel like CTAS. That said, this could well be down to indexing. What is the actual code that you're running? Do you have indexes in place to help find those records you're expiring? You do still need to apply some of the standard tuning techniques of applying non-clustered indexes to ...


5

A few questions. 20 million rows isn't necessarily that large. What process are you using to perform your updates and deletes right now? Is the dimension a CLUSTERED COLUMNSTORE INDEX, CLUSTERED INDEX or HEAP? Are you saying that there is movement whilst you update and delete this table or did you just see movement when you changed the table from ...


1

Sorry, this may not be An Answer, but I couldn't add a comment. This is surely a 'solved problem' in that hotel and reservation systems are available commercially 'off the shelf'? Why develop your own? Even if you do decide for good reason that you need to, it might well be worth investigating the data models underlying a few commercial products, if you can ...


1

Being replicated doesn't stop you from using partitioning. Partition your table. Then, for the rows you need to delete or update, CTAS the entire partition into a new table, using LEFT JOIN and COALESCE to get the appropriate (i.e. new) values for updates from the changed rows while keeping the rows you want and excluding the ones you don't. Finally, ...


3

To answer more concisely than the existing answers: REORGANIZE produces the same amount of log with SIMPLE and FULL. It's just that SIMPLE almost immediately makes that space available again. Except if something is preventing log truncation such as an open transaction (unrelated to the session the REORGANIZE runs in).


2

Do you really need ROW_NUMBER and OUTPUT? How about a nice set-based DELETE, eg something like this: USE tempdb SET XACT_ABORT ON BEGIN TRAN SELECT * INTO #o FROM sys.objects -- Identify records to keep SELECT TOP 2 object_id INTO #keep FROM #o ORDER BY object_id -- Delete others DELETE o FROM #o o ...


0

SQL Users are still available primarily to give access from systems that don't have easy AD access. For example in my company our UNIX systems do not use AD so applications written on those systems need to use a SQL Login in order to connect to a SQL Server instance. I'm told it is possible to set a UNIX system up so that it can use AD but ours just aren't ...


1

The security concept of SQL Servers differentiate between login and user. A login is at the SQL Server instance level and can be mapped to different users for different databases, as long as the database user is a SQL user and not a Windows user. You could create the Windows login and the user (both having the same name) followed by script that copies the ...


10

This is expected behaviour at the moment the function gets evaluated on the DELETE stream. So it actually behaves like this (pseudo code) DELETE k OUTPUT Deleted.name, ROW_NUMBER() OVER (ORDER BY Deleted.object_id) as r FROM ( SELECT k.*, ROW_NUMBER() OVER (ORDER BY object_id) r FROM #o k ) k WHERE r <> 1 --OUTPUT returns rows ...


1

You missed the exact point what books on line was trying to mention it states that The following index operations require no additional disk space: ALTER INDEX REORGANIZE; however, log space is required. If you read complete article it was trying to point out the commands or operations in SQL Server which would require additional disk space and ...


1

Database recovery mode SIMPLE never means that log will not be created. SIMPLE recovery model just ensure automatically reclaiming of log space once operation is complete/committed. ALTER INDEX REORGANIZE operation creates log and for this it needs log space regardless of database RECOVERY model. Learn more about recovery models in SQL Server ...


-3

One of the major purposes of a UNIQUE constraint is to prevent duplicate records. If one needs to have a table in which there can be multiple records where a value is "unknown", but no two records are allowed to have the same "known" value, then the unknown values should be assigned artificial unique identifiers before they are added to the table. There ...


1

This may not be technically accurate, but philosophically it helps me sleep at night... Like several other have said or alluded to, if you think of NULL as unknown, then you cannot determine whether one NULL value is in fact equal to another NULL value. Thinking of it this way, the expression NULL == NULL should evaluate to NULL, meaning unknown. A Unique ...


0

Although replication may not be High Availability in design, it depends on your definition of HA. Certainly it has been used for HA by many people. If replication is down long enough it can be marked as Inactive. To automatically reactivate a replication, you could try using Kin's response: SQL Server replication subscriptions marked as inactive This ...


0

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 ...


2

I have dealt with some long running jobs in the past and the best way I have found to identify them is to run a separate job that checks on that sort of thing. Thomas LaRock put together a nice script that does that, and you can modify it to either email you or if you wanted you could have it just stop the job as well. In my case, the job literally just ...


0

I can understand that you feel some pieces that you are used to are missing. But that is only because they are missing. Nonetheless, SQL Server was being successfully used when Foreign Keys were just a concept (which we implemented through triggers in those days), not a physical implementation such as a constraint. Declarative Referential Integrity was ...


17

Why does it work this way? Because way back when, someone made a design decision without knowing or caring about what the standard says (after all, we do have all kinds of weird behaviors with NULLs, and can coerce different behavior at will). That decision dictated that, in this case, NULL = NULL. It wasn't a very smart decision. What they should have done ...


5

Correct. The implementation of a unique constraint or index in sql server allows one and only one NULL. Also correct that this technically doesn't fit with the definition of NULL but it's one of those things they did to make it more useful even though it isn't "technically" correct. Note a PRIMARY KEY (also a unique index) does not allow NULLs (of ...



Top 50 recent answers are included