I generated several workload files over a two day period against my production server. When I run the tuning adviser can I run it against my development database or should I run it against the production database?
Thanks.
I ran the Upgrade Adviser Report on a Server and it identifies the Proxy's as being deprecated.
What action is needed?
Object Type: ProxyObject Name: sa-apro-cms
Object Type: ProxyObject Name: sa-apro-payroll
Object Type: ProxyObject Name: sa-pi-sql-agent
Hi
Is there any good books for Query Tuning and Stored procedure Tuning
Thanks
I am using SMO in VS2005 using C#. I can successfully create a new database, add a couple of tables with columns and then subsequently use normal ADO.NET to access them.
One problem I am having is that I created routines to Add and Remove my Database. They work GREAT when I start the application, create the database and then delete it. However, if I create the database, terminate the application, restart the application... removing the database fails with an error.
This is the code:
if (m_server.Databases.Contains(m_dbName))
{
Database db = new Database(m_server, m_dbName);
m_server.DeleteBackupHistory(m_dbName);
m_server.KillAllProcesses(m_dbName);
m_server.KillDatabase(m_dbName);
MessageBox.Show("Dropped");
}
else
{
MessageBox.Show("Database Not Found");
}
It fails on the KillDatabase statement with an inner exception as follows: {"Unable to open the physical file "c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDB.mdf".
Operating system error 32: "32(The process cannot access the file because it is being used by another process.)".
Let me repeat... if I run this code in the same executable session as when I create the database, it works great.
I use this routine prior to entering either the create to delete database routine:
SqlConnection sqlConnection = new SqlConnection(@"Integrated Security=SSPI; Data Source=(local)SQLEXPRESS");
Microsoft.SqlServer.Management.Common.ServerConnection serverConnection = new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConnection);
m_server = new Server(serverConnection);
The create routines is:
Database db = new Database(m_server, m_dbName);
db.DatabaseOptions.AutoShrink = true;
db.DatabaseOptions.AutoClose = true;
db.Create();
if (m_server.Databases.Contains(m_dbName))
{
MessageBox.Show("Created");
Table tbl = new Table(db, "tblMyTable");
Column col = new Column(tbl, "identity"); ....
Hi guys, I am new to use Database Tuning Advisor. Right now I just save a workload as a sql and runs it in Database Tuning Advisor. The script that I wrote is kinda simple ( Select EID,EName,EDescription From Events Where EID = 300) . However, after I finish analyzed it, there's nothing to display on the recommendations session. Is it suppose got any details in it ? Or does it mean I no need to tune my query performance ? Hope someone can help me out here. Thx guys.
Best Regards,
Hans
Hi all
I'm a newbie with Visual studio 2005 .Recently i installed it ,AFAIK sqlServer 2005
also is shiped with vs2005.after installing vs2005 i have the following entries in my
StartMenu/All Programms/
1) Microsoft Sql Server 2005
Configuration Tools
sql Server Configuration Manager
Sql Server Error and usage Reporting
SQL Server Surface Area Configuration
2) Microsoft Visual studio 2005
.....
........
Apparently there isn't any ENTERPRISE MANAGER and query analyzer.?
By the way in Visual studio IDE i noticed "Server Explorer" nexed to "Solution Explorer"
but i didn't find any Database entry or "Create New Database" or some thing like this.!!!
Could any one help me.? i want to Create Database, Tables,....
Thanks in advance.
Regards.
The primary database i'm responsible for has started to grow super fast. Every couple of days is growing by 10% (which matches with the db settings). But, the recent growth doesn't match with the historical growth. It took a couple of months to grow from 7 to 8 GB, but it has grown to about 24 Gb in the last 2 months. Bottom line - trust my assertion that it's growing alarming fast.
I need help determine what objects are fueling the growth. If I know the objects, I can probably determine the cause. From a flip-side, it might be legit data stored very poorly. I'm open to any ideas...but I need to get ahead of this problem in the next week or so...or I'm going to run out of room on the hard drive and could start to affect my users.
Please send my any ideas you might have.
Thanks,
alex8675
HI
I have this complex logical model with 60 tables which i have converted to phycical model and into sql 7.0 database now the database is completely new for a new internet project without even single row of data now i have to optimise database and determine if the indexes created by the the designing team are good in sql 7.0 will the give me optimised database or not . I just dont know what to do
1) is it possible for me to use indextuning wizard without the data
whenever i try to to use indextuning wizard it askes me to use a workload file or make workloas file useing a trace as i do not have the data i just have to capture a trace by saying select * from <table>
as the work load file does not return any records the index tuning wizard does not suggest any changes In the indexing
tell me HOW DO I DETERMINE WHAT INDEXES TO CREATE FOR A OPTIMISED DATABESE ?
2) IT IS NOT POSSIBLE FOR ME TO PUT DATA IN ANY ONE TABLE AS ALL THE TABLES HAVE fk AND pk RELATION SHIP so what do i do
3) WHAT DOES dba normally do when he recives an empty data base to optimise
4) where do i make views to optimise the data
5) how do I populate data in so many tables
will puting dummy data help ?
Can some one help me with some SP which will populate the table with dummy data by some kind of do while loop with counter incrementing with 1000 rows
please help me
and if i am not clear
pl tell ask me the questions on my default email or
johnusa44@hotmail.com
john or G2
Hi experts! I would like to ask for some help regarding Database Engine Tuning Advisor. I was trying to create Session Monitor then I choose TABLE as a workload. Then after creating and selecting the corresponding setup then I start the analysis, during the analysis it prompted an error?
Error MSG:
The specified workload(file or table) has no tunable events. Events must be one of the following types - SQL:BatchStarting, SQL:BatchCompleted, RPC:Starting, RPC:Completed, SP:StmtStarting or SP:StmtCompleted for workload trace file or table.
But if I tried to use the Workload FILE instead of the table the session is successful and completed the analysis. My SQL current setup is client only, I was just accessing the server. Pls help me how to fix or do I need to configure something? Badly needed your help experts. Thanks in advance.
Tatas move
Hi,
I'm just testing the database engine tuning advisor.
I created a basic trace file in SQL Server profiler by randomly opening a number of views in the Adventureworks db. I then used the Database Engine Tuning advisor to analyse this.
The tuning advisors recommendation was to drop about 2-3 dozen indexes, which sounds like a really dumb idea.
Is it normal for DET to recommend that any index not referenced by the workload file be dropped?
Hello all,I want to use the SQL Server 2005 Tuning Advisor on our database, andI'm hoping someone here can just confirm the steps for me. We have a10GB database that has a number of applications hitting it constantly,all performing many SELECTs, INSERTs and UPDATEs. Are the followingthe steps I need to take?1. Stop all the applications hitting the database.2. Make a backup of the database.3. Start the profiler on the original database.4. Turn on all the applications again.5. Wait for a time before turning off the profiler and saving theprofile.6. Run the tuning wizard against the backup database using the savedprofile.Do I have to do this every time or am I missing something obvious? I'munder the impression I need the backup because our database has a highvolume of INSERTs and UPDATEs so I have to make sure the state of thedatabase matches the state of the profile.As a matter of interest, why is it not possible for the tuning wizardto analyse the database using the *current* activity? Why does it haveto be a 'pre-recorded' workload?Many thanks,Tommy.
View 4 Replies View RelatedOkay, so i'll just let you know right now, that i suck at making quries
i'm testing out a 2k5 eval copy of Sql Server (if i like, i'll migrate from 2k)
i have a db of almost 4 million names, with no indexes, and i'm trying to have
Sql tune a procedure that i wrote to see what it recommends for adjustments.
but for some reason it comes up with no results. any ideas why?
--Update
just noticed that if i type in the full query (not in the procedure form)
it recommends some indexes for me. is there any why to get recommendations
though the stored procedure below? i have about 112 quries that i want
to analyse, i could change them to actual quries if i had to, but it'd
take awhile
Current Table DDL:
USE [Test]
GO
/****** Object: Table [dbo].[mortgage] Script Date: 05/27/2007 01:52:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[mortgage](
[fips_state] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[state_abbrv] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[zip] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[zip_four] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[del_point] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[car_rte] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[city_abbrv] [varchar](13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[city] [varchar](28) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[addr_house_num] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[addr_pre_dir] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[addr_st_name] [varchar](28) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[addr_st_suff] [varchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[addr_post_dir] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[addr_unit_des] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[addr_unit_desnum] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[address1] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[fips_cnty] [char](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[county_name] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[census_tract] [char](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[census_block] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[lattitude] [char](9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[longitude] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[fips_ispsa] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[wealth_rating] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[time_zone] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[phone] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[homeowner] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[est_inc] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per1_fname] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per1_mi] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per1_lname] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per1_title] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per1_gender] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per1_dob] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per1_ageconf] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per1_age] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per1_msconf] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per1_ms] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per2_fname] [char](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per2_mi] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per2_lname] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per2_title] [varchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per2_gender] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per2_dob] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per2_ageconf] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[per2_age] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[child_pres] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[child_0_3] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[child_0_3_gender] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[child_4_6] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[child_4_6_gender] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[child_7_9] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[child_7_9_gender] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[child_10_12] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[child_10_12_gender] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[child_13_18] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[child_13_18_gender] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[religious_contrib] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[political_contrib] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[health_contrib] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[general_contrib] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[hm_purprice] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[hm_purdate] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[hm_year_build] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[donate_env] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[char_contrib] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[pres_cc] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[pres_perm_cc] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[oo_mtg_amnt] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[oo_mtg_lender_name] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[oo_mtg_rate] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[oo_mtg_rate_t] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[oo_mtg_loan_t] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[dnc] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[oo_refi_deed_date] [char](8) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[oo_refi_amnt] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[oo_refi_lender_name] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[oo_refi_rate_t] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[oo_refi_month_term] [char](4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[oo_refi_loan_t] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FullName] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[address] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Source] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[citystate] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[countystate] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[areacode] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Stored Procedure:
USE [Test]
GO
/****** Object: StoredProcedure [dbo].[search_orders_2] Script Date: 05/27/2007 01:53:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
create PROCEDURE [dbo].[search_orders_2]
@lowagenvarchar(10)=null,
@highagenvarchar(10)=null,
@highwealthnvarchar(10)=null,
@lowwealthnvarchar(10)=null,
@highpurpricenvarchar(10)=null,
@lowpurpricenvarchar(10)=null,
@lowmtgamtnvarchar(10)=null,
@highmtgamtnvarchar(10)=null,
@lowincomevarchar(3)=null,
@highincomevarchar(3)=null,
@marriedstatvarchar(3)=null,
@lowyearbuildvarchar(4)=null,
@highyearbuildvarchar(4)=null,
@mtgpresstatvarchar(3)=null,
@lenderpresstatvarchar(3)=null,
@ratetsvarchar(3)=null,
@loantsvarchar(10)=null,
@childstatvarchar(3)=null,
@homeownerstatvarchar(10)=null,
@lowpurdatevarchar(8)=null,
@highpurdatevarchar(8)=null,
@addresstype varchar(3)= null,
@SPSirvarchar(3)= null,
@debttypevarchar(3)= null,
@dwelltypevarchar(3)= null,
@zipselectnvarchar(4000)=null,
@acselectnvarchar(4000)=null,
@stselectnvarchar(4000)=null,
@cityselectnvarchar(4000)=null,
@counselectnvarchar(4000)=null,
@tablenamenvarchar(2)=null,
@orderbynvarchar(2)=null,
@Zipcode char(11)=null,
@milesfloat=null,-- as
@debug bit = 0 AS
DECLARE @sql nvarchar(4000),
@paramlist nvarchar(4000),
@highlatitude float,
@lowlatitudefloat,
@HighLongitudefloat,
@LowLongitude float,
@StartLatitudefloat,
@StartLongitude float,
@LatitudeRange float,
@LongitudeRange float
if @miles>0
SELECT @StartLatitude = lat, @StartLongitude = lng FROM zipinfo2 WHERE zip = @Zipcode
Set @LongitudeRange = @Miles / (((Cos(@StartLatitude * pi() / 180) * 6076.0) / 5280.0) * 60)
Set @LatitudeRange = @Miles / 69.045454545454545454545454545455
Set @LowLatitude = @StartLatitude - @LatitudeRange
Set @HighLatitude = @StartLatitude + @LatitudeRange
Set @LowLongitude = @StartLongitude - @LongitudeRange
Set @HighLongitude = @StartLongitude + @LongitudeRange
set @sql = ''
if @orderby is null set @orderby='1'
if @tablename is null set @tablename='1'
if @spsir='1'
select @sql = @sql+ 'SELECT '
+case @orderby
when 1 then 'o.state_abbrv, count(o.state_abbrv) '
when 2 then 'o.county_name, o.state_abbrv, count(o.state_abbrv) '
when 3 then 'o.city, o.state_abbrv, count(o.state_abbrv) '
when 4 then 'o.zip, count(o.zip) '
end + 'as kount FROM dbo.'
+ case @tablename
when 1 then 'mortgage'
when 2 then 'homeowner'
when 3 then 'condnc'
when 4 then 'mailable'
end +' o with (nolock) left join dbo.Spanish s on o.per1_lname = s.lname'
if @spsir='2'
select @sql = @sql+ 'SELECT distinct '
+case @orderby
when 1 then 'o.state_abbrv, count(o.state_abbrv) '
when 2 then 'o.county_name, o.state_abbrv, count(o.state_abbrv) '
when 3 then 'o.city, o.state_abbrv, count(o.state_abbrv) '
when 4 then 'o.zip, count(o.zip) '
end + 'as kount FROM dbo.'
+ case @tablename
when 1 then 'mortgage'
when 2 then 'homeowner'
when 3 then 'condnc'
when 4 then 'mailable'
End +' o with (nolock) inner join dbo.Spanish s on o.per1_lname = s.lname'
if @spsir='ALL' or @spsir is null
SELECT @sql = 'SELECT '
+case @orderby
when 1 then 'o.state_abbrv, count(o.state_abbrv) '
when 2 then 'o.county_name, o.state_abbrv, count(o.state_abbrv) '
when 3 then 'o.city, o.state_abbrv, count(o.state_abbrv) '
when 4 then 'o.zip, count(o.zip) '
end + 'as kount FROM dbo.'
+ case @tablename
when 1 then 'mortgage'
when 2 then 'homeowner'
when 3 then 'condnc'
when 4 then 'mailable'
End + ' o with (nolock)'
select @sql=@sql+' WHERE 1 = 1'
if @spsir='1'
select @sql = @sql+ ' and s.lname is null'
IF @lowage<>0 and @lowage is not null
SELECT @sql = @sql + ' AND o.per1_age >= @xlowage'
IF @highage<>0 and @highage IS NOT null
SELECT @sql = @sql + ' AND o.per1_age <= @xhighage'
IF @lowincome<>'DF' and @lowincome IS NOT NULL
SELECT @sql = @sql + ' AND o.est_inc >= @xlowincome'
IF @highincome<>'DF' and @highincome IS NOT NULL
SELECT @sql = @sql + ' AND o.est_inc <= @xhighincome'
IF @highwealth<>0 and @highwealth IS NOT NULL
SELECT @sql = @sql + ' AND o.wealth_rating <= @xhighwealth'
IF @lowwealth<>0 and @lowwealth IS NOT NULL
SELECT @sql = @sql + ' AND o.wealth_rating >= @xlowwealth'
IF @highpurprice<>0 and @highpurprice IS NOT NULL
SELECT @sql = @sql + ' AND o.hm_purprice <= @xhighpurprice'
IF @lowpurprice<>0 and @lowpurprice IS NOT NULL
SELECT @sql = @sql + ' AND o.hm_purprice >= @xlowpurprice'
IF @lowyearbuild<>'DF' and @lowyearbuild IS NOT NULL
SELECT @sql = @sql + ' AND o.hm_year_build >= @xlowyearbuild'
IF @highyearbuild<>'DF' and @highyearbuild IS NOT NULL
SELECT @sql = @sql + ' AND o.hm_year_build <= @xhighyearbuild'
IF @lowmtgamt<>0 and @lowmtgamt IS NOT NULL
SELECT @sql = @sql + ' AND o.oo_mtg_amnt >= @xlowmtgamt'
IF @highmtgamt<>0 and @highmtgamt IS NOT NULL
SELECT @sql = @sql + ' AND o.oo_mtg_amnt <= @xhighmtgamt'
IF @lowpurdate<>'DF' and @lowpurdate IS NOT NULL
SELECT @sql = @sql + ' AND o.hm_purdate >= @xlowpurdate'
IF @highpurdate<>'DF' and @highpurdate IS NOT NULL
SELECT @sql = @sql + ' AND o.hm_purdate <= @xhighpurdate'
IF @marriedstat in ('M','S')
SELECT @sql = @sql + ' AND o.per1_ms = @xmarriedstat'
IF @ratets in ('V','F')
SELECT @sql = @sql + ' AND o.oo_mtg_rate_t = @xratets'
IF @homeownerstat in ('H','R')
SELECT @sql = @sql + ' AND o.homeowner = @xhomeownerstat'
IF @childstat in ('Y','N')
SELECT @sql = @sql + ' AND o.child_pres = @xchildstat'
IF @lenderpresstat ='Y'
SELECT @sql = @sql + ' AND o.oo_mtg_lender_name<>'' '''
IF @lenderpresstat ='N'
SELECT @sql = @sql + ' AND o.oo_mtg_lender_name='' '''
IF @mtgpresstat='Y'
SELECT @sql = @sql + ' AND o.oo_mtg_amnt>0'
IF @mtgpresstat='N'
SELECT @sql = @sql + ' AND o.oo_mtg_amnt=0'
IF @loants<>'DF' and @loants IS NOT NULL
SELECT @sql = @sql + ' and o.oo_mtg_loan_t in ( ''' + REPLACE( @loants, N',', N''',''' ) + N''' )'
if @dwelltype='S'
SELECT @sql = @sql + ' and o.addr_unit_desnum='' '''
if @dwelltype='M'
SELECT @sql = @sql + ' and o.addr_unit_desnum<>'' '''
if @DebtType='1'
select @sql = @sql + ' and o.pres_cc=''y'''
if @DebtType='2'
select @sql = @sql + ' and o.pres_perm_cc=''y'''
if @addresstype in ('1','3')
select @sql = @sql + ' and o.address1<>'' '''
if @acselect <> 'DF' or @stselect <> 'DF' or @cityselect <> 'DF' or @counselect <> 'DF' or @zipselect <>
'DF' or @miles<>0
SELECT @sql = @sql + ' and (1=2'
IF @acselect<>'DF' and @acselect IS NOT NULL
SELECT @sql = @sql + ' OR o.areacode in ( ''' + REPLACE( @acselect, N',', N''',''' ) + N''' )'
IF @stselect<>'DF' and @stselect IS NOT NULL
SELECT @sql = @sql + ' OR o.state_abbrv in ( ''' + REPLACE( @stselect, N',', N''',''' ) + N''' )'
IF @cityselect<>'DF' and @cityselect IS NOT NULL
SELECT @sql = @sql + ' OR o.citystate in ( ''' + REPLACE( @cityselect, N',', N''',''' ) + N''' )'
IF @counselect<>'DF' and @counselect IS NOT NULL
SELECT @sql = @sql + ' OR o.countystate in ( ''' + REPLACE( @counselect, N',', N''',''' ) + N''' )'
IF @zipselect<>'DF' and @zipselect IS NOT NULL
SELECT @sql = @sql + ' OR o.zip in ( ''' + REPLACE( @zipselect, N',', N''',''' ) + N''' )'
if @miles>0
select @sql = @sql+ ' or o.zip in (SELECT z.zip FROM dbo.zipinfo2 z WHERE (z.Lat <= @HighLatitude
AND z.Lat >= @LowLatitude) AND (z.Lng <= @HighLongitude AND z.Lng >= @LowLongitude))'
if @acselect <> 'DF' or @stselect <> 'DF' or @cityselect <> 'DF' or @counselect <> 'DF' or
@zipselect <> 'DF' or @miles<>0
SELECT @sql = @sql + ')'
select @sql=@sql +case @orderby
when 1 then ' group by o.state_abbrv order by o.state_abbrv '
when 2 then ' group by o.state_abbrv,o.county_name order by o.state_abbrv,o.county_name '
when 3 then ' group by o.state_abbrv,o.city order by o.state_abbrv,o.city '
when 4 then ' group by o.zip order by o.zip '
end
IF @debug = 1
PRINT @sql
SELECT @paramlist = '@xlowageint,
@xhighageint,
@xlowincomenvarchar(4000),
@xhighincomenvarchar(4000),
@xmarriedstatnvarchar(4000),
@xhighwealthint,
@xlowwealthint,
@xhighpurpriceint,
@xlowpurpriceint,
@xlowyearbuildnvarchar(4000),
@xhighyearbuildnvarchar(4000),
@xlowmtgamtint,
@xhighmtgamtint,
@xmtgpresstatnvarchar(4000),
@xlenderpresstatnvarchar(4000),
@xratetsnvarchar(4000),
@xloantsnvarchar(4000),
@xchildstatnvarchar(4000),
@xhomeownerstatnvarchar(4000),
@xlowpurdatenvarchar(4000),
@xhighpurdatenvarchar(4000),
@xzipselectnvarchar(4000),
@xacselectnvarchar(4000),
@xstselectnvarchar(4000),
@xcityselectnvarchar(4000),
@xcounselectnvarchar(4000),
@highlatitude float,
@lowlatitudefloat,
@HighLongitudefloat,
@LowLongitude float,
@StartLatitudefloat,
@StartLongitude float,
@LatitudeRange float,
@LongitudeRange float'
EXEC sp_executesql @sql, @paramlist,
@lowage,
@highage,
@lowincome,
@highincome,
@marriedstat,
@highwealth,
@lowwealth,
@highpurprice,
@lowpurprice,
@lowyearbuild,
@highyearbuild,
@lowmtgamt,
@highmtgamt,
@mtgpresstat,
@lenderpresstat,
@ratets,
@loants,
@childstat,
@homeownerstat,
@lowpurdate,
@highpurdate,
@zipselect,
@acselect,
@stselect,
@cityselect,
@counselect,
@highlatitude,
@lowlatitude ,
@HighLongitude,
@LowLongitude,
@StartLatitude,
@StartLongitude,
@LatitudeRange,
@LongitudeRange
Query to analyse:
Exec Search_orders_2
@mtgpresstat = 'Y',
@homeownerstat = 'H',
@stselect = 'PA,RI,SC',
@addresstype = '2',
@spsir = 'ALL',
@debttype = 'ALL',
@dwelltype = 'ALL',
@tablename = '1',
@orderby = '1';
Hi to all,
I am using SQL 2005, when i am connecting to Database Engine Tuning Advisor i am getting an error messsage like this
Failed to open a new connection. Database Engine Tuning Advisor does not support SQL Express. (DTAClient)
Help me to slove my problem.
Thanks in advance.
Regards,
Jose.P
Hi,I find much regrettable that Database Engine Tuning Advisor be not part ofMS-Express Edition ... A server without such help is not a server.Besides, you've got the tutorials, but not the tool to play with :-(((Regards,JM Blaise
View 1 Replies View RelatedI was trying to get some indexing recommendations from the Database Engine Tuning Advisor by consuming a trace file with known indexing issues. After several minutes all it did was create a report or two which list existing indexes. Not very useful. I must be doing something wrong.
A message displays that "75% of the consumed workload has syntax errors." I guess it can't take a regular trace (.trc) file?
I am conderned that the trace file I used did not contain what the Advisor needs. Do I need to setup the trace a special way to support the Advisor?
Also, what does "Database for workload analysis" mean? I chose tempdb but there is no Help, and BOL is a maze of small uninformative snippets . . .
Thanks,
Michael
I have a very irritating error with Database Engine Tuning Advisor. While processing traces from Sql Profiler I keep getting trace log full of syntax error of most of queries - some of them very simple and ALL of them perfectly correct ( I check them separatly in QueryAnaliser ) - program gives recomendtation when I enter queries one by one - in that case the syntax is correct for Database Engine Tuning Advisor, but I just can't imagine why quries given to analysis in the set aren't correct? I dont' want to enter trace query by query (while I have thousends of them) !
It all seem like a Database Engine Tuning Advisor bug, any ideas about this problem?
Joanna
Hi to all,
I am using SQl 2005, when i am connecting to Database Engine Tuning Advisor i am getting an error message like this..
Failed to open a new connection. Database Engine Tuning Advisor does not support SQL Express. (DTAClient)
Help me to solve my problem. Thanks in advance.
Regards,
Jose.P
Hi,
I am experiencing a Named Pipes Provider error 40 message as detailed below. I get this when I'm running the dta utility on a production server which is trying to remote onto a test server to use the metadata and optimizer there.
The error reported is...
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
(note: there is no (Microsoft SQL Server, Error: xxx) reported at the end of the error string)
The error results from the following cmdline syntax run from the production server.
dta -A 0 -E -e C:TuningLog.xml -F -ix C:DTA.xml -N OFF -of C:outputdta.sql -rl ALL -s DTA_20071205
Prod box: 64bit SQL2005 SP2 Std Ed Named instance, TCPIP and Named Pipes enabled, SQL Browser service running.
Test box: 32bit SQL2005 SP2 Std Ed Default instance, Listening for local and remote connections on TCPIP and Named Pipes (configured through the SAC), SQL Browser service running. From the SQL Log on the test box I can see TCPIP is listening & Named pipes is listening on \.pipesqlquery
There is no firewall configured on either box.
I can connect to the test server from the prod box using sqlcmd -E -S<servername>
I can ping the test server from the prod box
Would you have any idea what I'm doing incorrectly? The genericity of the error message doesn't give me much of a clue and the resolutions I've read all revolve around SQLBrowser, surface area config, and SQL Express which I've covered off.
many thanks
Matthew Eames
Does anyone know how to DTA to correctly read the output from the Profiler?
I get the error:
TITLE: DTAEngine
------------------------------
50% of consumed workload has syntax errors. Check tuning log for more information.
------------------------------
BUTTONS:
OK
------------------------------
And the log is full of lines like:
E000 SELECT COUNT(*) FROM Pictures WHERE AdRecId = 16329 2 [Microsoft][SQL Native Client][SQL Server]Invalid object name 'Pictures'.
E000 exec GetAd @RecId=15282 8 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'GetAd'.
E000 exec GetAd @RecId=15385 4 [Microsoft][SQL Native Client][SQL Server]SHOWPLAN permission denied in database 'xxx'.
Does anyone know what causes this?
I do have showplan permission on the login I use for tuning.
Hello,
I am sure you have heard of Community server - if not you are just using it ;)
I decided to try to optimise the performance of my site, run a trace and then DETA.
And I am getting errors like these:
E000 exec dbo.cs_user_Get @UserName=N'jded',@UserID=0,@IsOnline=0,@LastAction=N'',@SettingsID=1000 122 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_user_Get'.
exec dbo.cs_thread_IsTracked @ThreadID=5969,@UserID=28236,@SettingsID=1000,@IsTracked=@p4 output
select @p4 1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_thread_IsTracked'.
E000 declare @p4 bit
set @p4=0
exec dbo.cs_thread_IsTracked @ThreadID=414,@UserID=1001,@SettingsID=1000,@IsTracked=@p4 output
select @p4 1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_thread_IsTracked'.
E000 exec dbo.cs_Section_GetSectionIDByPostID @SettingsID=1000,@PostID=44641 1 [Microsoft][SQL Native Client][SQL Server]Could not find stored procedure 'dbo.cs_Section_GetSectionIDByPostID'.
The "trouble" is that those sprocs do exist and that the site apparently is working fine. But not for DETA. As far as DETA is concerned... 54% of my processing power is used to serve syntax errors!
A couple of hints.
The database was an upgrade from 2000.:
- I changed the compatibility level to 2005 but no luck there. I tried with a brand new database, and the errors keep cropping up.
B. The errors were observed in a kit that comprises of a 32bit IIS and 64bit SQL2005 and thought that it had to do with the connectivity of those two.
- I run the traces in one (32bit) server that hosts both IIS and SQL and I am getting the same errors.
Any help will be greatly appreciated.
Thank you.
I would like to find Query to retrieve User DataBase Startup Time from SQL server that we see in the Sql Server Log.
I was able to find the Server Startup Time but not the individual Databases .
command in principal server
ALTER DATABASE database name
SET PARTNER = 'TCP://<mirror_server_name>:5022'
return:
Msg 1413, Level 16, State 1, Line 1
Communications to the remote server instance 'TCP://<mirror_server_neme>:5022' failed before database mirroring was fully started. The ALTER DATABASE command failed. Retry the command when the remote database is started.
This problem is only in production database any testing database include adventureworks mirroring sucessfuly. Why is problem:?
size? database > 9GB
slow HW? Principal database Intel D 3Ghz,4GB RAM, 4x 15k RPM HDD RAID 5 / Mirror database 2x Xeon 3Ghz,4GB RAM,6x HDD 10k RPM RAID 10
Slow LAN? both servers connect 1Gb/s
please help me
THX
Dear All,
This is my first time posting in this forum, so forgive me if my post is out of place. If so, let me know.
I am trying to get started setting up a development server on Windows 2000. These are the componants I have installed so far:
- IIS 5
- MSDE Database Server
- .NET Framework 1.1
- MDAC (Microsoft Data Access Componants)
- ASP.NET Web Matrix
I have also downloaded a quickstart example, TimeTracker, to learn from.
Here is my problem: When I try to install TimeTracker, it does not detect a database connection. Also, when I try to connect to a database in Web Matrix, it says I need to have client tools installed. What are these and how to I get them?
Is there anyone that can give me a hand with this? Is it a matter of running the MSDE service? If so, how do you do this?
My apologies for not being more knowledgable, but thanks for helping me get started.
Sincerely,
Chris
I have just started programming using sql server. how do i get stated?
the basics are needed thanks for ur advice
I just installed SQL server 2005 with SP2 on Vista business.
When I open Management Studio, and try to Register a new server, I get this message:
Cannot connect to MyServer.
Additional information:
An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could nor open a connection to SQL Server) (Microsoft SQL Server, Error: 1231)
When I open SQL Server 2005 Surface Area configuration and click on Add New Administrator, there are no Available privileges. Not sure if this is relevant.
Don't know what to do, any help will be appreciated.
I have just installed SQL Server at home. Had a bit of experience at work, but at home I have to get started from scratch.
Enterprise manager says 'Connection could not be established'. In this error it talkes about 'registration properties'. I am (by default ?) a member of SQL Server group, but I have no sub items, like databases. And how do I get to where I can edit stored procedures ?
As you can see I'm quite a novice, but with a little nudge I can would a lot out myself. But not all. If you can help me now, I'll be back soon.
Thank you so much.
Regards
Robert
i have roughly 10-15 access databases that i am trying to run through sql server express, i installed this thing thinking it would would "just work minus a little configuration", what a misconception.
so do tell, what do i need to do. i have sql server express running, it is configured to allow remote connections. i know the path's of these access databases. how do i attach them. i saw something about the command line interface but im not interested in that because of the number of databases i will need to manage, not to mention trying to add an additional one 6 months down the road and i will be lost all over again. i thought about downloading SQL Server Management Studio thinking it would be a "gui side" to the CLI version. while it was downloading, it said i need XX program, i go to that page, it says i need XX and XX first, it is turning into a pyramid scheme to install a billion programs.
so point blank, i want to share some access databases to my local lan, i have sql server express installed and running. what do i need to finish this out. the simplest and smallest footprint solution is needed.
all this because the program im using cant do remote databases. ugh.
Hi,
I am trying to make a small vb.net program in visual studio 2005 edition. I would like to have a SQL database with it.
I have the Microsoft Visual Studio and SQL Server 2005 installed on my PC. I am running XP but will be moving it all to a new pc running Vista next week.
How to I get the SQL database started ?
I am not using the express edition.
This is a repost of my post on the VB Express board.
I would like your opinions.
I am a network guy. I learned basic years ago. I can do some html edits as needed to change sites.
Today I would like to start the long process of learning VB with .net applications. I thought I could start with Office developer xp to learn VB. I have that package.
I also would like to be able to build SQL quaries and integrations or automate and manipulate data exports and imports later down the road.
Example: Export various order and customer information from a shopping cart and import it in to Quickbooks or MS Accounting.
First off I need to be able to build web sites and I know I want to grow into VB and SQL.
What should I do?
Learn HTML from web monkey?
Start with Office xp developer Tutorials?
Start with VB Express?
Buy a book?
Can someone tell me where to start and when to move to the next language?
What is the best resource to get to it, without the bla bla bla and a commnd/syntax refference?
How do I mix Html, CSS and .Net (VB)...in the learning process?
I would like to get the fast track for the long haul.
Thanks so much.
I am looking to learn about reporting with SQL Server for my company
Currently we have an applicaiton running on SQL Server 2000 and have SQL Server 2000 Reporting Services installed. However, I have VB Express and Web Development Express 2005.
How do I get started here? What versions "work" with what? Do I need to either upgrade the DB to SQL 2005 or find VB.net 2003, or can I use what I have to get started?
hello,
i own about a dozen interactive forums (see example at http://ConcealedCarryForum.com for one of them) that i host on a windows 2000 server dedicated server out of my home on a commercial cable backbone. the forums are ASP using Access DBs. we have outgrown the capabilities of Access and are getting complaints from DB lag during peak usage, and i must upgrade my DB to ensure the QOS end users get from my forums. i have no intention of switching over to php/MySQL/linux formats.
i tried unsuccessfully to get MySQL to play nicely in a windows environment, and recently learned that MSSQL offers free versions, the compact and the express. trouble is, i know absolutely nothing about DB management. Access is idiot proof so i have used it as long as i could. now i find myself in a scramble to learn how to create, employ, and use a more powerful DB.
for my intended usage (running about a dozen DBs on a dedicated server), will compact or express be the appropriate version to start learning? for my intended usage, is there any benefit to downloading/installing/learning the additional tools that are also available? if so, which tools? where do i learn how to create new DBs, and what tools do i need to do this? most tech papers ive seen on MSSQL assume a prior working knowledge of MSSQL DB management and im coming into this completely ignorant but willing to dedicate myself to learning.
answers, advice, etc. very much appreciated.
Hello,
I'm studying some articles about DMX and i have a question. DMX statements is only for prediction?
Can i create a model and run a classification/clustering/decision tree algorithm through DMX instead of prediction?Because all the examples i found are talking about prediction.
Are there any other sources i can study, so i can have a better understanding of what DMX does?
Thank you in advance!
I am using this program for the first time but as it has no direct interface on its own, I would like to ask if anyone could help me get a database up and running.Nothing fancy but simple and functional for my staff to use for basic csv searches and so forth. skywalkerza@highveldmail.co.za