Is there any way to run the query more than 8000 character via openquery?

  • Hi eneryone,

    Is there any way to run the query more than 8000 character via openquery? the query is something like below, because we have to create one temp table on local server, and structure of temp table is undefinied. Openquery should be a good way to run the our query, but we got one error (query is too long. Maximum length is 8000.) if the @sqlquery has more than 8000 character, how to overcome it? Really appreciated if you can share anything. Thanks a lot:)

    Thanks

    Lindsay

    DECLARE @sql1 VARCHAR(max)

    DECLARE @sqlquery VARCHAR(max)

    SET @sql1 = 'Select * into #temp1 from OPENQUERY(Lkremote, '+@sqlquery+')'

    EXEC(@sql1)

  • Do you have a chance to either create a view or a sproc at the db referenced in OPENQUERY that would hold the content of @sqlquery? Then you could just call the sproc or the view instead of using such a long statement. Most probably the recommended solution would also help to maintain and troubleshoot...



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Thanks for your reply Lutz^_^

    Could you please give me a sample to create that SP? In our scenario, the querystring is parameter, which is passed into openquery no matter whether we create the SP. Could you please give me a sample for that? Thanks a lot.

    Thanks

    Lindsay

  • How would such a parameter string look like?

    Could you attach a fake sample?

    I'm just wondering what kind of query would require more than 8k for parameter...



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Lutz,

    Please refer to the following sample, I only want to find one query which has 8000+ charater, the table in the query is the sample database of Adventure database from MS, please let me know if anything is not clear. Thanks a lot.

    Thanks

    Lindsay

    DECLARE @Query varchar(max)

    DECLARE @sql1 varchar(max)

    SET @Query = '

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    UNION

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    UNION

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    UNION

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    UNION

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    UNION

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    UNION

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    UNION

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    UNION

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    UNION

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    UNION

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    UNION

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    UNION

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    UNION

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    UNION

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    UNION

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]'

    SELECT LEN(@Query)

    SET @sql1 = 'Select * INTO #temp1 from OPENQUERY(lmremote, '''+@Query+''')'

    EXEC(@sql1)

  • SQL Server 2005 v9.0 SP4----

    Msg 103, Level 15, State 1, Line 1

    The character string that starts with '

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ' is too long. Maximum length is 8000.

    *******************************************************************

    SQL Server 2008 v10.0 SP2----

    Msg 103, Level 15, State 1, Line 1

    The character string that starts with '

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ' is too long. Maximum length is 8000.

    Thanks

    Sachin

  • what do you mean Sachin?

    This is the topic of this thread, I hope to seek one solution to resolve the issue when the query has 8000+ data. the query I attached has 8000+ data, and the openquery doesn't work......

    Thanks

    Lindsay

  • I mean to say, the query which you given for 8000+ width gives error on Both version of 2005/2008. do you have other solution?.

  • Is that really the type of query you're running?

    If so, I simply would use

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    since the queries are all identical and merged using UNION therewith removing duplicates leading to a single SELECT.

    I expect the real query looks quite different...

    By "fake sample" I referred to obfuscated table, column, and parameter naemes but to keep the original structure of the query. The goal is to provide an alternative that will return the same results as your current query.

    And for the current sample the query above will be (one) valid replacement...



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Lutz,

    I only want to create one query has 8000+ charaters, and prove the openquery doesn't work. and see a solution for it. Becasue I can't give you the my original query.......

    Thanks

    Lindsay

  • lindsay,

    use you original query to create a view on the remote server (of course, if you can do it):

    CREATE VIEW RemoteReport

    AS

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    UNION

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    UNION

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    UNION

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    UNION

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    UNION

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    UNION

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    UNION

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    UNION

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    UNION

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    UNION

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    UNION

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    UNION

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    UNION

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    UNION

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    UNION

    SELECT TOP 1 [EmployeeKey],[ParentEmployeeKey],[EmployeeNationalIDAlternateKey],[ParentEmployeeNationalIDAlternateKey]

    ,[SalesTerritoryKey],[FirstName],[LastName],[MiddleName],[NameStyle],[Title],[HireDate],[BirthDate],[LoginID]

    ,[EmailAddress],[Phone],[MaritalStatus],[EmergencyContactName],[EmergencyContactPhone],[SalariedFlag]

    ,[Gender],[PayFrequency],[BaseRate],[VacationHours],[SickLeaveHours],[CurrentFlag],[SalesPersonFlag]

    ,[DepartmentName],[StartDate],[EndDate],[Status]

    FROM [ADW1].[dbo].[DimEmployee]

    Then you can use

    SELECT * FROM RemoteReport in your OPENQUERY statement.

    _____________
    Code for TallyGenerator

  • Thanks a lot Sergiy. This solution works for me^_^

    Thanks

    Lindsay

  • lindsaywang (5/8/2011)


    Lutz,

    I only want to create one query has 8000+ charaters, and prove the openquery doesn't work. and see a solution for it. Becasue I can't give you the my original query.......

    Thanks

    Lindsay

    If you know the shape of the resultset you can use INSERT INTO...EXEC()...AT. Unlike OPENQUERY EXEC() can accept a query as a variable and that variable can be declared as a MAX datatype.

    Something like this:

    IF OBJECT_ID(N'tempdb..#temp1') > 0

    DROP TABLE #temp1 ;

    CREATE TABLE #temp1

    (

    id INT,

    column1 VARCHAR(100)

    ) ;

    GO

    DECLARE @sqlquery VARCHAR(MAX) = 'SELECT 1 as id, ''hello'' as column1;';

    INSERT INTO #temp1 (id, column1)

    EXEC(@sqlquery) AT [Lkremote] ;

    SELECT * FROM #temp1

    GO

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Thanks a lot opc.three.

    It is indeed good way to get data, but it has a restriction that we should know the table structure before we insert the data into the table. in our case, this sql query is located in the SP which we can't control the the table structure. But we can use your suggestion if the table stucture before insert data. Thanks for your suggestion.

    Thanks

    Lindsay

  • lindsaywang (5/11/2011)


    ...but it has a restriction that we should know the table structure before we insert the data into the table

    I only presented the INSERT INTO...EXEC() AT technique because you seemed open to parking a VIEW on the remote instance...implying you would always know the table structure 😀

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply