Helpful Blogs - Take A Look Before Posting

Aug 29, 2007

I think we could solve quite a few questions much faster by posting helpful blogs here.
Some questions are asked over and over and the answer is typically: see this blog. People simply don't know it's out there.

So, if you are the author of a useful Reporting Services blog, or have found a Reporting Services blog particularly helpful, please post it here (keep it strictly to links that you feel solve a large number of RS questions).

If you would, also provide a short description as to the content of the blog.


I'll start with these:

Session Timeouts -- http://blogs.msdn.com/jgalla/archive/2006/10/11/session-timeout-during-execution.aspx
SSRS Setup -- http://prologika.com/CS/blogs/blog/archive/2007/07/12/ssrs-setup-woes.aspx
Complex SQL Issues -- http://spacefold.com/lisa/category/SQL-Server.aspx

View 2 Replies


ADVERTISEMENT

Blogs

Jul 23, 2005

Hi All,Can anyone recommend any blogs that should be read on a regular basis?ThanksMichael

View 1 Replies View Related

Was This Post Helpful

Mar 20, 2007

If u get any solution then plz share knowledge with me because I am new in SQL

View 3 Replies View Related

Database Engin Tuning Advisor, Not Helpful

May 27, 2007

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';

View 20 Replies View Related

DBCC Checkdb Might Soon Be Very Very Helpful To Me. MAYBE Hardware Problems

Nov 12, 2006

Hey guys. I've a question for you. I don't know where to start for the question but we have a SAN with 2 nodes and an activeactive cluster built on it with SQL 2005 ent edition installed. I've started running DBCC checkdb(db1,noindex) recently and am doing it every week. My question is this. I DON'T TRUST MY HARDWARE. I very often used to get the message that

'The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000b75e74000 in file 't:MSSQLDatafile1Index.ndf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.'

I also get the error described in http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/21/642314.aspx. I just finished running dbcc checkdb on my db today and luckily have come up with no errors.

I'm wondering what are the steps I should take to convince my higher ups that there are problems with Hardware and we are eventually going to have problems if something is not done about the hardware. I'e been taking diff and tran and full backups on this db but i don't want to go that route if I can get them convinced that it's not SQL. We have been on SQL 2005 for about 1 month now. This problems were there since 2000. Now, they are less recurring. Since, I've been on 2005, today is the first time I ran reorg
eindex for my db. Everything was fine. But, WHY ARE THESE I/O ERRORS/INFO OCCURING? All your help is appreciated. Thank you.

View 8 Replies View Related

Posting A Query?

Nov 15, 2007

Its very difficulty to find the link to post query...
In this forums site...pls tell me the location...where can I find the link to post a query...

View 1 Replies View Related

Posting Stopped ?!?!

Mar 31, 2004

It appears that posting of messages has stopped for at least an hour. I've never seen that happen in this forum during US daytime hours!

-PatP

View 4 Replies View Related

Why Has This Connect Posting Been Set To [Won't Fix]?

Jun 29, 2007

I have a connect posting here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=284301&wa=wsignin1.0 that has been set to Resolved (Won't fix). I'm very annoyed about that for starters because its something that is EASILY fixed but what's even worse is that there is no explanation as to WHY it has been set to this.



I have complained voraciously about the lack of feedback on Connect postings in the past, and to be fair it has improved, but this is going back to the bad old days.



Why has this been set to "Won't fix"?



-Jamie



[Microsoft follow-up]

View 3 Replies View Related

Need Help Posting To Sql Table With A Sproc

Jan 4, 2005

To all,

Here's the error:
Input string was not in a correct format.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.FormatException: Input string was not in a correct format.

Source Error:


Line 159:
Line 160:objConn.Open()
Line 161:objCmd.ExecuteNonQuery()
Line 162:objConn.Close()
Line 163:


Source File: C:FullerAviationSupplymanagecatalog.vb Line: 161

Stack Trace:


[FormatException: Input string was not in a correct format.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +194
managecatalog.SubmitProd(Object s, EventArgs e) in C:FullerAviationSupplymanagecatalog.vb:161
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1277




--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573





Here's the code from my vb file:


Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region


Protected WithEvents dgProductCatalog As datagrid
Protected WithEvents tbxProductName As TextBox
Protected WithEvents tbxInventory As TextBox
Protected WithEvents dgCatProd_Active As DropDownList
Protected WithEvents dlvendor As DropDownList
Protected WithEvents dlSubcat As DropDownList
Protected WithEvents btnLinkvend As Button
Protected WithEvents tbxshortdesc As TextBox
Protected WithEvents tbxlongdesc As TextBox
Protected WithEvents qtyPA As TextBox
Protected WithEvents pricePA As TextBox
Protected WithEvents qtyPB As TextBox
Protected WithEvents pricePB As TextBox
Protected WithEvents qtyPC As TextBox
Protected WithEvents pricePC As TextBox
Protected WithEvents sizeX As TextBox
Protected WithEvents dlX As DropDownList
Protected WithEvents sizeY As TextBox
Protected WithEvents dlY As DropDownList
Protected WithEvents sizeZ As TextBox
Protected WithEvents dlZ As DropDownList
Protected WithEvents weight As TextBox
Protected WithEvents dlweight As DropDownList
Protected WithEvents dgInsertProduct As Button
Protected WithEvents thumbfile As TextBox
Protected WithEvents mainfile As TextBox



Dim objDA As SqlDataAdapter
Dim objDS As New DataSet()
Dim objDX As New DataSet()
Dim objDV As New DataSet()
Dim objDU As New DAtaSet()
Dim objDW As New DAtaSet()
Dim objDT As DataTable
Dim objDR As DataRow
Dim objConn As SqlConnection = New SqlConnection(ConStr)

Sub Page_Load()

If Not IsPostBack Then
BindData()
End IF

End Sub

Sub BindData()


objDA = New SqlDataAdapter("GETCATALOG",objConn)
objDA.Fill(objDS, "MainCatalog")
objDT = objDS.Tables("Catalog")
dgProductCatalog.DataSource = objDS
dgProductCatalog.DataBind()


objDA = New SqlDataAdapter("GETSUBCAT",objConn)
objDA.Fill(objDX, "SubCat")
dlSubcat.DataSource = objDX
dlSubcat.DataValueField = "SubCat_ID"
dlSubcat.DataTextField = "SubCat_Name"
dlSubcat.DataBind()

objDA = New SqlDataAdapter("GETVENDOR",objConn)
objDA.Fill(objDV, "Vendor")
dlvendor.DataSource = objDV
dlvendor.DataValueField = "VendorID"
dlvendor.DataTextField = "CompanyName"
dlvendor.DataBind()

objDA = New SqlDataAdapter("GETSIZEUNITS",objConn)
objDA.Fill(objDU, "Units")
dlX.DataSource = objDU
dlX.DataValueField = "Size_ID"
dlX.DataTextField = "Size_Unit"
dlX.DataBind()

dlY.DataSource = objDU
dlY.DataValueField = "Size_ID"
dlY.DataTextField = "Size_Unit"
dlY.DataBind()

dlZ.DataSource = objDU
dlZ.DataValueField = "Size_ID"
dlZ.DataTextField = "Size_Unit"
dlZ.DataBind()

objDA = New SqlDataAdapter("GETWEIGHTUNITS",objConn)
objDA.Fill(objDW, "Units")
dlweight.DataSource = objDW
dlweight.DataValueField = "weight_unitID"
dlweight.DataTextField = "weight_Unit"
dlweight.DataBind()

End Sub

Sub SubmitProd(s As Object, e As EventArgs)

Dim objCmd As New SqlCommand("INSERTPRODUCT", objConn)
objCmd.CommandType = CommandType.StoredProcedure
objCmd.Parameters.Add("@prodName", tbxProductName.Text)
objCmd.Parameters.Add("@descShort", tbxshortdesc.Text)
objCmd.Parameters.Add("@descLong", tbxlongdesc.Text)
objCmd.Parameters.Add("@qtyPA", SqlDbType.int ).Value = qtyPA.Text
objCmd.Parameters.Add("@qtyPB", SqlDbType.int ).Value = qtyPB.Text
objCmd.Parameters.Add("@qtyPC", SqlDbType.int ).Value = qtyPC.Text
objCmd.Parameters.Add("@pricePA",SqlDbType.money).Value = pricePA.Text
objCmd.Parameters.Add("@pricePB",SqlDbType.money).Value = pricePB.Text
objCmd.Parameters.Add("@pricePC",SqlDbType.money).Value = pricePC.Text
objCmd.Parameters.Add("@vendorID", dlvendor.SelectedItem.Value)
objCmd.Parameters.Add("@inventoryAvail", SqlDbType.int).Value = tbxInventory.Text
objCmd.Parameters.Add("@imageThumb", thumbfile.Text)
objCmd.Parameters.Add("@imageLarge", mainfile.Text)
objCmd.Parameters.Add("@sizeX", sizeX.Text)
objCmd.Parameters.Add("@sizeY", sizeY.Text)
objCmd.Parameters.Add("@sizeZ", sizeZ.Text)
objCmd.Parameters.Add("@sizeUX", dlX.SelectedItem.Value)
objCmd.Parameters.Add("@sizeUY", dlY.SelectedItem.Value)
objCmd.Parameters.Add("@sizeUZ", dlZ.SelectedItem.Value)
objCmd.Parameters.Add("@weight", weight.Text)
objCmd.Parameters.Add("@prodActive", dgCatProd_Active.SelectedItem.Value)
objCmd.Parameters.Add("@weightU", dlweight.SelectedItem.Value)
objCmd.Parameters.Add("@SubCat_ID", dlSubcat.SelectedItem.Value)


objConn.Open()
objCmd.ExecuteNonQuery()
objConn.Close()

tbxProductName.Text=""
tbxInventory.Text=""
tbxshortdesc.Text=""
tbxlongdesc.Text=""
qtyPA.Text=""
pricePA.Text=""
qtyPB.Text=""
pricePB.Text=""
qtyPC.Text=""
sizeX.Text=""
sizeZ.Text=""
sizeZ.Text=""
weight.Text=""
BindData()

End Sub




And here's the Stored Procedure:

CREATE PROCEDURE INSERTPRODUCT
(
@prodName nvarchar(50),
@descShort nvarchar(3000),
@descLong nvarchar(4000),
@qtyPA int,
@qtyPB int,
@qtyPC int,
@pricePA money,
@pricePB money,
@pricePC money,
@vendorID int,
@inventoryAvail int,
@imageThumb nvarchar,
@imageLarge nvarchar,
@sizeX nvarchar,
@sizeY nvarchar,
@sizeZ nvarchar,
@sizeUX int,
@sizeUY int,
@sizeUZ int,
@weight nvarchar,
@prodActive int,
@weightU int,
@SubCat_ID int
)

AS

INSERT INTO ProductCatalog (ProductName, Desc_Short, Desc_Long, qty_PerA, qty_PerB, qty_PerC, Price_PerA, Price_PerB, Price_PerC, VendorID, InventoryAvail, image_Thumb, image_Large, Size_X, Size_Y, Size_Z, SizeUnit_X, SizeUnit_Y, SizeUnit_Z, weight, Prod_Active, weight_unit, SubCat_ID) VALUES (@prodName, @descShort, @descLong, @qtyPA, @qtyPB, @qtyPC, @pricePA, @pricePB, @pricePC, @vendorID, @inventoryAvail, @imageThumb, @imageLarge, @sizeX, @sizeY, @sizeZ, @sizeUX, @sizeUY, @sizeUZ, @weight, @prodActive, @weightU, @SubCat_ID)
GO


Any help would be greatly appreciated.

Regards,
Seth

View 2 Replies View Related

Nightly Job Has Been Running For Months. Now It Crashes Without Any Helpful Error Message.

Apr 11, 2007

2:30:03 AM SQL Server Scheduled Job 'Our Job Name' (0x888B36086CCCC34FB353B408DEBE0432) - Status: Failed - Invoked on: 2007-04-11 02:30:00 - Message: The job failed. The Job was invoked by Schedule 30 (Our Job Name). The last step to run was step 1 (Our Job Name).



Where do you begin to discover the cause of the crash?

View 6 Replies View Related

Posting An Image To SQL Server 2005

Jun 22, 2007

Hello Everyone I am trying to write image files to sql server using the following code. I have recieved the following error message
Failed to convert parameter value from a String to a Byte[].  Please help.  I am sure it is a problem gathering the iostream.  I am not very familiar.  Any help is gretaly appreciated.
Thanks in advance
Here is the code:
'Dim User As MembershipUser = Membership.GetUser(CreateUserWizard1.UserName)
'Dim t As TextBox = DirectCast(FormView1.FindControl("aspnet_UserID"), TextBox)Dim objConn As SqlConnection
Dim objCom As SqlCommand
If Me.FileUpload1.HasFile Then
Dim fileExtension As String
Dim fileOK As Boolean = False
fileExtension = System.IO.Path. _
GetExtension(FileUpload1.FileName).ToLower()Dim allowedExtensions As String() = _
{".jpg", ".jpeg", ".png", ".gif", ".mwv"}For i As Integer = 0 To allowedExtensions.Length - 1
If fileExtension = allowedExtensions(i) Then
fileOK = True
End If
Next
'TryDim imagestream As System.IO.Stream = FileUpload1.FileContent
Dim data() As ByteReDim data(imagestream.Length - 1)
imagestream.Read(data, 0, imagestream.Length)
imagestream.Close()objConn = New SqlConnection(strNewConnection)
objCom = New SqlCommand("insert into ProfileImagesAndDocs(aspnet_userid,img_name,img_data,img_contenttype)values(@aspnet_userid,@imagename,@Picture,@CategoryName)", objConn)
'---------------------------------------------
'this is the aspnet_useridDim useridparameter As SqlParameter = New SqlParameter("@aspnet_userid", SqlDbType.VarChar)useridparameter.Value = Me.aspnet_userid.Text
objCom.Parameters.Add(useridparameter)
'---------------------------------------------
'This is the image name Dim imagenameparameter As SqlParameter = New SqlParameter("@imagename", SqlDbType.VarChar)imagenameparameter.Value = Me.FileUpload1.FileName
objCom.Parameters.Add(imagenameparameter)
'----------------------------------------------
'this is the picture dataDim pictureParameter As SqlParameter = New SqlParameter("@Picture", SqlDbType.Image)
pictureParameter.Value = data
objCom.Parameters.Add(pictureParameter)
'----------------------------------------------
'this is the profile area or category i.e. video introDim categorynameParameter As SqlParameter = New SqlParameter("@CategoryName", SqlDbType.VarChar)
pictureParameter.Value = "IntroVideo"
objCom.Parameters.Add(categorynameParameter)
 
objConn.Open()
objCom.ExecuteNonQuery()
objConn.Close()
'bookmark
Label1.Text = "File uploaded!"
'Catch ex As Exception
Label1.Text = "File could not be uploaded."
End If

View 2 Replies View Related

Help On Execute Proces Task --posting Secoind Time

Feb 20, 2008



Hi,

I have a batch file which is located in different server. When I try to execute in BIDS it is running fine. But when I execute as SQL agent job it is not executing.

In the execute process task under executable I have supplied the batch file path . For example \112.89.93.9abcsample.bat.

It is executing fine when I execute this in BIDS supplying the above path under executable. But when I run this as SQL agent job it keeps on running and does not end and it is doing anything.

Do I need to mention any cmd.exe file under executable and give the path under arguments tab?

Any help is appreciated.

Thanks




View 10 Replies View Related

Data Flow Job Failing, Destination = Microsoft SQL Native Client, Error Message Not Too Helpful...

Dec 14, 2007

Hi there,

I have a Data Flow task which uses an XML File Source with six parellell Outputs, each going firstly to a Data Conversion Task, then the results of each end in a SQL Server Destination Object. (All using the SQL Native Client)

To eplain this further, the Xml file contains 6 different types of elements, the Dataflow splits out each type of element and processes them into different tables. The Data Transformation object exists only because the XML fields are Uni-code and the table fields are VarChar not nVarChar.


Initially using this setup I found that the Connection would timeout using the SQL Native Client so I changed the Timout on the Destination Objects to 0. This fixed the problem to some degree, however at present I can run the Pakage using the Visual Studio enviroment and everything works fine, no problem. When I run the Dtsx file using the SQL Server Agent, I end up getting the error below...



Error: 2007-12-14 14:33:19.16 Code: 0xC0202009 Source: Import XML File to SQL SQL - CP [2746] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Reading from

I understand that this error is somewhat of a 'catch all' and that the way the Native SQL Server Connection object works makes Error Capturing difficult. I have tried a few things which I will list as I'm sure they will be suggested...


I have played around with the 'MaxInsertCommitSize' property of the SQL Server Destination Objects to no avail (IE, changing to 50000, 10000, 1000 all of which resulted in the same problem)

I am running the ssis pakage from the server which is the destination

As mentioned above the Timeout on the SQL Server Destination Objects is set to 0

What I have already mentioned and still don'tt quite understand is that I can run the job successfully from the Visual Studio enviroment but as a job run off the SQL Server it fails...



Can

View 8 Replies View Related

How Do I Prevent SQL 2000 From Posting This Message To The Event Viewer - Application Log

Sep 12, 2005

How do I prevent SQL Server 2000 from posting successful backupcompletion messages to the Windows 2000 Application Event Log?I have scheduled jobs which backup my transaction logs on 50+ databasesand it always writes to the Windows 2000 application event log uponcompletion.Due to the frequency of the jobs it only takes a day for theApplication Log to fill up, which is causing other jobs to get hung upwhen trying to write to it. On my Windows 2000 server, I have theapplication log event viewer setting correctly set as:"When maximum log size is reached - Overwrite events as needed" but forsome reason this setting no longer applies like it did for the pastthree years. SQLServerAgent and MSSQLSERVER both run under a localAdmin account, without a domain.When I researched how prevent SQL Server from logging this type ofmessage, I found that I can use sp_update_alert to disable thismessage, but I cannot findthe message_id to correctly disable this message. In sysmessages, themessage I am trying to suppress iserror:18265severity:10dlevel:128description:Log backed up: Database:%1, creation date(time): %2(%3), first LSN: %4, last LSN: %5, number ofdump devices: %7!d!, device information: (%8).mslangid:1033I tried calling sp_update_alert as follows:exec sp_update_alert @name = 'Log backed up: Database: %1, creationdate(time): %2(%3), first LSN: %4, last LSN: %5, number of dumpdevices: %7!d!, device information: (%8).', @enabled = 0but got the error message:Server: Msg 14262, Level 16, State 1, Procedure sp_update_alert, Line105The specified @name ('Log backed up: Database: %1, creation date(time):%2(%3), first LSN: %4, last LSN: %5, number of dump devices: %7!d!,device inf') does not exist.Looks like it can only handle 128 characters.How can I disable this message from being logged in the ApplicationLog? Or alternatively, how can I get the event viewer to behave asexpected and"Overwrite events as needed"?Thanks,Mike Orlando

View 9 Replies View Related

SQL 2005 -- Export Table To A Fixed Width Flat File -- Posting Again

Dec 7, 2006

I am sorry, I am posting this message again, since I did not get anyreply.I want to export a table into a "fixed width" file using SQL 2005import export wizard.This is the version I have:SQL Server 2005 - 9.00.2047.00For some reason it joins all the rows together. For EX: if the tableis like this:Create table Mytable (col1 varchar(50) null, col2 varchar(60) null,col3 varchar (100) Null)Insert into MyTable values ("abcdef", "12345", "8900")Insert into MyTable values ("xxxxxxx", "11111111", "22222222")Insert into MyTable values ("yyyyyyyyy", "5555555555555555","6666666666")Insert into MyTable values ("abcdef", "12345", "8900")Insert into MyTable values ("xxxxxxx", "11111111", "22222222")Insert into MyTable values ("yyyyyyyyy", "5555555555555555","6666666666")It is not exporting every row in a single line. Actually if I open itin "Ultra Edit", it is all in one line.I used to do this regularly with SQL 2000 import export wizard and itexported every row in one line.I looked at the setting:The header row delimiter has {CR}{LF}Code page has 1252 Ansi-Latin.In the Advanced tab:String:dt_str.I tried changing the header row delimiter to just {CR} or just {LF}.Also I tried changing the string to dt_text and nothing seems to help.Please help.Thank you

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved