Wednesday, May 29, 2013

[SQL Server 2008 issues] Servers Consolidation and Linked Servers

[SQL Server 2008 issues] Servers Consolidation and Linked Servers

Servers Consolidation and Linked Servers

Posted: 28 May 2013 07:55 AM PDT

Hi,We are planning to consolidate 5 servers in a 2-node active/active cluster.Some of the servers have Linked server between them.After the consolidation, I guess Linked server between Instances inside same Server/Cluster won't be necessary anymore, but I can't figure out how i could substitute them so that instances keep communicating.Any idea ?Thanks,Dok

Can you delcare an operator?

Posted: 28 May 2013 01:57 AM PDT

Can you declare an operator?DECLARE @MYTEST AS VARCHAR(3)SET @MYTEST = '<>'

stored proc not completing

Posted: 28 May 2013 07:18 AM PDT

Hello - I have a stored proc that's not completing.I ran it once before and it completed in 1.5 hours but then I ran the same proc on Friday and it was still running when I came into work this morning (> 3 days).Have you ever encountered this behavior with a stored proc? Do you have any first impressions about what might be causing the stored proc not to complete? What next steps would you take to debug and fix the root cause?

transaction chunk sizes

Posted: 28 May 2013 06:58 AM PDT

how can i tell of transactions are written as 'all or nothing'...i realize they are acid, but for example, if there is an update that is going to affect a million rows, but the transaction is not broken down into say 100,000 row chunks, isnt going to take longer to do the update all in one gulp, instead of it being broken into ten units of 100 K each?Follow up question, is it best to do that chunking in a loop based on some increasing id indexed value, or failing that, maybe using values gleaned from dbcc show_statistics to determine appropriate chunking for the transaction size?thanks a lot

Searching stored procedure for table name but ignoring comments

Posted: 27 Nov 2010 08:43 PM PST

Hi All,Thanks to others who have posted helpful tips I have a way of searching through stored procedures to list those that reference a specific table (one way listed below ) but ideally I would like to exclude those table references that are listed in comments or hashed out and no longer part of the active body of the procedure ..Currently can't see a way to do this - has anyone got an idea on how to approach this or have actioned this themselves ? It may be this is just not possible searching through the sys tables ..but thought I would just ask Any feedback/tips much appreciatedThanks,Ralph SELECT DISTINCT SO.NAME FROM SYSOBJECTS SO (NOLOCK) WHERE SO.TYPE = 'P' AND SO.NAME LIKE @StringToSearch ORDER BY SO.Name

NOLOCK hint.

Posted: 28 May 2013 03:08 AM PDT

we have a high activity transactional database and we have put NOLOCK hints on all SELECT statements...for sometime now we are experiencing timeouts inspite of NOLOCK hintI thought with NOLOCK hint we should not get timeouts ?The same queries run OK from SSMS and we dont see issue with optimization..the problem occurs when WCFmakes hundreds of hits per min....[It is OK for us to to have dirty reads because of NOLOCK hints but we cant have timeouts...]

Risks of not upgrading from SQL 2000, SQL 2005

Posted: 28 May 2013 02:36 AM PDT

Hello - I am inheriting 5 servers to administer from a jack-of-all-trades DBA/developer in another department.2 of the instances are SQL 2000 standard and are worried that SQL 2000 is out of support.The jack-of-all-trades DBA/developer will still be involved but has no desire to upgrade the SQL 2000 instances. (DTS packages that would need to be rewritten and he is understaffed. Plus, its not broke so don't fix it.)I was told by my boss that we need to inform them of the risks of running an out of support version.I had a conversation with the developer and he said "if the servers are behind a firewall, then there are no security risks"Can anyone offer an educated response to the firewall statement? Any links or insights would be appreciated. ThanksDave

URL File Download from SSIS

Posted: 28 May 2013 07:39 AM PDT

I know this type of question has been asked many times but I can't seem to find a thread that fits my particular situation (or admitedly, perhaps I'm just tired of looking). So here's my deal, I have a URL that I get from a partner by calling another URL and feeding it some query parameters. That call returns to me a URL which is what I need to then go get the file I'm downloading. I have no problem getting that URL into a variable in my SSIS package. It ends up in a format like this:http://www.company.com/server/GetReport?[some KVPs]Needless to say, there are several key-value pairs after that question mark which denote the login information, the report ID that it's picking up, etc.If I manually take that URL and dump it into IE and hit enter, I get the standard "Open/Save/Cancel" prompt. If I open it, I see the XML that should be in my file in the browser. If I save it, I get the standard save dialoge box so that I can save the xml file.How do I do this in SSIS? I can create a file connection for where I want this XML file to be saved, but I don't see an option in the XML task to simply save the file from a URL. I'm not sure what the best way to do this is.Any direction would be much appreciated.

Procedure with Table-Valued Parameters in 2 databases ?

Posted: 28 May 2013 12:13 AM PDT

Hello! I created a type ( CREATE TYPE AS TABLE myType .. ) in my [b]2 databases[/b] to be passed as a parameter in a Procedure.Follows the example:declare @myType myType;INSERT @myType (a,b)SELECT a, bFROM [b]DataBaseA[/b]..tableA IF EXISTS(select * from @myType )BEGIN -- execute PD in other database EXEC [b]DataBaseB[/b]..PD_MyProcedure @myTypeEND;The following error is occurring:"Operand type clash: myType is incompatible with myType"But when I run the same database, the error does not occur ..Could you help me ?Thanks,Jose Anchieta C. JR

Invoke or BeginInvoke cannot be called on a control until the window handle has been created

Posted: 04 Nov 2009 06:44 AM PST

HiI am trying to install SQL Server 2008 Developer Edition x64 on a Windows Server 2008 VM. I run the installer for a standalone installation and this works fine for the setup support rules bit. That passes ok but then I get this error:TITLE: SQL Server Setup failure.------------------------------SQL Server Setup has encountered the following error:Invoke or BeginInvoke cannot be called on a control until the window handle has been created..------------------------------BUTTONS:OK------------------------------How can I resolve this? I am getting 2008 R2 CTP but this is just a CTP. My download is from DreamSpark.

If Index Seeks + Scans + Lookups = 0, okay to drop the index with a lot of updates on it?

Posted: 28 May 2013 01:46 AM PDT

We're trying to optimize some of our tables and notice quite a few of our indexes are getting updated frequently, but the combined total of seeks/scans/lookups is zero. Just to be safe, I'm only scanning indexes which have been updated greater than an arbitrary amount of time (say 100,000 updates sinces last restart or stats reset).I know some people have had issues in the past dropping indexes which had scans but no seeks, and their queries slow down immediately, forcing them to re-create or re-enable the index, but in my case, these indexes are not being used at all, efficiently or inefficiently.Thanks.

Long-running process

Posted: 27 May 2013 11:46 PM PDT

Hi,We have a third-party application with a SQL Server backend that imports the records we insert into a staging table. Usually, the count is around 4,000 records. When the database was on SQL Server 2005, the import process took only a few minutes. Ever since we moved the database to a SQL Server 2008 R2 instance, the application's import process takes 4-6 hours. No other databases were adversely affected. Do any performance-tuning/database administration experts have some ideas of what I can check? I've run a bunch of queries to try to see if there's a long-running query on the server, but there's nothing that looks out of place. In addition, the queries from the application shouldn't really have changed--only the location of the database itself changed.Thanks,Mike

Question in BCP - SQL 2008

Posted: 28 May 2013 02:20 AM PDT

IS there a way to use BCP out statement in a single query like (without using xp cmdshell)BCP ... out ....file1.csvBCP ... out ....file2.csv

how to get the text of isnull function in sql server

Posted: 27 May 2013 08:26 PM PDT

In Sql server to see the function text, we can use sp_helptext. can you please help me to view the text of the isnull (system defined function).

Cluster validation - Active/passive

Posted: 27 May 2013 11:59 PM PDT

I setup two nodes (node1 and node2 ) as active/passive clustered server. When I run the falilover cluster validation, I get below when validating network communication:Node NODE2 is reachable from Node NODE1 by only one pair of interfaces. It is possiblethat this network path is a single point of failure for communication within the cluster. Please verify thatthis single path is highly available or consider adding additional networks to the cluster.** Note: The ping is successful from both serversI also get Multiple adapters on node node2 have addresses on the same subnet.Is this OK? I can failover from one server to another successfully.Thanks,Lava

Making a copy of a table.

Posted: 28 May 2013 12:47 AM PDT

What is a good method to make a copy of a table.Only the table not the data.Including all indexes.Excluding referential constraints.Including other (domain) constraints.At the moment I generate the table, change the name of the table to the new name and use the generated script.Problem the indexes have the 'old' name.Not all indexes include the name of the table so only substituting the name of the table is not enough.[b]Any nice ways to make a empty copy of a table including indexes ?[/b]Thanks for you time and attention.Ben Brugman

Creating enough empty pages in the database.

Posted: 28 May 2013 12:55 AM PDT

I use the following script to create empty space in a database.(So that during an opperation the database does not have to extend itself).The script see below.I wanted this to share with this community, because I learn so much from here I want to make an efford to attribute to this group.If this is the wrong way to handle this.Or if there is a better way, please correct me.GreetingsBen Brugman[code="sql"]----------------------------------------------------------------------------------- Generate a number of rows with one row per page --------------------------------------------------------------------------------------- 20130528-- ben brugman---- To allocate a number of pages to the database.-- Create a table of the required size.-- The table is build with one row for each page. Number of rows therefore is the number of pages.-- At the end of the process the table is dropped. declare @longstring varchar(8000) = replicate('The quick brown fox jumps over the lazy dog.',90)declare @number_of_pages_needed bigint = 123456print datalength(@longstring)-- Hoeveel pages moeten er worden gevuld.;WITH L0 AS(SELECT 0 AS c UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0), -- 4 L1 AS(select 0 as x from L0 A, L0 B, L0 C, L0 D), -- 4 ^4 = 256 L2 AS(select 0 as x from L1 A, L1 B, L1 C, L1 D), -- (4 ^ 4) ^4 = 4 Giga L9 AS(Select *, row_number() OVER(PARTITION BY x order by x ) as nr from L2) -- voeg rijnummers toeSELECT nr as getal, @longstring large_field INTO Number_of_PagesFROM L9 where nr <= @number_of_pages_neededdrop table number_of_pages---------------------------------------------------------------------------------[/code]

SQL Server 2008R2 Evaluation edition download link

Posted: 27 May 2013 11:01 PM PDT

Hi, I am trying to find the free trail version of SQL Server 2008 R2 Evaluation edition but no success.I tried this linkhttp://www.microsoft.com/en-us/download/details.aspx?id=8158[u][/u] but the page is empty.Could someone help me out to find the location.Thanks....

Estimating space needed for database

Posted: 27 May 2013 11:21 PM PDT

Hello - is there a way to determine the disk space needed based on this request..."please determine if we could host a data mart in SQL Server that would consist of a minimum of two tables (perhaps more) with the largest table consisting of 200 columns and ten million rows with a record size of about 1500 bytes"Thanks in advanceDave

Query executed by user

Posted: 27 May 2013 08:19 PM PDT

On my DB Instance some user truncated all my data table.So is there a way to find the user who executed this query.Help :unsure:

SQL Server 2008 Business Studio

Posted: 27 May 2013 08:56 PM PDT

Hello Folks, I am looking out to learn about the business studio features in details in 2008 version. I am little confused about choosing the book. There are so many. I am totally new to BI and so looking for user friendly and step-by-step approach to move from easy to difficult subjects within. Also want to avoid the complex books written specically for people already having basic knowledge.Any help will be appreciated.

Sum Time

Posted: 27 May 2013 08:00 PM PDT

Hey,I have a column named TOTALTIME (datetime data type). This is used to store the amount of time something takes rather than a clock time.So a user presses Start on a timer and then presses Stop 10 minutes later, the value would be 00:10:00. 2 hours 10 minutes between pressing Start and Stop would be 02:10:00 etc. etc.Not my application by the way!I need to sum up these times, so based on my 2 examples the total would be 02:20:00.Seconds are NOT used, just hh:mm.Thanks

trigger for email alert

Posted: 27 May 2013 10:43 PM PDT

i want the trigger for email alert any user taken backup or restore and also anybody login in development server

Settings of Windows Server for SQL

Posted: 18 May 2013 05:35 AM PDT

Hello, Do you have any tips for settings of WS? Roles, memory, etc.. ? it can be different betwen performance? any link or references for interesting reading?thank you for response

Report not working on Report Server

Posted: 21 May 2013 05:48 AM PDT

I'm not sure if this is the right place to post this one, but maybe someone can help.I have a report that I built in Report Builder and it works perfect. I have uploaded it to the Report Server, but when I try to view it, I get an error with the query:An error occurred during client rendering.An error has occurred during report processing.Query execution failed for dataset 'DataSet1'.For more information about this error navigate to the report server on the local server machine, or enable remote errors Here is the query that the report is running:select HourOfTheDay, sum(case when TimeReceived between @startdate and @enddate then NumberOfOrdersInBatch else 0 end) as NumberOfOrders, AVG(case when TimeReceived between @startdate and @enddate then TimeToProcess else 0 end) as AverageTime, sum(case when TimeReceived between @startdate2 and @enddate2 then NumberOfOrdersInBatch else 0 end) as PrvWkNumberOfOrders, AVG(case when TimeReceived between @startdate2 and @enddate2 then TimeToProcess else 0 end) as PrvWkAverageTimefrom info.batchstats with (nolock)where TimeReceived between @startdate and @enddate or TimeReceived between @startdate2 and @enddate2group by HourOfTheDayorder by hourofthedayAny help is appreciated.

SQL Server Corporate Standards - Automated Compliance Checking

Posted: 21 May 2013 08:43 AM PDT

Does anyone know of a flexible automated SQL Syntax checker?I want to be able to analyse scripts before they are deployed to our test systems to check for certain things such as the fact that each statement has an appropriate error check after it.I want to make sure each statement is commented.I want to spot syntax that would be legal in an on premise SQL Server but not in Azure.I am expecting to configure my own rules.Does such a thing exist? I have started to write one but clearly its complicated because I am effectively having to start to simulate SQLs own parser so I can break down and analyse statements.Any feedback will be much appreciated (positive or negative).ThanksTim
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog