Debugging SQL Server Statements In Code

Jul 28, 2005

How can I view the raw SQL code that ASP.NET sends to SQL Server when using parameters?

The code below doesn't display the actual SQL statement, but instead: System.Data.SqlClient.SqlCommand.

string SQL = "INSERT INTO [BLAH] VALUES (@startDate)";
SqlConnection mySqlConn = new SqlConnection(ConfigurationSettings.AppSettings["DSN"]);
SqlCommand mySqlCmd = new SqlCommand(SQL, mySqlConn);

mySqlCmd.Parameters.Add(new SqlParameter("@startDate", SqlDbType.DateTime));
mySqlCmd.Parameters["@startDate"].Value = Request.Form["start_date"];

lblSQLDebug.Text = mySqlCmd.ToString();

TIA.

View 6 Replies


ADVERTISEMENT

Dataset Code Debugging

Jul 23, 2005

Is there any better way to debug a query encased in IIF statements thanto find bugs at runtime? I have an especially ugly CommandText for oneof my datasets, and debugging it when there is an error is nearlyimpossible. I realize that it's much cleaner when you have straightSQL, but otherwise the tool doesn't seem to be very user-friendly.Thanks,Mike

View 1 Replies View Related

DML Statements In Code Vs. Stored Procedures

Aug 4, 2005

Hi,We're having a big discussion with a customer about where to store the SQL and DML statements. (We're talking about SQL Server 2000)We're convinced that having all statements in the code (data access layer) is a good manner, because all logic is in the "same place" and it's easier to debug. Also you can only have more problems in the deployment if you use the stored procedures. The customer says they want everything in seperate stored procedures because "they always did it that way". What i mean by using seperate stored procedures is:- Creating a stored procedure for each DML operation and for each table (Insert, update or delete)- It should accept a parameter for each column of the table you want to manipulate (delete statement: id only)- The body contains a DML statement that uses the parameters- In code you use the name of the stored procedure instead of the statement, and the parameters remain... (we are using microsoft's enterprise library for data access btw)For select statements they think our approach is best...I know stored procedures are compiled and thus should be faster, but I guess that is not a good argument as it is a for an ASP.NET application and you would not notice any difference in terms of speed anyway. We are not anti-stored-procedures, eg for large operations on a lot of records they probably will be a lot better.Anyone knows what other pro's are related to stored procedures? Or to our way? Please tell me what you think...Thanks

View 1 Replies View Related

When Debugging, Do You Have To Shut Down And Restart BIDS Every Time You Rebuild The Custom Component That You're Debugging?

Jul 9, 2006

Hi,

I have found that when I'm debugging a custom component in BIDS that I've created in another instance of Visual Studio, every time I rebuild the component I have to shutdown and restart BIDS and then reattach to the BIDS process. Which is pretty time consuming... And if I find a small error in my custom component when debugging then I don't seem to be allowed to make any changes to the code unless I stop debugging and go through the process above.

Am I missing something here? Or do I really have to manually go through these steps every time I want to change code in the component I'm debugging?

Can I automate the process with MSBuild or NAnt? If so, is there an example of this anywhere?

Thanks in advance,

Lawrie.

View 1 Replies View Related

Tools For Design Recovery (reengineering) Of C# Source Code With Embedded Sql Statements ?

Sep 16, 2005

Wonder if somebody know if it exist som tools that makes it possible to geta graphical overview of relationships between C# source code components andtheir's interaction with the database tables/elemts (E.g SQL serverdatabase)A typecial rewengineering tool to get a grasp of a system with manycomponents acessing multiple tables in a rdm system.A tool that parses the C# code for sql/(databse interaction) and presentsthe interaction grahical in som way?Many thanksAksel

View 3 Replies View Related

Right Code Statements Of SqlConnection && ConnectionString For Connecting A Database In Database Explorer Of VB 2005 Express?

Feb 14, 2008

Hi all,

In the VB 2005 Express, I can get the SqlConnection and ConnectionString of a Database "shcDB" in the Object Explorer of SQL Server Management Studio Express (SSMSE) by the following set of code:
///--CallshcSpAdoNetVB2005.vb--////

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Public Class Form1

Public Sub InsertNewFriend()

Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=shcDB;Integrated Security=SSPI;"

Dim connection As SqlConnection = New SqlConnection(connectionString)

Try

connection.Open()

Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)

command.CommandType = CommandType.StoredProcedure
.......................................
etc.
///////////////////////////////////////////////////////
If the Database "shcDB" and the Stored Procedure "sp_inertNewRecord" are in the Database Explorer of VB 2005 Express, I plan to use "Data Source=local" in the following code statements to get the SqlConnection and ConnectionString:
.........................
........................

Dim connectionString As String = "Data Source=local;Initial Catalog=shcDB;Integrated Security=SSPI;"

Dim connection As SqlConnection = New SqlConnection(connectionString)

Try

connection.Open()

Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)

command.CommandType = CommandType.StoredProcedure
........................
etc.

Is the "Data Source=local" statement right for this case? If not, what is the right code statement for my case?

Please help and advise.

Thanks,
Scott Chang

View 6 Replies View Related

Help With Converting Code: VB Code In SQL Server 2000-&&>Visual Studio BI 2005

Jul 27, 2006

Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here:
Function Main()
on error resume next
dim cn, i, rs, sSQL
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
set rs = DTSGlobalVariables("SQLstring").value

for i = 1 to rs.RecordCount
sSQL = rs.Fields(0).value
cn.Execute sSQL, , 128 'adExecuteNoRecords option for faster execution
rs.MoveNext
Next

Main = DTSTaskExecResult_Success

End Function

This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)

Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:

public Sub Main()

...

Dts.TaskResult = Dts.Results.Success

End Class

I get the following error when I attempt to compile this:

Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.

I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.

- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).

Given this statement:

dim cn, i, rs, sSQL

I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:

Set cn = CreateObject("ADODB.Connection")

cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"

set rs = CreateObject("ADODB.Recordset")

This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!

View 7 Replies View Related

Sql Server Debugging Tool!!!

Aug 16, 2001

HI Everyone,
I need to ask you a question about T-Sql Debugger. I have installed the sqldbreg.exe and sqldbg.dll in the program filesmicrosoft sharedsql debuggin directory. What I need to know is how to access this tool so that I could debug my procedure I have written in sql server 7.0
Thank you so much!!
sj

View 1 Replies View Related

Debugging SQL SERVER Stored Procedures

Jun 8, 2005

I don't see the option to debug a stored procedure. I right click on
the procedure and should'nt it appear in the properties window. Do I
need to install some extra component to get the debugger.

View 4 Replies View Related

Debugging SQL Server 2000 Using Query Analyzer

Dec 19, 2006

I'm trying to debug some procedural functions in SQL Server 2000 using Query Analyzer, and not having much luck. 
Any help would be appreciated!
 
Thanks,
 
Doug

View 3 Replies View Related

Debugging Database Errors With SQL Server Express

Apr 24, 2006

I am trying to debug a classic ASP application using Visual Web Developer Express and SQL Server Express editions. When my application tries to execute a stored procedure, I get the following error:

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available.

Is there a way for me to trace queries that are getting executed in SQL Server Express so I can see which stored procedure is failing? I seem to remember being able to do this with SQL Server 2000. Any other ideas on how to debug SQL Server errors like these using SQL Server Express?

TIA for the help.

Michael

View 1 Replies View Related

Debugging A Worker Thread Inside SQL Server

May 5, 2006

Hi Everyone!

Just started looking into Assemblies within SQL Server in the hope of creating a multi-threaded application and when writing a basic test assembly I came across the following problem...

When I create a worker thread within my assembly I am unable to do the following in my newly created thread:

Stop on a breakpoint in VS2005
Use Debug.Writeline to output to the output window

I know the thread is running as I am able to write to the EventLog, but VS2005 seems to be unaware of any thread other than the main SQL server thread.

If anyone could tell me whether there it is possible to debug non-SQL server threads, whether I am doing something wrong, or whether I should be doing things differently that would be great!

Thanks in advance for any help!!

The code is as follows (apologies as its not very well written!):

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Threading;

using System.Diagnostics;

public partial class StoredProcedures

{

/// <summary>

/// This stored procedure starts the comms

/// </summary>

[Microsoft.SqlServer.Server.SqlProcedure]

public static void StartComms()

{

try

{

// Output some debug output to show the thread is running

Debug.WriteLine("Comms is starting");

// Create the thread to run in the main thread class

Thread zThread = new Thread(new ThreadStart(ThreadFunction));

// Start the thread running

zThread.Start();

// Sleep to let the other one run

Thread.Sleep(2000);

// Output some debug output to show the thread is running

Debug.WriteLine("Comms is about to wait for thread to finish");

// Wait for the thread to complete

zThread.Join();

// Output some debug output to show the thread is running

Debug.WriteLine("Comms is ending");

}

catch

{



throw;

}



}

/// <summary>

/// The main thread function

/// </summary>

public static void ThreadFunction()

{

try

{

// Flag to keep thread running

bool bRunning = true;

// While the thread is running

while (bRunning == true)

{

// Output some debug output to show the thread is running

Debug.WriteLine("Thread function running");



System.Diagnostics.EventLog.WriteEntry("SQLServerThreadTest",

"Thread function is running");

// Sleep for two seconds

Thread.Sleep(5000);

}

}

catch

{

throw;

}

}

};

View 3 Replies View Related

Steps For Debugging Stored Procedure In Sql Server 2005

Dec 20, 2007

hi friends,
     Anyone give give me the steps we have to follow for debussing a stored procedure in sql server 2005...
 

View 2 Replies View Related

Unable To Start T-SQL Debugging. Could Not Attach To SQL Server Process On

Jul 27, 2006

Please help! I cannot debug CLR procs remotely. Whenever I try to start debugging in VS2005 I get the error: Unable to start T-SQL Debugging. Could not attach to SQL Server process on .... I have clr enabled set to 1 and I have Allow SQLCLR Debugging set on. But I continue to get this error. Any help would be greatly appreciated.

Thanks!

GN

View 21 Replies View Related

Debugging And ActiveX Script Task In A DTS Package Running On SQL Server 2005

Sep 13, 2006

I have a DTS package that I brought over from SQL server 2000 in to SQL Server 2005. I have installed all of the legacy components to run the DTS packages but I need to debug an ActiveX script task. In SQL Server 2000 I could turn on Just-In-Time debugging and use the stop operator (in my vbscript) to break the running script and launch the debugger.

I don't see how to do this in SQL Server 2005 Management Studio. Is it possible to debug a script object in a DTS package running in SQL Server 2005?



Jay Abbott

View 1 Replies View Related

Error Message: Unable To Start T-SQL Debugging. Could Not Attach To SQL Server Process On 'Server1'

Jan 24, 2008


I've installed Visual Studio 2005 Professional evaluation edition on both my desktop & on the server to try out the Debugger to be used with my Stored procedures

I got this error message every time i try to 'Step into Stored Procedure' from my desktop to connect to the stored procedure which resides on the Server.

Error message: Unable to Start T-SQL Debugging. Could not attach to SQL Server Process on 'Server1'.The RPC server is unavailable

The 'Step into Stored Procedure' works though if I open the stored procedure directly on the server.

Both my PC and Server has been installed with Remote Debugger and my windows login has been assigned permission to debug on the debugger that resides on the Server and has sysadmin server role on SQL 2005 both on PC and Server.

Can someone tell me what i miss out to debug Stored procedure in the SQL Server 2005 on the Server from my PC?

Thanks in advance.

View 1 Replies View Related

SQL SERVER Backup As SQL Statements

Sep 5, 2007

Hi,
I want to backup my Sql server as insert and other sql statements. i am not sure how to do it. also i am not able to find any good third party software for the same which can genrate script of complete database instead of table by table (a feature of CodeSmith).

Thanks
Dev

View 6 Replies View Related

Debugging SQL Server 2005 Stored Proc With Visual Studio 2005

Aug 2, 2006

Hello,



we have a SQL server 2005 with Visual studio Prof. 2005 in the
employment.



The debuggers function only in Visual studio correctly, as long as no
code on the SQL server must be implemented.



If a BREAK POINT in a Stored Procedure is set, this is not activated,
since this cannot be bound.

Does someone know, what it lies and can like one it eliminate?



Thank you for your assistance in advance.



Yours sincerely

Big_Ben_31

This entry was translated automatically with the translation
service babel.altavista.com from the German into English.

View 1 Replies View Related

SQL Server 2008 :: Merge 2 Select Statements

Aug 25, 2015

SELECT
part.num, woitem.qtytarget AS woitemqty,

(SELECT LIST(wo.num, ',')

FROM wo INNER JOIN moitem ON wo.moitemid = moitem.id
WHERE moitem.moid = mo.id) AS wonums, mo."USERID" AS mo_USERID

[Code] ...

View 5 Replies View Related

Can We Use FOR Statements In Stored Procedures In SQL Server 2005

Aug 9, 2007

Hai,
I just waana know whether we can use "FOR statement" in Stored Procedures. If yes, can you describe it with some examples of how they are used in stored procedures ?

View 3 Replies View Related

Transact SQL :: Deadlocks On Server From Select Statements?

Feb 13, 2014

I am not sure if i am looking correctly at the deadlocks but i see deadlocks between two select statements.These statements are being run through an application. Below is the table schema from where the select is being performed

CREATE TABLE [dbo].[CMS_LOCKS7](
[PARENTID] [int] NOT NU,  --we have a non clustered index on this column
[CHILDID] [int] NOT NULL, --we have a non clustered index on this column
[ISMEMBER] [int] NOT NULL, -- we have a non clustered index on this column
[ORDINAL] [int] NULL,-- we have a non clustered index on this column

[Code] ....

View 12 Replies View Related

Trace Sql Statements In Sql Server Express Edition

May 30, 2007

Hi,



I have recently started using sql server express edition in place of my oracle db instance and have a need to enable trace on this to view some of the selects that are being fired by the application. I am unable to find out how exactly this is done. I was taken to a couple of sites that said that I needed SSEUtil and have installed that also.



Could one of you gurus out there, please help me out as this is pretty urgent for me to find.



TIA

View 2 Replies View Related

SQL Server 2012 :: Order Of Execution Of Statement With GO Statements?

Jan 16, 2014

Curious if I have the code below as an example and I execute this code does sql execute from top to bottom? And does the Update run and complete before the delete occurs? Or does SQL execute the update and delete in parallel?

UPDATE tbl1 SET col1 = 1

GO

DELETE FROM tbl1 where col2 = 2

View 2 Replies View Related

SQL Server Admin 2014 :: Restriction On DML Statements In SSMS

Apr 27, 2014

I would like to know if there is any option to Restrict DML statements in SSMS for a user where the same user should be able to perform these actions through application on particular database.

View 1 Replies View Related

SQL Server 2008 :: Date Manipulation And CASE Statements

Mar 1, 2015

I have a question on date manipulation functions and CASE statements

My sql is passed the following parameter's and performs a select using a manipulation on these date param's to get a start and end date range depending on the conditions;-

monthColHeader = eg 'Feb 2015'
defaultStartDate and defaultEndDate
filterStartDate and filterEndDate.

These are my conditions;-

if defaultStart and End = filterStart and End use monthColHeader for the date range
if defaultStart and End != filetrStart and End AND the month/year of filterStart and filterEnd match then use the filterStart & End month/Year with the monthColHeader to get the date range
if defaultStart and End != filetrStart and End AND the month/year of filterStart and filterEnd DON't match use filterStart Day and monthColHeader for our start date and monthColHeader for our end date.

When I say use monthColHeader I mean like this;-

(r.dbAddDate >= (CAST('@Request.monthColHeader ~' AS DATETIME)) AND r.dbAddDate < DATEADD(mm,1,'@Request.monthColHeader ~'))

This sql works for converting say 'Feb 2015' to '2015-02-01' & '2015-02-28'....

View 1 Replies View Related

SQL Server 2014 :: Minimizing Locking On Update Statements?

May 4, 2015

I have a main table called Stock for example.

One process ( a service ) inserts data into this table , as well as updates certain fields of the table periodically.

Another process ( SQL Job ) updates the table with certain defaults and rules that are unknown to the service - to deal with some calculations and removal of null values where we can estimate the values etc.

These 2 processes have started to deadlock each other horribly.

The SQL Job calls one stored procedure that has around 10 statements in it. This stored proc runs every minute. Most of them are of the form below - the idea being that once this has corrected the data - the update will not affect these rows again. I guess there are read locks on the selecting part of this query - but usually it updates 0 rows - so I am wondering if there are still locks taken ?

UPDATE s
SET equivQty = Qty * ISNULL(p.Factor,4.5) / 4.5
FROM Stock s
LEFT OUTER JOIN Pack p
on s.Product = p.ProductId
AND s.Pack = p.PackId
WHERE ISNULL(equivQty,0) <> Qty * ISNULL(p.Factor,4.5) / 4.5

The deadlocks are always between these statements from the stored procedure - and the service updating rows. I can't really see how the deadlocks occur but they do.

Another suggestion has been to try and use an exists before the update as below

IF EXISTS( SELECT based on above criteria )
( UPDATE as before )

Does this reduce the locking at all ? I don't know how to test the theory - i added this code to some of the statements, and it didn't seem to make much difference ?

Is there a way to make a process ( in my case the stored procedure ) - give up if it can't aquire the locks rather than being deadlocked - which leads to job failures and emails etc ?

We are currently trying to filter down the data that is updated to be only the last few months - to reduce the amount of rows even analyzed - as the deadlocking does seem to be impacted by the number of rows in the tables.

View 9 Replies View Related

Debugging SQL CLR Hangs With SQL Server 2005 And VS 2005

Apr 2, 2007

Hello,



I am having a problem with SQL Server (Express) hanging during simple debugging of CLR code. I created a test project with a user defined function, and call this from the test script. The output log shows that the sqlservr.exe was auto-attached successfully, and then the everything hangs. I have to terminate debugging in order to regain control, and this takes perhaps a minute or two.



Any ideas?

View 11 Replies View Related

SQL Server 2014 :: COALESCE To Replace Multiple CASE Statements

May 27, 2014

I have a query with huge number of case statements. Basically I need to short this query with getting rid of these hundreds of CASE statements.

Because of the nature of the application I am not allowed to use a function, and just wondering if there is a possible way to rewrite this with COALESCE().

SELECT
CASE WHEN A.[COL_1] LIKE '%cricket%' THEN 'ck' + ',' ELSE '' END +
CASE WHEN A.[COL_1] LIKE '%soccer%' THEN 'sc' + ',' ELSE '' END +
....
CASE WHEN A.[RESIUTIL_DESC] LIKE '%base%ball' THEN 'BB' + ',' ELSE '' END
FROM TableName A

View 9 Replies View Related

SQL Server 2008 :: Track All DML Statements Executed From SSMS Into A Table

Apr 1, 2015

I have a specific requirement. I need to insert the DML statements executed from Management Studio into a SQL table. We have SQL Server 2008 R2 and 2012 instances.

View 8 Replies View Related

SQL Server 2008 :: How To Find Statements That Cause Large Memory Paging

Apr 22, 2015

I am monitoring our production server, and noticed that periodically we have spikes of Memory Paging Rate (pages/sec).

How to find particular queries/stored procedures that causing this?

View 5 Replies View Related

SQL Server 2014 :: How To Roll Back All Changes If Not All Statements Executed Successfully

Oct 15, 2015

I have a script contains multiple statements to update multiple tables. How can I make sure that either all statements get executed successfully or no changes apply to the tables (in case one or more errors occur)? I've been searching on Internet and it seems like I need to use Rollback and begin transaction.

View 5 Replies View Related

SQLExecDirect Using SQL Server ODBC Driver Treats SQL Statements With IF-ELSE As Batch?

Apr 11, 2007

Hi all,

When I run the following T-SQL statement using SQLExecDirect with the SQL Server ODBC driver, it seems to indicate to me that the driver is somehow treating the SQL as a batch rather than a single statement.





Code Snippet
IF (1=1)
UPDATE MyTable
SET [Guid] = 'A663EC5D-A3AF-4391-8C45-9EBE87C9FD6C',
[Id] = 1
[FieldA] = 'Always run'
WHERE ([ID] = 1)
ELSE
INSERT INTO MyTable ([Guid],[Id],[FieldA]) VALUES (NEWID(), 2,'Never run')



Now the above statement is only a simplified statement to illustrate my problem, but I've also tested this stripped down version to ensure that I could replicate the problem with it.

Assuming I have the following table definition and data, I would expect that SQLExecDirect return SQL_ERROR due to the unique key constraint being violated on the [guid] column. However, I'm getting a SQL_SUCCESS_WITH_INFO error.




Code Snippet




CREATE TABLE MyTable
(
[Guid] uniqueidentifier,

[Id] int,

[FieldA] varchar(100)
)




INSERT INTO MyTable ([Guid],[Id],[FieldA]) VALUES (NEWID(), 1,'A')

INSERT INTO MyTable ([Guid],[Id],[FieldA]) VALUES (NEWID(), 1,'B')



According to a MS knowledge base article 195297:

In the version 3.6 SQL Server ODBC driver, SQLExecute, SQLExecDirect, or SQLParamData returns SQL_ERROR only if no other statements are executed after the first statement. If any other statements are executed after the first, even a simple RETURN statement with no return value, SQLExecute or SQLExecDirect returns SQL_SUCCESS_WITH_INFO

This makes me think that somehow the SQL Server driver is treating the T-SQL (with IF-ELSE) as a batch of statements (normally delimited with semi colon e.g. "statement1; statement2;").

I'm running MSDE, with SQL Server driver (SQLSRV32.DLL) v2000.85.1117.00 on Windows XP Professional SP2.

However, running the same SQL but changing the driver to the SQL Native Client (SQLNCLI.DLL) v2005.90.1399.00 returns SQL_ERROR as I expect.

Also the same SQL returns the desired error using both SQL Server and SQL Native Client drivers when connecting to SQL Server 2005.

So in summary:

Driver MSDE SQL Server 2005
{SQL Server} SQL_SUCCESS_WITH_INFO SQL_ERROR
{SQL Native Client} SQL_ERROR SQL_ERROR

Is there any settings that I need to set? Does anybody have any ideas?

Any help greatly appreciated!

View 3 Replies View Related

Check If A Remote Server Is Online/available Before Executing A Series Of Sql Statements

Sep 30, 2007

Hi,

We have unreliable circuits connecting our office to remote sql servers. At our office I need to delete/insert a bunch of records from those remote locations. Since the connection is unreliable what is the best way to check if a server is available before executing delete/insert statements. A few points to consider:

1. I want this to be done in SQL
2. I have a cursor that cycles through a tabe that contains the name of the linked servers. The cursure is used to delete/insert a bunch of records from each remote server.
3. If one server is unavailable the stored proc should continue to the next sql server (next record in the cursor.)


Thanks,
Bradley

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved