Saturday, August 17, 2013

[SQL Server 2008 issues] problem with installation of Sql server express 2008

[SQL Server 2008 issues] problem with installation of Sql server express 2008

problem with installation of Sql server express 2008

Posted: 16 Aug 2013 07:12 PM PDT

i already have visual studio 2008 on my laptop. i am trying to install ms Sql server 2008 express but for one reason or another the installation stops .last time it was because master.mdf was already installed on my system. i checked and found out that C:\Program Files (x86) contains both 'Microsoft SQL Server Compact Edition' and 'Microfost Sql Server' folders. How can i install Ms sql server 2008 express? Thanks....

Error while importing to excel.

Posted: 16 Aug 2013 04:36 PM PDT

Hi, I am trying to import sample data into excel,using following code,but it gives me following error,Create Table Test(a Varchar(2),b Varchar(2))Insert Into testvalues ('ab','bc')insert into openrowset('microsoft.ace.oledb.12.0','excel 12.0; database= D:\test' ,'SELECT * FROM [Sheet1$]')select * from testMsg 7399, Level 16, State 1, Line 2The OLE DB provider "microsoft.ace.oledb.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.Msg 7303, Level 16, State 1, Line 2Cannot initialize the data source object of OLE DB provider "microsoft.ace.oledb.12.0" for linked server "(null)".

Backup to another network server

Posted: 16 Aug 2013 02:54 PM PDT

Hi,I am trying to backup from my sql 2005 DB which is on windows server 2003 to sql 2008R2 on windows server 2008R2 but getting access denied,I have tried using SSMS, T-Sql but still getting issue with this.I have created backup folder on my 2008R2 server and assign the full access to Everyone on security permission, I have added user and also grant the FUll Permission.1) I tried using SSMS ut didn't work2) I tried using T-sql, didn't workI have just install the sql 2008R2 on Windows 2008 R2.

Upgrade form Sql2008 Standard to Enterprise Edition

Posted: 02 Aug 2013 09:06 AM PDT

Hello,We have currently build new Server 2008R2 with 8 core CPU with 32 GB Memory and currently working to get license for sql server 2008R2 Enterprise edition.We have currently sql 2005 standard and planning to migrate to new server as mentioned above.1) If I upgrade 1st using sql standard edition and later upgrade to Enterprise with using 8 core CPU with 32 GB Memory will be ok?Can we upgrade form Sql2008 Standard to Enterprise Edition?Let say we have sql 2008/sql 2008R2 already running and I would like to upgrade to sql 2008/sql 2008R2 Enterprise Edition.Is it just upgrade instance wizard I have to follow?Reading from the articles, little confused that is it we need to do another backup/restore too?If I install just cd and run the upgrade Instance wizard will be work or any other procedure I have to follow?I think I just need to restart the service and server, correct?2) I have another question is that if I download from the technet for testing then if I have to upgrade it using production licensed key, can we do and is it the same way?Thanks,

Script to montior ErrorLog every 1 hour

Posted: 16 Aug 2013 05:23 AM PDT

Hi,I would like to have a Script to montior ErrorLog which will run every 5 mins to check for the errors that occurred since last one hour and if it finds any error, it should send an email to dbagroup.Can you please give some inputs on this? Thanks

select data from a table if a column is logged with specific data

Posted: 16 Aug 2013 10:37 AM PDT

Hi Experts,How can i select data from a table if the data logged into a status column(varchar) shows as "Total Completed is greater than 1"For Eg:Date Status2013-03-13 12:30 Total Completed =4 /*select this*/2013-03-13 12:00 Total Completed =0 Thanks in advance

Very strange performance issue

Posted: 16 Aug 2013 10:05 AM PDT

I have a simple query, if i run that query from SSMS it takes about 10 mins and if i run the same query as a exec sql task inside SSIS package takes less than 3 mins? I am clearing the buffers after each execution, and yes the source connection strings are the same. I am logged in the server and testing using SSMS and SSIS. Is there any reason why it is faster from SSIS? Query returns about 20 million records[code="plain"]SELECT * FROM vwActivities WHERE CreatedDateKey>=20130101 and Fde='A123'[/code]

Shrink does not release space

Posted: 13 Aug 2013 07:58 AM PDT

Hi =)Im trying to shrink my production database, because we archived about 200GB of data, but - I don't know why - none space is released. I tried rebuild some indexes, shrink via file and even suspended the mirror (this environment has mirroring) but 0 KB is released rs.Recently I created an environment for testing and restore one of my lastest backups from the production database. I shrinked and ..... works fine. Released the unused space... I really don't know what to do. Pls, Heeelp rsps.: I searched in others topics, but didn't find any solutionps2: Sorry for my english...

Remove item in sysobjects because it is no longer in database

Posted: 16 Aug 2013 03:44 AM PDT

I have an item in sysobjects that is no longer displaying in my database. (Possible corruption, I'm not sure.)As a result, I'd like to remove this item. When I run the following query, I get an error and when I try to allow ad hoc updates, I still get an error. How can I remove this item from sysobjects?[code="sql"]delete from sysobjectswhere name = 'temptest'Error:Msg 259, Level 16, State 1, Line 1Ad hoc updates to system catalogs are not allowed.sp_configure 'allow updates', 1GORECONFIGURE;GOError:Msg 5808, Level 16, State 1, Line 1Ad hoc update to system catalogs is not supported.[/code]

Automated Data Comparison?

Posted: 15 Aug 2013 09:16 PM PDT

Hey guys,I'm sure this is something some of you have had to do from time to time, so I'm wondering if there's any built in functionality in SQL which could help...Essentially I'll be creating a data set once a day which will be dumped to a table. You would expect to see:Small amounts of growth day on day (less than say 1%) row count wiseOverall colum data coverage to be very similar (again, slight growth or reduction)Some values to change but not massively.I was about to create a series of important things I wanted to check but I wondered if there was any kind of 'sql compare' type thing (except for the actual SQL compare tool - that's for code!), which could have thresholds set and then trigger alerts when theyr'e broken.Probably a massive long shot but I thought I'd ask since it's multiple tables and quite a few columns I'd be wanting to evaluate.

left join question

Posted: 16 Aug 2013 02:41 AM PDT

[code="other"]select a.col1,a.col2from Table1 Aleft join Table2 Bselect a.col1,a.col2from Table1 A[/code]Would these two queries return same result set?

Indexes

Posted: 15 Aug 2013 07:10 PM PDT

Hi Team,am having a table with id, emp_no in a table, and the two columns constraint type is Primary Key (non clustered) , and having 20000+ records in that table.and the combination of the two columns is a unique value,instead of non clustered index, if i changed it to clustered index.if there any issues.Please suggest.

Sanity Check - Disable and re-Enable a Trigger in a Sproc

Posted: 16 Aug 2013 06:44 AM PDT

I have a project that I am about to get hip deep into, and I want to check if my approach is going to cause more problems than the system currently has.[b]Project:[/b]We have a batch process that updates a table. This table has a trigger that is over 5,400 lines long, and is a real nightmare. The trigger is really nothing more than a giant Case statement, but all of the updates it makes really need to be completed right away instead of the records being marked with a flag to update later (believe, I argued this point till even I had to accept the trigger has to stay).I have been tasked with breaking up the trigger into individual sprocs, but some of the steps will update the same table the trigger fires from. The system is currently set to disable the trigger when the trigger updates its own table, but since I will be running these updates in sprocs I will need to manually disable the trigger, run the needed updates, and then re-enable the trigger.[b]Additional points to consider:[/b]Currently this is a SQL Server 2000 database, but we are converting it to a SQL Server 2008 R2 database (to be completed no more than 4 weeks from today). There is a question of whether we will run it in 80 or 100 compatibility mode, so my solution needs to account for both variations.[b]My questions:[/b]Has anyone run into an issue using the approach before?Are there differences in this running in 2k, vs. 08R2 compatibility 80 vs. 08R2 compatibility 100?What am I not thinking of that may bite me later?

SQL Accounts in Local Administrator group, any reason for this?

Posted: 16 Aug 2013 07:19 AM PDT

We have a SQL 2008 server where all of the SQL service domain accounts (Engine, Agent, Reporting, Analysis) are in the local Administrators group. I'm unable to find any documentation that shows that this is required, but I'm leery of removing them just in-case. Anyone know of anything that may have caused my predecessor to setup the server like this?Thanks

parsing blocked processes report

Posted: 16 Aug 2013 06:38 AM PDT

Hi Folks- thanks in advance for any help you can provide. I'm capturing a blocked processes report to a file one disk and I'm trying to query against it and parse the xml into readable information. Most of that is fairly well documented and I'm having success except for one small snag. I'm trying to take the sqlhandle from the report and return the sql statement associated with it. When I do so, I'm getting the error:Implicit conversion from data type varchar(max) to varbinary is not allowed. Use the CONVERT function to run this query.here's what I'm doing:1.) capture a blocked processes trace to a file called :\trace_blockedprocesses_2013-08-16_0400.trc and create a couple of entries by blocking (blocked processes threshold is set)2.) drop that into a table, just to make things a little simple for now (will eventually just use a CTE) SELECT CAST(textdata AS XML) as fullblockedprocessereportXML, CAST(textdata AS XML).value( '(/blocked-process-report/blocked-process/process/executionStack/frame/@sqlhandle)[1]','varchar(max)') as blockedprocess, CAST(textdata AS XML).value('(/blocked-process-report/blocking-process/process/executionStack/frame/@sqlhandle)[1]','varchar(max)') AS BlockingProcess1, CAST(textdata AS XML).value('(/blocked-process-report/blocking-process/process/executionStack/frame/@sqlhandle)[2]','varchar(max)') AS BlockingProcess2, CAST(textdata AS XML).value('(/blocked-process-report/blocking-process/process/executionStack/frame/@sqlhandle)[3]','varchar(max)') AS BlockingProcess3, CAST(textdata AS XML).value('(/blocked-process-report/blocking-process/process/executionStack/frame/@sqlhandle)[4]','varchar(max)') AS BlockingProcess4, CAST(textdata AS XML).value('(/blocked-process-report/blocking-process/process/executionStack/frame/@sqlhandle)[5]','varchar(max)') AS BlockingProcess5, CAST(textdata AS XML).value('(/blocked-process-report/blocking-process/process/executionStack/frame/@sqlhandle)[6]','varchar(max)') AS BlockingProcess6, CAST(textdata AS XML).value('(/blocked-process-report/blocking-process/process/executionStack/frame/@sqlhandle)[7]','varchar(max)') AS BlockingProcess7,@@servername as servername,Duration/1000000.0 as durationsecs,DatabaseName into #tempwc1from fn_trace_gettable(N'd:\trace_blockedprocesses_2013-08-16_0400.trc', default)3.) attempt to return results like this and error is produced:select * from #tempwc1 wccross apply sys.dm_exec_sql_text(wc.blockedprocess) AS stWhat I have tried already:When I do select * from #tempwc1 and take the value returned for blockedprocess and manually plug it into sys.dm_exec_sql_text like:select text from sys.dm_exec_sql_text(0x03000d008702963122150300aca100000100000000000000)I do then get the correct blocking query.When I change the data type, or convert the value to varbinary like this:.... CAST(textdata AS XML).value( '(/blocked-process-report/blocked-process/process/executionStack/frame/@sqlhandle)[1]','varbinary)') as blockedprocess .....in that case, the acutal value of the sqlhandle changes so I dont get any result...So to summarize my question, how can return the sql statements involved in a blocked process along with the rest of the relevant information in the blocked processes report without having to manaully plug in the sqlhandle.Thanks!

Schema level permissions

Posted: 16 Aug 2013 03:31 AM PDT

Hi Experts:Requirement: We have three schemas in one database known as NY, CA and DM. Users should only have select on NY and CA schemas but should have select, update, insert, delete truncate, execute on DM schema. Is this is possible ?

Maximum Database Size in 2005\2008\R2\Denali 524,258 vs 524,272

Posted: 27 Oct 2011 06:01 AM PDT

Hi,As per http://msdn.microsoft.com/en-us/library/ms143432.aspx the maximum database size specification for [b]SQL Server 2005 - 524258 SQL Server 2008\R2\Denali - 524272[/b]I'm not getting the values correct for 2008/R2/Denali by doing the basic math.Database can only have 32767 files including the data and log files. I tested this out in test environment. [i]Msg 5033, Level 16, State 1, Line 1The maximum of 32767 files per database has been exceeded.[/i]So max number of data files per database is 32766. Data file can grow only 16 tb and a Log file can grow only 2 tb..Now 32766 *16 + 2 = 524258. Why the specification say maximum db size as 524272 ?Just want to figure out what I missed here!!!! Any help is appreciated..Posted the same on MSDN --No Replies so far

Does Index maintenance cause log file growth

Posted: 15 Aug 2013 11:48 PM PDT

HiWe have a nightly maintenance task that reorgansies the indexes and checks db integrity across all our servers.The job was failing upto 2 days ago on one particular server beacuse of a table that had an index that didn't allow page level locks. I changed the index to allow the locks and now the job runs. However, i have noticed since then, that the database log file that the table belongs to auto grows during the night. I have checked this by quering the msdb database for log file auto events and i can see it grows by apporx 5Gb - not leaving us much space on our drive. The actual log file is now 10Gb and during the day we back it up every 10mins so it never really gets anywhere near 10Gb - this only happens at night when the maintanence job runs.So, am i correct in assuming it is the index maintanence job that causes the growth ? And will it always need this 10Gb of space to complete the task ?

Display results even if Joined table doesn't have any matches

Posted: 16 Aug 2013 01:34 AM PDT

[code="sql"]SELECT Records.Listcode, Records.JulianDate, Records.Records, COUNT(sales_view.JulianDate) AS SalesFROM Records INNER JOIN sales_view ON Records.Listcode = sales_view.listcode AND Records.JulianDate = sales_view.JulianDateGROUP BY Records.JulianDate, Records.Records, Records.ListcodeHAVING (Records.Listcode = 'LM')ORDER BY LEN(Records.JulianDate) DESC, Records.JulianDate DESC[/code]This is my sql query and it is pulling only 2 results because within both the records table and the sales table it has two matches, but within the records tables I have 224 matches for Listcode "LM". I was wanting to know how I would be able to get with this code I have above to show all the LM listcodes from the records and have a 0 in the Sales column? Hopefully I explained this clear enough, if not please let me know and I will try to re-explain a different or more clear way! Thanks in advance!

Update table values if corresponding values change in another table

Posted: 23 Jul 2013 10:03 PM PDT

Hi,I am very new to SQL and really dont know how to phrase my question. There are 2 tables linked through a primary key and if the values in one table change, the corresponding values in another table should be changed and reflected accordingly.Does someone know what logic I need to apply for this to work ? Do I have to create a primary key-foreign key relationship and then create a trigger on the other table on which the values need to be updated ?The values in the table will be changed through a webpage.Any ideas would be appreciated.Thanks.

Delete datetime record

Posted: 16 Aug 2013 12:03 AM PDT

How to code to delete record in ORDERDATE for bbb?NAME------ORDERDATE------------------------------------aaa 2013-05-02 00:00:00.000bbb 2013-06-03 00:00:00.000ccc NULL

Unable to view the triggers

Posted: 15 Aug 2013 09:48 PM PDT

Hi friends,I have created trigger for login 'XXXX' in master database which does not exceeds 3 sessions. But am unable to see the trigger both in GUI or by query.Trigger script is given below:use masterCREATE TRIGGER connection_limit_triggerON ALL SERVER WITH EXECUTE AS 'login_test'FOR LOGONASBEGINIF ORIGINAL_LOGIN()= 'login_test' AND (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = 'login_test') > 3 -- only three sessions ROLLBACK;END;I used select * from sys.triggers but am unable to see this trigger. Am unable to get the trigger name. Kindly suggest a way to find a solution for the above problem.

add an alias field

Posted: 15 Aug 2013 10:48 PM PDT

Hi,I have a table with products name from a group (1.3.7.2.1).All products which has this group are part of a category products (ex: [Grupa] like '1.3." & "%'" are in Electromagnetic_locks category)Please tell me how can I filter "group" field and to appear category for each products.I atach a png file with my query in sql, please how can I introduce that filter in my sql[code="sql"][/code]SELECT pozdoccmibtl.Tip, pozdoccmibtl.Cantitate * pozdoccmibtl.Pret_vanzare AS valoare_cantitate, pozdoccmibtl.Data_facturii, pozdoccmibtl.Data_scadentei, pozdoccmibtl.Contract, nomencl.Denumire, terti.Denumire AS Client, lm.Denumire AS Loc_munca, terti.Judet, Zone.Denumire_zona, pozdoccmibtl.Factura, grupe.Denumire AS [grupe produse], pozdoccmibtl.Cantitate, pozdoccmibtl.Discount, terti.Tert, grupe.GrupaFROM pozdoccmibtl INNER JOIN nomencl ON pozdoccmibtl.Cod = nomencl.Cod INNER JOIN terti ON pozdoccmibtl.Tert = terti.Tert INNER JOIN judzone ON terti.Judet = judzone.Judet INNER JOIN Zone ON judzone.Zona = Zone.Zona INNER JOIN infotert ON pozdoccmibtl.Tert = infotert.Tert INNER JOIN lm ON infotert.Loc_munca = lm.Cod INNER JOIN grupe ON nomencl.Grupa = grupe.Grupa INNER JOIN gestiuni ON pozdoccmibtl.Gestiune = gestiuni.Cod_gestiuneWHERE (pozdoccmibtl.Tip = 'ap' OR pozdoccmibtl.Tip = 'ac') AND (Zone.Denumire_zona = 'ardeal' OR Zone.Denumire_zona = 'muntenia si oltenia' OR Zone.Denumire_zona = 'moldova si dobrogea' OR Zone.Denumire_zona = 'banat crisana' OR Zone.Denumire_zona = 'bucuresti')[code="sql"][/code]thanks a lot.

The service broker initaitor queue on Server\Instance.Database experienced an error ((null)) during receipt of transmission. The conversation has been closed.

Posted: 15 Aug 2013 10:42 PM PDT

Following error is found in the SQL Server log. How to fix this?MessageThe service broker initaitor queue on Server\Instance.Database experienced an error ((null)) during receipt of transmission. The conversation has been closed.Error: 50201, Severity: 18, State: 1.Note: "initaitor" typo is in the orginal error message by MS.

Outer Apply and Left Join differance

Posted: 15 Aug 2013 09:32 PM PDT

Want to know the difference between [b]Outer Apply[/b] and [b]Left Join[/b].For the same query if i use Outer Apply, query output will be faster but in some DBs Outer Apply takes log period for returning details.i am not seeing consistency in the data output time.Is there any restriction due to the SQL Server 2005/2008/2008 R2 for using Cross Apply in queries.Thanks & Regards,Balaji

Getting the CPU Utilization Of SQL SERVER 2008

Posted: 15 Aug 2013 08:19 PM PDT

Hello Everyone,I'm trying to get the Memory Usage of the SQL SERVER (Total Memory Usage) and available free memory of the system using the query which I got on the internet. The query seems to work for SQL SERVER 2008 but doesn't work on SQL SERVER 2005. The reason being [b]"dm_os_sys_memory" and "dm_os_process_memory "[/b] are not available in SQL SERVER 2005.Note: This should be achieved through queries only, as I don't have permission on machine to use permon and other features provided by windows.The below mentioned query works in SQL SERVER 2008, please suggest if there is an alternate way [b]select A.total_physical_memory_kb/1024 as Total_Memory, A.available_physical_memory_kb/1024 as Free_Memory, B.physical_memory_in_use_kb/1024 as SQLServer_Memory from master.sys.dm_os_sys_memory A, master.sys.dm_os_process_memory B;[/b]Regards,Prakasha N
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog