Loading ...
Sorry, an error occurred while loading the content.

RE: [SQLQueriesNoCode] Re: how do I convert rows to column headings + a few complex calculations!

Expand Messages
  • John Warner
    In that case, I think you are going to need to write a program and have the program process all the data. I m not sure SQL alone will get you where you need to
    Message 1 of 10 , Mar 31, 2004
    • 0 Attachment
      In that case, I think you are going to need to write a program and have
      the program process all the data. I'm not sure SQL alone will get you
      where you need to be with the data like it is. Or you could create a set
      of temp tables, use a set of insert queries to get the data relational
      and then ...

      John Warner
      mailto:john@...

      > -----Original Message-----
      > From: bereleni [mailto:Barry.ROBINSON@...]
      > Sent: Wednesday, March 31, 2004 3:59 AM
      > To: SQLQueriesNoCode@yahoogroups.com
      > Subject: [SQLQueriesNoCode] Re: how do I convert rows to
      > column headings + a few complex calculations!
      >
      >
      > can't agree more, third party has already agreed to a redesign but
      > for this project it's too late, the data is already going in and I
      > need to extract something useful..
      >
      > --- In SQLQueriesNoCode@yahoogroups.com, "John Warner" <john@j...>
      > wrote:
      > > You have a real problem here. The problem is you or your boss
      > allowed
      > > the Database design to be driven by the ASP and not the other way
      > > around. You really need to sit down with the powers at your company
      > and
      > > make them understand that this project suffers from a near fatal
      > flaw
      > > that needs to be corrected. I know you are saying you have to live
      > with
      > > this, but you really need to step up to the plate on this and make
      > > management understand there is a real problem here with the basic
      > design
      > > of things. This bad design will come back to haunt in other ways in
      > the
      > > future, even if you solve your immediate problem. Before you get
      > further
      > > committed to this bad design, now is the time to fix it,
      > things will
      > > only get worse with time.
      > >
      > > John Warner
      > > mailto:john@j...
      > >
      > > > -----Original Message-----
      > > > From: bereleni [mailto:Barry.ROBINSON@l...]
      > > > Sent: Tuesday, March 30, 2004 11:15 AM
      > > > To: SQLQueriesNoCode@yahoogroups.com
      > > > Subject: [SQLQueriesNoCode] Re: how do I convert rows to
      > > > column headings + a few complex calculations!
      > > >
      > > >
      > > > Thanks for your reply Dave, it's very much appreciated.
      > Unfortunately
      > > > I have no control over the tables design and how the data is
      > stored
      > > > in these tables by the ASP code. I thought I'd simplify things by
      > > > only listing the fields I need to extract with my query but off
      > > > course this just made it look like a badly designed database -
      > which
      > > > it probably still is!
      > > >
      > > > I've attached below the SQL to re-create the tables and just
      > > > commented out any columns I don't need/are not used.
      > > >
      > > > I'm not sure if you can help, but I'm having problems to deal
      > with
      > > > the way the radio buttons and checkboxes responses are stored.
      > (how
      > > > to convert IntegerValue to their Element equivalents)
      > > >
      > > > RadioButtons just store the number of the selected Element in the
      > > > IntegerValue1 field and
      > > >
      > > > CheckBoxes records a 1 in the IntegerValue of the corresponding
      > > > selected Element and a 0 in those not selected.
      > > >
      > > >
      > > > Note: The question types (QuestionType) are defined in the ASP
      > > > processing code
      > > >
      > > > ' Question Types
      > > > const constQTypeSingleLine = 1
      > > > const constQTypeMultiLine = 2
      > > > const constQTypeRadioButtons = 3
      > > > const constQTypeCheckBoxes = 4
      > > >
      > > >
      > > > --------------------------------------------------------------
      > > >
      > > >
      > > > --tblFeedbackQuestionnaires
      > > > if exists (select * from dbo.sysobjects where id = object_id
      > (N'[dbo].
      > > > [tblFeedbackQuestionnaires]') and OBJECTPROPERTY(id,
      > > >
      > > > N'IsUserTable') = 1)
      > > > drop table [dbo].[tblFeedbackQuestionnaires]
      > > > GO
      > > >
      > > > CREATE TABLE [dbo].[tblFeedbackQuestionnaires] (
      > > > [QNID] [int] IDENTITY (1, 1) NOT NULL ,
      > > > -- [Status] [tinyint] NOT NULL ,
      > > > [QNName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
      > > > NOT NULL ,
      > > > -- [Heading] [varchar] (300) COLLATE
      > > > SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      > > > -- [LinkMessage] [varchar] (300) COLLATE
      > > > SQL_Latin1_General_CP1_CI_AS NULL ,
      > > > -- [QNType] [tinyint] NOT NULL ,
      > > > -- [Title] [varchar] (100) COLLATE
      > SQL_Latin1_General_CP1_CI_AS
      > > > NULL ,
      > > > -- [IntroText] [varchar] (500) COLLATE
      > > > SQL_Latin1_General_CP1_CI_AS NULL ,
      > > > [Created] [datetime] NOT NULL ,
      > > > -- [OwnerID] [int] NOT NULL ,
      > > > -- [ShowPrivacy] [tinyint] NOT NULL ,
      > > > -- [HasUserInformation] [tinyint] NULL
      > > > ) ON [PRIMARY]
      > > > GO
      > > >
      > > >
      > > >
      > > > --tblFeedbackQuestions
      > > > if exists (select * from dbo.sysobjects where id = object_id
      > (N'[dbo].
      > > > [tblFeedbackQuestions]') and OBJECTPROPERTY(id,
      > > >
      > > > N'IsUserTable') = 1)
      > > > drop table [dbo].[tblFeedbackQuestions]
      > > > GO
      > > >
      > > > CREATE TABLE [dbo].[tblFeedbackQuestions] (
      > > > [QuestionID] [int] IDENTITY (1, 1) NOT NULL ,
      > > > [QNID] [int] NOT NULL ,
      > > > [QuestionType] [tinyint] NOT NULL ,
      > > > [QuestionText] [varchar] (400) COLLATE
      > > > SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      > > > [Element1] [varchar] (200) COLLATE
      > > > SQL_Latin1_General_CP1_CI_AS NULL ,
      > > > [Element2] [varchar] (200) COLLATE
      > > > SQL_Latin1_General_CP1_CI_AS NULL ,
      > > > [Element3] [varchar] (200) COLLATE
      > > > SQL_Latin1_General_CP1_CI_AS NULL ,
      > > > [Element4] [varchar] (200) COLLATE
      > > > SQL_Latin1_General_CP1_CI_AS NULL ,
      > > > [Element5] [varchar] (200) COLLATE
      > > > SQL_Latin1_General_CP1_CI_AS NULL
      > > > ) ON [PRIMARY]
      > > > GO
      > > >
      > > >
      > > >
      > > > --tblFeedbackResponseDetails
      > > > if exists (select * from dbo.sysobjects where id = object_id
      > (N'[dbo].
      > > > [tblFeedbackResponseDetails]') and OBJECTPROPERTY(id,
      > > >
      > > > N'IsUserTable') = 1)
      > > > drop table [dbo].[tblFeedbackResponseDetails]
      > > > GO
      > > >
      > > > CREATE TABLE [dbo].[tblFeedbackResponseDetails] (
      > > > [ResponseDetailID] [int] IDENTITY (1, 1) NOT NULL ,
      > > > [ResponseID] [int] NOT NULL ,
      > > > [QuestionID] [int] NOT NULL ,
      > > > [TextValue] [varchar] (2000) COLLATE
      > > > SQL_Latin1_General_CP1_CI_AS NULL ,
      > > > [IntegerValue1] [tinyint] NULL ,
      > > > [IntegerValue2] [tinyint] NULL ,
      > > > [IntegerValue3] [tinyint] NULL ,
      > > > [IntegerValue4] [tinyint] NULL ,
      > > > [IntegerValue5] [tinyint] NULL
      > > > ) ON [PRIMARY]
      > > > GO
      > > >
      > > >
      > > >
      > > > --tblFeedbackResponses
      > > > if exists (select * from dbo.sysobjects where id = object_id
      > (N'[dbo].
      > > > [FK_tblFeedbackResponseDetails_tblFeedbackResponses]')
      > > >
      > > > and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
      > > > ALTER TABLE [dbo].[tblFeedbackResponseDetails] DROP CONSTRAINT
      > > > FK_tblFeedbackResponseDetails_tblFeedbackResponses
      > > > GO
      > > >
      > > > if exists (select * from dbo.sysobjects where id = object_id
      > (N'[dbo].
      > > > [tblFeedbackResponses]') and OBJECTPROPERTY(id,
      > > >
      > > > N'IsUserTable') = 1)
      > > > drop table [dbo].[tblFeedbackResponses]
      > > > GO
      > > >
      > > > CREATE TABLE [dbo].[tblFeedbackResponses] (
      > > > [ResponseID] [int] IDENTITY (1, 1) NOT NULL ,
      > > > -- [TheDate] [datetime] NOT NULL ,
      > > > [QNID] [int] NOT NULL ,
      > > > -- [PageURL] [varchar] (250) COLLATE
      > > > SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      > > > -- [Ranking] [tinyint] NULL ,
      > > > -- [RemoteIP] [varchar] (50) COLLATE
      > > > SQL_Latin1_General_CP1_CI_AS NULL ,
      > > > -- [UIName] [varchar] (150) COLLATE
      > SQL_Latin1_General_CP1_CI_AS
      > > > NULL ,
      > > > -- [UIEmailAddress] [varchar] (150) COLLATE
      > > > SQL_Latin1_General_CP1_CI_AS NULL ,
      > > > -- [UICompanyName] [varchar] (150) COLLATE
      > > > SQL_Latin1_General_CP1_CI_AS NULL ,
      > > > -- [UIInvestorType] [int] NULL ,
      > > > -- [UIOrigin] [int] NULL
      > > > ) ON [PRIMARY]
      > > > GO
      > > >
      > > >
      > > >
      > > > --------------------------------------------------------------
      > > >
      > > > insert tblFeedbackQuestionnaires values(1, 'Questionnaire1')
      > > >
      > > > insert tblFeedbackQuestionnaires values(2, 'Questionnaire2')
      > > >
      > > > --------------------------------------------------------------
      > > >
      > > > insert tblFeedbackQuestions values(1, 1, 1, 'Name')
      > > > insert tblFeedbackQuestions values(2, 1, 2, 'Comments') insert
      > > > tblFeedbackQuestions values(3, 1, 3, 'Picture Quality',
      > 'excellent',
      > > > 'good', 'average', 'poor', 'unacceptable')
      > > > insert tblFeedbackQuestions values(4, 1, 4, 'Interesting
      > > > Sections', 'this section',
      > > > 'that section', 'my section', 'your section', 'her section')
      > > >
      > > > --------------------------------------------------------------
      > > >
      > > >
      > > > insert tblFeedbackResponses values(1, 1)
      > > > insert tblFeedbackResponses values(2, 1)
      > > >
      > > >
      > > > --------------------------------------------------------------
      > > > insert tblFeedbackResponseDetails values(1, 1, 1, 'Billy Bob', 0,
      > 0,
      > > > 0, 0, 0)
      > > > insert tblFeedbackResponseDetails values(2, 1, 2, 'These are my
      > > > multiline comments.\n They have
      > > > new lines.', 0, 0, 0, 0, 0)
      > > > insert tblFeedbackResponseDetails values(3, 1, 3, '', 3, 0,
      > > > 0, 0, 0) insert tblFeedbackResponseDetails values(4, 1, 4,
      > > > '', 0, 1, 0, 1, 1)
      > > >
      > > >
      > > > insert tblFeedbackResponseDetails values(5, 2, 1, 'Billy Ray', 0,
      > 0,
      > > > 0, 0, 0)
      > > > insert tblFeedbackResponseDetails values(6, 2, 2, 'These are my
      > > > multiline comments.\n They have
      > > > new lines.', 0, 0, 0, 0, 0)
      > > > insert tblFeedbackResponseDetails values(7, 2, 3, '', 2, 0,
      > > > 0, 0, 0) insert tblFeedbackResponseDetails values(8, 2, 4,
      > > > '', 1, 0, 0, 1, 1)
      > > >
      > > >
      > > >
      > > > --- In SQLQueriesNoCode@yahoogroups.com, "Dave Cline" <dave@t...>
      > > > wrote:
      > > > > First off,
      > > > > Your table is poorly designed.
      > > > >
      > > > > I've made a better one. Normalized (2ndnormal)
      > > > >
      > > > > --- drop it in querybuilder and have at it ---
      > > > >
      > > > > If exists(select null from sysobjects where name= 'Response' )
      > drop
      > > > table
      > > > > Response
      > > > > If exists(select null from sysobjects where name= 'Question' )
      > drop
      > > > table
      > > > > Question
      > > > > If exists(select null from sysobjects where
      > name= 'QuestionType' )
      > > > drop table
      > > > > QuestionType
      > > > > If exists(select null from sysobjects where
      > name= 'Questionnaire' )
      > > > drop
      > > > > table Questionnaire
      > > > >
      > > > > ---------------------------------
      > > > > create table QuestionType (
      > > > > QuestionType varchar(20) not null primary key
      > > > > )
      > > > >
      > > > > ---------------------------------
      > > > > create table Questionnaire(
      > > > > QuestionnaireID int identity(1,1) primary key
      > > > > ,CreateDate smalldatetime not null default getdate()
      > > > > )
      > > > >
      > > > > ---------------------------------
      > > > > create table Question(
      > > > > QuestionID int identity(1,1) primary key
      > > > > ,QuestionType varchar(20) not null references QuestionType
      > > > (QuestionType)
      > > > > ,QuestionText varchar(500) not null default '' ,OptionPrompt1
      > > > > varchar(50) ,OptionPrompt2 varchar(50)
      > > > > ,OptionPrompt3 varchar(50)
      > > > > ,OptionPrompt4 varchar(50)
      > > > > ,OptionPrompt5 varchar(50)
      > > > > )
      > > > >
      > > > > ---------------------------------
      > > > > create table Response (
      > > > > ResponseID int identity(1,1) primary key ,QuestionnaireID int
      > > > > references Questionnaire
      > (QuestionnaireID)
      > > > > ,QuestionID int references Question (QuestionID) ,ResponseText
      > > > > varchar(500) not null ,OptionValue1 varchar(50)
      > > > > ,OptionValue2 varchar(50)
      > > > > ,OptionValue3 varchar(50)
      > > > > ,OptionValue4 varchar(50)
      > > > > ,OptionValue5 varchar(50)
      > > > > )
      > > > >
      > > > > ---------------------------------
      > > > > insert QuestionType values( 'SingleLine')
      > > > > insert QuestionType values( 'MultiLine')
      > > > > insert QuestionType values( 'Radio')
      > > > > insert QuestionType values( 'Checkbox')
      > > > >
      > > > > ---------------------------------
      > > > >
      > > > > insert Question values('SingleLine', 'Name', null, null, null,
      > > > null, null)
      > > > > insert Question values('MultiLine', 'Comments', null, null,
      > null,
      > > > null,
      > > > > null)
      > > > > insert Question values('Radio', 'Picture
      > > > Quality', 'excellent', 'good',
      > > > > 'average', 'poor', 'unacceptable')
      > > > > insert Question values('Checkbox', 'Interesting Sections', 'this
      > > > section',
      > > > > 'that section', 'my section', 'your section', 'her section')
      > > > >
      > > > > insert Questionnaire values(default)
      > > > > insert Response values(1, 1, 'Billy bob Thornton', null, null,
      > > > null, null,
      > > > > null)
      > > > > insert Response values(1, 2, 'These are my multiline comments.\n
      > > > They have
      > > > > new lines.', null, null, null, null, null)
      > > > > insert Response values(1, 3, '', 'excellent', null, null, null,
      > > > null)
      > > > > insert Response values(1, 4, '', 'this section', 'that section',
      > > > null, null,
      > > > > 'her section')
      > > > >
      > > > > insert Questionnaire values(default)
      > > > > insert Response values(2, 1, 'Billy ray Pringle', null, null,
      > null,
      > > > null,
      > > > > null)
      > > > > insert Response values(2, 2, ' These are also my multiline
      > > > comments.\n They
      > > > > have new lines.', null, null, null, null, null)
      > > > > insert Response values(2, 3, '', null, null, 'average', null,
      > null)
      > > > > insert Response values(2, 4, '', null, null, 'my section', 'your
      > > > section',
      > > > > null)
      > > > >
      > > > > /*
      > > > > -- QUERY 1 - Cross tabbed on Question Type
      > > > > Select
      > > > > case when B.QuestionType='SingleLine' then
      > ResponseText end
      > > > as SingleLine
      > > > > ,case when B.QuestionType='MultiLine' then
      > ResponseText end
      > > > as MultiLine
      > > > > ,case when B.QuestionType='Radio' then isnull
      > > > (OptionValue1,'') +
      > > > > isnull(OptionValue2,'') + isnull(OptionValue3,'') + isnull
      > > > (OptionValue4,'')
      > > > > + isnull(OptionValue5,'') end as Radio
      > > > > ,case when B.QuestionType='Checkbox' AND NOT
      > OptionValue1 is
      > > > null then
      > > > > OptionValue1 end as Checkbox1
      > > > > ,case when B.QuestionType='Checkbox' AND NOT
      > OptionValue2 is
      > > > null then
      > > > > OptionValue2 end as Checkbox2
      > > > > ,case when B.QuestionType='Checkbox' AND NOT
      > OptionValue3 is
      > > > null then
      > > > > OptionValue3 end as Checkbox3
      > > > > ,case when B.QuestionType='Checkbox' AND NOT
      > OptionValue4 is
      > > > null then
      > > > > OptionValue4 end as Checkbox4
      > > > > ,case when B.QuestionType='Checkbox' AND NOT
      > OptionValue5 is
      > > > null then
      > > > > OptionValue5 end as Checkbox5
      > > > > From Response A
      > > > > Join Question B on A.QuestionID = B.QuestionID
      > > > >
      > > > > -- You could use this query selected into a temp table and then
      > > > total up
      > > > > -- the fields that had values to see a snapshot of all your
      > > > responses.
      > > > > */
      > > > >
      > > > > /*
      > > > > -- QUERY 2 - Cross tabbed on Question Text
      > > > > */
      > > > > ALTER PROC DenormQuestionResponses
      > > > > as
      > > > > SET NOCOUNT ON
      > > > >
      > > > > -------------------------------
      > > > > -- FIRST create question column table
      > > > > -------------------------------
      > > > > IF exists (select null from sysobjects Where name
      > > > = 'QuestionColumns') drop
      > > > > table QuestionColumns
      > > > >
      > > > > DECLARE @QuestionID int, @QuestionText varchar(40),
      > @QuestionColumns
      > > > > varchar(8000)
      > > > > SET @QuestionColumns = ''
      > > > > DECLARE QuestionCursor CURSOR FOR
      > > > > SELECT QuestionID, LEFT(QuestionText, 40)
      > > > > FROM Question
      > > > > ORDER BY QuestionID
      > > > >
      > > > > OPEN QuestionCursor
      > > > > FETCH NEXT FROM QuestionCursor INTO @QuestionID, @QuestionText
      > > > >
      > > > > WHILE @@FETCH_STATUS = 0
      > > > > BEGIN
      > > > > SET @QuestionColumns = @QuestionColumns + '"' + @QuestionText
      > > > + '"
      > > > > varchar(100),'
      > > > > -- PRINT @QuestionColumns
      > > > >
      > > > > -- Get the next author.
      > > > > FETCH NEXT FROM QuestionCursor
      > > > > INTO @QuestionID, @QuestionText
      > > > > END
      > > > >
      > > > > IF LEN(@QuestionColumns) > 0 BEGIN
      > > > > SET @QuestionColumns = LEFT(@QuestionColumns, LEN
      > > > (@QuestionColumns)-1)
      > > > > END
      > > > >
      > > > > CLOSE QuestionCursor
      > > > > DEALLOCATE QuestionCursor
      > > > >
      > > > > DECLARE @EXEC varchar(8000)
      > > > > SET @EXEC = 'CREATE TABLE QuestionColumns (' + @QuestionColumns
      > > > + ')'
      > > > > --PRINT @EXEC
      > > > > EXEC (@EXEC)
      > > > >
      > > > > -------------------------------
      > > > > -- NOW insert responses
      > > > > -------------------------------
      > > > >
      > > > > DECLARE @QuestionType varchar(20), @QuestionnaireID int,
      > > > @ResponseText
      > > > > varchar(40), @Opt1 varchar(20), @Opt2 varchar(20), @Opt3 varchar
      > > > (20), @Opt4
      > > > > varchar(20), @Opt5 varchar(20)
      > > > > DECLARE @ResponseInsert varchar(8000), @LastQuestionnaireID int
      > SET
      > > > > @ResponseInsert = ''
      > > > >
      > > > > DECLARE ResponseCursor CURSOR FOR
      > > > > SELECT LEFT(QuestionText, 40), QuestionType, QuestionnaireID,
      > > > > LEFT(ResponseText, 40), OptionValue1, OptionValue2,
      > OptionValue3,
      > > > > OPtionValue4, OptionValue5 FROM Response JOIN Question ON
      > > > > Response.QuestionID =
      > > > Question.QuestionID
      > > > > ORDER BY QuestionnaireID, Question.QuestionID
      > > > >
      > > > > OPEN ResponseCursor
      > > > > FETCH NEXT FROM ResponseCursor
      > > > > INTO @QuestionText, @QuestionType, @QuestionnaireID,
      > @ResponseText,
      > > > @Opt1,
      > > > > @Opt2, @Opt3, @Opt4, @Opt5
      > > > >
      > > > > SET @LastQuestionnaireID = 0
      > > > > SET @ResponseInsert = 'INSERT QuestionColumns VALUES('
      > > > >
      > > > > WHILE @@FETCH_STATUS = 0
      > > > > BEGIN
      > > > >
      > > > > -- execute the insert ---
      > > > > IF (@LastQuestionnaireID != 0 AND
      > > > @LastQuestionnaireID !=
      > > > > @QuestionnaireID) BEGIN
      > > > > SET @ResponseInsert =
      > LEFT(@ResponseInsert, LEN
      > > > (@ResponseInsert)-1) +
      > > > > ')'
      > > > > PRINT @ResponseInsert
      > > > > EXEC (@ResponseInsert)
      > > > > SET @ResponseInsert = 'INSERT
      > QuestionColumns
      > > > VALUES('
      > > > > END
      > > > >
      > > > > SET @LastQuestionnaireID = @QuestionnaireID
      > > > >
      > > > > SET @ResponseInsert = @ResponseInsert +
      > > > > CASE
      > > > > WHEN @QuestionType =
      > 'SingleLine'
      > > > Then '''' + @ResponseText + ''','
      > > > > WHEN @QuestionType = 'MultiLine'
      > > > Then '''' + @ResponseText + ''','
      > > > > WHEN @QuestionType = 'Radio'
      > > > Then '''' + isnull(@Opt1,'') +
      > > > > isnull(@Opt2,'') + isnull(@Opt3,'') + isnull(@Opt4,'') + isnull
      > > > (@Opt5,'') +
      > > > > ''','
      > > > > WHEN @QuestionType = 'Checkbox'
      > > > Then '''' + isnull(@Opt1,'') + ' ' +
      > > > > isnull(@Opt2,'') + ' ' + isnull(@Opt3,'') + ' ' + isnull
      > (@Opt4,'')
      > > > + ' ' +
      > > > > isnull(@Opt5,'') + ''','
      > > > > END
      > > > >
      > > > > -- Get the next author.
      > > > > FETCH NEXT FROM ResponseCursor
      > > > > INTO @QuestionText, @QuestionType,
      > @QuestionnaireID,
      > > > @ResponseText,
      > > > > @Opt1, @Opt2, @Opt3, @Opt4, @Opt5
      > > > > END
      > > > >
      > > > > -- LAST Insert ---
      > > > > SET @ResponseInsert = LEFT(@ResponseInsert, LEN
      > (@ResponseInsert)-
      > > > 1) + ')'
      > > > > PRINT @ResponseInsert
      > > > > EXEC (@ResponseInsert)
      > > > >
      > > > > CLOSE ResponseCursor
      > > > > DEALLOCATE ResponseCursor
      > > > >
      > > > > -- DenormQuestionResponses
      > > > > -- select * from QuestionColumns
      > > >
      > > >
      > > >
      > > >
      > > >
      > > > Yahoo! Groups Links
      > > >
      > > >
      > > >
      > > >
      > > >
      > > >
      >
      >
      >
      >
      >
      > Yahoo! Groups Links
      >
      >
      >
      >
      >
      >
    • A Friend
      Subject: Could not open FCB for invalid file Id 0 in database dbname Hi all Could not open FCB for invalid file Id 0 in database dbname I am having a
      Message 2 of 10 , Apr 6, 2004
      • 0 Attachment

        Subject: Could not open FCB for invalid file Id 0 in database 'dbname'

        Hi all

        "Could not open FCB for invalid file Id 0 in database 'dbname' "

        I am having a serious problem whith Dbcc checkdb, when I run dbcc checkdb with repair_rebuild option from Query Anaylzer.

        My query is

        Alter Database: dbName

        Set Single_User

        Go

        dbcc checkdb ('dbName',repair_rebuild)

        go

        Alter Database: dbName

        Set Multi_User

        Go

        it failed upon execution and message appears.

        Server: Msg 5180, Level 22, State 1, Line 2

        Could not open FCB for invalid file Id 0 in database 'dbname'

        ODBC: Msg 0, Level 16, State 1

        Communication Link Failure

        Communication Broken

        I can not repair the databses, these are the JD Edwards databses. (JDE_Production and JDE_Prestine in case if any JDE DBA is here)

        I could not find any solution of it. I checked Network connectivity it is ok, Hardware is not failing. I installed another SQL installation and install Sql 2k SP3a, copy mu user database 'dbName' (without Master/msdb) try dbcc checkdb but still got the same problem. I also upgrade the MDAC to 2.8 latest and got no luck. I use Tcp/ip and remove name pipe also, but same.

        How to repair the user database?

        Do I have to rebuild Master db first? I think it is also currupted. How to rebuild Master db (any pros or corns).

        Can I copy Master, msdb and model Db to another SQL Server and repair there, is this possible?

        FCB stands for what?

        Please help.

      • John Warner
        My first thought in a case like this (production accounting data) would be call JD Edwards. I know it is SQL server, but ... John Warner
        Message 3 of 10 , Apr 6, 2004
        • 0 Attachment
          Message
          My first thought in a case like this (production accounting data) would be call JD Edwards. I know it is SQL server, but ...

          John Warner
          mailto:john@...

          -----Original Message-----
          From: A Friend [mailto:amajidk@...]
          Sent: Tuesday, April 06, 2004 8:13 AM
          To: SQLQueriesNoCode@yahoogroups.com
          Subject: [SQLQueriesNoCode] Could not open FCB for invalid file Id 0 in database 'dbname'

          Subject: Could not open FCB for invalid file Id 0 in database 'dbname'

          Hi all

          "Could not open FCB for invalid file Id 0 in database 'dbname' "

          I am having a serious problem whith Dbcc checkdb, when I run dbcc checkdb with repair_rebuild option from Query Anaylzer.

          My query is

          Alter Database: dbName

          Set Single_User

          Go

          dbcc checkdb ('dbName',repair_rebuild)

          go

          Alter Database: dbName

          Set Multi_User

          Go

          it failed upon execution and message appears.

          Server: Msg 5180, Level 22, State 1, Line 2

          Could not open FCB for invalid file Id 0 in database 'dbname'

          ODBC: Msg 0, Level 16, State 1

          Communication Link Failure

          Communication Broken

          I can not repair the databses, these are the JD Edwards databses. (JDE_Production and JDE_Prestine in case if any JDE DBA is here)

          I could not find any solution of it. I checked Network connectivity it is ok, Hardware is not failing. I installed another SQL installation and install Sql 2k SP3a, copy mu user database 'dbName' (without Master/msdb) try dbcc checkdb but still got the same problem. I also upgrade the MDAC to 2.8 latest and got no luck. I use Tcp/ip and remove name pipe also, but same.

          How to repair the user database?

          Do I have to rebuild Master db first? I think it is also currupted. How to rebuild Master db (any pros or corns).

          Can I copy Master, msdb and model Db to another SQL Server and repair there, is this possible?

          FCB stands for what?

          Please help.

        Your message has been successfully submitted and would be delivered to recipients shortly.