Wednesday, June 19, 2013

[SQL 2012] Creating Snippets in 2012

[SQL 2012] Creating Snippets in 2012

Creating Snippets in 2012

Posted: 19 Jun 2013 02:45 AM PDT

Hi SSC,I'm a big fan of using snippets to reduce typing the same things over and over again. For 2008 I had [url=http://www.ssmstoolspack.com/]a piece of freeware[/url] which worked really nicely for snippets, but for 2012, it's no longer free. However with the integration of SQL into visual studio, snippets are now built in to the IDE. The problem is, I have no idea how to use them.I've found articles which show how to reference the folder in which they reside and how to put them into the query window, but what I haven't been able to find is how to actually write the XML which constitutes a snippet. The best I've been able to do is try to reverse engineer existing snippets, but that only goes so far. Does anyone have a good resource or explanation of the schema of a snippet XML document so I can build my own quicker and more flexibly? NOTE: This is about the best example I've been able to find for the purposes of re-engineering[code="xml"]<?xml version="1.0" encoding="utf-8" ?><CodeSnippets xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet"> <CodeSnippet Format="1.0.0"> <Header> <Title>Template - Insert</Title> <Description>Demo for Insert Code Snippet.</Description> <Author> Ken O. Bonn </Author> <SnippetTypes> <SnippetType>Expansion</SnippetType> </SnippetTypes> </Header> <Snippet> <Declarations> <Literal> <ID>DatabaseName</ID> <ToolTip>Name of database.</ToolTip> <Default></Default> </Literal> </Declarations> <Code Language="SQL"> <![CDATA[ ------------------------------------------------------ -- FILENAME: Template-Expansion.snippet -- PURPOSE: Template for Expansion (Insert) snippets. -- AUTHOR: Ken O. Bonn -- DATE: May 15, 2013 ------------------------------------------------------ SET NOCOUNT ON; EXEC SP_HELPDB $DatabaseName$; ]]> </Code> </Snippet> </CodeSnippet></CodeSnippets>[/code]

Restoring a database everynight

Posted: 19 Jun 2013 01:10 AM PDT

Hello all, I was wondering if you could assit with the following please.I am in the process of a data warehouse project and one of our suppliers who hosts our data will be uploading a .BAK file of the database to an SFTP server that we have set up.I want to automate the restore of the .BAK file directly from the SFTP (which I'm fine with as the SFTP is hosted internally and I can restore from a unc path).The code I will use for this is as below - USE masterRESTORE DATABASE [Oneserve_Stage] FROM DISK = N'\\server\oneserve_DW\ExportFromOneserve\MHS_DataCut_PROD.BAK' WITH FILE = 1, REPLACE, RECOVERYWhat I want to then do once it has restored is move the .BAK file to another location - call it server2 and delete any exsisting .BAK files that may exsist there already.Is there a way I can automate this all in SSIS at all as a project that I can then schedule?Would that be the best way to do it?

Build multi subnet active / active cluster SQL 2012

Posted: 18 Jun 2013 11:03 PM PDT

Hi,my company had offices in 4 different geographical location and want to build Multi subnet cluster with active / active, to make database available for all location to update centrally. Our intention is to connect cluster database through local active instance to get performance.1. I like to know is it possible to build active / active multi subnet cluster ?2. If not what could be the best solution in sql to resolve this ? We Implemented replication but they create data conflict. regardsSachin

Limiting the number of databases on an instance.

Posted: 18 Jun 2013 08:49 PM PDT

Good day all,I would like to know if it is possible to limit the number of databases on an instance and if yes, how can I do it? I thank you in advance for your assistance.

SSIS 2012 Package Won't Run as SQL Server Agent Job

Posted: 06 Jun 2013 10:04 AM PDT

Hello, I'm new to the forum so sorry in advance for any etiquette I break :)I have an SSIS 2012 package that has a OLE DB data source connecting to an Access Database. The package runs fine when i run it from SQL Server Data Tools. I then deploy it to an SSIS Catalog and I can run the package fine from there. Next I add it as the only step in an SQL Server Agent Job and I get 4 consistent error messages."failed validation and returned validation status "VS_ISBROKEN"""One or more component failed validation""There were errors during task validation""An error occurred executing the provided SQL command: "select * from 'zqryExportTesting'". Object reference not set to an instance of an object.I've tries everything i can find. I've set the package encryption to be EncryptSensativeWithPassword instead of the default EncryptSensativeWithUserID. I've tried the 32 vs 64 bit runtime (I'm using 32 in the scenarios where it works). I've set the SQL Server Agent services to log on as the same user I am currently logged on to the server as. I've set up a proxy with the same user I am logged on to the servers as. I'm pretty sure i've tried every combination i've researched and still got nothing. I'm sure there is a simple setting or trick I'm missing.Thanks a TON in advance for any help you can provide.-Nick

Adding parameter to SqlCommand hides query from the profiler

Posted: 18 Jun 2013 10:25 AM PDT

I am using the SQL profiler to look at queries coming into my sql server. I have noticed that adding actual parameters to my query object in Ado.net causes the query not to show up in the profiler.For example, the code below works just fine, but the profiler can't see it. If I comment out the line where I add the parameter, I'll get an error, but the profiler is also able to see the query. What is happening here? Is this a bug? Is there a setting I am missing on the profiler? Currently I have it set to just look for "BatchStarting" events. [code="other"]using (var connection = new SqlConnection("Data Source=x2;Initial Catalog=dummy;Integrated Security=True;")) { connection.Open(); var command = new SqlCommand( "SELECT COUNT(*) FROM dummyTable WHERE dummyName LIKE @myparam", connection); command.Parameters.AddWithValue("myparam", "Fred"); Console.WriteLine(command.ExecuteScalar()); }[/code]

project parameters

Posted: 18 Jun 2013 09:53 AM PDT

How do I write values back to the project parameters in SSIS 2012?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog