Wednesday, July 10, 2013

[SQL 2012] OpenRowSet works locally but not over networked drives

[SQL 2012] OpenRowSet works locally but not over networked drives

OpenRowSet works locally but not over networked drives

Posted: 10 Jul 2013 03:23 AM PDT

Hi,My company got some new stuff. I spent an hour or so on the phone with a sysadmin this morning going over permissions issues etc. in getting SQL able to import files via ORS. So we got it to work on local drives, but not over networked drives. This is the code and error:[code="sql"]select * into sample.dbo.[eriktest] fromOPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source=\\server\Sample\test.xlsx;Extended Properties="Excel 12.0 XML;HDR=YES;IMEX=1"')...[Sheet1$][/code][quote]Msg 7399, Level 16, State 1, Line 1The 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 1Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".[/quote]But going from the local C: or D: drives is fine. I also tried using xp_cmdshell (SORRY OPC) to map a drive, and then tried using ORS with the drive letter, and got the same error. Has anyone else run into this? I've spent a lot of time reading up about the errors, and tried everything that sounded like a reasonable suggestion on other message boards.Thanks

Certification

Posted: 10 Jul 2013 01:09 AM PDT

Afternoon all,I looking to renew my certifications for BI to the latest version - MCSE: Business Intelligence Solutions Expertthere are a number of exams required:Step1 Querying Microsoft SQL Server 2012 exam: 461 2 Administering Microsoft SQL Server 2012 Databases exam: 462 3 Implementing a Data Warehouse with Microsoft SQL Server 2012 exam: 463 After successfully completing steps 1-3, you'll earn a Microsoft Certified Solutions Associate (MCSA): SQL Server 2012 certification.4 Implementing Data Models and Reports with Microsoft SQL Server 2012 exam: 466 5 Designing Business Intelligence Solutions with Microsoft SQL Server 2012 exam: 467 just a general question initially - from your experience is it worth me just booking exams 1,2 and 3 straight away as I work with SQL daily and have my MCITP? Is it worth buying the books, or would I just be waisting money? Are there any SQL 2012 gotcha I need to read up on first?

Add readonly user to secondary replica?

Posted: 10 Jul 2013 12:51 AM PDT

I am using an Availability Group to mirror a database from our prod environment to a disaster recovery environment. I set the secondary replica to be read-only. Can I create new logins on the DR environment and grant them read to the secondary replica? Thanks,DK

out-of-range value

Posted: 09 Jul 2013 09:40 PM PDT

Hi,I have a system that has been moved from a SQL2008R2 box to a SQL2012 box but there is query it executes (had to run a trace to find it) that throws the old 'The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value'. The query is evaluating the UK date '29/05/2013'. I know the best way to fix it would be to amend the query but that would involve the 3rd party to amend the front-end which is not going to happen.The new server is also running Windows 2012 and I've checked all locales are set to United Kingdom and the dates are set to 'dd\mm\yyyy'. The language the login the front-end uses is also set to 'British English'.Any thoughts on how to get round this without altering the code. Is SQL2012 more stringent with dates?Any help greatly appreciated.

sql query

Posted: 09 Jul 2013 06:54 PM PDT

Is there a difference in these two queries??1.if not Exists (select TimeOffEntryId from TimeOffEntry where FinancialYearId = 1 and AWOLUserId = 'ST01574' and LeaveTypeId = 16)(select 1)else(select -1)2.exec sp_executesql N'if not Exists (select TimeOffEntryId from TimeOffEntry where FinancialYearId = @financialYearId and AWOLUserId = @awolUserId and LeaveTypeId = leaveTypeId)(select 1)else(select -1)',N'@awolUserId varchar(7),@financialYearId int,@leaveTypeId int',@awolUserId='ST01574',@financialYearId=1,@leaveTypeId=16The second query is taken from sql profiler. Both run on same table but the first one gives me a correct result that is 1, however the second gives me -1. I am so confused..I dont know may be it is a silly error that I cant figure out. :S

Generating a set of semi-random numbers

Posted: 09 Jul 2013 11:54 AM PDT

I can generate random numbers just fine using NewID(). I got it from SqlAuthority.com, works a charm. I am generating numbers between 1 and 5. If I just use NewID() or similar, I will likely get an even spread of values, which I don't want. Instead, I wanted the frequency to decrease as the number increased. So for example, I would have a spread like this (for example):(1 indicates a mild symptom, 5 indicates a fatality)1 - 40%2 - 25%3 - 20%4 - 14%5 - 1%I was playing around with generating numbers and using something like 6-(Power(randomNum,CAST(1/6) AS DECIMAL())and then using FLOOR or CEILING to round up or down as necessary. The catch is that I'm trying to avoid using a cursor for all of the data generation. I may have to for the patients, and then loop until the code generates a grade 5, because that indicates a fatality. At that point, I would "deactivate" the patient so no more cycles would be run.Is this a sane way to do this, or am I missing something obvious? (Sorry, I would read Ken Henderson's advice in Guru's Guide, but it's at home and I'm not.)Thanks!Pieter

SQLCMD and inserting data

Posted: 09 Jul 2013 12:58 PM PDT

I am not that familiar with SQLCMD. I am calling a main script which in turn calls other script files. All the other scripts work fine but I am having a problem with only one. In this script I am inserting data into three tables that do have foreign constraints between them. I have no problem running the script in a query window of SSMS when I first drop the constraints, then insert the data, and then add the constraints back. However, when I call the file form the main script it fails with foreign constraint errors. I don't understand why the contents in the file wouldn't be handled in the same way it is when run through SSMS? It is driving me nuts. The worse part is my manager did run it shortly before I got there - i started a week long project leading up to the deployment of their website - and now the thing doesn't work, and I look like the one who broke it. This one file is just many that process the same way. First the tables are created, then the constraints are added, and then data is inserted. Has anyone have any idea what maybe causing the problem?

SQL 2012 SSIS and SQL 2008 R2 SSIS - 2 instances

Posted: 09 Jul 2013 06:27 AM PDT

We have upgraded a dev server from 2008 R2 to 2012. Because 2 apps are staying on 2008 R2, we have installed a named instance on the same box. We use global env. variables for SSIS_Config_Servername in a normal env. Can we create a second for the 208 R2? Can we support multiple instances with SSIS? So far, I cannot login to SSIS on the named instance with error:SSIS service does not support multi-instance, use just server name instead of "server name\instance"So... does everything else have to be on the named instance instead of the new?

Deploying SQL Server 2012 SSIS packages in SQL Server 2008 R2

Posted: 09 Jul 2013 07:29 AM PDT

We had developed SSIS packages in SQL Server 2012 environment. But due to some unavoidable reason we need to deploy these packages in SQL Server 2008 R2 environment. Does SQL Server supports forward compatibility for SSIS packages?

Availability group planning with MSDTC as a requirement

Posted: 09 Jul 2013 03:07 AM PDT

We've got a number of clustered SQL 2008 instances and currently use transactional replication to copy data off to a secondary database to use as a datasource for reporting purposes.I'd like to migrate to SQL 2012 and utilize availability groups to point reporting to a read-only secondary replica to eliminate database replication. The application we have requires the use of MSDTC.Where I'm kind of running into a brick wall is on what this means for my use of availability groups. Does this mean that I can't use availability groups AT ALL? Or does it just mean that I need to utilize FCI based instances for my availability group members?Basically, I want read-only secondary replicas AND MSDTC, am I asking too much? :)
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog