Excel Import

Thursday, March 29, 2012

execption with store procedure

HI!

how do i make the store procudere only exec when new data inserted in the other table? recently i hav use sp_procoption to make an autoexecution.... but it will insert the new data together with the old data
any suggestion?

CREATE PROCEDURE [P1] AS

INSERT INTO TABLE1

SELECT sa.NoMatriks, sa.SesiSem,
SUM(Nilai * KreditSubjek) / SUM(KreditSubjek) AS CPA,
SUM(KreditSubjek) AS JumKredit,
p.Agama, p.Aliran, p.Negeri, p.Kaum
FROM TblSubjekAmbil sa, TblSubjek s, TblGred g, TblPelajar p
WHERE sa.NoMatriks = p.NoMatriks AND
sa.KodSubjek = s.KodSubjek AND
sa.Gred = g.Gred
GROUP BY sa.NoMatriks, sa.SesiSem,
p.Agama, p.Aliran, p.Negeri,
p.Kaum (SELECT sa.NoMatriks, t1.NoMatriks
FROM TblSubjAmbil sa, TABLE1 t1
WHERE t1.NoMatriks <> sa.NoMatriks)

exec sp_makestartup N'P1'

exec sp_makestartup N'P1'

exec sp_makestartup N'P1'Databases have a great concept, called trigger. Just define an INSERT trigger for your table, and let that trigger execute your code or procedure.sql

ExecContextHit through SQL Profiler

Hello,
Hopefully someone can answer this question. I am running a procedure which uses Dynamic SQL ie: Scroll Cursor, User Functions, and sp_executesql to update a table. When I run this sp in a query window it runs much faster then when it is called from a Jo
b. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a sligh
t delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?
Thanks !!
ExecContextHit means that an execution context version (has
session specific info) was found in cache.
Monitor SP:Recompile for recompiles. If you are experiencing
recompiles, you can reference the following:
INF: Troubleshooting Stored Procedure Recompilation
http://support.microsoft.com/?id=243586
You should also make sure the job has set nocount on in the
beginning of the stored procedure and T-SQL batches.
Depending on your version of SQL Server, you can also hit
issues with delays in Agent jobs if you aren't on the latest
service pack. I think it was SQL 7, SP4 that addressed
issues related to this.
-Sue
On Mon, 28 Jun 2004 13:43:01 -0700, "nupee"
<nupee@.discussions.microsoft.com> wrote:

>Hello,
>Hopefully someone can answer this question. I am running a procedure which uses Dynamic SQL ie: Scroll Cursor, User Functions, and sp_executesql to update a table. When I run this sp in a query window it runs much faster then when it is called from a J
ob. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a slig
ht delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?
>Thanks !!
|||ExecContextHit means that an execution context version (has
session specific info) was found in cache.
Monitor SP:Recompile for recompiles. If you are experiencing
recompiles, you can reference the following:
INF: Troubleshooting Stored Procedure Recompilation
http://support.microsoft.com/?id=243586
You should also make sure the job has set nocount on in the
beginning of the stored procedure and T-SQL batches.
Depending on your version of SQL Server, you can also hit
issues with delays in Agent jobs if you aren't on the latest
service pack. I think it was SQL 7, SP4 that addressed
issues related to this.
-Sue
On Mon, 28 Jun 2004 13:43:01 -0700, "nupee"
<nupee@.discussions.microsoft.com> wrote:

>Hello,
>Hopefully someone can answer this question. I am running a procedure which uses Dynamic SQL ie: Scroll Cursor, User Functions, and sp_executesql to update a table. When I run this sp in a query window it runs much faster then when it is called from a J
ob. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a slig
ht delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?
>Thanks !!
|||The set nocount on worked. Thank You !
"Sue Hoegemeier" wrote:
[vbcol=seagreen]
> ExecContextHit means that an execution context version (has
> session specific info) was found in cache.
> Monitor SP:Recompile for recompiles. If you are experiencing
> recompiles, you can reference the following:
> INF: Troubleshooting Stored Procedure Recompilation
> http://support.microsoft.com/?id=243586
> You should also make sure the job has set nocount on in the
> beginning of the stored procedure and T-SQL batches.
> Depending on your version of SQL Server, you can also hit
> issues with delays in Agent jobs if you aren't on the latest
> service pack. I think it was SQL 7, SP4 that addressed
> issues related to this.
> -Sue
> On Mon, 28 Jun 2004 13:43:01 -0700, "nupee"
> <nupee@.discussions.microsoft.com> wrote:
Job. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a sl
ight delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?
>
|||Your welcome - thanks for posting back that it fixed the
issue.
-Sue
On Tue, 29 Jun 2004 12:48:01 -0700, "nupee"
<nupee@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>The set nocount on worked. Thank You !
>"Sue Hoegemeier" wrote:
a Job. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a s
light delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?[vbcol=seagreen]
|||The set nocount on worked. Thank You !
"Sue Hoegemeier" wrote:
[vbcol=seagreen]
> ExecContextHit means that an execution context version (has
> session specific info) was found in cache.
> Monitor SP:Recompile for recompiles. If you are experiencing
> recompiles, you can reference the following:
> INF: Troubleshooting Stored Procedure Recompilation
> http://support.microsoft.com/?id=243586
> You should also make sure the job has set nocount on in the
> beginning of the stored procedure and T-SQL batches.
> Depending on your version of SQL Server, you can also hit
> issues with delays in Agent jobs if you aren't on the latest
> service pack. I think it was SQL 7, SP4 that addressed
> issues related to this.
> -Sue
> On Mon, 28 Jun 2004 13:43:01 -0700, "nupee"
> <nupee@.discussions.microsoft.com> wrote:
Job. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a sl
ight delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?
>
|||Your welcome - thanks for posting back that it fixed the
issue.
-Sue
On Tue, 29 Jun 2004 12:48:01 -0700, "nupee"
<nupee@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>The set nocount on worked. Thank You !
>"Sue Hoegemeier" wrote:
a Job. I ran Profiler traces with both methods and noticed that there were SP:ExecContectHit entries in the Traces. Does this mean that at the point that I see these statements that there is an sp_recompile occurring within the sp? There is definitely a s
light delay in the job sp after each of the SP:ContectHit statements. If it is recompiling is there anyway to prohibit that?[vbcol=seagreen]

Exec/Query across servers (unlinked)

Can I exec/query across unlinked servers that are on the network but
physically different server instances? If so, how, please?
TIA!
RobertTake a look at OPENROWSET and OPENDATASOURCE in Books On line
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||You can use OPENROWSET
SELECT *
FROM OPENROWSET('SQLOLEDB','otherinstance';'u
ser';'pwd',
'SELECT * FROM somedb.dbo.mytable')|||My bad...
I wasn't using fully qualified naming. I was using something like
ABC123.dbo.TableName instead of DEV.ABC123.dbo.TableName.
Thanks!
Robert
"Robert Davis" <radbase@.yahoo.com> wrote in message
news:OKtDvGkXGHA.3660@.TK2MSFTNGP04.phx.gbl...
> Can I exec/query across unlinked servers that are on the network but
> physically different server instances? If so, how, please?
> TIA!
> Robert
>

EXEC('string') from a UDF?

Can I not use an EXEC command from with in a UDF? I am looking through Book
s
online, but can't find anything that says I can't.
In my UDF I have
Exec('Select fields fromTABLE Where this=that')
I use this in stored procs alot when the table name is a variable.
When trying to do it from a UDF, I get:
"Invalid use of 'EXECUTE' within a function"
Thanks,
SteveHi
UDF's can contain certain functions like EXECUTE. Basic rule of the UDF.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
news:915E2719-E431-454E-89B3-A8B4802EC390@.microsoft.com...
> Can I not use an EXEC command from with in a UDF? I am looking through
> Books
> online, but can't find anything that says I can't.
> In my UDF I have
> Exec('Select fields fromTABLE Where this=that')
> I use this in stored procs alot when the table name is a variable.
> When trying to do it from a UDF, I get:
> "Invalid use of 'EXECUTE' within a function"
> Thanks,
> Steve|||You cant execute q sqlstring in a UDF, try to code a stored prcoedure for
that.
HTH, Jens Suessmeyer.
"SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
news:915E2719-E431-454E-89B3-A8B4802EC390@.microsoft.com...
> Can I not use an EXEC command from with in a UDF? I am looking through
> Books
> online, but can't find anything that says I can't.
> In my UDF I have
> Exec('Select fields fromTABLE Where this=that')
> I use this in stored procs alot when the table name is a variable.
> When trying to do it from a UDF, I get:
> "Invalid use of 'EXECUTE' within a function"
> Thanks,
> Steve|||> Exec('Select fields fromTABLE Where this=that')
> I use this in stored procs alot when the table name is a variable.
[shudder] Parameterizing table names is a very bad idea and with good design
it shouldn't be necessary. Why would you want to do this in a function
anyway? (BTW, you can't)
David Portas
SQL Server MVP
--|||Thanks for all the responses.
I use a UDF so I can use it to return a table variable to base a MS ACCESS
form off of. The table name is passed in cause it is a ##table, and will be
different for different users. The UDF gathers info from different tables,
including the ##table, then puts it all in a table variable to return to the
form.
Steve
"David Portas" wrote:

> [shudder] Parameterizing table names is a very bad idea and with good design
> it shouldn't be necessary. Why would you want to do this in a function
> anyway? (BTW, you can't)
> --
> David Portas
> SQL Server MVP
> --
>
>|||Local temp tables are scoped to a session anyway so there's no need to
parameterize the name. On the other hand, why are you using temp tables to
return data to the client?
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Local temp tables are scoped to a session anyway so there's no need to
> parameterize the name. On the other hand, why are you using temp tables to
> return data to the client?
There may be plenty of reasons for this. Say for instance you result
set includes a column that is an expression. Assume further that you
also use the recordset client-side to keep new and updated data, and
that you write data back through stored procedures. Problem is that
this field becomes read-only if you are in ADO. So our application
has it's fair share of temp tables to work around this brain-deadness
in ADO.
And then there are of course plenty of procedures where a temp table
is used as a work table, and this is where data is returned from.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||David,
Say the ACCESS application deals with orders. You enter the order section,
in the background, the code takes your userid and gathers lots of info from
different places and puts it into ##DavidTable. Your ACCESS form is based
off that table. When you leave that section, you apply any updates from the
temp table to the normal data structures. While in the section, you may nee
d
a report. The UDF reads the ##useridTable and creates a table variable with
info from that, and from other sources. Say another user (me) gets in while
you are there to work with orders also, they will get ##SteveTable to work
with.
That is what I am working with.
Thanks
"David Portas" wrote:

> Local temp tables are scoped to a session anyway so there's no need to
> parameterize the name. On the other hand, why are you using temp tables to
> return data to the client?
> --
> David Portas
> SQL Server MVP
> --
>
>|||If you use local temp tables instead of global (prefix with a single #
instead of ##) then the table is scoped to the connection. That way you can
use the same name for each user and you won't have to parameterize the name.
In ADO.NET you could use a disconnected recordset for this but I don't know
what other options exist in Access. I suspect there ought to be a method not
using temp tables. You might want to ask the question in Access forum.
David Portas
SQL Server MVP
--|||Steve
Have tried adp of access where you can dynamically do all this stuff with
sql server
Regards
R.D
"Erland Sommarskog" wrote:

> David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> There may be plenty of reasons for this. Say for instance you result
> set includes a column that is an expression. Assume further that you
> also use the recordset client-side to keep new and updated data, and
> that you write data back through stored procedures. Problem is that
> this field becomes read-only if you are in ADO. So our application
> has it's fair share of temp tables to work around this brain-deadness
> in ADO.
> And then there are of course plenty of procedures where a temp table
> is used as a work table, and this is where data is returned from.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>

exec(select...), how supress the output?

Hi
I have a dynamically constructed sql query that I want to execute, e.g.
exec('select * from ' + @.tablename)

(1) Can I suppress the output somehow if this returns no values?
(2) Can I use the result of this query in another query somehow? e.g.
select
(3) Can I control the size of the columns in the output somehow

Thanks
F(foldface@.yahoo.co.uk) writes:
> I have a dynamically constructed sql query that I want to execute, e.g.
> exec('select * from ' + @.tablename)
> (1) Can I suppress the output somehow if this returns no values?

EXEC ('IF EXISTS (SELECT * FROM ' + @.tablename ' + ') SELECT * FROM ' +
@.tablename)

> (2) Can I use the result of this query in another query somehow? e.g.
> select

INSERT #tmp (...)
EXEC('...')

> (3) Can I control the size of the columns in the output somehow

This question is unclear. Output columns from SQL Server does not
really have any size, but that is up to the client tool you use.

But if you are using Query Analyzer, and want some nice output there,
you can use convert(varchar(n), ...) where n is the size of your choice.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> > (1) Can I suppress the output somehow if this returns no values?
> EXEC ('IF EXISTS (SELECT * FROM ' + @.tablename ' + ') SELECT * FROM ' +
> @.tablename)

worked this out in the end but as I'm using google for news access from
work I couldn't reply to my own mail. Thanks anyway

> > (2) Can I use the result of this query in another query somehow? e.g.
> > select
> INSERT #tmp (...)
> EXEC('...')

sorry, don't understand this? Can you elaborate?

> > (3) Can I control the size of the columns in the output somehow
> But if you are using Query Analyzer, and want some nice output there,
> you can use convert(varchar(n), ...) where n is the size of your choice.

create table #TempTable (id int)
insert #TempTable Values (convert(varchar(50), 2))
select * from #TempTable

I know this is meant to convert values but what exactly should I be seeing
here? I am talking about Query Analyser|||(foldface@.yahoo.co.uk) writes:
>> > (2) Can I use the result of this query in another query somehow? e.g.
>> > select
>>
>> INSERT #tmp (...)
>> EXEC('...')
> sorry, don't understand this? Can you elaborate?

You can save the output from an EXEC() statement in a temp table, and
then use the temp table in the next query.

>> > (3) Can I control the size of the columns in the output somehow
>>
>> But if you are using Query Analyzer, and want some nice output there,
>> you can use convert(varchar(n), ...) where n is the size of your choice.
> create table #TempTable (id int)
> insert #TempTable Values (convert(varchar(50), 2))
> select * from #TempTable
> I know this is meant to convert values but what exactly should I be seeing
> here? I am talking about Query Analyser

I don't know exactly what you are trying to achieve, and you might be
better off if you explained more about your business requirements.

But the column width in QA depends on two things: the column name and the
data type. If you want control over the column width, all output columns
must be varchar. You cannot control the width of a float or an int column.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

EXEC(@SQL) And Unicode Result Bug

I've got a dynamic SQL query that is generated inside a SP, and is run using
the
EXEC (@.SQL)
or
EXEC sp_executesql @.SQL
This has worked fine until now, where we are now using a database for
unicode charachters to support Japanese language.
All fixed code stored procedures return data correctly in the unicode
format. However, i have had to use string splicing in certain situations to
generate a fully customisable query. These queries all run using EXEC /
sp_executesql from inside the SP. However, i have discovered that all data i
s
return '?' instead of unicode charachters.
This is a cause of some serious issues, and i hope someone can tell me if
there is a solution for this!
Cheers
TrisTris (Tris@.discussions.microsoft.com) writes:
> I've got a dynamic SQL query that is generated inside a SP, and is run
> using the
> EXEC (@.SQL)
> or
> EXEC sp_executesql @.SQL
> This has worked fine until now, where we are now using a database for
> unicode charachters to support Japanese language.
> All fixed code stored procedures return data correctly in the unicode
> format. However, i have had to use string splicing in certain situations
> to generate a fully customisable query. These queries all run using EXEC
> / sp_executesql from inside the SP. However, i have discovered that all
> data is return '?' instead of unicode charachters.
> This is a cause of some serious issues, and i hope someone can tell me if
> there is a solution for this!
First of all, you should use sp_executesql and parameterised statements
rather than EXEC() for dynamic SQL. For a longer disucssion see
http://www.sommarskog.se/dynamic_sql.html.
As for your actual problem, it's diffcult to say without seeing the code.
But my guess would be that you have some varchar variable somewhere that
causes problems, or that you use '' for literals rather than N''. Again,
I suspect that these are problems that would go away if you always use
parameterised statements and never interpolate values into the query string.
I like to stress that this is all guessworks.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Can you post a script that reproduces the problem? Erland mentioned causes
of these symptoms that are not bugs but a specific case is needed to clearly
determine whether or not your issue is a defect or expected behavior.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tris" <Tris@.discussions.microsoft.com> wrote in message
news:A6E5E6A6-F96E-44C8-938F-EC0D459C7862@.microsoft.com...
> I've got a dynamic SQL query that is generated inside a SP, and is run
> using
> the
> EXEC (@.SQL)
> or
> EXEC sp_executesql @.SQL
> This has worked fine until now, where we are now using a database for
> unicode charachters to support Japanese language.
> All fixed code stored procedures return data correctly in the unicode
> format. However, i have had to use string splicing in certain situations
> to
> generate a fully customisable query. These queries all run using EXEC /
> sp_executesql from inside the SP. However, i have discovered that all data
> is
> return '?' instead of unicode charachters.
> This is a cause of some serious issues, and i hope someone can tell me if
> there is a solution for this!
> Cheers
> Tris|||Hi, thanks for the responses.
Yes, some of the arguments used to generate the string were VARCHAR, and
changing them to NVARCHAR has solved the problem.
Cheers
T

Thursday, March 29, 2012

execption with store procedure

ExecContextHit through SQL Profiler

ExecContextHit through SQL Profiler

Exec/Query across servers (unlinked)

EXEC('string') from a UDF?

exec(select...), how supress the output?

EXEC(@SQL) And Unicode Result Bug

Excel Import

Blog Archive

About Me