Thursday, August 8, 2013

[SQL Server 2008 issues] How do I connect an access DB to a SQL server 2008 R2 instance via SSL?

[SQL Server 2008 issues] How do I connect an access DB to a SQL server 2008 R2 instance via SSL?

How do I connect an access DB to a SQL server 2008 R2 instance via SSL?

Posted: 07 Aug 2013 07:03 PM PDT

Hi everyone, I need to connect an access database to a SQL server 2008 R2 instance, via SSL, how can I do it? when I try to connect the db, using access, by clicking ODBC database and I select the path of the server in which runs sql server, nothing happens, no credentials request, no error, nothing. When I try to reach the same server via rdp, credentials are required, which is correct.I found no help or guide about this, can someone help?thanks

sys.dm_os_performance_counters not accurate?

Posted: 07 Aug 2013 06:52 PM PDT

I've started collecting data from here now & on our busiest system I am seeing 450 transactions/secSurely this is too high to be accurate? My data is like this:[code]TRANSACTIONS_BASE TRANSACTIONS_DIFF6945125076 1546686945527571 402495[/code]So 6945527571 - 6945125076 = 402495 transactions over a 15 minute period if this is the correct way to calculate it?402495 / 60 (seconds) / 15 (minutes) = 447 transactions/sec :sAnyone had transaction counts this high? There hasn't been any news about the tran/sec data being inaccurate has there?

Performance Impact due to different collations?

Posted: 06 Aug 2013 10:00 PM PDT

Hi all,We've just 'upgraded' one of our 3rd party apps which required a new version of the vendors database (Using SQL 2008 R2 SP1 Standard Edition running on Windows Server 2008 R2 Enterprise edition). Upon familiarising myself with the new database (no they didn't bring me in on the initial testing and implementation!) I discovered that the collation is set to 'Latin1_General_100_CI_AI' which is different to the instances TempDB collation of 'SQL_Latin1_General_CP1_CI_AS'. The app is running into performance problems (speed issues mostly when logging in and with certain transactions), my question is two-fold: 1. Could this be related to the difference in collations if the app uses the TempDB?2. Should I recommend that the collation on the app db be amended by the vendor to reflect that of TempDB?Thanks for any suggestions.M

Transactions in SQL

Posted: 07 Aug 2013 03:31 PM PDT

I have some doubts related to transactions in SQL.Connection 1 open for DB1 databaseBegin transaction for Connection 1Run the commands on Connection 1Connection 2 open for DB2 databaseBegin transaction for Connection 2Run the commands on Connection 2Commit transaction for Connection 2Commit transaction for Connection 1If commit transaction on Connection 1 fails(rollback) then transaction on connection 2 should rollback.Is it possible? How it can be done?

SSIS - Pull data from two different servers

Posted: 07 Aug 2013 06:31 AM PDT

Here is my situation. I have to pull data from two different servers that are not linked together. For that, I will have to use SSIS to create a package to pull down the data. In server A, there is a staging table and that is where I will pull the data from two different servers into. Here is what I have so far in SSIS package. In Control flow, I added an Execute SQL Task to get the necessary information from multiple tables in Server A and put it into the staging table in Server A. Now, I need to use the list of IDs from the staging table to query a table in Server B and pull the results into the staging table in Server A.I am fairly new to SSIS and I think the only way to do this is to use Script Task as that is the only task that can connect to two different servers at the same time. Do you have a better suggestions or ideas?

denormalizing the table

Posted: 07 Aug 2013 03:28 PM PDT

Hi Experts,I need to denormalizing the below table:[code]CREATE TABLE #temp( Id int ,category int ,Parent_id int ,Lvl tinyint ,Name varchar(20))insert into #tempSELECT 1,1,null,1,'World' UNION ALLSELECT 2,1,1,2,'Asia' UNION ALLSELECT 3,1,2,3,'India' UNION ALLSELECT 4,2,null,1,'a' UNION ALLSELECT 5,2,null,1,'d' UNION ALLSELECT 6,2,5,2,'dd' UNION ALLSELECT 7,2,4,2,'aa' UNION ALLSELECT 8,2,7,3,'aaa' UNION ALLSELECT 9,2,6,3,'ddd' UNION ALLSELECT 10,2,9,4,'dddd' UNION ALLSELECT 11,2,8,4,'aaaa' UNION ALLSELECT 12,2,8,4,'bbbb' UNION ALLSELECT 13,2,8,4,'cccc' UNION ALLSELECT 14,1,2,3,'china' --Expected Output:Id category Level1_id Level1_Name Level2_id Level2_Name Level3_id Level3_Name Level4_id Level4_Name Level5_id Level5_Name1 1 1 World 2 Asia 14 china NULL NULL NULL NULL2 1 1 World 2 Asia 3 India NULL NULL NULL NULL3 2 4 a 7 aa 8 aaa 11 aaaa NULL NULL4 2 4 a 7 aa 8 aaa 12 bbbb NULL NULL5 2 4 a 7 aa 8 aaa 13 cccc NULL NULL6 2 5 d 6 dd 9 ddd 10 dddd NULL NULL[/code]Currenlty I am achieving this with joins .. but there could be a better way to do this. Thanks!

What is dbo?

Posted: 07 Aug 2013 03:41 AM PDT

Why do some do this Database..Table instead of Database.dbo.Table. Which one is a good practice?What is dbo?Thanks

Using CTE - Does this help the optimiser

Posted: 07 Aug 2013 06:30 AM PDT

If I use CTE, I can write a more readable SQL statement. But does this affect what the optimiser does?One of the reasons I use temp tables or table variables is to break down the queries into simpler queries that the optimser can work out more easily.Do I get the same effect with CTE? E.g if I have a query that joins 6 tables, but break it down into two CTEs that join 3 and then a final join of the two CTE results, will I get a more consistent plan?I'm assuming that I can structure the CTE in asensible way that makes good use of the indexes.ThanksTim

Update in Procedure doesn't work (but it does outside the SP)

Posted: 07 Aug 2013 06:57 AM PDT

Hi guys,I came back to check on my new stats gathering but found an issue..all the steps of the stored procedure run except for an update. A summarised version of the procedure is below with the full update statement. When I look at the results (running every 15 minutes since 4pm) the columns that should have had a numerical value are all null. If I run it manually it sets them all to 0 (since at this point the entries are already in the main table).[code]-- 1. cursor of linked servers-- 2. truncate dbo.PERF_COUNTERS_TEMP-- 3.0 <loop each linked server>-- 3.1 insert perfmon values into dbo.PERF_COUNTERS_TEMP-- 3.2 </loop>-- 4. insert perfmon values for this repository db into dbo.PERF_COUNTERS_TEMP-- 5. run the update belowUPDATE dbo.PERF_COUNTERS_TEMP SET TRANSACTIONS_DIFF = (a.TRANSACTIONS_BASE - b.TRANSACTIONS_BASE), LOGINS_DIFF = (a.LOGINS_BASE - b.LOGINS_BASE), LAZYWRITES_DIFF = (a.LAZYWRITES_BASE-b.LAZYWRITES_BASE), LATCHES_DIFF = (a.LATCHES_BASE-b.LATCHES_BASE), FULLSCANS_DIFF = (a.FULLSCANS_BASE-b.FULLSCANS_BASE), RANGESCANS_DIFF = (a.RANGESCANS_BASE-b.RANGESCANS_BASE)FROM PERF_COUNTERS_TEMP a INNER JOIN ( SELECT row_number() over (partition by instance, db_name order by coll_date desc) rnk, instance, db_name, COLL_DATE, TRANSACTIONS_BASE, LOGINS_BASE, LAZYWRITES_BASE, LATCHES_BASE,FULLSCANS_BASE,RANGESCANS_BASE from dbo.PERF_COUNTERS) b on a.INSTANCE = b.INSTANCE and a.DB_NAME = b.DB_NAME and b.rnk=1;-- 6. insert all data from dbo.PERF_COUNTERS_TEMP into dbo.PERF_COUNTERS-- 7. insert all data from dbo.PERF_COUNTERS_TEMP into an Oracle database[/code]Steps 6 & 7 work fine but the problem is it doesnt have the "_DIFF" values I was hoping for :( Instead they remain null from the inserts inside the loop. Do I need some GO statements inside my SP to make the update work or something? Why does it work when i run it separate to the rest of the code? :s

SQl Job running an SSIS package in 2005 server failing while trying to connect 2008R2 server

Posted: 07 Aug 2013 12:29 AM PDT

"Access to the remote server is denied because no login-mapping exists"This is the error message i am receiving while trying to run a sql server agent job running an SSIS package from 2005 server installed ssis package.The ssis Package data sources are pointing to a sql 2008 R2 server.The sql agent is running under sql server agent proxy account this is an AD account and have sa access on both the 2005 and 2008r2 server.can some one please help me out in figuring out the issue.When i run the the the job is throwiung this message.Executed as user: XXX_XXDev. ...sts.". End Error Error: 2013-08-06 18:30:38.40 Code: 0xC0202009 Source: Update Lot Status Update IsOperational [2800] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Access to the remote server is denied because no login-mapping exists.". End Error Error: 2013-08-06 18:30:38.40 Code: 0xC0202009 Source: Update Lot Status Update IsOperational [2800] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Access to the remote server is denied because no login-mapping exists.". End Error Error: 2013-08-06 18:30:38.40 Code: 0xC0202009 Source: Update Lot Status Update IsOpera... The package execution fa... The step failed.

Metaphone 3

Posted: 07 Aug 2013 03:51 AM PDT

We are currently using Ray Cardillo's "Double Metaphone" for phonetic name matching, and would like to upgrade to the newly released "Metaphone 3" (http://amorphics.com/buy_metaphone3.html). Unfortunately, the code is written in PL/SQL and we are operating in a T-SQL environment. Is anyone aware if "Metaphone 3" was translated to T-SQL, or if there is a simple way to translate the PL/SQL code to T-SQL?

Index Rebuild/Reorg

Posted: 07 Aug 2013 04:49 AM PDT

How to load Index Rebuild/Reorg results in table? For e.g. the results we see when we run in query analyzer. I would like to capture the results in table with date it ran against server name,frag % etc.Thanks

All Identity Column values in replicated database jumped to over 1,500,000

Posted: 07 Aug 2013 04:37 AM PDT

I'm not a db admin. I'm a developer trying to keep the SQL Server 2008 R2 replicated database running. The database was not being used because we didn't have any projects so things lapsed. I now have 2 projects and I'm trying to work with the database and all the identity column values jumped from in the thousands to the millions seamingly overnight. This change is blowing out the application I use to access the database. I'm getting "overflow" on everything. The question is why did it jump. I looked at the article properties for a few of the tables where I added records to a table and the identity column value was in the millions. I looked at the Identity Range Management for the table and I see that all my identity column range values have jumped into the millions. I tried dbcc checkident to reseed a table back into the thousands but on a subsequent add I received an error saying the addition failed because the identity column was not within the approriate range. So now I can't add to the table. It's clear that I need to change the identity ranges at the publicher but I am uncertain how to do it. I keep reading that I should use sp_adjustpublisheridentityrange to fix this but the parameters for the sp does not allow for a range so I havn't tried it. Can anyone tell me why this has happend and make some suggestions on how to fix it.Thanks

Integrated Security and AD Group

Posted: 07 Aug 2013 01:05 AM PDT

This is probably a basic question but I've not thought about it much until now. If a user has a domain login to a server with one set of rights and is part of an AD group login that has a second set of rights do the rights get merged or does one login take precedence over the other?Regards,Erin

Increase Max workers Threads

Posted: 07 Aug 2013 02:15 AM PDT

I have a SCCM database that while reports are and pushes are running together in the morning I get threadpool waits. I run the below with the results listed. Would increasing my max worker threads to 720 help this issue. More cpus is not an option and I have extra RAM availabe on the server. Currently max worker threads is set to 0 and with 12 vcpu that give me 640. most the time my count is around 660 workers. -not option to edit the SP's these jobs run and I have anaylzed indexes (frag, added, etc)select COUNT(*) from sys.dm_os_workers - 655sp_configure "max worker threads"name minimum maximum config_value run_valuemax worker threads 128 32767 0 0select max_workers_count from sys.dm_os_sys_info; - 640

Using extended events to capture sort warnings and hash warnings

Posted: 01 Aug 2013 02:48 AM PDT

HiI have just downloaded the extended events gui for sql2008.Can anybody point me to any articles on how to set this up to capture the info for sort warnings and hash warnings ?

select ident_current('table name')

Posted: 13 Sep 2011 03:54 AM PDT

I use ident_current to get ID which just inserted. 1) The code below is working in current database:select ident_current('history')2) The code below is not working in another server:select ident_current('ServerA.live.dbo.history')How to make code in 2) working?

How to make this a SQL Syntax!

Posted: 06 Aug 2013 10:48 PM PDT

I have a script but not in a SQL language: if ({act_type.act_type_sc} = ["ADD INFO INTERN","ADD INFO P/C"] and instr({act_reg.act_rmk},"closed")>0) then 1else 0I was search and i think it was CASE but i dont get a good syntax.. Can somebody helping me out.Tnx

Tempdb tables two months old

Posted: 01 Aug 2013 02:06 AM PDT

Hi AllI run the following query to get all tempdb tables:[code="sql"]SELECT *FROM tempdb.sys.sysobjectsWHERE name LIKE '#%'ORDER BY crdate[/code]I can see around 50 tables that are between one and two months old, and another 70 that are between one week and one month old.I'm guessing these are created by application components that maintain a constant connection, and are never dropped or deallocated. Is there a way I can find out what procedure, application, or even connection is creating these tables? I'd like to go into whatever is creating them and add the DROP or SET = NOTHING.Thank you

UPDATE comparing tables (most recent row only)

Posted: 06 Aug 2013 10:25 PM PDT

Hi guys,I was gonna put this in the TSQL sub-section but the little intro says it's for discussing "changes" to TSL in 10.0~I'm looking at sys.dm_os_performance_counters and attempting to derive values from their accumulating columns (e.g. transactions/sec). My plan is as follows:2 tables (main & temp) with columns TRANSACTIONS_BASE and TRANSACTIONS_DIFF.Data is inserted into the BASE then an update statement should update the DIFF value (from null). The problem is the main table may have hundreds of records for each database & I only want the most recent base value for each database. Having seen that guy's (sorry forgot your name) RBAR signature repeatedly I wanted to try & avoid doing a looping update but my SQL attempts arent getting close :fIt will update the columns but it looks at every row in the main table so in the example below it would do 4 updates instead of the 2 it should (overwriting values):[code]UPDATE dbo.PERF_COUNTERS_TEMP SETTRANSACTIONS_DIFF = (a.TRANSACTIONS_BASE - b.TRANSACTIONS_BASE)/60/10FROM PERF_COUNTERS_TEMP a INNER JOIN ( SELECT instance, db_name, COLL_DATE, TRANSACTIONS_BASE from dbo.PERF_COUNTERS c) b on a.INSTANCE = b.INSTANCE and a.DB_NAME = b.DB_NAME[/code]Anyone know how to only take into account the most recent entries in perf_counters for comparisson? Below is some code if anyone is willing to help & wants to replicate the scenario[code]CREATE TABLE perf_counters ( instance varchar(50), db_name varchar(50), coll_date smalldatetime, transactions_base bigint, transactions_diff smallint);GOCREATE TABLE perf_counters_temp ( instance varchar(50), db_name varchar(50), coll_date smalldatetime, transactions_base bigint, transactions_diff smallint);GOINSERT INTO perf_counters values ('SRV01','DB01',getdate(),1000,null);GOINSERT INTO perf_counters values ('SRV01','DB02',getdate(),4000,null);GO-- wait a whileINSERT INTO perf_counters values ('SRV01','DB01',getdate(),1200,null);GOINSERT INTO perf_counters values ('SRV01','DB02',getdate(),4150,null);GO-- wait a whileINSERT INTO perf_counters_temp values ('SRV01','DB01',getdate(),1250,null);GOINSERT INTO perf_counters_temp values ('SRV01','DB02',getdate(),4270,null);GO[/code]Edit: It's easiest to remove the /60/10 for testing since the time intervals won't be there (so it's not always 0).So with the example above the DML would update the perf_counters temp rows to be:SRV01, DB01, *date*, 1250, 50SRV01, DB02, *date*, 4270, 120Since it would ignore the 1000/4000 rows as they are older.

invisible views

Posted: 06 Aug 2013 11:14 PM PDT

I'm looking after a SQL 2008 R2 box and several users have suddenly encountered a problem in a particular database, all views under a particular schema are now invisible however they previously were visible to these users. Strangely they can still select from these views if they can remember the names of the views. They can still see all views under the 'dbo' schema though fine.After going through their permissions on the accounts, they all have 'data reader' permissions and no 'deny' permissions. I've created a test user and given it only 'data reader' permissions and logged in to the server using that account, as suspected it can see all the views and all works perfectly.I'm at a loss as to why this is happening, I could have understood if the test user had the same problem but its fine :-)I'm sure its probably something daft in front of my eyes but has anyone got a clue as to what I'm missing? This is bangin my head now!! :w00t:

Server side trace to a table

Posted: 11 Jan 2012 12:58 AM PST

The link below helps me to create a server side trace to a file. I'd like to send the results to a table. Could some please tell me what change I need to make in the script to send the results out to a table?http://sqlserverpedia.com/wiki/The_Server-side_Trace:_What,_Why,_and_HowAnd also, I was told that server side tracing to a table in SQL 2000/2005 is different from SQL 2008. Is it?

SSIS - FTP Task and SQL Agent Job

Posted: 06 Aug 2013 08:56 PM PDT

Hi everyoneI have created a SSIS Package that gets a list of files on a FTP site, receives and then deletes each file using a ForEach enumerator.It is working 100% when run using BIDS, but when I schedule this as a job, it fails:Description: The object in the variable "User::FileList" does not contain an enumeratorBut when I run the BIDS version directly after this error, it pulls down the files?What would cause the Job not to get the file list? Its a plain Script Task using a FTP Connection.Any ideas?

Sql cluster8 r2 with out msdtc

Posted: 06 Aug 2013 10:07 PM PDT

One of our client want dba to install sql cluster 2008 r2 on single cluster node.(maybe in the future a second node will be add to the cluster).Can any one clarify the below things.IS it possible to configure msdtc with out secodary node.? Is it possible to install sql cluster on 2008R2 on sinlge node with out configuring MSDTC? orcan we create MSDTC after adding the 2nd node to the existing single node cluster?Advance thanks,

can not create temp directory

Posted: 06 Aug 2013 09:37 PM PDT

Hi All,I am getting below error.can not create temp directoryPlease advice on this.

Trigging a Rport manager report from the agent

Posted: 06 Aug 2013 08:11 PM PDT

Hello!To deliver statistics to the business controllers in our company, I use both report manager for built reports that they trigger themselves by logging in, filling in the forms and retrieve the data in the way they want - PDF, excel or just viewing the data. Another way is the scheduled jobs in the agent that runs and uses the msdb..sp_send_dbmail to e-mail the results. The problem with this second option is that the output is plain text, very ugly and hard to use. The solution I am looking for is to be able to trigger a Report Manager report from a stored procedure (that I schedule in the agent) and get the result from that report in excel format mailed to a specified recipient.Any ideas?

SQLServer Error: 15404

Posted: 06 Aug 2013 07:34 PM PDT

It appears to me that SQL Server do not support hostname over 8 characters. When I enter my hostname to schedule an SQL maintenance plan and save it, it will automatically rename my account from: SOLOMON2009\Administrator to SOLOMON2\Administrator. Backup will fail with the below error log:[298] SQLServer Error: 15404, Could not obtain information about Windows NT group/user 'SOLOMON2\Administrator', error code 0x534. [SQLSTATE 42000] (ConnIsLoginSysAdmin)
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog