Tag Info

New answers tagged

0

First of all, the Table Designer is not the wisest of all software and probably does not choose the approach to change the table that you would expect. The designer usually creates a new table, moves the data, then drops the original table. However, based on your error the designer also may not be FILESTREAM savvy. But if you try it yourself through ...


0

To find out more about the PREEMPTIVE_COM_SEQSTRMREAD wait type I would read this great article at sqlauthority, but as Shanky said in the comments, SSIS would more than likely serve you better for a large migration.


-2

SQL Server traces and Profiler can be used for auditing, but as the feature is announced to be deprecated in future versions of SQL Server, it’s not recommended to use this approach moving forward.


2

There's no ELSE in your first CASE, which means if none of your WHEN expressions are matched then it returns NULL. My guess is there's an issue in your logic below that causes date1 and date2 to always be equal, which would miss both your WHENs in the first CASE and return NULL.


0

as this is standard SharePoint maintenance the OP is unlikely to be responsible for the code or be able to interfere in it. What they could influence, is the time it is run at. dbo.proc_MSS_DefragGathererIndexes should only really be run manually and after your first crawl but not generally otherwise. Can you confirm this proc has not been automated as ...


1

Using an intermediate table is the proper way to do this. With proper indexes and good maintenance it won't hurt performance much at all. Avoid the multi-column solution (refid1, refid2 etc.) - that's repeating groups and not properly normalised. Plus your queries will degenerate into a dog's breakfast of OR clauses and CASE statements, which will hurt ...


1

Probably best to: Import the entire CSV to a table using Import and Export Wizard. Run the query to pull the data you want from the table. Use the Import and Export Wizard to import to the other database. Clean up the CSV import, probably by truncating or deleting the table. Unless you want to use SSIS? Then you can design a package to do it easily ...


0

Not knowing much else about your problem, if your database is online and you're not using anything that may be actively using part of the log - such as Mirroring, AGs, Replication, etc, you could attempt removing that part of the log from the sweep by changing to the simple recovery mode (if in bulk or full) and issuing a checkpoint. Then go back to your ...


0

Probably by design. Obfuscation of the highest level of username makes sense to me... security through obscurity.


0

In addition to the above suggestions, have you also duplicated the tempdb configurations? It may be that your previous server had multiple tempdbs configured, while your new one only has one. If you need to configure them, have a look here :


0

Your idea isn't that crazy. Ordering the primary key DOES reduce fragmentation, but this can be achieved with an IDENTITY column. However, ordered keys also has a drawback, namely the one described here: http://kejser.org/clustered-indexes-vs-heaps. Now, I can see how you would benefit from knowing WHEN the key was generated and that you therefore want pack ...


1

Following information was found for victim process from the deadlock graph Isolationlevel="read uncommitted (1)" for process that was killed as part of deadlock. lasttranstarted="2014-07-25T00:00:41.230" lockMode="X" procname="Search_Service_Application_CrawlStoreDB_3a7fefdbc44a48ef902a65ffb71c7d3a.dbo.proc_MSS_GetNextCrawlBatch" Problematic statement ...


0

My preference is to use an identity column for primary key and if required add a unique index on other fields if you need to enforce uniqueness. In my opinion, a primary keys job is to uniquely identify a single row of data. Integers do this more efficiently without risk of ambiguity. I dislike using varchars as pkeys or in joins as they can be affected by ...


-1

One way to do it - have a varchar column with a delimited list of additional references if they exist (123,458,658,587 etc). Then create a UDF to split that data if it exists, which you can use in SELECT queries. Should be easy to design the UDF to extract the data. Since it is rare it shouldn't hurt resources much. A varchar column should also mean minimum ...


0

A trigger is an option to do this. create trigger ins_updt_trig on cust for update, insert as begin print 'cust' end This will display a message when an update or insert occurs on 'cust' table. But I don't think this is a better option for a lot of tables because triggers are very difficult to manage.


0

For your first example: Update cust Set name = 'john' output 'cust' --, inserted.Name -- optionally, uncomment this field as well, to get the Key of the updated record. Where custid = '4001'


2

When I run a query with a bad plan the server slow down and all other query, That says they're contending for the same resource, and non problems on disk I/O doesn't say much about why the disk wouldn't be the resource they're contending for. Logically we know that a query with a "bad plan" is going to read a lot of data from the disk, perhaps ...


2

select obj.PK_ObjectID, obj.name, obs.timestamp, obs.note -- etc from Objects as obj join Pts as obs on obj.PK_ObjectID = FK_ObservationID order by obj.PK_ObjectID, obs.FK_ObservationID You're googling the wrong places, if I may say so. You yourself say you're new to SQL, and yet you've figured out how to implement a cursor instead of writing ...


1

Hmm, I agree with you, almost, and not with the folks who suggest using an arbitrary integer to identify your rows. Your primary key should be tid char(2), asof datetime, sequence smallint where you control the sequence. That's a perfectly fine primary key because it identifies the row. The burden is on the surrogate crowd to explain the ...


0

Make the outer joins inner joins, and enforce the relationships. Superficially, it looks like every PAMtx should have a proper transaction type and meter mapping anyway. If you can't do that -- because the relationships doesn't always hold -- try a UNION of an inner join to one (or two) WHERE NOT EXISTS queries for those rows that don't match. Also ...


0


1

The Log you posted seems incomplete, also it is not the log for SQL Server installation but for patch update. You can see in the log Requested action: Patch Anyways log is helpful because it shows below Product features discovered: Product Instance Instance ID Feature ...


0

You haven't installed SQL Server. You have only installed SSMS. I have checked if Microsoft SQL Server 2012 is in the Programs and Features list. This means that any SQL Server feature is installed. SSMS is one such feature. Exception's description is :SqlException was unhandled by user code.An exception of type 'System.Data.SqlClient.SqlException' ...


0

You can use CNAME in DNS. When you're done with GARDB1, rename it to something else (GARDB1_OLD). Create a CNAME in the DNS server called GARDB1 which should point to GARDB2008. It should solve your problem because GARDB1 will resolve to GARDB2008 now. Ideally what you need to have is a generic CNAME that you can just re-point to a new DB server each time ...


2

Think of the right answer in terms of the COALESCE command, which returns the first non-null value from a list. The prioritized list of recommendations are: Your tested and proven settings. Some folks have enough time to do repeated benchmarks of their own applications on their own hardware. That's fairly unusual, though. Microsoft's Fast Track Reference ...


0

I often use a SELECT to generate other SQL: select 'insert dbo.TEST(A, B) values (' + convert(varchar(20), A) + ', ''' + B + ''');' from dbo.TEST; Save the output and re-use as needed. If column B contains text delimiters you'll have to deal with this.


0

I'd say it's all based on the resource consumption (cpu / ram / concurrent disk activity) of each instance, as well as the overall health of the virtualization environment. Virtualization is all about resource queues. If it looks like physical environment might hit a bottleneck based on the cumulative resource consumption of three instances in one VM, it's ...


1

You can also use the bcp utility to import or export data. In your case use something like the following command: bcp "SELECT A, B FROM TEST" queryout Test.txt -c -T To insert data from the file into another table: bcp Database.Schema.Table in Test.txt -T –c Hope this helps as an alternative


-2

Figure out.Some simple thing I forgot......just use "as" select l.depart_code,l.depart_name,y.HR_NO,y.HR_NAME,m.PAY_SCOPE,x.per,x.com from psr_main m,hr_depart_data n,hr_depart_data l,( select calculate_time, employee_id, (accident_insure_per+job_insure_per) as per, (accident_insure_com+job_insure_com+zhi_insure_com) as com from ...


0

Are your indexes & tables maintained recently and are you statistics up-to-date? Fragmentation and wrong statistics can cause big problems.


0

If the tables were dropped, ApexSQL Recover can recover them even from databases in the simple recovery model. ApexSQL Recover can recover both table structure and table records On the other hand, ApexSQL Log cannot recover records lost when a table was dropped, it can only recover the table structure In case the records that were lost using DELETE (not ...


0

Just for fun, why not try this SQL instead, which rearranges things a bit: create table #t(name sysname) insert #t(Name) select Name from sys.databases where state_desc = 'ONLINE' except select name from ( values (cast('master' as sysname)), ('model'), ('msdb'), ('tempdb'), ('ReportServer'), ('ReportServerTempDB'), ...


0

Would This work? Got rid of case statement in where clause... SELECT name, OBJECT_ID(QUOTENAME(name) + '.[dbo].[_LOOK_UP_TABLE]', 'U') AS [LookUpID] FROM sys.databases WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerTempDB','1303VISBOC') AND state_desc = 'ONLINE' AND ISNULL(OBJECT_ID(QUOTENAME(name) + ...


1

With a CTE to generate some test data like so: with t1 as (select 1 as data union all select 15 as data), t2 as (select 2 as data union all select 14 as data), t3 as (select 3 as data union all select 13 as data), t4 as (select 4 as data union all select 12 as data), t5 as (select 5 as data union all select 11 as data) Try this: select t,data from ( ...


1

You could use dynamic SQL DECLARE @sql nvarchar(max) SET @sql = STUFF((SELECT ' UNION ALL ' + NCHAR(13) + 'SELECT TOP 1 * FROM ' + QUOTENAME(TABLES.TABLE_SCHEMA) + '.' + QUOTENAME(TABLES.TABLE_NAME) + NCHAR(13) FROM INFORMATION_SCHEMA.TABLES WHERE ...


1

Found the answer: http://blogs.msdn.com/b/sql_shep/archive/2012/06/21/sql-server-2012-license-core-limitaion.aspx My consultants installed the wrong version (CAL instead of core), so it's limited to 20 cores.


1

This is not a complete answer (I'm sorry), but maybe a starting point for further thinking. For a low number of tables UNION is the best approach IMHO. In special if the list of tables are not changing. For a bigger number of tables you might can think of using some T-SQL and iterate through a list of tables and building up your query with dynamic SQL -- ...


1

If you don't have a DBA chances are the server hasn't been configured according to simple but critical best practices. You can't really make a definitive diagnosis until you've done that. Engage and expert to assess what needs getting done. Then either make the changes or have them do it.


2

I will answer you question point wise like you asked No its not necessary that LSN of differential backup would match the LSN of full backup. LSN is basically a stamp of last records that accessed the log. You should have also included DatabaseBackupLSN/checkpointLSN and then you will note that DatabasebackupLSN of differential backup will match the ...


1

Make sure your group name returned matches (identically) to your actual workload group name. Some times, I've found, it's helpful to reconfigure even if it doesn't say it needs to be done. use master GO -- Create a test DB which our CF will be based on CREATE DATABASE RGDB; GO -- enable RG ALTER RESOURCE GOVERNOR RECONFIGURE GO CREATE FUNCTION ...


0

I think your CASE statements may be flawed as you're always going to have validity_name and validity_surname equal to 0, because the name cannot be 3 different things at once. When do you want the validity_name to equal 1? Regardless of that fact, I like the APPLY VALUES method of grouping on a computed column. Using AdventureWorks as an example, this ...


1

In the referenced link How to force the use of row locks? Paul White demonstrates that index reorganization cannot happen if that index has page locks disabled. You can, of course, reset Page locking back on before doing a reorganization and then turn it off again afterward: -- Allow page locks on Index ALTER INDEX indexname ON tablename SET ...


-1

I assume you use SQL Server because you used the tag: Add an index to [PAMTx].CustomerId Foreign keys are not automatically indexed in SQL Server.


0

From Page Compression Implementation: However, the metadata for the table indicates that page compression should be used. As data is added to the first data page, data is row-compressed. Because the page is not full, no benefit is gained from page compression. When the page is full, the next row to be added initiates the page compression operation. The ...


0

Clearly [doc_index] is the PK in tblDocument linked to by the FK's in all the other tables. While they may not be actually linked (i.e. the FK may be enforced in stored procs or code), it appears the database is actually normalised in terms of having PK/FK's. The first step though is to ensure that every other table that has [doc_index] has a corresponding ...


0

If you anticipate that your database file(s) will exceed more than 1TB in size you should configure the server to put the data files across multiple VHDs. How can spread my databases across multiple VHDs? Create one or more dynamic volumes consisting of multiple VHDs at the operating system level and put your database files on these volumes. Split your ...


0

I think Brent Ozar's post is spot on. I've been in a massively bloating DB project and had exactly the same issue you do here, and it just isn't that simple. Since it's better to at least do something - even if not really that accurate -, I'd set up the required tables and a job (or whichever other method you want, anything to just query the sizes and store ...


4

Sure you CAN do it, but why would you? To save a few CAST expressions? That seems a bit weak. The reasons to have auto incrementing surrogate int PK's are many: You pretty much never have to manage them with normal databases. Ever. Unless you are rebuilding tables or inserting data where you have to turn the identity off and on. But operationally it works ...


0

You can use a case statement for this, if it must be formatted in the database and you can't persist it. Select case when len(facilityCode) = 1 then '0' else '' end + facilityCode as facilityCode From YourTable It sounds like you are doing this as a FK, if so you can't have a FK constraint on a calculation. To make doing the join easier, you ...


2

This would most definitely not be preferred to an auto-increment integer field. For starters: Index width would be degraded significantly with your proposal. Just how do you propose to manage all those random 2-digit integers to enforce uniqueness. Have you thought of how much code would have to be written and maintained to implement this scheme. Won't ...



Top 50 recent answers are included