August 9, 2011 at 10:54 am
i have a linked server, i need to do a describe where i can get the field name and the fields data type:
a1_pipe_snapshot is the table name
I have tried the following and none of it works:
SELECT * FROM OPENQUERY (daps_linked,''SET FMTONLY OFF EXEC sp_help' + ' a1_pipe_snapshot'')
select * from openquery(daps_linked, 'show colums from a1_pipe_snapshot;')
SELECT * FROM openquery(daps_linked,'Exec sp_help a1_pipe_snapshot')
SELECT * FROM openquery(daps_linked,''Exec sp_help' + ' a1_pipe_snapshot'')
WHAT AM I DOING WRONG
thanks in advance
this should be very easy
August 9, 2011 at 10:59 am
Always easy when you know it ;-).
EXEC [daps_linked].[db name].dbo.sp_help 'dbo.Calendar'
August 9, 2011 at 12:45 pm
Hi
I ran it,
now i can see my tables in any of the link servers
and when i run any query against the link servers they just kind of hang.
I went into the error and log and then is what i see.
2011-08-07 18:03:35.56 server Microsoft SQL Server 2000 - 8.00.2055 (Intel X86)
Dec 16 2008 19:46:53
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
2011-08-07 18:03:35.57 server Copyright (C) 1988-2002 Microsoft Corporation.
2011-08-07 18:03:35.57 server All rights reserved.
2011-08-07 18:03:35.57 server Server Process ID is 2124.
2011-08-07 18:03:35.57 server Logging SQL Server messages in file 'e:\MSSQL\log\ERRORLOG'.
2011-08-07 18:03:35.84 server SQL Server is starting at priority class 'normal'(1 CPU detected).
2011-08-07 18:03:37.04 server SQL Server configured for thread mode processing.
2011-08-07 18:03:37.37 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2011-08-07 18:03:37.81 server Attempting to initialize Distributed Transaction Coordinator.
2011-08-07 18:03:41.29 spid3 Starting up database 'master'.
2011-08-07 18:03:42.56 server Using 'SSNETLIB.DLL' version '8.0.2039'.
2011-08-07 18:03:42.56 spid5 Starting up database 'model'.
2011-08-07 18:03:42.62 spid3 Server name is 'HWVANDD1164'.
2011-08-07 18:03:42.62 spid8 Starting up database 'msdb'.
2011-08-07 18:03:42.62 spid9 Starting up database 'pubs'.
2011-08-07 18:03:42.62 server SQL server listening on 172.21.80.86: 1433.
2011-08-07 18:03:42.62 server SQL server listening on 127.0.0.1: 1433.
2011-08-07 18:03:42.64 spid10 Starting up database 'Northwind'.
2011-08-07 18:03:42.64 spid11 Starting up database 'OE_MF_USER'.
2011-08-07 18:03:42.73 server SQL server listening on TCP, Shared Memory, Named Pipes.
2011-08-07 18:03:42.73 server SQL Server is ready for client connections
2011-08-07 18:03:43.17 spid5 Clearing tempdb database.
2011-08-07 18:03:43.24 spid10 Starting up database 'z_DM_Test'.
2011-08-07 18:03:43.32 spid9 Starting up database 'z_StagingDB_Test'.
2011-08-07 18:03:44.29 spid8 Recovery is checkpointing database 'msdb' (4)
2011-08-07 18:03:44.73 spid8 Starting up database 'OHC_DB'.
2011-08-07 18:03:45.74 spid5 Starting up database 'tempdb'.
2011-08-07 18:03:47.20 spid9 Starting up database 'OE_MF_Data_Mart'.
2011-08-07 18:03:47.64 spid8 Starting up database 'OE_MF_Staging'.
2011-08-07 18:03:51.79 spid11 Starting up database 'Ad_Hoc_Data_Requests'.
2011-08-07 18:03:52.42 spid10 Analysis of database 'z_DM_Test' (8) is 100% complete (approximately 0 more seconds)
2011-08-07 18:03:52.87 spid10 Starting up database 'stg_7_20_2011'.
2011-08-07 18:03:53.62 spid11 Starting up database 'dm_7_20_2011'.
2011-08-07 18:03:55.40 spid8 Analysis of database 'OE_MF_Staging' (12) is 100% complete (approximately 0 more seconds)
2011-08-07 18:03:55.76 spid9 Analysis of database 'OE_MF_Data_Mart' (11) is 100% complete (approximately 0 more seconds)
2011-08-07 18:04:02.70 spid11 Analysis of database 'dm_7_20_2011' (15) is 100% complete (approximately 0 more seconds)
2011-08-07 18:04:09.81 spid3 Recovery complete.
2011-08-07 18:04:09.81 spid3 SQL global counter collection task is created.
2011-08-07 18:04:12.37 spid51 Using 'xpsqlbot.dll' version '2000.80.2039' to execute extended stored procedure 'xp_qv'.
2011-08-07 18:04:13.00 spid52 Using 'xpstar.dll' version '2000.80.2039' to execute extended stored procedure 'xp_sqlagent_monitor'.
2011-08-09 14:05:22.61 spid57 Using 'dbghelp.dll' version '4.0.5'
*Stack Dump being sent to e:\MSSQL\log\SQLDump0001.txt
2011-08-09 14:05:22.65 spid57 Error: 0, Severity: 19, State: 0
2011-08-09 14:05:22.65 spid57 SqlDumpExceptionHandler: Process 57 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION.
SQL Server is terminating this process..
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 08/09/11 14:05:22 spid 57
*
* Exception Address = 10007685 (mdsGetPerThreadData + 00000035 Line 0+00000000)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 03000724
* Input Buffer 130 bytes -
* EXEC [daps_linked].[master].dbo.sp_help 'dbo.a1_pipe_snapshot'
*
*
* MODULE BASE END SIZE
* sqlservr 00400000 00CBCFFF 008bd000
* ntdll 7C800000 7C8C2FFF 000c3000
* kernel32 77E40000 77F41FFF 00102000
* ADVAPI32 7D1E0000 7D27BFFF 0009c000
* RPCRT4 77C50000 77CEFFFF 000a0000
* Secur32 76F50000 76F62FFF 00013000
* MSVCP71 7C3A0000 7C41AFFF 0007b000
* MSVCR71 7C340000 7C395FFF 00056000
* opends60 41060000 41065FFF 00006000
* SHELL32 7C8D0000 7D0CEFFF 007ff000
* msvcrt 77BA0000 77BF9FFF 0005a000
* GDI32 77C00000 77C48FFF 00049000
* USER32 77380000 77410FFF 00091000
* SHLWAPI 7D180000 7D1D1FFF 00052000
* sqlsort 42AE0000 42B6FFFF 00090000
* ums 41070000 4107DFFF 0000e000
* comctl32 77420000 77522FFF 00103000
* sqlevn70 41080000 4108AFFF 0000b000
* NETAPI32 02CA0000 02CF6FFF 00057000
* AUTHZ 02D00000 02D13FFF 00014000
* COMRES 02E30000 02EF5FFF 000c6000
* ole32 03080000 031B8FFF 00139000
* XOLEHLP 031C0000 031C5FFF 00006000
* MSDTCPRX 031D0000 03248FFF 00079000
* OLEAUT32 03250000 032DAFFF 0008b000
* msvcp60 032E0000 03344FFF 00065000
* MTXCLU 03350000 03368FFF 00019000
* VERSION 03370000 03377FFF 00008000
* WSOCK32 03380000 03388FFF 00009000
* WS2_32 03390000 033A6FFF 00017000
* WS2HELP 033B0000 033B7FFF 00008000
* CLUSAPI 033C0000 033D1FFF 00012000
* RESUTILS 033E0000 033F2FFF 00013000
* USERENV 03400000 034C1FFF 000c2000
* mswsock 034E0000 03520FFF 00041000
* DNSAPI 03530000 03559FFF 0002a000
* winrnr 035A0000 035A6FFF 00007000
* WLDAP32 035B0000 035DDFFF 0002e000
* rasadhlp 03600000 03604FFF 00005000
* SSNETLIB 03DD0000 03DE5FFF 00016000
* NTMARTA 00E50000 00E70FFF 00021000
* SAMLIB 00E90000 00E9EFFF 0000f000
* security 040F0000 040F3FFF 00004000
* hnetcfg 04100000 04159FFF 0005a000
* wshtcpip 04360000 04367FFF 00008000
* SSmsLPCn 043F0000 043F7FFF 00008000
* SSnmPN70 04400000 04406FFF 00007000
* ntdsapi 04490000 044A3FFF 00014000
* kerberos 044C0000 04518FFF 00059000
* cryptdll 04520000 0452BFFF 0000c000
* MSASN1 04530000 04541FFF 00012000
* SQLFTQRY 040C0000 040E5FFF 00026000
* xpsp2res 04940000 04C04FFF 002c5000
* CLBCatQ 04C10000 04C92FFF 00083000
* sqloledb 04CA0000 04D20FFF 00081000
* MSDART 04300000 04319FFF 0001a000
* MSDATL3 04320000 04334FFF 00015000
* oledb32 04F30000 04FA8FFF 00079000
* OLEDB32R 04340000 04350FFF 00011000
* rsaenh 05040000 05074FFF 00035000
* PSAPI 05080000 0508AFFF 0000b000
* xpsqlbot 05090000 05095FFF 00006000
* xpstar 050B0000 050FCFFF 0004d000
* SQLRESLD 05100000 0510BFFF 0000c000
* SQLSVC 05110000 0512AFFF 0001b000
* ODBC32 05130000 0516DFFF 0003e000
* COMCTL32 05170000 05206FFF 00097000
* comdlg32 05210000 05258FFF 00049000
* odbcbcp 05260000 05265FFF 00006000
* W95SCM 05270000 0527CFFF 0000d000
* SQLUNIRL 05280000 052ACFFF 0002d000
* WINSPOOL 052B0000 052D6FFF 00027000
* SHFOLDER 052E0000 052E8FFF 00009000
* odbcint 05400000 05416FFF 00017000
* NDDEAPI 05430000 05437FFF 00008000
* SQLSVC 05440000 05445FFF 00006000
* xpstar 05450000 05458FFF 00009000
* msv1_0 05500000 05526FFF 00027000
* iphlpapi 05530000 05549FFF 0001a000
* comsvcs 05670000 057B4FFF 00145000
* msdasql 05840000 0588CFFF 0004d000
* MSDASQLR 05890000 05893FFF 00004000
* SYODASE 05A20000 05A4EFFF 0002f000
* syodutl 10000000 10055FFF 00056000
* syodbas 05A50000 05AA7FFF 00058000
* libct 05AB0000 05B14FFF 00065000
* libintl 05B20000 05B2BFFF 0000c000
* libcomn 05B30000 05B90FFF 00061000
* libtcl 05BA0000 05BC7FFF 00028000
* libcs 05BD0000 05BDDFFF 0000e000
* odbccp32 05C20000 05C39FFF 0001a000
* msdaps 05DC0000 05DF4FFF 00035000
* msadce 06220000 06277FFF 00058000
* msadcer 042B0000 042B4FFF 00005000
* syodutlR 04230000 04235FFF 00006000
* syodbasR 04270000 04278FFF 00009000
* SYODASER 04280000 04285FFF 00006000
* nlwnsck 04290000 04294FFF 00005000
* nlmsnmp 042A0000 042A4FFF 00005000
* dbghelp 067A0000 0689FFFF 00100000
*
* Edi: 06012DC0: 05A8F234 05BE6280 00000010 00000000 00000010 00000004
* Esi: 03000700:
* Eax: 05BE2770: 1003434C 05BE4E88 0000131C 00000000 00000000 00000000
* Ebx: 00001774:
* Ecx: BE538805:
* Edx: 05301A2C: 00139078 FFFFFFFE 00000001 00001774 00000000 00000000
* Eip: 10007685: 85244E8B 390575C9 0974085E 8504768B EB0A74F6 0FF685EB
* Ebp: 0641DF2C: 0641DF48 051397A0 05301F30 00000001 00000010 04D37580
* SegCs: 0000001B:
* EFlags: 00010206: 00000079 00410043 00480053 004F0043 0050004D 0043003D
* Esp: 0641DEC8: 06012D88 00000010 10007765 10008A77 00000000 05A816BB
* SegSs: 00000023:
* *******************************************************************************
* -------------------------------------------------------------------------------
* Short Stack Dump
* 10007685 Module(syodutl+00007685) (mdsGetPerThreadData+00000035)
* 051397A0 Module(ODBC32+000097A0) (SQLFreeStmt+00000021)
* 05849500 Module(msdasql+00009500) (DllGetClassObject+000070B3)
* 0584953B Module(msdasql+0000953B) (DllGetClassObject+000070EE)
* 05849409 Module(msdasql+00009409) (DllGetClassObject+00006FBC)
* 058496EA Module(msdasql+000096EA) (DllGetClassObject+0000729D)
* 04F40ED3 Module(oledb32+00010ED3) (DllCanUnloadNow+000095D7)
* 04F40F60 Module(oledb32+00010F60) (DllCanUnloadNow+00009664)
* 04F40FC9 Module(oledb32+00010FC9) (DllCanUnloadNow+000096CD)
* 04F40FE5 Module(oledb32+00010FE5) (DllCanUnloadNow+000096E9)
* 04F315D7 Module(oledb32+000015D7)
* 04F32744 Module(oledb32+00002744) (DllGetClassObject+00000E00)
* 0078915A Module(sqlservr+0038915A) (SQLExit+001896B3)
* 7C34347E Module(MSVCR71+0000347E) (EH_prolog+00000046)
* 0062FC69 Module(sqlservr+0022FC69) (SQLExit+000301C2)
* 00419EDC Module(sqlservr+00019EDC)
* 0041A2E2 Module(sqlservr+0001A2E2)
* 0041A100 Module(sqlservr+0001A100)
* 005094DD Module(sqlservr+001094DD)
* 00509619 Module(sqlservr+00109619)
* 41075309 Module(ums+00005309) (ProcessWorkRequests+000002D9 Line 456+00000000)
* 41074978 Module(ums+00004978) (ThreadStartRoutine+00000098 Line 263+00000007)
* 7C34940F Module(MSVCR71+0000940F) (endthread+000000AA)
* 77E6482F Module(kernel32+0002482F) (GetModuleHandleA+000000DF)
* -------------------------------------------------------------------------------
2011-08-09 14:05:23.95 spid57 The exception information has been submitted to Watson error reporting.
2011-08-09 14:05:23.95 spid57 Stack Signature for the dump is 0xC8FBC9E0
2011-08-09 14:05:23.97 spid57 Error: 0, Severity: 19, State: 0
2011-08-09 14:05:23.97 spid57 language_exec: Process 57 generated an access violation. SQL Server is terminating this process..
August 9, 2011 at 12:49 pm
Sounds like ram failure. But I'm not a network admin so I might be 10 miles away on this one.
Can you run the same query straight on the correct server?
August 9, 2011 at 1:14 pm
i ran the following:
dbcc traceon(3604,7300)
go
EXEC daps_linked.master.dbo.sp_help 'a1_pipe_snapshot'
go
i get
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider reported an unexpected catastrophic failure.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x8000ffff: The provider reported an unexpected catastrophic failure.].
August 9, 2011 at 1:15 pm
Catastrophic failure pretty much means hardware failure.
I'd look at the windows event log to find more usefull info.
That's how far my expertise goes into this domain. Might be time to call your network admin for a consult as well.
August 9, 2011 at 1:19 pm
OP started a new thread for the new problem. Please forward the discussion here : http://www.sqlservercentral.com/Forums/Topic1157186-1292-1.aspx
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply