Tag Info

New answers tagged

2

You should use the value() method of the XML data type. If your XML structure looks like this: <DLR> <siteID>12345</siteID> </DLR> Your query would be select msg.value('(/DLR/siteID/text())[1]', 'int') as DOMAIN from dbo.YourTable But it does however not look like you have an XML column at all since msg like '<DLR%' is ...


0

If MS SQL Server is what you're using, it supports XPath query within the SELECT statement. See here for an example and here's an SO question. This will allow you to extract the numerals from within the xml irrespective of how many digits it contains. You can then CONVERT() it to int or whatever as needs be.


1

If you use log shipping, you can do the copy operation of the transaction log backups manually to take them from one server to the other.


2

I have tried to rebuild the indexes as most of them are sitting at 99%. after the re-build they go to 0. but with in a few minutes they are back at 99%. I guess I know the answer here and have seen many times. Can you please check if auto shrink is on for the databases. You can use below query select name,is_auto_shrink_on from sys.databases where ...


-2

SOLUTION i had two queries without outer join, which worked fast and correct on Microsoft SQL Server but both did not work out on H2 Database one of em because MSSQL uses + to concatenate strings and H2 uses CONCAT (which is crap because afaik this does not match the SQL specifications) my final solution is to create one single query in my code and find ...


3

Try changing sql.Parameters.AddWithValue("tb", "'" + table[2].ToString() + "'"); to sql.Parameters.AddWithValue("tb", table[2].ToString() ); hope this helps.


1

Your join has to describe a condition where rows in actual are matched to rows in test. In your example, you're just filtering rows using variables; the join is effectively a CROSS JOIN (a cartesian product), which is why you get SUM(actual)=122 on every row. The following query should solve your problem, with the assumption that rows in actual cannot span ...


0

I think the TPC-H benchmark (or TPC-DS) might be a good start. The council publishes the tool to generate data and the workload.


1

Thank you all of you guys for helping with this issue. The problem was the remappipenames in the registry was set to 0 instead of 1. After I changed the value back to 1, SQL server resource was able to get online using a non-default port. I also don't have to use an alias.


0

As said in the link from comment, It's a bug in SQL Server Management Studio SQL Server 2012 Management Studio spuriously forgets password. and SQL Server Management studio solution explorer doesn't remember SQL authentication password Some alternate solutions may work for you.


0

I think that password is only kept during the session with BIDS.


1

A default instance install (instance name of MSSQLSERVER and on port 1433) will disable the SQL Browser service because 1433 is assumed. On a clustered SQL install the cluster service will try to connect tot he instance and run a "select @@servername" in order to show that the server is up, but because it's a default instance it tries to use 1433 by ...


1

Posting this as an answer to address the core question: Is there a way to perform my history insert and then tell SQL Server to continue with the original INSERT, UPDATE or DELETE without writing those myself? No, it is not possible to have an INSTEAD OF trigger just perform the original DML for you. This is why they are called INSTEAD OF and not ...


1

You could just use a FOR trigger instead of an INSTEAD OF trigger. The records, as they were before the DML statement, are still in the deleted table (for updates and deletes). Everything will still be contained in an implicit transaction. In my opinion, INSTEAD OF triggers are really handy when you don't want to run the original DML, or on views.


1

You are defining an ordinary AFTER trigger. So use an AFTER trigger, not an INSTEAD OF trigger. The cases where you want to use 'instead of' triggers are, basically, none.


2

You can rewrite the LEFT JOIN to a subquery, but it may still compile as an outer join query - I have no knowledge of those aspects of the H2 platform. SELECT * FROM tSysNls WHERE nlsGuid IN ( SELECT COALESCE( --- first option: (SELECT de_AT.nlsGuid FROM tSysNLS AS de_AT WHERE de_AT.nlsClazz=x.nlsClazz AND ...


2

As Remus said, you're only option is to restore from your last known good backup. The reason the .MDF file will not re-attach is that the database was not in a consistent state when your recovery utility took its snapshot (I'm assuming that's how you retrieved the file), i.e. - there were active IO operations taking place. The only way file backup systems ...


3

You can do it on the fly without storing the stripped down name, but that would mean recalculating the strings (e.g. Lisa's Nights, Lisa's Night, LISASNIGHTS) each time a new entry should to be checked against an existing string. On the other hand, if your table also has a column for the STRIPPEDDOWNNAME, you can leverage SQL Server's constraints. Create ...


0

Although the Transaction Log file may be large is it full? have you run the DBCC SQLPERF(LOGSPACE) command to see what percentage of the file is in use. it could be that the backups have cleared the data out of the file and you could shrink the file to make space. This would still need you to find out why the log had grown so large.


0

I could be that the Linux TDS implementation is outdated compared with the SQL Server version Windows Azure uses. See: http://msdn.microsoft.com/en-us/library/dd340799.aspx. At least the FreeTDS docs at http://www.freetds.org/userguide/tdshistory.htm indicate they are not up to par with the latest versions. Try to connect using JDBC on your Linux machine for ...


0

First thing is after changing recovery model from full to simple you loose point in time recovery. Is this database production database(which i feel it is)? if so you are not doing correct thing. If database fails at this point in time you would loose point in time recovery and would loose data are you ok with it. If you switch from the full or bulk-logged ...


0

Yes you can change the db recovery model while it's in use.


1

Working from Aaron Bertrand's Script to create dynamic PIVOT queries in SQL Server and with a sample data set defined thus: if object_id('#ObtainedMarkEntry') is not null drop table #ObtainedMarkEntry; create table #ObtainedMarkEntry( StudentID int not null, SubjectID int not null, ObtainedMarks int not null ); insert ...


4

In SQL Server 2014 parallel DML for the SELECT INTO statement requires compatibility level 120. 110: A SELECT INTO statement always creates a single-threaded insert operation. 120: A SELECT INTO statement can create a parallel insert operation. When inserting a large numbers of rows, the parallel operation can improve performance. Note, that ...


0

As shown in your diagram, I think you have number of seats available per bus on BUS table let say in table TRIP_BUS you have column like, where for every trip you need to make entry with BUS_ID and TRIP_ID PK_TRIP_BUS -- make it auto incremented BUS_ID TRIP_ID ...Other column In table TICKET_SELL let say PK_TICKET_ID FK_TRIP_BUS -- PK of TRIP_BUS ...


0

I think this is cleaner as you only want to know if the result is not NULL. If this is SQL Server, create the habit of adding the schema as well "dbo.my_table", if you don't specify it, SQL has to figure out the user's default schema every time a query runs, avoid the extra work by making it explicit. if exists(select 1 from dbo.my_table)


4

The answer is right there on the last line of the error message: Sql client not registered You need to install the SQL Server Client Library on the machine in question. You can obtain that package from http://go.microsoft.com/fwlink/?LinkID=239648&clcid=0x409 Also, Isolation Level = ReadUncommitted may be problematic if you don't fully understand ...


4

I presume that you actually want to do something with the @category_name that comes out of the stored procedure. If so, you need to have something locally to capture it, as the other answers have shown: DECLARE @category_name VARCHAR(200); EXEC dbo.getFavoriteList @customer_id = 87381, @category_name = @category_name OUTPUT; PRINT @category_name; If you ...


2

Try this DECLARE @CategoryName varchar(200) EXECUTE getFavoriteList @customer_id = 87381, @category_name = @CategoryName OUTPUT;


3

You need to specify a variable for the OUT parameter, e.g. declare @catname varchar(200); execute getFavoriteList 87381, @category_name = @catname OUT;


4

If your retention period for backups is 1 month or something relatively limited, I would suggest the following approach. Retain the OLD NAS until the expiry of your retention period for those backups. Create your directory structure on the NEW NAS and redirect your backups there. Once your retention period on the OLD NAS expires, then drop use of the OLD ...


0

but I'm concerned this move will break SQL Server's awareness of where to find backup files in the backup chain. (It will try to find old backup files in in the old NAS location, not the new NAS location.) You need to first update your script to point to new location. Then you need to make sure SQL Server service account has read write permission on the ...


0

If the Pk has multiple columns, and you don't specify the columns in the Fk specification, then it may get them wrong. I chased a bug for a while that turned out to be that it guessed the columns wrong. For example: FOREIGN KEY (OneID, TwoID) REFERENCES MySchema.ForeignTable was interpreted as: FOREIGN KEY (OneID, TwoID) REFERENCES ...


1

Try this: Write an SP. You'll get all user tables SELECT o.name, o.id FROM sysobjects o WHERE o.xtype = 'U' and write a cursor on this query. So write an INSERT statement dinamically meanwhile you read your cursor. Meanwhile you browse your cursor apply a query to extract all fields about one table, in this way: SELECT c.name FROM syscolumns c WHERE ...


4

Linked Servers are not database specific, but provide a pipeline between two servers. Of course, the rights granted to the users will control which databases they can access. The account you are using naturally needs to be able to logon to both servers with enough permissions to execute your query. Yes, creating a Linked Server is an administrative ...


6

The behaviour is not explicitly mentioned in any of the official SQL Server documentation I am familiar with, but the 1992 Draft SQL Standard (section 11.8.2.b) does say: If the <referenced table and columns> does not specify a <reference column list>, then the table descriptor of the referenced table shall include a unique constraint that ...


-1

I just stumbled across his question on accident. For some reason I'm compelled to explain why this issue has occurred as it will build a strong database architecture concept as well as a better understanding of programming logic. Deletes in SQL server are sequential/serial. You cannot multi-thread deletes for many reasons. Rough example: thread one ...


0

Check the advanced properties of the job step this is happening on. Are "Output file:", "Log to table" and "Include step output in history" checked? If so, uncheck them and see if this issue goes away.


0

Try this in command promt In winodws authentication mode SQLCMD -E -S SERVERNAME -Q "BACKUP DATABASE DatabaseName TO DISK='D:\Backup\DatabaseName.bak'" In Sql Authentication mode SQLCMD -S SERVERNAME -U sqlUser -P Password then run the BACKUP DATABASE DatabaseName TO DISK='D:\Backup\DatabaseName.bak' in next step


3

You can use sqlcmd from the command prompt. It's default location for SQL 2012 is here :- C:\Program Files\Microsoft SQL Server\110\Tools\Binn For windows authentication run :- sqlcmd -SSERVERNAME or for sql authentication run:- sqlcmd -SSERVERNAME -ULogin -PPassword You can then run a backup sql statement. ...


1

Use Generate Script option in SQL Server Management Studio, Follow these steps Right click database Go to tasks Select generate scripts Select script data option to true Click next Choose tables Click next Select Output option to file or New Query window Click next and Finish your script wizard Run those generated script in Other SQL Server. Check ...


2

Copying ONLY data does not copy the keys and indices, since these are metadata describing how to treat the data. The following article suggests that you create a SQL Server 2008 instance, backup the SQL Server 2000 database and restore to the SQL Server 2008 database. Then backup the 2008 database and restore it to SQL Server 2014. (Be sure to check ...


2

SQL server 2005 SP3 which you have as of now(9.00.4035.00 ) is out of support. So first thing you should do is apply SP4. Access violation exception generally means some bug in SQL Server and its best to get this stack dump analyzed by Microsoft Engineer. You might get lucky with debugger but you would not be able to draw correct inference from it. Even if ...


5

This is probably caused by a blocking process. 3-5 times a day this is blocked by some update or other write that takes ~40 seconds. Your SELECT blocks until it can continue. If this is indeed the case, the problem can be identified using the Blocked Process Report Event. You will have to enable this event and set the threshold to ~35 seconds. See blocked ...


2

Is Sql server thread-safe? This issue is not with that SQL Server being thread safe or not.The issue is there with SQL Server Connection Pooling In Short:- Whenever a user calls Open on a connection, the pooler looks for an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new ...


1

From the database perspective, this can be configured with isolation levels. Be aware that every isolation has some scenarios which can be solved but also with a certain impact. I think the wikipedia article is very clear for a first step. See: http://en.wikipedia.org/wiki/Isolation_(database_systems) If you do not want/can't solve it with your database, ...


0

Ok, got it. I had to force the TCP1433 port in SQL Configuration manager... That was only that. I found it when trying to connect with a wrong password : SQLServer denied connection because of this wrong pass. So, the mistake was between the listener and the server supposed to accept the connection... which was the second one, because of the read-only ...


0

I did not see complete answer to your question so I would add an answer. I would like to know if there is a way to prevent SQL Server from maintaining the log file at all. No not possible every transaction in SQL server is logged in some way. Logging depends on recovery model you use. In simple recovery model almost enough information would be logged ...


3

There is no way to prevent SQL Server from writing to the transaction log. In the simple recovery mode, as soon as a transaction finishes its log records are marked so that they can be overwritten but the transaction is still written to the log. This means that a large transaction can still expand the log, if the current size of the log is too small. You ...


0

You can check the %age of log space used first by running command DBCC SQLPERF(LOGSPACE). The check space used column which seems high percentage, in this case it will be your database Shrink the log file by DBCC sHRINKFILE([dblogfilename],1) and run above command, to check how much it reduced.



Top 50 recent answers are included