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
>
Showing posts with label call. Show all posts
Showing posts with label call. Show all posts
Thursday, March 22, 2012
Wednesday, March 21, 2012
Dedicated Database Connection
We are looking at ways to optimize our process for retrieving primary keys.
Currently, we are creating a new database call to get each ID we need. We
are considering the idea of keeping a database connection open with a single
responsibility of retrieving a new primary key. Can you provide me a
response to the following questions?:
Are there any timeouts on open connections to the database from within SQL
Server?
Can you think of negative impacts this could have on the database to have a
consistent open connections?newie wrote:
> We are looking at ways to optimize our process for retrieving primary
> keys. Currently, we are creating a new database call to get each ID
> we need. We are considering the idea of keeping a database
> connection open with a single responsibility of retrieving a new
> primary key. Can you provide me a response to the following
> questions?:
> Are there any timeouts on open connections to the database from
> within SQL Server?
> Can you think of negative impacts this could have on the database to
> have a consistent open connections?
Conistent open connections are the standard for many client-server
applications. Even something like IIS would likely use connection
pooling and keep a certain number of connections open. There is no
problem with an open connection. In fact, in most cases it can improve
application performance.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||take a look at connection pooling
http://sqlservercode.blogspot.com/
"newie" wrote:
> We are looking at ways to optimize our process for retrieving primary keys.
> Currently, we are creating a new database call to get each ID we need. We
> are considering the idea of keeping a database connection open with a single
> responsibility of retrieving a new primary key. Can you provide me a
> response to the following questions?:
> Are there any timeouts on open connections to the database from within SQL
> Server?
> Can you think of negative impacts this could have on the database to have a
> consistent open connections?
>sql
Currently, we are creating a new database call to get each ID we need. We
are considering the idea of keeping a database connection open with a single
responsibility of retrieving a new primary key. Can you provide me a
response to the following questions?:
Are there any timeouts on open connections to the database from within SQL
Server?
Can you think of negative impacts this could have on the database to have a
consistent open connections?newie wrote:
> We are looking at ways to optimize our process for retrieving primary
> keys. Currently, we are creating a new database call to get each ID
> we need. We are considering the idea of keeping a database
> connection open with a single responsibility of retrieving a new
> primary key. Can you provide me a response to the following
> questions?:
> Are there any timeouts on open connections to the database from
> within SQL Server?
> Can you think of negative impacts this could have on the database to
> have a consistent open connections?
Conistent open connections are the standard for many client-server
applications. Even something like IIS would likely use connection
pooling and keep a certain number of connections open. There is no
problem with an open connection. In fact, in most cases it can improve
application performance.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com|||take a look at connection pooling
http://sqlservercode.blogspot.com/
"newie" wrote:
> We are looking at ways to optimize our process for retrieving primary keys.
> Currently, we are creating a new database call to get each ID we need. We
> are considering the idea of keeping a database connection open with a single
> responsibility of retrieving a new primary key. Can you provide me a
> response to the following questions?:
> Are there any timeouts on open connections to the database from within SQL
> Server?
> Can you think of negative impacts this could have on the database to have a
> consistent open connections?
>sql
Dedicated Database Connection
We are looking at ways to optimize our process for retrieving primary keys.
Currently, we are creating a new database call to get each ID we need. We
are considering the idea of keeping a database connection open with a single
responsibility of retrieving a new primary key. Can you provide me a
response to the following questions?:
Are there any timeouts on open connections to the database from within SQL
Server?
Can you think of negative impacts this could have on the database to have a
consistent open connections?
newie wrote:
> We are looking at ways to optimize our process for retrieving primary
> keys. Currently, we are creating a new database call to get each ID
> we need. We are considering the idea of keeping a database
> connection open with a single responsibility of retrieving a new
> primary key. Can you provide me a response to the following
> questions?:
> Are there any timeouts on open connections to the database from
> within SQL Server?
> Can you think of negative impacts this could have on the database to
> have a consistent open connections?
Conistent open connections are the standard for many client-server
applications. Even something like IIS would likely use connection
pooling and keep a certain number of connections open. There is no
problem with an open connection. In fact, in most cases it can improve
application performance.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||take a look at connection pooling
http://sqlservercode.blogspot.com/
"newie" wrote:
> We are looking at ways to optimize our process for retrieving primary keys.
> Currently, we are creating a new database call to get each ID we need. We
> are considering the idea of keeping a database connection open with a single
> responsibility of retrieving a new primary key. Can you provide me a
> response to the following questions?:
> Are there any timeouts on open connections to the database from within SQL
> Server?
> Can you think of negative impacts this could have on the database to have a
> consistent open connections?
>
Currently, we are creating a new database call to get each ID we need. We
are considering the idea of keeping a database connection open with a single
responsibility of retrieving a new primary key. Can you provide me a
response to the following questions?:
Are there any timeouts on open connections to the database from within SQL
Server?
Can you think of negative impacts this could have on the database to have a
consistent open connections?
newie wrote:
> We are looking at ways to optimize our process for retrieving primary
> keys. Currently, we are creating a new database call to get each ID
> we need. We are considering the idea of keeping a database
> connection open with a single responsibility of retrieving a new
> primary key. Can you provide me a response to the following
> questions?:
> Are there any timeouts on open connections to the database from
> within SQL Server?
> Can you think of negative impacts this could have on the database to
> have a consistent open connections?
Conistent open connections are the standard for many client-server
applications. Even something like IIS would likely use connection
pooling and keep a certain number of connections open. There is no
problem with an open connection. In fact, in most cases it can improve
application performance.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||take a look at connection pooling
http://sqlservercode.blogspot.com/
"newie" wrote:
> We are looking at ways to optimize our process for retrieving primary keys.
> Currently, we are creating a new database call to get each ID we need. We
> are considering the idea of keeping a database connection open with a single
> responsibility of retrieving a new primary key. Can you provide me a
> response to the following questions?:
> Are there any timeouts on open connections to the database from within SQL
> Server?
> Can you think of negative impacts this could have on the database to have a
> consistent open connections?
>
Dedicated Database Connection
We are looking at ways to optimize our process for retrieving primary keys.
Currently, we are creating a new database call to get each ID we need. We
are considering the idea of keeping a database connection open with a single
responsibility of retrieving a new primary key. Can you provide me a
response to the following questions?:
Are there any timeouts on open connections to the database from within SQL
Server?
Can you think of negative impacts this could have on the database to have a
consistent open connections?newie wrote:
> We are looking at ways to optimize our process for retrieving primary
> keys. Currently, we are creating a new database call to get each ID
> we need. We are considering the idea of keeping a database
> connection open with a single responsibility of retrieving a new
> primary key. Can you provide me a response to the following
> questions?:
> Are there any timeouts on open connections to the database from
> within SQL Server?
> Can you think of negative impacts this could have on the database to
> have a consistent open connections?
Conistent open connections are the standard for many client-server
applications. Even something like IIS would likely use connection
pooling and keep a certain number of connections open. There is no
problem with an open connection. In fact, in most cases it can improve
application performance.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||take a look at connection pooling
http://sqlservercode.blogspot.com/
"newie" wrote:
> We are looking at ways to optimize our process for retrieving primary keys
.
> Currently, we are creating a new database call to get each ID we need. We
> are considering the idea of keeping a database connection open with a sing
le
> responsibility of retrieving a new primary key. Can you provide me a
> response to the following questions?:
> Are there any timeouts on open connections to the database from within SQL
> Server?
> Can you think of negative impacts this could have on the database to have
a
> consistent open connections?
>
Currently, we are creating a new database call to get each ID we need. We
are considering the idea of keeping a database connection open with a single
responsibility of retrieving a new primary key. Can you provide me a
response to the following questions?:
Are there any timeouts on open connections to the database from within SQL
Server?
Can you think of negative impacts this could have on the database to have a
consistent open connections?newie wrote:
> We are looking at ways to optimize our process for retrieving primary
> keys. Currently, we are creating a new database call to get each ID
> we need. We are considering the idea of keeping a database
> connection open with a single responsibility of retrieving a new
> primary key. Can you provide me a response to the following
> questions?:
> Are there any timeouts on open connections to the database from
> within SQL Server?
> Can you think of negative impacts this could have on the database to
> have a consistent open connections?
Conistent open connections are the standard for many client-server
applications. Even something like IIS would likely use connection
pooling and keep a certain number of connections open. There is no
problem with an open connection. In fact, in most cases it can improve
application performance.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||take a look at connection pooling
http://sqlservercode.blogspot.com/
"newie" wrote:
> We are looking at ways to optimize our process for retrieving primary keys
.
> Currently, we are creating a new database call to get each ID we need. We
> are considering the idea of keeping a database connection open with a sing
le
> responsibility of retrieving a new primary key. Can you provide me a
> response to the following questions?:
> Are there any timeouts on open connections to the database from within SQL
> Server?
> Can you think of negative impacts this could have on the database to have
a
> consistent open connections?
>
Wednesday, March 7, 2012
Decimal Formatting from OLEDB Source to Flat File
I have a table with column (call it Rate) defined as decimal(18,4). This column can have any positive decimal value. When the value is less than 1 (e.g. 0.1234) strange things are happening in SSIS.
To set the scenario ...
If I do a select in Management Studio, the value is correctly displayed as 0.1234
If I do the same select from the command line via OSQL, I get .1234
If I do SELECT CONVERT(char(6), Rate) from OSQL, I get 0.1234 (which is what I want)
In SSIS I have an OLE DB Source which defines the SELECT statement to use, and that maps straight to a Flat File Destination.
The default column type in the flat file connection manager is DT_NUMERIC, Precision 18, Scale 4. I Set InputColumnWidth to 6 as that's how I need it in the flat file. The value appears in the file as .1234
I change the SELECT in the OLEDB Source to apply the CONVERT(char(6), Rate), and change the column type in the connection manager to DT_STR length 6. Output is *still* .1234
How can I get the output column in my file to show 0.1234 ?
Greg.
Restarting this thread to check for any new information.
Greg.
|||have u tried Currency datatype|||Yes. It made no difference.
Greg.
Decimal Formatting from OLEDB Source to Flat File
I have a
table with column (call it Rate) defined as decimal(18,4). This column
can have any positive decimal value. When the value is less than 1
(e.g. 0.1234) strange things are happening in SSIS.
To set the scenario ...
If I do a select in Management Studio, the value is correctly displayed as 0.1234
If I do the same select from the command line via OSQL, I get .1234
If I do SELECT CONVERT(char(6), Rate) from OSQL, I get 0.1234 (which is what I want)
In SSIS I have an OLE DB Source which defines the SELECT statement to use, and that maps straight to a Flat File Destination.
The
default column type in the flat file connection manager is DT_NUMERIC,
Precision 18, Scale 4. I Set InputColumnWidth to 6 as that's how I need
it in the flat file. The value appears in the file as .1234
I
change the SELECT in the OLEDB Source to apply the CONVERT(char(6),
Rate), and change the column type in the connection manager to DT_STR
length 6. Output is *still* .1234
How can I get the output column in my file to show 0.1234 ?
Greg.
Restarting this thread to check for any new information.
Greg.|||have u tried Currency datatype|||Yes. It made no difference.
Greg.
Saturday, February 25, 2012
Debugging?
Hi,
Is there a way to debug an SQL Server stored procedure? I call a number of sp's from my .net app and need a way to step through them.
Thank you,http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsdebug/html/vxtskasqldebuggingexample.asp
|||Thanks. Your link also pointed to the following link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsdebug/html/vchowapplicationdebugging.asp
that shows, supposedly, how to debug a stored procedure inline with your app. This is what I'm looking for, because I need to be able to see what the stored procedure is receiving in it's parameters. I followed the steps in the link above, put a breakpoing in the stored procedure and another one in my app just before the stored procedure, and, contrary to the claims of the article, I was not able to step into the procedure. Any help getting this to work would be appreciated!
Thanks.|||Is the SQL Server on your local machine? If not you have toinstall the remote debugging components. This article tells youhow to install and configure remote debugging (as well as various othercool debugging tips):
http://www.dbazine.com/sql/sql-articles/cook1
|||It is on my local machine.
Is there a way to debug an SQL Server stored procedure? I call a number of sp's from my .net app and need a way to step through them.
Thank you,http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsdebug/html/vxtskasqldebuggingexample.asp
|||Thanks. Your link also pointed to the following link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsdebug/html/vchowapplicationdebugging.asp
that shows, supposedly, how to debug a stored procedure inline with your app. This is what I'm looking for, because I need to be able to see what the stored procedure is receiving in it's parameters. I followed the steps in the link above, put a breakpoing in the stored procedure and another one in my app just before the stored procedure, and, contrary to the claims of the article, I was not able to step into the procedure. Any help getting this to work would be appreciated!
Thanks.|||Is the SQL Server on your local machine? If not you have toinstall the remote debugging components. This article tells youhow to install and configure remote debugging (as well as various othercool debugging tips):
http://www.dbazine.com/sql/sql-articles/cook1
|||It is on my local machine.
Sunday, February 19, 2012
Debugging a .net application makes an sql server hang
Hello everyone,
I have been debugging a module I have coded using the VS.NET debugger. This
makes a call to a SQL 2000 database, and closes the connection, I then start
debugging after this point.
Someone has started to use a web application on a live sever which connects
to a SQL 2000 database, this database is a seperate server on a completly
different machine to the developing computer and DB.
However when I start debugging my application this live database hangs until
I stop debugging, does anyone have any idea why?
Many Thanks
PhilHi Phil
I tried following your post but it's a bit ambiguous, so I'm not 100% sure
what's exactly happening over there.. Anyhow, have you tried running the
"exec sp_who2" TSQL command to see if any connections are blocked whilst the
server appears hung? There are many scenarios where debugging can hang
applications, such as the application not draining cursors - perhaps your
application is doing that? Running sp_who2 will tell you if any connections
(spids) are blocked within the database engine. If youu find any blocked
connections, the next step is to analyse why the blocking connection is
hanging onto locks & fix that problem from there..
Regards,
Greg Linwood
SQL Server MVP
"Philip Poole" <philip@.dontreply.org> wrote in message
news:412628fb$0$193$db0fefd9@.news.zen.co.uk...
> Hello everyone,
> I have been debugging a module I have coded using the VS.NET debugger.
> This
> makes a call to a SQL 2000 database, and closes the connection, I then
> start
> debugging after this point.
> Someone has started to use a web application on a live sever which
> connects
> to a SQL 2000 database, this database is a seperate server on a completly
> different machine to the developing computer and DB.
> However when I start debugging my application this live database hangs
> until
> I stop debugging, does anyone have any idea why?
> Many Thanks
> Phil
>
>
I have been debugging a module I have coded using the VS.NET debugger. This
makes a call to a SQL 2000 database, and closes the connection, I then start
debugging after this point.
Someone has started to use a web application on a live sever which connects
to a SQL 2000 database, this database is a seperate server on a completly
different machine to the developing computer and DB.
However when I start debugging my application this live database hangs until
I stop debugging, does anyone have any idea why?
Many Thanks
PhilHi Phil
I tried following your post but it's a bit ambiguous, so I'm not 100% sure
what's exactly happening over there.. Anyhow, have you tried running the
"exec sp_who2" TSQL command to see if any connections are blocked whilst the
server appears hung? There are many scenarios where debugging can hang
applications, such as the application not draining cursors - perhaps your
application is doing that? Running sp_who2 will tell you if any connections
(spids) are blocked within the database engine. If youu find any blocked
connections, the next step is to analyse why the blocking connection is
hanging onto locks & fix that problem from there..
Regards,
Greg Linwood
SQL Server MVP
"Philip Poole" <philip@.dontreply.org> wrote in message
news:412628fb$0$193$db0fefd9@.news.zen.co.uk...
> Hello everyone,
> I have been debugging a module I have coded using the VS.NET debugger.
> This
> makes a call to a SQL 2000 database, and closes the connection, I then
> start
> debugging after this point.
> Someone has started to use a web application on a live sever which
> connects
> to a SQL 2000 database, this database is a seperate server on a completly
> different machine to the developing computer and DB.
> However when I start debugging my application this live database hangs
> until
> I stop debugging, does anyone have any idea why?
> Many Thanks
> Phil
>
>
Debugging a .net application makes an sql server hang
Hello everyone,
I have been debugging a module I have coded using the VS.NET debugger. This
makes a call to a SQL 2000 database, and closes the connection, I then start
debugging after this point.
Someone has started to use a web application on a live sever which connects
to a SQL 2000 database, this database is a seperate server on a completly
different machine to the developing computer and DB.
However when I start debugging my application this live database hangs until
I stop debugging, does anyone have any idea why?
Many Thanks
PhilHi Phil
I tried following your post but it's a bit ambiguous, so I'm not 100% sure
what's exactly happening over there.. Anyhow, have you tried running the
"exec sp_who2" TSQL command to see if any connections are blocked whilst the
server appears hung? There are many scenarios where debugging can hang
applications, such as the application not draining cursors - perhaps your
application is doing that? Running sp_who2 will tell you if any connections
(spids) are blocked within the database engine. If youu find any blocked
connections, the next step is to analyse why the blocking connection is
hanging onto locks & fix that problem from there..
Regards,
Greg Linwood
SQL Server MVP
"Philip Poole" <philip@.dontreply.org> wrote in message
news:412628fb$0$193$db0fefd9@.news.zen.co.uk...
> Hello everyone,
> I have been debugging a module I have coded using the VS.NET debugger.
> This
> makes a call to a SQL 2000 database, and closes the connection, I then
> start
> debugging after this point.
> Someone has started to use a web application on a live sever which
> connects
> to a SQL 2000 database, this database is a seperate server on a completly
> different machine to the developing computer and DB.
> However when I start debugging my application this live database hangs
> until
> I stop debugging, does anyone have any idea why?
> Many Thanks
> Phil
>
>
I have been debugging a module I have coded using the VS.NET debugger. This
makes a call to a SQL 2000 database, and closes the connection, I then start
debugging after this point.
Someone has started to use a web application on a live sever which connects
to a SQL 2000 database, this database is a seperate server on a completly
different machine to the developing computer and DB.
However when I start debugging my application this live database hangs until
I stop debugging, does anyone have any idea why?
Many Thanks
PhilHi Phil
I tried following your post but it's a bit ambiguous, so I'm not 100% sure
what's exactly happening over there.. Anyhow, have you tried running the
"exec sp_who2" TSQL command to see if any connections are blocked whilst the
server appears hung? There are many scenarios where debugging can hang
applications, such as the application not draining cursors - perhaps your
application is doing that? Running sp_who2 will tell you if any connections
(spids) are blocked within the database engine. If youu find any blocked
connections, the next step is to analyse why the blocking connection is
hanging onto locks & fix that problem from there..
Regards,
Greg Linwood
SQL Server MVP
"Philip Poole" <philip@.dontreply.org> wrote in message
news:412628fb$0$193$db0fefd9@.news.zen.co.uk...
> Hello everyone,
> I have been debugging a module I have coded using the VS.NET debugger.
> This
> makes a call to a SQL 2000 database, and closes the connection, I then
> start
> debugging after this point.
> Someone has started to use a web application on a live sever which
> connects
> to a SQL 2000 database, this database is a seperate server on a completly
> different machine to the developing computer and DB.
> However when I start debugging my application this live database hangs
> until
> I stop debugging, does anyone have any idea why?
> Many Thanks
> Phil
>
>
Debugging a .net application makes an sql server hang
Hello everyone,
I have been debugging a module I have coded using the VS.NET debugger. This
makes a call to a SQL 2000 database, and closes the connection, I then start
debugging after this point.
Someone has started to use a web application on a live sever which connects
to a SQL 2000 database, this database is a seperate server on a completly
different machine to the developing computer and DB.
However when I start debugging my application this live database hangs until
I stop debugging, does anyone have any idea why?
Many Thanks
Phil
Hi Phil
I tried following your post but it's a bit ambiguous, so I'm not 100% sure
what's exactly happening over there.. Anyhow, have you tried running the
"exec sp_who2" TSQL command to see if any connections are blocked whilst the
server appears hung? There are many scenarios where debugging can hang
applications, such as the application not draining cursors - perhaps your
application is doing that? Running sp_who2 will tell you if any connections
(spids) are blocked within the database engine. If youu find any blocked
connections, the next step is to analyse why the blocking connection is
hanging onto locks & fix that problem from there..
Regards,
Greg Linwood
SQL Server MVP
"Philip Poole" <philip@.dontreply.org> wrote in message
news:412628fb$0$193$db0fefd9@.news.zen.co.uk...
> Hello everyone,
> I have been debugging a module I have coded using the VS.NET debugger.
> This
> makes a call to a SQL 2000 database, and closes the connection, I then
> start
> debugging after this point.
> Someone has started to use a web application on a live sever which
> connects
> to a SQL 2000 database, this database is a seperate server on a completly
> different machine to the developing computer and DB.
> However when I start debugging my application this live database hangs
> until
> I stop debugging, does anyone have any idea why?
> Many Thanks
> Phil
>
>
I have been debugging a module I have coded using the VS.NET debugger. This
makes a call to a SQL 2000 database, and closes the connection, I then start
debugging after this point.
Someone has started to use a web application on a live sever which connects
to a SQL 2000 database, this database is a seperate server on a completly
different machine to the developing computer and DB.
However when I start debugging my application this live database hangs until
I stop debugging, does anyone have any idea why?
Many Thanks
Phil
Hi Phil
I tried following your post but it's a bit ambiguous, so I'm not 100% sure
what's exactly happening over there.. Anyhow, have you tried running the
"exec sp_who2" TSQL command to see if any connections are blocked whilst the
server appears hung? There are many scenarios where debugging can hang
applications, such as the application not draining cursors - perhaps your
application is doing that? Running sp_who2 will tell you if any connections
(spids) are blocked within the database engine. If youu find any blocked
connections, the next step is to analyse why the blocking connection is
hanging onto locks & fix that problem from there..
Regards,
Greg Linwood
SQL Server MVP
"Philip Poole" <philip@.dontreply.org> wrote in message
news:412628fb$0$193$db0fefd9@.news.zen.co.uk...
> Hello everyone,
> I have been debugging a module I have coded using the VS.NET debugger.
> This
> makes a call to a SQL 2000 database, and closes the connection, I then
> start
> debugging after this point.
> Someone has started to use a web application on a live sever which
> connects
> to a SQL 2000 database, this database is a seperate server on a completly
> different machine to the developing computer and DB.
> However when I start debugging my application this live database hangs
> until
> I stop debugging, does anyone have any idea why?
> Many Thanks
> Phil
>
>
Subscribe to:
Posts (Atom)