Friday, March 1, 2013

[SQL Server 2008 issues] Searching for Solution

[SQL Server 2008 issues] Searching for Solution

Searching for Solution

Posted: 28 Feb 2013 04:26 PM PST

Hello All, I have one query (it may sound stupid though). We have one SQL Server 2008 (Microsoft SQL Server 2008 (SP2) - 10.0.4266.0 (X64)) and on my machine I've installed the client tools. We have some process where we create soem bulky temp tables after doing some heavy processing on sybase and thereafter through a java code the data in those temp tables in Sybase are transferred to SQL batchwise. I think this is not safe and efficient strategy and somewhat time consuming and also feel that it should be handled using SSIS packages (?). Can anyone suggest on that ?Secondly, if creating SSIS package is good option then will I have to uninstall the SQL client on my machine and install the developer edition (with business studio) ? Is there license requirement (we have license for SQL Server Enterprise Edition) ?

Unable to connec the site using sql crentials.

Posted: 28 Feb 2013 05:12 PM PST

we have a new application on to a server, which uses SQL 2008 ENT for itsUser is unable to connect from the app server using login credentials[Fri Mar 01 06:52:08 2013] [error] [client xx.2xx.xx.xxx] PHP Warning: mssql_connect(): Unable to connect to server: testsql in /na/testvol/eocs/int_consumer/database/live/mssql.php on line 8But we are able to connect the databse through SSMS uing the same login and password.Please help if any one faced this issue.Many thanks,

High fragmentation of indexes

Posted: 28 Feb 2013 01:57 PM PST

Hi all,I have a table Order with primary key ID column is uniqueidentifier data type on server B. Nightly, a synchronization job runs to synchronize orders from server A to server B. After that, I check the fragmentation of clustered index I see that the value is always so high. Does uniqueidentifier data type cause the high frag because SQL insert data into Order table with random mode?I also set FILLFACTOR = 80%Thanks,

SELECT INTO vs INSERT INTO

Posted: 28 Feb 2013 01:18 PM PST

Hi,I am trying to create a temporary table. It will contain approx 25 million rows to start with and will increase in future.I tried Option1: SELECT col1 into #tempT from tableand Option2: create table #tempT (col1 int)insert into #tempTselect col1 from tableWhile doing both I ran the following to get locking information. SELECT resource_type, (CASE WHEN resource_type = 'OBJECT' THEN object_name(resource_associated_entity_id) WHEN resource_type IN ('DATABASE', 'FILE', 'METADATA') THEN 'N/A' WHEN resource_type IN ('KEY', 'PAGE', 'RID') THEN ( SELECT object_name(object_id) FROM sys.partitions WHERE hobt_id=resource_associated_entity_id ) ELSE 'Undefined' END) AS requested_object_name, request_mode AS lock_type, request_status, request_owner_id AS transaction_id FROM sys.dm_tran_locks where request_session_id =52Question:1. I have read that SELECT INTO creates exclusive lock on objects in the system catalog. In fact, creation of any object would do that, howwever when SELECT INTO runs for longer then the catlog locking will be longer and at that point in time if you try to query system catalog then you might run into blocking issues.When i ran the query to get the locking information I see only schema locks on the table from which i am trying to read the data and possibly the new temp table that was being created. This happened for both SELECT INTO and INSERT INTO.Can someone help me uunderstand -1. How INSERT INTO is better as compared to SELECT ITNO when there are huge datasets expected. 2. Would this SELECT INTO also hamper the rebuild index and other db activities if the catlog is exclusively locked.

Can I call a UDF in another database?

Posted: 28 Feb 2013 12:19 PM PST

I'm trying to write a check constraint that checks that the current column value exists in a library table in a library database.[code="sql"]USE LibGOCREATE FUNCTION [dbo].[fnIsValidResource] (@gResource UNIQUEIDENTIFIER)RETURNS BITASBEGIN RETURN 1ENDGOUSE MaintDBGOCREATE TABLE [dbo].[Als]( [gPK] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL DEFAULT NEWSEQUENTIALID(), [grObjType] UNIQUEIDENTIFIER NOT NULL CHECK (Lib.dbo.IsValidResource(grObjType)=1))GO[/code]When I try to execute the CREATE TABLE command, I get the error:Msg 4121, Level 16, State 1, Line 4Cannot find either column "Lib" or the user-defined function or aggregate "Lib.dbo.IsValidResource", or the name is ambiguous.Can someone please explain why this doesn't work and how to make it work?Thanks,Greg

Can`t build an OLAP cube from Project Server 2010

Posted: 28 Feb 2013 03:37 AM PST

I try to build for first time the OLAP Cube and show me this erroro CBSOlapProcessingFailure (17004) - Failed to process the Analysis Services database DatabaseName on the n01wdpjs01 server. Error: Internal error: The operation terminated unsuccessfully. Server: The operation has been cancelled. OLE DB error: OLE DB or ODBC error: Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.; 28000; Cannot open database "ProjectServer_Reporting" requested by the login. The login failed.; 42000. Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'Project Reporting data source', Name of 'Project Reporting data source'. Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Project List', Name of 'Project List' was being processed. Errors in the OLAP storage engine: An error occurred while the 'Start Date' attribute of the 'Project List' dimension from the 'DatabaseName' database was being processed. . Details: id='17004' name='CBSOlapProcessingFailure' uid='2a5d679a-5a15-4457-afcc-b96198f51199' QueueMessageBody='Setting UID=00007829-4392-48b3-b533-5a5a4797e3c9 ASServerName=n01wdpjs01 ASDBName=DatabaseName ASExtraNetAddress= RangeChoice=0 PastNum=1 PastUnit=0 NextNum=1 NextUnit=0 FromDate=12/26/2012 00:00:00 ToDate=12/26/2012 00:00:00 HighPriority=True' Error='Failed to process the Analysis Services database DatabaseName on the n01wdpjs01 server. Error: Internal error: The operation terminated unsuccessfully. Server: The operation has been cancelled. OLE DB error: OLE DB or ODBC error: Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.; 28000; Cannot open database "ProjectServer_Reporting" requested by the login. The login failed.; 42000. Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'Project Reporting data source', Name of 'Project Reporting data source'. Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Project List', Name of 'Project List' was being processed. Errors in the OLAP storage engine: An error occurred while the 'Start Date' attribute of the 'Project List' dimension from the 'DatabaseName' database was being processed. '. Some Idea how correct this?Regards

Generate reports

Posted: 28 Feb 2013 10:18 AM PST

I have a question related to SSRS. What i do is after all ETL process i am generating a report off of a table that has numerous columns. I have a column in the table that store integer values. My requirement is that the report should be sent out to all the recipients only if a row in that column is greater than 0 Else don't send out any email at all. I have been googling for a while and i can't seem to find a solution. Any advise/direction would be much appreciated.Thanks.

having doubt in output of dbcc shrinkdatabse command

Posted: 28 Feb 2013 04:23 AM PST

Hi on executing dbcc shrinkdatabase we get following as result a>DbId, b>FileId, c>CurrentSize, d>MinimumSize e>EstimatedPagesexcept for first id didn`t get rest allfirst database can have many file and when execute shrinkdatabase then does sql server tries to shrink all files aor only onesecond why option c,d,e are expressed in number of 8 kb pages , are this outputs mere number or they are sizes in gb/md/kb and in order to get actual size we have to do some maths to decode it .Please clear my doubts

Help getting all the weeks between two random dates for each row

Posted: 27 Feb 2013 11:58 PM PST

HI AllI need some pointers/help in getting this doneI have some data sample below each row has two week numbers yyyyww 201301 or 201352. I need to build a row of the same data but with each week in it Starting dataItemNumber | StoreNumber | WeekStart | Week Finish123456 | 01 | 201250 | 201310123456 | 02 | 201301 | 201305654321 | 01 | 201308 | 201311REQUIRED FINAL FORMATItemNumber | StoreNumber | Week 123456 | 01 | 201250 123456 | 01 | 201251 123456 | 01 | 201252 123456 | 01 | 201301 123456 | 01 | 201302 123456 | 01 | 201303 123456 | 01 | 201304 123456 | 01 | 201305 123456 | 01 | 201306123456 | 01 | 201307123456 | 01 | 201308123456 | 01 | 201309123456 | 01 | 201310123456 | 02 | 201301123456 | 02 | 201302123456 | 02 | 201303123456 | 02 | 201304123456 | 02 | 201305etc....I tried doing something with tally tables but couldn't get anywhere i'm currently trying CTE but i cant see the light at the end of the tunnel.Any help appreciated !!!

Export Contacts from Outlook to SQL Server 2008

Posted: 28 Feb 2013 06:28 AM PST

Hi All, I would like to export all the mail id's which are available in my outlook inbox and store those id's into my SQL Server 2008 table.Is there any chance do like this?If Yes can you please share your ideas

Estimated Execution Plan Curiosity

Posted: 28 Feb 2013 01:31 AM PST

I have a nightly job the runs about 50 insert & update statements and takes a few hours. I ran the whole query's Estimated Execution Plan, and query #35 said it had a 75% cost relative to the batch. It is a simple update, with 2 clustered index scans. I tested the statement originally and it took 33 seconds, added a couple of indexes on the join columns and it took 2 seconds.But when I run the estimated execution plan again it says that query is now 88% relative to the batch.Seems odd (to someone not well versed in execution plans)

Using a pecking order for addresses

Posted: 28 Feb 2013 01:11 AM PST

Hello All,I have encountered a new problem that I haven't dealt with before. I have a table of individuals who have more than 1 address type. I would like to write a query that returns 1 address for each individual. This result set should include an address based on a pecking order. For this example, the pecking order should be Main > Work > SeasonalHere's a sample table:[code="plain"]IndName Account AddressType AddressLine1 AddressLine2 City StJohn 12345 Main 123 Main St. Boston MAJohn 12345 Work 45 Spring St. Boston MAJohn 12345 Seasonal 23 Ivaloo St. Portland MEJim 23456 Seasonal 14 Church St. Houghton METhe output desired is:IndName Account AddressType AddressLine1 AddressLine2 City StJohn 12345 Main 123 Main St. Boston MAJim 23456 Seasonal 14 Church St. Houghton ME[/code]Any thoughts on the most efficient way to approach this? Thanks in advance!-Ken G

Add Radio Button

Posted: 27 Feb 2013 08:55 PM PST

How to add Radio button in SSRS more than 3 radio button with submit option.Thanks

get the first and last day of any Year/Month

Posted: 25 Feb 2013 07:11 AM PST

Hi,i have a view with two columns, lets say SpecYear and SpecMonth, both are integer. How can I build two new columns with the first and last day of this year/month:pinch:ex.2013 02 => 2013-02-01 2013-02-28etc.thanx

Restore log stop at option

Posted: 28 Feb 2013 01:10 AM PST

Why do we use "STOPAT" option while restoring logs to database?Thanks

Error login packet

Posted: 28 Feb 2013 01:15 AM PST

Hi,We are using sql server 2008 with SP3.We are getting below alerts frequently on sql server.can any one help me why we are getting ths error."The login packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library"THanks,

Compare the rowcount

Posted: 28 Feb 2013 01:09 AM PST

hello World,I have table with date and rowcount like below.Time stamp Row Count2013-02-28 10:53:50.283 12112013-02-28 11:53:50.283 12122013-02-28 12:53:50.283 12132013-02-28 13:53:50.283 12132013-02-28 14:53:50.283 1213I am running the below command to retrive the information.select getdate(), 'Row Count' = count(*) from dba_backuplocationNow, i have to create a table with two columns Timestamp & Row count (say the table name is Rowcount_monitor) and insert the rowcount values every hour usinga sql server job. ( If we put my requiremnt is stored procedure and get that executed through job).Requiremnt :I have to monitor the table and compare the last three hours of rowconut and if the rowcount is same then i have send andatabase mail saying rowcount did not change from last three hours. We can use the above table as example where the last three rowcount did not change. Also we have have to delete the records if they are two days old.Can someone help on this immediately.

Accessing a cluster SQL Server instance without the instance name

Posted: 27 Feb 2013 11:44 PM PST

Our application is currently connecting to a SQL default clustered instance using a 'CNAME' or alias. I am in the process of building a new cluster and the new SQL instance has to be a named instance instead of a default instance. I would like to connect to this new named instance by just switching the CNAME to the new virtual server name. Essentially I would like to connect to the cluster named instance without using the instance name. See example below. Current – "SQLServer1" – no instance name since it is a default instanceNew – "SQLServer2\InstanceName"Alias/CNAME – "DBServer" currently pointing to "SQLServer1" and ideally would have it just changed to "SQLServer2". I found and tested this solution. http://trycatchfinally.net/2009/05/accessing-a-clustered-sql-server-instance-without-the-instance-name/ My initial tests didn't uncover any issues. Does anyone see any issues having a very important OLTP application connect to the database in this manner?

login_name shows up as blank in sys.dm_exec_sessions but not in sp_who2

Posted: 29 Aug 2011 06:03 AM PDT

When I probe [b]sys.dm_exec_sessions [/b](joining with other DMVs to get active-session info) I get the login_name column to be blank for a certain session.Yet, for the same spid, the Login shows up properly (not blank) when I execute "sp_who2 active".It also shows up properly in [b]sys.sysprocesses[/b].What could be the reason for the faulty output of [b]sys.dm_exec_sessions [/b]?

Query in SSRS

Posted: 27 Feb 2013 07:51 PM PST

hi All ,i have one requirement where the SSRS report is displaying only certain data of a column based on a filter on a date range ,whereas i should get all the data even though the date range is selected or not for that particular column ,how can we achieve this is SSRS report
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog