Tuesday, August 20, 2013

[SQL Server 2008 issues] Select specific set of data from text column

[SQL Server 2008 issues] Select specific set of data from text column

Select specific set of data from text column

Posted: 19 Aug 2013 03:45 PM PDT

Hi Experts,How can i select the data from a table where the data is as follows.Requirement: Select rows from the table where the log entry is as "Number of Files count= 'is greater than zero" The query should select the related rows of that whole process.Following sample data has two download events, one with 4 files and other with 2 files.The query output should select where the process files is greater than zero. ignore when no files were processedDateTime TextColumn2011-03-09 14:40:35.413 Prcessed Files count=02011-03-09 14:10:34.073 Prcessed Files count=2 --End 42011-03-09 14:10:34.390 Success download for Z345330 --32011-03-09 14:10:20.913 Success download for Z699090 --22011-03-09 14:09:56.667 Number of Files count=2 --Start 12011-03-09 13:39:55.280 Prcessed Files count=02011-03-09 13:09:53.940 Prcessed Files count=02011-03-09 12:39:52.770 Processed Files count=4 -- End 62011-03-09 12:39:53.190 Success download for Z294754 -- 52011-03-09 12:39:28.930 Success download for Z995876 --4 2011-03-09 12:39:03.183 Success download for Z940565 -- 32011-03-09 12:39:06.153 Success download for Z556433 -- 22011-03-09 12:38:39.730 Number of Files count=4 ----Start 1Sample Output2011-03-09 12:38:39.730 Number of Files count=4 -2011-03-09 12:39:06.153 Success download for Z5564332011-03-09 12:39:03.183 Success download for Z940565 2011-03-09 12:39:28.930 Success download for Z995876 2011-03-09 12:39:53.190 Success download for Z294754 2011-03-09 12:39:52.770 Processed Files count=4 2011-03-09 14:09:56.667 Number of Files count=2 2011-03-09 14:10:20.913 Success download for Z6990902011-03-09 14:10:34.390 Success download for Z345330 2011-03-09 14:10:34.073 Prcessed Files count=2

Creating a Test environment for a 10TB database

Posted: 19 Aug 2013 06:01 PM PDT

Hi All,Working on SQL Server 2008 R2 Enterprise Edt.Need to create a testing environment for this production server. One of the database's is 10TB in size. Database consists of multiple file groupsThe issue I'm having is there's not enough space on the Test server to do a full restore.Was thinking of creating a blank database then exporting data from Production to Test (all the data is not required)...but this will obviously effect the production server (this environment is 24/7)Just wanted to check if there are any other better ways of doing this...with no downtime on Production

xp_logininfo not finding user

Posted: 19 Aug 2013 04:47 AM PDT

I'm using xp_logininfo to find which domain group a user belongs to, and manipulate some controls in the application based on the results. I want to enable/disable various controls, depending on the privilege level of a user, so that a particular action is not possible for a user with insufficient privilege, rather than let them try it and have the app berate them them for the attempt.All the logic in the app works fine, but xp_logininfo is not locating one of my users. The command:[code="sql"]EXEC xp_logininfo 'nmp\zagorsekka'[/code]or [code="sql"]EXEC xp_logininfo 'nmp\zagorsekka', 'all'[/code]both give me the following error message.Msg 15404, Level 16, State 11, Procedure xp_logininfo, Line 62Could not obtain information about Windows NT group/user 'nmp\zagorsekka', error code 0xffff0002.[code="sql"]EXEC xp_logininfo 'nmp\zagorsekka', 'members'[/code]gives me nothing.The user DOES exist, and has been on this network for years - I just tried logging onto another computer using his credentials - no problem.The group to which he belongs is defined as a login and is mapped to the database. Also, another user in the same group works fine - returns the account name, type, privilege, mapped login name and permission path with no fuss. Is there something special that needs to be done with this SP? We do have a mirrored domain controller on site, while the main one is in another building across town.

How to find sql installed services on server

Posted: 19 Aug 2013 09:40 AM PDT

Hi Fnds,I am looking to find out ssis, ssrs, ssas installed on which server, i have several servers and i want find out those services on all servers. can any one help me?ThanksReally it'll be great help, if anyone can help?????Cheersmac

SQL backup on Cluster shared drive or Local drive.

Posted: 19 Aug 2013 03:46 PM PDT

I have a SQL server 2008 r2 cluster enviroment. Due to space constraints on the storage drive i had to move the backups from the shared storage drive to the local sql server. Now my server team has given me adequate space on the shared drive but from a best practice point of view i do not know whether it's better to keep the backups on shared drive or on the respective nodes local drive itself.Any suggestions ?

Google Analytics SQL Import

Posted: 08 May 2012 09:07 PM PDT

Hi AllI was just wondering if anyone has ever done a Google Analytics import to SQL via SSIS?A quick google brought up some C# scripts, a PHP web scraper and using the java client to export to CSV and then import it, but was wondering if anyone has managed to query the API and load it direct in SSIS to the DB?This was a task which was done a couple of years ago and I have been asked to re-ignite the flame to get it moving again and the previous way was to use xp_cmdshell to execute a file which loads it to CSV and imports it, which I have no trouble with, just wondering if anyone has a better solution.Thanks

sp_configure ‘user options’, 2

Posted: 19 Aug 2013 10:31 AM PDT

Hi,I accidentally ran the below command and I do not know what changes this command did. sp_configure 'user options', 2How to roll back the changes?Thanks

This implementation is not part of the Windows Platform FIPS validated cryptographic algorithms.

Posted: 19 Aug 2013 09:11 AM PDT

Hi Experts,Inititally when i tried to open Browser for ReportManager it gave me HTTP 500 Error.We opened web.config file and added as suggested in following link.http://myitforum.com/myitforumwp/2011/11/04/sql-srs-this-implementation-is-not-part-of-the-windows-platform-fips-validated-cryptographic-algorithms/Immediately i can see the report manager, with no HTTP Error. :)when i added rdl file and created data source to th reportmanager and tried to see report it is throwing me the following errorThis implementation is not part of the Windows Platform FIPS validated cryptographic algorithms. Any suggestion is appreciatedThanks

Backup during re-index

Posted: 19 Aug 2013 08:06 AM PDT

We have a database that at times can take up to 18 days to re-index. The database is in simple recovery model and runs a full backup every 12 hours to storage directory not on this SQL Server. Randomly the backup will fail with the following error messages to SQL logs.Error: 18210, Severity: 16, State: 1.BackupIoRequest::ReportIoError: write failure on backup device 'PATH\FileName.bak'. Operating system error 2(The system cannot find the file specified.).Error: 3041, Severity: 16, State: 1.BACKUP failed to complete the command BACKUP DATABASE dbname. Check the backup application log for detailed messages.Error: 3634, Severity: 16, State: 2.The operating system returned the error '64(The specified network name is no longer available.)' while attempting 'SetEndOfFile' on 'PATH\FileName.bak'.Error: 3634, Severity: 16, State: 2.The operating system returned the error '64(The specified network name is no longer available.)' while attempting 'FlushFileBuffers' on 'PATH\FileName.bak'.I feel this must be a network connection issue, but our network engineers are not seeing anything on their end. Is it possible re-index could cause something like this?

Restoring Database From Prod to UAT and Not Restoring USER,ROLES, SCHEMAS

Posted: 19 Aug 2013 08:51 AM PDT

Restoring Database From Prod to UAT and Not Restoring USER,ROLES, SCHEMAS what methods and scripts is available to do that.is there away to delete these users, roles and schemas before restoring into UAT server?how to fix user roles mismatch when orphan user scripts does not fix everything thanks I appreciate guys

SQL Server layout on a SAN

Posted: 19 Aug 2013 08:39 AM PDT

Ugh, every time I think I have my head wrapped around SAN technology; I realize I don't.SQL Server 2008 R2. Database in question is 3.9 TB. Here's what I know about my storage setup for my data files: I have 6 - 1 TB mount points. They are NetApp SCSI LUNs spread across 160 back-end spindles. I've read (in the NetApp documentation) that the Microsoft recommendation on multiprocessor servers is to have at least one LUN per logical processor.1) Does LUN equate to Mount Point? Or can I have several mount points but it really only be one LUN?2) In the case of a SAN; is there really any benefit to spreading data amongst multiple files in a single file group (placing each file on a different mount point)? Clearly if your SQL Server is using local hard disks the answer is yes; but I can't find anything conclusive on LUN setup. One one hand the data is already being spread across a bunch of drives so it seems like it wouldn't matter. On the other hand does having two specifically different files possibly help with performance.

Performance Issue: Where do you look?

Posted: 19 Aug 2013 07:55 AM PDT

I want to know your opinions when a customer says the database is slow today. Usually I would:*Check for blocking using the sp_who2*Check for statistics out of date (and perform update statistics)*Check for wait types*Identify fragmentation % (and then do an index reorg or rebuild accordingly)*Run profiler and identify deadlocks (or enable deadlock traceflags)*Run missing indexes script and check if any indexes are required*Run unused indexes script and check for indexes with 0 reads and delete them*Identify queries that are taking longer time to complete and tune them with DTA and add recommended indexes or statistics in a test env. first and move it to prod database*Check for IO bottlenecks by running the perfmon with appropriate counters from a remote machine*Verify with the network admins if there was a change in the network

virtual servers users lock each other

Posted: 19 Aug 2013 12:39 AM PDT

Hi Friends -thanks in advance for any help you can provid [URL=http://imgbox.com/adeQi0jc][IMG]http://t.imgbox.com/adeQi0jc.jpg[/IMG][/URL]As shown on the picture above, we have 5 virtual servers and 1 SQL server. (Windows Server 2008 R2 installed.)Users connect virtual servers via remote desktop.The problem is that users lock each other when they delete, update or select on servers. I checked sql profiler to see that sql server put the transactions in order. This ordering makes users lock each other. Is there a setting in the sql server to solve this problem?

DBA's facing customers...how do you handle?

Posted: 15 Aug 2013 12:50 AM PDT

I think this post is relevant here and I am curious to know DBAs thoughts on facing the toughest customers. How do you manage it?This is not something technical but would like to see everyone's experiences with their toughest customers:-)

unable to install sql management studio 2008 R2

Posted: 19 Aug 2013 05:53 AM PDT

Running Windows-7 Pro 32 bit. Trying to start SQL Server Agent. It is stopped & greyed out so cannot be started. Tried to install SQL Management Studio 2008 R2, get Validation Errors unable to install. Is there any quick way I can get the SQL Server Agent to run and/or install the above SQL Managemnet Studio?Thanks for any help

Find the Slow Running query.

Posted: 19 Aug 2013 03:01 AM PDT

Hi Team,am having a online application, while doing some actions like (select drop down, select any item from list), application is taking long time (10 seconds), previously it was done with in fraction of seconds.how to identify the query which is taking time, using below query but am not getting the exact result, SELECT TOP 10 a.total_worker_time/a.execution_count AS [High CPU Ave], SUBSTRING(b.text,a.statement_start_offset/2, (CASE WHEN a.statement_END_offset = -1 then len(convert(nvarchar(max), b.text)) * 2 ELSE a.statement_END_offset END -a.statement_start_offset)/2) AS SQL_Text, db_name(b.dbid) AS DatabaseName, object_name(b.objectid) AS ObjectName FROM sys.dm_exec_query_stats a CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) as b ORDER BY [High CPU Ave] DESC GO

Maximum row size of 8060

Posted: 19 Aug 2013 12:10 AM PDT

In SQL Server 2008r2, the maximum row size of 8060 is not applicable to [url=http://technet.microsoft.com/en-us/library/ms186981(v=sql.105).aspx] varchar(max), nvarchar(max), varbinary(max), text, image, or xml[/url] columns. I thought that this was a statement of fact, but I'm having some difficulties with an upgrade test for a backup of one of our customer databases to the latest version of our schema. The table in question has 23 columns and we're adding 2 new columns. 1 column is an XML column, all of the rest are fixed width columns (no TEXT, no IMAGE, no MAX size). I ran through the upgrade script for that table, it essentially drops the msrepl_tran_version column, adds two new columns (CHAR(1) and BIGINT) then adds the msrepl_tran_version (UNIQUEIDENTIFIER) column back with the default constraint of NEWID() [i](I know that none of this is necessary, it's something we have to do with our OEM version of the software because the guys we OEM to insist on ordinal positions of columns being the same and msrepl_tran_version always being the last column)[/i].Anyway, whilst adding the column back I get the following error: -[code="plain"]Msg 511, Level 16, State 1, Line 4Cannot create a row of size 8063 which is greater than the allowable maximum row size of 8060.[/code]I wrote a script to take a look at the data, as I assume that this is a data issue since upgrading a blank version of this schema to the latest one has no issue but can't see any issues. When I run the script like this: -[code="sql"]DECLARE @table VARCHAR(30) = 'TB_DIM_ITEM_PKEY_MAP', @idcol VARCHAR(20) = 'PKEY_MAP_URN', @sql NVARCHAR(MAX);SET @sql = 'SELECT ' + @idcol + ' , ROW_SIZE = (0';SELECT @sql = @sql + ' + ISNULL(DATALENGTH(' + NAME + '), 1)'FROM syscolumnsWHERE id = object_id(@table) --AND name <> 'XML_DATA';SET @sql = @sql + ') FROM ' + @table + ' ORDER BY ROW_SIZE DESC';EXEC sp_executesql @sql;[/code]The biggest row size is '130273', however if I uncomment the XML_DATA part to exclude the XML column then the biggest row size is '190'. So, I can't honestly see the issue. I've setup a test where I am inserting the data from this table to a copy of the table with the new definitions, one row at a time, to see if I can find the "bad" data. But since there is a lot of data in the table, this will take some time. I was wondering if someone else had come across the same issue? Or if someone knows more about it than me? ;-)

Job duration

Posted: 03 Feb 2012 07:53 PM PST

Hi ,Can any one tell me how to know the duration of job that was run last time.

can not create temp directory

Posted: 06 Aug 2013 09:37 PM PDT

Hi All,I am getting below error.can not create temp directoryPlease advice on this.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog