SQLServerCentral / Programming / General

Trying to run CMD file on another server

rayh 98086 — Wed, 22 May 2013 10:29:27 GMT

Hi,I am trying to run a CMD file on Server A from Server B. I understand that UNC paths do not work in CMD files. I have have also tried mapping a drive to the other server, but cannot get it to run. Does anyone have any suggestions?Thank you!

Procedure to create procedures in master

JJB@TGT — Wed, 22 May 2013 07:26:39 GMT

I'm trying to develop an "installation pack" for my servers, we are regularly adding new servers to our inventory as we host client servers. So to speed up my configuring of a new server to our standard build, I've been configuring a load of procs to apply standard settings across a range of things (maintenance, security, standard jobs etc).Something I'm trying to do is build a procedure that will create a set of procs on the master database. There's not many, there isn't a major usage of master, it's just for 1 or 2 system wide procs. Arguably I could put this in another database and we'd all be happy, but I'd prefer them to be in master if possible.The process I've been using is to push into a variable the command to build an object then executing the variable, something along these lines:[code="sql"]CREATE PROCEDURE [build].[usp_CreateGetDateView]AS DECLARE @EXSQL AS VARCHAR(MAX) SET @EXSQL = ' CREATE VIEW dbo.vwGetDate AS SELECT GETDATE() AS Result ' EXEC (@EXSQL)[/code]So the above will create a proc that when executed will create a view that returns the result of GETDATE(). It means I can repeat this for multiple objects and just run the proc.What I cannot seem to be able to do is configure a proc that once run, creates a proc in the master database. Firstly, I cannot put "CREATE PROCEDURE master.dbo.[procname]" since you cannot specify the database and I cannot put "USE master CREATE PROCEDURE dbo.[procname]" since the CREATE command needs to be the first statement in a batch (and putting GO in doesn't work either).Does anyone have any suggestions as to how I can do this or is it fundamentally not possible?I accept that if I simply had a script somewhere with all of this in, I could simply run it and if that's the best option then that's what I'll do, but I'm interested in whether there is a way to get around this restriction?

Best way to implement transactions in a procedure with If statements

hiram.osiris — Sun, 19 May 2013 11:18:07 GMT

Hello, I'm writing a complex stored procudere with a lot of IF statements, inside of each IF statement is an Update, Delete or Insert Operation. Example:[code="sql"]CREATE PROC Proc1@Var1 INT,@Var2 INT,@Var3 INTASIF @var1 IS NOT NULL AND @Var2 IS NOT NULL BEGIN Update some table Insert INTO some other table ENDIF @Var1 IS NULL AND @Var2 IS NOT NULL BEGIN delete from some table ENDIF @var3 IS NOT NULL AND @Var1 IS NULL AND @Var2 IS NULL BEGIN Update Some other table END[/code]What would be the best approach for using Transactions? Open a transaction inside each statement?Greetings :ermm:

The Empty Set vs No Result

Knowledge Draftsman — Tue, 14 May 2013 07:47:03 GMT

So, I've got somewhat of an academic question, but one that might have some practical implications... depending on the answer.So suppose I've got the following table...[code="sql"]CREATE TABLE Foo( FooId INT PRIMARY KEY, Bar VARCHAR(50), UserId INT)[/code]And another table like this...[code="sql"]CREATE TABLE Users( UserId INT PRIMARY KEY, UserName VARCHAR(50))[/code]Now suppose, I've got a stored procedure that contains a simple query. From a practical standpoint, is there any difference between[code="sql"]DECLARE @UserId INT;SELECT @UserId = UserId FROM users WHERE userName = 'myUserName';IF (@UserId Is Not Null)BEGIN SELECT Bar FROM Foo WHERE UserId = @UserIdEND[/code]... and ...[code="sql"]SELECT BarFROM Foo F INNER JOIN Users U ON F.UserId = U.UserIdWHERE U.userName = 'myUserName';[/code]From a purely academic standpoint, there is a big difference. As the first one might not return any result, but the second one will always return a result. That result just might be an empty set. That I get.But from a practical standpoint, is there any difference? In performance? To ADO.NET?I know what I would expect, but I'm curios what others think or have seen.

usp stored procedure prefix

Olja Dimitrijevic — Fri, 17 Feb 2006 08:55:00 GMT

I've been using the usp prefix for years and have been challenged by a collegue about its usage who prefers no prefixes. The only reasons that I can think of, to continue using it, are to distinguish it from the system or global procedures (sp). I am not sure how strong these reasons are. Are there other compelling arguments to do so?

Thanks,

Olja

SQL Agent Jobs in SSDT

laurie-789651 — Thu, 26 Jul 2012 05:54:11 GMT

I've got 2 SQL Agent jobs to deploy with a database, the whole lot being in a Sql Server Development Tools project. There is no dedicated Agent Job object, so I've scripted them in the post-deployment script using drop & re-create. (Obviously they're in msdb database.)Is this the best way of doing it?When you script the 'Schedule', the parameters differ between 2005/2008 so it is necessary to handle this issue in the script.

Upload directory in Stored Procedure

rayh 98086 — Mon, 06 May 2013 09:54:40 GMT

Hi,I am using a stored procedure and would like it to find a file on a different server location. When on the server, I have access to the drive using UNC or if I map a drive. However, when I try to do this within my Stored Procedure it does not work. Any suggestions to what the problem may be? The problem seems to be related to trying to reach the other server. (Could this be a network permission?)Here is my code: @GetFileName nvarchar(500),@filedir nvarchar(50),@cleanfilename nvarchar(500),@filepath nvarchar(500),@dircmd nvarchar(500),-- 1. Get File from Upload directory and pull out file name-- SET @filedir = 'Y:\' Mapped drive attemptSET @filedir = '\\servername\directory\'SET @cleanfilename = (SELECT SUBSTRING(@getfilename,4,100))SET @filepath = @filedir + @cleanfilenameSET @dircmd = 'dir ' + @filepath

Auto Recovery Information in SSMS

mark_copley — Wed, 01 May 2013 09:05:43 GMT

I know this might be a simple question but I have not been able to find my answer as of yet and thought I would throw it out there in hopes somebody knows the answer. In my SSMS (management studio) I get the 'Saving Auto Recovery Information' message when it is saving off the Auto Recovery Information which I do not have an issue with my issue is the frequency of this seems to set to every couple minutes and I want to set that to every 30min if possible.Any help appreciated.Microsoft SQL Server Management Studio 10.0.5500.0Microsoft Analysis Services Client Tools 10.0.5500.0Microsoft Data Access Components (MDAC) 6.1.7601.17514Microsoft MSXML 3.04.05.06.0Microsoft Internet Explorer 8.0.7601.17514Microsoft.NET Framework 2.0.50727.5448Operating System 6.1.7601

Updating information in a datagrid by using a button

rayh 98086 — Fri, 26 Apr 2013 09:18:24 GMT

Hi,Another C# question here. What I am trying to do is update information within a datagrid that could contain multiple records and multiple edits when clicking an Update button. When I run my code I get no errors, but also no action happens either. Can you help me understand what I am missing here?SqlConnection myConnection = new SqlConnection(Test_Utility.Properties.Settings.Default.ConnectionString); SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter("Select * from Assignment", myConnection); mySqlDataAdapter.UpdateCommand = new SqlCommand("Update Assignment SET Active='0' WHERE ID='99999'", myConnection); SqlCommandBuilder cb = new SqlCommandBuilder(mySqlDataAdapter); cb.GetUpdateCommand(); DataSet ds = new DataSet(); mySqlDataAdapter.Fill(ds); dataGridView1.DataSource = ds; mySqlDataAdapter.Update(ds);Thanks in advance!

Creating database,stored procedures from batch file

adlakha.22 — Wed, 24 Apr 2013 04:29:12 GMT

Hello,I am using below code to execute a sql scripts through batch file.....[passing parameters ].[code="plain"]rem This script executes the scripts to create the database, tables and stored proceduresecho offecho onosql -n -m-1 -E -S ADLAKHA\SQLEXPRESS -d HOLDB -i D:\batch\Server.sql -v tbname=employ -o hi.txt echo offecho Script execution is complete![/code]Server.sql : - contains any query using parameter from batch file[code="plain"]select * from $(tbName)[/code][b]GETTING ERROR : Msg 102, Level 15, State 1, Server MANISHA\SQLEXPRESS, Line 1Incorrect syntax near '$'[/b]I am new to batch files, Could any one please suggest me how to pass parameters from batch file..?Thanks

How to copy a table structure and data to a different server?

Blair Dee-474691 — Wed, 24 Apr 2013 10:04:53 GMT

I am wondering if there is a simple command I can run to copy a table structure and its data from one SQL server to another. I am connected to both servers in SSMS and I know I can do a select into to do this to a different database on the same server but is something like this possible if i want to create the table on another server?

Hiding a dataGridView

rayh 98086 — Mon, 29 Apr 2013 09:26:35 GMT

Hi,Is there a way to hide or disable a datagridview completly? I have a few radio button options and would like to display different datagrids in the same location if possible.I have tried the following but this does not work: dataGridView1.Enabled = false;

How to pull a return value from Stored procedure into Winows Form using c#

rayh 98086 — Thu, 25 Apr 2013 16:13:19 GMT

Hi,I am trying to pull a return a single value from a stored procedure, but cannot seem to find the correct logic.Can someone share with me help me with my code and tell me where I would put the return value?conn = new SqlConnection(Test_Utility.Properties.Settings.Default.ConnectionString); conn.Open(); SqlCommand cmd = new SqlCommand("sp_UploadFile", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@RunNo", theRunNo)); cmd.Parameters.Add(new SqlParameter("@getFileName", txtFileToProcess.Text)); rdr = cmd.ExecuteReader(); rdr.Close(); rdr.Dispose();

Ocassional rare "Self-Commit" with ADO .NET and SQL Server 2008

Nemachtiani — Mon, 08 Apr 2013 08:55:35 GMT

HiWe've got a production environment with SQL Server 2008 R2 using VB .NET code and .NET Framework 3.5., our database was a SQL Server 2000 migrated to SQL SERVER 2008 R2We've got this environment running just fine for many time (years) with no drastic changes after migration.I'l' try to focus this post as an SQL Server 2008 R2 issue as I already post this case in Microsoft ADO NET Forum (without a usuable response or tip to solve it...or reproduce it), if forum admin don't mind all detail is here: [url=http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/6d8e3a86-8a55-462b-8896-871a67ca9029][/url]A transaction started with ADO .NET code (sqlConnection, sqlTransaction, sqlCommand involved) COMMITS itself, data is persisted even though .NET Rollback() is executed (also this Rollback() doesn't throw a .NET exception).no errors have been found in SQL Server logs, we did turn on deadlock events to be written to log but no deadlock events where foundthrough our code (.NET) we open a connection (sqlConnection instance), begin a transaction ( initialize an sqlTransaction instance through "conn.BeginTransaction" where was already open "conn.Open()" ) and use this sqlTransaction instance and its Connection property to execute 5 SPsthe rare case happens ocassionally when executing the first Stored Procedure through an sqlCommand.ExecuteScalar(), this SP returns an IDENTITY value, internally uses a T-SQL transaction: BEGIN TRAN ... COMMIT with IF @@ERROR<>0 ...ROLLBACK TRAN - RAISERROR- RETURN clauses... the Identity value first SP returns (not with RETURN clause, with SELECT) we use it as an "ID" parameter for the other 4 SPswhen this rare event happens we know first SP has been executed because: we got the Identity value in our "custom logs", no .NET exceptions neither SQL errors occur, can see persisted data with the returned Identity value in two tablesthe Connection property of sqlTransaction instance its turned to nothing (tran.Connection=Nothing) so the next attempt to using it causes a .NET "System.InvalidOperationException...Connection property has not been initialized"...this is how we noticed we got a problem, but we haven´t found why it was lost...the "rare" of this case is that ~99.97% of the time its working as expected: of the 5 diferent SPs, 2 of them are over other Database (SQL Server 2008 R2 either) in same server, but when this rare case happens this sps are not executed.we CAN "reprocess" the SAME input, in the same environment, with the same code involved and IT WORKS....I did try some test doing our first SP to return high severity errors but in developer environment this high severity errors DID cause events in SQL server log, also ADO .NET throws .NET ExceptionsOur .NET code has try... catch blocks and logs any .NET Exception, even I just add a "custom logger" class when this event happens and give us all detaillast thing we try was running "SELECT @@TRANCOUNT" and "SELECT @@SSPID", but as we already know we will lost the Connection I make a "copy" of the Connection poperty of sqlTransaction before invoking the .ExecuteScalar() when this event happens and found that the copy REMAINS (not "Nothing") and also its State = Openso running "SELECT @@TRANCOUNT" and "SELECT @@SPID" give this results:@@TRANCOUNT = 1 with sqlTransaction.Connection BEFORE .ExecuteScalar()@@TRANCOUNT = 1 with "copy" BEFORE .ExecuteScalar()@@SPID = 97 with sqlTransaction.Connection BEFORE .ExecuteScalar()@@SPID = 97 with "copy" BEFORE .ExecuteScalar().ExecuteScalar() -- case happens, sqlTransaction.Connection = Nothing, also "copy" <> Nothing and "copy.State" = Opencannot run SELECT @@TRANCOUNT with sqlTransaction.Connection AFTER .ExecuteScalar()@@TRANCOUNT = 0 (ZERO) with "copy" AFTER .ExecuteScalar()cannot run SELECT @@SPID with sqlTransaction.Connection AFTER .ExecuteScalar()@@SPID = 97 with "copy" AFTER .ExecuteScalar()Unfortunatelly cannot start a sql profiler: 1. its production, 2. doesn´t happen again when re-trying (reprocessing)Does anyone could give us a clue or tip to find a proper way to reproduce it... or solve it?thanks a lot

Updating datagrid with C#.net

rayh 98086 — Tue, 16 Apr 2013 12:30:45 GMT

Hi,I am a newbie and am trying to figure out how to update data from a datagrid to a database. I have copied exisiting code but it is not doing exactly what I would like. My two questions that I have are:1.) How do I put loop here to update all records in data grid????????????????????2.) What is the correct syntax to pass an update from data grid to the database????? int TestID = 999999; DataSet ds_Assignment = new DataSet(); dataGridView1.DataSource = ds_Assignment; SqlConnection myConnection = new SqlConnection(Assignment_Utility.Properties.Settings.Default.STH_D_TESTConnectionString); try { myConnection.Open(); } catch (Exception eConn) { MessageBox.Show("error opening database connection." + eConn.ToString()); return; } SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter("Select * from Assignment where TestID = " + TestID.ToString(), myConnection); SqlCommandBuilder cb = new SqlCommandBuilder(mySqlDataAdapter); cb.GetUpdateCommand(); mySqlDataAdapter.Fill(ds_Assignment, "Assignment");// 1.) How do I put loop here to update all records in data grid????????????????????// 2.) What is the correct syntax to pass an update from data grid to the database????? ds_Assignment.Tables[0].Rows[0]["EndDt"] = WHAT DO I DO HERE TO GET UPDATE FROM DATA GRID? ds_Assignment.Tables[0].Rows[0]["Active"] = "0"; ds_Assignment.Tables[0].Rows[0]["StatusType"] = "REVO"; try { mySqlDataAdapter.Update(ds_Assignment, "Assignment"); }Thank you in advance!!!!

changing directory in .bat files

YarHad — Tue, 09 Apr 2013 08:17:30 GMT

Hi All,I am trying to use to compress some backup files with the 7zip program.I am able to compress the files via the command prompt by doing the following1. I first of all change the directory by entering ...cd\program files\7zip2. I press enter3 then I enter the following command...7z a -t7z C:\offers\datafile.7z "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\*" -p"withoutytreedd" -mhe -r -yThis compresses the files and add them to datafile.7zHowever when I save7z a -t7z C:\offers\datafile.7z "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\*" -p"withoutytreedd" -mhe -r -yto a batch file (.bat), it doesn't work.My question is 'How do I change the directory within the same batch file so that I can schedule it via windows scheduler?I need the batch file to 1. change the directory2. compress the files and add it to the zip file (datafile.7z)Thanks for your help

query other databases from the information in another database

pjuster — Mon, 25 Mar 2013 11:06:12 GMT

Hi,I have a database called databaseA with a table called, otherdbs with two columnsserver databasenameserver1 partsserver2 partsI would like a query that queries databaseA and table called otherdbs, which then uses the server and databasename to issue another query on each of the databases.Apologies for the lack of details, is this possible? any hints on where to start would be brilliant.

Find duplicate client records and suggest one record to keep

peterlim05 — Mon, 25 Mar 2013 05:39:06 GMT

I'm trying to get a list of duplicate records and suggest one to keep.The first column returns the unique client numbers (custno) and the second column (keepno) should be the Max value of that group.I'm struggling to get the keepno value.The following code I've done so far finds a lists the duplicates okay (based on name and zip code) but I don't know how to get that keepno. It's probably a two step procedure?[code="sql"]SELECT a.custno, RTRIM(a.firstname)+' '+RTRIM(a.lastname) AS name, company, RTRIM(address1) as address1, add1.zip FROM cusfil a JOIN address add1 ON a.custno=add1.custno WHERE RTRIM(a.firstname)+RTRIM(a.lastname)+RTRIM(add1.zip) IN ( SELECT RTRIM(b.firstname)+RTRIM(b.lastname)+RTRIM(add2.zip) FROM cusfil b JOIN address add2 ON b.custno=add2.custno WHERE add2.curraddr=1 AND RTRIM(b.lastname)<>'' GROUP BY b.firstname,b.lastname,add2.zip HAVING COUNT(b.firstname+b.lastname+add2.zip)>1 ) AND add1.curraddr=1 AND RTRIM(a.lastname)<>'' ORDER BY a.lastname,a.firstname,add1.zip [/code]example results:custno keepno name company address1 zip"8001" "8001" "John Doe" "" "10 Happy St" "1114""15" "8001" "John Doe" "" "10 Happy St" "1114""7456" "7456" "Fred Finstone" "" "121 Bedrock Pl" "203""147" "7456" "Fred Finstone" "" "121 Bedrock Pl" "203"

Alias / CTE alternative

sdhanpaul — Fri, 22 Mar 2013 18:16:01 GMT

More often than not I am wrong about my logic towards database problem solving. Hence, I ask your opinion on this logic:Problem:1. Alias cannot be reference to a calculated field in a stored procedure2. CTE's can be a bit ... so so... to deal with.3. You have to type each calculation, over and over and over again if one calculation depends on another.My solution:Create a physical temporary table that stores the data.Update each column of the temp data table using a single stored procedure one column at a time.Example:employees over the age of 60 do not contribute to insurance scheme... hence ... I would have something like this...employee name date of birth Age Earnings Insurance Contribution net pay where age is a calculated field based on date of birth and insurance contribution is also a calculated field based on age and net pay is based on earnings less insurance contribution!My solution is simple:have the above as a temp table....Update the age fieldupdate the insurance contributionupdate the net pay (earnings - ic)run my report from that table... then delete the temp data.Can anyone advise what the disadvantages of using this approach will be? My main concern is speed...Generally its a theory.... say you have 10 calculated fields each dependent on another.... Comments welcomed.Thanks.

what next?is it helpfull in future

gurjer48 — Sat, 23 Mar 2013 00:02:08 GMT

I am working in a electronics company in sql reporting from past 6 month .we here generate small reports of running hrs of machinery,material consumption.i use sql server 2008 and display report in visual studio.will this type job can help me in future.because i am working in electronics company is it difficult to move to software because its diff than what we do here.please guide me

How to store SQL Server job code and stored procedure in SVN Repository

amar_kaur16 — Fri, 22 Mar 2013 12:13:37 GMT

what is the best way to store SQL Server jobs code and stored procedure in SVN Repository so that other team members can access it as wellThanks,Blyzzard

A tricky one

Matt-1034261 — Sun, 17 Mar 2013 05:06:35 GMT

HiI have a table of links to images. I have an identity key for a particular image but i want to get the identity value of the image 4 rows beneath it using the current identity value. Is there an SQL statement that could do this?ThanksMatt

"instead of delete" trigger is not fired from "after" trigger

EKD — Wed, 21 Nov 2012 00:05:23 GMT

Hi everyone,I have a problem that "instead of delete" trigger is not fired from "after insert" trigger. Reading BOL it seems that "after" triggers should fire "instead of" triggers. I have nested triggers option set to 1, recursive triggers option is on. Below is an example. The example is very simplified (no constraints, no real life logic) and I use it just to demonstrate the problem. Table t1 has 2 triggers - instead of delete trigger justwrites to t1History table. Second trigger calls delete from t1 if the same row as valid inserted already exists with status 0.You can see that when test statement1 is called, history row is not created even though the old row is deleted.Can you please help me understand why the "instead of delete" trigger is not fired from "after" trigger? I have SQL Server 2008 R2.select SERVERPROPERTY('ProductVersion'), SERVERPROPERTY('ProductLevel'), SERVERPROPERTY('Edition')10.50.2500.0 SP1 Enterprise Edition (64-bit)Thank you,EKDcreate table t1History( itemCode int, itemName varchar(200), eventdate smalldatetime)create table t1( itemCode int, itemName varchar(200), itemStatus int --0- old, 1- new)insert into t1 (itemCode, itemName, itemStatus) values (1, '111', 0)insert into t1 (itemCode, itemName, itemStatus) values (1, '222', 1)gocreate trigger t1AfterInsert on t1for insertasdelete From t1 where exists (Select 1 from inserted where inserted.itemName = t1.itemName and inserted.itemStatus = 1 and t1.itemStatus = 0)gocreate trigger t1InsteadOfDelete on t1instead of deleteasinsert into t1History select itemCode, itemName, GETDATE() from deleteddelete from t1 from t1join deleted d on d.itemCode = t1.itemCode and d.itemName = t1.itemName and d.itemStatus = t1.itemStatusgo--test statement1---- the below statement calls after insert trigger and teh row with status 0 is successfully deleted, but no row in t1History is created!insert into t1 (itemCode, itemName, itemStatus) values (1, '111', 1)--test statement2--the below statement correctly fires instead of delete triggerdelete From t1 where itemName = '222'

Save SP to table

stryk — Thu, 14 Mar 2013 02:21:10 GMT

Hi there!Hmmm ... actually I read a lot of articles about how to accomplish that, and indeed I could make this work in another case ... but this time I always get an error like "[i]you cannot have nested INSERT EXEC commands[/i]" or something (the original message is in German).This is what I try:[code="sql"]CREATE TABLE #mirroring_stats( [database_name] SYSNAME ,[role] INT ,[mirroring_state] INT ,[witness_status] INT ,[log_generation_rate] INT ,[unsent_log] INT ,[send_rate] INT ,[unrestored_log] INT ,[recovery_rate] INT ,[transaction_delay] INT ,[transactions_per_sec] INT ,[average_delay] INT ,[time_recorded] DATETIME ,[time_behind] DATETIME ,[local_time] DATETIME)INSERT INTO #mirroring_stats( [database_name] ,[role] ,[mirroring_state] ,[witness_status] ,[log_generation_rate] ,[unsent_log] ,[send_rate] ,[unrestored_log] ,[recovery_rate] ,[transaction_delay] ,[transactions_per_sec] ,[average_delay] ,[time_recorded] ,[time_behind] ,[local_time] )EXEC msdb..sp_dbmmonitorresults @database_name = 'NAV_Mirroring' ,@mode = 0 ,@update_table = 1SELECT * FROM #mirroring_statsDROP TABLE #mirroring_stats[/code]So actually I want to save the output of sp_dbmmonitorresults into a temporary table ...What am I doing wrong???Any help appreciated!Cheers,Jörg

Web dev suggestions

T_Peters — Fri, 22 Feb 2013 10:14:32 GMT

In some circles this could be as touchy a topic as asking which religion to follow. I'm a full time DBA with an interested in learning some web development. I have some databases, both MS SQL and MySQL, that I'd like to put my own web front end on. It would be mostly for displaying aggregated data and some data entry. I haven't done any web development since learning HTML 10+ years ago. Any suggestions on what language to start with? Thanks,Tim

cmd("@xxx") vs cmd.Parameters.Append cmd.CreateParameter("@xxx")

gbatta — Thu, 07 Mar 2013 14:35:44 GMT

Can anyone tell me if the following two methods of sending information to a store procedure are behaving in the same way? Is there a benefit to one over the other, mostly in terms of security? Both ways work for me, I'm just wondering what the difference is. Thank you!--METHOD #1-- cmd.CommandText = "spGetInfo" cmd("@InfoID") = CInt(InfoID)) cmd("@Visits") = CInt(1) cmd("@View") = "Full" Set rs = Server.CreateObject("ADODB.Recordset") rs.CursorLocation = 3 rs.CursorType = 3 rs.LockType = 3 rs.Open Cmd --METHOD #2-- cmd.CommandText = "spGetInfo" cmd.CommandType = 4 cmd.Prepared = true cmd.Parameters.Append cmd.CreateParameter("@InfoID", 3, 1, 4, CInt(InfoID)) cmd.Parameters.Append cmd.CreateParameter("@Visits", 3, 1, 4, CInt(1)) cmd.Parameters.Append cmd.CreateParameter("@View", 200, 1, 30, "Full") Set rs = Server.CreateObject("ADODB.Recordset") rs.CursorLocation = 3 rs.CursorType = 3 rs.LockType = 3 rs.Open Cmd--STORED PROCEDURE-- ALTER PROCEDURE [spGetInfo] @InfoID int = 0, @Visits int = 0, @View nvarchar(10) = null AS IF @View = 'Full' BEGIN SELECT * FROM tbInfo WHERE InfoID = @InfoID END

column selection on dynamic basis

asela115 — Sun, 24 Feb 2013 11:23:08 GMT

Hi,How can i select columns of a table dynamically through a select statement if the column name need to be changed based on the value selected in a Combo Box in a different form.it's like select column A from table X if combo box selection is A, select column B from same table if combo box selection is B etc..Please assistRegards,asela115

Generating JSON from SQL

Ray K — Thu, 24 Mar 2011 08:04:59 GMT

Does anyone know of (or can recommend) a resource that tells me how to convert SQL query results into JSON format?Thanks!

Query Analyzer

hiemmi — Mon, 13 Feb 2006 21:37:00 GMT

Hi,Does anyone know if Query Analyzer stores all the queries that are executed into a history file and if so how do i view the last executed query which i did not save it.Thanks.

is there a delay?

wm.m.thompson — Wed, 06 Feb 2013 18:07:21 GMT

I just posted a question today.I have gone back to see if there was any answers, but I cannot find the question. is there a delay in answering or is there a pause or delay when posting new questions?

Can I return an identity value to be used in an insert trigger on a table?

njfuller — Wed, 06 Feb 2013 10:51:46 GMT

Something along the lines of ....create TRIGGER [dbo].[trg_tb1_Insert] ON [dbo].[tb_tb1] AFTER INSERTASBEGIN SET NOCOUNT ON; UPDATE dbo.tb2 SET tb2_id = SCOPE_IDENTITY() FROM dbo.tb1 INNER JOIN Inserted ON Inserted.tb1_id = dbo.tb1.tb1_idENDgo

"\1" in a formula (Translating MSAccess to T-SQL)

Sim-473257 — Fri, 01 Feb 2013 05:56:43 GMT

Well I feel dumb asking this, but I've Googled my fingertips off ...I have been given some SQL from MSAccess, and asked to translate it to T-SQL.One of the expressions selected is "Select [value]\1 as [Field1]"Does anyone have any idea what the "\1" is meant to do ?Unfortunately it seems I can find out how to build half an atom bomb on google, but something as simple as a "\" ... is beyond me :(Many Thanks

Add SQL Objects to TFS/ Version Control

Vishal Sinha — Fri, 16 Jul 2010 12:52:44 GMT

I work for a small company and we are trying to get all database objects in to TFS. Our dot net code is already in TFS but SQL objects code still not in source safe.We use TFS 2005 and Visual Studio 2005 and SQL 2008.Currently we only add SQL objects to TFS if we make changes to it recently. What we are trying to do is add all objects in to TFS one time and then manage it from within TFS - this will give us a history of all changes and also help us version the scripts.One issue though - we need to maintain different versions of the code since some of our customers are in different versions. Like our latest build is 7.3.0 but some customers are in 7.0.0. and some are in 6.x.x etc. So every time we start a new build effort, we branch the code and start developing in the version. How will the SQL objects be managed if we are to create a branch and yet maintain history of changes?Are we trying to accomplish too much? Would you have any ideas on to do this (add SQL objects to TFS and Source Control at the very least)?

Revoking select/execute on sys tables from PUBLIC

johnnyrmtl — Thu, 24 Jan 2013 09:07:16 GMT

Hello,I have a task from an audit to remove all executes and selects from the sys schema from PUBLIC.i.e ....revoke Execute ON [sys].[sp_oledb_defdb] FROM [public]Is this safe and where can I find the Microsoft article explaiing why not to do this ?many thx

Reverse string without built in functions

pshvets — Tue, 14 Apr 2009 18:40:58 GMT

Hello all.I am trying to write a function which reverses passed string WITHOUT using any built-in functionsSo if 'abc' is passed, it returns 'cba'I want to use recursion. Here is what I got:create function StringReverse( @InString varchar(20))returns varchar(20)ASbegindeclare @RevString varchar(20)IF len(@InString) in (0,1) set @RevString = @InStringELSEset @RevString = ( dbo.StringReverse(substring(@InString, len(@InString)/2+1, len(@InString)) + dbo.StringReverse(substring(@InString, 1, len(@InString)/2))) )return @RevStringendIt compiles fine, but when I call it, it throws an exception:select dbo.StringReverse('abc')Msg 217, Level 16, State 1, Line 1Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).Why? Function calls itself at most 5 times:Pass1 - RevString('c') + RevString('ab')Pass2 - 'c' + (RevString(RevString('b') + RevString('a'))Pass3 - 'cba'Should I use a loop?Thanks in advance for your help!Pit

Check Datetime Existence within multiple columns

adnan8t2 — Wed, 23 Jan 2013 05:07:30 GMT

Hi there,I've a BillDate as date, and a Mark as bit column in First table.(Mark=0 by default)In Second table I've FromDate as date, and ToDate as date Column.I want to set Mark=1 if BillDate is exists between FromDate & ToDateLet Say In First Table the data is----------------------------BillDate | Mark----------------------------2012-11-10 11:15:30 | 02012-12-12 09:00:00 | 0In Second Table the data is---------------------------------------------FromDate | ToDate---------------------------------------------2012-11-01 07:00:00 | 2012-11-09 23:59:592012-12-08 07:00:00 | 2012-12-15 23:59:59So in the above scenario only the second row from First tablewhich is having, BillDate->2012-12-12 09:00:00 will be Mark as 1because it comes between second row of second tableI hope I've explained my scenario,

How to prevent duplicate rows inserted by SP called simultaneously by different SPIDs (without unique constraint on table)

Laurence Neville — Wed, 09 Jan 2013 08:58:39 GMT

A stored procedure "AllocatePaymentToOrderItems" creates rows in a table "Transactions". The intention is that the SP never creates duplicates, i.e. if any records already exist in the table with the same OrderID, PaymentID and TransactionTypeID, the SP should insert zero rows. OrderID, PaymentID and TransactionTypeID are constants either passed in as parameters or calculated inside the SP. The SP code is below.There is no unique constraint/index on OrderID, PaymentID and TransactionTypeID in the Transactions table - this is intended, because in other scenarios is is legal to have duplicates - they just must never be created by this SP.There is a check within the SP that no rows already exist at the time the insert is performed.The problem is that if the SP is called simultaneously with the same parameters by separate SPIDs, duplicates are created. The SPIDs have to reach the insert at exactly the same moment, but this has been seen to happen (usually due to a lock being placed on a dependant table e.g. TransactionTypes - see below, which is then released, allowing SPIDs that were blocked at the same point in the SP to progress).I have tried all the following but none eliminate the issue:1. Wrapping the insert in BEGIN TRAN / COMMIT TRAN2. Setting the TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED, REPEATABLE READ and SERIALIZABLE3. Using an IF NOT EXISTS (SELECT ....) prior to the insert instead of the LEFT JOINI am looking for a solution that can be implemented within the SP. I also would like to avoid solutions that just try to reduce the probability of the issue arising e.g. by reading from the dependant tables without locks. Lastly, there are a high number of reads and writes against the Transactions table, so I have to lock it in the most minimal way.Any ideas? Many thanks.PS: this is SQL Server 2008 R2/******************************************************************************************************/create procedure dbo.AllocatePaymentToOrderItems( @OrderID int, @PaymentID int)asbegin declare @TransactionTypeID int select @TransactionTypeID = tr.TransactionTypeID from TransactionTypes tr where tr.TransactionTypeCode = 'PAYM' create table #NewTransactions ( OrderID int not null, OrderLineNumber int not null, AmountAllocated decimal(10,2) not null, primary key (OrderID, OrderLineNumber) ) /* LOGIC TO POPULATE TEMP TABLE GOES HERE */ -- Add the transactions insert Transactions ( TransactionDate, TransactionTypeID, OrderID, OrderLineNumber, Value, PaymentID ) select getdate() as TransactionDate, @TransactionTypeID, pa.OrderID, pa.OrderLineNumber, pa.AmountAllocated as Value, @PaymentID from #NewTransactions pa left join Transactions t on pa.OrderID = t.OrderID and pa.PaymentID = t.PaymentID and t.TransactionTypeID = @TransactionTypeID where t.TransactionID is null -- Ensure no equivalent transactions already exist in the orderendgo/******************************************************************************************************/

Duplicate mails from sp_send_dbmail within trigger

johnnyrmtl — Tue, 15 Jan 2013 08:58:32 GMT

Hello,I have a trigger on a table and when a record is inserted I receive the email 3 times.I did a quick search and haven't found anything to resolve the issue. any ideas ? as this is my trigger.USE [ABC]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER TRIGGER [dbo].[table1] ON [dbo].[table1] FOR INSERT, UPDATE, DELETE AS BEGIN DECLARE @EmailTXT varchar(max) IF (Select Count(*) from Inserted) > 0 BEGIN -- inserted Select @EmailTXT = srvname + ' ' + name + ' ' from inserted EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBA Alert', @recipients = 'emailhidden', @subject = 'A record has been inserted or modified on table blabla on server. Please Verify', @body_format = 'HTML', @importance = 'High', @body = @EmailTXT END ELSE If (Select Count(*) from Deleted) > 0 BEGIN -- deleted Select @EmailTXT = srvname + ' ' + name + ' ' from deleted EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBA Alert', @recipients = email hidden, @subject = 'A record has been deleted on table blabla on server. Please Verify', @body_format = 'HTML', @importance = 'High', @body = @EmailTXT END END

importing data remotly executing.

jason-810420 — Mon, 14 Jan 2013 05:58:55 GMT

This must be possible but never looked into.At the Moment I have some FTP software that every hour checks a folder location for a file and then copies and imports it into the db etc.I would like to have say the client software push the data to the location and this is the part I haven't a clue about, the client software tell MSSQL to execute a stored procedure or a vbscript to import the file and do whatever is necessary.what different ways can this be done, just an area I know next to nothing about.thanksJasemilly

problem in merging 2 sql queries into a single query to distinguish old and new.plz help!!

suvo.kundu — Sat, 12 Jan 2013 06:32:58 GMT

in my table i have the records likeId Record1 Record2 Level gid1 testRec1 testRec2 0 12 testRec11 testRec22 0 23 testRec111 testRec222 0 34 testRec111 testRec2222 0 4 5 newtestRec newtestRec2 1 1I want to show records likeId OldRecord1 NewRecord1 OldRecord2 NewRecord2 1 testRec1 newtestRec1 testRec2 newtestRec2 2 testRec11 NoNewRecord testRec22 NoNewRecord3 testRec22 NoNewRecord testRec33 NoNewRecord4 testRec33 NoNewRecord testRec33 NoNewRecordactually i have 2 querys this is for old recordselect * from tablename where level=0this is for New recordselect * from tablename where level=1I want to merge this into a single record set as old and new..plz help