Saturday, March 16, 2013

[SQL Server 2008 issues] Read Committed_Snapshot

[SQL Server 2008 issues] Read Committed_Snapshot

Read Committed_Snapshot

Posted: 15 Mar 2013 06:14 PM PDT

"Read Committed_Snapshot" isolation level in sql has any negatives or disadvantages?

Collation settings

Posted: 15 Mar 2013 05:53 PM PDT

Hi all, Today i found that my reporting database and reporting tempdb are in different collation rom the normal databases. Can databases in a instance be in different collations .

Charindex,substring.....

Posted: 15 Mar 2013 10:21 AM PDT

I have a string column very long with multiple underscores in between. My requirement is to only select those columns where the string between second and third underscore matches a certain criteria. For instances lets say The Compare Values is "RIC" AND I have a row value DMV1004/343M.TR3432_PLC089_RIC_9843CLHere i need to check if the string between the 2nd and 3rd underscore is RIC which in this case is true.So i would need to compare and if true insert that value. How would i do that efficiently? thnx.

Database name is visible but not available.

Posted: 15 Mar 2013 05:02 AM PDT

When I reboot my server and log into SSMS my database is listed but it cannot be expanded. The table is not available. If I rightclick and take the DB offline then bring it online the database is then expandable and available. Server is VM with database on ISCI SAN on a volume.Suggestions Please!

How to create this result set

Posted: 15 Mar 2013 09:43 AM PDT

Hi,Hi have this two tablesTable Node:[quote]CREATE TABLE [dbo].[Node]( [Id] [int] NOT NULL, [Name] [varchar](50) NULL,PRIMARY KEY CLUSTERED ( [Id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY][/quote]Table Edge:[quote]CREATE TABLE [dbo].[Edge]( [FromNode] [int] NOT NULL, [ToNode] [int] NOT NULL, [Dist] [decimal](10, 3) NOT NULL, CONSTRAINT [PK__Edge__023D5A04] PRIMARY KEY CLUSTERED ( [FromNode] ASC, [ToNode] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOALTER TABLE [dbo].[Edge] WITH CHECK ADD CONSTRAINT [FK__Edge__FromNode__03317E3D] FOREIGN KEY([FromNode])REFERENCES [dbo].[Node] ([Id])GOALTER TABLE [dbo].[Edge] CHECK CONSTRAINT [FK__Edge__FromNode__03317E3D]GOALTER TABLE [dbo].[Edge] WITH CHECK ADD CONSTRAINT [FK__Edge__ToNode__0425A276] FOREIGN KEY([ToNode])REFERENCES [dbo].[Node] ([Id])GOALTER TABLE [dbo].[Edge] CHECK CONSTRAINT [FK__Edge__ToNode__0425A276][/quote]Data of Table node:1 Buenos Aires2 New York3 Liverpool4 Casa Blanca5 Cape TownData of Table Edge:1 2 6.0001 4 5.0001 5 4.0002 3 4.0003 4 3.0003 5 6.0004 3 3.0004 5 6.0005 2 8.000What I need is to create a procedure. The procedure receives a parameter with the startpoint and Endpoint and then calculates the possivel roots to the endpoint.for instance:If I create a procedure with the name "ReturnNodes" that receives the parameter 1 (startpoint) and parameter 2 (endpoint)then it need to show me all the ways that I have to go from the startpoint to the end point.ReturnNodes 1,3 should return the following resukt set:1,2,31,5,31,4,2,3Can someone help create this code?I have tryed but I can't find a solution...Thank you

Ways to replicate

Posted: 11 Mar 2013 09:39 PM PDT

Hello, I have a live db that users need to access it for reporting purposes. What is the best way to let them access ir without affectinglive? They need the recent copy of the db every minute. Is there any way of replicating the data somewhere else and be able to let the users access that without issues?Your help is much appreciated. Riri

How to find where sp_spaceused is being executed from?

Posted: 15 Mar 2013 03:30 AM PDT

I inherited an environment where a couple of times a day, we start getting a single spid blocking other spids which causes errors in the application. In researching it using sp_who2, I see the command as DBCC, the program as OSQL-32 and the execution as sp_spaceused @updateusage = 'TRUE'. This runs for 15 minutes or so and then the errors go away because the spid is no longer blocking.I don't know where this is being executed from? I have researched the jobs that are scheduled in Windows during that time and don't see it. I have looked at SQL schedules and don't see it. Is there an easy way to track down where this is coming from?

Setting rowcount also limits the available databases in the database drop down...

Posted: 15 Mar 2013 07:16 AM PDT

All, When setting the rowcount in a query in SQL Server Management Studio, the count of databases in the database drop down in SSMS also shrinks to the number in the "SET ROWCOUNT" statement.For example: SET ROWCOUNT 0 - Shows all databasesSET ROWCOUNT 2 - Shows ONLY the top 2 database on the serverIt also seems that the setting this in the "Tools/Options/Query Execution" has the same behavior.Is this the desired behavior since the drop down is using the same session as the query or just a bug in SSMS?Anton

how to combine and convert two integer columns to datetime

Posted: 15 Mar 2013 07:13 AM PDT

I have date stored in a column as integer type and time stored in a column as integer type. How do I combine and convert both columns into datetime type?For Example: Col A has 20130314 (yyyymmdd) and Col B has 123000 (hhmmss). My Output should be 2013-03-14 12:30:00

OLE DB provider 'VFPOLEDB' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

Posted: 24 Sep 2011 06:43 PM PDT

hello all.I want to use vfpoledb provider in sql server 2008 and windows server 2008 and download vfpoledb from http://www.microsoft.com/download/en/details.aspx?id=14839and run this query :INSERT INTO OPENROWSET('VFPOLEDB', 'dBase IV;Database=C:\Temp;','select * from dskwor00')select * from testbut get error:Cannot create an instance of OLE DB provider "VFPOLEDB" for linked server "(null)".then exec this :sp_configure 'show advanced options', 1reconfigure gosp_configure 'Ad Hoc Distributed Queries', 1reconfigure gosp_configure 'show advanced options', 0reconfigure goUSE [master]GOEXEC master.dbo.sp_MSset_oledb_prop N'VFPOLEDB', N'AllowInProcess' , 1GOUSE [master]EXEC master.dbo.sp_MSset_oledb_prop N'VFPOLEDB' , N'DynamicParameters' , 1GOnow get error:OLE DB provider 'VFPOLEDB' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.what do i do for solve this error?is there vfpoledb for win server 2008?please help me.

Why is CDC returning Insert/Delete when I UPDATE a row in a table?

Posted: 15 Mar 2013 06:11 AM PDT

This is my second post asking this question. Hopefully someone will respond this time. I am experimenting with using CDC to track user changes in our application database. So far I've done the following:[code="sql"]-- ENABLE CDC ON DV_WRP_TESTUSE dv_wrp_test GO EXEC sys.sp_cdc_enable_db GO -- ENABLE CDC TRACKING ON THE AVA TABLE IN DV_WRP_TESTUSE dv_wrp_test GO EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'AVA', @role_name = NULL GO-- UPDATE A ROW IN THE AVA TABLE FROM SSMSUPDATE AVA SET AvaDesc = 'Test3' WHERE AvaKey = 119-- GET CDC RESULTS FOR CHANGES TO AVA TABLEUSE dv_wrp_test GO SELECT * FROM cdc.dbo_AVA_CT GO--RESULTS SHOW OPERATION 3 (BEFORE UPDATE) AND 4 (AFTER UPDATE) CORRECTLY--__$start_lsn __$end_lsn __$seqval __$operation __$update_mask AvaKey AvaDesc AvaArrKey AvaSAPAppellationID--0x0031E84F000000740008 NULL 0x0031E84F000000740002 3 0x02 119 Test2 6 NULL--0x0031E84F000000740008 NULL 0x0031E84F000000740002 4 0x02 119 Test3 6 NULL[/code]The results shown above are what I expect to see. My problem occurs when I use our application to update the same column in the same table. The vb.net application passes a Table Valued Parameter to a stored procedure which updates the table. Below is the creation script for the stored proc:[code="sql"]SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOif exists (select * from sysobjects where id = object_id('dbo.spdv_AVAUpdate') and sysstat & 0xf = 4) drop procedure dbo.spdv_AVAUpdateGOCREATE PROCEDURE [dbo].[spdv_AVAUpdate] @AVA AS tvpAVA READONLY -- table valued parameterASDECLARE @ErrLogID AS INTEGERBEGIN TRY SET NOCOUNT ON BEGIN TRANSACTION UPDATE AVA SET AVA.AvaDesc = TVP.AvaDesc, AVA.AvaArrKey = TVP.AvaArrKey FROM @AVA TVP INNER JOIN AVA AVA ON (AVA.AvaKey = TVP.AvaKey) -- Commit the transaction COMMIT TRANSACTION -- Return '' for success SELECT '' AS ErrMessageEND TRY BEGIN CATCH -- Roll back any active or uncommittable transactions IF XACT_STATE() <> 0 BEGIN ROLLBACK TRANSACTION END -- Log the error into the ErrorLog table EXECUTE dbo.uspLogError @ErrLogID = @ErrLogID OUTPUT -- Retrieve logged error information. SELECT dbo.ErrorLog.* FROM dbo.ErrorLog WHERE ErrLogID = @ErrLogID END CATCH GOGRANT EXECUTE on dbo.spdv_AVAUpdate TO publicGO[/code]When I look at the results of CDC, instead of operations 3 and 4, I see 1 (DELETE) and 2 (INSERT) for the change that was initiated from the stored procedure:[code="sql"]-- GET CDC RESULTS FOR CHANGES TO AVA TABLEUSE dv_wrp_test GO SELECT * FROM cdc.dbo_AVA_CT GO-- RESULTS SHOW OPERATION 1 (DELETE) AND 2 (INSERT) INSTEAD OF 3 AND 4--__$start_lsn __$end_lsn __$seqval __$operation __$update_mask AvaKey AvaDesc AvaArrKey AvaSAPAppellationID--0x0031E84F000000740008 NULL 0x0031E84F000000740002 3 0x02 119 Test2 6 NULL--0x0031E84F000000740008 NULL 0x0031E84F000000740002 4 0x02 119 Test3 6 NULL--0x0031E84F00000098000A NULL 0x0031E84F000000980003 1 0x0F 119 Test3 6 NULL--0x0031E84F00000098000A NULL 0x0031E84F000000980004 2 0x0F 119 Test4 6 NULL[/code]Does anyone know why this might be happening, and if so, what can be done to correct it? Also, is there any way to get the user id associated with the CDC?Thanks,Gina

BULK Question

Posted: 15 Mar 2013 12:34 AM PDT

I export some data from a query into a text file. Then I create a temporary table and bulk insert the data from the text file.Most all is inserted properly - just a few aren't.For example (×Ϲ⻪ÓîÆ´ÒôÊäÈë) gets messed up during the bulk insert. It comes like (+ù+Å-¦+ó-+-¬+ô+«+å-¦+Æ+¦+è+ñ+ê+½).(×Ϲ⻪ÓîÆ´ÒôÊäÈë) exist in the text file and in the database, but when I check in temporary table it isn't there.declare @file sysname;SET @file = 'C:\Table.txt' SET nocount ON CREATE TABLE #tempfile (Column1 varchar(600)) EXEC ('bulk INSERT #tempfile FROM "' + @file + '"') SELECT DISTINCT *FROM #tempfileWHERE Column1 = '×Ϲ⻪ÓîÆ´ÒôÊäÈë' ORDER BY DisplayName0 ASC DROP TABLE #tempfile

Extended Events

Posted: 13 Mar 2013 03:03 AM PDT

I only recently discovered Extended Events. Why? I don't know!I jumped in and learned how to use it and then I let the rest of our sql team in on the discovery. One of the team members quickly stated that it's worthless and is too difficult to use and read, and about the only thing it might be good for is locating long running queries. I thought it was easy to create scripts using 2012 and also easy to use ssms to read the xel in table-format.Am I just dealing with a case of Debbie Downer here or am I just too excited with the shinny-toy? Perfmon seems to be the tool of choice for, Debbie.Anyone else think that this is a great tool?What types of things do you prefer to use it for?Thanks!!

SQL 2008 - Min and Max server memory

Posted: 15 Mar 2013 12:06 AM PDT

For SQL 2008, the min and max server memory settings are limited by total memory. If my server has 4gb memory and 12 processrors whats the max value i can give?

Xe not getting data for sql_text (inputbuffer)

Posted: 15 Mar 2013 03:09 AM PDT

We have an event session in which we are trying to capture the stored proc executed along with the parameters passed. Session here:[code="sql"]--Using a comparator predicateIF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='InputbufferTrace') DROP EVENT SESSION AuctionTrace ON SERVER;CREATE EVENT SESSION AuctionTraceON SERVERADD EVENT sqlserver.sp_statement_completed( ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.client_pid, sqlserver.session_id, sqlserver.sql_text, sqlserver.tsql_stack) WHERE (sqlserver.client_hostname = 'HostName') ),--ADD EVENT sqlserver.rpc_starting(-- ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.client_pid,-- sqlserver.session_id, sqlserver.sql_text, sqlserver.tsql_stack)-- WHERE (sqlserver.client_hostname = 'hostname')-- ),ADD EVENT sqlserver.module_end ( ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.client_pid, sqlserver.session_id, sqlserver.sql_text, sqlserver.tsql_stack) WHERE (sqlserver.client_hostname = 'HostName') )--,--ADD EVENT sqlserver.sp_statement_completed(-- ACTION(sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.client_pid,-- sqlserver.session_id, sqlserver.sql_text, sqlserver.tsql_stack)-- WHERE (sqlserver.client_hostname = 'hostname')-- )ADD TARGET package0.ring_buffer( SET max_memory=4096)with (track_causality = on, MAX_DISPATCH_LATENCY = 1 SECONDS)GO [/code]We are confident through testing that we are getting the event information for the proper requests, but it always says that the sql text is unavailable. Through testing, we can get this in profiler. Any thoughts on how to really get this inputbuffer information through extended events? Is it a 2008 R2 issue?

Dynamic Bulk Insert executing as background task?

Posted: 15 Mar 2013 01:43 AM PDT

Question.. the following code:[code="sql"] set @strSQL = 'bulk insert MYServer..[' + @tablename + '] from ''\\mylocation\subfolder\' + @tableName + '.dat'' with (fieldterminator = ''|'');' exec sp_executesql @strSQL print @tableName[code]will run for a while and then spit out 20 or 30 table names...then run for a while and then spit out 20 or 30 more names.......as it runs through my list of files to import.Can someone tell me why it's not operating in a sequential order? ie, 1. bulk insert statement created, 2. run bulk insert statement, 3. PRINT THE DAMN TABLE NAME before going back to step one.Is this a dynamic sql issue or a bulk insert issue or a completely unrelated issue?

Can't import Excel xml format

Posted: 15 Mar 2013 02:03 AM PDT

I'm trying to select a Excel XML but my code doesn't work. The file i'm trying to import looks like this:----------------------------- 1 Unit 1 2 Unit 2 3 Unit 3
---------------------------My code looks like this select @xmlData = cast(c1 as xml)from OPENROWSET (BULK 'C:\Test2.xml',SINGLE_BLOB) as T1(c1)SELECT ref.value('Cell[1]', 'nvarchar(255)') AS UnitID, ref.value('Cell[2]', 'nvarchar(255)') AS UnitName FROM @xmlData.nodes('/Workbook/Worksheet/Table/Row') xmlData( ref )I works fine if I strip it down to this:------------------------------------------------ 1 Unit 1 2 Unit 2 3 Unit 3
---------------------------------------Is there any way to get it to work or is it because Excel's XML format is invalid?

secondary database is sink

Posted: 14 Mar 2013 11:02 PM PDT

secondary database in logshipping is sink how can i resume ?

Security Problems after SSRS Domain Migration

Posted: 14 Mar 2013 09:41 PM PDT

Morning all,I recently carried out an SSRS Migration from one domain to another.I used the backup and restore method, and all reports are generating fine and all access for existing users appears to be fine.However, there is one (fairly significant) problem;Some users appear to have been duplicated (seems that a problem with new SIDs being generated / mapped for each domain user affected, and this has resulted in me being unable to edit the security on the site levels/folders where this is a problem.I can neither add, amend or delete users and was advised to delete the first instance of a user to correct this, but it seems this has to be ruled out, due to the fact that even the top level folder contains duplicate users, and an error occurs when I attempt to delete thr user via the site security.The original error was:"Response is not well-Formed XML"The error when I try to delete a user now in one of the affected folders is;"role assignment is not valid. The role assignment is either empty or it specifies a user or group name that is already used in an existing role assignment for the current item. (rsInvalidPolicyDefinition)"I tried amending the ownership and modifiedby entries in the database for one of the specific users to myself, and then deleted the user and policies assigned to him, but it unfortunately never resolved the problem (The user still remains in place with security entries on the site, even if deleted within the database).Has anyone else ever encountered this problem and is there a way of resolving this which doesn't mean having to resort to backing up the original db with the affected users stripped out, in order for a restore to be carried out.A lot of further configuration work and report imports has occurred since this was put in place.Thanks in advance for any responses.

update a column based on Ideal

Posted: 14 Mar 2013 08:26 PM PDT

hi,when the table is ideal or not access in sql server for some time say for 3 hours then i need to update the one column in that table as flag 1.i will update my last_modified_date when there is an select/update/insert/delete, so based on last_modified_date i need to maintain the session for 3 hours and close the same when it pass 3 hours.

Cmd Shell

Posted: 14 Mar 2013 09:58 PM PDT

Hi Team,Is there any query to find cmd shell is enabled to disabled.i want to know the current status.Please help...

restore backup

Posted: 14 Mar 2013 08:14 PM PDT

how to restore a database of size 30 gb with the backup size of 10 GB of disk space 15 gb in sql

down grade Service pack

Posted: 14 Mar 2013 09:17 PM PDT

is it possible to down grade SP4 to SP3 in sql 2005 ?what is the procedure for this

EncryptByPassPhrase depends on datatype?

Posted: 14 Mar 2013 07:58 PM PDT

Can i use EncryptByPassPhrase for column of any datatype? I tried it for using integer,varchar and nvarchar. it worked properly. But when i use it for column of type nchar(10) i am getting errorMsg 8152, Level 16, State 5, Line 1String or binary data would be truncated.The statement has been terminated.In the article http://technet.microsoft.com/en-us/library/ms190357(v=sql.105).aspx it is used only for varbinary. Does it can be used for any type of column because it is working for integer,varchar and nvarchar. Please clarify my doubts.Thank you

endpoints

Posted: 14 Mar 2013 08:16 PM PDT

what are end points in mirroring ?
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog