March 4, 2009 at 10:54 pm
You have changed the requirements of what you want with each post.
You should start by thinking through what you actually want, and then asking that question, instead of asking 4 or 5 more questions that you don't actually need the answer to.
March 4, 2009 at 11:19 pm
Michael Valentine Jones (3/4/2009)
You have changed the requirements of what you want with each post.You should start by thinking through what you actually want, and then asking that question, instead of asking 4 or 5 more questions that you don't actually need the answer to.
Hi Michael, thanks for the suggestion,
All I'm doing is to Compare an Access table with a SQL table
(Except the columns of type MEMO in access (NTEXT in SQL))
So as in Post #668126 above, I have written a custom code to achieve this.
Now my problem is I'm not able to figure out how to collect the input parameters into an array variable inside the sproc.
(input parameters:
first parameter: SQL Table
second: Access Table - in the form Access_LnkdSrvr...[TableName]
third parameter onwards: names of MEMO fileds)
is there any way as in C language like
int Main(int argc, char argv[])
so that all the parameters from third can be stored in argv[] (from 0 to n)
if this is possible in Sproc then I can implement the same in that code and get what I need.
Thanks,
San
Thanks,
Santhosh
March 5, 2009 at 12:37 am
Santhosh (3/4/2009)
Hi Gail,In the Remarks section I found the below statement
Remarks
The tablediff utility cannot be used with non-SQL Server servers.
I'm Comparing a table of Access database(Linked Server) with a table in SQL Server.
Is it possible to do this?
As the MSDN page says, no.
If you'd mentioned anywhere that this was an Access table, I wouldn't have spent 30 min finding that util, finding the page and suggesting is as an option.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2009 at 1:41 am
GilaMonster (3/5/2009)
If you'd mentioned anywhere that this was an Access table, I wouldn't have spent 30 min finding that util, finding the page and suggesting is as an option.
Hi Gail,
I am really very sorry for that...
I think I consumed your precious 30mins of time...
Sorry again
Thanks
San
Thanks,
Santhosh
March 5, 2009 at 1:54 am
No worries. Please in future explain the entire problem up front.
What are you still having a problem with?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 5, 2009 at 2:16 am
GilaMonster (3/5/2009)
No worries. Please in future explain the entire problem up front.What are you still having a problem with?
Ya sure I will,
now the problem is,
in the below code, instead of declaring @memo1,@memo2,@memo3,@memo4
In general for many inputs, can i declare a variable something like
array and store all the inputs from 3rd parameter onwards into it?
so that I can LOOP through the array variables and insert those to
@excluded_columns table variable
This code will compare the tables excluding the columns specified in the parameter.
EXEC USP_Compare_Gen 'SQLTable','Access...[Table]',col1,col5
The input might contain only col1 OR col1,col5 OR nothing OR some 10 columns
CREATE PROCEDURE USP_Compare_Gen
@table1 SYSNAME,
@table2 SYSNAME,
@memo1 VARCHAR(100) = '',
@memo2 VARCHAR(100) = '',
@memo3 VARCHAR(100) = '',
@memo4 VARCHAR(100) = ''
--=============
--instead i need to declare an array like variable here
--=============
AS
BEGIN
DECLARE @sql VARCHAR(MAX),
@errsql VARCHAR(8000),
@lnkserver VARCHAR(100)
--Check for the existence of SQL Table
IF NOT EXISTS (SELECT * FROM information_schema.tables WHERE table_name=@table1)
BEGIN
SET @errsql='Table ['+@table1+'] does not exists in the current database'
RAISERROR(@errsql,16,1)
RETURN
END
--Check for existence of Linked Server(Access Database)
SET @lnkserver=SUBSTRING(@table2,1,CHARINDEX('.',@table2)-1)
IF NOT EXISTS(SELECT * FROM sys.sysservers WHERE srvname=@lnkserver)
BEGIN
SET @errsql='Linked Server ['+@lnkserver+'] does not exists'
RAISERROR(@errsql,16,1)
RETURN
END
SET @table1='['+@table1+']'
--If there is no NTEXT field then compare whole table
IF @memo1 = ''
BEGIN
SET @sql = 'SELECT ''SQL'' AS DB,''' + @table1 + ''' AS Table_Name, * FROM
(SELECT * FROM ' + @table1 + '
EXCEPT
SELECT * FROM ' + @table2 + ') x
UNION ALL
SELECT ''Access'' AS DB,''' + @table2 + ''' AS Table_Name, * FROM
(SELECT * FROM ' + @table2 + '
EXCEPT
SELECT * FROM ' + @table1 +') x'
EXEC(@sql)
END --IF
--If do exists NTEXT field then compare tables without that column
ELSE
BEGIN
DECLARE @excluded_columns TABLE (name SYSNAME)
DECLARE @Access_Columns NVARCHAR(MAX),
@SQL_Columns NVARCHAR(MAX)
-=================
--check whether the columns passed in parameter exist in SQL table
-=================
-- Excluded columns
-=================
/*here I need a LOOP that inserts the parameters from the array kind of variable
*/
INSERT INTO @excluded_columns VALUES (@memo1)
IF @memo2!=''
INSERT INTO @excluded_columns VALUES (@memo2)
IF @memo3!=''
INSERT INTO @excluded_columns VALUES (@memo3)
IF @memo4!=''
INSERT INTO @excluded_columns VALUES (@memo4)
-=================
SET @Access_Columns = ''
SELECT @Access_Columns = @Access_Columns + ', ' + QUOTENAME(name)
FROM sys.columns
WHERE OBJECT_ID = OBJECT_ID(@table1)
and name NOT IN(SELECT name FROM @excluded_columns)
SET @Access_Columns = RIGHT(@Access_Columns, LEN(@Access_Columns) - 2)
SET @SQL_Columns = ''
SELECT @SQL_Columns = @SQL_Columns + ', ' + QUOTENAME(name)
FROM sys.columns
WHERE OBJECT_ID = OBJECT_ID(@table1)
and name NOT IN(SELECT name FROM @excluded_columns)
SET @SQL_Columns = RIGHT(@SQL_Columns, LEN(@SQL_Columns) - 2)
SET @sql = 'SELECT ''SQL'' AS DB,''' + @table1 + ''' AS Table_Name, * FROM
(SELECT '+@SQL_Columns +'FROM ' + @table1 + '
EXCEPT
SELECT '+@Access_Columns +'FROM ' + @table2 + ') x
UNION ALL
SELECT ''Access'' AS DB,''' + @table2 + ''' AS Table_Name, * FROM
(SELECT '+@Access_Columns +'FROM ' + @table2 + '
EXCEPT
SELECT '+@SQL_Columns +'FROM ' + @table1 +') x'
EXEC(@sql)
END --ELSE
END --DECLARE
Also am I using the RAISERROR method correctly?
can we use any of the severity level from 0 to 18 as in
RAISERROR(@msg,16,1)
Please let me know if any issues...
Thanks
San
Thanks,
Santhosh
March 6, 2009 at 7:46 am
GilaMonster (3/4/2009)
Yup, list all the column names (as you've done in your example)If you don't want to type, and I don't blame you, then there's a quick way. Expand out object explorer until you can see the table. Expand that out as well. Click on "Columns" and drag that to the query window and you'll have a full list of all the columns in the table, comma delimited. Then you can remove the two that you don't want.
Gail, once again just by reading your answer, I learned something that I never knew I wanted to know! Thank you!
Student of SQL and Golf, Master of Neither
March 6, 2009 at 7:56 am
You can also just right-click on a table in the SSMS object explorer, and pick Script Table as, SELECT
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply