Thursday, May 30, 2013

[SQL Server 2008 issues] Creating two databases at the same time, strange behavior

[SQL Server 2008 issues] Creating two databases at the same time, strange behavior

Creating two databases at the same time, strange behavior

Posted: 29 May 2013 07:03 PM PDT

Situation:Create a database in SQL Server 2008 R2, using the wizard. (with a big logfile so it takes some time)When the database is being created, start the wizard again to create another database.Don't click OK but look at the options. The recovery model is SIMPLE and the Compatibility level is 2005!!Has anybody seen this behavior also and is there an explanation for this behavior?thanks,Robbert

SQL Server 2008 Database Mail

Posted: 29 May 2013 05:04 PM PDT

Hi SQL Masters,I have a situation on my SQL Server hosted on Virtual machine (Oracle Virtual Box). I have a SQL Server there and i configured the Database mail via Wizard and 'Database Mail XPs'. My problem here is that the test mail could not succeed.Anybody could help on this situation?Thanks and Regards,dev1

Render CSV with different row header format,detail format, footer format, is possible?

Posted: 29 May 2013 02:18 PM PDT

I am just trying for fulfil the request of my task master. I know there are better tools (ssis) for this but I am going to ask any way. We are using SQL SERVER 2008 R2I am trying to create a ssrs report with csv rendering in the following format. H,20130501,0000022D, ABC,DDD, EEE, 10.50D, ABC,SDD, EEE, 9.50D, ABC,YDD, EEE, 200.00T,000003,220.00My task master will not be happy with doing all the concatination in a sproc. Has anybody done something similar? Is possible in SSRS ?

importing data from 2008 named instance to 2005

Posted: 29 May 2013 02:42 PM PDT

Hi All,I am planning to copy a data from SQL server 2008 Enterprise Edition to SQL server 2005 Enterprise edition and data needs to be refreshed everyday ones.What are the best possible options available to do this.While connecting through SSIS using Microsoft OLEDB provider FOR SQL server i am getting below error :Test connection failed because of an error in initializing provider. [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.Please help with any useful links or suggestThanks,Gangadhara MS

Is there an open source equivalent to sqlcmd.exe?

Posted: 29 May 2013 11:17 AM PDT

Is there an open source equivalent to sqlcmd.exe for Windows Server 2008 that will connect to SQL Server 2008 R2? I am looking for a portable .exe that does not require an installer so that I can connect to SQL Server from the command line to debug a server that I do not normally support. If possible, I would like to avoid installing any additional software (including the SQL Server Management Tools) on that server.

Importing text file(without delimiters) to SQL server.

Posted: 28 May 2013 07:45 PM PDT

Hi Experts,i have a text file with 1 -3 lakh records and 5 columns or so.... each column is seperated by tab delimiter. i need to import this file to SQLSERVER 2008.while importing this text file into SQL server, the first column in text file has 10 characters stringi need to split this string into 4 separate strings of fixed lengths (like1st string -2char, 2nd string - 2char, 3rd string - 3char, 4th string -- remaining chars) and insert it into 4 different columns in sql server.example i have a string in first column like INAPHYD00001 when i import this into sql server it should split like IN ------to be inserted into Country column in sql server.AP -----to be inserted into State column in sql server.HYD ----to be inserted into City column in sql server.00001 --to be inserted into LocalityID column in sql server.i heard i can use Bulk Copy but i'm not sure how to use it and also how to prepare a format file for this requirement.please help me in this regard.ThanksKishorekishorefeb28@icloud.com

CTE Help!

Posted: 29 May 2013 03:28 PM PDT

I have two tables,#allowance#actualwith sample data as below[code="sql"]create table #allowance(id int, free_quantity numeric(18,4))insert into #allowance (id, free_quantity)select 947, 8.0insert into #allowance (id, free_quantity)select 950, 12.0Create table #actual(id int, actual_quantity numeric(18,4), start_dt datetime)insert into #actual(id, actual_quantity, start_dt) select 947, 5.0, '1/1/2013'insert into #actual(id, actual_quantity, start_dt) select 947, 2.0, '2/1/2013'insert into #actual(id, actual_quantity, start_dt) select 947, 3.0, '3/1/2013'insert into #actual(id, actual_quantity, start_dt) select 950, 15.0, '3/1/2013'select * from #allowanceselect * from #actual[/code]The #allowance contains the total free quantity for each "id"The #actual contains the actual used quantities for "id" across different dates.Now, I need a "billable_quantity" column in the "#actual" table that is basically the used quantity less the free quantity. For example, the total free quantity for "id" 947 is 8. The #actual table should be updated with a billable_quantity which is the free_quantity taken from #allowance table and distributed across the #actual table for the same "id".The result of the #actual table should be:id actual_quantity start_dt billable_quantity947 5.0000 2013-01-01 0.0 (total actual qty 5.0 < 8.0 therefore 0.0)947 2.0000 2013-02-01 0.0 (total actual qty 7.0 < 8.0 therefore 0.0)947 3.0000 2013-03-01 2.0 (total actual qty 10.0 > 8.0 therefore 10.0 - 8.0 = 2.0)950 15.0000 2013-03-01 3.0 (total actual qty 15.0 > 12.0 therefore 15.0 - 12.0 = 3.0) I am sure CTE can be used for this but do not know how to distribute the free quantity across each row.Can someone please help?Thanks.

List of queries using MAXDOP

Posted: 29 May 2013 09:47 AM PDT

Gurus, currently, in our environment we configured our sql servers for MAXDOP of 4. here is our config:4 CPUS - each 8 Core - 16 logical processorsCan you please help me determine if it is possible to find which queries are utilizing MAXDOP for query processing?ThanksJagan K

TDE implementation

Posted: 28 May 2013 11:46 PM PDT

Hi All,we are planing to implement TDE database level security. Please confirm me if that there are no application level changes required to work with TDE. We are using ASP.NET code and it does not need to request encryption/decryption of data in and out of the database? Awaiting for your response.

How to keep two fields in sync with one another?

Posted: 29 May 2013 10:12 AM PDT

Hello All!Here's my conundrum...First off, the PK is IDNUMBER. I have a table, let's call it AL with an Email column, the AL table can be updated from a form that specific users have access to. Then there is another table, let's call it AD with a column for Address1 and a "qualifier" column AddressType. The AD table can be updated from a different form that different users have access to. I need to be able to update AL.Email when someone makes a change to AD.Address1 WHERE AddressType = 'Email'...AND...I need to be able to update AD.Address1 WHERE AddressType = 'Email' when someone makes a change to AL.Email. I have created triggers which will work one of the two ways but if I enable both, they compete and will not allow the data to be saved. I'm guessing that it is something simple that I am missing but I just can't wrap my head around it today. Anyone out there have any good ideas? Sample code is great if you care to share! I'm sure someone out there is doing this exact thing! Thanks in advance!

To Get First Day of the month!!!

Posted: 29 May 2013 09:30 AM PDT

Hi,Am trying to give dynamic date to SP that would be first of every month. 5/01/2013 for this month can any one help me with the sql expression? i was trying with this but it is not applicable for other months.declare@date dateselect@date=CAST(MONTH(GETDATE()) AS VARCHAR(2))+'/'+CAST('01' AS varchar(3))+'/'+CAST(YEAR(GETDATE()) AS VARCHAR(4)) print @dateThanks,

How to use two tables , each from different database into one stored procdure

Posted: 29 May 2013 06:09 AM PDT

My Soruce table A is in A1 database and Destincation table B is in A2 database , how I can call them in one stored procedure as in paramater value.http://www.sqlservercentral.com/articles/EDW/77100/ , in this it is using two tables from same database , but i want to know how we can use this SP between two database tables.Please help me, If someone has any idea.Thanks.

SQL Server 2008 R2 Backup Failed 'There is insufficient system memory in resource pool 'default' to run this query.'

Posted: 19 Jul 2012 08:40 PM PDT

Hi I have been having an issue for some time now with Backups failing on an instance of SQL Server 2008 R2 Enterprise Version 10.50.2500The error message is 'There is insufficient system memory in resource pool 'default' to run this query.'I do not believe that this error message is correect. I have been able to replicate this manually today during production (when 100s of queriers are running) on a tiny database. The databse is 24 MB. When I try to run a manual backup i get the same error message as above. I can confirm that there is no other activity against the database when trying to execute the backup. I have tried doing a manual backup of other databases (much larger) and they complete without any issues. When executing the backup query, it returns in around 2 seconds with the error.I can shrink, take off line, detach and run queries against this database but for some reason it will not let me back it up. I know restarting SQL Server will solve this in the short term but it will just start happening again.I found some arcticles and hot fixes on-line in relation to memory leaks in SQL Server 2008 & 2008R2 but they are for earlier versions than I am using. There are no available patches for SQL Server (according to windows update)Does anyone know what might be causing this.NB: The error logs contain nothing more than the error above so I don't think posting them will be any helpThanks in advance for any help.

GeoSpatial Data Types

Posted: 29 May 2013 01:50 AM PDT

Hi,I'm going crazy here. I need to store Google map locations in a SQL table.I create the table with two fields Latitude and Longitude - both with Geography data types.When I go to insert my Latitude value '38.80345' it says :"Invalid value for cell ..invalid cast from system.string to Types SQLGeography - type a value appropriate for the data type"what the...pretty sure I've done this in the past..what I'm am not doing right?

Divide the result of count function

Posted: 29 May 2013 04:09 AM PDT

Hi all,I have a question I hope the community can help me with. I have a query with count function and the result is 97, I need divide the result like this:Quant name30 cccccc30 cccccc30 cccccc7 cccccc

XE - more files than MAX_ROLLOVER_FILES

Posted: 29 May 2013 05:41 AM PDT

I have created an XE for deprecated features that writes to an asynchronous file target. Code below:[code="sql"]CREATE EVENT SESSION Monitor_Deprecated_Discontinued_features ON SERVER--We are looking at deprecated featuresADD EVENT sqlserver.deprecation_announcement(--Add additional columns to trackACTION (sqlserver.database_id, sqlserver.sql_text, sqlserver.session_id,sqlserver.tsql_stack, sqlserver.username, sqlserver.client_hostname)),--We are looking at discontinued featuresADD EVENT sqlserver.deprecation_final_support(--Add additional columns to trackACTION (sqlserver.database_id, sqlserver.sql_text, sqlserver.session_id, sqlserver.tsql_stack))--As a best practice use asynchronous file target, reduces overhead.ADD TARGET package0.asynchronous_file_target( SET filename= 'filepath' , metadatafile= 'filepath' , max_file_size = 5 ,max_rollover_files = 5) WITH(MAX_DISPATCH_LATENCY = 1 SECONDS, STARTUP_STATE = ON )[/code]The problem is that I am seeing more than 5 files on almost all of our servers. Any idea why the amount of files is greater than the MAX rollover?

Calling Two tables, each from different database in one parameterized stored procedure.

Posted: 29 May 2013 06:07 AM PDT

My Soruce table A is in A1 database and Destincation table B is in A2 database , how I can call them in one stored procedure as in paramater value.http://www.sqlservercentral.com/articles/EDW/77100/ , in this it is using two tables from same database , but i want to know how we can use this SP between two database tables.Please help me, If someone has any idea.Thanks.

Do you know of any Enterprise Database Inventory Management Systems?

Posted: 29 May 2013 05:07 AM PDT

Hello all,For the past half a decade, I as well as former and current colleagues have been custom developing an enterprise solution for inventory management as it relates to the SQL Server world, pretty much as a side task. This includes but is not limited to Servers, Instances, Databases, Applications, Windows Configurations, SQL Configurations, Database Sizes, Extended Properties, SQL Job History and quite a bit more. It's pretty awesome and serves its purpose; however time is never with us and we seem to never have enough time to dedicate towards ongoing enhancements and reporting. As we all know, daily life and operational tasks always takes priority over the bigger fun stuff! :(With the above said, I'm curious if anyone knows of, or can recommend any vendor based software out there that could possibly replace/enhance what we have. We have a massive SQL Server infrastructure and there is such a wealth of data out there that we truly need a robust and scalable application with trending/predictive reporting.Would appreciate any feedback.Thanks

Query question - clustered index internals

Posted: 29 May 2013 02:32 AM PDT

Hi all,I have a question I hope the community can help me with. I have a query that is running against a legacy system. I think I know what is happening internally, but am looking to confirm that. The query is the following:DECLARE @Stuff varchar(50) SELECT max(Date) AS CloseDate FROM TableName with (nolock) WHERE Product LIKE @Stuff + '%' The query is running against a fairly large table (250 million rows). When a user passes in a value for @Stuff that returns a date, the query runs subsecond. When a user passes in a value for @Stuff and there is no record to return for this particular value, it runs extremely slow. The clustered index (also the PK) is on the Date and Product fields (in that order). The execution plan indicates a clustered index scan for both runs. My therory as to why it runs fast when there is data is because since the clustered index is created on the date field first, it is able to return the first date it finds for the first Product returned by WHERE Product LIKE @Stuff + '%'. When there is no valid return for the value passed in by @Stuff, it has the scan the entire table only to return no data (and with 250 million records, this is why it runs very slow). Is this correct?

Resource Governor to Restrict a Windows Login/Group.

Posted: 29 May 2013 05:19 AM PDT

Hello All,We have a requirement to restrict a user/group on Production server to use limited resources, i.e. RMA/CPU. Do we have any option in SQL Server Resource Governor to accomplish this task.I kindly request to suggest me if we can do this using Resource governor.Thanks a lot in advance.Regards,Shyam.

What is the method you prefer for reading SQL Server books

Posted: 29 May 2013 04:06 AM PDT

[b]Please help to reach the best way[/b]

The best method for reading books

Posted: 29 May 2013 03:52 AM PDT

[b]Please answer me because your method in reading and learning will help me greatly [/b]

How to find all SQL Server Agent Jobs that are currently running by Query

Posted: 29 May 2013 02:22 AM PDT

The query below I searched from net, this returns jobs status. When I give job Id in filter of this query this will give job status of "Success" but actually my job is currently in executing stage. I want to get all jobs that are currently in executing status. Would you please help to modify this query or suggest me another one.Thanks[code="sql"]Use msdb go select distinct j.Name as "Job Name", --j.job_id, case j.enabled when 1 then 'Enable' when 0 then 'Disable' end as "Job Status", jh.run_date as [Last_Run_Date(YY-MM-DD)] , case jh.run_status when 0 then 'Failed' when 1 then 'Successful' when 2 then 'Retry' when 3 then 'Cancelled' when 4 then 'In Progress' end as Job_Execution_Status from sysJobHistory jh, sysJobs j where j.job_id = jh.job_id and jh.run_date = (select max(hi.run_date) from sysJobHistory hi where jh.job_id = hi.job_id )[/code]

Greater/Less than on text column

Posted: 29 May 2013 01:48 AM PDT

Hey,I have a column (INVOICENO) which is nvarchar(12). It's this datatype as an invoice could be a credit note, so xxCN, or debit note so xxDN.All other values are just integers, starting a 1. How would I go about showing only say invoices 10-20, including anything CN, DN etc.1011DN121314151617CN18DN1920etc... not my application (3rd party) so cannot change datatype.Not hopeful, but thanks!

Indexed view

Posted: 29 May 2013 12:55 AM PDT

Is it a good idea to put index on view? Some of the views are taking longer to execute and I am thinking about putting index on views. But doesn't it use the index on the underlying tables? How does index on view work?

Alter data logical name, add _data as defaul during database creation

Posted: 29 May 2013 01:44 AM PDT

Greetings -Is there a way you can change the default database creation settings to add _data in the end of the logical data file name using SQL Management Studio? The default logical data file name is set to database name when creating a new database, the log file name is _log. I know there is an alter database statement, but that is not what I am looking for. I would appreciate if someone shows me howto do so using careate database template.Thanks,Lava

SSIS - What is the best practice for creating a fixed length flat file.

Posted: 29 May 2013 01:36 AM PDT

I am running into the problem of SSIS putting the CRLF in random places at the end of the row. If we assume I am going to start from scratch, can I fix this issue in the Stored Procedure I use in my OLE source, or can I fix it on the SSIS side?I did see the following at MSDN but it did not make any sense to me since I don't see where to do this in the connection manager.[i][b]To make this work so that each row is exactly the same length, you may need to add a zero length column as the last one in the connection manager, and set the row delimiter on that one.[/b][/i]I did read as possibly using the advanced editor on the OLE adapter and adding a column there but how do I create a zero length column. If I send the header in the first row it is going to be at least 1 character.Any help would be greatly appreciated.FYI, I am using ragged right.

Checking References for a New DBA

Posted: 28 May 2013 07:39 PM PDT

Hi, I've recently taken on a role that involves overseeing some of our IT services, and one of them includes managing our CRM system. It's run on a SQL database system (2008) and I have to hire a DBA to manage it. I've had more than a few applications already and some of them have qualifications like MCITP.Do I need to contact Microsoft to verify if these are genuine?Thanks very much in advance.Chris

how to archive data based on on archive it self?

Posted: 28 May 2013 11:36 PM PDT

HiI have an archive tabel in Oracle. We copy this to a ms sql 2008 r2 database for datawarehouse purpose.The problem is that it only contains 90 days of records. I now need to archive this archive table. :-DThere is no unique key in the table. It log our load_id but one id may come several times a day even several days.I need to take a copy of the archive as is, and each day make it run thru the new transfer from oracle to figure out what has cahnged and add those lines to my copy.How to solve this the best way?BRDan

Ho to track the single user data in a multi user application ?

Posted: 29 May 2013 12:11 AM PDT

Hi Friends, I am trying to track a multi user application . I am one of the user. I want to check some tables. So i am trying to enter some data through application to check what are all the tables are affected. When i track that using SQL profiler, it is getting all the informations which are done by all the users... So i am unable to see the information only that i need. Is it possible to filer by user or with something? I am new to SQL profiler. SO if my question does not make any sense, sorry guys.Any suggestions would be really appreciated.

Finding Differing Module Definitions

Posted: 29 May 2013 12:22 AM PDT

So, I've been given the task of improving the performance of a database that was acquired via the purchase of a different company. It's not a pretty situation, but I'm slowly taking baby steps to improve performance.One of the first things I'm doing is making sure all the stored procedures start with SET NOCOUNT ON. So, I've gone through the scripts for a selected subset of the stored procedures and made sure they start with that statement. That's where my problems start...Each stored procedure is only under source control once. However, there are hundreds of instances of it; one for each instance of a customer database. And, of course, there are a few cases where the previous developers made modifications to a few specific customer's databases and didn't add those changes into source control. So, I'm afraid to just run the scripts I've got, as I might wipe out some customer specific changes.The first thing I'm doing is trying to identify all the different definitions for a stored procedure. Here's what I've got so far, and it works alright. But I thought I'd throw this out to the community and see if anybody has any comments or suggestions...[code="sql"]SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;SET NOCOUNT ON;-- To look for a different module, change @ModuleName to be the schema qualified-- name of the module you want to check.DECLARE @ModuleName SYSNAME = N'[lookup].[RetrieveColor]';-- Get a list of all customer databases.DECLARE @DatabaseList TABLE( Id INT IDENTITY(1,1) , DBName SYSNAME);INSERT INTO @DatabaseList( DBName)SELECT [name]FROM [sys].[databases]WHERE ([name] <> N'master') AND ([name] <> N'model') AND ([name] <> N'msdb') AND ([name] <> N'tempdb') AND ([name] <> N'ReportServer') AND ([name] <> N'ReportServerTempDB') AND ([name] <> N'LiteSpeedLocal') AND ([name] <> N'SQLdmRepository') AND ([name] <> N'testdb') AND ([name] <> N'BillingSystemController') AND ([name] NOT LIKE N'xdoc%') AND ([name] NOT LIKE N'%demo%')DECLARE @DBIndex INT = 1;DECLARE @SqlCOmmand NVARCHAR(Max);DECLARE @ModuleDefs TABLE( DefId INT IDENTITY(1,1) , Id INT , DBName SYSNAME , Def NVARCHAR(Max))-- From each customer database, retrieve the definition of the module in question.-- As include a "Not Defined" row, in case the module doesn't exist. We'll deal with-- quasi-duplicates later.WHILE @DBINdex <= ( SELECT Max(Id) FROM @DatabaseList )BEGIN SELECT @SqlCommand = N'SELECT ' + CAST(D.Id AS NVARCHAR) + N' AS [Id], ''' + D.DBName + N''' AS [DBName], ' + N'M.definition FROM [' + D.DBName + N'].[sys].[sql_modules] M INNER JOIN [' + D.DBName + N'].[sys].[objects] O ON M.[object_id] = O.[object_id] WHERE Object_Id(''[' + D.DBName + N'].' + @ModuleName + N''') = O.[object_id] UNION SELECT ' + CAST(D.Id AS NVARCHAR) + N' AS [Id], ''' + D.DBName + N''' AS [DBName], ''Not Defined'' ORDER BY definition DESC' FROM @DatabaseList D WHERE D.[ID] = @DBIndex; PRINT @SqlCommand; INSERT @ModuleDefs EXEC sp_executeSQL @SqlCommand; SET @DBIndex += 1;END;-- Rank the definitions partitioned by database, and just take the top definition. SELECT Rank() OVER(PARTITION BY Id ORDER BY DefId) AS DBOrder , DBName , Def FROM @ModuleDefs;WITH RankedDefs AS( SELECT Rank() OVER(PARTITION BY Id ORDER BY DefId) AS DBOrder , DBName , Def FROM @ModuleDefs)SELECT RD1.DBName , RD1.Def , RD2.DBName , RD2.DefFROM RankedDefs RD1 CROSS JOIN RankedDefs RD2WHERE RD1.DBOrder = 1 AND RD1.DBName <> RD2.DBName AND RD1.Def <> RD2.Def[/code]One thing I'm seeing, which I can't explain is... If I change 'Not Defined' in the dynamic SQL to be the empty string or NULL, then later on in the CROSS JOIN I get two rows for each database where the definition differs; one for RD1 and one for RD2. But, if I use anything else, I get just one row. I can't figure this one out. Either way, I know how to read the results. But, it sure would be nice to know what's going on.If anybody has ideas on a more eloquent way to accomplish my goal, or an explanation for the weirdness I mentioned, I'd love to hear it.Or, if you just want to give me a little sympathy for the messiness I need to deal with, that'd be appreciated, too. :-D

kll

Posted: 28 May 2013 11:08 PM PDT

hghjm
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog