Today we were trying to use restore wizard (recovery advisor) to restore a database which is over 80 GB, but the wizard won't load or start. However the wizard dialog box opens without any problems with all other databases stored in the instance. Is there any problem or reason to why the restore wizard won't come up?
This is SQL 2012 enterprise version we are running.
Last week I backed up my SQL Server by using BE 2012. I named the file "SQL Server BAK" which contained copies of my SQL Server databases. A few days ago I lost some part of my data due to accidental deletion. I backed it up, so I tried to restore the database from the .bkf file. The problem comes here, when I try to to restore my .bkf file, it becomes inaccessible.what causes this?
View 7 Replies View RelatedI have an SQL .bak file and I would only like to restore specific columns as one of the columns is a free text field and is substantially increasing the size of the file. I can't restore it due to disk space constraints so dropping the column isn't possible if I can't get the table into a database locally.
View 1 Replies View RelatedOne of our DB is in recovery state due to insufficient space in drive where the log files resides. Is there anyway we can come out of this situation?
View 9 Replies View RelatedDiscovered that a geo-spatial AlwaysOn HA database (1 of 4) was not synchronizing as at a point in time earlier in the day. Suspend Data Movement appears to be working perpetually without finishing. The SQL Server services is in a Pending Changes state after an attempt to restart it from SQL Configuration Manager. The Cluster Dashboard says it is in a Not Synchronizing state, with only the one database in question having a yellow triangle, all 3 others show green.
The warning for the cluster is:At least one availability database on this availability replica has an unhealthy data synchronization state. If this is an asynchronous-commit availability replica, all availability databases should be in the SYNCHRONIZING state. If this is a synchronous-commit availability replica, all availability databases should be in the SYNCHRONIZED state. There is no abnormal data movement from the primary to the seconday.The warnings for the unhealthy database are:
The data synchronization state of this availability database is unhealthy. On an asynchronous-commit availability replica, every availability database should be in the SYNCHRONIZING state. On a synchronous-commit replica, every availability database should be in the SYNCHRONIZED state.Either a database administrator or the system has suspended data synchronization on this availability database.So how to get this database back to synchronizing state?
I have a SQL cluster which ran out of disk the other day. I threw plenty more disk at the SAN. However, 1 of the databases came back with Recovery Pending and has been over 24 hours. I have tried to backup the database with no success. I don't have a clean backup from the day it occured so would have a good amount of work missing.
Running a dbcc check returns the following:
Msg 945, Level 14, State 2, Line 1
Database 'sqldb' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
I have not tried a dbcc check with REPAIR_ALLOW_DATA_LOSS as there seems to be a high risk of losing whatever was going on. how I can get the db back online?
We are trying to restore from a backup (.bak) of a sql 2005 database with full text catologs to sql 2012.
We are getting errors saying that the backup file context are different from what we are trying to restore to, even if we try to create a new database from the backup.The new database is just a test database. The source is a production database with full cataologs.
The normal restore from a bak file does not seem to work.
We dont need the full text catalogs in the new database, but cannot change settings in the source production database.
Is there a way to take a backup of the sql 2005 database without the full text cataologs being included ? , without changing setting in the source database beforehand ?
We installed the SQL Server 2012 GUI tools, including "Upgrade Advisor," on a new virtual machine. We can connect to the old SQL Server 2008 R2 clustered databases just fine over port 1433 using SQL Server Management Studio 2012, but Upgrade Advisor is a mixed bag. Here are the steps we are taking:
- Click on "Start," "All Programs," "Microsoft SQL Server 2012," "SQL Server 2012 Upgrade Advisor"
- Fill in the server name with a valid SQL Server Network Name
- Select "SQL Server" as the only component to be analyzed
- Fill in the instance name with a valid named instance
- Use Windows Authentication (the default)
- The "SQL Server Parameters" window displays valid database names, meaning that we have a successful connection to the desired SQL Server instance at this point
- Leave all of the databased selected (the default)
- Click the "Run" button
- The "Upgrade Advisor Progress" window spins for a while and eventually generates the following error:
TITLE: mscorlib
------------------------------
The network path was not found.
------------------------------
BUTTONS:
OK
------------------------------
Why would Upgrade Advisor be able to display the database names after making a successful connection only to fail later saying that it cannot find the network path?
Hi,
We want trace ALL activity on prod
server between 3:00 AM and 3:15 AM
Profiler has stop time option ,but
no start option.
I am looking for example how it could be done
Thank you
Alex
In the case of a manual failover, what happens to open transactions. Are they killed (rolled back), completed, a little bit of both?
Does the status of the query has any impact (are running queries handled differently then waiting ones, does the type of wait have an impact...)
I am using synchronous commit mode and all I seem to find is reference to the potential or absense of loss of data.
I have an ASP.NET project that uses a ASPNETDB.MDF file. This file is attached with a SQL Server Express everytime when I run the application, include debuging session. There are two debugging modes under Visual Studio 2005, through WebDev.WebServer or IIS. Both ways worked fine with this project until yesterday. Right now, whenever I try to debug it with WebDev.WebServer, it will show me an error:
Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.
But if I debug it with IIS it works fine. Also, using the same machine and same installation of Visual Studio 2005 I can debug another project with WebDev.WebServer, that also uses a .mdf file (a different file). So, seems to me I jsut have problem with one mode and one project. The only thing that I did yesterday that I think may be relevant was that I reinstalled Visual Studio 2005 SP1 to fix another issue. Anyone has an idea? Thanks!
Pages on a full recovery model database corrupted, need to ensure data loss is minimal for restore operation am thinking about restoring the latest full backup.
View 4 Replies View RelatedDisaster Recovery Options based on the following criteria.
--Currently running SQL 2012 standard edition
--We have 18000 databases (same schema across databases)- majority of databases are less than 2gb-- across 64 instances approximately
--Recovery needs to happen within 1 hour (Not sure that this is realistic
-- We are building a new data center and building dr from the ground up.
What I have looked into is:
1. Transactional Replication: Too Much Data Not viable
2. AlwaysOn Availability Groups (Need enterprise) Again too many databases and would have to upgrade all instances
3. Log Shipping is a viable option and the only one I can come up with that would work right now. Might be a management nightmare but with this many databases probably all options with be a nightmare.
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
We have an issue with a 3 node SQL 2012 Always on availability group. Normal operation is node 1 (primary replica) with node 2 and node 3 as secondary replicas.After some patching, SQL wasn't running on node 1 hence the AG flipped over to node 2. This went unnoticed for some time and the transaction log for one of the AG databases became full on node 2 and node 3. (I think this is because it couldn't commit the transactions on node 1 so couldn't truncate it's t-log?) The DB is using synchronous replication btw.So I started SQL on node 1 and flipped the AG back to node 1 (with a data loss warning but I accepted this).Now the issue is that on node 2 and 3, the DB in question is stuck in a "Reverting / In Recovery" State. I've tried various commands such as ALTER DATABASE SET ONLINE, RESTORE DATABASE WITH RECOVERY etc but these fail stating unable to obtain a lock on the DB.
The weird thing is that on node 1 the state of the DB is "synchronised".how to resolve this issue on node 2 and 3? I've left them overnight (in case they were rolling back transactions, the DB is fairly large) but nothing seems to have happened. remove the DB from the AG in node 2 and 3 and add it back in again, ie recreate the replication?
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
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 RelatedI get the below error only when my IDE open. It connects well when it is found closed.
[SqlException (0x80131904): Cannot open user default database. Login failed.Login failed for user 'JPASPNET'.]
I could solve this by giving the logged in windows user to impersonate under IIS window > WEBSITE > ASP.NET tab > EDIT CONFIG > APPLICATION tab
But I wish someone could give me the proper solution.
I almost tried all from giving ASPNET user as a administrator to configuring the same in Express management tool.
Environment: XP pro, VWD and SQL Express
Okay, 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 have sample data like 'J.S.xxxx','J.xxxx.'
Want to separate the . in the middle of word with single space and display results in following way.
J. S. xxxx
J. xxxx
Need the code in optimized way.
3rd party company set up SQL 2008 R2 SP1(awhile ago, years), they disabled SA no other logon was created with Admin rights, Database is Mixed Mode, never had a DBA. the database is from a Transactional system. Now comes me. I need to set up backups, maintenance etc..
I have been provided System AdminLocal Admin rights on the Server. The Server has been set up like a Domain Controller. I have tried all of the command line options that I know and I can find. I have not been able to get any to work, they all fail login to SQL. Before I take the route of reinstalling SQL on the server I want to make sure I haven't missed any thing, especially since this is a 100% production environment with 3rd party processes and applications attached that are not documented or supported. (nightmare)
I would prefer a cmdline or PowerShell script (ps2).I figure that the holiday is coming up so whatever I will end up needing to do I will be able to complete over the holiday weekend when it is down.
One set I tried as "run as Admin"
SQLServr.Exe –m
SQLCMD –S <Server_NameInstance_Name>
CREATE LOGIN ‘<Login_Name>’ with PASSWORD=’<Password>’
GO
SP_ADDSRVROLEMEMBER '<Login_Name>','SYSADMIN'
I should point out the rest of our environments are SQL 2012
i have Two SQL 2012EE Node , it is installed in VCE Vblook 100 , now i want to move it to another hardware my question is " How i can i achive this task by move Windows 2012 R2 Cluster and SQL 2012 EE Cluster to another hardware".
View 4 Replies View RelatedI need a query to get the first value greater than a specific threshold value
LoadTemp1 LoadTemp2 LoadTemp3 LoadTemp4 TimeStamp
300 200 100 320 12-10-2013 13:30:29
100 250 113 340 12-10-2013 14:20:12
114 339 209 345 12-10-2013 14:45:01
I need to get the first value >= 340 threshold
I need to make a Benchmark on when the first LoadTemp crosses Threshold of 340 and capture the time.
I have a simple example of what I am trying to do. Here is some code to make a quick table to demonstrate:
create table #students
(
lastname varchar(50)
,firstname varchar(50)
,address1 varchar(50)
[Code].....
I want to select all the records, and them them be in alphabetical order first by lastname, then by firstname, then by address. HOWEVER, and this is the tricky part, I want to group names together that have the same address. So, in this example, I want the results to be in this order:
HallC6309 N Olive
HallP6309 N Olive <---- grouped with the C record because they have the same address
HallE5488 W Catalina <---- back to alphabetical by first name
HallJ7222 N Cocopas
How to remove Character From Column,Just Character. I want to work with number.
View 4 Replies View Related