Professional Web Applications Themes

BCP using queryout - Microsoft SQL / MS SQL Server

I am using bcp to save the results of a procedure to a file. The syntax looks like this: bcp "exec reports.dbo.rptsp_VoteInstructionsForNextDayMtgs" queryout "d:\share\reports\ivs\Vote_Instructions_For_Next_D ay_Mtgs_Report060803.txt" /c -Udatareader -Ptest When I run that statement I get the following error: output ------------------------------------------------ -------------------------------------------- SQLState = S0002, NativeError = 208 Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '##clientinstructions'. NULL I am not writing the select statement directly in bcp because the bcp statement is built dynamically based on what report is running so it can change. Most of the procedures need temp tables so I created global temp tables for them ...

  1. #1

    Default BCP using queryout

    I am using bcp to save the results of a procedure to a file. The syntax
    looks like this:

    bcp "exec reports.dbo.rptsp_VoteInstructionsForNextDayMtgs" queryout
    "d:\share\reports\ivs\Vote_Instructions_For_Next_D ay_Mtgs_Report060803.txt"
    /c -Udatareader -Ptest

    When I run that statement I get the following error:

    output


    ------------------------------------------------
    --------------------------------------------
    SQLState = S0002, NativeError = 208
    Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
    '##clientinstructions'.
    NULL

    I am not writing the select statement directly in bcp because the bcp
    statement is built dynamically based on what report is running so it can
    change. Most of the procedures need temp tables so I created global temp
    tables for them but I'm not sure why I get this error. For any procedure
    using bcp where there are not temp table involved this statement works it is
    just the ones where there are temp tables that fail.
    Any suggestions as to what I might be missing?

    thanks
    Meenal


    Meenal Guest

  2. #2

    Default Re: BCP using queryout

    I think you have not handled temporary table creation or reference to
    temporary table correctly inside stored procedure. There should not be any
    issues with using global temp. tables inside stored procedure and using bcp.
    Try running the stored procedure independently from query yzer and see
    if you are getting the same error.

    If you are dropping/recreating the temp table inside stored procedure you
    may have to put some check as follows .
    Ex:
    if object_id ('tempdb..##t') is not null
    drop table ##t

    create table ##t (prodname varchar(500), total int)

    --
    -Vishal

    "Meenal Dhody" <com> wrote in message
    news:phx.gbl... 
    "d:\share\reports\ivs\Vote_Instructions_For_Next_D ay_Mtgs_Report060803.txt" 
    -- 
    is 


    Vishal Guest

  3. #3

    Default Re: BCP using queryout

    I just ran the procedure form Query yzer and made sure I referenced the
    Global temp table. The procedure completed successfully but did have this
    message:

    Database name 'tempdb' ignored, referencing object in tempdb.

    and I got a similar message when I ran it using bcp:

    output


    ------------------------------------------------
    --------------------------------------------
    SQLState = 01000, NativeError = 2701
    Warning = [Microsoft][ODBC SQL Server Driver][SQL Server]Database name
    'tempdb' ignored, referencing object in tempdb.
    SQLState = 01000, NativeError = 2701
    Warning = [Microsoft][ODBC SQL Server Driver][SQL Server]Database name
    'tempdb' ignored, referencing object in tempdb.
    SQLState = S0002, NativeError = 208
    Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
    '##clientinstructions'.
    NULL

    I can post the actual syntax of the procedure if that will help.

    thanks

    "Vishal Parkar" <com> wrote in message
    news:phx.gbl... 
    bcp. 
    >[/ref]
    "d:\share\reports\ivs\Vote_Instructions_For_Next_D ay_Mtgs_Report060803.txt" 
    >[/ref]
    ---------------------------------------------- [/ref]
    name [/ref]
    temp [/ref]
    procedure [/ref]
    it 
    >
    >[/ref]


    Meenal Guest

  4. #4

    Default Re: BCP using queryout

    The tables and procedure are below. The strange thing is I tried the bcp
    statement on my computer and it works but when I run it on the server I get
    the error.

    thanks
    Meenal

    Tables:

    CREATE TABLE [dbo].[ClientVoteIns] (
    [LocationID] [smallint] NOT NULL ,
    [AgendaID] [int] NOT NULL ,
    [ItemOnAgendaID] [int] NOT NULL ,
    [VoteRecID] [tinyint] NOT NULL ,
    [DateModified] [datetime] NOT NULL ,
    [Contact] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Notes] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [PolicyID] [smallint] NOT NULL ,
    [VISstatusID] [tinyint] NOT NULL ,
    [ReplaceNoteYN] [tinyint] NULL
    ) ON [PRIMARY]
    GO
    CREATE TABLE [dbo].[Account] (
    [AccountID] [int] IDENTITY (1, 1) NOT NULL ,
    [LocationID] [smallint] NOT NULL ,
    [StatusID] [smallint] NULL ,
    [ClientAccountNbr] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ,
    [AccountName] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
    ,
    [IndividualReports] [bit] NOT NULL ,
    [VoteLeadTime] [smallint] NULL ,
    [OtherBusVoteID] [smallint] NULL ,
    [NVRVoteID] [smallint] NULL ,
    [ReconciliationID] [smallint] NULL ,
    [ConfirmationID] [smallint] NULL ,
    [OSDate] [datetime] NULL ,
    [CloseISSDate] [datetime] NULL ,
    [FirstBallotDate] [datetime] NULL ,
    [UpdatedID] [smallint] NULL ,
    [UpdatedDate] [datetime] NULL ,
    [Notes] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ReconciliationStatus] [bit] NOT NULL ,
    [ReportGroupID] [smallint] NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[Agenda] (
    [AgendaID] [int] NOT NULL ,
    [AgendaTypeID] [tinyint] NOT NULL ,
    [SecurityID] [varchar] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [SecurityTypeID] [tinyint] NOT NULL ,
    [MeetingID] [int] NOT NULL ,
    [LastUpdated] [datetime] NOT NULL ,
    [PETransmitYN] [bit] NOT NULL ,
    [PEQueueYN] [bit] NOT NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[Ballot] (
    [MeetingID] [int] NOT NULL ,
    [SecurityID] [char] (14) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [SecurityTypeID] [tinyint] NOT NULL ,
    [AgendaTypeID] [tinyint] NOT NULL ,
    [CustAcctXrefID] [int] NOT NULL ,
    [SeqNumber] [tinyint] NOT NULL ,
    [BallotID] [int] IDENTITY (1, 1) NOT NULL ,
    [BallotShares] [numeric](12, 0) NULL ,
    [EnteredDate] [datetime] NULL ,
    [ReceivedDate] [datetime] NULL ,
    [SharesHeld] [numeric](12, 0) NULL ,
    [SharesOnLoan] [numeric](12, 0) NULL ,
    [VotedYN] [bit] NOT NULL ,
    [VotedDate] [datetime] NULL ,
    [VotedStaffID] [smallint] NULL ,
    [VoteConfirmedDate] [datetime] NULL ,
    [VotedWithoutSignOff] [bit] NOT NULL ,
    [ADPControlNum] [char] (13) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [BallotTypeID] [smallint] NULL ,
    [NonISSAddressYN] [bit] NOT NULL ,
    [VoteByException] [tinyint] NOT NULL ,
    [ReVoteYN] [bit] NOT NULL ,
    [LastDateChanged] [datetime] NULL ,
    [LastChangedStaffID] [smallint] NULL ,
    [Note] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [PreviousVoteYN] [tinyint] NULL ,
    [DeliveryTypeID] [smallint] NULL ,
    [ReconcileType] [smallint] NULL ,
    [HoldingID] [numeric](12, 0) NULL ,
    [VotingBlockID] [smallint] NULL ,
    [AgendaID] [int] NULL ,
    [IgnoreYN] [tinyint] NOT NULL ,
    [ReceivedStaffID] [int] NULL ,
    [DateAccepted] [datetime] NOT NULL ,
    [VoteSubmittedDate] [datetime] NULL ,
    [ImageFilename] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [WaitingBallotID] [int] NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[BlockVote] (
    [AgendaID] [int] NOT NULL ,
    [VotingBlockID] [smallint] NOT NULL ,
    [ItemOnAgendaID] [int] NOT NULL ,
    [VoteCast] [tinyint] NULL ,
    [VotedReleasedDate] [datetime] NULL ,
    [VotedReleasedYN] [bit] NOT NULL ,
    [ReleasedStaffID] [smallint] NULL ,
    [VotedEnteredDate] [datetime] NULL ,
    [VotedEnteredStaffID] [smallint] NULL ,
    [Note] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [NotesYN] [bit] NOT NULL
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[Company] (
    [CompanyID] [int] IDENTITY (1, 1) NOT NULL ,
    [Name] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CalenderSCusip] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Address1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Address2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Address3] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [AddCity] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [AddState] [smallint] NULL ,
    [AddZIP] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CountryID] [smallint] NULL ,
    [Phone1] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Phone2] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Fax1] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Fax2] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CompanyStatusID] [smallint] NULL ,
    [StatusDate] [datetime] NULL ,
    [LinkFromID] [int] NULL ,
    [LinkFromDate] [datetime] NULL ,
    [LinkFromTypeID] [tinyint] NULL ,
    [CompanyNote] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CompanyNoteDate] [datetime] NULL ,
    [CompanyNoteBy] [smallint] NULL ,
    [DateAdded] [datetime] NULL ,
    [DateModified] [datetime] NULL ,
    [ModifiedBy] [smallint] NULL ,
    [CompanyTypeID] [tinyint] NULL ,
    [StaffID] [smallint] NULL ,
    [CUSIP6] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [SICmajor] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [SICminor] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CIKNo] [int] NULL ,
    [GlobalIndustryCode] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Website] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [LinkToID] [int] NULL ,
    [LinkToDate] [datetime] NULL ,
    [LinkToTypeID] [tinyint] NULL ,
    [FiscalYrEnd] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [LastProfileDate] [datetime] NULL ,
    [FilingDate] [datetime] NULL ,
    [SolicitorID] [int] NULL ,
    [CoDescription] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ,
    [StateOfIncID] [int] NULL ,
    [CountryOfIncID] [int] NULL ,
    [AuditCom] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CompCom] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [NomCom] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [CoCurrencyID] [int] NULL ,
    [FinancialSourceID] [smallint] NULL ,
    [CompressedName] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
    NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[CountryCode] (
    [CountryID] [smallint] IDENTITY (1, 1) NOT NULL ,
    [Country] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ISOCountryCode] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [GlobalCoreCountryYN] [bit] NOT NULL ,
    [DueDayDiff] [smallint] NOT NULL ,
    [ShareBlockingMarket] [bit] NOT NULL ,
    [ShareBlockingFileName] [varchar] (100) COLLATE
    SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[CustodianAccount] (
    [CustAcctXrefID] [int] IDENTITY (1, 1) NOT NULL ,
    [CustodianID] [smallint] NOT NULL ,
    [CustBankAcctNbr] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
    NULL ,
    [AccountID] [int] NOT NULL ,
    [ContactID] [smallint] NULL ,
    [ProxyServiceID] [smallint] NULL ,
    [CustodianAccountTypeID] [smallint] NULL ,
    [ConsentVoteYN] [bit] NOT NULL ,
    [VotingPolicyID] [smallint] NOT NULL ,
    [CustPolicyTypeID] [smallint] NULL ,
    [PESetupNeeded] [bit] NOT NULL ,
    [ProxyEdgeReqDate] [datetime] NULL ,
    [ProxyEdgeComDate] [datetime] NULL ,
    [AccountMgrID] [smallint] NULL ,
    [GroupMgrId] [smallint] NULL ,
    [MaterialsFwdID] [smallint] NOT NULL ,
    [HoldingsGroupID] [smallint] NULL ,
    [VotingBlockID] [smallint] NULL ,
    [ElecVoteGroupID] [int] NULL ,
    [AddressChangeYN] [bit] NOT NULL ,
    [CustLetterRcvdDate] [datetime] NULL ,
    [CustAddrChangeDate] [datetime] NULL ,
    [OmnibusMemberYN] [bit] NOT NULL ,
    [OmnibusMasterYN] [bit] NOT NULL ,
    [OmnibusMasterID] [int] NULL ,
    [ADPInternalID] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [BallotForwardID] [smallint] NULL ,
    [HoldingAccountNewID] [int] NULL ,
    [PEIneligibleYN] [bit] NOT NULL ,
    [UpdateDate] [datetime] NULL ,
    [StatusID] [smallint] NULL ,
    [UpdatedID] [smallint] NULL ,
    [DataFeedTypeID] [tinyint] NULL ,
    [FirstBallotDate] [smalldatetime] NULL ,
    [CloseISSDate] [smalldatetime] NULL ,
    [OSDate] [smalldatetime] NULL ,
    [RetailYN] [bit] NOT NULL ,
    [CustLtrFileName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ,
    [ConfirmFileName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[Meeting] (
    [MeetingID] [int] IDENTITY (1, 1) NOT NULL ,
    [CompanyID] [int] NOT NULL ,
    [CountryID] [smallint] NOT NULL ,
    [MeetingDate] [datetime] NULL ,
    [MeetingTypeID] [tinyint] NULL ,
    [RecordDate] [datetime] NULL ,
    [MeetingStatusID] [tinyint] NOT NULL ,
    [CalMeetingID] [int] NULL ,
    [CalFileName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [VoteDeadline] [datetime] NULL ,
    [LastUpdated] [datetime] NULL ,
    [ADPMeetingID] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [AgendaChangedYN] [bit] NOT NULL ,
    [VotingDeadline] [datetime] NULL ,
    [ConfirmedYN] [bit] NOT NULL ,
    [SolicitorID] [smallint] NULL ,
    [CoreYN] [bit] NOT NULL ,
    [S_P500] [bit] NOT NULL ,
    [ProfileYN] [bit] NOT NULL ,
    [NumberofClients] [smallint] NULL ,
    [FilingDate] [smalldatetime] NULL ,
    [Proxylevel] [tinyint] NULL ,
    [ProxySubLevel] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [MeetingLocation] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ,
    [DateAdded] [datetime] NULL ,
    [RetCEOYN] [bit] NOT NULL ,
    [CEOLsChair] [bit] NOT NULL ,
    [LatestPCPStatusLogID] [int] NULL ,
    [LatestPASStatusLogID] [int] NULL ,
    [LatestPASystStatusLogID] [int] NULL ,
    [AlertInLieuID] [int] NULL ,
    [ShareBlockingStartDate] [datetime] NULL ,
    [ClassYN] [bit] NOT NULL ,
    [ADPAgendaItemCount] [tinyint] NULL ,
    [MeetingDateSourceID] [smallint] NULL ,
    [ADRRecordDate] [datetime] NULL ,
    [MaterialCompleteYN] [bit] NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[MeetingAgenda] (
    [MeetingID] [int] NOT NULL ,
    [AgendaID] [int] IDENTITY (1, 1) NOT NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[MeetingTypeCode] (
    [MeetingTypeID] [tinyint] NOT NULL ,
    [MeetingType] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
    ,
    [MeetingTypeAbbrev] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[Staff] (
    [StaffID] [smallint] IDENTITY (1, 1) NOT NULL ,
    [NetworkID] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [Password] [varchar] (8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [FirstName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [LastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
    [JobID] [smallint] NULL ,
    [DeptID] [smallint] NULL ,
    [CalDirLetter] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [ProxyEdgeDirectory] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
    NULL ,
    [ProxyEdgeWorkStation] [tinyint] NULL ,
    [ProxyEdgeID] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [LastLogin] [datetime] NULL ,
    [Title] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Fax] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [RightFaxLogin] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [RightFaxPassword] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ,
    [CostCtr] [int] NULL ,
    [Phone] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [StaffInitials] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[VAS_InvalidMeetings] (
    [MeetingID] [int] NOT NULL ,
    [Action] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [NewMeetingID] [int] NULL ,
    [LastUpdated] [datetime] NOT NULL
    ) ON [PRIMARY]
    GO

    CREATE TABLE [dbo].[VotingBlock] (
    [VotingBlockID] [smallint] IDENTITY (1, 1) NOT NULL ,
    [VotingBlock] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [VotingPolicyID] [smallint] NOT NULL ,
    [LocationID] [int] NULL ,
    [OtherBusVoteID] [int] NULL ,
    [NVRVoteID] [smallint] NULL ,
    [ConsentVoteYN] [bit] NOT NULL ,
    [ActiveYN] [bit] NOT NULL ,
    [RetiredDate] [smalldatetime] NULL ,
    [AutoReleaseID] [smallint] NOT NULL
    ) ON [PRIMARY]
    GO


    Procedure:

    CREATE PROCEDURE rptsp_VoteInstructionsForNextDayMtgs AS
    Set transaction isolation level read uncommitted
    drop table tempdb..##clientinstructions
    Create table tempdb..##clientinstructions ( Locationid int, agendaid int,
    policyid smallint, [Client Inst. Received] datetime )
    insert into tempdb..##clientinstructions ( Locationid, agendaid, policyid,
    [Client Inst. Received] )
    select distinct
    locationid,
    agendaid,
    policyid,
    convert (varchar,max(datemodified),101) [Client Inst. Received]
    from clientvoteins where visstatusid = 2
    group by locationid, agendaid, policyid

    Select
    Distinct
    rtrim(c.Name) [Company],
    cc.country [Country],
    b.securityid [Sec ID],
    b.meetingid [MtgID],
    convert (varchar,m.MeetingDate,101) [MDate],
    mt.MeetingType [Mtg Type],
    vb.VotingBlock [Voting Block],
    ag.AgendaID [AgendaID],
    convert(varchar,bv.votedreleaseddate,101) [Released],
    count(*) [Ballots],
    s.lastname [AM],
    ci.[Client Inst. Received],
    vb.consentvoteyn[Mandatory Signoff yes=0 no=1]

    From
    Elmo_Prod..ballot b
    join Elmo_Prod..meeting m on b.meetingid = m.meetingid and
    b.VoteByException = 0 and b.BallotShares > 0 and(b.ignoreyn =0 or b.ignoreyn
    is null) and
    m.meetingdate between convert(datetime, convert(varchar, getdate(),
    101)) and convert(datetime, convert(varchar, getdate()+ 2, 101)) --use
    today's date to start and 2 days out to end
    left outer join Elmo_Prod..meetingagenda ma on ma.meetingid=m.meetingid
    left outer join Elmo_Prod..agenda ag on (b.MeetingID = ag.MeetingID
    and b.securityid = ag.securityid
    and b.securitytypeid = ag.securitytypeid)
    join Elmo_Prod..company c on m.companyid = c.companyid
    join Elmo_Prod..custodianaccount ca on b.custacctxrefid =
    ca.custacctxrefid and ca.custodianaccounttypeid = 0
    left outer join Elmo_Prod..votingblock vb on ca.votingblockid =
    vb.votingblockid
    left outer join Elmo_Prod..blockvote bv on (ag.agendaid = bv.agendaid
    and ca.votingblockid = bv.votingblockid)
    join Elmo_Prod..account a on ca.accountid = a.accountid
    join Elmo_Prod..staff s on ca.AccountMgrID = s.StaffID
    left outer join Elmo_Prod..countrycode cc on c.countryid = cc.countryid
    join Elmo_Prod..meetingtypecode mt on m.meetingtypeid = mt.meetingtypeid
    left outer join Elmo_Prod..vas_invalidmeetings im on b.meetingid =
    im.meetingid and im.action is NULL
    left outer join tempdb..##clientinstructions ci on (a.locationid =
    ci.locationid
    and ci.agendaid = ag.agendaid
    and ci.policyid = vb.votingpolicyid)

    group by
    rtrim(c.Name),
    cc.country,
    b.securityid,
    b.meetingid,
    convert (varchar,m.MeetingDate,101),
    mt.MeetingType,
    vb.VotingBlock,
    ag.AgendaID,
    convert(varchar,bv.votedreleaseddate,101),
    s.lastname,
    ci.[Client Inst. Received],
    vb.consentvoteyn
    order by s.lastname , convert(varchar,m.MeetingDate,101), rtrim(c.Name),
    vb.VotingBlock
    GO




    "Vishal Parkar" <com> wrote in message
    news:phx.gbl... 
    > the [/ref]
    this 
    >[/ref]
    ---------------------------------------------- [/ref]
    name [/ref]
    > any [/ref][/ref]
    using [/ref]
    > see [/ref]
    > you [/ref]
    > syntax 
    > >[/ref]
    >[/ref]
    "d:\share\reports\ivs\Vote_Instructions_For_Next_D ay_Mtgs_Report060803.txt" 
    > > ---------------------------------------------- [/ref][/ref]
    object [/ref][/ref]
    bcp [/ref]
    > can 
    > > temp 
    > > procedure [/ref][/ref]
    works 
    > >
    > >[/ref]
    >
    >[/ref]


    Meenal Guest

  5. #5

    Default Re: BCP using queryout

    no need to reference temporary tables prefixing with the tempdb other than
    while dropping them.
    See the following modified code.

    if you are running bcp command through xp_cmdshell then you can see
    resulting bcp command syntax using print statement.

    alter PROCEDURE rptsp_VoteInstructionsForNextDayMtgs AS
    Set transaction isolation level read uncommitted
    if object_id ('tempdb..##clientinstructions') is not null--change
    drop table ##clientinstructions

    Create table ##clientinstructions ( Locationid int, agendaid int,
    policyid smallint, [Client Inst. Received] datetime )

    insert into ##clientinstructions ( Locationid, agendaid, policyid,
    [Client Inst. Received] )
    select distinct
    locationid,
    agendaid,
    policyid,
    convert (varchar,max(datemodified),101) [Client Inst. Received]
    from clientvoteins where visstatusid = 2
    group by locationid, agendaid, policyid

    Select
    Distinct
    rtrim(c.Name) [Company],
    cc.country [Country],
    b.securityid [Sec ID],
    b.meetingid [MtgID],
    convert (varchar,m.MeetingDate,101) [MDate],
    mt.MeetingType [Mtg Type],
    vb.VotingBlock [Voting Block],
    ag.AgendaID [AgendaID],
    convert(varchar,bv.votedreleaseddate,101) [Released],
    count(*) [Ballots],
    s.lastname [AM],
    ci.[Client Inst. Received],
    vb.consentvoteyn[Mandatory Signoff yes=0 no=1]

    From
    Elmo_Prod..ballot b
    join Elmo_Prod..meeting m on b.meetingid = m.meetingid and
    b.VoteByException = 0 and b.BallotShares > 0 and(b.ignoreyn =0 or b.ignoreyn
    is null) and
    m.meetingdate between convert(datetime, convert(varchar, getdate(),
    101)) and convert(datetime, convert(varchar, getdate()+ 2, 101))

    left outer join Elmo_Prod..meetingagenda ma on ma.meetingid=m.meetingid
    left outer join Elmo_Prod..agenda ag on (b.MeetingID = ag.MeetingID
    and b.securityid = ag.securityid
    and b.securitytypeid = ag.securitytypeid)
    join Elmo_Prod..company c on m.companyid = c.companyid
    join Elmo_Prod..custodianaccount ca on b.custacctxrefid =
    ca.custacctxrefid and ca.custodianaccounttypeid = 0
    left outer join Elmo_Prod..votingblock vb on ca.votingblockid =
    vb.votingblockid
    left outer join Elmo_Prod..blockvote bv on (ag.agendaid = bv.agendaid
    and ca.votingblockid = bv.votingblockid)
    join Elmo_Prod..account a on ca.accountid = a.accountid
    join Elmo_Prod..staff s on ca.AccountMgrID = s.StaffID
    left outer join Elmo_Prod..countrycode cc on c.countryid = cc.countryid
    join Elmo_Prod..meetingtypecode mt on m.meetingtypeid = mt.meetingtypeid
    left outer join Elmo_Prod..vas_invalidmeetings im on b.meetingid =
    im.meetingid and im.action is NULL
    left outer join ##clientinstructions ci on (a.locationid =
    ci.locationid
    and ci.agendaid = ag.agendaid
    and ci.policyid = vb.votingpolicyid)

    group by
    rtrim(c.Name),
    cc.country,
    b.securityid,
    b.meetingid,
    convert (varchar,m.MeetingDate,101),
    mt.MeetingType,
    vb.VotingBlock,
    ag.AgendaID,
    convert(varchar,bv.votedreleaseddate,101),
    s.lastname,
    ci.[Client Inst. Received],
    vb.consentvoteyn
    order by s.lastname , convert(varchar,m.MeetingDate,101), rtrim(c.Name),
    vb.VotingBlock
    GO


    --
    -Vishal
    Meenal Dhody <com> wrote in message
    news:#phx.gbl... 
    get 
    NULL 
    NULL 



    NULL 

    NULL 
    NULL 
    NULL 
    NULL 



    NULL 
    b.ignoreyn [/ref]
    referencing [/ref][/ref]
    referenced [/ref]
    > this 
    > >[/ref]
    > ---------------------------------------------- [/ref]
    > name [/ref][/ref]
    to [/ref][/ref]
    be [/ref]
    > using [/ref][/ref]
    and [/ref][/ref]
    procedure 
    > > syntax [/ref][/ref]
    queryout 
    > >[/ref]
    >[/ref]
    "d:\share\reports\ivs\Vote_Instructions_For_Next_D ay_Mtgs_Report060803.txt" [/ref]
    > object [/ref]
    > bcp [/ref][/ref]
    it [/ref][/ref]
    global [/ref]
    > works 
    > >
    > >[/ref]
    >
    >[/ref]


    Vishal Guest

  6. #6

    Default Re: BCP using queryout

    Thanks - that worked
    Meenal

    "Vishal Parkar" <com> wrote in message
    news:phx.gbl... 
    b.ignoreyn [/ref]
    bcp 
    > get [/ref]

    > NULL 
    > NULL [/ref]
    NULL [/ref]
    , [/ref]
    NULL [/ref]
    , [/ref]
    , [/ref]
    NULL [/ref]

    > NULL [/ref]
    NOT [/ref]
    NOT [/ref]
    NULL 
    > NULL 
    > NULL [/ref]
    , [/ref]

    > NULL 
    > NULL [/ref]
    NULL [/ref]
    NULL [/ref]
    , [/ref]
    NULL [/ref]
    , [/ref]
    SQL_Latin1_General_CP1_CI_AS [/ref]
    NULL 
    > NULL [/ref]
    , [/ref]
    int, [/ref]
    policyid, 
    > b.ignoreyn [/ref]
    01)) --use [/ref]
    ma.meetingid=m.meetingid [/ref]
    cc.countryid [/ref]
    mt.meetingtypeid [/ref]
    > referencing [/ref]
    > referenced [/ref][/ref]
    have 
    > > ---------------------------------------------- [/ref][/ref]
    name [/ref][/ref]
    name [/ref][/ref]
    object [/ref]
    > to [/ref][/ref]
    not 
    > > using [/ref]
    > and [/ref]
    > procedure [/ref][/ref]
    The [/ref]
    > queryout 
    > >[/ref]
    >[/ref]
    "d:\share\reports\ivs\Vote_Instructions_For_Next_D ay_Mtgs_Report060803.txt" 
    > > object [/ref][/ref]
    the [/ref][/ref]
    so [/ref]
    > global 
    > > works 
    > >
    > >[/ref]
    >
    >[/ref]


    Meenal Guest

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139