I have 2 fields, dtDate and dtTime. dtDate is datetime and dtTime is
nvarchar(8) type. I have 2 problems.
1. I need a way to concatenate the 2 fields into 1 datetime field with a
select statement
2.In my example data below, you can see that dtTime is in a "military" time
format. Is there a way within SQL to convert it to a normal time format with
the AM/PM?
Any ideas or help would be greatly appreciated.
Example of Data ******************
dtDate dtTime
---
3/14/2006 12:00:00 AM 01:21:57
3/15/2006 12:00:00 AM 14:42:53Why are these separate? Anyway, try this, untested...
SELECT CONVERT(CHAR(10), dtDate, 120) + ' ' +
LTRIM(SUBSTRING(CONVERT(CHAR(22), dtTime, 22), 9, 14)) FROM tablename
Of course, this will only work if all of your dtTime values are valid times.
Since you chose NVARCHAR for some reason, this is an extra hassle to
validate / constrain.
"Scott Bailey" <sbailey@.mileslumber.com> wrote in message
news:%23y6Ns69YGHA.4580@.TK2MSFTNGP03.phx.gbl...
>I have 2 fields, dtDate and dtTime. dtDate is datetime and dtTime is
>nvarchar(8) type. I have 2 problems.
> 1. I need a way to concatenate the 2 fields into 1 datetime field with a
> select statement
> 2.In my example data below, you can see that dtTime is in a "military"
> time format. Is there a way within SQL to convert it to a normal time
> format with the AM/PM?
> Any ideas or help would be greatly appreciated.
>
> Example of Data ******************
> dtDate dtTime
> ---
> 3/14/2006 12:00:00 AM 01:21:57
> 3/15/2006 12:00:00 AM 14:42:53
>|||Your code returns just the date part like:
2006-03-14
Can you modify it to display the date and time? Also, can you have the whole
result converted to datetime format?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23wruO%239YGHA.4688@.TK2MSFTNGP04.phx.gbl...
> Why are these separate? Anyway, try this, untested...
> SELECT CONVERT(CHAR(10), dtDate, 120) + ' ' +
> LTRIM(SUBSTRING(CONVERT(CHAR(22), dtTime, 22), 9, 14)) FROM tablename
> Of course, this will only work if all of your dtTime values are valid
> times. Since you chose NVARCHAR for some reason, this is an extra hassle
> to validate / constrain.
>
> "Scott Bailey" <sbailey@.mileslumber.com> wrote in message
> news:%23y6Ns69YGHA.4580@.TK2MSFTNGP03.phx.gbl...
>|||One last note, I didn't create this db, I just inherited it or I would never
have split the Date and Time into different fields.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23wruO%239YGHA.4688@.TK2MSFTNGP04.phx.gbl...
> Why are these separate? Anyway, try this, untested...
> SELECT CONVERT(CHAR(10), dtDate, 120) + ' ' +
> LTRIM(SUBSTRING(CONVERT(CHAR(22), dtTime, 22), 9, 14)) FROM tablename
> Of course, this will only work if all of your dtTime values are valid
> times. Since you chose NVARCHAR for some reason, this is an extra hassle
> to validate / constrain.
>
> "Scott Bailey" <sbailey@.mileslumber.com> wrote in message
> news:%23y6Ns69YGHA.4580@.TK2MSFTNGP03.phx.gbl...
>|||I was basing it on this:
SELECT CONVERT(CHAR(10), GETDATE(), 120) + ' ' +
LTRIM(SUBSTRING(CONVERT(CHAR(22), GETDATE(), 22), 9, 14));
Which returns:
2006-04-19 7:40:33 PM
Maybe it will work better like this:
SELECT CONVERT(CHAR(10), dtDate, 120) + ' ' +
LTRIM(SUBSTRING(CONVERT(CHAR(22), CONVERT(DATETIME, dtTime), 22), 9, 14));
I would put this into a view so you don't have to repeat this calculation
everywhere.
If that still doesn't yield the correct results, then please post DDL and
sample data so we can actually try and reproduce your issue.
"scott" <sbailey@.mileslumber.com> wrote in message
news:eHuNqXAZGHA.3880@.TK2MSFTNGP04.phx.gbl...
> Your code returns just the date part like:
> 2006-03-14
> Can you modify it to display the date and time? Also, can you have the
> whole result converted to datetime format?
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:%23wruO%239YGHA.4688@.TK2MSFTNGP04.phx.gbl...
>|||I was basing it on this:
SELECT CONVERT(CHAR(10), GETDATE(), 120) + ' ' +
LTRIM(SUBSTRING(CONVERT(CHAR(22), GETDATE(), 22), 9, 14));
Which returns:
2006-04-19 7:40:33 PM
Maybe it will work better like this:
SELECT CONVERT(CHAR(10), dtDate, 120) + ' ' +
LTRIM(SUBSTRING(CONVERT(CHAR(22), CONVERT(DATETIME, dtTime), 22), 9, 14));
I would put this into a view so you don't have to repeat this calculation
everywhere.
If that still doesn't yield the correct results, then please post DDL and
sample data so we can actually try and reproduce your issue.
"scott" <sbailey@.mileslumber.com> wrote in message
news:eHuNqXAZGHA.3880@.TK2MSFTNGP04.phx.gbl...
> Your code returns just the date part like:
> 2006-03-14
> Can you modify it to display the date and time? Also, can you have the
> whole result converted to datetime format?
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:%23wruO%239YGHA.4688@.TK2MSFTNGP04.phx.gbl...
>|||thank you. it works.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ug$RJsAZGHA.1228@.TK2MSFTNGP02.phx.gbl...
>I was basing it on this:
> SELECT CONVERT(CHAR(10), GETDATE(), 120) + ' ' +
> LTRIM(SUBSTRING(CONVERT(CHAR(22), GETDATE(), 22), 9, 14));
> Which returns:
> --
> 2006-04-19 7:40:33 PM
> Maybe it will work better like this:
> SELECT CONVERT(CHAR(10), dtDate, 120) + ' ' +
> LTRIM(SUBSTRING(CONVERT(CHAR(22), CONVERT(DATETIME, dtTime), 22), 9, 14));
> I would put this into a view so you don't have to repeat this calculation
> everywhere.
> If that still doesn't yield the correct results, then please post DDL and
> sample data so we can actually try and reproduce your issue.
>
> "scott" <sbailey@.mileslumber.com> wrote in message
> news:eHuNqXAZGHA.3880@.TK2MSFTNGP04.phx.gbl...
>
Showing posts with label time. Show all posts
Showing posts with label time. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
Concantenating Dates
I am transferring data between DB's. One has DATE and TIME as two individual fields. I need to concantenate them into a single DATETIME field. I am sure the solution is simple but I can't figure it out.
Any advice?
Thanks!If you select from your time column, you should see that it's date is set to January 1st, 1900, like this:
1900-01-01 14:59:27.293
Your date column should show a date as of midnight like this:
2003-07-14 00:00:00.000
If this is the case, you can just add these value together to concatenate them:
select @.Yourdate + @.Yourtime
If this is not the case, you will need to concatenate them as formatted strings and then cast or convert the result to a datetime value.
blindman
Any advice?
Thanks!If you select from your time column, you should see that it's date is set to January 1st, 1900, like this:
1900-01-01 14:59:27.293
Your date column should show a date as of midnight like this:
2003-07-14 00:00:00.000
If this is the case, you can just add these value together to concatenate them:
select @.Yourdate + @.Yourtime
If this is not the case, you will need to concatenate them as formatted strings and then cast or convert the result to a datetime value.
blindman
Computing SUM on DATETIME datatype
hi everybody,
i'm trying to calculate the 'SUM' of time spent in hrs. n min. How can i do this using SQL Server?
What i mean is, i've a column 'TIME_SPENT' that has 'datetime' datatype. This column saves time spent for an activity in format 'hh:mm'. Suppose a user spends 45min for activity 'A' and say 1hr 25 min for activity 'B' then i want to calculate the 'SUM' of 'TIME_SPENT' for the user which should appear as 'Total time spent =2:10'
Can somebody pls help me with this?
Thnx in advance.create table #timetable (username varchar(50),timespend varchar(8))
insert into #timetable values ('joe','03:01')
insert into #timetable values ('joe','00:01')
insert into #timetable values ('foo','00:03')
insert into #timetable values ('foo','01:02')
select username,
convert(varchar(5),dateadd(second,sum(datediff(sec ond,'19000101','1900-01-01T'+timespend+':00')),'19000101'),8)
as t_timespend
from #timetable
group by username|||hey thnx for ur reply mallier,
ur code works great for the example u explained. But when i try to run it for my table in the database it gives following error:-
'The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.'
My code goes as follows:-
select empid,
convert(varchar(5),dateadd(second,sum(datediff(sec ond,'19000101','1900-01-01T'+time_spent+':00')),'19000101'),8) as 'Time spent'
from timesheet
where empid=9
group by empid
can u pls guide me on this?
thnx once again.|||try this select query on ur table (I hope data is in 'hh:mm' format)
create table #timetable (username varchar(50),timespend char(8))
insert into #timetable values ('joe','03:01')
insert into #timetable values ('joe','00:01')
insert into #timetable values ('foo','00:03')
insert into #timetable values ('foo','01:02')
select username,case when days>0 then cast(days*24+cast(left(times,2) as int)as varchar)+':'+right(times,2)
else
times
end as total_time
from
(
select username,
datediff(day,'19000101',dateadd(second,sum(datedif f(second,'19000101','1900-01-01T'+ltrim(rtrim(timespend))+':00')),'19000101'))
as days,
convert(varchar(5),dateadd(second,sum(datediff(sec ond,'19000101','1900-01-01T'+ltrim(rtrim(timespend))+':00')),'19000101'),8 ) as times
from #timetable
group by username
) as tm|||create table #babu ( names varchar(10),times varchar(10))
insert into #babu values ('babu0','01:20')
insert into #babu values ('babu1','01:20')
insert into #babu values ('babu1','01:20')
insert into #babu values ('babu0','01:20')
insert into #babu values ('babu0','01:20')
select names, dateadd(second,sum(datediff(second,'1900-01-01',convert(datetime,times))),'1900-01-01') from #babu group by names|||create table #babu ( names varchar(10),times varchar(10))
insert into #babu values ('babu0','01:20')
insert into #babu values ('babu1','01:20')
insert into #babu values ('babu1','01:20')
insert into #babu values ('babu0','01:20')
insert into #babu values ('babu0','01:20')
select names, dateadd(second,sum(datediff(second,'1900-01-01',convert(datetime,times))),'1900-01-01') from #babu group by namesthat wont help him,b'cos datatype of time column is char and he dont want see the date value too.
i'm trying to calculate the 'SUM' of time spent in hrs. n min. How can i do this using SQL Server?
What i mean is, i've a column 'TIME_SPENT' that has 'datetime' datatype. This column saves time spent for an activity in format 'hh:mm'. Suppose a user spends 45min for activity 'A' and say 1hr 25 min for activity 'B' then i want to calculate the 'SUM' of 'TIME_SPENT' for the user which should appear as 'Total time spent =2:10'
Can somebody pls help me with this?
Thnx in advance.create table #timetable (username varchar(50),timespend varchar(8))
insert into #timetable values ('joe','03:01')
insert into #timetable values ('joe','00:01')
insert into #timetable values ('foo','00:03')
insert into #timetable values ('foo','01:02')
select username,
convert(varchar(5),dateadd(second,sum(datediff(sec ond,'19000101','1900-01-01T'+timespend+':00')),'19000101'),8)
as t_timespend
from #timetable
group by username|||hey thnx for ur reply mallier,
ur code works great for the example u explained. But when i try to run it for my table in the database it gives following error:-
'The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.'
My code goes as follows:-
select empid,
convert(varchar(5),dateadd(second,sum(datediff(sec ond,'19000101','1900-01-01T'+time_spent+':00')),'19000101'),8) as 'Time spent'
from timesheet
where empid=9
group by empid
can u pls guide me on this?
thnx once again.|||try this select query on ur table (I hope data is in 'hh:mm' format)
create table #timetable (username varchar(50),timespend char(8))
insert into #timetable values ('joe','03:01')
insert into #timetable values ('joe','00:01')
insert into #timetable values ('foo','00:03')
insert into #timetable values ('foo','01:02')
select username,case when days>0 then cast(days*24+cast(left(times,2) as int)as varchar)+':'+right(times,2)
else
times
end as total_time
from
(
select username,
datediff(day,'19000101',dateadd(second,sum(datedif f(second,'19000101','1900-01-01T'+ltrim(rtrim(timespend))+':00')),'19000101'))
as days,
convert(varchar(5),dateadd(second,sum(datediff(sec ond,'19000101','1900-01-01T'+ltrim(rtrim(timespend))+':00')),'19000101'),8 ) as times
from #timetable
group by username
) as tm|||create table #babu ( names varchar(10),times varchar(10))
insert into #babu values ('babu0','01:20')
insert into #babu values ('babu1','01:20')
insert into #babu values ('babu1','01:20')
insert into #babu values ('babu0','01:20')
insert into #babu values ('babu0','01:20')
select names, dateadd(second,sum(datediff(second,'1900-01-01',convert(datetime,times))),'1900-01-01') from #babu group by names|||create table #babu ( names varchar(10),times varchar(10))
insert into #babu values ('babu0','01:20')
insert into #babu values ('babu1','01:20')
insert into #babu values ('babu1','01:20')
insert into #babu values ('babu0','01:20')
insert into #babu values ('babu0','01:20')
select names, dateadd(second,sum(datediff(second,'1900-01-01',convert(datetime,times))),'1900-01-01') from #babu group by namesthat wont help him,b'cos datatype of time column is char and he dont want see the date value too.
Monday, March 19, 2012
component taking long time to load
hello all
we have an applicaion developed in ASP with SQL server as back end.
Our Application consists of many compoents (typically treated as hyperlinks in the application). now, all the components are responding fine except one component taking long time to load. I have checked my Queries in the backend and they were fine..
what could be the problem ?
I am getting an error OLEDB DRIVER TIMEOUT EXPIRED when i click on the component link.
Plese suggest me some ways...
Thanks and Regards
SAIHave you tried increasing the timeout ? What are you clicking on - what type of application ?|||Originally posted by rnealejr
Have you tried increasing the timeout ? What are you clicking on - what type of application ?
Hello,
the Application is a ASP application, and i am clicking on one of the Hyper link out of several..
Except this link all the other links are working fine..
sai|||But what type of application/database are you trying to connect to ? Have you tried to increase the timeout ? What are the connection strings like for this application that is having a problem - is it hitting a different sql server ?
we have an applicaion developed in ASP with SQL server as back end.
Our Application consists of many compoents (typically treated as hyperlinks in the application). now, all the components are responding fine except one component taking long time to load. I have checked my Queries in the backend and they were fine..
what could be the problem ?
I am getting an error OLEDB DRIVER TIMEOUT EXPIRED when i click on the component link.
Plese suggest me some ways...
Thanks and Regards
SAIHave you tried increasing the timeout ? What are you clicking on - what type of application ?|||Originally posted by rnealejr
Have you tried increasing the timeout ? What are you clicking on - what type of application ?
Hello,
the Application is a ASP application, and i am clicking on one of the Hyper link out of several..
Except this link all the other links are working fine..
sai|||But what type of application/database are you trying to connect to ? Have you tried to increase the timeout ? What are the connection strings like for this application that is having a problem - is it hitting a different sql server ?
Saturday, February 25, 2012
Complex Query Help.. Please :)
Hello group,
First time post..
I've designed an online evaluation script (php/mssql) and need help either writing a query to get the posted data out in a way that is useable to those collecting the evaluations..
Here are the tables.
evaluations - holds a record for each evauation that is created: evalID, evalName
fields - holds a record for each potential field that could be added to the evaluation: fieldID, fieldTitle, fieldDescription, and fieldTypeID (types are in a seperate table)
fields_evals - contains the link between the evaluations table and the fields table. this one essentially creates teh evaluation from the eval name and the list of fields. it also contains some switches that specify how the data should be reported (i.e. averaged and/or grouped by) and values that determine if the fields are required and what position they are to be listed in the evaluation form.. (hope that made sense) : evalID, FieldID, position, required, groupby, average
fields_custom - one of the field types is a custom field where the user can create a field with a list of options (i.e. Jan, Feb, Mar, April... or whatever they like) : optionID, optionText, fieldID
submitted - holds data for each individual submitted eval..: subID, timestamp, and evalID
eval_data - this is the biggie.. this is the table that all the responses are written to. it has these fields... data_ID, subID, fieldID, data
Here's the problem..
I want the report page to be able to summraize the data by grouping by the data set in the eval_fields table.. (a row for each value submitted) and on that row have all of the averaged fields (again indicated from the eval_fields table) listed with their averages.. all of the data will come from the eval_data table.. possibly including (and i think this may be the main problem) an id value that relates to the optionID in teh custom_fields table.
an example of a posted evaluation will create:
1 row in the submitted table..
and a row in the eval_data table for each field submitted..as well as
with any luck this explanation makes sense and the problem will be easy to solve..
:rolleyes:
any replys are greatly appreciated..
Thanks
Willok.. here is some clarification..
I started thinking about the first post and decided that much of that information was probably not needed..
Here is the sql i'm workin with so far..
SELECT AVG(CAST(d.chData AS float)) AS average , f.chEvalFieldText
FROM eval_eval_data AS d
INNER JOIN fields_evaluation as fe ON fe.intField_ID = d.intField_ID
INNER JOIN fields as f ON f.intField_ID = d.intField_ID
INNER JOIN fields_custom as c ON f.intField_ID = c.intFieldID
WHERE fe.intAverage = '1'
AND c.intOption_ID = '26'
Group By f.chEvalFieldText
my question boils down to this..
regarding the rows in the data table..
can i group by a value in a liked table (f),
and average values in the data table
while limiting by values in teh data table as well.
anybody?
Am i on the wrong track?
Am i totally lost?
have i lost everybody else??
thanks
ws
-|||You are right on track, good job.
:D|||me again...
I'm fairly confident that there is a query that will get me what i need.. but i'm having a helluva time getting it to work..
here is some example data from the data table..
intSub intField_ID chData
1 1 1
1 2 1
1 3 4
1 4 2
1 26 27
2 1 1
2 2 2
2 3 3
2 4 5
2 26 28
intField_ID is a key that relates to fields in the evaluation
chData is the value that was submitted and intSubmission indicates which submission the data came from.
In this case the field_ids 1-4 ratings 1- 5 that will be averaged
the average will be grouped by the field ID no biggie.. here's where i'm stumped.
I Also have to average and group those values when the fieldID 26 (which is a field that was created within the application) equals a certian value.
make sense?
there is also a table that connects the fields to the evaluation and indicates which fields will be averaged..
for the data above .. i need to
Average the values for the indicated fields grouped by the field id..
but only where the value for fieldID = 26 are equal..
any help is greatly appreciated.
If you need more info.. please let me know..
thanks
will
First time post..
I've designed an online evaluation script (php/mssql) and need help either writing a query to get the posted data out in a way that is useable to those collecting the evaluations..
Here are the tables.
evaluations - holds a record for each evauation that is created: evalID, evalName
fields - holds a record for each potential field that could be added to the evaluation: fieldID, fieldTitle, fieldDescription, and fieldTypeID (types are in a seperate table)
fields_evals - contains the link between the evaluations table and the fields table. this one essentially creates teh evaluation from the eval name and the list of fields. it also contains some switches that specify how the data should be reported (i.e. averaged and/or grouped by) and values that determine if the fields are required and what position they are to be listed in the evaluation form.. (hope that made sense) : evalID, FieldID, position, required, groupby, average
fields_custom - one of the field types is a custom field where the user can create a field with a list of options (i.e. Jan, Feb, Mar, April... or whatever they like) : optionID, optionText, fieldID
submitted - holds data for each individual submitted eval..: subID, timestamp, and evalID
eval_data - this is the biggie.. this is the table that all the responses are written to. it has these fields... data_ID, subID, fieldID, data
Here's the problem..
I want the report page to be able to summraize the data by grouping by the data set in the eval_fields table.. (a row for each value submitted) and on that row have all of the averaged fields (again indicated from the eval_fields table) listed with their averages.. all of the data will come from the eval_data table.. possibly including (and i think this may be the main problem) an id value that relates to the optionID in teh custom_fields table.
an example of a posted evaluation will create:
1 row in the submitted table..
and a row in the eval_data table for each field submitted..as well as
with any luck this explanation makes sense and the problem will be easy to solve..
:rolleyes:
any replys are greatly appreciated..
Thanks
Willok.. here is some clarification..
I started thinking about the first post and decided that much of that information was probably not needed..
Here is the sql i'm workin with so far..
SELECT AVG(CAST(d.chData AS float)) AS average , f.chEvalFieldText
FROM eval_eval_data AS d
INNER JOIN fields_evaluation as fe ON fe.intField_ID = d.intField_ID
INNER JOIN fields as f ON f.intField_ID = d.intField_ID
INNER JOIN fields_custom as c ON f.intField_ID = c.intFieldID
WHERE fe.intAverage = '1'
AND c.intOption_ID = '26'
Group By f.chEvalFieldText
my question boils down to this..
regarding the rows in the data table..
can i group by a value in a liked table (f),
and average values in the data table
while limiting by values in teh data table as well.
anybody?
Am i on the wrong track?
Am i totally lost?
have i lost everybody else??
thanks
ws
-|||You are right on track, good job.
:D|||me again...
I'm fairly confident that there is a query that will get me what i need.. but i'm having a helluva time getting it to work..
here is some example data from the data table..
intSub intField_ID chData
1 1 1
1 2 1
1 3 4
1 4 2
1 26 27
2 1 1
2 2 2
2 3 3
2 4 5
2 26 28
intField_ID is a key that relates to fields in the evaluation
chData is the value that was submitted and intSubmission indicates which submission the data came from.
In this case the field_ids 1-4 ratings 1- 5 that will be averaged
the average will be grouped by the field ID no biggie.. here's where i'm stumped.
I Also have to average and group those values when the fieldID 26 (which is a field that was created within the application) equals a certian value.
make sense?
there is also a table that connects the fields to the evaluation and indicates which fields will be averaged..
for the data above .. i need to
Average the values for the indicated fields grouped by the field id..
but only where the value for fieldID = 26 are equal..
any help is greatly appreciated.
If you need more info.. please let me know..
thanks
will
Complex query
Hi everybody,
I have a table storing the quiz data of the students like this:
Date | Time | Name | Grade
I can show the data of specific student by the query below:
SELECT *
FROM tbl_quiz
WHERE name ='xxx'
Suppose I have 10 records for each student, and I wanna display the
data like this:
Previous quiz date | Previous quiz time | Date | Time | Name | Grade
How can I make the "Previous quiz date" and "Previous quiz time"? How
can I query it? Please advice
FRANK?? (lokalun@.gmail.com) writes:
> I have a table storing the quiz data of the students like this:
> Date | Time | Name | Grade
> I can show the data of specific student by the query below:
> SELECT *
> FROM tbl_quiz
> WHERE name ='xxx'
> Suppose I have 10 records for each student, and I wanna display the
> data like this:
> Previous quiz date | Previous quiz time | Date | Time | Name | Grade
> How can I make the "Previous quiz date" and "Previous quiz time"? How
> can I query it? Please advice
Is that date as 2006-04-02 and time as 14:30:21, or does any of them
include the other? Normally you store datetime values in one column in
SQL Server as there are no pure date and time data types. To make it
simpler, I'm assuming that time includes both date and time.
SELECT predate = c.date, prevtime = c.time,
a.date, a.time, a.name, a.grade
FROM tbl_quiz a
JOIN (SELECT time = MAX(time), name
FROM tbl_quiz
GROUP BY name) AS b ON a.name = b.name
JOIM tbl_quiz c ON c.name = b.name
AND c.time = (SELECT MAX(d.time)
FROM tbl_quiz d
WHERE d.name = b.name
AND d.time < b.time)
WHERE a.name = 'xxx'
--
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|||Sommarskog, thanks for your posting. I may not clearly describe my
situation. I want to show the previous quiz result with the current
quiz detail. If I show the 1st quiz result, there will be no previous
quiz date and time; If I show the 2nd quiz result, I can show 1st quiz
date and 1st quiz time as well, and so on. Is your solution still works
in this situation?|||It will be much better if u can give me some idea if I would like to do
this:
Previous quiz date | Previous quiz grade | Date | Time | Name | Grade |
Next quiz date | Next quiz grade
That is, if I show the 4th quiz detail, it will display the 3rd quiz
date and grade and 5th quiz date and grade as well. Is that impossible?
Please let me know if u want more information. Many thanks.
FRANK|||?? (lokalun@.gmail.com) writes:
> It will be much better if u can give me some idea if I would like to do
> this:
> Previous quiz date | Previous quiz grade | Date | Time | Name | Grade |
> Next quiz date | Next quiz grade
> That is, if I show the 4th quiz detail, it will display the 3rd quiz
> date and grade and 5th quiz date and grade as well. Is that impossible?
> Please let me know if u want more information. Many thanks.
It is at this point that I will ask to provide:
o CREATE TABLE statements for your table.
o INSERT statemetns with sample data.
o The desired data given the sample.
This makes it easy to copy-and-paste test data into a query tool, and
develop a tested solution.
Moreover, if you don't give us a clear specification of what you want,
you cannot not get what you want.
--
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|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
You also do not know that rows are not records, what reserved words are
or how to name data elements. In T-SQL, there is s DATETIME data type
which is like the Standard SQL's TIMESTAMP.
CREATE TABLE QuizScores
(student_name CHAR(20) NOT NULL,
quiz_date DATETIME NOT NULL,
quiz_grade INTEGER NOT NULL,
PRIMARY KEY (student_name, quiz_date));
We already posted several verisions of this problem. Look for a posting
about the 2nd edition of my Puzzles book.
Sunday, February 19, 2012
Complete newbie to replication, have a question
We have had a server in LocationA for quite some time. The need arose
recently to have a server setup in LocationB due to some bandwidth issues.
LocationB was given a snapshot of the DB @. LocationA when it was first
setup. However, both machines will need to have the same data as both are
used in a production environment. I will probably want to do this nightly.
I have read a lot of posts and webistes, but still am semi in the dark.
About the only thing i've figured out (i think) is that I will need to do
some sort of merge replicaiton.
What do I need to do to get started, a link to a HOW TO for a smiliar setup
would be great. I have read a bit and some mention having a 3rd server, do
i need a 3rd server as the final merged server? I guess more importantly is
a 3rd server an optimal solution?
Any help would be greatly appreciated.
Forgot to mention, I am running SQL Server 2000 on both servers.
"Lucas Graf" <lgraf2000@.comcast.net> wrote in message
news:OjZ0E0bQGHA.5296@.TK2MSFTNGP09.phx.gbl...
> We have had a server in LocationA for quite some time. The need arose
> recently to have a server setup in LocationB due to some bandwidth issues.
> LocationB was given a snapshot of the DB @. LocationA when it was first
> setup. However, both machines will need to have the same data as both are
> used in a production environment. I will probably want to do this
> nightly.
> I have read a lot of posts and webistes, but still am semi in the dark.
> About the only thing i've figured out (i think) is that I will need to do
> some sort of merge replicaiton.
> What do I need to do to get started, a link to a HOW TO for a smiliar
> setup would be great. I have read a bit and some mention having a 3rd
> server, do i need a 3rd server as the final merged server? I guess more
> importantly is a 3rd server an optimal solution?
> Any help would be greatly appreciated.
>
|||In merge replication you will have Distributor, Publisher and Subscriber.
They are different roles for a replication setup but they can be on the same
physical machine. I think the 3rd server you mentioned is distributor. You
can setup the distributor on the same server with the publisher, so you
don't need a third machine.
Yi Chen
"Lucas Graf" <lgraf2000@.comcast.net> wrote in message
news:OjZ0E0bQGHA.5296@.TK2MSFTNGP09.phx.gbl...
> We have had a server in LocationA for quite some time. The need arose
> recently to have a server setup in LocationB due to some bandwidth issues.
> LocationB was given a snapshot of the DB @. LocationA when it was first
> setup. However, both machines will need to have the same data as both are
> used in a production environment. I will probably want to do this
> nightly.
> I have read a lot of posts and webistes, but still am semi in the dark.
> About the only thing i've figured out (i think) is that I will need to do
> some sort of merge replicaiton.
> What do I need to do to get started, a link to a HOW TO for a smiliar
> setup would be great. I have read a bit and some mention having a 3rd
> server, do i need a 3rd server as the final merged server? I guess more
> importantly is a 3rd server an optimal solution?
> Any help would be greatly appreciated.
>
|||Ok. That makes sense.
I have been messing around trying to set it up today, and am still lost.
Currently the 2 servers are out of sync. Both have data the other doesn't.
What do I need to do to get them both to have the same data now, and then
keep them replciating from here on out?
"Yi Chen [MSFT]" <yiche@.online.microsoft.com> wrote in message
news:%23o7jSucQGHA.1204@.TK2MSFTNGP12.phx.gbl...
> In merge replication you will have Distributor, Publisher and Subscriber.
> They are different roles for a replication setup but they can be on the
> same physical machine. I think the 3rd server you mentioned is
> distributor. You can setup the distributor on the same server with the
> publisher, so you don't need a third machine.
> Yi Chen
> "Lucas Graf" <lgraf2000@.comcast.net> wrote in message
> news:OjZ0E0bQGHA.5296@.TK2MSFTNGP09.phx.gbl...
>
|||Lucas Graf wrote:
> We have had a server in LocationA for quite some time. The need arose
> recently to have a server setup in LocationB due to some bandwidth issues.
> LocationB was given a snapshot of the DB @. LocationA when it was first
> setup. However, both machines will need to have the same data as both are
> used in a production environment. I will probably want to do this nightly.
> I have read a lot of posts and webistes, but still am semi in the dark.
> About the only thing i've figured out (i think) is that I will need to do
> some sort of merge replicaiton.
> What do I need to do to get started, a link to a HOW TO for a smiliar setup
> would be great. I have read a bit and some mention having a 3rd server, do
> i need a 3rd server as the final merged server? I guess more importantly is
> a 3rd server an optimal solution?
> Any help would be greatly appreciated.
|||Lucas,
presumably you have initialized the subscriber and are using merge
replication? If so, you just need to synchronize the subscriber to have the
data merged. You'll need to consider who should win conflicts (publisher by
default) and if conflicts should be allowed (filtering).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Ok.
I got this far and was tentative on going further. I will keep trucking and
see what else may come up that causes issues. Thanks for the help so far, I
hope to report back a positive experience.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23ogHO4uQGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Lucas,
> presumably you have initialized the subscriber and are using merge
> replication? If so, you just need to synchronize the subscriber to have
> the data merged. You'll need to consider who should win conflicts
> (publisher by default) and if conflicts should be allowed (filtering).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||I guess I am still confused w/the whole publisher/distributor thing.
Since I want both servers to merge both of their data are they both set to
publishers and distributors? Or is only 1 a publisher and both distributors
and subscribers?
So confused..
"Lucas Graf" <lgraf2000@.comcast.net> wrote in message
news:Ob$pMuzQGHA.2436@.TK2MSFTNGP11.phx.gbl...
> Ok.
> I got this far and was tentative on going further. I will keep trucking
> and see what else may come up that causes issues. Thanks for the help so
> far, I hope to report back a positive experience.
>
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:%23ogHO4uQGHA.2628@.TK2MSFTNGP15.phx.gbl...
>
|||Ok.
ServerA
I have set him to a Distributor, Publisher and set the Database i want to
publish to the other server as the "push" datatabase. All seems good on
ServerA.
ServerB
Not sure what to do here so its data gets pushed to ServerA to be merged
there as well. Everything I try i end up getting the error "You cannot
create a merge replicaion in database xxxc. The database contains one or
more merge subscritptions that are anonymous or that use the Priority of the
Publisher when resolving conflicts."
"Lucas Graf" <lgraf@.nvidia.com> wrote in message
news:uSQuaI8QGHA.5552@.TK2MSFTNGP14.phx.gbl...
>I guess I am still confused w/the whole publisher/distributor thing.
> Since I want both servers to merge both of their data are they both set to
> publishers and distributors? Or is only 1 a publisher and both
> distributors and subscribers?
> So confused..
>
> "Lucas Graf" <lgraf2000@.comcast.net> wrote in message
> news:Ob$pMuzQGHA.2436@.TK2MSFTNGP11.phx.gbl...
>
|||Maybe Bidirectional Transactional Replication is more what I am looking for?
"Lucas Graf" <lgraf@.nvidia.com> wrote in message
news:uSQuaI8QGHA.5552@.TK2MSFTNGP14.phx.gbl...
>I guess I am still confused w/the whole publisher/distributor thing.
> Since I want both servers to merge both of their data are they both set to
> publishers and distributors? Or is only 1 a publisher and both
> distributors and subscribers?
> So confused..
>
> "Lucas Graf" <lgraf2000@.comcast.net> wrote in message
> news:Ob$pMuzQGHA.2436@.TK2MSFTNGP11.phx.gbl...
>
recently to have a server setup in LocationB due to some bandwidth issues.
LocationB was given a snapshot of the DB @. LocationA when it was first
setup. However, both machines will need to have the same data as both are
used in a production environment. I will probably want to do this nightly.
I have read a lot of posts and webistes, but still am semi in the dark.
About the only thing i've figured out (i think) is that I will need to do
some sort of merge replicaiton.
What do I need to do to get started, a link to a HOW TO for a smiliar setup
would be great. I have read a bit and some mention having a 3rd server, do
i need a 3rd server as the final merged server? I guess more importantly is
a 3rd server an optimal solution?
Any help would be greatly appreciated.
Forgot to mention, I am running SQL Server 2000 on both servers.
"Lucas Graf" <lgraf2000@.comcast.net> wrote in message
news:OjZ0E0bQGHA.5296@.TK2MSFTNGP09.phx.gbl...
> We have had a server in LocationA for quite some time. The need arose
> recently to have a server setup in LocationB due to some bandwidth issues.
> LocationB was given a snapshot of the DB @. LocationA when it was first
> setup. However, both machines will need to have the same data as both are
> used in a production environment. I will probably want to do this
> nightly.
> I have read a lot of posts and webistes, but still am semi in the dark.
> About the only thing i've figured out (i think) is that I will need to do
> some sort of merge replicaiton.
> What do I need to do to get started, a link to a HOW TO for a smiliar
> setup would be great. I have read a bit and some mention having a 3rd
> server, do i need a 3rd server as the final merged server? I guess more
> importantly is a 3rd server an optimal solution?
> Any help would be greatly appreciated.
>
|||In merge replication you will have Distributor, Publisher and Subscriber.
They are different roles for a replication setup but they can be on the same
physical machine. I think the 3rd server you mentioned is distributor. You
can setup the distributor on the same server with the publisher, so you
don't need a third machine.
Yi Chen
"Lucas Graf" <lgraf2000@.comcast.net> wrote in message
news:OjZ0E0bQGHA.5296@.TK2MSFTNGP09.phx.gbl...
> We have had a server in LocationA for quite some time. The need arose
> recently to have a server setup in LocationB due to some bandwidth issues.
> LocationB was given a snapshot of the DB @. LocationA when it was first
> setup. However, both machines will need to have the same data as both are
> used in a production environment. I will probably want to do this
> nightly.
> I have read a lot of posts and webistes, but still am semi in the dark.
> About the only thing i've figured out (i think) is that I will need to do
> some sort of merge replicaiton.
> What do I need to do to get started, a link to a HOW TO for a smiliar
> setup would be great. I have read a bit and some mention having a 3rd
> server, do i need a 3rd server as the final merged server? I guess more
> importantly is a 3rd server an optimal solution?
> Any help would be greatly appreciated.
>
|||Ok. That makes sense.
I have been messing around trying to set it up today, and am still lost.
Currently the 2 servers are out of sync. Both have data the other doesn't.
What do I need to do to get them both to have the same data now, and then
keep them replciating from here on out?
"Yi Chen [MSFT]" <yiche@.online.microsoft.com> wrote in message
news:%23o7jSucQGHA.1204@.TK2MSFTNGP12.phx.gbl...
> In merge replication you will have Distributor, Publisher and Subscriber.
> They are different roles for a replication setup but they can be on the
> same physical machine. I think the 3rd server you mentioned is
> distributor. You can setup the distributor on the same server with the
> publisher, so you don't need a third machine.
> Yi Chen
> "Lucas Graf" <lgraf2000@.comcast.net> wrote in message
> news:OjZ0E0bQGHA.5296@.TK2MSFTNGP09.phx.gbl...
>
|||Lucas Graf wrote:
> We have had a server in LocationA for quite some time. The need arose
> recently to have a server setup in LocationB due to some bandwidth issues.
> LocationB was given a snapshot of the DB @. LocationA when it was first
> setup. However, both machines will need to have the same data as both are
> used in a production environment. I will probably want to do this nightly.
> I have read a lot of posts and webistes, but still am semi in the dark.
> About the only thing i've figured out (i think) is that I will need to do
> some sort of merge replicaiton.
> What do I need to do to get started, a link to a HOW TO for a smiliar setup
> would be great. I have read a bit and some mention having a 3rd server, do
> i need a 3rd server as the final merged server? I guess more importantly is
> a 3rd server an optimal solution?
> Any help would be greatly appreciated.
|||Lucas,
presumably you have initialized the subscriber and are using merge
replication? If so, you just need to synchronize the subscriber to have the
data merged. You'll need to consider who should win conflicts (publisher by
default) and if conflicts should be allowed (filtering).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Ok.
I got this far and was tentative on going further. I will keep trucking and
see what else may come up that causes issues. Thanks for the help so far, I
hope to report back a positive experience.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23ogHO4uQGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Lucas,
> presumably you have initialized the subscriber and are using merge
> replication? If so, you just need to synchronize the subscriber to have
> the data merged. You'll need to consider who should win conflicts
> (publisher by default) and if conflicts should be allowed (filtering).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||I guess I am still confused w/the whole publisher/distributor thing.
Since I want both servers to merge both of their data are they both set to
publishers and distributors? Or is only 1 a publisher and both distributors
and subscribers?
So confused..
"Lucas Graf" <lgraf2000@.comcast.net> wrote in message
news:Ob$pMuzQGHA.2436@.TK2MSFTNGP11.phx.gbl...
> Ok.
> I got this far and was tentative on going further. I will keep trucking
> and see what else may come up that causes issues. Thanks for the help so
> far, I hope to report back a positive experience.
>
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:%23ogHO4uQGHA.2628@.TK2MSFTNGP15.phx.gbl...
>
|||Ok.
ServerA
I have set him to a Distributor, Publisher and set the Database i want to
publish to the other server as the "push" datatabase. All seems good on
ServerA.
ServerB
Not sure what to do here so its data gets pushed to ServerA to be merged
there as well. Everything I try i end up getting the error "You cannot
create a merge replicaion in database xxxc. The database contains one or
more merge subscritptions that are anonymous or that use the Priority of the
Publisher when resolving conflicts."
"Lucas Graf" <lgraf@.nvidia.com> wrote in message
news:uSQuaI8QGHA.5552@.TK2MSFTNGP14.phx.gbl...
>I guess I am still confused w/the whole publisher/distributor thing.
> Since I want both servers to merge both of their data are they both set to
> publishers and distributors? Or is only 1 a publisher and both
> distributors and subscribers?
> So confused..
>
> "Lucas Graf" <lgraf2000@.comcast.net> wrote in message
> news:Ob$pMuzQGHA.2436@.TK2MSFTNGP11.phx.gbl...
>
|||Maybe Bidirectional Transactional Replication is more what I am looking for?
"Lucas Graf" <lgraf@.nvidia.com> wrote in message
news:uSQuaI8QGHA.5552@.TK2MSFTNGP14.phx.gbl...
>I guess I am still confused w/the whole publisher/distributor thing.
> Since I want both servers to merge both of their data are they both set to
> publishers and distributors? Or is only 1 a publisher and both
> distributors and subscribers?
> So confused..
>
> "Lucas Graf" <lgraf2000@.comcast.net> wrote in message
> news:Ob$pMuzQGHA.2436@.TK2MSFTNGP11.phx.gbl...
>
compiling objects to multiple databases
Anyone have any suggestions they could share on compiling objects to multipl
e
databases at the same time on the same server and also to multiple servers?
Example:
Stored procedure needs to be compiled on 60 databases on the same server.
Thanks for any help!Assuming the name of the SP on all 60 databases on the server are the same:
sp_MSforeachdb 'USE ? IF EXISTS (SELECT * FROM sysobjects WHERE name =
''sp_name'' AND xtype = ''U'') EXEC sp_recompile ''sp_name'''
"BL" wrote:
> Anyone have any suggestions they could share on compiling objects to multi
ple
> databases at the same time on the same server and also to multiple servers
?
> Example:
> Stored procedure needs to be compiled on 60 databases on the same server.
> Thanks for any help!|||Jack - how would I go about getting them compiled in the database when they
currently exist in a .txt file?
"BL" wrote:
> Anyone have any suggestions they could share on compiling objects to multi
ple
> databases at the same time on the same server and also to multiple servers
?
> Example:
> Stored procedure needs to be compiled on 60 databases on the same server.
> Thanks for any help!|||I would write an application using ADO.NET which reads the text files and cr
eate the procedures on
each server/database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BL" <BL@.discussions.microsoft.com> wrote in message
news:87375008-C1A7-4009-835E-095F70427CE2@.microsoft.com...
> Jack - how would I go about getting them compiled in the database when the
y
> currently exist in a .txt file?
> "BL" wrote:
>
e
databases at the same time on the same server and also to multiple servers?
Example:
Stored procedure needs to be compiled on 60 databases on the same server.
Thanks for any help!Assuming the name of the SP on all 60 databases on the server are the same:
sp_MSforeachdb 'USE ? IF EXISTS (SELECT * FROM sysobjects WHERE name =
''sp_name'' AND xtype = ''U'') EXEC sp_recompile ''sp_name'''
"BL" wrote:
> Anyone have any suggestions they could share on compiling objects to multi
ple
> databases at the same time on the same server and also to multiple servers
?
> Example:
> Stored procedure needs to be compiled on 60 databases on the same server.
> Thanks for any help!|||Jack - how would I go about getting them compiled in the database when they
currently exist in a .txt file?
"BL" wrote:
> Anyone have any suggestions they could share on compiling objects to multi
ple
> databases at the same time on the same server and also to multiple servers
?
> Example:
> Stored procedure needs to be compiled on 60 databases on the same server.
> Thanks for any help!|||I would write an application using ADO.NET which reads the text files and cr
eate the procedures on
each server/database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"BL" <BL@.discussions.microsoft.com> wrote in message
news:87375008-C1A7-4009-835E-095F70427CE2@.microsoft.com...
> Jack - how would I go about getting them compiled in the database when the
y
> currently exist in a .txt file?
> "BL" wrote:
>
Compile store procedures
I'm having a excessive amount of lock timeout and I discover that every time
when users execute store procedures, these procedures are compile before
execution. Anyone has an idea about this behaviour ?
thank you for your help...Check this link:
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q243/5/86.ASP&NoWebContent=1
--
HTH,
SriSamp
Please reply to the whole group only!
"Tony" <news@.hotmail.com> wrote in message
news:%23l0Kxh8PDHA.2424@.tk2msftngp13.phx.gbl...
> I'm having a excessive amount of lock timeout and I discover that every
time
> when users execute store procedures, these procedures are compile before
> execution. Anyone has an idea about this behaviour ?
> thank you for your help...
>
when users execute store procedures, these procedures are compile before
execution. Anyone has an idea about this behaviour ?
thank you for your help...Check this link:
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q243/5/86.ASP&NoWebContent=1
--
HTH,
SriSamp
Please reply to the whole group only!
"Tony" <news@.hotmail.com> wrote in message
news:%23l0Kxh8PDHA.2424@.tk2msftngp13.phx.gbl...
> I'm having a excessive amount of lock timeout and I discover that every
time
> when users execute store procedures, these procedures are compile before
> execution. Anyone has an idea about this behaviour ?
> thank you for your help...
>
Friday, February 17, 2012
compile a store proc again
Hi there!!
Can we force stored procs in a database to recompile and see if there is any compile time error in the stored procs then and there.
Something like sp_recompile which marks Stored Proc for recompilation and compilation occurs later on.
I am looking for something which will force the existing stored procs to compile and let me see the error if any.
Best Regards
Rahul Kumar, Software Engineer, India
SP_RECOMPILE is helpful to recompile the next time they are run.
It will remove all the chache Plan for the current object when you run the SP it will compile it.
You can do the following step to get the compile errors...
Exec Sp_recompile 'MySp'
Exec @.R = MySp
If @.R = 0
Print 'Success'
Else
Print 'Failed on Compile'
But the drawback here is you can't find the error is occured by compiler or runtime execution.
|||
This can be done for one stored proc, but we cant have this approach if we want to recompile all the strored procs in the database- as they may be having different parameters.
|||You can try calling DBCC FREEPROCCACHE
This will flush the procedure cache and the cached plans. Next time you execute the procedures, they will compile and you should be able to hit the compilation error.
|||It all depends on what you mean by "recompile". I know that I was really keen that the meant what it sounded like, which would be to take the source code, build an executable module, then a plan, and get things ready.
When you run sp_recompile, all it does is bump an internal value that is used to tell objects to recompile because something has changed. It will not take existing source code and rebuild the executable.
What is the purpose you are trying to achieve? Find where structures have changed? This is something you need to do with source control. Either by having documentation you can search (using a tool, or just manually searching) or just rebuild your project from scratch. Even recompiling is not foolproof unfortunately because of delayed name resolution, meaning that, in a procedure, if it comes to a table name that does not exist, it assumes that you know what you are doing and that the object will be created later. Man, I hate delayed name resolution, except when I need it (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124490)
|||Okay let me be bit specific what I am trying to achieve.--will also explain what i mean by "recompile"
I have a database in Sql Server 2000 and I am trying to copy it on Sql server 2005.Now we have many changes like sort of =* join which we have to do in stored procs.So I am trying to get list of stored procs which wont compile successfully on sql server 2005.
One way to get this list is with SQL Server 2005 Ugrade Advisor, I am surprise but it didnt give me an exhaustive list.
So I thought I would be better if I could recompile all the stored procs, and that what i am trying to achieve.
Best Regards
Rahul
|||For that you would need to script out the stored procedures, and try to compile them. As long as object names are still the same (mitigating the whole question of delayed name resolution) then that will work.
If you know the pattern you are looking for, like =* or *=, you could use a search to find these cases:
DECLARE @.value nvarchar(128)
SET @.value = '=*'
SELECT cast(schema_name(schema_id) + '.' + name AS varchar(60)) AS name,
cast(type_desc AS varchar(20)) AS type , create_date,
modify_date,
char(13) + char(10)
+ '--select object_definition(' + cast(object_id as varchar(10)) + ') as [' + name + ']'
FROM sys.objects
WHERE charindex(@.value,replace(replace(object_definition(object_id),'[',''),']','')) > 0
--http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1139.entry
Object_definition is a new function in 2005 that gives you the text of the object in a nice package.
But, no, there is no way to have this kind of syntax check done automatically for you if the Upgrade Advisor misses it.
|||
Thanks Louis
this '=*' was just one issue I cited for example, there are and can (which i dont know) many more, which give error on sql server 2005 and even SQL Server 2005 Ugrade Advisor didnt catch.
Can we rewrite your given code as
select object_name(id) from syscomments where text like '%=*%'
@. Louis -- But, no, there is no way to have this kind of syntax check done automatically for you if the Upgrade Advisor misses it.
Does this mean that we can not identify affected stored proc if Upgrade Advisor misses them, expect by executing them.
Regards
Rahul
|||Install the SQL Server Best Practices Analyzer and run it against your SQL Server 2000 database. This has a rule to check for the older-style outer joins.|||
Yeah, anything that the program Umachandar mentions or the upgrade advisor miss will not be found. I was just giving an alternative for searching through your code if you find things you need to.
And no, syscomments cannot be relied upon for a precise search, since it is chunked into 4000 character chunks. Object_definition returns the full text as a varchar(max) that you can use the like on.
My experience iwth the Upgrade Advisor was pretty pleasant. We had very little trouble taking our 2000 databases from 2005, but then again, we try to keep up and usually make those kind of changes ahead of time (not that that helps you :)
|||Just to site one example which made me wondering--
Here is a line of code from my stored proc
tsequal(TmStp, convert(varbinary(8), convert(bigint, @.xTmStp)))
Now the irony is my stored proc is got compiled in sql server 2000 and when i moved it to sql server 2005 and compiled it ther it gave me following error:-
Msg 102, Level 15, State 1, Procedure rsp_UpdPolSchdTmStp, Line 43
Incorrect syntax near 'TSEQUAL'.
Regards
Rahul Kumar
|||Yep, TSEQUAL is gone for good in 2005 (http://sqljunkies.com/Forums/ShowPost.aspx?PostID=2534) but it wasn't documented for quite a while.
It isn't necessary, so you can change:
tsequal(TmStp, convert(varbinary(8), convert(bigint, @.xTmStp)))
to
TmStp = convert(varbinary(8), convert(bigint, @.xTmStp)))
though I am not sure why you are doing all of the converting. if @.xTmStp is of varbinary(8) type (or rowversion/timestamp type) you can just do:
TmStp = @.xTmStp
compile a store proc again
Hi there!!
Can we force stored procs in a database to recompile and see if there is any compile time error in the stored procs then and there.
Something like sp_recompile which marks Stored Proc for recompilation and compilation occurs later on.
I am looking for something which will force the existing stored procs to compile and let me see the error if any.
Best Regards
Rahul Kumar, Software Engineer, India
SP_RECOMPILE is helpful to recompile the next time they are run.
It will remove all the chache Plan for the current object when you run the SP it will compile it.
You can do the following step to get the compile errors...
Exec Sp_recompile 'MySp'
Exec @.R = MySp
If @.R = 0
Print 'Success'
Else
Print 'Failed on Compile'
But the drawback here is you can't find the error is occured by compiler or runtime execution.
|||
This can be done for one stored proc, but we cant have this approach if we want to recompile all the strored procs in the database- as they may be having different parameters.
|||You can try calling DBCC FREEPROCCACHE
This will flush the procedure cache and the cached plans. Next time you execute the procedures, they will compile and you should be able to hit the compilation error.
|||It all depends on what you mean by "recompile". I know that I was really keen that the meant what it sounded like, which would be to take the source code, build an executable module, then a plan, and get things ready.
When you run sp_recompile, all it does is bump an internal value that is used to tell objects to recompile because something has changed. It will not take existing source code and rebuild the executable.
What is the purpose you are trying to achieve? Find where structures have changed? This is something you need to do with source control. Either by having documentation you can search (using a tool, or just manually searching) or just rebuild your project from scratch. Even recompiling is not foolproof unfortunately because of delayed name resolution, meaning that, in a procedure, if it comes to a table name that does not exist, it assumes that you know what you are doing and that the object will be created later. Man, I hate delayed name resolution, except when I need it (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124490)
|||Okay let me be bit specific what I am trying to achieve.--will also explain what i mean by "recompile"
I have a database in Sql Server 2000 and I am trying to copy it on Sql server 2005.Now we have many changes like sort of =* join which we have to do in stored procs.So I am trying to get list of stored procs which wont compile successfully on sql server 2005.
One way to get this list is with SQL Server 2005 Ugrade Advisor, I am surprise but it didnt give me an exhaustive list.
So I thought I would be better if I could recompile all the stored procs, and that what i am trying to achieve.
Best Regards
Rahul
|||For that you would need to script out the stored procedures, and try to compile them. As long as object names are still the same (mitigating the whole question of delayed name resolution) then that will work.
If you know the pattern you are looking for, like =* or *=, you could use a search to find these cases:
DECLARE @.value nvarchar(128)
SET @.value = '=*'
SELECT cast(schema_name(schema_id) + '.' + name AS varchar(60)) AS name,
cast(type_desc AS varchar(20)) AS type , create_date,
modify_date,
char(13) + char(10)
+ '--select object_definition(' + cast(object_id as varchar(10)) + ') as [' + name + ']'
FROM sys.objects
WHERE charindex(@.value,replace(replace(object_definition(object_id),'[',''),']','')) > 0
--http://drsql.spaces.live.com/blog/cns!80677FB08B3162E4!1139.entry
Object_definition is a new function in 2005 that gives you the text of the object in a nice package.
But, no, there is no way to have this kind of syntax check done automatically for you if the Upgrade Advisor misses it.
|||
Thanks Louis
this '=*' was just one issue I cited for example, there are and can (which i dont know) many more, which give error on sql server 2005 and even SQL Server 2005 Ugrade Advisor didnt catch.
Can we rewrite your given code as
select object_name(id) from syscomments where text like '%=*%'
@. Louis -- But, no, there is no way to have this kind of syntax check done automatically for you if the Upgrade Advisor misses it.
Does this mean that we can not identify affected stored proc if Upgrade Advisor misses them, expect by executing them.
Regards
Rahul
|||Install the SQL Server Best Practices Analyzer and run it against your SQL Server 2000 database. This has a rule to check for the older-style outer joins.|||
Yeah, anything that the program Umachandar mentions or the upgrade advisor miss will not be found. I was just giving an alternative for searching through your code if you find things you need to.
And no, syscomments cannot be relied upon for a precise search, since it is chunked into 4000 character chunks. Object_definition returns the full text as a varchar(max) that you can use the like on.
My experience iwth the Upgrade Advisor was pretty pleasant. We had very little trouble taking our 2000 databases from 2005, but then again, we try to keep up and usually make those kind of changes ahead of time (not that that helps you :)
|||Just to site one example which made me wondering--
Here is a line of code from my stored proc
tsequal(TmStp, convert(varbinary(8), convert(bigint, @.xTmStp)))
Now the irony is my stored proc is got compiled in sql server 2000 and when i moved it to sql server 2005 and compiled it ther it gave me following error:-
Msg 102, Level 15, State 1, Procedure rsp_UpdPolSchdTmStp, Line 43
Incorrect syntax near 'TSEQUAL'.
Regards
Rahul Kumar
|||Yep, TSEQUAL is gone for good in 2005 (http://sqljunkies.com/Forums/ShowPost.aspx?PostID=2534) but it wasn't documented for quite a while.
It isn't necessary, so you can change:
tsequal(TmStp, convert(varbinary(8), convert(bigint, @.xTmStp)))
to
TmStp = convert(varbinary(8), convert(bigint, @.xTmStp)))
though I am not sure why you are doing all of the converting. if @.xTmStp is of varbinary(8) type (or rowversion/timestamp type) you can just do:
TmStp = @.xTmStp
Tuesday, February 14, 2012
compatibility level 80 in sql server 2005
Hi,
I have a general question about the implications of setting sql server 2005
database to compatibility level 80. It will take me some time to convert and
test the existing db schema and app to fully support sql 2005, so for now I
use this compatibility feature.
Besides not being able to use the new features of sql 2005 will setting to
compatibility level 80 effect negatively the db response time, performance,
etc...?
And if there are no problems with that temporary solution does anybody know
about any resources or articles on the web that I could provide to my
clients who are concerned about setting the compatibility level to 80?
Thank you,
VadimThere are some features in 2005 you won't be able to take advantage of but
for the most part it should not affect performance. But my question is why
are you at 80? Did you try it at 90 and have issues? Did you run the
Upgrade Advisor against your 2000 db and traces to see what issues you have
if any?
--
Andrew J. Kelly SQL MVP
"Vadim" <vadim@.dontsend.com> wrote in message
news:%23qPp0BniGHA.4716@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a general question about the implications of setting sql server
> 2005 database to compatibility level 80. It will take me some time to
> convert and test the existing db schema and app to fully support sql 2005,
> so for now I use this compatibility feature.
> Besides not being able to use the new features of sql 2005 will setting to
> compatibility level 80 effect negatively the db response time,
> performance, etc...?
> And if there are no problems with that temporary solution does anybody
> know about any resources or articles on the web that I could provide to my
> clients who are concerned about setting the compatibility level to 80?
> Thank you,
> Vadim
>|||Andrew J. Kelly wrote:
> There are some features in 2005 you won't be able to take advantage of but
> for the most part it should not affect performance. But my question is why
> are you at 80? Did you try it at 90 and have issues? Did you run the
> Upgrade Advisor against your 2000 db and traces to see what issues you have
> if any?
> --
> Andrew J. Kelly SQL MVP
>
> "Vadim" <vadim@.dontsend.com> wrote in message
> news:%23qPp0BniGHA.4716@.TK2MSFTNGP03.phx.gbl...
> > Hi,
> > I have a general question about the implications of setting sql server
> > 2005 database to compatibility level 80. It will take me some time to
> > convert and test the existing db schema and app to fully support sql 2005,
> > so for now I use this compatibility feature.
> > Besides not being able to use the new features of sql 2005 will setting to
> > compatibility level 80 effect negatively the db response time,
> > performance, etc...?
> > And if there are no problems with that temporary solution does anybody
> > know about any resources or articles on the web that I could provide to my
> > clients who are concerned about setting the compatibility level to 80?
> >
> > Thank you,
> >
> > Vadim
> >
For backward compatibility details look into in BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/4760732b-aa3c-4f07-96ec-ba920476dd69.htm
Regards
Amish Shah|||Hi Andrew,
Yes, the main and I think only problem is sql syntax for linking tables for
inner and outer joins, I currently syntax compatible with Oracle and Sql
Server 7/2000, Microsoft just discontinued support for that syntax so I'll
have to chnage and test the whole app to make sure it works properly and it
takes time.
Thank you for your reply,
Vadim
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eXcp8MqiGHA.3496@.TK2MSFTNGP02.phx.gbl...
> There are some features in 2005 you won't be able to take advantage of but
> for the most part it should not affect performance. But my question is why
> are you at 80? Did you try it at 90 and have issues? Did you run the
> Upgrade Advisor against your 2000 db and traces to see what issues you
> have if any?
> --
> Andrew J. Kelly SQL MVP
>
> "Vadim" <vadim@.dontsend.com> wrote in message
> news:%23qPp0BniGHA.4716@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I have a general question about the implications of setting sql server
>> 2005 database to compatibility level 80. It will take me some time to
>> convert and test the existing db schema and app to fully support sql
>> 2005, so for now I use this compatibility feature.
>> Besides not being able to use the new features of sql 2005 will setting
>> to compatibility level 80 effect negatively the db response time,
>> performance, etc...?
>> And if there are no problems with that temporary solution does anybody
>> know about any resources or articles on the web that I could provide to
>> my clients who are concerned about setting the compatibility level to 80?
>> Thank you,
>> Vadim
>|||Thank you, Amish, good info but they don't mention how this affects the
performance internally, although based on the previous post it seems like
there are no performance issues.
I'll try also to run the upgrade advisor.
Vadim
"amish" <shahamishm@.gmail.com> wrote in message
news:1149742077.528427.325660@.u72g2000cwu.googlegroups.com...
> Andrew J. Kelly wrote:
>> There are some features in 2005 you won't be able to take advantage of
>> but
>> for the most part it should not affect performance. But my question is
>> why
>> are you at 80? Did you try it at 90 and have issues? Did you run the
>> Upgrade Advisor against your 2000 db and traces to see what issues you
>> have
>> if any?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Vadim" <vadim@.dontsend.com> wrote in message
>> news:%23qPp0BniGHA.4716@.TK2MSFTNGP03.phx.gbl...
>> > Hi,
>> > I have a general question about the implications of setting sql server
>> > 2005 database to compatibility level 80. It will take me some time to
>> > convert and test the existing db schema and app to fully support sql
>> > 2005,
>> > so for now I use this compatibility feature.
>> > Besides not being able to use the new features of sql 2005 will setting
>> > to
>> > compatibility level 80 effect negatively the db response time,
>> > performance, etc...?
>> > And if there are no problems with that temporary solution does anybody
>> > know about any resources or articles on the web that I could provide to
>> > my
>> > clients who are concerned about setting the compatibility level to 80?
>> >
>> > Thank you,
>> >
>> > Vadim
>> >
> For backward compatibility details look into in BOL
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/4760732b-aa3c-4f07-96ec-ba920476dd69.htm
> Regards
> Amish Shah
>
I have a general question about the implications of setting sql server 2005
database to compatibility level 80. It will take me some time to convert and
test the existing db schema and app to fully support sql 2005, so for now I
use this compatibility feature.
Besides not being able to use the new features of sql 2005 will setting to
compatibility level 80 effect negatively the db response time, performance,
etc...?
And if there are no problems with that temporary solution does anybody know
about any resources or articles on the web that I could provide to my
clients who are concerned about setting the compatibility level to 80?
Thank you,
VadimThere are some features in 2005 you won't be able to take advantage of but
for the most part it should not affect performance. But my question is why
are you at 80? Did you try it at 90 and have issues? Did you run the
Upgrade Advisor against your 2000 db and traces to see what issues you have
if any?
--
Andrew J. Kelly SQL MVP
"Vadim" <vadim@.dontsend.com> wrote in message
news:%23qPp0BniGHA.4716@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a general question about the implications of setting sql server
> 2005 database to compatibility level 80. It will take me some time to
> convert and test the existing db schema and app to fully support sql 2005,
> so for now I use this compatibility feature.
> Besides not being able to use the new features of sql 2005 will setting to
> compatibility level 80 effect negatively the db response time,
> performance, etc...?
> And if there are no problems with that temporary solution does anybody
> know about any resources or articles on the web that I could provide to my
> clients who are concerned about setting the compatibility level to 80?
> Thank you,
> Vadim
>|||Andrew J. Kelly wrote:
> There are some features in 2005 you won't be able to take advantage of but
> for the most part it should not affect performance. But my question is why
> are you at 80? Did you try it at 90 and have issues? Did you run the
> Upgrade Advisor against your 2000 db and traces to see what issues you have
> if any?
> --
> Andrew J. Kelly SQL MVP
>
> "Vadim" <vadim@.dontsend.com> wrote in message
> news:%23qPp0BniGHA.4716@.TK2MSFTNGP03.phx.gbl...
> > Hi,
> > I have a general question about the implications of setting sql server
> > 2005 database to compatibility level 80. It will take me some time to
> > convert and test the existing db schema and app to fully support sql 2005,
> > so for now I use this compatibility feature.
> > Besides not being able to use the new features of sql 2005 will setting to
> > compatibility level 80 effect negatively the db response time,
> > performance, etc...?
> > And if there are no problems with that temporary solution does anybody
> > know about any resources or articles on the web that I could provide to my
> > clients who are concerned about setting the compatibility level to 80?
> >
> > Thank you,
> >
> > Vadim
> >
For backward compatibility details look into in BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/4760732b-aa3c-4f07-96ec-ba920476dd69.htm
Regards
Amish Shah|||Hi Andrew,
Yes, the main and I think only problem is sql syntax for linking tables for
inner and outer joins, I currently syntax compatible with Oracle and Sql
Server 7/2000, Microsoft just discontinued support for that syntax so I'll
have to chnage and test the whole app to make sure it works properly and it
takes time.
Thank you for your reply,
Vadim
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eXcp8MqiGHA.3496@.TK2MSFTNGP02.phx.gbl...
> There are some features in 2005 you won't be able to take advantage of but
> for the most part it should not affect performance. But my question is why
> are you at 80? Did you try it at 90 and have issues? Did you run the
> Upgrade Advisor against your 2000 db and traces to see what issues you
> have if any?
> --
> Andrew J. Kelly SQL MVP
>
> "Vadim" <vadim@.dontsend.com> wrote in message
> news:%23qPp0BniGHA.4716@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> I have a general question about the implications of setting sql server
>> 2005 database to compatibility level 80. It will take me some time to
>> convert and test the existing db schema and app to fully support sql
>> 2005, so for now I use this compatibility feature.
>> Besides not being able to use the new features of sql 2005 will setting
>> to compatibility level 80 effect negatively the db response time,
>> performance, etc...?
>> And if there are no problems with that temporary solution does anybody
>> know about any resources or articles on the web that I could provide to
>> my clients who are concerned about setting the compatibility level to 80?
>> Thank you,
>> Vadim
>|||Thank you, Amish, good info but they don't mention how this affects the
performance internally, although based on the previous post it seems like
there are no performance issues.
I'll try also to run the upgrade advisor.
Vadim
"amish" <shahamishm@.gmail.com> wrote in message
news:1149742077.528427.325660@.u72g2000cwu.googlegroups.com...
> Andrew J. Kelly wrote:
>> There are some features in 2005 you won't be able to take advantage of
>> but
>> for the most part it should not affect performance. But my question is
>> why
>> are you at 80? Did you try it at 90 and have issues? Did you run the
>> Upgrade Advisor against your 2000 db and traces to see what issues you
>> have
>> if any?
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Vadim" <vadim@.dontsend.com> wrote in message
>> news:%23qPp0BniGHA.4716@.TK2MSFTNGP03.phx.gbl...
>> > Hi,
>> > I have a general question about the implications of setting sql server
>> > 2005 database to compatibility level 80. It will take me some time to
>> > convert and test the existing db schema and app to fully support sql
>> > 2005,
>> > so for now I use this compatibility feature.
>> > Besides not being able to use the new features of sql 2005 will setting
>> > to
>> > compatibility level 80 effect negatively the db response time,
>> > performance, etc...?
>> > And if there are no problems with that temporary solution does anybody
>> > know about any resources or articles on the web that I could provide to
>> > my
>> > clients who are concerned about setting the compatibility level to 80?
>> >
>> > Thank you,
>> >
>> > Vadim
>> >
> For backward compatibility details look into in BOL
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/4760732b-aa3c-4f07-96ec-ba920476dd69.htm
> Regards
> Amish Shah
>
compatibility level 80 in sql server 2005
Hi,
I have a general question about the implications of setting sql server 2005
database to compatibility level 80. It will take me some time to convert and
test the existing db schema and app to fully support sql 2005, so for now I
use this compatibility feature.
Besides not being able to use the new features of sql 2005 will setting to
compatibility level 80 effect negatively the db response time, performance,
etc...?
And if there are no problems with that temporary solution does anybody know
about any resources or articles on the web that I could provide to my
clients who are concerned about setting the compatibility level to 80?
Thank you,
VadimThere are some features in 2005 you won't be able to take advantage of but
for the most part it should not affect performance. But my question is why
are you at 80? Did you try it at 90 and have issues? Did you run the
Upgrade Advisor against your 2000 db and traces to see what issues you have
if any?
Andrew J. Kelly SQL MVP
"Vadim" <vadim@.dontsend.com> wrote in message
news:%23qPp0BniGHA.4716@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a general question about the implications of setting sql server
> 2005 database to compatibility level 80. It will take me some time to
> convert and test the existing db schema and app to fully support sql 2005,
> so for now I use this compatibility feature.
> Besides not being able to use the new features of sql 2005 will setting to
> compatibility level 80 effect negatively the db response time,
> performance, etc...?
> And if there are no problems with that temporary solution does anybody
> know about any resources or articles on the web that I could provide to my
> clients who are concerned about setting the compatibility level to 80?
> Thank you,
> Vadim
>|||Andrew J. Kelly wrote:
[vbcol=seagreen]
> There are some features in 2005 you won't be able to take advantage of but
> for the most part it should not affect performance. But my question is why
> are you at 80? Did you try it at 90 and have issues? Did you run the
> Upgrade Advisor against your 2000 db and traces to see what issues you hav
e
> if any?
> --
> Andrew J. Kelly SQL MVP
>
> "Vadim" <vadim@.dontsend.com> wrote in message
> news:%23qPp0BniGHA.4716@.TK2MSFTNGP03.phx.gbl...
For backward compatibility details look into in BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/4760732b-aa3c-4f07-96ec-
ba920476dd69.htm
Regards
Amish Shah|||Hi Andrew,
Yes, the main and I think only problem is sql syntax for linking tables for
inner and outer joins, I currently syntax compatible with Oracle and Sql
Server 7/2000, Microsoft just discontinued support for that syntax so I'll
have to chnage and test the whole app to make sure it works properly and it
takes time.
Thank you for your reply,
Vadim
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eXcp8MqiGHA.3496@.TK2MSFTNGP02.phx.gbl...
> There are some features in 2005 you won't be able to take advantage of but
> for the most part it should not affect performance. But my question is why
> are you at 80? Did you try it at 90 and have issues? Did you run the
> Upgrade Advisor against your 2000 db and traces to see what issues you
> have if any?
> --
> Andrew J. Kelly SQL MVP
>
> "Vadim" <vadim@.dontsend.com> wrote in message
> news:%23qPp0BniGHA.4716@.TK2MSFTNGP03.phx.gbl...
>|||Thank you, Amish, good info but they don't mention how this affects the
performance internally, although based on the previous post it seems like
there are no performance issues.
I'll try also to run the upgrade advisor.
Vadim
"amish" <shahamishm@.gmail.com> wrote in message
news:1149742077.528427.325660@.u72g2000cwu.googlegroups.com...
> Andrew J. Kelly wrote:
>
> For backward compatibility details look into in BOL
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/4760732b-aa3c-4f07-96e
c-ba920476dd69.htm
> Regards
> Amish Shah
>
I have a general question about the implications of setting sql server 2005
database to compatibility level 80. It will take me some time to convert and
test the existing db schema and app to fully support sql 2005, so for now I
use this compatibility feature.
Besides not being able to use the new features of sql 2005 will setting to
compatibility level 80 effect negatively the db response time, performance,
etc...?
And if there are no problems with that temporary solution does anybody know
about any resources or articles on the web that I could provide to my
clients who are concerned about setting the compatibility level to 80?
Thank you,
VadimThere are some features in 2005 you won't be able to take advantage of but
for the most part it should not affect performance. But my question is why
are you at 80? Did you try it at 90 and have issues? Did you run the
Upgrade Advisor against your 2000 db and traces to see what issues you have
if any?
Andrew J. Kelly SQL MVP
"Vadim" <vadim@.dontsend.com> wrote in message
news:%23qPp0BniGHA.4716@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have a general question about the implications of setting sql server
> 2005 database to compatibility level 80. It will take me some time to
> convert and test the existing db schema and app to fully support sql 2005,
> so for now I use this compatibility feature.
> Besides not being able to use the new features of sql 2005 will setting to
> compatibility level 80 effect negatively the db response time,
> performance, etc...?
> And if there are no problems with that temporary solution does anybody
> know about any resources or articles on the web that I could provide to my
> clients who are concerned about setting the compatibility level to 80?
> Thank you,
> Vadim
>|||Andrew J. Kelly wrote:
[vbcol=seagreen]
> There are some features in 2005 you won't be able to take advantage of but
> for the most part it should not affect performance. But my question is why
> are you at 80? Did you try it at 90 and have issues? Did you run the
> Upgrade Advisor against your 2000 db and traces to see what issues you hav
e
> if any?
> --
> Andrew J. Kelly SQL MVP
>
> "Vadim" <vadim@.dontsend.com> wrote in message
> news:%23qPp0BniGHA.4716@.TK2MSFTNGP03.phx.gbl...
For backward compatibility details look into in BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/4760732b-aa3c-4f07-96ec-
ba920476dd69.htm
Regards
Amish Shah|||Hi Andrew,
Yes, the main and I think only problem is sql syntax for linking tables for
inner and outer joins, I currently syntax compatible with Oracle and Sql
Server 7/2000, Microsoft just discontinued support for that syntax so I'll
have to chnage and test the whole app to make sure it works properly and it
takes time.
Thank you for your reply,
Vadim
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eXcp8MqiGHA.3496@.TK2MSFTNGP02.phx.gbl...
> There are some features in 2005 you won't be able to take advantage of but
> for the most part it should not affect performance. But my question is why
> are you at 80? Did you try it at 90 and have issues? Did you run the
> Upgrade Advisor against your 2000 db and traces to see what issues you
> have if any?
> --
> Andrew J. Kelly SQL MVP
>
> "Vadim" <vadim@.dontsend.com> wrote in message
> news:%23qPp0BniGHA.4716@.TK2MSFTNGP03.phx.gbl...
>|||Thank you, Amish, good info but they don't mention how this affects the
performance internally, although based on the previous post it seems like
there are no performance issues.
I'll try also to run the upgrade advisor.
Vadim
"amish" <shahamishm@.gmail.com> wrote in message
news:1149742077.528427.325660@.u72g2000cwu.googlegroups.com...
> Andrew J. Kelly wrote:
>
> For backward compatibility details look into in BOL
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/4760732b-aa3c-4f07-96e
c-ba920476dd69.htm
> Regards
> Amish Shah
>
Sunday, February 12, 2012
Compatability between MSSQL server and MSDE??
Can we install both MSSQL and MSDE in the same server? And can they operate
at the same time? Just in case when the CAL is exceeded in the SQL server I
can use MSDE as alternative for other applications.
Yes, you can run them at the same time. Only one can be a default instance, the other(s) need to be
named instances.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AdrianY" <AdrianY@.discussions.microsoft.com> wrote in message
news:8B83C9E8-0449-4ECA-85D2-7556D5121F5E@.microsoft.com...
> Can we install both MSSQL and MSDE in the same server? And can they operate
> at the same time? Just in case when the CAL is exceeded in the SQL server I
> can use MSDE as alternative for other applications.
at the same time? Just in case when the CAL is exceeded in the SQL server I
can use MSDE as alternative for other applications.
Yes, you can run them at the same time. Only one can be a default instance, the other(s) need to be
named instances.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AdrianY" <AdrianY@.discussions.microsoft.com> wrote in message
news:8B83C9E8-0449-4ECA-85D2-7556D5121F5E@.microsoft.com...
> Can we install both MSSQL and MSDE in the same server? And can they operate
> at the same time? Just in case when the CAL is exceeded in the SQL server I
> can use MSDE as alternative for other applications.
Compatability between MSSQL server and MSDE??
Can we install both MSSQL and MSDE in the same server? And can they operate
at the same time? Just in case when the CAL is exceeded in the SQL server I
can use MSDE as alternative for other applications.Yes, you can run them at the same time. Only one can be a default instance, the other(s) need to be
named instances.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AdrianY" <AdrianY@.discussions.microsoft.com> wrote in message
news:8B83C9E8-0449-4ECA-85D2-7556D5121F5E@.microsoft.com...
> Can we install both MSSQL and MSDE in the same server? And can they operate
> at the same time? Just in case when the CAL is exceeded in the SQL server I
> can use MSDE as alternative for other applications.
at the same time? Just in case when the CAL is exceeded in the SQL server I
can use MSDE as alternative for other applications.Yes, you can run them at the same time. Only one can be a default instance, the other(s) need to be
named instances.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"AdrianY" <AdrianY@.discussions.microsoft.com> wrote in message
news:8B83C9E8-0449-4ECA-85D2-7556D5121F5E@.microsoft.com...
> Can we install both MSSQL and MSDE in the same server? And can they operate
> at the same time? Just in case when the CAL is exceeded in the SQL server I
> can use MSDE as alternative for other applications.
Subscribe to:
Posts (Atom)