LessThanDot

I support a couple of in house adp projects, which use ADO and OLEDB to connect to SQL server. Has anyone got advice on the best alternatives to this setup, losing the minimal of coding investment, and what are the gotchas?

How to make the entire thing happen through SQL SERVER AGENT. So that in a particular date and time one email get triggered to the respective user.

I have doubts that would happen if our publication has multiple subscribers, and these in the future would have also would have mirror
settings as serious
the current is so
  -PublisherFailoverPartner: server2

but since that publication, subscribe multiple servers as serious


  -PublisherFailoverPartner: serverA2, serverB2, serverC3

this could be?

Hi Fer,
There's a few issues in your comment. I'd like to point them out because they are all critical to how knowledge is not only transferred but retained.
1) There is a wrong way to write something like an article, blog or whitepaper. That wrong way is to determine there is a failure and simply state it providing no value or wealth in the remaining topic and skills being handed to the reader with no real selfish concept behind it by the author. The point of not starting something like this article with, “that crap isn’t supported” is, would you continue to read and retain the value that the blog obviously has in it? Simple minded views show this to be a fact, readers would not. So the value is lost in what you gain from it.
2) Everyone has value in how something is written, how the layout is put down on a screen and how the reading is taken. If you had a specific need to determine if there was support, it would be fairly obvious to see this was not the specific layout or flow you needed. Move on. Google and Bing are powerful tools that assist in finding other documentation that provides a specific answer to a specific question.
3) All this being said, the criticism and complete lack of respect you’ve shown for the author taking the time is down the path of the same exact message you failed at an attempt to get across. You essentially wasted the time it takes to read your comment and now, reply to it. Again, if you have a specific problem and are in need of a specific answer, obviously you found the wrong resource and should have moved along happily until you came to a solution.
4) If you can write it better, get off your lazy %@#* and do it so others that might gain from it, can.
The single largest take away here is, there is no need for the comment unless it retains value as an extension to the article as well as, you seem to not be able to work a search engine very well.

If multiple resultsets are not supported in SSRS, why this article doesn't start advising the fact ? I suggest to be objective.

You makes me waste my time reading simple things......

Hi Orlando, thanks for reading. I will be doing a blog post series on setting up a SharePoint 2013 BI demo environment. I still have to start writing, so I hope I can make it in time so you can benefit from it :)

Thanks for the post Koen. I am about to embark on a proof-of-concept project setting up a new SharePoint 2013 instance and getting PowerView going at a client site. I am looking forward to future posts.

Finally got the real tutorial here.. Thanks a lot admin, if I don't get this, I was going to delete my Mongo DB.

Are you sure the log file doesn't have a ldf extension instead of mdf? Shouldn't it be C:\HR\TestSQL_log.ldf?

I want to move the log file as well but as soon as I do that it tells me:
Msg 5120, Level 16, State 101, Line 7
Unable to open the physical file "C:\HR\TestSQL.mdf". Operating system error 2: "2(The system cannot find the file specified.)".
File activation failure. The physical file name "C:\HR\TestSQL_log.mdf" may be incorrect.
Msg 5181, Level 16, State 5, Line 7
Could not restart database "TestSQL". Reverting to the previous status.
Msg 5069, Level 16, State 1, Line 7
ALTER DATABASE statement failed.
What can I do to move the log file as well or will it automatically move. Not as far as I could see.

Thanks, Chris! One of the best comments and compliments I've received. :)

Hi, thankyou for the wonderful post, however can you please provide me the C# code instead of VBCode. I appreciate your help.
Thanks

I recently post a three part series on SQL server tuning that you may find helpful: http://www.entrancesoftware.com/2013/04/12/software-consultant-best-practices-sql-databases-indexing/

If you query the sys.sysobjects view you will see them in the xtype column.


For example
WHERE xtype = 'D'

will give you all the defaults in your database

where it will be used for? Kindly give some samples.

Impressive post, I can see this concept being used within a CMS realm as well for me across the board. I also liked the fact that you iterated noting work. I was just in a conversation the other day about that - Giving credit where credit is do type of conversation. It seems more and more evident to me that is not the case. One of the best articles I've read from both a technical standpoint and integrity standpoint

Any idea, why they came up with this 8kb thingy?
Is it because earlier in IT-tech CPUs first-level-cache was 8kb?

Regards

Hi, I used to get many latest news form your post. This post on SQL tuning is really interesting. Keep on doing.

Intellisense will immediately try to establish another connection and silently fail - but you'll get 4 Errors in the Instance Log. Enough for purists to avoid SSMS for DAC.

just what I needed thanks!

Restore SQL server database in any corruption situation in SQL server 2000, 2005 and 2008 without any problem. Fix all types of error in SQL server with help of Restore SQL server database software is powerful for recovery of SQL server MDF, NDF and LDF file format. for more info: http://www.articlesbase.com/software-articles/sql-database-recovery-assured-with-technical-intellect-application-6538100.html

thanks Dennis, we are implementing XML targets now . you'll see me on your blog quite often.

Thank you !!

Thanks Aaron for the links.
It's indeed a huge time waster, so I left a comment on the connect item and I voted it up. Sincerly hope they change it in future versions.

Allan Hirt talked about this:

http://www.sqlha.com/2012/03/01/windows-server-8-sql-server-2012-and-net-framework/

http://www.sqlha.com/2012/05/14/an-update-windows-server-2012-net-framework-3-51-and-sql-server/

And filed a bug:

https://connect.microsoft.com/SQLServer/feedback/details/742036/net-3-51-payload-is-not-availabile-by-default-in-windows-server-2012

Thank you Koen.
SSIS and Excel is not a good marriage. If you work with your own computer then you can fidle around with "work arounds" but as a consultant for a company with strict rules that might be impossible. If you only want to pull some data out from a sheet in Excel, make a small macro which saves the data as a TAB separated text file and use bulk insert or SSIS. If you want to push data (actual values) and pull out data (prognose) from the same sheet consider use VBA. With VBA you can build a "front end" to SQL Server with Excel.

Ah, I have confirmed what Stewart observed: 32-bit Office apps stop working after forcing installation of the 64-bit ACE driver. Excel is no longer able to open files after installing 64-bit ACE driver, but this can be reversed by uninstalling the 64-bit driver and reinstalling 32-bit.
Will evidently need to decide which tool is more important: SQL Server openrowset queries, or Excel itself. Or bite the bullet and replace 32-bit Office with 64-bit.
Agree with Stewart that Microsoft could/should do better than this.

@Stewart: the ACE providers are a little disaster. I installed the 32-bit provider on my 64-bit machine with 64-bit Office. All worked fine as described in the blog, until I wanted to repair my office install. I got an error saying I had to uninstall the 32-bit driver first.

@jdbrauer: good to know your problem was solved!

Hi Jes,
So, most of my environments are flaky. I'm looking at my dual-node 2008 R2 cluster, and one node has a PaxosTag of blah:57 (active node) and the other has a PaxosTag of blah:60.

Is this expected?

Is there a list somewhere of what *should* be the same on a SQL Server registry?

Amanda

Koen, thanks so much for posting this with the pointer to Lowell's solution for installing the 64-bit ACE driver on a machine with 32-bit Office apps. It solved my problem - I can now run openrowset queries against Excel files.

I have Office 2010 32-bit installed and I tried installing the 64-bit ACE using the /passive switch. It installed without complaints and the 64-bit connectivity appeared to work. HOWEVER, my 32-bit Access refused to start! Every time I tried to open Access it would go through an Office setup configuration process and then fail to open with some sort of configuration error. :-( As soon as I uninstalled the 64-bit ACE my Access started working again. Not sure why this happened but it is VERY annoying that MS didn't write their 32-bit and 64-bit ACE drivers to play nice alongside each other.

There are some nice tips. Thanks

Thanks for the post. I'd like to mention that the file with the Latitude/Longitude had some inaccurate Lat/Lon. I created a console app that will update the lat/lon from Bing Maps if anyone is interested.

Where I found the problem was with two zip codes that are next to each other, but were considered 7 miles apart with the download. I checked the values in Bing and Google and found out that the lat/lon was incorrect.

I have the 32 bit 2007 drivers and the 64 bit 2010 drivers. This works quite well and installs without offering me any arguments as to why it is not allowed.

Naomi, I don't know of any easy or built-in way. I'm sure they're stored in a config file somewhere, but I haven't dug around for it.

Is there a way to export and import the settings made?

:) Sure sounds logical

Doesn't creating duplicate indexes cause your SEEKs to be twice as fast? That's a good thing, right? Sometimes I even create 3 or 4 duplicate indexes to really speed things up.

I pay attention to them unless it is a one off query. I like the fact it writes the create for me a lot of times. (Lazy Efficiency) The best and worst case I usually explain how things can go wrong is the experience I ran into where someone automated the creation of any missing index suggestion. That was ugly.

I usually never bother with the wizards and suggestions from SSMS, especially if the tables are huge, you can really shoot yourself in the foot by running out of disk space, filling up the log etc etc

Should also comment. Like stated, "This should let it go" the log space error is still a possibility.

Something more weird: you can define check constraints (myisam, innodb; alter table add constraint ... check) but they are never checked.

Indeed, nice. Do you have any idea, or even better hard data about performance difference between the two approaches?

yup its Work... thanks

@Bert, I have read the ETL Toolkit, but I'm not going to say it really helped me pass the exam. You can do just fine without reading the book.

Don't get me wrong, it certainly is a very interesting read - and I recommend this book to everyone - but not exactly a prerequisite for the exams. The Data Warehouse Toolkit is much more essential.

very interesting blog.
I completely agree on what you say about Kimball.
but you forgot to mention "The Datawarahouse ETL Toolkit" from the very same Ralph Kimball and Joe Casserta.
It is a bit old fashioned now ( it is a book from 2006 in the end ) but in a lot of principles of SQL 2012 DQS, i recognise a lot of stuff that's also been described in this book (mind, written in 2006 allready)

Thanks Denis great post!
Now, I am thinking of not just reviewing my stored procedures but to review the generated code of LINQ as well.

Congratulations on passing all the exams

In order to assist anyone else doing this amount of data, I had to reevaluate my outbound speed, calculate that to the packet size I was sending, trim it down and now I'm sending without SQL Azure actively disconnecting me once and awhile due to it waiting for a large amount of data choking it at some points. I've been actively pushing data (at around 40 million out now) for 23 hours.

It was also critical to have a structure in the table(s) in which I could restart the process from. A simple identity column met that. Although that meant I had to order the data in the source. Making sure there was a NC index there to do that with a good execution plan helped. Then, if the line cuts, I can determine the max ID and just edit the query to start from the next.

This was needed because if you try to delete 20 million rows on SQL Azure, you'll likely get a log space error after waiting for awhile. I have to check to see how to manipulate that or do it another way. But, the only way around to get it done quicker was to drop the table which was not logged like a delete (as most of you know).

A little planning and work, it can be done.

Excellent! Startup trace flags are also in the resultset with the predicate "WHERE registry_key LIKE N'%MSSQLServer\Parameters'"
On my instance with startup trace flag 8048 specified in the startup parameters, -T8048 shows up in the value data.

Nice link! I couldn't find anything when I did my searches.

I agree completely sqlsister!