Default Field Value?

Is it possible to change the default value of a field using a stored proc or query? Any hints on how to do this if it is possible would be appreciated!
Mike BJust supply the new value on the INSERT?

Or to permanently change it you need to use ALTER TABLE...I think...gotta check...go look up ALTER in Books Online (BOL)

OK?

Default Constraints (call: User-Defined Function or proc)

Hi
Have any way to call User-Defined Function or proc of a Default Constraint'
INT CONSTRAINT test_df DEFAULT dbo.myFunc()
ThaksAccording to the SQL BOL, a user-defined function can be used in a DEFAULT
clause. A stored procedure - not that I am aware of.
HTH
Jerry
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:%23hlZz7LwFHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi
> Have any way to call User-Defined Function or proc of a Default
> Constraint'
> INT CONSTRAINT test_df DEFAULT dbo.myFunc()
> Thaks
>|||Hi
Here is an example of a udf:
CREATE FUNCTION SetDate ()
RETURNS Datetime
AS
BEGIN
DECLARE @.Startdate Datetime
SET @.Startdate = '20050101'
RETURN @.Startdate
END
CREATE TABLE MyTest ( id int not null identity(1,1) Primary key,
name varchar(10) NOT NULL,
StartDate datetime not null default dbo.SetDate(),
EndDate datetime not null default getdate()+1
)
INSERT INTO MyTest ( name ) VALUES ( 'John')
SELECT * FROM MyTest
/*
id name StartDate
EndDate
-- -- ----
-
--1 John 2005-01-01 00:00:00.000
2005-09-25 15:38:48.220
(1 row(s) affected)
*/
John
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:%23hlZz7LwFHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi
> Have any way to call User-Defined Function or proc of a Default
> Constraint'
> INT CONSTRAINT test_df DEFAULT dbo.myFunc()
> Thaks
>

decimal precision of number output

I have a field on my report that is getting a number back from the supporting
stored proc. This number is sometimes a whole number, sometimes a decimal
number.
I want the number output to have 2 decimal places, no matter what the input.
I have tried the conditional formatting on the textbox, but it doesn't seem
to work. Is there a bug? Or am I doing something wrong?
I have input into the "format" section of the properties on the textbox:
#####.##
#,##
d2
D2
2D
2d
d#.##
d#,##
and many variations of the supposed formatting that is supposed to work.
Any suggestions?
Thanks!Hi,
Use an expression like FormatNumber(data,2).
Thanks|||The formatcode property of textboxes uses .NET format codes as defined on
MSDN. For numeric values check these links:
*
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconstandardnumericformatstrings.asp
*
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconcustomnumericformatstrings.asp
In your case, it sounds like you want formatcode N2
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"david boardman" <davidboardman@.discussions.microsoft.com> wrote in message
news:8A5FB147-B81B-40AB-9929-091F141358AD@.microsoft.com...
>I have a field on my report that is getting a number back from the
>supporting
> stored proc. This number is sometimes a whole number, sometimes a decimal
> number.
> I want the number output to have 2 decimal places, no matter what the
> input.
> I have tried the conditional formatting on the textbox, but it doesn't
> seem
> to work. Is there a bug? Or am I doing something wrong?
> I have input into the "format" section of the properties on the textbox:
> #####.##
> #,##
> d2
> D2
> 2D
> 2d
> d#.##
> d#,##
> and many variations of the supposed formatting that is supposed to work.
> Any suggestions?
> Thanks!|||Try N2
"david boardman" wrote:
> I have a field on my report that is getting a number back from the supporting
> stored proc. This number is sometimes a whole number, sometimes a decimal
> number.
> I want the number output to have 2 decimal places, no matter what the input.
> I have tried the conditional formatting on the textbox, but it doesn't seem
> to work. Is there a bug? Or am I doing something wrong?
> I have input into the "format" section of the properties on the textbox:
> #####.##
> #,##
> d2
> D2
> 2D
> 2d
> d#.##
> d#,##
> and many variations of the supposed formatting that is supposed to work.
> Any suggestions?
> Thanks!

Debugging Stored Proc

Hi,
When I go to debug any SP thru Object browser in query analyser ... it
starts well ... takes all the required parameters to begin ... but when I
click on execute button, it completes the SP execution immediately. It does
not allow to use functions like Step into, Step over etc ... Why does this
happen?
Because of this, my main purpose of debugging any SP, does not get satisfied
.
Pls Help."Krishnapra Paralikar" <Krishnapra
Paralikar@.discussions.microsoft.com> wrote in message
news:9DDB8999-67C1-44C8-ADD6-B2979892E612@.microsoft.com...

> When I go to debug any SP thru Object browser in query analyser
http://www.sql-server1.org/detail-7330883.html

Debugging SQL Server 2005 Stored Proc with Visual Studio 2005

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.

I will have to ask the obvious question about your setup. Have you ensured that it is complete? The new topics in BOL and MSDN describe the steps in details including troubleshooting ones. Please start with the links below:

http://msdn2.microsoft.com/en-us/library/kkyhd4yb.aspx

http://msdn2.microsoft.com/en-us/library/s4sszxst.aspx

Debugging SQL Server 2005 Stored Proc with Visual Studio 2005

Hi all,

I have a big ol' stored proc (about 6,000 lines), written in T-SQL. I want to debug this stored proc using something other than a bunch of PRINT statements. I tried using the Visual Studio debugger (Server Explorer - Database - Stored Proc - "Step Into Stored Procedure"), but this is behaving erratically.
The "Current statement" yellow cursor is rarely on the line that is currently being executed. It is usually between 2 and 100 lines above the actual current statement. It seems that the further down the code I get, the bigger the distance (in lines) between the yellow cursor and the actual current statement. Maybe something related to comments or multi-line statements?
I have heard of a similar problem with older versions of Visual C++ related to line feeds and return carriages (CR, LF vs. CRLF) but this doesn't seem to be the problem.
Has anyone had similar problems?

Thank you,

Vince

Might be experiencing something similar. I am used to working in SQL Server. I create my stored procedures in the query editor in SQL Server 2005. Sometimes I want to debug the procedure, so I have to go into VS, open up the object explorer, make sure that the debugging setting it on, find the stored procedure and step into it.

Well... I am seeiing erradic behavior as well; about 75% of the time, the yellow cursor disappears. Like right now... It thinks it is still debugging but the cursor is gone and the only option available for debuuging is to stop debugging. oh, and the task bar says its running... I can't seem to find out why this is happening, since I am seeing no other reports of this happening to people. Your post is the closest so far. Did you resolve your problem?

|||

While I dont have an exact remedy for you, this is not that uncommon. I have used the tools (both VS and SQL) for about two years now since the first public beta whenever that was :)...

I have seen this behavior over the months several times, but not as of late. It usually had to do with referring to old versions of code. Make sure you are referencing the correct Server/DB/Proc and refresh then debug. And check you DB connection properties.

Hope this helps,

Derek

|||

I do think it does have something to do with saving the procedure using Visual Studio 2005. It *seems* that if I save it to a project and try debugging it I have less problems...

This leads me to ask about best practices for managing T-SQL and databases. I'm sure my circumstances are not uncommon; I have a single, rather large database that is utilized by multiple applications (many of which can be in a beta development stage requireing frequent changes to the database). I was to implement source control practices for myself and my teams and I favor subversion for source control. When I create a new application, or upgrade an application to VS 2005, how can I best set up my database for change management?

I have the impression that Microsoft wants me to change the way that I work to handle my database, but it is very unclear HOW they want me to work with their products. Sometimes I write stored procedures that are not part of a single application, but that are used by multiple applications - both web and desktop. So, in this case would it be best to create some kind of database project that is seperate from my applications? And if so, how could I organize it to manage change and quality? Ugh.

|||

Hi Ryan,

I think you will find SQL Server source control integration a good first step towards acommplishing change management in the database. There have been several 3rd party attempts at the problem of managing change in the database, but those I have used were fair at best.

In general, your database can and should be a seperate entity for all but the most simplest projects aka "mom and pop websites/apps". My dev. team uses VSS (not used Team System so you may want to check into that as well) and each project is self-contained in the repository. We do not mix and match projects of different types. So maybe very simplistically speaking your VSS repository structure...

FRONTEND

-Web

-Desktop

MIDDLETIER

-Classes

-Web Services

DATABASE

-Database X

--Stored Procs

--Functions

--Triggers

DML

DDL

-Database Y

-Database Z

etc...you may enjoy a recent post on my blog regarding the usage of mangement studio's VSS integration...

http://derekcomingore.iuplog.com/default.asp?item=167168

Derek

Debugging in QA

Hi,
Could anyone please guide me how can I debug a stored proc/ function in
Query Analyzer. and Is it possible to debug a trigger same way?
Thanks
1.Make sure debugging
are enabled / installed on the sql server.
2.Trigger deb.: Use a stored procedure that fires the trigger, debug the sp,
that will let you step into the trigger.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Zero.NULL" wrote:

> Hi,
> Could anyone please guide me how can I debug a stored proc/ function in
> Query Analyzer. and Is it possible to debug a trigger same way?
> Thanks
>
|||I just want to add what I am facing when trying to debug a procedure
(created on master DB using sa login)
when I start debugging on this procedure in query analyzer, I recieve a
msg box which says:
SP debugging may not work properly if you log on as 'Local System
account'
while SQL Server is configured to run as a service.
You can open Event Viwer to see details.
Do you want to continue?
When I continue with this and execute this procedure by providing
parameter values, I get the print outputs, but procedure execution
doesn't break on break points!!
I am puzzled now how to work around with this? How can I break
execution on break points?
Thanks

Debugging in QA

Hi,
Could anyone please guide me how can I debug a stored proc/ function in
Query Analyzer. and Is it possible to debug a trigger same way?
Thanks1.Make sure debugging
are enabled / installed on the sql server.
2.Trigger deb.: Use a stored procedure that fires the trigger, debug the sp,
that will let you step into the trigger.
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Zero.NULL" wrote:
> Hi,
> Could anyone please guide me how can I debug a stored proc/ function in
> Query Analyzer. and Is it possible to debug a trigger same way?
> Thanks
>|||I just want to add what I am facing when trying to debug a procedure
(created on master DB using sa login)
when I start debugging on this procedure in query analyzer, I recieve a
msg box which says:
SP debugging may not work properly if you log on as 'Local System
account'
while SQL Server is configured to run as a service.
You can open Event Viwer to see details.
Do you want to continue?
When I continue with this and execute this procedure by providing
parameter values, I get the print outputs, but procedure execution
doesn't break on break points!!
I am puzzled now how to work around with this? How can I break
execution on break points?
Thanks

Debugging in QA

Hi,
Could anyone please guide me how can I debug a stored proc/ function in
Query Analyzer. and Is it possible to debug a trigger same way?
Thanks1.Make sure debugging
are enabled / installed on the sql server.
2.Trigger deb.: Use a stored procedure that fires the trigger, debug the sp,
that will let you step into the trigger.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Zero.NULL" wrote:

> Hi,
> Could anyone please guide me how can I debug a stored proc/ function in
> Query Analyzer. and Is it possible to debug a trigger same way?
> Thanks
>|||I just want to add what I am facing when trying to debug a procedure
(created on master DB using sa login)
when I start debugging on this procedure in query analyzer, I recieve a
msg box which says:
SP debugging may not work properly if you log on as 'Local System
account'
while SQL Server is configured to run as a service.
You can open Event Viwer to see details.
Do you want to continue?
When I continue with this and execute this procedure by providing
parameter values, I get the print outputs, but procedure execution
doesn't break on break points!!
I am puzzled now how to work around with this? How can I break
execution on break points?
Thanks

Debug Stored Proc's in MSSQL 2005

In SQL Server 2000's Query Analyzer, you can debug a stored procedure by right-clicking a SP and select "Debug".

You can then step through the SP one line at a time.

I don't see this in SQL Server 2005 and have searched but cannot find any documentation on what happened to this feature.
What did Microsoft do with this?

You can not debug Sp from SQL Server enviornment in 2005.... use Visual Studio

Ref :http://msdn2.microsoft.com/en-us/library/zxsa8hkf(VS.80).aspx

Madhu

|||

Thanks, I had seen that one before but didn't want to write it as managed code, so I stop short looking further. I did however, follow a few more links at the bottom and it brought me to the article:

How To: Step into an Object Using Server Explorer
http://msdn2.microsoft.com/en-us/library/ms165041(VS.80).aspx

Thanks!

Debug Proc

Dear floks,
I use "Embarcadero Rapid SQL 7.2.1" to debug proc,but report error:
Program Error establishing a debugger session with the server. See debug
log for more information.
Does anyone know how could we do such thing?OS:Windows XP+MS Sql2000

debug (step into) sql stored proc from managed code

I am trying to debug sql2000 sp from managed code app with VS.Net 2003 archetect Ed..
It did not stop at the break point within the sql sp.
I did granted execute permission for sp_sdidebug.
Do I need to attach any process?

Is there anything left off by the article?
I referenced msdn article option 2: http://support.microsoft.com/default.aspx?kbid=316549

Thanks.Please use the word "bump" or something such as, "Any help?" so that the moderators recognise the purpose of your single-character message. Otherwise, the message may be deemed meaningless, and will be deleted.|||Sorry, this is really a Visual Studio.NET question. I have no idea.

Terri