Monday, March 11, 2013

[SQL Server 2008 issues] Doubt in Data Warehouse design...

[SQL Server 2008 issues] Doubt in Data Warehouse design...

Doubt in Data Warehouse design...

Posted: 10 Mar 2013 07:14 PM PDT

Hi,We are creating a Data Warehouse for our ClientWe need to store a list of locations that our company.My Colleague says we will store the list of all the cities in the world in a table (this table contains 8 MIllion records roughly) but i am suggestion that we will use only the required data in that table (Not more than 1000).Which will be better option.Kindly Tell me the correct way to go.Note: This is a data warehouse and not a OLTP database.

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

Need Help In SSRS

Posted: 10 Mar 2013 06:30 PM PDT

Hi everyone i have an issue in SSRS report .i have created a new entry for one column of a table.whereas my report is still showing the old value .actually the values are picking up from the dataset which refers to a Stored Procedure.it should pick the recent value in the report from the table .what should i change in the report for this.=First(Fields!TitleReconciliation10.Value, "ReportTranslation")

REG compatability levels of a database

Posted: 10 Mar 2013 06:18 PM PDT

Recently i got new job. when i checked the production databases. some databases are in compatibility level 80 . some in 90 and some are in 100... i know that when they moved databases from previous versions to the new 2008r2 they have to change the compatibility level as-well . but they are in production for more than a year.. my question is can i change them to 100. does it any way going to affect the production.

Create CLUSTERED Index

Posted: 10 Mar 2013 05:28 PM PDT

HiI want to change the order of two fields in Pk of a table,I need to drop it and recreate it,but it takes a lot of time in my test server,how can I speed it up?Can I use MaxDop = Number of processors in main server?My table is about 400G.

SQL backup plan

Posted: 10 Mar 2013 04:01 PM PDT

what are the best practices for the backup plan.

Query comes to a crawl until executed using the WITH RECOMPILE option

Posted: 05 Mar 2013 12:37 AM PST

we have a stored procedure that will run just fine for a couple of hours but then somewhere during that time its execution plan seems to get confused and executing it then comes to crawlwhen executing the stored procedure using the WITH RECOMPILE option we see that query begins executing at what we consider a normal amount of time again and will usually for a while, but eventually it slows down to a crawl againcurrently, a simple fix for us is to leave the WITH RECOMPILE option in the stored procedurehowever, we'd like to get a better understanding and come to an actual resolution if possible rather than this "workaround"any insight is greatly appreciated, thanks in advance!

Diagnosing Page Latch Issue

Posted: 10 Mar 2013 11:49 AM PDT

I'm working with both a Vendor's DB and a purge script they've provided, and I need some outside opinions.This purge runs incredibly long, especially on our SAN. Our first thought, was because the purge was querying tables using non-indexed columns, especially several large tables the big one being 60m rows and 160GB. We added indexes and we definitely saw the performance improvements you'd expect when just purging other tables. Additionally, after about an hour of the purge, I can query the big table being purged and according to SQL Server receive the expected post-purge counts/values.The catch here is that 5-6 hours after the table "looks" purged, the job is continuing to alternate in pagiolatch waits. Checking the resource information confirms that it's the same table requiring the wait.It doesn't appear to be a script issue, per se. But, does this suggest anything that I can give back to my SAN admins or the vendor?Portion of the script (implicit transactions is off by default in this environment):BEGIN SELECT @table_name=TABLE_NAME,@duration=PURGE_DURATION FROM OLTPDB_PURGE WHERE TABLE_NAME = 'CHECKPOINTDATA'; SET @purge_date = CURRENT_TIMESTAMP-@duration; DECLARE m_cursor CURSOR FOR SELECT ID FROM CHECKPOINTDATA WHERE CREATEDATE < @purge_date OPEN m_cursor FETCH NEXT FROM m_cursor INTO @str_id; WHILE @@FETCH_STATUS = 0 BEGIN DELETE FROM CHECKPOINTDATA WHERE ID = @str_id; SET @record_cnt = @record_cnt + 1; IF (@record_cnt >=1000) BEGIN COMMIT; SET @record_cnt = 0; END FETCH NEXT FROM m_cursor INTO @str_id; END CLOSE m_cursor DEALLOCATE m_cursorEND

Repeating code over time period

Posted: 06 Mar 2013 12:40 AM PST

Hi Guys,I have a code snippet to run every month and have to take a union of current data and previous 11 months data and put in excel to prepare a report. I have to create a table with current month suffix and there are changes to be made to date variables.Any suggestion how I can avoid making changes to the code and generate reports based on the current month,(it should automatically create a table based on the current month and take a union with the previous 12 months data only)? Your help is appreciated!Regards,Prasanna

Replication ??

Posted: 10 Mar 2013 06:30 AM PDT

My database ''ABC" is configured with snapshot to Sub 1 and transactional replication to Sub 2. last week i drop transactional replication then i want to truncate all tables data keeping snapshot publication. i was not able truncate.Is this a bug ? after dropping snapshot replication i was able to truncate. Any clue?

SSRS Reporting assitance- consolidated report using 3 Stroed PROC

Posted: 04 Mar 2013 10:48 AM PST

Hi,I have 3 Stored Procedures 1) SP_JobPostingID,2)SP_WorkerOrder, and3) SP_WorkOrderRevisionNote- all the 3 have same number and signature of Columns, what I need to know is how develop an SSRS report using them combined in Microsoft Visual Studio.one way I can think of is creating 3 Datasets for the same Reportdata but wished to know if any other way out .ThanksDhananjay

Upgrading/moving datbases frm 2005 to 2008

Posted: 10 Mar 2013 04:17 AM PDT

After moving databases from 2005 to 2008, do we need to do run any queries (like update usage which we do in 200o to 2005).I will change frm 90 to 100 after necessary precautions. aything else i am missing?

Login Error

Posted: 09 Mar 2013 07:52 PM PST

TITLE: Connect to ServerCannot connect to "my machine name".ADDITIONAL INFORMATION:A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 2)Error Number: 2Severity: 20State: 0

Publish SSRS 2008 in LAN

Posted: 27 Feb 2013 03:51 PM PST

How to publish SSRS for internal alone.I have a project which needs to be circulated only to my team members using my hostname.What are the steps to deploy them.now i am able to do with http://localhost/Reportshttp://myhost-name/Reportswhere as if i see in another machine [b]http://myhost-name/Reports[/b] i am unable to get the details.Thanks

Change Article FILTER -- Where Clause -- in transactional replication

Posted: 06 Mar 2013 09:53 PM PST

Looking for best way to accomplish this. I need to simply change the Horizontal FILTER / WHERE CLAUSE to include a few additional filter values in a SINGLE transactionally replicated article.My publication consists of over 100 articles, some of them very large so I'd like to avoid regenerating the entire snapshot. - Should I DROP the existing object via EXEC sp_dropsubscription to drop article in the subscription, then DROP the article in the publication by UN-Checking the box --then Re-Add the article w/ the new Horizontal FILTER? or- Should I use EXEC sp_changearticle to CHANGE the existing article? If anyone has a solid, tested recommendation and perhaps a sample script to attach, it would be very much appreciated! thx in advance

sqlcode vs sp

Posted: 07 Mar 2013 12:44 AM PST

hi friends i have small doubt in sql plese tell me how to Determine when to use stored procedures vs. SQL in the code
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog