Hi,
I have MSSQL-Server 2000 installed on a PIII 1.2 GHz server running with 256 MB of RAM.
The server is starting to run slower and slower at the peak times.
When ever I check the task manager performance, the processor is always bussy with red indicator, while the RAM is calm and running under 50%.
Is there any configuration in MSSQL-Server, I can do, so I can use the RAM to take some of the load on the processor?
I will really appreciate your help.
ThanksSQL server does all its actions in RAM. So if you insert more RAM the performance should (technically) increase. But first check how much RAM is available for SQL server and monitor the harddisk activity.|||Could you please help me, how to check for the amount of RAM reserved for MS SQL server?
Thanks|||Start SQL Enterprise manager
1
Connect to the appropiate SQL server
2
Right click on de name of the server in the right panel of the screen and select properties.
3
Choose the Tab memory
The memory must be configured as Dynamically with a minimum of 0 and a maximum of the total amount of RAM availleble.
You did determine that the process sqlservr.exe is consuming the bulk of the RAM and of the CPU time? If not than another proces is responsible for the slow response time!|||If you've sufficient memory allocated to SQL Server (Microsoft recommends don't restrict sql to a particular amount of memory, let it acquire memory on its own, hence it is preferred to allocated all memory dedicated to sql server.) If this is setted properly go to 'Performance Monitor', and under System object see '%Processor Time' which should be well below 80% . At the same time also see 'Processor Queue Length' which should be below 2 or equal to 2.
If above conditions are not satisfied you have processor bottleneck and must upgrade your processor
Showing posts with label installed. Show all posts
Showing posts with label installed. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
incorrect value
Hi,
I'm using crystal reports with firebird database. I installed the firebird ODBC Driver and I
created a DSN connection.
I've in my table a field of type number(15,2) and when I add it in the Crystal report a
incorret value is displayed.
Ex:
Table value field 5,45 and is displayed 0,05 int the crystal report.
The firebird dont have currency type.
number type only.
How I can do for the crystal report to use it number as currencey?
Please, help me.Hi
I dont know about the firebird database.
but please try to insert the value on the filed
like 547 without using comma
regards
I'm using crystal reports with firebird database. I installed the firebird ODBC Driver and I
created a DSN connection.
I've in my table a field of type number(15,2) and when I add it in the Crystal report a
incorret value is displayed.
Ex:
Table value field 5,45 and is displayed 0,05 int the crystal report.
The firebird dont have currency type.
number type only.
How I can do for the crystal report to use it number as currencey?
Please, help me.Hi
I dont know about the firebird database.
but please try to insert the value on the filed
like 547 without using comma
regards
Monday, March 12, 2012
Inconsistent SP performance on different Servers
One of my developers recently installed a backup of the production database onto his test site. His test server has the same configuration as the production server.
One of the Stored Procedures that is called takes 1:45 to run on his machine, but only 2 seconds on the production server. This same SP takes only 2 seconds on my development database.
The SP is called iteratively, up to 10 times... to run against 10 separate fields. Depending on a value for a parameter called @.CriteriaClassID, depends on which portion of the SP runs.
The significant difference in processing time in itself is baffling (since the servers are same specs / configuration, as far as I can tell, and the data is identical, since he has a backup of the most recent production data).
But more baffling: if, in his data, I switch the values from field 1 to field 2, and vice versa, his results take 2 seconds (switching the values in field 1 to field 2 switches the value in @.CriteriaClassID which is passed through to this SP).
It's exactly the same SP; the only difference is that field 1 is processed first, field 2 second, field 3 third etc. On the production site and my development site, it doesn't make a difference in the order they are processed. On his machine it does.
Any ideas? I though perhaps his Indexes were corrupted in the rebuild, but we ran a SQL Server maintenance schedule to clean it up, and no improvement.
This is the SP, if it is of any help:
CREATE procedure [dbo].[st_pull_model_data] @.ModelID as integer, @.CriteriaID as integer
as
declare @.ClientID as integer, @.CriteriaClassId as char(1)
/*Procedure to pull data from org_model_data and postalcode_model_data for modeling and media analysis */
/*Need to have table #temp_data created outside of SP with fields org_id and zip_code */
/*This procedure is used by SP st_model_data */
If @.CriteriaID is not null
begin
set @.CriteriaClassId = (Select model_criteria_type from model_criteria where model_criteria_id = @.CriteriaID)
if @.CriteriaClassID = 'G' -- changes client_id from specific to general, if General is required.
begin
set @.ClientID = 0
end
else
begin
set @.ClientID = (Select client_id from model where model_id = @.ModelID)
end
If @.CriteriaClassId in ('G','P')
Begin
update #temp_data
set data1 = postal_criteria_value
from #temp_data t
left outer join
(select postalcode, postal_criteria_value
from postalcode_model_data pmd
join model_org_trade_area mota on mota.zip_code = pmd.postalcode
join model_org mo on mo.model_org_id = mota.model_org_id
where model_criteria_id = @.CriteriaID
and client_id = @.ClientID
and mo.model_id = @.ModelID) as PMD
on PMD.postalcode = t.zip_code
end
else
Begin
update #temp_data
set data1 = org_criteria_value
from #temp_data t
left outer join
(select distinct postalcode, org_criteria_value, omd.org_id
from org_model_data omd
join org o on o.org_id = omd.org_id
join model_org_trade_area mota on mota.zip_code = omd.postalcode
join model_org mo on mo.model_org_id = mota.model_org_id and mo.org_id = o.org_id
where model_criteria_id = @.CriteriaID and o.client_id = @.ClientID and mo.model_id = @.ModelID) as OMD
on OMD.postalcode = t.zip_code and omd.org_id = t.org_id
end
endJust a thought on something to try...
Declare two local variables that are similar to the two passed in parameters, copy the parameters to these local variables at the top of your sporc and then use the local variables only within the code.
If this has the effect of solving your issue, you have a "parameter sniffing" issue.|||One other thought, is his tempdb on the same physical disk as the database data files? It won't explain the whole performance problem, but it will explain a part.|||Chopin,
Thanks a ton. The inclusion of the two local variables (copying over the SP variables) solved the problem for performance.
Another lesson learned.|||In that case does that mean backup restore do not restore query plans for the stored proc in the database? Any idea?|||NO ... backup does not save the procedure cache.
One of the Stored Procedures that is called takes 1:45 to run on his machine, but only 2 seconds on the production server. This same SP takes only 2 seconds on my development database.
The SP is called iteratively, up to 10 times... to run against 10 separate fields. Depending on a value for a parameter called @.CriteriaClassID, depends on which portion of the SP runs.
The significant difference in processing time in itself is baffling (since the servers are same specs / configuration, as far as I can tell, and the data is identical, since he has a backup of the most recent production data).
But more baffling: if, in his data, I switch the values from field 1 to field 2, and vice versa, his results take 2 seconds (switching the values in field 1 to field 2 switches the value in @.CriteriaClassID which is passed through to this SP).
It's exactly the same SP; the only difference is that field 1 is processed first, field 2 second, field 3 third etc. On the production site and my development site, it doesn't make a difference in the order they are processed. On his machine it does.
Any ideas? I though perhaps his Indexes were corrupted in the rebuild, but we ran a SQL Server maintenance schedule to clean it up, and no improvement.
This is the SP, if it is of any help:
CREATE procedure [dbo].[st_pull_model_data] @.ModelID as integer, @.CriteriaID as integer
as
declare @.ClientID as integer, @.CriteriaClassId as char(1)
/*Procedure to pull data from org_model_data and postalcode_model_data for modeling and media analysis */
/*Need to have table #temp_data created outside of SP with fields org_id and zip_code */
/*This procedure is used by SP st_model_data */
If @.CriteriaID is not null
begin
set @.CriteriaClassId = (Select model_criteria_type from model_criteria where model_criteria_id = @.CriteriaID)
if @.CriteriaClassID = 'G' -- changes client_id from specific to general, if General is required.
begin
set @.ClientID = 0
end
else
begin
set @.ClientID = (Select client_id from model where model_id = @.ModelID)
end
If @.CriteriaClassId in ('G','P')
Begin
update #temp_data
set data1 = postal_criteria_value
from #temp_data t
left outer join
(select postalcode, postal_criteria_value
from postalcode_model_data pmd
join model_org_trade_area mota on mota.zip_code = pmd.postalcode
join model_org mo on mo.model_org_id = mota.model_org_id
where model_criteria_id = @.CriteriaID
and client_id = @.ClientID
and mo.model_id = @.ModelID) as PMD
on PMD.postalcode = t.zip_code
end
else
Begin
update #temp_data
set data1 = org_criteria_value
from #temp_data t
left outer join
(select distinct postalcode, org_criteria_value, omd.org_id
from org_model_data omd
join org o on o.org_id = omd.org_id
join model_org_trade_area mota on mota.zip_code = omd.postalcode
join model_org mo on mo.model_org_id = mota.model_org_id and mo.org_id = o.org_id
where model_criteria_id = @.CriteriaID and o.client_id = @.ClientID and mo.model_id = @.ModelID) as OMD
on OMD.postalcode = t.zip_code and omd.org_id = t.org_id
end
endJust a thought on something to try...
Declare two local variables that are similar to the two passed in parameters, copy the parameters to these local variables at the top of your sporc and then use the local variables only within the code.
If this has the effect of solving your issue, you have a "parameter sniffing" issue.|||One other thought, is his tempdb on the same physical disk as the database data files? It won't explain the whole performance problem, but it will explain a part.|||Chopin,
Thanks a ton. The inclusion of the two local variables (copying over the SP variables) solved the problem for performance.
Another lesson learned.|||In that case does that mean backup restore do not restore query plans for the stored proc in the database? Any idea?|||NO ... backup does not save the procedure cache.
Friday, March 9, 2012
Inconsistency Report
I have a SQL Server 6.5 installation that I want to
migrate to sql 2000. I've installed sql 2000 on the same
machine, and next I'm starting the database upgrade.
Just before starting this process, the installation gives
me a "Warning" :
****************
Inconsistency Report for database : yumyum
****************
dbo.sp_ImporterEnteteCommande
Procedure
Text Not found
--
What can I do for that?Thanks for your Answer.
To bypass this problems here is what i've done (but not
sure if it's the best idea)
-generated a sql script of every stored proc in 6.5
-deleted every stored proc in 6.5
-migrating database to 2000
-creating every stored proc from the sql script of my
stored proc.
The migrating process gives me no warning and seems
succesfull. Then I just goes to the login section and re-
enter every password.
The problem is that on a client computer there is a c++
programm connecting to the sql server through odbc. He
is crashing at startup. After debugging this c++
programm, I've seen that the faulty lines is this one:
PointeurProduitSet->Open(CRecordset::dynaset, strSQL,
CRecordset::none);
If you have any clue about this issue, let me know..
thanks!
Note: I have already tryed some version of Mdac component
in the client machine (running nt4) but no sucess.
>--Original Message--
>The upgrade needs the text to recreate the stored
procedure. As it is, this
>proc will not upgrade successfully. You can drop it and
the error will go
>away.
>For your new database, you should go to wherever you
keep the source of you
>stored procedures and recreate it from source code.
>(FWIW - In SQL 6.5 I believe that the text of a stored
procedure could be
>deleted as a 'security' measure to keep the code
hidden. From 7.0 forward
>this no longer works.)
>Russell Fields
>"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com>
wrote in message
>news:0a8c01c35daf$01514870$a301280a@.phx.gbl...
>> I have a SQL Server 6.5 installation that I want to
>> migrate to sql 2000. I've installed sql 2000 on the
same
>> machine, and next I'm starting the database upgrade.
>> Just before starting this process, the installation
gives
>> me a "Warning" :
>> ****************
>> Inconsistency Report for database : yumyum
>> ****************
>> dbo.sp_ImporterEnteteCommande
>> Procedure
>> Text Not found
>> --
>> What can I do for that?
>
>.
>|||Jean-Francois,
Well, I am not a C++ guy, so I am not much help here. Is the C++ crashing
on its own? (Could it be an ODBC or OLE DB error in setup?) Or is the SQL
Server failing to return it the expected result and crashing because of
that?
I would be interested in what is inside strSQL. It would be interesting if
it was a call to the stored procedure that had no text. (If, when you say
"generated a sql script of every stored proc in 6.5" you mean that you
generated them from the database, any procedure that had no text in
syscomments would not be recreated.)
Russell Fields
"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com> wrote in message
news:0b2d01c35db8$108122d0$a301280a@.phx.gbl...
> Thanks for your Answer.
> To bypass this problems here is what i've done (but not
> sure if it's the best idea)
> -generated a sql script of every stored proc in 6.5
> -deleted every stored proc in 6.5
> -migrating database to 2000
> -creating every stored proc from the sql script of my
> stored proc.
> The migrating process gives me no warning and seems
> succesfull. Then I just goes to the login section and re-
> enter every password.
> The problem is that on a client computer there is a c++
> programm connecting to the sql server through odbc. He
> is crashing at startup. After debugging this c++
> programm, I've seen that the faulty lines is this one:
> PointeurProduitSet->Open(CRecordset::dynaset, strSQL,
> CRecordset::none);
> If you have any clue about this issue, let me know..
> thanks!
> Note: I have already tryed some version of Mdac component
> in the client machine (running nt4) but no sucess.
>
> >--Original Message--
> >The upgrade needs the text to recreate the stored
> procedure. As it is, this
> >proc will not upgrade successfully. You can drop it and
> the error will go
> >away.
> >
> >For your new database, you should go to wherever you
> keep the source of you
> >stored procedures and recreate it from source code.
> >
> >(FWIW - In SQL 6.5 I believe that the text of a stored
> procedure could be
> >deleted as a 'security' measure to keep the code
> hidden. From 7.0 forward
> >this no longer works.)
> >
> >Russell Fields
> >"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com>
> wrote in message
> >news:0a8c01c35daf$01514870$a301280a@.phx.gbl...
> >> I have a SQL Server 6.5 installation that I want to
> >> migrate to sql 2000. I've installed sql 2000 on the
> same
> >> machine, and next I'm starting the database upgrade.
> >>
> >> Just before starting this process, the installation
> gives
> >> me a "Warning" :
> >>
> >> ****************
> >> Inconsistency Report for database : yumyum
> >> ****************
> >> dbo.sp_ImporterEnteteCommande
> >> Procedure
> >> Text Not found
> >> --
> >>
> >> What can I do for that?
> >
> >
> >.
> >|||If you are getting an unhandled SQL exception, you might try a Profiler
trace to spot the errant SQL statement.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com> wrote in message
news:0b2d01c35db8$108122d0$a301280a@.phx.gbl...
> Thanks for your Answer.
> To bypass this problems here is what i've done (but not
> sure if it's the best idea)
> -generated a sql script of every stored proc in 6.5
> -deleted every stored proc in 6.5
> -migrating database to 2000
> -creating every stored proc from the sql script of my
> stored proc.
> The migrating process gives me no warning and seems
> succesfull. Then I just goes to the login section and re-
> enter every password.
> The problem is that on a client computer there is a c++
> programm connecting to the sql server through odbc. He
> is crashing at startup. After debugging this c++
> programm, I've seen that the faulty lines is this one:
> PointeurProduitSet->Open(CRecordset::dynaset, strSQL,
> CRecordset::none);
> If you have any clue about this issue, let me know..
> thanks!
> Note: I have already tryed some version of Mdac component
> in the client machine (running nt4) but no sucess.
>
> >--Original Message--
> >The upgrade needs the text to recreate the stored
> procedure. As it is, this
> >proc will not upgrade successfully. You can drop it and
> the error will go
> >away.
> >
> >For your new database, you should go to wherever you
> keep the source of you
> >stored procedures and recreate it from source code.
> >
> >(FWIW - In SQL 6.5 I believe that the text of a stored
> procedure could be
> >deleted as a 'security' measure to keep the code
> hidden. From 7.0 forward
> >this no longer works.)
> >
> >Russell Fields
> >"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com>
> wrote in message
> >news:0a8c01c35daf$01514870$a301280a@.phx.gbl...
> >> I have a SQL Server 6.5 installation that I want to
> >> migrate to sql 2000. I've installed sql 2000 on the
> same
> >> machine, and next I'm starting the database upgrade.
> >>
> >> Just before starting this process, the installation
> gives
> >> me a "Warning" :
> >>
> >> ****************
> >> Inconsistency Report for database : yumyum
> >> ****************
> >> dbo.sp_ImporterEnteteCommande
> >> Procedure
> >> Text Not found
> >> --
> >>
> >> What can I do for that?
> >
> >
> >.
> >|||Jean-Francois,
The texts for stored procedures are stored in the syscomments table.
Without the texts, it is not possible to create a script for the stored
procedures in question since the scripting tool cannot create the TSQL
needed.
Regarding your problem, if the stored procedure exists and the things it
references exist, then I have no idea. Dan's recommendation to trace your
efforts with Profiler is the best idea I have.
Russell Fields
"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com> wrote in message
news:06af01c36003$b4bf4c30$a301280a@.phx.gbl...
> Sorry I didn't paste the good c++ line that is crashing..
> here it is:
> PointeurSecuriteSet->Open(CRecordset::dynaset, "{call
> sp_SelectSecurite()}", CRecordset::none);
> It is a call to a simple stored procedure with no params.
> About the stored proc, the way i have transfer them
> between sql 6.5 to sql 2000 is:
> -I just generated a sql script from 6.5;
> -in sql 2000, I copied the content of the sql script in
> the query analyser and run it.
> Like you said, the "no text" error may not be solved this
> way.. Where are defined those "text" and what are they?
> Thanks!!
>
> >--Original Message--
> >Jean-Francois,
> >
> >Well, I am not a C++ guy, so I am not much help here.
> Is the C++ crashing
> >on its own? (Could it be an ODBC or OLE DB error in
> setup?) Or is the SQL
> >Server failing to return it the expected result and
> crashing because of
> >that?
> >
> >I would be interested in what is inside strSQL. It
> would be interesting if
> >it was a call to the stored procedure that had no text.
> (If, when you say
> >"generated a sql script of every stored proc in 6.5" you
> mean that you
> >generated them from the database, any procedure that had
> no text in
> >syscomments would not be recreated.)
> >
> >Russell Fields
> >
> >
> >"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com>
> wrote in message
> >news:0b2d01c35db8$108122d0$a301280a@.phx.gbl...
> >> Thanks for your Answer.
> >> To bypass this problems here is what i've done (but not
> >> sure if it's the best idea)
> >> -generated a sql script of every stored proc in 6.5
> >> -deleted every stored proc in 6.5
> >> -migrating database to 2000
> >> -creating every stored proc from the sql script of my
> >> stored proc.
> >>
> >> The migrating process gives me no warning and seems
> >> succesfull. Then I just goes to the login section and
> re-
> >> enter every password.
> >>
> >> The problem is that on a client computer there is a c++
> >> programm connecting to the sql server through odbc. He
> >> is crashing at startup. After debugging this c++
> >> programm, I've seen that the faulty lines is this one:
> >>
> >> PointeurProduitSet->Open(CRecordset::dynaset, strSQL,
> >> CRecordset::none);
> >>
> >> If you have any clue about this issue, let me know..
> >> thanks!
> >>
> >> Note: I have already tryed some version of Mdac
> component
> >> in the client machine (running nt4) but no sucess.
> >>
> >>
> >> >--Original Message--
> >> >The upgrade needs the text to recreate the stored
> >> procedure. As it is, this
> >> >proc will not upgrade successfully. You can drop it
> and
> >> the error will go
> >> >away.
> >> >
> >> >For your new database, you should go to wherever you
> >> keep the source of you
> >> >stored procedures and recreate it from source code.
> >> >
> >> >(FWIW - In SQL 6.5 I believe that the text of a stored
> >> procedure could be
> >> >deleted as a 'security' measure to keep the code
> >> hidden. From 7.0 forward
> >> >this no longer works.)
> >> >
> >> >Russell Fields
> >> >"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com>
> >> wrote in message
> >> >news:0a8c01c35daf$01514870$a301280a@.phx.gbl...
> >> >> I have a SQL Server 6.5 installation that I want to
> >> >> migrate to sql 2000. I've installed sql 2000 on the
> >> same
> >> >> machine, and next I'm starting the database upgrade.
> >> >>
> >> >> Just before starting this process, the installation
> >> gives
> >> >> me a "Warning" :
> >> >>
> >> >> ****************
> >> >> Inconsistency Report for database : yumyum
> >> >> ****************
> >> >> dbo.sp_ImporterEnteteCommande
> >> >> Procedure
> >> >> Text Not found
> >> >> --
> >> >>
> >> >> What can I do for that?
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||The c++ programm was not working because the call to a
stored procedure in SQL 2000 must be without () if you
don't have parameters.
sp_SelectSecurite() must be :
sp_SelectSecurite
It's working well now, even with the inconsistency error.
Thanks guys
migrate to sql 2000. I've installed sql 2000 on the same
machine, and next I'm starting the database upgrade.
Just before starting this process, the installation gives
me a "Warning" :
****************
Inconsistency Report for database : yumyum
****************
dbo.sp_ImporterEnteteCommande
Procedure
Text Not found
--
What can I do for that?Thanks for your Answer.
To bypass this problems here is what i've done (but not
sure if it's the best idea)
-generated a sql script of every stored proc in 6.5
-deleted every stored proc in 6.5
-migrating database to 2000
-creating every stored proc from the sql script of my
stored proc.
The migrating process gives me no warning and seems
succesfull. Then I just goes to the login section and re-
enter every password.
The problem is that on a client computer there is a c++
programm connecting to the sql server through odbc. He
is crashing at startup. After debugging this c++
programm, I've seen that the faulty lines is this one:
PointeurProduitSet->Open(CRecordset::dynaset, strSQL,
CRecordset::none);
If you have any clue about this issue, let me know..
thanks!
Note: I have already tryed some version of Mdac component
in the client machine (running nt4) but no sucess.
>--Original Message--
>The upgrade needs the text to recreate the stored
procedure. As it is, this
>proc will not upgrade successfully. You can drop it and
the error will go
>away.
>For your new database, you should go to wherever you
keep the source of you
>stored procedures and recreate it from source code.
>(FWIW - In SQL 6.5 I believe that the text of a stored
procedure could be
>deleted as a 'security' measure to keep the code
hidden. From 7.0 forward
>this no longer works.)
>Russell Fields
>"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com>
wrote in message
>news:0a8c01c35daf$01514870$a301280a@.phx.gbl...
>> I have a SQL Server 6.5 installation that I want to
>> migrate to sql 2000. I've installed sql 2000 on the
same
>> machine, and next I'm starting the database upgrade.
>> Just before starting this process, the installation
gives
>> me a "Warning" :
>> ****************
>> Inconsistency Report for database : yumyum
>> ****************
>> dbo.sp_ImporterEnteteCommande
>> Procedure
>> Text Not found
>> --
>> What can I do for that?
>
>.
>|||Jean-Francois,
Well, I am not a C++ guy, so I am not much help here. Is the C++ crashing
on its own? (Could it be an ODBC or OLE DB error in setup?) Or is the SQL
Server failing to return it the expected result and crashing because of
that?
I would be interested in what is inside strSQL. It would be interesting if
it was a call to the stored procedure that had no text. (If, when you say
"generated a sql script of every stored proc in 6.5" you mean that you
generated them from the database, any procedure that had no text in
syscomments would not be recreated.)
Russell Fields
"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com> wrote in message
news:0b2d01c35db8$108122d0$a301280a@.phx.gbl...
> Thanks for your Answer.
> To bypass this problems here is what i've done (but not
> sure if it's the best idea)
> -generated a sql script of every stored proc in 6.5
> -deleted every stored proc in 6.5
> -migrating database to 2000
> -creating every stored proc from the sql script of my
> stored proc.
> The migrating process gives me no warning and seems
> succesfull. Then I just goes to the login section and re-
> enter every password.
> The problem is that on a client computer there is a c++
> programm connecting to the sql server through odbc. He
> is crashing at startup. After debugging this c++
> programm, I've seen that the faulty lines is this one:
> PointeurProduitSet->Open(CRecordset::dynaset, strSQL,
> CRecordset::none);
> If you have any clue about this issue, let me know..
> thanks!
> Note: I have already tryed some version of Mdac component
> in the client machine (running nt4) but no sucess.
>
> >--Original Message--
> >The upgrade needs the text to recreate the stored
> procedure. As it is, this
> >proc will not upgrade successfully. You can drop it and
> the error will go
> >away.
> >
> >For your new database, you should go to wherever you
> keep the source of you
> >stored procedures and recreate it from source code.
> >
> >(FWIW - In SQL 6.5 I believe that the text of a stored
> procedure could be
> >deleted as a 'security' measure to keep the code
> hidden. From 7.0 forward
> >this no longer works.)
> >
> >Russell Fields
> >"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com>
> wrote in message
> >news:0a8c01c35daf$01514870$a301280a@.phx.gbl...
> >> I have a SQL Server 6.5 installation that I want to
> >> migrate to sql 2000. I've installed sql 2000 on the
> same
> >> machine, and next I'm starting the database upgrade.
> >>
> >> Just before starting this process, the installation
> gives
> >> me a "Warning" :
> >>
> >> ****************
> >> Inconsistency Report for database : yumyum
> >> ****************
> >> dbo.sp_ImporterEnteteCommande
> >> Procedure
> >> Text Not found
> >> --
> >>
> >> What can I do for that?
> >
> >
> >.
> >|||If you are getting an unhandled SQL exception, you might try a Profiler
trace to spot the errant SQL statement.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com> wrote in message
news:0b2d01c35db8$108122d0$a301280a@.phx.gbl...
> Thanks for your Answer.
> To bypass this problems here is what i've done (but not
> sure if it's the best idea)
> -generated a sql script of every stored proc in 6.5
> -deleted every stored proc in 6.5
> -migrating database to 2000
> -creating every stored proc from the sql script of my
> stored proc.
> The migrating process gives me no warning and seems
> succesfull. Then I just goes to the login section and re-
> enter every password.
> The problem is that on a client computer there is a c++
> programm connecting to the sql server through odbc. He
> is crashing at startup. After debugging this c++
> programm, I've seen that the faulty lines is this one:
> PointeurProduitSet->Open(CRecordset::dynaset, strSQL,
> CRecordset::none);
> If you have any clue about this issue, let me know..
> thanks!
> Note: I have already tryed some version of Mdac component
> in the client machine (running nt4) but no sucess.
>
> >--Original Message--
> >The upgrade needs the text to recreate the stored
> procedure. As it is, this
> >proc will not upgrade successfully. You can drop it and
> the error will go
> >away.
> >
> >For your new database, you should go to wherever you
> keep the source of you
> >stored procedures and recreate it from source code.
> >
> >(FWIW - In SQL 6.5 I believe that the text of a stored
> procedure could be
> >deleted as a 'security' measure to keep the code
> hidden. From 7.0 forward
> >this no longer works.)
> >
> >Russell Fields
> >"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com>
> wrote in message
> >news:0a8c01c35daf$01514870$a301280a@.phx.gbl...
> >> I have a SQL Server 6.5 installation that I want to
> >> migrate to sql 2000. I've installed sql 2000 on the
> same
> >> machine, and next I'm starting the database upgrade.
> >>
> >> Just before starting this process, the installation
> gives
> >> me a "Warning" :
> >>
> >> ****************
> >> Inconsistency Report for database : yumyum
> >> ****************
> >> dbo.sp_ImporterEnteteCommande
> >> Procedure
> >> Text Not found
> >> --
> >>
> >> What can I do for that?
> >
> >
> >.
> >|||Jean-Francois,
The texts for stored procedures are stored in the syscomments table.
Without the texts, it is not possible to create a script for the stored
procedures in question since the scripting tool cannot create the TSQL
needed.
Regarding your problem, if the stored procedure exists and the things it
references exist, then I have no idea. Dan's recommendation to trace your
efforts with Profiler is the best idea I have.
Russell Fields
"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com> wrote in message
news:06af01c36003$b4bf4c30$a301280a@.phx.gbl...
> Sorry I didn't paste the good c++ line that is crashing..
> here it is:
> PointeurSecuriteSet->Open(CRecordset::dynaset, "{call
> sp_SelectSecurite()}", CRecordset::none);
> It is a call to a simple stored procedure with no params.
> About the stored proc, the way i have transfer them
> between sql 6.5 to sql 2000 is:
> -I just generated a sql script from 6.5;
> -in sql 2000, I copied the content of the sql script in
> the query analyser and run it.
> Like you said, the "no text" error may not be solved this
> way.. Where are defined those "text" and what are they?
> Thanks!!
>
> >--Original Message--
> >Jean-Francois,
> >
> >Well, I am not a C++ guy, so I am not much help here.
> Is the C++ crashing
> >on its own? (Could it be an ODBC or OLE DB error in
> setup?) Or is the SQL
> >Server failing to return it the expected result and
> crashing because of
> >that?
> >
> >I would be interested in what is inside strSQL. It
> would be interesting if
> >it was a call to the stored procedure that had no text.
> (If, when you say
> >"generated a sql script of every stored proc in 6.5" you
> mean that you
> >generated them from the database, any procedure that had
> no text in
> >syscomments would not be recreated.)
> >
> >Russell Fields
> >
> >
> >"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com>
> wrote in message
> >news:0b2d01c35db8$108122d0$a301280a@.phx.gbl...
> >> Thanks for your Answer.
> >> To bypass this problems here is what i've done (but not
> >> sure if it's the best idea)
> >> -generated a sql script of every stored proc in 6.5
> >> -deleted every stored proc in 6.5
> >> -migrating database to 2000
> >> -creating every stored proc from the sql script of my
> >> stored proc.
> >>
> >> The migrating process gives me no warning and seems
> >> succesfull. Then I just goes to the login section and
> re-
> >> enter every password.
> >>
> >> The problem is that on a client computer there is a c++
> >> programm connecting to the sql server through odbc. He
> >> is crashing at startup. After debugging this c++
> >> programm, I've seen that the faulty lines is this one:
> >>
> >> PointeurProduitSet->Open(CRecordset::dynaset, strSQL,
> >> CRecordset::none);
> >>
> >> If you have any clue about this issue, let me know..
> >> thanks!
> >>
> >> Note: I have already tryed some version of Mdac
> component
> >> in the client machine (running nt4) but no sucess.
> >>
> >>
> >> >--Original Message--
> >> >The upgrade needs the text to recreate the stored
> >> procedure. As it is, this
> >> >proc will not upgrade successfully. You can drop it
> and
> >> the error will go
> >> >away.
> >> >
> >> >For your new database, you should go to wherever you
> >> keep the source of you
> >> >stored procedures and recreate it from source code.
> >> >
> >> >(FWIW - In SQL 6.5 I believe that the text of a stored
> >> procedure could be
> >> >deleted as a 'security' measure to keep the code
> >> hidden. From 7.0 forward
> >> >this no longer works.)
> >> >
> >> >Russell Fields
> >> >"Jean-Francois Bouchard" <jeanfrancois_21@.hotmail.com>
> >> wrote in message
> >> >news:0a8c01c35daf$01514870$a301280a@.phx.gbl...
> >> >> I have a SQL Server 6.5 installation that I want to
> >> >> migrate to sql 2000. I've installed sql 2000 on the
> >> same
> >> >> machine, and next I'm starting the database upgrade.
> >> >>
> >> >> Just before starting this process, the installation
> >> gives
> >> >> me a "Warning" :
> >> >>
> >> >> ****************
> >> >> Inconsistency Report for database : yumyum
> >> >> ****************
> >> >> dbo.sp_ImporterEnteteCommande
> >> >> Procedure
> >> >> Text Not found
> >> >> --
> >> >>
> >> >> What can I do for that?
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >|||The c++ programm was not working because the call to a
stored procedure in SQL 2000 must be without () if you
don't have parameters.
sp_SelectSecurite() must be :
sp_SelectSecurite
It's working well now, even with the inconsistency error.
Thanks guys
Incompatible Frameworks between Sql Server June CTP and VS 2005
Hi,
I have a Sql Server 2005 June CTP which just arrived for me. I installed it on the my workstation which is XP Professional with SP2 successfully.
I also did attempted to install the latest (to my knowledge) of VS 2005 Beta which downloaded from MSDN from my work Version 8.0.50712.6 which has the framework version of 2.0.50712.
Obviously error is prompted regarding the framework conflict. So I uninstalled the new Sql Server June CTP in flavour of installing the VS2005.
At work, we have the Sql Server April CTP which has framework version 2.0.50215. I tested connection by using new VS 2005 I just installed to this server. Well, I can see it and able to goes amend the tables etc. Have not test the T-SQL or do any coding yet.
Can any one advice me that is there an version of VS 2005 which works happily with June CTP version, so I can use it together.
I still do have the April CTP for both apps at home which are installed on my home pc. I gather that this Sql Server June CTP would not work with April CTP Beta2 as well or is it?
Are there any package to downloads to upgrade this apps to get them to work together?
How you can help me, and thanks in advance
Punprom Kasemsant 
Due to the length of time since this issue was posted, I'm marking it as resolved. If you're still in need of help, please respond here.
Paul
Incompatible Frameworks between Sql Server June CTP and VS 2005
Hi,
I have a Sql Server 2005 June CTP which just arrived for me. I installed it on the my workstation which is XP Professional with SP2 successfully.
I also did attempted to install the latest (to my knowledge) of VS 2005 Beta which downloaded from MSDN from my work Version 8.0.50712.6 which has the framework version of 2.0.50712.
Obviously error is prompted regarding the framework conflict. So I uninstalled the new Sql Server June CTP in flavour of installing the VS2005.
At work, we have the Sql Server April CTP which has framework version 2.0.50215. I tested connection by using new VS 2005 I just installed to this server. Well, I can see it and able to goes amend the tables etc. Have not test the T-SQL or do any coding yet.
Can any one advice me that is there an version of VS 2005 which works happily with June CTP version, so I can use it together.
I still do have the April CTP for both apps at home which are installed on my home pc. I gather that this Sql Server June CTP would not work with April CTP Beta2 as well or is it?
Are there any package to downloads to upgrade this apps to get them to work together?
How you can help me, and thanks in advance
Punprom Kasemsant
Due to the length of time since this issue was posted, I'm marking it as resolved. If you're still in need of help, please respond here.
Paul
Wednesday, March 7, 2012
Including SQL Server Express as a nested MSI install
I'm working on an MSI-based install that installs a suite of applications. One of the applications requires SQL Server 2005 Express to be installed or already on the machine, but if the user doesn't want that app installed, I don't want to have to bother with the SQL Server issue; however, if that app is to be installed and SQL Server is not already installed, I want to be able to have it installed automatically during our install in order to make it easier for our end users. The obvious answer to this is to launch the SQL Server Express install as a nested MSI install at the appropriate point in our install. Since the SQL Server install is so complex, I was wondering if this would work if I were to launch the SQL Server install's setup.exe; or do I have to launch one of its dozen MSI files? If it's the latter, which one should my install launch? (Or is the SQL Server Express install simply too complex to be included in my install in this way? My plan is to have the SQL Server install sitting somewhere on our install CD - we've already obtained a redistribution licence - and have our install access it from there.) And will SQL Server Express appear as a separate entry in the Add/Remove Programs applet if it's installed this way? (I'd like to be able to leave SQL Server in place when uninstalling our application suite - or even just the one app that needs it - and allow SQL Server to be uninstalled separately if the user wishes.)Moving to the SQL Express forum, this is not a SSIS issue.|||
Hi Rob,
Yes, you can install SQL Express as part of your applications installation, but you can not nest it inside your existing MSI. Windows Installer does not allow nesting of MSIs, so you have to create a wrapper that will handle running both the SQL Express installation and your own application MSI.
If you are building your application using Visual Studio 2005, the VS Bootstrapper is already designed to do this for you. Just add SQL Express from the list of Pre-requisites and VS will build your setup to automatically install SQL Express along side your application. If you're trying to roll you own, there are a number of different resources available to help you do this.
Check out the FAQ that I've just posted at the top of this forum, you will find links to information about installing SQL Express in quite mode and detecting if SQL Express is already on the computer.|||It would appear, then, that I'm in a catch 22 situation. As I mentioned in my original posting, my install is MSI-based, and it installs a vertical market suite of applications, only one of which needs SQL Server Express to be installed. As I also mentioned, I don't want to have to deal with the SQL Server issue if the user doesn't want to install that particular application. This suggests that SQL Server be installed after the application suite is installed. However, if the application in question is installed, the install has to start it once it's installed, meaning that SQL Server must be installed first. These two conflicting needs point to the need for SQL Server to be installed at an appropriate point during my suite install.
I'm using Wise for Windows Installer to develop my installer, and because I want only one entry for the entire application suite in the ARP applet, I don't think separating the install for each application in the suite into a separate MSI would be the way to go. Also, if each application were installed by a separate MSI, wouldn't each MSI have its own GUI. I'd like to have one GUI for the entire suite, preferably without having to roll my own dialogues from scratch. Hence, the install was constructed as a single MSI.
Is there no way to launch the SQL Server Express install as a nested install using one of the MSIs that come in the package, or are those MSIs inextricably tied into the setup.exe wrapper?|||
Hi Rob,
No, you can not nest MSI installation. This is a limitation of the Windows Installer technology.
I'm not sure you understood all of my post; it is totally possible to install SQL Server in quite mode, so that it doesn't show any UI at all. Read the quite mode install FAQ and the embedding paper to learn more about how to deploy SQL Express with your own application.
SQL Express will always install as a separate item in the ARP list. We have to do this for the purpose of discovery and patching. There is a bunch of technical mumbo jumbo, but the short version is that if you could install SQL Express so that it didn't show up in ARP, then we couldn't detect it from Microsoft Update, and there would be no facility for patching it. In the event that there is a security issue that needs to be patched, we must be able to detect SQL Express to patch it. If you hid SQL Express, you would be putting your customers at risk, which I'm sure you don't want to do.
Thousands of developers accomplish exactly what you are trying to do using a wrapper program to chain together multiple MSI based installations. You will need to write a wrapper program that contains the logic to determine which components need to be installed. In your case, you would simple have a wrapper that would allow customers to pick which parts of your application you want to install, if they pick the part that requires SQL Express, then your wrapper checks for SQL Express, and if it is not there, installs it. The wrapper acts as a unifying UI driver. (This is exactly what SQL Server does, we have a wrapper, Setup.exe, that combines multiple MSIs into a single experience.)
It's possible that Wise might even provide the ability to create wrapper programs, I'm sure there are a number of Wise support forums out there where you could find others who are facing this exact same issue. Start with the Wise web site and I'll be there is already a solution.
Regards,
Mike
Sunday, February 19, 2012
In touch with Reporting Services
Hello, All!
It`s the first time with Reporting Services and would like to know if there
are any performance problems when installed on the same server with a SQL
Server.
I`m a Database Administrator and I have a SQL Server into a DMZ with almost
200GB of data and nowadays the company is demanding a Reporting Service into
a DMZ and we don`t have a dedicated server to use with. Otherwise, we buy a
SQL Server license just for this request don`t worth.
So, we decide to install Reporting Service on this server, but I have no idea
about how much recource is needed to handle it and if this installation could
cause any issues.
Anybody knows how Reporting Service works? Are there any best practices when
installing Reporting Services on a OLTP SQL Server ?
Thanks
Juliano Horta
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200802/1You need to get IIS configured on that server before you can install RS,
which itself is a ASP.NET application. That means you will have to run IIS
and SQL Server on the same box. If the hardware can handle that will depend
on the work load. Also, if the RS is SQL Server RS2000, its report rendering
is quite CPU-hungry process and very slow. If a fairly big reports are
requested often, it will definitely slow things down. SQL Server2005 RS'
report rendering is said much better (than RS2000).
Yes, if you place RS in a box different from where SQL Server is, you need
another SQL Server license. You need to make decision based on your work
load and the possible cost of extra box/license.
"julianohorta via SQLMonster.com" <u13014@.uwe> wrote in message
news:8045aaa722294@.uwe...
> Hello, All!
> It`s the first time with Reporting Services and would like to know if
> there
> are any performance problems when installed on the same server with a SQL
> Server.
> I`m a Database Administrator and I have a SQL Server into a DMZ with
> almost
> 200GB of data and nowadays the company is demanding a Reporting Service
> into
> a DMZ and we don`t have a dedicated server to use with. Otherwise, we buy
> a
> SQL Server license just for this request don`t worth.
> So, we decide to install Reporting Service on this server, but I have no
> idea
> about how much recource is needed to handle it and if this installation
> could
> cause any issues.
> Anybody knows how Reporting Service works? Are there any best practices
> when
> installing Reporting Services on a OLTP SQL Server ?
> Thanks
> Juliano Horta
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200802/1
>
It`s the first time with Reporting Services and would like to know if there
are any performance problems when installed on the same server with a SQL
Server.
I`m a Database Administrator and I have a SQL Server into a DMZ with almost
200GB of data and nowadays the company is demanding a Reporting Service into
a DMZ and we don`t have a dedicated server to use with. Otherwise, we buy a
SQL Server license just for this request don`t worth.
So, we decide to install Reporting Service on this server, but I have no idea
about how much recource is needed to handle it and if this installation could
cause any issues.
Anybody knows how Reporting Service works? Are there any best practices when
installing Reporting Services on a OLTP SQL Server ?
Thanks
Juliano Horta
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200802/1You need to get IIS configured on that server before you can install RS,
which itself is a ASP.NET application. That means you will have to run IIS
and SQL Server on the same box. If the hardware can handle that will depend
on the work load. Also, if the RS is SQL Server RS2000, its report rendering
is quite CPU-hungry process and very slow. If a fairly big reports are
requested often, it will definitely slow things down. SQL Server2005 RS'
report rendering is said much better (than RS2000).
Yes, if you place RS in a box different from where SQL Server is, you need
another SQL Server license. You need to make decision based on your work
load and the possible cost of extra box/license.
"julianohorta via SQLMonster.com" <u13014@.uwe> wrote in message
news:8045aaa722294@.uwe...
> Hello, All!
> It`s the first time with Reporting Services and would like to know if
> there
> are any performance problems when installed on the same server with a SQL
> Server.
> I`m a Database Administrator and I have a SQL Server into a DMZ with
> almost
> 200GB of data and nowadays the company is demanding a Reporting Service
> into
> a DMZ and we don`t have a dedicated server to use with. Otherwise, we buy
> a
> SQL Server license just for this request don`t worth.
> So, we decide to install Reporting Service on this server, but I have no
> idea
> about how much recource is needed to handle it and if this installation
> could
> cause any issues.
> Anybody knows how Reporting Service works? Are there any best practices
> when
> installing Reporting Services on a OLTP SQL Server ?
> Thanks
> Juliano Horta
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200802/1
>
Subscribe to:
Posts (Atom)