May 4, 2011 at 10:35 pm
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)
May 5, 2011 at 1:44 am
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...
May 5, 2011 at 1:55 am
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
May 5, 2011 at 11:26 am
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...
May 5, 2011 at 7:32 pm
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)
May 6, 2011 at 1:19 am
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
May 6, 2011 at 3:10 am
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
May 6, 2011 at 9:13 am
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?.
May 6, 2011 at 9:22 am
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...
May 8, 2011 at 7:56 pm
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
May 8, 2011 at 8:28 pm
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
May 9, 2011 at 1:05 am
Thanks a lot Sergiy. This solution works for me^_^
Thanks
Lindsay
May 11, 2011 at 2:16 pm
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
May 11, 2011 at 10:55 pm
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
May 12, 2011 at 7:04 am
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