.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
ASPEvil
david stephan
Santhakumar Munuswamy
Fauzul Azmi
Post New Web Links

SELECT FROM OracleView behaves differently if embedded in a SQL Server View

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :
 

(This forum heading says "access TO Sql Server", but I don't know where else to file this question.)I am running Windows Server 2008 R2 64-bit with SQL Server 2008 64-bit. I am using Oracle Provider for OLE DB for a Linked Server to Oracle 11g with the Oracle 11g client. If I run "SELECT from an Oracle view" in Management Studio it returns data just fine. If that SELECT is used as the definition of a SQL Server view, and a SELECT is done from that view, I get an error:

Msg 7356, Level 16, State 1, Line 2  The OLE DB provider "OraOLEDB.Oracle" for linked server "P101" supplied inconsistent metadata for a column. The column "DATESTAT" (compile-time ordinal 6) of object ""S4250000"."SIGNED_CONTRACTS"" was reported to have a "DBCOLUMNFLAGS_ISFIXEDLENGTH" of 16 at compile time and 16 at run time.

I am aware of the difficulties of dealing with dates between SQL Server and Oracle. In fact we will probably circumvent our problem by creating a custom view in Oracle with all the CAST functions to make the dates tolerable. I would still like to know more about why the dates are handled when the bare select from Oracle is executed, but they are not handled when the same


View Complete Post


More Related Resource Links

Need to install Report View Redistributables on the report server?

  
Hi there:   I've developed a webapps utilizing MS reporting service reports and it works fine on my local machine. When I promoted to production server, it failed . People say I need to install MS reportViewer redistributables on the report server.   1. What is this MS ReportView redistributables anyway??? I've already included ReportView control in my apps and can someone explain to me why I need to install on the report server?   2. If I can not install the redistributables on the report server as the client may refuse to install due to whatsoever reasons, it there anyway to get around?   3. For any winForms apps, do I need to install the redistributables on the report server?   thanks    Hui--Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

How to create a view in my database from a table in another server/database

  
Is there a way to create a view in my database referencing a table in another server/database if I have the connection information.  I know how to do it in Oracle, simply create a database link, but have never done so in sqlserver.

20,000 foot view: Sql Server clustering vs. Windows OS Clustering

  
I read that Sql Server Failover requires Windows Sevrer clustering to be in place first. (The former builds on the latter). Why is this the case? In other words, what does "sql server clustering" bring to the mix  that normal Windows clustering doesn't supply? TIA, Barkingdog  

Calculated date field displays differently in List View and Data View web parts

  
In SharePoint GUI I created a simple custom list with a calculated Date/Time field (Due).  The calculation is =[Created]+7.  I'm trying to display this data on a page using a DVWP.  But first, for testing purposes, I also created a second calculated Date/Time (TestCreate) field in my custom list that is simply =[Created]. In the List View from within SharePoint, the dates show as expected (for example): Created = 8/12/2010 3:45 PM Due = 8/19/2010 3:45 PM TestCreate = 8/12/2010 3:45 PM In SharePoint Designer, I added a Data View web part and the above three data elements (each configured to display mm/dd/yyyy hh:mm:ss tt).  I see the time displayed for the example list item as: Created = 8/12/2010 3:45:30 PM Due = 8/19/2010 10:45:00 AM TestCreate = 8/12/2010 10:45:00 AM I've tested a couple scenarios and the calculated fields always display five hours in the past (along with dropping the seconds).  It seems like the DVWP is returning the date in a different time zone (with no seconds).  The lcid is set right (1033).  I didn't see any time zone settings for the DVWP specifically.  I'm basically stuck.  Could someone help me figure this one out. Thanks.

SQL Server 2008 Linked Server SELECT INTO issue

  
I have a SQL Server 2008 installation running on a clustered Windows Server 2008 R2. I am trying to execute a query on a remote SQL Server to create a table. In order to do so, I call a stored procedure on the remote SQL Server. The stored procedure's description is as follows: ------------------------------------- Create procedure [dbo].[USP_RemoteExec] @varSQL varchar(max) as declare @tempSql nvarchar(max); set @tempSql = CONVERT(nvarchar(max),@varSQL); exec sp_executesql @tempSql ------------------------------------- I pass the following command to the remote stored procedure as follows: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty3'') is not null drop table testdb.dbo.tmp_testEmpty3; create table testdb.dbo.tmp_testempty3(col1 int, col2 varchar(20));' go select * from [servername].testdb.dbo.tmp_testEmpty3 go This statement returns the following: --------------------------------- col1,col2 (0 row(s) affected) But, when I run the following statement: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty'') is not null drop table testdb.dbo.tmp_testEmpty; select * into testdb.dbo.tmp_testEmpty from (select top 10 mytab.[col1] as [col1] , mytab.[col2] as [col2] , mytab.[col3] as [col3] FROM testdb.dbo.mytab as [mytab] with (nolock)) as A' go select * from [

SQL Server 2008 Linked Server SELECT INTO issue

  
I have a SQL Server 2008 installation running on a clustered Windows Server 2008 R2. I am trying to execute a query on a remote SQL Server to create a table. In order to do so, I call a stored procedure on the remote SQL Server. The stored procedure's description is as follows: ------------------------------------- Create procedure [dbo].[USP_RemoteExec] @varSQL varchar(max) as declare @tempSql nvarchar(max); set @tempSql = CONVERT(nvarchar(max),@varSQL); exec sp_executesql @tempSql ------------------------------------- I pass the following command to the remote stored procedure as follows: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty3'') is not null drop table testdb.dbo.tmp_testEmpty3; create table testdb.dbo.tmp_testempty3(col1 int, col2 varchar(20));' go select * from [servername].testdb.dbo.tmp_testEmpty3 go This statement returns the following: --------------------------------- col1,col2 (0 row(s) affected) But, when I run the following statement: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty'') is not null drop table testdb.dbo.tmp_testEmpty; select * into testdb.dbo.tmp_testEmpty from (select top 10 mytab.[col1] as [col1] , mytab.[col2] as [col2] , mytab.[col3] as [col3] FROM testdb.dbo.mytab as [mytab] with (nolock)) as A' go select * from [

Select "Name" from all Tables in SQL Server

  
I am trying to Select column "Name" from all the Tables in SQL Server     Dim MyConnection2 As New SqlClient.SqlConnection("Data Source=XXX")            Dim mydtadapter2 = New SqlDataAdapter("Select o.name + '.' + c.name from syscolumns c, sysobjects o where (c.name & 128 = 128) and o.name = c.name order by o.name", MyConnection2)            Dim ds As New DataSet            mydtadapter2.Fill(ds, "name")                                  clientname2.DataSource = ds.Tables("name").DefaultView            clientname2.DataBind()

Linked Server using ASE OLE DB Provider and Select * into where datetime maps to SQL Server datetime

  
Hi, I'm currently using MSSQL Server 2008 x64 SP1 [Microsoft SQL Server Standard Edition (64-bit)], version 10.0.2531.0.  I'm using Sybase ASE 15.0.3 ESD#3, or 15.5 ESD#1, and doing a simple Select into query where my original table on ASE, pubs..sales with DDL: stor_id     char(4)     not null, ord_num  varchar(20) not null, date         datetime    not null I run this query with Linked Server, using ASE OLE DB Provider x64, 15.5.0.1016: select * into mssql_sales from openquery(ASE1550, 'select * from pubs2..sales') The table mssql_sales has the datetime datatype from ASE mapped to datetime2(7). [With MSSQL Server 2005 the mapping is straight to datetime]. So far no problem.  However, if doing a bulk load out of this table, mssql_sales and trying to bulk into a table on MSSQL Server 2005: C:\Program Files\Microsoft SQL Server\100\Tools\Binn>bcp odbc.dbo.mssql_sales3 out c:\temp\mssql_sales3.dat -Spvero-2k8vm\pvms2008 -T -n -k Starting copy... 30 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total     : 1      Average : (30000.00 rows per sec.) C:\Program Files\Microsoft SQL Server\100\Tools\Binn>bcp test.dbo.mssql_sales in c:\temp\mssql_sales3.dat -Spvero-2k3\pvms2005 -T -n -k Starting copy..

Select random 10 records in sql server 2005 in terms of time

  
Hello,i have a query that select random records in access database in terms of time : "SELECT TOP 10 * FROM Tbl ORDER BY Rnd(-10000000*TimeValue(Now())*[ID])"now i wish to convert this query for using in sql server 2005 such that return random records in term of time 

Linked Server using ASE OLE DB Provider and Select * into where datetime maps to SQL Server datetime

  
Hi, I'm currently using MSSQL Server 2008 x64 SP1 [Microsoft SQL Server Standard Edition (64-bit)], version 10.0.2531.0.  I'm using Sybase ASE 15.0.3 ESD#3, or 15.5 ESD#1, and doing a simple Select into query where my original table on ASE, pubs..sales with DDL: stor_id     char(4)     not null, ord_num  varchar(20) not null, date         datetime    not null I run this query with Linked Server, using ASE OLE DB Provider x64, 15.5.0.1016: select * into mssql_sales from openquery(ASE1550, 'select * from pubs2..sales') The table mssql_sales has the datetime datatype from ASE mapped to datetime2(7). [With MSSQL Server 2005 the mapping is straight to datetime]. So far no problem.  However, if doing a bulk load out of this table, mssql_sales and trying to bulk into a table on MSSQL Server 2005: C:\Program Files\Microsoft SQL Server\100\Tools\Binn>bcp odbc.dbo.mssql_sales3 out c:\temp\mssql_sales3.dat -Spvero-2k8vm\pvms2008 -T -n -k Starting copy... 30 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total     : 1      Average : (30000.00 rows per sec.) C:\Program Files\Microsoft SQL Server\100\Tools\Binn>bcp test.dbo.mssql_sales in c:\temp\mssql_sales3.dat -Spvero-2k3\pvms2005 -T -n -k Starting copy..

Cannot select 'SQL Server Compact Edition' from Server Type drop down

  
I have seen this issue addressed for SQL Server 2005 but not 2008. I have installed these components:SQL Server 2008 (Browser, Management Objects, Native Client, Policies and Setup Support Files)SQL Server Compact 3.5 (for Devies, Server Tools and SP1)In the SQL Server Management Studio, on the Connect to Server dialog, the "Server Type" drop down list is disabled and "Database engine" is the default. Does anyone know what I need to do to make "SQL Server Compact Edition" a choice in the "Server Type" drop down list?

Need to allow user to view all logins on SQL Server

  
SQL Server 2005/2008. By default, on SQL Server any user can see only his login or NT Groups, which he belongs to (both through Security\Logins in SSMS and sys.server_princapals DMV). I need to permit one power user to see all SQL Server logins. But only see, not change. Can you help me? P.S. VIEW ANY DEFINITION helps, but this permissions are too much.

Performance Monitor Counters To View How "Busy" A SQL Server Is

  
What are some good performance monitor counters to look at, when trying to figure out how "busy" our SQL 2008 server is? We were told to look at: SQLServer: SQL Statistics: Batch Requests/Sec and SQLServer: Databases: Transaction/Sec . Any other critical ones? Is there a counter to get the number of  selects, updates, and deletes independently per second? Thanks.

Create view that amins to tables of another database on the same sql server instance

  
Hi to everybody, I found a situation ever met before. I develop on Dynamics NAV 5.01 and I have developed a method to be able to see some particular tables of an external database. In substance it deals with a property of tables of Dynamics NAV. When I create a table in NAV, I can create it in 3 different ways: table common to all the companies table  for company or table linked to a view.    This last case is mine, on the same db of NAV I have created a view with some fields, I have created in NAV a table linked with equal fields and types. Until here all normal.    The view, however, aims to another database that doesn't center anything with NAV but that it is on the same intance of SQL server.    The consumer that accesses NAV is a consumer type database SQL Server and has the permitted db_public and db_datareader on both the database. Then he can read the views on the db of nav both on the db of the other database.    When it tries to enter from the console of sql server, with the consumer database, all it works, if I do it by NAV, it show me an error "The server principal "username" is not able to access the database "some_database_name" under the current security context. (Microsoft SQL Server, Error: 916) "    If I add on the database NAV to the consumer, the role db_owner,

XSL Lookup to another List in a View. Joining in data from another List. xsl:value-of select=

  
I've been able to create a DataView with data joined from two SP Lists. I did this using SPD, a linked Source, and doing joined subviews.  The technique works nicely, but I have to create my DataView from scratch.As an alternative, I took an existing AllItems View in SPD, converted it to XSL, and was able to add a column from another List as follows:   <TD Class="{$IDA1VDGH}" style="width: 62px; height: 23px"><xsl:value-of select="/dsQueryResponse/Master/Rows/Row/@Lastname" />The problem is, it reports the column for the first row everytime. I'm new to XSL and was wondering..    using XSL, and preserving all the original columns already in the View, is there anyway to make the added column smarter, maybe with a filter?I noticed in my working example, I have this filter (I guess where the subview join magic is)            [@Title = $dvt_ParentRow/@Key] which does not resolve syntax:I tried this, but it does not work, only finding one match, and it also seems to get set for the entire Dataview and not just the column.     [../../../Master/Rows/Row/@Title = @Key] I guess I'm looking either for the SPD steps, or XSL code for adding a single column from another list, joined in by a column in both lists.Thanks for any help or information.

SharePoint Users Behind a Forefront TMG Server Cannot Access Explorer View in Document Libraries

  
SharePoint server via a Forefront TMG server in a separate sub-domain. These users cannot access Explorer View in Document Libraries nor can they map a drive to the SharePoint directory structure, as users who are in the same sub-domain as the SharePoint server can. How do I setup SharePoint and the TMG server so that these users can map a drive to the SharePoint site? Office SharePoint 2007 SP2 Windows 2008 Server SP2

Linked Server using ASE OLE DB Provider and Select * into where datetime maps to SQL Server datetime

  
Hi, I'm currently using MSSQL Server 2008 x64 SP1 [Microsoft SQL Server Standard Edition (64-bit)], version 10.0.2531.0.  I'm using Sybase ASE 15.0.3 ESD#3, or 15.5 ESD#1, and doing a simple Select into query where my original table on ASE, pubs..sales with DDL: stor_id     char(4)     not null, ord_num  varchar(20) not null, date         datetime    not null I run this query with Linked Server, using ASE OLE DB Provider x64, 15.5.0.1016: select * into mssql_sales from openquery(ASE1550, 'select * from pubs2..sales') The table mssql_sales has the datetime datatype from ASE mapped to datetime2(7). [With MSSQL Server 2005 the mapping is straight to datetime]. So far no problem.  However, if doing a bulk load out of this table, mssql_sales and trying to bulk into a table on MSSQL Server 2005: C:\Program Files\Microsoft SQL Server\100\Tools\Binn>bcp odbc.dbo.mssql_sales3 out c:\temp\mssql_sales3.dat -Spvero-2k8vm\pvms2008 -T -n -k Starting copy... 30 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total     : 1      Average : (30000.00 rows per sec.) C:\Program Files\Microsoft SQL Server\100\Tools\Binn>bcp test.dbo.mssql_sales in c:\temp\mssql_sales3.dat -Spvero-2k3\pvms2005 -T -n -k Starting copy..
Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend