Friday, August 30, 2013

[SQL Server 2008 issues] Creating a database takes a long time due to model db size. Not sure why?

[SQL Server 2008 issues] Creating a database takes a long time due to model db size. Not sure why?

Creating a database takes a long time due to model db size. Not sure why?

Posted: 28 Aug 2013 07:46 PM PDT

Help! I am confused. The following scenario is happening on our system.I have a 2008 R2 SP2 two node cluster. The SQL Server service account is added to the "Perform volume Maintenance" local security policy option on both nodes.After SQL installation, I always set the model database size to 64 MB data, 64 MB log and set the autogrow sizes for both to be 64 MB , just so that new databases have something a little better than the SQL default. This seems to be causing an issue though:If I execute the following script:CREATE DATABASE [test1] ON PRIMARY ( NAME = N'test1', FILENAME = N'E:\Data\test1.mdf' , SIZE = 1048MB , FILEGROWTH = 65536KB ) LOG ON ( NAME = N'test1_log', FILENAME = N'K:\Logs\test1.ldf' , SIZE = 64MB , FILEGROWTH = 65536KB )The command takes 50 seconds to run.If I set the model database to be a smaller initial size: 2 MB data, 17 MB log, the same command completes in just 3 seconds. I am not sure why there is such a difference in time due to the size of the model database? I have never encountered this issue before.Any help would be great.Thanks.

Dynamic SQL and DATEADD with User Defined Variable in Number/2nd position

Posted: 29 Aug 2013 08:28 AM PDT

This syntaxSET @sql = 'SELECT * FROM <sometable> WHERE dateadd(hh, ''' + @tzoffset + '''), END_DATETIME) BETWEEN ''' + @StartDate + ''' AND ''' + @EndDate + ''''returns this errorConversion failed when converting the varchar value 'SELECT * FROM <sometable> WHERE dateadd(hh, '' to data type int.How do I get around this?

Would like to add data in table when sql m/c starts or shutdown

Posted: 28 Aug 2013 10:04 PM PDT

I would like to add data like time in a table in sqlserver when sqlserver machine is started or shutdown.

Create a stored procedure on multiple databases

Posted: 29 Aug 2013 05:37 AM PDT

How can I create sps on multiple database?

Commit

Posted: 29 Aug 2013 07:48 AM PDT

It has happened to me for the 2nd time, on two different servers but both were 2008 R2. I tried to do a little research and find out why this happened but there was nothing to be read.So, I start a transaction by [code="plain"]Begin Tran[/code]do my stuffcheck the affected tablesdecide what to do, usually by running [code="plain"]COMMIT[/code]After a while of doing so, sql gets timeouts and the system gets locked. I'm confused as I find out that I still have several uncommitted transactions. The result of running commit was "Command completed successfully".Can anyone tell me what was happening? Why these transactions did not commit even though commit completed successfully?

Hive insert with openquery

Posted: 29 Aug 2013 12:56 PM PDT

Hi,My question is based on this articlehttp://ayadshammout.com/2013/05/27/import-hadoop-data-into-sql-bi-semantic-model-tabular/Mentioned in the article is an example of using openquery to perform select statement on a Hive table through a linkedserver.I was wondering how can i also perform an insert statement with openquery?I understand that for example to insert into Hive is to use a Load command, like:[code="other"]load data inpath '/tmp/data.csv' overwrite into table tableA;[/code]How do i execute this with openquery? I've tried the example below and some slight variations but all I get in return were syntax errors[code="other"]insert openquery (linkedserverName, 'load data inpath '/tmp/data.csv' overwrite into table tableA;')[/code]Hope to hear from somebody on the correct syntaxThank you,Koh

Microsoft SQL 2008 R2 Backup and Restore

Posted: 29 Aug 2013 06:01 AM PDT

This is a warning to anyone running SQL 2008 R2.If you back up any of your system databases on version/build (10.50.3720) , they will not be able to be restored on anything except the same build, and IT is no longer available from Microsoft. This service pack came out on May 13, 2012. It was named, "SQL Server 2008 R2 Service Pack 2 Community Technology Preview (CTP)". On July 26, 2012, another service pack 2 came out, "SQL Server 2008 R2 Service Pack 2 (SP2) aka (10.50.4000)".If you try to restore a backup created from the May build, you will not be able to restore on a server running the June build, you will get the following error; "The backup of the system database on the device D:\sqlbackups\msdb.bak cannot be restored because it was created by a different version of the server (10.50.3720) than this server (10.50.4000)."Microsoft's work-around is to remove SQL, reinstall, then install the updates to the same build that the backup was created, but it is no longer available. Is anyone at MS using a brain?Hopefully, Steve Ballmer leaving will improve things, but the track record from MS has not been promising. My boss has been wanting to move to MYSQL, and although I have been trying to discourage it, these kinds of issues make it hard to find reasons to stay with MS-SQL.For the money we pay for SQL, there should be more value in the products they push out. I guess they expect us all, their paying customers, be be their beta testers.

Mult-Part Identifier cannot be bound??

Posted: 29 Aug 2013 02:08 AM PDT

Anyone have any ideas on why I'm gettingMult-part Identifier cannot be bound error on the followingThanksJoe[code="sql"]SELECT [rpt_v_ch_all clients].LName + ', ' + ISNULL([rpt_v_ch_all clients].FName, ' ') AS clientname ,dbo.[rpt_v_ch_all clients].Client_ID ,dbo.[rpt_v_ch_all clients].LName ,dbo.[rpt_v_ch_all clients].FName ,dbo.[rpt_v_ch_all clients].AdmitDate ,dbo.[rpt_v_ch_all clients].DischargeDate ,dbo.[rpt_v_ch_all clients].Code ,dbo.[rpt_v_ch_all clients].AbbrName ,dbo.[rpt_v_ch_all clients].ID AS Expr1 ,dbo.[JBD_V_CH_Info].ENDTIME ,dbo.JBD_V_CH_Info.poscode ,dbo.JBD_V_CH_Info.dmcode ,dbo.JBD_V_CH_Info.dmabbr ,dbo.JBD_V_CH_Info.posabbr ,dbo.JBD_V_CH_Info.CAD226 ,dbo.JBD_V_CH_Info.CAD204 ,dbo.JBD_V_CH_Info.CAD205 ,dbo.JBD_V_CH_Info.CAD207 ,dbo.JBD_V_CH_Info.CAD227 ,dbo.JBD_V_CH_Info.FullName ,dbo.JBD_V_CH_Info.BOX ,dbo.JBD_V_CH_Info.OCount ,dbo.JBD_V_CH_Info.SCount ,dbo.JBD_V_CH_Info.[Days Diff] ,dbo.JBD_V_CH_Info.RECORDED_SERVICE_VOIDED_MONIKER ,dbo.JBD_V_CH_Info.datepartofstarttime ,dbo.JBD_V_CH_Info.CLIENT_IDENTIFIER ,dbo.JBD_V_CH_Info.Expr1 AS Expr3 ,dbo.JBD_V_CH_Info.CIEffdate ,dbo.JBD_V_CH_Info.CIexpdate ,dbo.JBD_V_CH_Info.ID ,dbo.JBD_V_CH_Info.STARTTIMEFROM dbo.[JBD_V_CH_Info]RIGHT JOIN dbo.[rpt_v_ch_all clients] ON dbo.JBD_V_CH_Info.ID = dbo.[rpt_v_ch_all clients].ID[/code]

Bad results from 3 part update

Posted: 29 Aug 2013 05:05 AM PDT

I recently finished reading this article wrote by [i]Mr Jeff Moden[/i].[url=http://www.sqlservercentral.com/articles/T-SQL/68467/]http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]We wants to add "Multiples of 3" in our table like 3, 6, 9, 12. So we made a table for this goal with this structure:[code="sql"]create TABLE temp(RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, SomeInt INT, Expected INT) INSERT INTO temp (SomeInt, Expected) SELECT 0,3 UNION ALL SELECT 0,6 UNION ALL SELECT 0,9 UNION ALL SELECT 0,12[/code]Now we insert 3, 6, 9, 12 into our table with this query without any problem:[code="sql"]DECLARE @N INT, @Anchor INT SELECT @N = 0 UPDATE temp SET @N = SomeInt = @N + 3, --Adds 3 to N and updates SomeInt with N @Anchor = RowNum FROM temp OPTION (MAXDOP 1)[/code]But this query return unexpected results:[code="sql"]DECLARE @N INT, @Anchor INT SELECT @N = 0 UPDATE temp SET @N = @N + 1, --Adds 1 to N @N = SomeInt = @N + 2, --"Forgets" to do @N + 2 after first row @Anchor = RowNum FROM temp OPTION (MAXDOP 1)[/code][i]Jeff Moden[/i] mentioned "There's a very long winded explanation about this that I'll very gratiously step aside".And also he mentioned "YOU MUST HAVE AN "ANCHOR" COLUMN" like @Anchor = RowNum. He also said "there are some unpredictable cases where an error can occur without anchor"Now I want to know why? I want to know the exact reasons. I want to realize it logically.What makes 3 part update sometimes don't return appropriate results?Also why I have to use an anchor in my query while logically there is no need of it?Warmly thank you for your time and help

Error HRESULT E_FAIL has been returned from a call to a COM component. SSIS

Posted: 29 Aug 2013 04:34 AM PDT

I have an SSIS package that is connecting to a database using an ODBC connection. The connection is setup and tests successfully. If I create a simple Execute SQL Task and run:select top 1000 *from [MyTableData] where DateEntered >= GETDATE()-45it runs fine. If I run:deletefrom [MyTableData] where DateEntered >= GETDATE()-45Then I get this:failed with the following error: "Error HRESULT E_FAIL has been returned from a call to a COM component.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Any ideas? The connection must be fine because it will do a select just fine, but it won't delete.

Deleting empty files using ForEach loop container

Posted: 29 Aug 2013 03:23 AM PDT

I have a package that is building MANY flat files. Once the files are built, I would like to use a ForEach loop container, and delete ONLY the empty files. The package will produce both files with data and empty files. Can I do this in a ForEach? Thank you.

Trace - data quantity of query results

Posted: 29 Aug 2013 01:58 AM PDT

I have been experimenting with creating and replaying traces for a web application - investigating some performance issues. The trace works fine, using sp_trace_create, though the sp_trace_setevent values are a bit of a mystery, but seem to work.Now we have identified missing indexes etc, attention is turning to the quantity of data going from SQL Server to the web application.Is there an easy way to find the amount of data selected, number or rows/ record sizes? Could it be requested in those mysterious sp_trace_setevents?

SSIS (2008 R2) Fuzzy Lookup not working. Not matching similar names.

Posted: 29 Aug 2013 02:16 AM PDT

Hello,I am running a very simple SSIS package with a fuzzy lookup that is not working. I am RDP'ing to the server (Windows Server 2008 R2 Ent) with SQL Server 2008 R2. I simply want to create a listing of all customer names and indicate if they are similar to other names (for example, ABC Inc is similar to ABC Inc.). I am comparing on a single column (Name).When I run the package, it basically returns _Similarity = 1 for almost all names -- even though they are clearly similar. I have similarity threshold set to .7 and delimiters are space, carriage return, tab, and line feed. My data source is a table using OLE DB.Here are partial results to illustrate what is happening:Name _Similarity _ConfidenceABC CORPORATION 1 1ABC CORPORATION 1 1ABC CORPORATION OF AMERICA - CEJ502453 1 1ABC CORPORATION OF AMERICA - CEJ502870 1 1ABC CORPORATION OF AMERICA, 1 1ABC CORPORATION OF AMERICA, 1 1ABC CORPORATION OF AMERICA, 0.9875 0.5ABC CORPORATION/ MORVEN PARK 1 1Sorry about the poor formatting but as you can see _similarity is 1 for all but a single name. Based on previous uses of this tool, I would expect ABC CORPORATION to be 1 and the remainder to be < 1, depending on the similarity.I have found some articles online reporting similar behaviour but they seem to be having this problem when the data source is an Excel file. This is related to 32 bit office and 64 bit SSIS. I'm using OLE DB for my data source, not Excel.I've experimented with different reference table settings, etc. -- all to no avail.What am I missing.Any help would be appreciated.Let me know if I left any pertinent info out.Thanks,Brett
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog