Problem with OPENROWSET

Transact-SQL

Index ‹ SQL Server

- Previous
  - 1
    - Find and delete reversed key pairs. Thought so... I never used such construct (I mean, it depends on the context) before, though. Good to know. Tag: Problem with OPENROWSET
  - 2
    - Can I create a database schema from a DataSet and/or .xsd file? I have some questions about creating SQL Server CE databases. Based on my experiments and what I've read on these forums, it looks like there are a couple ways to create a database schema. I can edit the database schema via the Server Explorer in Visual Studio, or use an external program like SQL Server Management Studio and somehow convert those files to .sdf files. I find Visual Studio's built in tools to be cumbersome to use and limited in functionality, and using SSMS seems like a roundabout way of approaching the problem. I understand Microsoft will be releasing better tools with Orcas, but in the meantime, I'm wondering if there are alternative ways to generate database schemas. For instance, I find Visual Studio's DataSet designer fairly easy to use. The DataSet designer generates schema definitions (.xsd files), and an instantiated DataSet can both read and write schema definitions via Read/WriteXMLSchema. Furthermore, DataAdapter's Fill and FillSchema methods can be used to push a schema from a database to a DataSet. So, can I somehow go the other direction and push a schema from a DataSet to a database It seems like all the tools are there... For example, if I create the DataSet schema, could I use a small app to create a new .sdf file, instantiate a DataSet, write the schema from the DataSet to the database, and then save the .sdf file Or given the generated .xsd file, is there any way to create a SQL database from that Thanks in advance for any replies. Tag: Problem with OPENROWSET
  - 3
    - Can't reinstall sql server 2005 express Hi thanks for trying to help, but as you know I still can't seem to install it. At first I remove all SQL components on the ADD/REMOVE programs windows. I did not remove .net 2.0 framework or IIS from my computer as I need them for other programs. Then I restart the computer and install SQL Server 2005. It installed SQL native client, SQL setup files, SQL VSS writer, and then when it is trying to install SQL services it suddenly stopped and a messagebox appears saying "SQL service won't start please refer to online help". I tried installing like ten times and the same things happen. How can I install SQL server on my computer As for the error 26, I think I forgot to turn on the "remote access" from the system panel in control panel. Tag: Problem with OPENROWSET
  - 4
    - How to protect my parameters Hello. I have a report with parameter called "parm1", that gets a value of "true" or "false" depanding on another parameter. When the report is runnig the parm1 value is "false". How can I protect this parameter from a change by the user I mean - the user can run the report and then add to the url "&param1 = true". Can I do anything against that I tried marking it as "internal" and I thought that now it can get his value only from inside the report but it didn't worked. Any ideas Thanks. Tag: Problem with OPENROWSET
  - 5
    - Check to see if file exist on server did u resolve this and if so how did u do it Tag: Problem with OPENROWSET
  - 6
    - error Are you using any UNION on your query.. Tag: Problem with OPENROWSET
  - 7
    - Formating how to format an integer in sql 2005 for ex: 1.09876 i want to format to 1.1 in access i can use : format(field,'0.0') as field1 thanks Tag: Problem with OPENROWSET
  - 8
    - sql cluster - fulltext error We have a sql server 2000 active/passive cluster running on Windows Server 2003 and within the last 6 months have started getting the following errors which eventually cause the SQL Resource Group to failover. An error occurred in the configuration monitor for instance <SQL Server Fulltext>: 80070005 - Access is denied . SQL is at the latest service pack/hotfixes. (8.00.2187) This cluster has been running fine for the past 3 years with no problems. Nothing has changed on the servers. We've had Symantic Antivirus running on the servers for a long time but the FTDATA folder and the sql data and transaction logs are excluded and nothing has changed on the configuration. We also run Diskeeper 2007 server on these servers but I can't see any problems with this. Once it fails over it will run fine for several days and then the same errors will occur and it fails over again. Any ideas on what might be causing this I can't really find anything on the Microsoft site that matches this error. Note: This has also just started happening on our test cluster which is an identical configuration. Tag: Problem with OPENROWSET
  - 9
    - MSSQLServerOLAPService Stops MSSQLServerOLAPService Service seems to crash rather sporadically during our nightly ETL with the error of: The description for Event ID ( 22 ) in Source ( MSSQLServerOLAPService ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Internal error: An unexpected exception occured. Internal error: An unexpected exception occured.. The time of the error in the event viewer seems to correspond with the step in the ETL that processes our AS 2005 database (Analysis Services Command Task). The job step reports success, and the data is refreshed so I don't understand why it is reporting an error in Event Viewer. The server is x86 with SQL 2005 sp2 installed. Has anyone else run across this Tag: Problem with OPENROWSET
  - 10
    - How to Add Subscription or Subscribers manually through scripts instead of programming Hello everyone, I am new for SQL SERVER 2005 NS. I have gone through some samples about NS. Most of them add subscriptions and subscribers through vbs or .net application. So I got some questions to ask for your help: 1. I just wonder if there are some ways for me to add subscriptions and subscribers manually through T-SQL scripts instead of programming. Because sometimes, we don't need to ask our users to subscribe our message. 2. Is that possible for NS to send notification by SMTP without adding subscription For example, I have already populated all emails from database, what I need is just to send simple notes to these emails, and at this time, I don't need users to subscribe them. I just need to get NS works as database mail at this point - only send email notification without subscription. I am not sure if this is possible. ( I know with databse mail, this can be done easily, how about with SQL NS, any idea ) Would you please give me some advices I appreciate your help in advance. Best Regards, Tag: Problem with OPENROWSET
  - 11
    - Retrieving a datetime with a time of midnight (from a typical datetime) Nothing difficult, I just need a way to generate a new datetime column based on the column [PostedDate], datetime. So basically I want to truncate the time. Thanks a lot. Tag: Problem with OPENROWSET
  - 12
    - Reporting Services Date Format Problem Well I installed SP2 in a hope that it would fix it, initially on my development machine and was going to install it on my server today... glad you've saved me the trouble.. This seems to be a pretty fundamental problem with Reporting Services has no one else got a work around or solution Tag: Problem with OPENROWSET
  - 13
    - Shared SSIS Staging/Dev environment We are in the process of setting up a shared staging/development enviroment in which multiple developers will deploy their packages for testing etc. The plan is to allow the developers to remote in to execute their packages, but we are worried about the concurrency limit of 2 users per terminal services. It is my understanding that you cannot execute packages remotely (ie you have to be on THAT box to kick it off. (FYI - we are not planning on using SQL Agent at this point - That will be with DBAs in UA) Please let me know what options are available/best practices... Thanks! Tag: Problem with OPENROWSET
  - 14
    - Control Result of ExecuteScalar The user is calling a Stored Procedure with ExecuteScalar. When the SQL doesn't find a match, I'd like to return the results of a different SQL. For Example: If this doesn't find a match: select amount from Lookup Where application = @app Then I'd like to return: select amount from Lookup Where application = "DEFAULT" My actual situation is more complex than this. The first SQL is in a CASE statement. After my CASE is done, can I check the current ExecuteScalar return value Or someone determine how many records are in the last SQL to execute Also, ExecuteScalar always seems to get the 1st column of the 1st query. Can I have it get the 1st column from the 3rd query Tag: Problem with OPENROWSET
  - 15
    - Remote Source and Destination Performance ~500,000 rows (depending on row size) should be extremely trivial to process. The timings of which should be under one minute, easily. I'm always amazed at the thought that 1,000,000 rows is a large amount of data. I'll put it into perspective: First Data Resources (one of the major credit card processing companies in the world) processes so many transactions per minute, that if 500,000 rows took over 10 minutes to run, they'd be out of business -- they'd never be able to catch up. With that said, the slow times are usually a factor of (and Jamie listed most of them already): network, local and remote hardware, row size, unique processing constraints (converting non-standard date formats to database date/time fields), whether or not you are logging each transaction, referential integrity checks, etc... Have you looked at the execution plan for your query to see if adding indexes or something might help Tag: Problem with OPENROWSET
  - 16
    - SSRS 2005 & Number of concurrent users Hi, In one CPU installation of SSRS 2005, are there any restriction on number of users accessing the same concurrently Please let me know. Regards, TNR Tag: Problem with OPENROWSET
- Next
  - 1
    - TempDB After you replace the failed drive(s), the 'simplest' solution would be to assign the same Drive letters as was previously used on the failed drive(s). Tag: Problem with OPENROWSET
  - 2
    - Dimension Writeback Hello Dave. In AS2000 it was possible to move members and groups of members in a dimension that was write enabled. I have not tested this on SSAS2005 because my client, that use this feature, is still on AS2000. Would not this be a structural change that could cause problems for BIDS The structure of the dimension in the BI project would not be the same as on the SSAS2005 server Another problem will be if several users can add changes on the same members I accept Mosha's conclusion that it is technically possible to do it, from a client, but would this not create more problems than it solves Regards /Thomas Ivarsson Tag: Problem with OPENROWSET
  - 3
    - Replication: periodic initial snapshot not yet available Hi, Piotr, What's the distribution database max_retention and history_retention settings If you have small values for those retention settings, then the subscription may expire even you set subscription to never expire. You can try to increate distribution db max_retention and history_retention value to see if the problem can be solved. Thanks, Zhiqiang Feng Tag: Problem with OPENROWSET
  - 4
    - Big problem I've write an application for testing RDA Syncronization When i try to connect to SQL Server 2005 Express for rda pull i've an error: The version of the Microsoft OLE Db Provider for SQL server is not connect. Install MDAC 2.8 or later. [600,version = ] I've Windows Vista, Visual Studio 2005 Sp1 + Sp for Vista, Sql Express 2005, SQL Compact Edition (SQL CE 3.1). I've write private string rdaOleDbConnectString = @"Provider=sqloledb.1;Data Source=192.168.1.63,1433;Initial Catalog=GestioneClientiSQL;User Id=sa;Password=pippobaudo;" ; private string rdaUrl = @"http://192.168.1.63/sqlce/sqlcesa30.dll" ; private string localConnection = @"Data Source=\My Documents\Archivio.sdf" ; How can solve my problem Raffaele Tag: Problem with OPENROWSET
  - 5
    - Exactly-Once-On-Order - conversation group question Hi, A SSB newbie question: Looking in a various resources, i see that a conversation group guarantees receiving messages EOID, which means that each message will be received only once, and that messages will be received in the order they were sent. However, the documentation explicitly states that only dialogs guarantee messages order. So, I��m a little bit confused, what's the "In-Order" stand for in "Exactly Once In Order" Thanks, Ido Tag: Problem with OPENROWSET
  - 6
    - SSIS Package Challenge!!! Hi All, Need to accomplish this in SSIS...if someone can point me the flow tips or how to proceed would sincerely appreciate it. The OwnerRecord_1 owns RecordA and RecordB....and so on...the owner record follows the details records Flat File Input RecordA........... RecordB.................. OwnerRecord_1 RecordC................. RecordD................. OwnerRecord_2 .............. Output Solution: TableA ID Records OwnerID 1 RecordA 7 2 RecordB 7 3 RecordC 8 4 RecordD 8 TableB OwnerID Desc: 7 OwnerRecord_1 8 OwnerRecord_2 TIA Tag: Problem with OPENROWSET
  - 7
    - changeing the table name while using transfer sql server object Not supported in the Transfer Objects task. However, you could use an Execute SQL after the table is copied to call sp_rename. Tag: Problem with OPENROWSET
  - 8
    - Inserting data having Japanese Characters(Kanji) in SQL Server 2000 sorry for multiple posts. Here is how the column is defined in table [theatre_name] [nvarchar] ( 200 ) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [theatre_rid] [numeric] ( 9 , 0 ) NULL, Tag: Problem with OPENROWSET
  - 9
    - Inserting on Partitioned View Hello, I'm trying to insert on a partitioned view and I get this error: INSERT INTO dbo.TREP_NOVEDADES ( NMSEC_NOVEDAD, CDCONCEPTO, CDTIPO_VALOR, CDPRECIO, CDTIP_HECT_DTO_PAG, FEGENERACION, FEPAGO_COBRO, CDMETODO_DISTRIBUC, CDTIPO_DCTO_PAGO, VRDESCUENTO_PAGO, SNTERCERO, CDCLASE_NOVEDAD, CDENTIDAD, CDESTADO, CDCAUSA, NMSEC_PREFIJO, SNPAGO_CAJA, SNVAL_PROPOR_PESO, CDTIP_HECT_DISTRIB, CDMONEDA, CDCOMPANIA, NMSEC_NOVEDAD_PADR, SNGENERADA, POEMBARGO, CDTERCERO, CDCIA_TERCERO, NMSEM_GENERACION, NMANO_GENERACION, NMPER_GENERACION, NMSEM_PAGOCOBRO, NMANO_PAGOCOBRO, NMPER_PAGOCOBRO, CDTERCERO_CAJA, CDSUCURSAL_CAJA, CDCOMPANIA_CAJA) VALUES ( 3904, 'C01', 'V', null, null, GETDATE(), GETDATE(), null, null, 25999, 'N', 'F', '016', '01', '000001', null, 'N', 'N', null, 'PESOC', '01', null, 'N', null, null, null, null, 2007, null, null, null, null, null, null, null) Server: Msg 4436, Level 16, State 12, Line 1 UNION ALL view 'TREP_NOVEDADES' is not updatable because a partitioning column was not found. ------------------------------- This is the tables information: CREATE TABLE [dbo].[TREP_NOVEDADES_2006] ( [NMSEC_NOVEDAD] [numeric](12, 0) NOT NULL , [CDCONCEPTO] [varchar] (12) NOT NULL , [CDTIPO_VALOR] [char] (1) NULL , [CDPRECIO] [varchar] (12) NULL , [CDTIP_HECT_DTO_PAG] [varchar] (2) NULL , [FEGENERACION] [datetime] NOT NULL , [FEPAGO_COBRO] [datetime] NULL , [CDMETODO_DISTRIBUC] [char] (1) NULL , [CDTIPO_DCTO_PAGO] [char] (1) NULL , [VRDESCUENTO_PAGO] [numeric](18, 5) NULL , [SNTERCERO] [char] (1) NOT NULL , [CDCLASE_NOVEDAD] [char] (1) NOT NULL , [CDENTIDAD] [varchar] (3) NOT NULL , [CDESTADO] [varchar] (2) NOT NULL , [CDCAUSA] [varchar] (6) NOT NULL , [NMSEC_PREFIJO] [numeric](12, 0) NULL , [SNPAGO_CAJA] [char] (1) NOT NULL , [SNVAL_PROPOR_PESO] [char] (1) NOT NULL , [CDTIP_HECT_DISTRIB] [varchar] (2) NULL , [CDMONEDA] [varchar] (5) NOT NULL , [CDCOMPANIA] [varchar] (2) NOT NULL , [NMSEC_NOVEDAD_PADR] [numeric](12, 0) NULL , [SNGENERADA] [char] (1) NOT NULL , [POEMBARGO] [decimal](7, 4) NULL , [CDTERCERO] [varchar] (15) NULL , [CDCIA_TERCERO] [varchar] (2) NULL , [NMSEM_GENERACION] [tinyint] NULL , [NMANO_GENERACION] [smallint] NULL , [NMPER_GENERACION] [numeric](6, 0) NULL , [NMSEM_PAGOCOBRO] [tinyint] NULL , [NMANO_PAGOCOBRO] [smallint] NULL , [NMPER_PAGOCOBRO] [numeric](6, 0) NULL , [CDTERCERO_CAJA] [varchar] (15) NULL , [CDSUCURSAL_CAJA] [varchar] (4) NULL , [CDCOMPANIA_CAJA] [varchar] (2) NULL , [SNGENERAR_TRG] [char] (1) NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[TREP_NOVEDADES_2006] ADD CONSTRAINT [DF__TREP_NOVE__SNGEN2006] DEFAULT ('S') FOR [SNGENERAR_TRG], CONSTRAINT [TREP_NOVEDADES2006_PK] PRIMARY KEY CLUSTERED ( [NMSEC_NOVEDAD] ) ON [PRIMARY] , CONSTRAINT [TREP_NOVEDADES_2006_NMANOGENER_CK] CHECK ([NMANO_GENERACION] <= 2006), CONSTRAINT [TREP_NOVEDADES_CDMET_DIST2006_CK] CHECK ([CDMETODO_DISTRIBUC] is null or ([CDMETODO_DISTRIBUC] = 'P' or ([CDMETODO_DISTRIBUC] = 'I' or ([CDMETODO_DISTRIBUC] = 'H' or ([CDMETODO_DISTRIBUC] = 'C' or [CDMETODO_DISTRIBUC] = 'D'))))), CONSTRAINT [TREP_NOVEDADES_CDTIP_DCTO2006_CK] CHECK ([CDTIPO_DCTO_PAGO] is null or ([CDTIPO_DCTO_PAGO] = 'H' or ([CDTIPO_DCTO_PAGO] = 'C' or [CDTIPO_DCTO_PAGO] = 'A'))), CONSTRAINT [TREP_NOVEDADES_CDTIPO_VAL2006_CK] CHECK ([CDTIPO_VALOR] is null or ([CDTIPO_VALOR] = 'P' or [CDTIPO_VALOR] = 'V' or [CDTIPO_VALOR] = 'E')), CONSTRAINT [TREP_NOVEDADES_SNGENERAD2006_CK] CHECK ([SNGENERADA] = 'S' or [SNGENERADA] = 'N') GO CREATE TABLE [dbo].[TREP_NOVEDADES_2007] ( [NMSEC_NOVEDAD] [numeric](12, 0) NOT NULL , [CDCONCEPTO] [varchar] (12) NOT NULL , [CDTIPO_VALOR] [char] (1) NULL , [CDPRECIO] [varchar] (12) NULL , [CDTIP_HECT_DTO_PAG] [varchar] (2) NULL , [FEGENERACION] [datetime] NOT NULL , [FEPAGO_COBRO] [datetime] NULL , [CDMETODO_DISTRIBUC] [char] (1) NULL , [CDTIPO_DCTO_PAGO] [char] (1) NULL , [VRDESCUENTO_PAGO] [numeric](18, 5) NULL , [SNTERCERO] [char] (1) NOT NULL , [CDCLASE_NOVEDAD] [char] (1) NOT NULL , [CDENTIDAD] [varchar] (3) NOT NULL , [CDESTADO] [varchar] (2) NOT NULL , [CDCAUSA] [varchar] (6) NOT NULL , [NMSEC_PREFIJO] [numeric](12, 0) NULL , [SNPAGO_CAJA] [char] (1) NOT NULL , [SNVAL_PROPOR_PESO] [char] (1) NOT NULL , [CDTIP_HECT_DISTRIB] [varchar] (2) NULL , [CDMONEDA] [varchar] (5) NOT NULL , [CDCOMPANIA] [varchar] (2) NOT NULL , [NMSEC_NOVEDAD_PADR] [numeric](12, 0) NULL , [SNGENERADA] [char] (1) NOT NULL , [POEMBARGO] [decimal](7, 4) NULL , [CDTERCERO] [varchar] (15) NULL , [CDCIA_TERCERO] [varchar] (2) NULL , [NMSEM_GENERACION] [tinyint] NULL , [NMANO_GENERACION] [smallint] NULL , [NMPER_GENERACION] [numeric](6, 0) NULL , [NMSEM_PAGOCOBRO] [tinyint] NULL , [NMANO_PAGOCOBRO] [smallint] NULL , [NMPER_PAGOCOBRO] [numeric](6, 0) NULL , [CDTERCERO_CAJA] [varchar] (15) NULL , [CDSUCURSAL_CAJA] [varchar] (4) NULL , [CDCOMPANIA_CAJA] [varchar] (2) NULL , [SNGENERAR_TRG] [char] (1) NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[TREP_NOVEDADES_2007] ADD CONSTRAINT [DF__TREP_NOVE__SNGEN] DEFAULT ('S') FOR [SNGENERAR_TRG], CONSTRAINT [TREP_NOVEDADES2007_PK] PRIMARY KEY CLUSTERED ( [NMSEC_NOVEDAD] ) ON [PRIMARY] , CONSTRAINT [TREP_NOVEDADES_2007_NMANOGENER_CK] CHECK ([NMANO_GENERACION] > 2006), CONSTRAINT [TREP_NOVEDADES_CDMET_DIST2007_CK] CHECK ([CDMETODO_DISTRIBUC] is null or ([CDMETODO_DISTRIBUC] = 'P' or ([CDMETODO_DISTRIBUC] = 'I' or ([CDMETODO_DISTRIBUC] = 'H' or ([CDMETODO_DISTRIBUC] = 'C' or [CDMETODO_DISTRIBUC] = 'D'))))), CONSTRAINT [TREP_NOVEDADES_CDTIP_DCTO2007_CK] CHECK ([CDTIPO_DCTO_PAGO] is null or ([CDTIPO_DCTO_PAGO] = 'H' or ([CDTIPO_DCTO_PAGO] = 'C' or [CDTIPO_DCTO_PAGO] = 'A'))), CONSTRAINT [TREP_NOVEDADES_CDTIPO_VAL2007_CK] CHECK ([CDTIPO_VALOR] is null or ([CDTIPO_VALOR] = 'P' or [CDTIPO_VALOR] = 'V' or [CDTIPO_VALOR] = 'E')), CONSTRAINT [TREP_NOVEDADES_SNGENERAD2007A_CK] CHECK ([SNGENERADA] = 'S' or [SNGENERADA] = 'N') go CREATE VIEW TREP_NOVEDADES AS SELECT * FROM dbo.TREP_NOVEDADES_2006 UNION ALL SELECT * FROM dbo.TREP_NOVEDADES_2007 Tag: Problem with OPENROWSET
  - 10
    - My SSIS problems MatthewRoche wrote: Still, it would not hurt to have an "Advanced Options" (or whatever - pick your name) page in the wizard that asked data size and volume questions, and set some of these properties more intelligently based on this input. This is a good idea. Either yourself or TheViewMaster should post a Connect feedback item requesting this feature to be added. Tag: Problem with OPENROWSET
  - 11
    - Infinite Loop while cube processing in SSAS 2005 SP2 Hi, We build the cube, quite large one processing 4 million records of data daily. It was working fine in SSAS 2005 Service Pack 1. But recently Service Pack 2 for SSAS came out, in the releases notes it said SP2 solves some of the issues we had on the drillthrough. So, after applying SP2 on the production server, running on 32 bit Windows 2003 SP2, the ETL process is stuck. It stuck while processing the cube, on large dimension. I was wondering whether microsoft released hotfix for this issue or anyone else had this problem. I note again, that whole thing was working fine under SSAS 2005 SP1 environment, but i believe in SP2 there is some flaw that leads to infinite loop while processing the cube. Regards, Bakhodir Makhamadov Tag: Problem with OPENROWSET
  - 12
    - SSAS cube deployed failed I am building one cube with one dimesion table and one fact table in SQL2005SP2. After the cube is created and I got the following error during the deployment 1. Internal error: The operation terminated unsuccessfully. 2. Errors in the OLAP storage engine: An error occurred while processing the 'Open AR' partition of the 'Open AR' measure group for the 'Solomon DW' cube from the Analysis Services Project2 database. 3.Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. I then go to the partition and complete the aggration wizard without any problems. But I am still getting the same error when I try to deploy the cube. I am kind of new to the SSAS so any help is appreciated. Thanks! wenchi Tag: Problem with OPENROWSET
  - 13
    - Replication with multiple server with the same server name Ok. I wouldnt consider those disconnected. You are still not going to have a problem with the servers with the same name. SQL Server doesnt use the name to identify the server. Once the server makes the initial connection to sync SQL will give it a GUID and perform all operations with the server using the GUID not the name. So when you delete a server it will not delete all servers with the same name. As for having multiple publications. I suspect you can setup each publication on a different web path in IIS. That way when you run your script it will know which one. However i find it hard to believe that it would not work on the same path anyways as the publication names will be different. If you paste some of your scripts it will give me a better understanding of what you are doing. Martin Tag: Problem with OPENROWSET
  - 14
    - Lookup Functionality You are aware that the lookup caches all of its data at the beginning of the data flow, aren't you So, by default, it can't match duplicates on rows that you are inserting in the same data flow You can work around this by using an aggregate transform to eliminate the duplicates, or by disabling caching on the lookup (found on the advanced tab), which forces it to query the database for each row. Unfortunately, disabling caching makes it run slower, and doesn't guarantee you won't get duplicates, because of the way rows are handled in batches. Also, I am fairly positive that because "u" and "u" are two different characters, the lookup will not match them. If you looked at the actual bytes making up those two strings, they would be different. You might need to use the Fuzzy Lookup to do your matching in this scenario. Tag: Problem with OPENROWSET
  - 15
    - Indexed View not used by the Query Optimizer Hello All, I have a series of Stored Procedure that has a query taking a join of 5 tables. These tables are quiet large with couple of them having around 10 million rows. As this is a DSS application having periodic data loads, I thought of creating Indexed View on top of these tables. Now the problem is that the Indexed View is not directly used by the optimizer. I need to change my queries and put a WITH (NOEXPAND) query hint to make sure the indexed views are used. This is inspite getting dramatic improvement in the query timings (from 64 secs down to 3 secs) after using the Indexed Views. I would like to know what can be the possible reason for the optimizer not using the Indexed View by itself. Is it because my Indexed View caters to multiple queries or I am missing out on something basic. Thanks in Advance, Mitesh Shah Tag: Problem with OPENROWSET
  - 16
    - How to stop automatic Replication Validation? Under EM's Replication Monitor/Publishers/pub1/publisher1 there is a "Validate Subscriptions" option on the right-click menu. Since I see unexplained Replication Validation activity I think that the "Validate Subscriptions" option is turned on. But there is no "turn-off" button or way to say "stop." Does anyone know how this "feature" works Thanks Tag: Problem with OPENROWSET

b00gieman

Hi!

I'm using OPENROWSET to import data from an excel file into a MS SQL table.I got it to work.....Now,my problem is that not all the data from the SQL table is imported and some values are different than the ones in the excel file.

For example,in the excel file,I have the value:87987845.In the SQL table,the value is:8.79878e+007.Any idea on what causes this

Re: Problem with OPENROWSET

oj

This is a limitation of Excel ISAM driver. The determined/sampled datatype is
based on the majority. Once this is selected, the rest will be returned as NULL.
One way to circumvent this is to force an ImportMixedTypes to text.

e.g.
select * from
OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\test.xls;IMEX=1',Sheet1$)

Re: Problem with OPENROWSET

b00gieman

I added imex=1 and the situation is the same....Any idea on what else could I do

Re: Problem with OPENROWSET

oj

What do you have for these two keys

HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Jet/4.0/Engines/Excel/ImportMixedType-s
HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows

You want to set ImportMixedTypes to "Text" and TypeGuessRows to "0".

Additional info can be found here.

http://support.microsoft.com/ id=194124

Re: Problem with OPENROWSET

b00gieman

The value I'm getting in the sql table is the exponential value of the number,right In this case,are those modifications to the keys still necessary

Thanks!

Re: Problem with OPENROWSET

oj

Yes. The format is based on the sampling (first 8 rows is the default). You'd want to change the two keys to control the import.

Re: Problem with OPENROWSET

b00gieman

Thank you for your answer.Also,I have a question:where are keys supposed to be changed I have a Database server.Are those keys supposed to be set on the database server

Re: Problem with OPENROWSET

oj

Yes. You change the values on the database server.

HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Jet/4.0/Engines/Excel/ImportMixedType-s
HKEY_LOCAL_MACHINE/SOFTWARE/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows

You want to set ImportMixedTypes to "Text" and TypeGuessRows to "0".