Thursday, August 29, 2013

[SQL Server 2008 issues] Combining 2 queries

[SQL Server 2008 issues] Combining 2 queries

Combining 2 queries

Posted: 28 Aug 2013 08:17 AM PDT

HiI have two queries in SQL linked to excel.Report 1 showsContract A E N PIncome 5000 6000 8000 4000Costs 4000 7000 6000 3000Report2 shows Contract A B ECustomerA 500 200 400CustomerB 450 100 200Is it possible to combine them so I getContract A B E N PIncome 5000 6000 8000 4000Costs 4000 7000 6000 3000CustomerA 500 200 400CustomerB 450 100 200ThanksSent from my iPad

sql version

Posted: 28 Aug 2013 05:44 PM PDT

we installed sql 2008 r2 version in one of the new servers , insatllation went fine .later we checked the version of sql it;s showing 10.52.4000 but expected as 10.50.--. what is it mean ?

MS Word Mail Merger from SQL view over SSL VPN

Posted: 23 Aug 2013 09:35 AM PDT

Hello,I am trying to mail merge a document over an SSL VPN connection. I can open a Word doc that is on our server, but when I try to mail merge the document, it freezes, or takes about 30 seconds to search 200 records. We have 16,000 records... When I'm in the office it take 2-3 seconds to mail merge.I am using a word data source that connects to a view on our SQL server. I have tried recreating the mail merge with Schema binding so I could create a unique Clustered Index on the base table's primary key, which is also the field we use to search for our records.This they anything I can do to speed things up? I'm open to idea's...As always any help is greatly appreciated,David92595

Using OSQL in VS Command Prompt (2010)

Posted: 28 Aug 2013 10:07 AM PDT

Ok i was given this code below to run in the command prompt.[b]osql -E -S .\SAM -i C:\aspnet.sql[/b]Can i just run the aspnet.sql in the window of SSMS 2008? I don't know what that code above means but i'm having problems running it.

MS SQL 2008 Audit query- Need Server names, DBs name and login information

Posted: 28 Aug 2013 10:00 AM PDT

Hi,I am writing a query to get the following information for my audit report. Can someone please send me script or help with this. Server NameSQL Server Instance NameDatabase NameLogin NameUser NameRole/permission assignedReport Date (current date)Appreciate your help.Thanks Ichbin

Problems with SQL dumps.....A temporary fix to get you by.

Posted: 28 Aug 2013 02:05 AM PDT

I am going to preface this post by saying this solution is TEMPORARY and for emergency situations on production servers. many will disagree with even using this but trust me there are circumstances where you need this...I know because I had a situation that necessitated it......Some background......Recently my company moved from a single on site data center to a dual remote data center environment. This meant i had several SQL clusters to move with some mirroring, etc. A typical farm in a typical environment if typical can be said of anything that is. In the process several of my servers were shut down less than gracefully. In fact one server was bounced back and forth between cluster nodes 10 times or more before someone who knew what they were doing shut it down correctly. this server came up with db's in emergency mode and all sorts of problems. I finally did get all teh databases to a point where I could select data from them. These were SharePoint databases on this instances. There are roughly 40 databases and SharePoint was displaying pages and seemed happy.So I wiped the sweat from my forehead and gave a quick "thank God!" adn went about my day only to be called in the middle of the night because SharePoint would not allow documents to be uploaded. What I said? Why? The drives had multiple 100's of gigs free and we expand roughly 1 gig per day.The issue was that the SQL server was saving dumps to the log drive and overnight had created almost 150 gigs of txt files filling up the whole drive. No this is a production system that was otherwise working. Clearly one of the databases has issues. I need to run some profiles, take them offline, do restores, and possible many other things that would take SharePoint out of service.Ever tried to convince your director that an emergency outage is needed when the service itself is up and reachable? Well lets just say he wasn't to keen on the idea of doing it outside a normal maintenance window. What do I do now? The databases are usable in some fashion because SharePoint is up. No data corruption seems to be taking place as everything put in SharePoint is retrievable and all the services are up.Do I just keep going in to delete logs every 15 minutes until the next window in 2 weeks? This solution is NOT the way to go about doing this long term BUT if you need to get through a week or two until a maintenance window the attached program (with source code) will watch any director for any particular file extension(or all files). When it sees a file being created that matches your criteria (for me it was log, mdmp, and txt files), the file will be automatically deleted. Not sent to the recycle bin btw but completely deleted. This will keep your drive from filling up.If anyone knows how to disable these dumps or move the directory without interrupting the service please do reply with how. I couldn't find a way personally though so I came up with this temporary solution and I'm sharing it with my favorite community. Again...this is NOT a permanent solution BUT it will get you by.....The source code and a compiled binary are in the attached zip file. Its written in c# and requires that .NET 2.0 or above be installed on the server. Its a PE so there is nothing to install. It can also be used remotely if you give it a UNC path to the directory you want to watch and its accessible. Here is an example of how it is used:Watcher.exe D:\MSSQL10_50.MSSQLSERVER\MSSQL\Log, mdmp;txt;log ^ A Comma separates the path from extensions. A semicolon delineates the different extensions to look for. These are the appropriate cmd line args if you are using it for the same purpose I did. Otherwisde it can be used to watch any directory for any files too so maybe it could come in handy for other uses.....Thanks

Parsing XML in sql server stored procedure

Posted: 28 Aug 2013 04:34 AM PDT

I have an XML string I need to parse into fields to be inserted into tables. I have 4 different XML strings being passed into a stored procedure to be parsed into 14 different tables. I have a large volume of XML strings being passed by a Web servers so I am getting Connection refused, Blocked and timeout errors. Currently I am able to handle 250,000 strings per hour but as volume goes above that number the errors start happening. I am using cross apply XML.nodes to parse the data from the string, is there a faster way within Sql that I need to be using. If this is the quickest way to parse XML in sql would using C# program be a better faster way to parse data into the sql tables?

Create link server for Microsoft Access2000

Posted: 28 Aug 2013 06:00 AM PDT

Hello,Can some one help to create link server for Microsoft Access2000, here is what I did but it is not working:EXEC sp_addlinkedserver @server = N'fx', @provider = N'Microsoft.ACE.OLEDB.12.0', @srvproduct = N'OLE DB Provider for ACE', @datasrc = N'\\us-balt-san-1\inhouse$\shipping\FX\DATA\fx.mdb';GO

How to Send Mail Task for Multiple packages

Posted: 28 Aug 2013 01:44 AM PDT

I have multiple execute package task running from one package. I need to send mail to multiple recipients, if it is success or failed. Also i want to show if a particular execute package task failed, send mail for it unles send one e-mail for displaying all tasks are successfully.Shaun

Renaming a Named Instance

Posted: 31 Jan 2012 01:14 PM PST

Is it possible to rename a named instance..?If yes, are there any issues..?

Help - query questions and best practise.

Posted: 27 Aug 2013 10:42 PM PDT

Hi everyone.I have situation that I hope I can get some advice on how to solve. I need to perform a check on a number of servers , post network outages on about 30 SQL servers.These are my constraints.1. Not all SQL servers in same domain so need to connect to them with SQL authentication - which works fine.2. Not all SQL servers are SQL 2008 so can't use CMS to do a multi query.3. I only have 1 machine configured to send DB mail. SMTP rules etc. are in place.So my question is can someone recommend a query to check SQL service status for a number of servers and then email the results out ?Also how do I loop thru a list of servers ? I currently have them all in a .txt file so if that could be used it would be very useful.Appreciate any help on this , was getting a bit caught up in it all therefore need some clarity on best way forward.many thanks

Failback and Restore

Posted: 23 Aug 2013 09:40 AM PDT

We are setting up Database Mirroring in our lab without a Witness. We are able to get the principal and mirror servers synchronized. When we manually failover, through their UI, everything works smoothly. Failing back, again through the UI, works.When we simulate a complete Principal shutdown, our Mirror stays in the Disconnected/In Recovery state. In order to get it out of that state and usable we need to run the following two commands:[code="sql"]ALTER DATABASE <database_name>SET PARTNER OFFRESTORE DATABASE <database_name> WITH RECOVERY[/code]Now to restart mirroring we have to go through the whole process of creating a full backup, tail backup, copy that over to the mirror (the original principal), apply the backup, and go through the Mirroring Wizard, start mirroring, and then, finally, failover to the original primary.I am just wondering if, in the above, is how it is supposed to be?

Accessing Log Shipping data using TSQL

Posted: 28 Aug 2013 01:31 AM PDT

Hi,I am using SQL Server 2008 R2 and would like to access the Transaction Log Shipping Status report using TSQL.To access this report using SSMS you right click on the SQL Server instance and then go to Reports > Standard Reports and Transaction Log Shipping Status report.I want to be able to access the Transaction Log status report data using TSQL.This data is stored in a table somewhere in SQL Server but I'm not sure where.Specifically I need access to all the columns in this report.Any help most appreciated.

SQL 2008 upgrade from standard to Developer

Posted: 27 Aug 2013 09:43 PM PDT

Hi Team,As there is no direct upgrade from sql 2k8 stand to devloper edition.Can anyone suggest how should I procced for that?

SCD not accepting connection for Teradata

Posted: 27 Aug 2013 09:43 PM PDT

HiI have an OLE DB provider for Teradata. I am trying to implement SCD using this connection manager. But the connection manager simply does not show up in the SCD component. Can someone please guide?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog