3G ram for SQL server: stored

Showing posts with label stored. Show all posts

Tuesday, March 27, 2012

A doubt about this: "Could not find stored Procedure"

Hi Everybody!

I have a problem in my SQL Server 2000 SP1 Database Called "Embossamento". When I run the following comand in the Query Analyzer, I have this error message :

command: exec dbcc_all_dbreindex
message: Could not find stored procedure 'dbcc_all_dbreindex'.

Otherwise, in the same server, but in another Database called "Autorizacao" I execute this dbcc_all_dbreindex with no problems.
Does anybody know why this happens?
I will be waiting for some reply, ok?
Thanks,This must be a custom stored procedure that uses the dbcc dbreindex command - check out the database, Autorizacao, and look for your stored procedures under that database.|||Ok! Thanks!
It worked! I just realized that my database did not have the procedure dbcc_all_dbreindex.
Now it's ok...

A difficult Combining Rows problem

Greetings,
I'm working to combine rows based on a time window and I am hoping to
be able to write a stored procedure to do this for me, rather than have
parse through all this data in my program. I'm not very well versed
with T-SQL syntax.. just enough to get by selecting using inner joins,
updating and inserting... thats about it. (Hence why I am here.)
The raw data I have below looks like this:
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 06:14:59, 7, 32, 13
1, 2005-10-05 06:15, 2005-10-05 06:29:59, 5, 29, 6
1, 2005-10-05 06:30, 2005-10-05 06:44:59, 5, 28, 4
1, 2005-10-05 06:45, 2005-10-05 06:59:59, 5, 29, 16
1, 2005-10-05 07:00, 2005-10-05 07:14:59, 5, 23, 13
1, 2005-10-05 07:15, 2005-10-05 07:29:59, 5, 25, 18
1, 2005-10-05 07:30, 2005-10-05 07:44:59, 5, 34, 49
1, 2005-10-05 07:45, 2005-10-05 07:59:59, 5, 31, 49
Pretty straight forward; you can see each entry is a 15 minute time
interval. What I want to be able to do is to use a view or a stored
procedure to view this in one hour chunks, like below:
groupID, StartTime, EndTime, Min, Max, Points
----
1, 2005-10-05 06:00, 2005-10-05 06:59:59, 5, 32, 39
1, 2005-10-05 07:00, 2005-10-05 07:59:59, 5, 34, 129
This involves several things:
- Recognizing that there are variable # of rows (maybe we only have 3
15 minute entries instead of 4)
- Getting a min of those row's min column
- Getting a max of those row's max column
- Getting a total for those row's points column
- Input to any view or whatver would be based on the startTime and
endTime and would always be in whole hours.
I have a feeling that I am going to be doing this all in the C# .NET
end of things, but it's at least worth a shot asking all of you SQL
experts. What I am basically interested in knowing is, do you all
think that this is possible using views or stored procedures or
something else I don't know about. I didn't even know about views
until i started researching how to do this.
Any ideas? Is this possible? Should I just give up and do it on the
C# end of things? Seems to me that it might be possible to do in a
stored procedure, but possible not worth my time. I aprpeciate any
help or suggestions.
JasonTry this:
SELECT groupid,
MIN(DATEADD(HH,DATEDIFF(HH,'20050101',st
arttime),'20050101')),
MIN(DATEADD(HH,DATEDIFF(HH,'20050101',st
arttime),'2005-01-01T00:59:59')),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(HH,'20050101',starttime) ;
David Portas
SQL Server MVP
--|||Hi
Check out the dateadd/datepart functions in Books Online for rounding times.
Try:
SELECT GROUPID, DATEADD(mi,-DATEPART(mi,Starttime),Starttime) AS StartTime,
DATEADD(ms,-3,DATEADD(hh,1,DATEADD(mi,-DATEPART(mi,Starttime),Starttime)))
AS EndTime,
Min([Min]), Max([Max]), SUM([Points])
FROM Readings
GROUP BY GroupId,
DATEADD(mi,-DATEPART(mi,Starttime),Starttime),
DATEADD(ms,-3,DATEADD(hh,1,DATEADD(mi,-DATEPART(mi,Starttime),Starttime)))
John
"Factor" wrote:

> Greetings,
> I'm working to combine rows based on a time window and I am hoping to
> be able to write a stored procedure to do this for me, rather than have
> parse through all this data in my program. I'm not very well versed
> with T-SQL syntax.. just enough to get by selecting using inner joins,
> updating and inserting... thats about it. (Hence why I am here.)
> The raw data I have below looks like this:
> groupID, StartTime, EndTime, Min, Max, Points
> ----
> 1, 2005-10-05 06:00, 2005-10-05 06:14:59, 7, 32, 13
> 1, 2005-10-05 06:15, 2005-10-05 06:29:59, 5, 29, 6
> 1, 2005-10-05 06:30, 2005-10-05 06:44:59, 5, 28, 4
> 1, 2005-10-05 06:45, 2005-10-05 06:59:59, 5, 29, 16
> 1, 2005-10-05 07:00, 2005-10-05 07:14:59, 5, 23, 13
> 1, 2005-10-05 07:15, 2005-10-05 07:29:59, 5, 25, 18
> 1, 2005-10-05 07:30, 2005-10-05 07:44:59, 5, 34, 49
> 1, 2005-10-05 07:45, 2005-10-05 07:59:59, 5, 31, 49
> Pretty straight forward; you can see each entry is a 15 minute time
> interval. What I want to be able to do is to use a view or a stored
> procedure to view this in one hour chunks, like below:
> groupID, StartTime, EndTime, Min, Max, Points
> ----
> 1, 2005-10-05 06:00, 2005-10-05 06:59:59, 5, 32, 39
> 1, 2005-10-05 07:00, 2005-10-05 07:59:59, 5, 34, 129
> This involves several things:
> - Recognizing that there are variable # of rows (maybe we only have 3
> 15 minute entries instead of 4)
> - Getting a min of those row's min column
> - Getting a max of those row's max column
> - Getting a total for those row's points column
> - Input to any view or whatver would be based on the startTime and
> endTime and would always be in whole hours.
> I have a feeling that I am going to be doing this all in the C# .NET
> end of things, but it's at least worth a shot asking all of you SQL
> experts. What I am basically interested in knowing is, do you all
> think that this is possible using views or stored procedures or
> something else I don't know about. I didn't even know about views
> until i started researching how to do this.
> Any ideas? Is this possible? Should I just give up and do it on the
> C# end of things? Seems to me that it might be possible to do in a
> stored procedure, but possible not worth my time. I aprpeciate any
> help or suggestions.
> Jason
>|||John Bell and David Portas,
I will have to read up on these Dateadd/DatePart parameters an actually
interpret what is going on within these statements, but just from what
you gave me here it looks like this will work out very well, and I
really appreciate the insight. This will allow me to vary that time
window fairly easily I do believe, all on a SQL call (that's much
better than bringing back all the data and parsing through it all it.
Thanks again,
Jason|||John
I have read over those functions and I now understand what they do and
how to use them, but I am still

as to why the min / max /
total fields actually work. I assume it has something to do with the
GROUP BY statements, but again, I don't know why.
Assuming black magic happens and thats just how it works, I should just
be able to change those hh,1 to hh,4 and get 4 hour increments instead.
When I do that, the Starttime and Endtime values do return correctly
(although I do get an entry for 8-12, 9-1, 10-2, etc... thats fine) but
the MIN/MAX/SUM stuff is still reflective of the 1 hour timing.. so
that black magic that is limiting the MIN/MAX/SUM to one hour is still
limiting them to one hour even with the altered start and end times.
I'm unsure how to fix or get around this because I don't yet understand
what is limiting that max to an hour in the first place. How does this
work? I've been tripped up GROUP BY things before, it's my kryptonite
for some reason.
Hope that is not too confusing, I'm all jumbled in my head.
I really apprecaite the help with this so far, you've all been
wonderful.
Jason|||John
I have read over those functions and I now understand what they do and
how to use them, but I am still

as to why the min / max /
total fields actually work. I assume it has something to do with the
GROUP BY statements, but again, I don't know why.
Assuming black magic happens and thats just how it works, I should just
be able to change those hh,1 to hh,4 and get 4 hour increments instead.
When I do that, the Starttime and Endtime values do return correctly
(although I do get an entry for 8-12, 9-1, 10-2, etc... thats fine) but
the MIN/MAX/SUM stuff is still reflective of the 1 hour timing.. so
that black magic that is limiting the MIN/MAX/SUM to one hour is still
limiting them to one hour even with the altered start and end times.
I'm unsure how to fix or get around this because I don't yet understand
what is limiting that max to an hour in the first place. How does this
work? I've been tripped up GROUP BY things before, it's my kryptonite
for some reason.
Hope that is not too confusing, I'm all jumbled in my head.
I really apprecaite the help with this so far, you've all been
wonderful.
Jason|||On 10 Nov 2005 11:41:54 -0800, Factor wrote:

>John
>I have read over those functions and I now understand what they do and
>how to use them, but I am still

as to why the min / max /
>total fields actually work. I assume it has something to do with the
>GROUP BY statements, but again, I don't know why.
Hi Jason,
Correct. The GROUP BY tells SQL Server to combine the data from several
rows into one row. This is normally used to report totals, minimum,
maximum per project, per section, etc. But with the appropriate
expression, it cal also be used to combine rows that fit in the same
"period" into one group.
Though John's and David's versions both work, I suggest you go with
Davids version, as this is more flexible. (And, once you get your head
around it, easier to understand as well).
Basically, John's version works by taking each of the date parts you
want to disregard (milliseconds, seconds, minutes), then subtracting
that amount of time from the Starttime. The end result will of course be
the last full hour equal to or before Starttime.
David's version works the other way around - it calculates the number of
full hours that have elapsed since a chosen anchor date, then adds that
number to the chosen anchor date. The result will be the same as John's
expression.
(Note: David chose to just use the number of hours for the group by, and
add it back to the anchor date in the SELECT clause only)

>Assuming black magic happens and thats just how it works, I should just
>be able to change those hh,1 to hh,4 and get 4 hour increments instead.
No. I'll give you two examples how to modify David's query to report on
4-hour intervals and to report on 1/2-hour intervals.
For 4-hour intervals, again calculate the number of hours since an
anchor date. Divide by 4 and truncate, then multiply by 4 again. Add
this number of hours to the anchor date. There you have the start of the
last 4-hour interval
SELECT groupid,
MIN(DATEADD(hour,
4 * (DATEDIFF(hour, '20050101', Starttime) / 4),
'20050101')),
MIN(DATEADD(hour,
4 * (DATEDIFF(hour, '20050101', Starttime) / 4),
'2005-01-01T03:59:59')),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(hour, '20050101', Starttime) / 4;
For 1/2-hour intervals, we can't divide the number of hours sice the
anchor date by 0.5, as that won't give us back the precision we already
lost. Instead, we'll have to calculate minutes and divide by 30:
SELECT groupid,
MIN(DATEADD(minute,
30 * (DATEDIFF(minute, '20050101', Starttime) /30),
'20050101')),
MIN(DATEADD(minute,
30 * (DATEDIFF(minute, '20050101', Starttime) /30),
'2005-01-01T00:29:59')),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(minute, '20050101', Starttime) /30;
In both cases, don't forget to change the shifted anchor value in the
expression for the end point of the interval. Instead of using the same
anchor date, then adding 30 minunte or 4 hours minus one second, the
anchor date is shifted by 30 minutes or 4 hours minus one second.
Now, the above code can still be simplified further. If your table
always has the complete data (as your sample roiws indicate), then you
could change the above queries to:
SELECT groupid,
MIN(StartTime), MAX(EndTime),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(minute, '20050101', Starttime) /30;
-- or: GROUP BY groupid, DATEDIFF(hour, '20050101', Starttime) / 4;
Note that this might show "holes" in the periods if your real data is
not as complete as the sample you posted indicates. But the advantage is
that you get rid of the "shifted" anchor date for calculating end time.
Final step would be to put it in a stored procedure and use a parameter
for the interval length (in minutes):
SELECT groupid,
MIN(StartTime), MAX(EndTime),
MIN(min), MAX(max), SUM(points)
FROM tbl
GROUP BY groupid, DATEDIFF(minute, '20050101', Starttime) / @.Interval;
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Jason,
What David Provided is an Excellent query .
Let us see if this query can help you.
Select GID , Min(STime) , Max(ETime) ,
Min(Minimum),Max(Maximum),Sum(Points)[co
lor=darkred]
>From yourTableName Group By[/color]
GID,Convert(varchar,STime,112),DatePart(
hh,STime)
Having same name as Functions/ Keyword sound confusing to me so I
changed them.
With Warm Regards
Jatinder Singh|||Hi
This is easier with David's method (see Hugo's reply for an explanation).
Dividing the number of hours by 4 and dropping the remainder will give you 4
hour chunks when they are multiplied back up. You also need to change the en
d
time to give a 4 hour gap.
SELECT groupid,
MIN(DATEADD(HH,
4*(DATEDIFF(HH,'20050101',starttime)/4),'20050101')
) AS Starttime,
MAX(DATEADD(HH,
4*(DATEDIFF(HH,'20050101',starttime)/4),'2005-01-01T03:59:59')
) AS Endtime,
MIN(min) AS [Min],
MAX(max) AS [Max],
SUM(points) AS [Total Points]
FROM Readings
GROUP BY groupid,
4*(DATEDIFF(HH,'20050101',starttime)/4)
John
"Factor" wrote:

> John
> I have read over those functions and I now understand what they do and
> how to use them, but I am still

as to why the min / max /
> total fields actually work. I assume it has something to do with the
> GROUP BY statements, but again, I don't know why.
> Assuming black magic happens and thats just how it works, I should just
> be able to change those hh,1 to hh,4 and get 4 hour increments instead.
> When I do that, the Starttime and Endtime values do return correctly
> (although I do get an entry for 8-12, 9-1, 10-2, etc... thats fine) but
> the MIN/MAX/SUM stuff is still reflective of the 1 hour timing.. so
> that black magic that is limiting the MIN/MAX/SUM to one hour is still
> limiting them to one hour even with the altered start and end times.
> I'm unsure how to fix or get around this because I don't yet understand
> what is limiting that max to an hour in the first place. How does this
> work? I've been tripped up GROUP BY things before, it's my kryptonite
> for some reason.
> Hope that is not too confusing, I'm all jumbled in my head.
> I really apprecaite the help with this so far, you've all been
> wonderful.
> Jason
>|||Wondeful! Lots ot take in, I thank everyone for their help. I've made
a lot of progress and I've learned a TON about SQL int he past two
days.
I hope I can help you all in the future with something!
Thanks again,
Jason

A DB block during stored procedure excecution...

Hi all,
I'd like to ask if is there any deference between executing a stored
procedure with 'exec' command in the Query analyzer and executing the code o
f
the stored procedure in Query analyzer too. I mean that I copied the source
of stored procedure in a window of the analyzer, I declare the parameters of
stored procedure as variables and I set the same values into them and I just
run the source...
When I execute the stored proc by "exec sp 'x', 'y', 'z' " where x,y,z are
the parameters sometimes it occures a block... When I run the source
declaring the parameters as variables etc, all are fine and I have never any
block... How is it possible? Is there any idea?
Thanks in advance..Christos,
What do you mean by block?
See if this helps:
[url]http://groups-beta.google.com/group/microsoft.public.sqlserver.server/msg/a5517668
94ed8781? q=%22what+is%22%2B%22parameter+sniffing%
22&hl=en&lr=&ie=UTF-8&rnum=1[/url
]
AMB
"Christos" wrote:

> Hi all,
> I'd like to ask if is there any deference between executing a stored
> procedure with 'exec' command in the Query analyzer and executing the code
of
> the stored procedure in Query analyzer too. I mean that I copied the sourc
e
> of stored procedure in a window of the analyzer, I declare the parameters
of
> stored procedure as variables and I set the same values into them and I ju
st
> run the source...
> When I execute the stored proc by "exec sp 'x', 'y', 'z' " where x,y,z are
> the parameters sometimes it occures a block... When I run the source
> declaring the parameters as variables etc, all are fine and I have never a
ny
> block... How is it possible? Is there any idea?
> Thanks in advance..
>|||I mean that stored procedure never ends, so it is blocking other users from
using the same tables etc (the sp updates data in some tables...)|||Christos,
If the sp never ends could be because another process is blocking the
resources needed by the sp and it is waiting, or the workload is heavy.
You can use EM (Management - Current Activity) or execute sp_who2 from QA to
see the processes and locks. You can also use Profiler to trace locks.
AMB
"Christos" wrote:

> I mean that stored procedure never ends, so it is blocking other users fro
m
> using the same tables etc (the sp updates data in some tables...)|||The strange in this situation is that when I have a block during the sp
execution, if I kill the process of sp and I try again to run it separetely
with the same parameters in the Q Analyzer's environment, the blocking
happens again. If I 'export' the source code in the analyzer's window and ru
n
it again using the parameters as variables it finishes imediatelly without
any problem...sql

A cursor with the name 'TESTING' does not exist.

Hi,
I have created a simple stored procedure and I am getting some errors in it.
I couldn't figure out why the error is.
Any help would be appreciated.
Here is my sp:
---
CREATE PROCEDURE dbo.test
(
@.ID int,
@.NUMBERS nvarchar(2000)
)
AS
DECLARE @.REF int
EXEC('DECLARE TESTING CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT REF FROM TABLE1 WHERE ID IN (SELECT DISTINCT ID
FROM TABLE2
WHERE (DS_ID IN (8))
AND (TABLE2.NUMBERS IN (SELECT CONVERT(nvarchar(20),VALUE) COLLATE
SQL_Latin1_General_CP1_CI_AS FROM fn_Split('''+@.NUMBERS+''','+''',''' + ')))
AND (REF <= 0) AND (S_ID ='+ @.ID + '))
GROUP BY REF')
OPEN TESTING
FETCH NEXT FROM TESTING
INTO @.REF
WHILE @.@.FETCH_STATUS = 0
BEGIN
--doing something
FETCH NEXT FROM TESTING INTO @.REF
END
CLOSE TESTING
DEALLOCATE TESTING
---
When I try to run this, the errors I get
Server: Msg 16916, Level 16, State 1, Procedure test, Line 21
A cursor with the name 'TESTING' does not exist.
Server: Msg 16916, Level 16, State 1, Procedure test, Line 22
A cursor with the name 'TESTING' does not exist.
Server: Msg 16916, Level 16, State 1, Procedure test, Line 33
A cursor with the name 'TESTING' does not exist.
Server: Msg 16916, Level 16, State 1, Procedure test, Line 34
A cursor with the name 'TESTING' does not exist.
Thanks
KiranKiran,
The rest of the Cursor code need to be within the EXEC statement.
Gopi
"Kiran" <kiran_nospam@.gmail.com> wrote in message
news:O0WJF0eWFHA.2540@.tk2msftngp13.phx.gbl...
> Hi,
> I have created a simple stored procedure and I am getting some errors in
> it.
> I couldn't figure out why the error is.
> Any help would be appreciated.
> Here is my sp:
> ---
> CREATE PROCEDURE dbo.test
> (
> @.ID int,
> @.NUMBERS nvarchar(2000)
> )
> AS
>
> DECLARE @.REF int
>
>
> EXEC('DECLARE TESTING CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
> SELECT REF FROM TABLE1 WHERE ID IN (SELECT DISTINCT ID
> FROM TABLE2
> WHERE (DS_ID IN (8))
> AND (TABLE2.NUMBERS IN (SELECT CONVERT(nvarchar(20),VALUE) COLLATE
> SQL_Latin1_General_CP1_CI_AS FROM fn_Split('''+@.NUMBERS+''','+''',''' +
> ')))
> AND (REF <= 0) AND (S_ID ='+ @.ID + '))
> GROUP BY REF')
> OPEN TESTING
> FETCH NEXT FROM TESTING
> INTO @.REF
>
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> --doing something
> FETCH NEXT FROM TESTING INTO @.REF
> END
> CLOSE TESTING
> DEALLOCATE TESTING
> ---
> When I try to run this, the errors I get
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 21
> A cursor with the name 'TESTING' does not exist.
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 22
> A cursor with the name 'TESTING' does not exist.
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 33
> A cursor with the name 'TESTING' does not exist.
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 34
> A cursor with the name 'TESTING' does not exist.
> Thanks
> Kiran
>|||Don't declare it as a local cursor, that makes it be local to the EXEC. And,
consider if you can do
without the cursor in the first place, tend to be code easier to read and pe
rform better.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kiran" <kiran_nospam@.gmail.com> wrote in message news:O0WJF0eWFHA.2540@.tk2msftngp13.phx.gb
l...
> Hi,
> I have created a simple stored procedure and I am getting some errors in i
t.
> I couldn't figure out why the error is.
> Any help would be appreciated.
> Here is my sp:
> ---
> CREATE PROCEDURE dbo.test
> (
> @.ID int,
> @.NUMBERS nvarchar(2000)
> )
> AS
>
> DECLARE @.REF int
>
>
> EXEC('DECLARE TESTING CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
> SELECT REF FROM TABLE1 WHERE ID IN (SELECT DISTINCT ID
> FROM TABLE2
> WHERE (DS_ID IN (8))
> AND (TABLE2.NUMBERS IN (SELECT CONVERT(nvarchar(20),VALUE) COLLATE
> SQL_Latin1_General_CP1_CI_AS FROM fn_Split('''+@.NUMBERS+''','+''',''' + ')
))
> AND (REF <= 0) AND (S_ID ='+ @.ID + '))
> GROUP BY REF')
> OPEN TESTING
> FETCH NEXT FROM TESTING
> INTO @.REF
>
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> --doing something
> FETCH NEXT FROM TESTING INTO @.REF
> END
> CLOSE TESTING
> DEALLOCATE TESTING
> ---
> When I try to run this, the errors I get
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 21
> A cursor with the name 'TESTING' does not exist.
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 22
> A cursor with the name 'TESTING' does not exist.
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 33
> A cursor with the name 'TESTING' does not exist.
> Server: Msg 16916, Level 16, State 1, Procedure test, Line 34
> A cursor with the name 'TESTING' does not exist.
> Thanks
> Kiran
>|||Thanks a ton Tibor.
Kiran kumar
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ed39iCfWFHA.616@.TK2MSFTNGP12.phx.gbl...
> Don't declare it as a local cursor, that makes it be local to the EXEC.
> And, consider if you can do without the cursor in the first place, tend to
> be code easier to read and perform better.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kiran" <kiran_nospam@.gmail.com> wrote in message
> news:O0WJF0eWFHA.2540@.tk2msftngp13.phx.gbl...
>

A cursor with the name 'MyRS' already exists

Any help appreciated on this one as I have scoured the internet and got no j
oy!
I have a complex set of triggers and stored procedures that should result in
changes to my _Company table being replicated to an equivalent table in a
different database on a different SQL 2000 Server.
This works 99% of the time. However, when I update a particular record, the
trigger associated with that table fires, and then returns error 16915 (A
cursor with the name 'MyRS' already exists).
It appears as though the update tried to fire previously and then some error
occurred, so that it now thinks this record is mid-transaction' I don't
really know.
The main question is, can I trace the root cause of this error message, and
remove it?
As I stated earlier, in 99% of cases, everything works fine, so I think this
is a problem with the cursor being left in an "open" state, rather than a
fundamental issue with the trigger code.
Here is the test code I am using to update the record and hence fire the
trigger:
UPDATE _Company SET update_note_at='Not Synchronised with Sage, Telesales'
WHERE (update_note_at IS NULL) AND reference IN('ABC1234567')
Here is the output received when running the above in query analyser:
(1 row(s) affected)
Server: Msg 16915, Level 16, State 1, Procedure at_Sage_Cust_Exp, Line 16
A cursor with the name 'MyRS' already exists.
The statement has been terminated.
Here is the actual code of the trigger:
CREATE TRIGGER at_Sage_Cust_Exp ON dbo._Company
FOR UPDATE
NOT FOR REPLICATION
AS
DECLARE @.TrigDate AS datetime
SET @.TrigDate=getdate()
DECLARE MyRS CURSOR
FOR
SELECT id, update_at, update_sage, On_AT, On_Sage, update_note_at,reference
FROM inserted
OPEN MyRS
declare @.coId as uniqueidentifier
DECLARE @.update_at AS bit
DECLARE @.Update_Sage AS bit
DECLARE @.On_AT AS bit
DECLARE @.On_Sage AS bit
DECLARE @.update_note_at AS nvarchar(100)
DECLARE @.reference AS nvarchar(30)
DECLARE @.Old_update_note_at AS nvarchar(100)
FETCH NEXT FROM MyRS INTO @.coId, @.update_at, @.Update_Sage, @.On_AT, @.On_Sage,
@.update_note_at, @.Reference
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
SET @.Old_update_note_at=(SELECT update_note_at FROM deleted WHERE id=@.coId)
IF @.update_at = 1 OR @.On_AT = 1
begin
set nocount on
IF @.update_note_at<>'Sage instigated sched_date update'
exec ap_Sage_CustAT_Exp @.coId
ELSE
UPDATE _Company SET update_note_at=@.Old_update_note_at WHERE id=@.coId
set nocount off
end
IF @.Update_Sage = 1 OR @.On_Sage = 1
begin
set nocount on
IF @.update_note_at<>'Sage instigated sched_date update'
exec ap_Sage_Cust_Exp @.coId, @.Reference
ELSE
UPDATE _Company SET update_note_at=@.Old_update_note_at WHERE id=@.coId
set nocount off
end
FETCH NEXT FROM MyRS INTO @.coId, @.update_at, @.update_sage, @.On_AT,
@.On_Sage, @.update_note_at,@.Reference
END
CLOSE MyRS
DEALLOCATE MyRS
THANKS,
Andy, MCDBAChange your cursor to "local"
that is
DECLARE MyRS CURSOR LOCAL
FOR
SELECT id, update_at, update_sage, On_AT, On_Sage,
update_note_at,reference
FROM inserted|||Never use cursors in triggers, is my advice. Why would you want to turn
every set-based update into a cursor?
Most of what you have can be done with two UPDATE statements so the
cursor looks superfluous. The only question is what your two SPs do.
Change them to set-based logic and you won't need the cursor at all.
David Portas
SQL Server MVP
--

A curious error message, local temp vs. global temp tables?!?!?

Hi all,

Looking at BOL for temp tables help, I discover that a local temp table (I want to only have life within my stored proc) SHOULD be visible to all (child) stored procs called by the papa stored proc.

However, the following code works just peachy when I use a GLOBAL temp table (i.e., ##MyTempTbl) but fails when I use a local temp table (i.e., #MyTempTable). Through trial and error, and careful weeding efforts, I know that the error I get on the local version is coming from the xp_sendmail call. The error I get is: ODBC error 208 (42S02) Invalid object name '#MyTempTbl'.

Here is the code that works:SET NOCOUNT ON

CREATE TABLE ##MyTempTbl (SeqNo int identity, MyWords varchar(1000))
INSERT ##MyTempTbl values ('Put your long message here.')
INSERT ##MyTempTbl values ('Put your second long message here.')
INSERT ##MyTempTbl values ('put your really, really LONG message (yeah, every guy says his message is the longest...whatever!')
DECLARE @.cmd varchar(256)
DECLARE @.LargestEventSize int
DECLARE @.Width int, @.Msg varchar(128)
SELECT @.LargestEventSize = Max(Len(MyWords))
FROM ##MyTempTbl

SET @.cmd = 'SELECT Cast(MyWords AS varchar(' +
CONVERT(varchar(5), @.LargestEventSize) +
')) FROM ##MyTempTbl order by SeqNo'
SET @.Width = @.LargestEventSize + 1
SET @.Msg = 'Here is the junk you asked about' + CHAR(13) + '---------'
EXECUTE Master.dbo.xp_sendmail
'YoMama@.WhoKnows.com',
@.query = @.cmd,
@.no_header= 'TRUE',
@.width = @.Width,
@.dbuse = 'MyDB',
@.subject='none of your darn business',
@.message= @.Msg
DROP TABLE ##MyTempTbl

The only thing I change to make it fail is the table name, change it from ##MyTempTbl to #MyTempTbl, and it dashes the email hopes of the stored procedure upon the jagged rocks of electronic despair.

Any insight anyone? Or is BOL just full of...well..."stuff"?I would still like to hear if anyone knows anything different, but while looking into Des' sendmail problem, I found this lil' tidbit in BOL for xp_sendmail If query is specified, xp_sendmail logs in to SQL Server as a client and executes the specified query. SQL Mail makes a separate connection to SQL Server; it does not share the same connection as the original client connection issuing xp_sendmail. I suspect the "separate connection to SQL Server" is the issue here?!?!?! Hmmmm...perhaps the local temp table can be seen by child processes called by the proc that creates the table EXCEPT in xp_sendmail, etc.|||You hit the problem right on the head... xp_sendmail does execute the query in a different context, meaning that it can't see local variables, settings, or temp tables. You can think of it almost as though xp_sendmail were cranking up OSQL.EXE to execute your query (that isn't what actually happens, but it is logically pretty close).

-PatP

Sunday, March 25, 2012

A couple of quick questions about SQL Server 6.5

I have a couple of quick questions to do with SQL 6.5.
1. Is there a stored proc that I can use in conjunction with
sp_foreachtable to step though all the user tables and list the permissions
that the public role has.
2. When I use the generate SQL Script , to have it script up all the
logins,users and permissions. I then look at the resulting script and it seem
to set all the passwords to (Null). Is there a way to have the passwords
intact?Hi
1. I think you would need to look at writing your own that queries the
sysprotects/sysusers tables.
2. I believe this is by design. You may want to look at:
http://tinyurl.com/5yw77
John
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:515AAE7C-B95F-4573-BD41-1BFB037F84D1@.microsoft.com...
> I have a couple of quick questions to do with SQL 6.5.
> 1. Is there a stored proc that I can use in conjunction with
> sp_foreachtable to step though all the user tables and list the
permissions
> that the public role has.
> 2. When I use the generate SQL Script , to have it script up all the
> logins,users and permissions. I then look at the resulting script and it
seem
> to set all the passwords to (Null). Is there a way to have the passwords
> intact?|||> 1. I think you would need to look at writing your own that queries the
> sysprotects/sysusers tables.
Or perhaps sp_helprotect?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%230$dreinEHA.3152@.TK2MSFTNGP10.phx.gbl...
> Hi
> 1. I think you would need to look at writing your own that queries the
> sysprotects/sysusers tables.
> 2. I believe this is by design. You may want to look at:
> http://tinyurl.com/5yw77
> John
> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> news:515AAE7C-B95F-4573-BD41-1BFB037F84D1@.microsoft.com...
>> I have a couple of quick questions to do with SQL 6.5.
>> 1. Is there a stored proc that I can use in conjunction with
>> sp_foreachtable to step though all the user tables and list the
> permissions
>> that the public role has.
>> 2. When I use the generate SQL Script , to have it script up all the
>> logins,users and permissions. I then look at the resulting script and it
> seem
>> to set all the passwords to (Null). Is there a way to have the passwords
>> intact?
>|||Hi Tibor
I didn't think that was in 6.5?
John
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OwymtcknEHA.3464@.tk2msftngp13.phx.gbl...
> > 1. I think you would need to look at writing your own that queries the
> > sysprotects/sysusers tables.
>
> Or perhaps sp_helprotect?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:%230$dreinEHA.3152@.TK2MSFTNGP10.phx.gbl...
> > Hi
> >
> > 1. I think you would need to look at writing your own that queries the
> > sysprotects/sysusers tables.
> >
> > 2. I believe this is by design. You may want to look at:
> > http://tinyurl.com/5yw77
> >
> > John
> >
> > "Russell" <Russell@.discussions.microsoft.com> wrote in message
> > news:515AAE7C-B95F-4573-BD41-1BFB037F84D1@.microsoft.com...
> >> I have a couple of quick questions to do with SQL 6.5.
> >>
> >> 1. Is there a stored proc that I can use in conjunction with
> >> sp_foreachtable to step though all the user tables and list the
> > permissions
> >> that the public role has.
> >>
> >> 2. When I use the generate SQL Script , to have it script up all the
> >> logins,users and permissions. I then look at the resulting script and
it
> > seem
> >> to set all the passwords to (Null). Is there a way to have the
passwords
> >> intact?
> >
> >
>|||Hi John,
To be honest, I'm not 100% certain. But if I'd guess, I'd guess that the proc did exist in 6.5.
Due to a disk crash, I don't have my VM Ware images with 6.5 anymore, so I guess we'll wait until
someone with 6.5 or 6.5 BOL can tell us for certain. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ens2aOmnEHA.3392@.TK2MSFTNGP15.phx.gbl...
> Hi Tibor
> I didn't think that was in 6.5?
> John
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OwymtcknEHA.3464@.tk2msftngp13.phx.gbl...
>> > 1. I think you would need to look at writing your own that queries the
>> > sysprotects/sysusers tables.
>>
>> Or perhaps sp_helprotect?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:%230$dreinEHA.3152@.TK2MSFTNGP10.phx.gbl...
>> > Hi
>> >
>> > 1. I think you would need to look at writing your own that queries the
>> > sysprotects/sysusers tables.
>> >
>> > 2. I believe this is by design. You may want to look at:
>> > http://tinyurl.com/5yw77
>> >
>> > John
>> >
>> > "Russell" <Russell@.discussions.microsoft.com> wrote in message
>> > news:515AAE7C-B95F-4573-BD41-1BFB037F84D1@.microsoft.com...
>> >> I have a couple of quick questions to do with SQL 6.5.
>> >>
>> >> 1. Is there a stored proc that I can use in conjunction with
>> >> sp_foreachtable to step though all the user tables and list the
>> > permissions
>> >> that the public role has.
>> >>
>> >> 2. When I use the generate SQL Script , to have it script up all the
>> >> logins,users and permissions. I then look at the resulting script and
> it
>> > seem
>> >> to set all the passwords to (Null). Is there a way to have the
> passwords
>> >> intact?
>> >
>> >
>>
>|||sp_helprotect has been around since since the beginning.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%234k4pTmnEHA.3988@.tk2msftngp13.phx.gbl...
> Hi John,
> To be honest, I'm not 100% certain. But if I'd guess, I'd guess that the
> proc did exist in 6.5.
> Due to a disk crash, I don't have my VM Ware images with 6.5 anymore, so I
> guess we'll wait until someone with 6.5 or 6.5 BOL can tell us for
> certain. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:ens2aOmnEHA.3392@.TK2MSFTNGP15.phx.gbl...
>> Hi Tibor
>> I didn't think that was in 6.5?
>> John
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in
>> message news:OwymtcknEHA.3464@.tk2msftngp13.phx.gbl...
>> > 1. I think you would need to look at writing your own that queries the
>> > sysprotects/sysusers tables.
>>
>> Or perhaps sp_helprotect?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:%230$dreinEHA.3152@.TK2MSFTNGP10.phx.gbl...
>> > Hi
>> >
>> > 1. I think you would need to look at writing your own that queries the
>> > sysprotects/sysusers tables.
>> >
>> > 2. I believe this is by design. You may want to look at:
>> > http://tinyurl.com/5yw77
>> >
>> > John
>> >
>> > "Russell" <Russell@.discussions.microsoft.com> wrote in message
>> > news:515AAE7C-B95F-4573-BD41-1BFB037F84D1@.microsoft.com...
>> >> I have a couple of quick questions to do with SQL 6.5.
>> >>
>> >> 1. Is there a stored proc that I can use in conjunction with
>> >> sp_foreachtable to step though all the user tables and list the
>> > permissions
>> >> that the public role has.
>> >>
>> >> 2. When I use the generate SQL Script , to have it script up all the
>> >> logins,users and permissions. I then look at the resulting script and
>> it
>> > seem
>> >> to set all the passwords to (Null). Is there a way to have the
>> passwords
>> >> intact?
>> >
>> >
>>
>>
>

Monday, March 19, 2012

8updating a sql servi stored procedures/triggers

hi all,
we have two servers. we have an oracle 8i and a sql 2000 server.
is it possible to write stored procedures or triggers in oracle 8i that will create, update and delete records in sql server.if so what are the steps to do so and an example of a trigger/stored procedure. i have seen many ways to connect two oracle or two sql servers. i also see ways to link the servers but what we are trying to do is when something commits in the the oracle database that it will also do the same in the sql server. keep in mind out table structures are different due to security rights on the sql server that is why the some fields will not move over. also what is the best method to do this? i also see using vb uding ado rdo. i see ole db and odbc connections between the two, and i see some third party software. any help is much appreciaedI would think that your best bet would be to accomplish this task using an application front end and not trying to do it through a trigger or stored proc on Oracle. But that's probably because I'm more of a developer than a DBA (tho' I'm working on the latter).

Using ADO, you can use BeginTrans and CommitTrans to ensure that a transaction completes on SQL before committing the transaction on Oracle. Set checks for errors and use RollbackTrans on both connections to "undo" everything if a problem occurs.

That said, what really matters is your requirement; are these transactions user-initiated or are they meant to be a part of an automated extract? Though each can be handled by ADO, you would handle each situation a little differently.

Regards,

Hugh Scott

Originally posted by rdavidoff
hi all,
we have two servers. we have an oracle 8i and a sql 2000 server.
is it possible to write stored procedures or triggers in oracle 8i that will create, update and delete records in sql server.if so what are the steps to do so and an example of a trigger/stored procedure. i have seen many ways to connect two oracle or two sql servers. i also see ways to link the servers but what we are trying to do is when something commits in the the oracle database that it will also do the same in the sql server. keep in mind out table structures are different due to security rights on the sql server that is why the some fields will not move over. also what is the best method to do this? i also see using vb uding ado rdo. i see ole db and odbc connections between the two, and i see some third party software. any help is much appreciaed|||thanks for your suggestion,
here is the business process that will give you a better understanding of our problem. see a sql server was purchased becuase a web developer is gong to do web based reports using tables from sql server 2000. the problem is that the company has a legacy 8.04 oracle dtabase that has been installed for years. the web developer wants to use and convinced hi people he wants to use sql server 2000. our job is to come up with a process to make the sql server data be as real time as possible to the oracle database b/c everything is really getting stored there. so whether it be by triggers, stored procedures, or some front end using either ado, ole db, odbc, or whatever api thats out there.
do you have some sort of sample of an ado instance. all out transactions will be user initiated and committed on the oracle side
thanks again,
robert

Originally posted by hmscott
I would think that your best bet would be to accomplish this task using an application front end and not trying to do it through a trigger or stored proc on Oracle. But that's probably because I'm more of a developer than a DBA (tho' I'm working on the latter).

Using ADO, you can use BeginTrans and CommitTrans to ensure that a transaction completes on SQL before committing the transaction on Oracle. Set checks for errors and use RollbackTrans on both connections to "undo" everything if a problem occurs.

That said, what really matters is your requirement; are these transactions user-initiated or are they meant to be a part of an automated extract? Though each can be handled by ADO, you would handle each situation a little differently.

Regards,

Hugh Scott|||Ewww, yuck. I realize decisions have already been made, but there's really nothing wrong with developing web apps using Oracle. I prefer SQL, but that's a different story.

We're doing something that might be considered a bit similar, but it is by no means "realtime". We have a production AS/400. Every fifteen minutes we siphon off selected data to a SQL server. We then use the SQL server to display the data on the web. The customer accepts the fifteen minute delay as a penalty. A side benefit is that the load on the AS/400 is regular and predictable while the customer can run queries to his heart's delight (and they delight in it a lot!) on the SQL server.

To pull the data from Oracle to SQL we use DTS packages that are scheduled on the SQL server.

Option 1
Do you control (own the source code) the application that stores the data on the Oracle Server?

If the answer is "yes", then you have a lot of rewriting to do to make updates to both databases, but it is potentially doable and "real time". Whether the re-write is justifiable is another matter for management to decide.

Option b
A possible alternative is to use a Linked Server (establish the Oracle Server as a linked server on SQL). You can then write distributed Queries that access the Oracle data directly. CAUTION: my experience with distributed queries is not stellar. The more complex they are, the longer they take to run. See SQL Books On Line for more information on Distributed Queries and Linked Servers.

Option iii
Use DTS packages to pull the data into SQL on a scheduled basis. This is doable (we are doing it now) but it requires a LOT of thought into what data is going to be brought across and consideration must be given to the state of the data (ie, open orders versus closed orders, etc).

Option Other
Maybe there is a way to do this in Oracle using triggers or stored procedures. You might even look into replication (now there's an idea!), but I have no idea how to go about setting it up.

Or you could simply find a web developer willing to work with Oracle!!!

Sorry, I hope one of these helps!

Regards,

Hugh Scott

Originally posted by rdavidoff
thanks for your suggestion,
here is the business process that will give you a better understanding of our problem. see a sql server was purchased becuase a web developer is gong to do web based reports using tables from sql server 2000. the problem is that the company has a legacy 8.04 oracle dtabase that has been installed for years. the web developer wants to use and convinced hi people he wants to use sql server 2000. our job is to come up with a process to make the sql server data be as real time as possible to the oracle database b/c everything is really getting stored there. so whether it be by triggers, stored procedures, or some front end using either ado, ole db, odbc, or whatever api thats out there.
do you have some sort of sample of an ado instance. all out transactions will be user initiated and committed on the oracle side
thanks again,
robert|||Originally posted by hmscott

We're doing something that might be considered a bit similar, but it is by no means "realtime". We have a production AS/400. Every fifteen minutes we siphon off selected data to a SQL server. We then use the SQL server to display the data on the web. The customer accepts the fifteen minute delay as a penalty. A side benefit is that the load on the AS/400 is regular and predictable while the customer can run queries to his heart's delight (and they delight in it a lot!) on the SQL server.

I am atempting this very thing. Only difference is it is not Oracle it is a remote Turbo Image Database (ISAM files).

I have a stored procedure written that does a set of queries using OPENQUERY and updates three tables. My plan is to run it once every 10 - 15 minutes or so.

My stumbling block is that the job I scheduled to run the stored procedure is failing, but once I get that worked out it should be fine. I have talked to the owners of the data (it is a registration database for a community college) and the delay is acceptable in this situation.

So, if the data is not life threatining if there is a delay, I'd say that this is your best bet.|||i was reading ur option iii and it really doesn't sound that bad. thanks again for all the advice 15 minutes delayed is still good enough for what we intend to use this data for. after allwe are not a brokerage company that needs streaming real time data. do you have any information or advise pn dts packages and where may i look into this further.
thanks again
Bob

Originally posted by hmscott
Ewww, yuck. I realize decisions have already been made, but there's really nothing wrong with developing web apps using Oracle. I prefer SQL, but that's a different story.

We're doing something that might be considered a bit similar, but it is by no means "realtime". We have a production AS/400. Every fifteen minutes we siphon off selected data to a SQL server. We then use the SQL server to display the data on the web. The customer accepts the fifteen minute delay as a penalty. A side benefit is that the load on the AS/400 is regular and predictable while the customer can run queries to his heart's delight (and they delight in it a lot!) on the SQL server.

To pull the data from Oracle to SQL we use DTS packages that are scheduled on the SQL server.

Option 1
Do you control (own the source code) the application that stores the data on the Oracle Server?

If the answer is "yes", then you have a lot of rewriting to do to make updates to both databases, but it is potentially doable and "real time". Whether the re-write is justifiable is another matter for management to decide.

Option b
A possible alternative is to use a Linked Server (establish the Oracle Server as a linked server on SQL). You can then write distributed Queries that access the Oracle data directly. CAUTION: my experience with distributed queries is not stellar. The more complex they are, the longer they take to run. See SQL Books On Line for more information on Distributed Queries and Linked Servers.

Option iii
Use DTS packages to pull the data into SQL on a scheduled basis. This is doable (we are doing it now) but it requires a LOT of thought into what data is going to be brought across and consideration must be given to the state of the data (ie, open orders versus closed orders, etc).

Option Other
Maybe there is a way to do this in Oracle using triggers or stored procedures. You might even look into replication (now there's an idea!), but I have no idea how to go about setting it up.

Or you could simply find a web developer willing to work with Oracle!!!

Sorry, I hope one of these helps!

Regards,

Hugh Scott|||Some gotcha's that you may want to consider and a site that is well worth looking at:

1. Data Transformation Services (DTS) are essentially mini-programs that are created in a GUI environment. As such, there is a LOT of complexity that is hidden from you. As you can imagine, there are both strong points and weak points to this:

a. Strong point: they are fairly easy to create and work with
b. Strong point: there are a lot of objects to work with and that makes just about any task doable.
c. Weak point: they are sometimes difficult to manage unless you spend a lot of time learning how to use dynamic properties that can be stored on the server
d. Weak point: since it's easy for a novice to work with these, it's easy to make a novice mistake and mess up a bunch of things

2. DTS runs in a separate memory space from the SQL server application. I have recently experienced some very negative side effects from having too many DTS packages running simultaneously. If it is at all possible, consider running DTS on a separate server from the database server.

3. This is a hard one for people new to DTS to understand. DTS always runs in the context of the client machine on which you are viewing the DTS package. Even though you are in Enterprise Manager and you THINK you are executing the DTS package on the SQL server, it is actually running using the DLL context of your client workstation. Send me an e-mail and I'll explain this one further. It's an important concept and not very well explained (in my opinion) in SQL BOL.

4. I have had a VERY bad experience with Meta Data Services. I would recommend avoiding this feature in SQL Server in a production environment.

5. Be sure that your client workstation and the SQL Server where the packages are stored are running the SAME version of SQL Server (same SP, too). DTS pacakges can be migrated from older versions to newer versions, but they are not necessarily backward compatible. DTS pacakges in SQL 7.0 were pretty bad until around SP3.

66. A good website to check out is www.sqldts.com. I found a really great tool for backing up DTS pacakges on there.

Good luck,

Hugh Scott
Originally posted by rdavidoff
i was reading ur option iii and it really doesn't sound that bad. thanks again for all the advice 15 minutes delayed is still good enough for what we intend to use this data for. after allwe are not a brokerage company that needs streaming real time data. do you have any information or advise pn dts packages and where may i look into this further.
thanks again
Bob

Thursday, March 8, 2012

70-229 Microsoft example

I am studying for the 70--229 microsoft exam using the MS study guide. An example of a stored procedure used to add customer and check for dupes is below. However although the dupelicatecheck sp is called duplicate contact's can be added. Can anyone give me advise on where I may have gone wrong with this example.

CREATE procedure dbo.addcustomer
@.firstname varchar(30)='Unknown',
@.lastname varchar(30)='Unknown', @.Phone varchar(24)= NULL,
@.Address1 varchar(60)= NULL,
@.address2 varchar(60)='unknown', @.city varchar(15)=Null,
@.state varchar(7)=Null, @.zip varchar(12)=NULL
as
IF (@.Firstname='Unknown') and (@.Lastname='Unknown')
return(1)

Else IF @.phone is null
return(2)
Else IF
@.Address1 is null or @.city is null or
@.state is null or @.zip is null
Return(3)
--begin nesting
declare @.r_code int, @.v_firstname varchar(30),
@.v_lastname varchar(30), @.v_city varchar(15),
@.v_state varchar(7), @.v_phone varchar(24)
execute @.r_code=dbo.checkforduplicatecustomer
@.1_firstname=@.firstname, @.1_lastname=@.lastname,
@.1_city=city, @.1_state=@.state, @.1_phone=@.phone,
@.o_firstname=@.v_firstname output,
@.o_lastname=@.v_lastname output, @.o_city=@.v_city output,
@.o_state=@.v_state output, @.o_phone=@.v_phone output
if @.@.rowcount>0
begin
Print'A duplicate record was found for' + @.v_firstname+' ' +
@.v_lastname
print 'in' +@.v_city + ' '+ @.v_state + ' with a phone number'
print 'of' + @.v_phone +','
return(5)
end
--end nesting
insert [bookshopdb].[dbo].[customers]
(firstname, lastname, phone,
address1, address2, city, state, zip)
values
(@.firstname, @.lastname, @.phone,
@.address1, @.address2, @.city, @.state, @.zip)
Return(select @.@.identity as 'identity')
if @.@.error <>0
return (4)

create procedure dbo.checkforduplicatecustomer
@.1_firstname varchar(30)='Unknown',
@.1_Lastname varchar(30)='Unknown',
@.1_City varchar (15)= Null, @.1_state varchar(7)= null,
@.1_phone varchar(24)= null, @.o_firstname varchar (30) output,
@.o_lastname varchar(30) output, @.o_city varchar (15) output,
@.o_state varchar(7) output, @.o_phone varchar(24) output
as
select @.o_firstname=firstname, @.o_lastname=lastname,
@.o_city=city, @.o_state=state, @.o_phone=phone
from customers
where firstname=@.1_firstname and lastname=@.1_lastname
and city=@.1_city and state=@.1_state and phone=@.1_phone
if @.@.rowcount <> 0
return (5)You assign the output of checkforduplicatecustomer to variable @.r_code, but your main procedure checks that value of if @.@.rowcount to determine whether to generate a duplicate record error. Shouldn't you be checking @.r_code instead?

Also, you would be better off wrting your checkforduplicatecustomer procedure as a user-defined function. They are much easier to use in code, like this:
if dbo.checkforduplicatecustomer(Parameters...) = 1 begin....

And why don't you just check for duplicates in your main code using IF EXISTS? That would be the simplest way, unless you really need to reuse the duplicate checking logic in other procedures as well.

blindman

7/11 Windows update broke my stored procedure

I have a managed code DLL that is used as a CLR assembly in my database. A stored procedure in the database references this CLR assembly.
My CLR assembly has a dependency on Microsoft's System.Management.dll.
In order to get my assembly to work, I added System.Management.dll as an assembly to the database using:

CREATE ASSEMBLY SystemManagement
FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Management.dll'
WITH PERMISSION_SET = UNSAFE
GO

For months this stored procedure has worked fine... up until today.

Today (7/11/07) I noticed a new Microsoft update was available.
I applied the patch to an XP machine that has my database and now my CLR assembly stored procedure no longer works.
I checked a 2003 Server machine that did not have the latest patch and see that the stored procedure is working fine.
I then apply Microsoft update to the 2003 server machine.
Now the stored procedure no longer works on the 2003 machine.

I deleted my stored procedure and CLR assembly and then added them back with no success.
I then deleted and added back the System.Management.dll assembly again and suddenly now my stored procedure works again.

This is BAD. I can't have Windows updates blowing up my app.
What am I doing wrong here?
I must use the System.Management.dll in my CLR stored procedure but I can't have changes to Microsoft's files causing me to be unable to reference them as assemblies.

How can my CLR stored procedure assembly reference a Microsoft assembly such that changes to the Microsoft assembly does not cause version mismatches?

Hmm, that's weird. In what way does you proc no longer work?

The reason I think it is weird is that as the dll in question is loaded from the database, having a new dll should not really matter, unless the patch also changes something else that the dll calls into and therefore fails.

So, once again - in what way does your proc fail?

Niels

|||

Unfortunately I do not remember the exact phraseology of the exception (the problem has been fixed on my development machine) but it did mention that the assembly it was looking for did not match the version found in the GAC. I'm certain it mentioned the GAC.

I will include as much detail as I can below.

I have a web app that makes calls to the stored procedure like this:

Code Snippet

try
{
using (SqlConnection sqlConnection = new SqlConnection(strConnectionString))
{
sqlCommand = new SqlCommand("usp_MyCLRStoredProcedure", sqlConnection);

sqlConnection.Open();

sqlCommand.CommandType = CommandType.StoredProcedure;

sqlParameter = new SqlParameter("@.iRetVal", SqlDbType.Int);
sqlParameter.Direction = ParameterDirection.ReturnValue;
sqlCommand.Parameters.Add(sqlParameter);

sqlParameter = new SqlParameter("@.iVersion", SqlDbType.Int);
sqlParameter.Value = (int)eVersion;
sqlCommand.Parameters.Add(sqlParameter);

// After applying the 7/11 Windows update, execution of
// the next line results in an exception being thrown
sqlCommand.ExecuteNonQuery();

// Code after this point never executes due to exception being thrown...

}
}
catch (Exception e)
{
Console.WriteLine("Error while attempting to access database:\n" + e.ToString());
}

The CLR stored procedure is a SQL Server Project written in Visual Studio and compiled into a DLL. This C# code fetches the CPU ID of the machine, and does some cryptography work. My CLR stored procedure is similar to the following code block (I've tried to omit non-relevant parts):

Code Snippet

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Globalization;
using System.Management;
using System.IO;
using System.Security.Cryptography;

public partial class StoredProcedures
{

// This is the entry point of the stored procedure
[Microsoft.SqlServer.Server.SqlProcedure]
public static int MyStoredProcedure(int iVersion)
{
// ... code ...

// calls are made to Environment.MachineName

// ... more code ...

// get the processor id
ManagementClass managementClass = new ManagementClass( "Win32_Processor" );
ManagementObjectCollection mocInstances = managementClass.GetInstances();

// ... more code ...
// Calls are made to System.Security.Cryptography.HMACSHA1
// Calls are made to System.Text.Encoding
// Calls are made to System.Convert
// ... more code ...

// Call ExecuteNonQuery() on a regular (T-SQL based) stored procedure

// ... more code ...
}
}

To hook the CLR DLL code above with a stored procedure that is accessible by SQL Server, I performed the following from within Microsoft SQL Server Management Studio:

Code Snippet

USE MyDatabase
GO

-- SQL server needs to know if a database is "trustworthy" before it
-- will allow it to load any extra assemblies
ALTER DATABASE MyDatabase SET trustworthy ON
GO

-- To add a reference to a "SQL Server Project" within Visual Studio
-- you must first add the dll you want to reference to the database itself
-- The following code block places "System.Management.dll" and all of
-- the assemblies it depends on in the database.
-- After completing this step, you should be able to use the "Add Reference"
-- feature of Visual Studio to add the "SystemManagement" class as a
-- reference in your SQL Server Project.
CREATE ASSEMBLY SystemManagement
FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Management.dll'
WITH PERMISSION_SET = UNSAFE
GO

-- The "compatibility level" of the Database needs to be set to 90
-- before it has the ability to recognize CLR calls
exec sp_dbcmptlevel 'MyDatabase', 90
GO

-- SQL Server 2005 won't recognize CLR calls unless you manually enable it.
-- To enable the CLR on SQL Server 2005, execute the code block below
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

-- Load a CLR DLL as an assembly onto the database
CREATE ASSEMBLY MyAssembly
FROM 'C:\Devel\MyProject\bin\Debug\MyCLRStoredProcedure.dll'
WITH PERMISSION_SET = UNSAFE
GO

-- The following code block demonstrates how to create and associate
-- a stored procedure with a method in a CLR assembly.
-- Of course, the CLR assembly must already be registered
-- with the database (as shown above) before this is attempted.
-- The "EXTERNAL NAME" clause takes the following parameter
-- [Assembly name (as shown in MSSQL Server Management Studio)].ClassName.MethodName
CREATE PROCEDURE usp_MyCLRStoredProcedure
@.iVersion int
AS EXTERNAL NAME MyAssembly.StoredProcedures.MyStoredProcedure
GO

-- Give execute priveleges to the newly added stored procedure
GRANT EXECUTE ON usp_MyCLRStoredProcedure TO MyWebAppUser
GO

-- To debug the CLR stored procedure, the PDB file
-- must be added to the assembly.
-- 1) The MyCLRStoredProcedure.PDB file must
-- be installed in the database; execute the
-- T-SQL code block below.
-- 2) In the Visual Studio IDE, select
-- Debug -> Attach to Process
-- 3) Select "sqlserver.exe" from the Available Processes
-- list box and click the "Attach" button.
-- 4) Breakpoints should now be functional in
-- the MyCLRStoredProcedure source code.
ALTER ASSEMBLY MyAssembly
ADD FILE FROM 'C:\Devel\MyProject\bin\Debug\MyCLRStoredProcedure.pdb'
GO

At this point, everything is wired up and ready to go.

Prior to the 7/11 Windows update, a user of the web app could initiate a call to "usp_MyCLRStoredProcedure" and everything would work fine.
After applying the 7/11 Windows update, an exception now occurs somewhere during the call to usp_MyCLRStoredProcedure.

After deleting the stored procedure "usp_MyCLRStoredProcedure" and the assemblies "MyAssembly" and "SystemManagement" from the database and then re-running the SQL code block shown above, everything worked normally again.

I have one machine that I haven't fixed yet but it does not have any sort of development environment. I can try to get a development environment set up on the machine or instrument the web app to log the exception text but in the meantime if anyone has any clues or sees something I am doing wrong in the code above, please let me know.

Thank you.

|||it would be definitely easier to help you with the exact error message that is thrown.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Thursday, February 9, 2012

3rd Party Performance Tool/Software

Is there any software that can analyze stored procedure performance and
recommend solutions?
Thanks,
Dave
How about the database engine tuning advisor, which ships with the product?
You can send it a workload consisting of a single stored procedure call, but
for a better analysis and set of recommended enhancements, you're better off
providing a workload of a typical business day.
Note that this tool will recommend structural changes like adding/removing
indexes, partitioning, etc. It will not look at your SELECT query with 18
unions and suggest a single FROM with a CASE expression. :-)
A
"David" <nospam@.home.com> wrote in message
news:216B98B9-8D9B-4143-A12C-810B24CD2723@.microsoft.com...
> Is there any software that can analyze stored procedure performance and
> recommend solutions?
> Thanks,
> Dave
|||As far as I am aware, there is no tool that would take a stored procedure,
optimize and refactor it, and recommend alternative solutions.
The only 'tool' that can do that is a skilled DBA!!
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"David" <nospam@.home.com> wrote in message
news:216B98B9-8D9B-4143-A12C-810B24CD2723@.microsoft.com...
> Is there any software that can analyze stored procedure performance and
> recommend solutions?
> Thanks,
> Dave
|||InDepth for SQL Server has such a feature which it implements by levaraging
either ITW or DTA depending on SQL version. It uses performance data stored
in its Performance Warehouse to feed the recommendations. It also clearly
identifies the statements and batches that are consuming most resource to
help guide your tuning efforts. To be honest we mostly use it for the latter
as it's extremely light weight and much less resource intensive than a trace
plus we don't want ITW/DTA run against a production database. However by
identifying the main resource consumers these can be tuned and measured in
the QA envionment to produce recommendations for production.
http://www.symantec.com/enterprise/products/overview.jsp?pcid=1021&pvid=317_1
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"David" <nospam@.home.com> wrote in message
news:216B98B9-8D9B-4143-A12C-810B24CD2723@.microsoft.com...
> Is there any software that can analyze stored procedure performance and
> recommend solutions?
> Thanks,
> Dave

Tuesday, March 27, 2012

A doubt about this: "Could not find stored Procedure"

A difficult Combining Rows problem

A DB block during stored procedure excecution...

A cursor with the name 'TESTING' does not exist.

A cursor with the name 'MyRS' already exists

A curious error message, local temp vs. global temp tables?!?!?

Sunday, March 25, 2012

A couple of quick questions about SQL Server 6.5

A couple of quick questions about SQL Server 6.5

Monday, March 19, 2012

8updating a sql servi stored procedures/triggers

Thursday, March 8, 2012

70-229 Microsoft example

7/11 Windows update broke my stored procedure

Thursday, February 9, 2012

3rd Party Performance Tool/Software

3G ram for SQL server

Blog Archive

About Me