Urgent (External Table Is Not In The Expected Format)

Aug 10, 2007



Hi,
I have 7 excel files that i have to import to sql server database and in that 6 of them work fine. But when i try to import the 7 one i get an

oledb exception saying that External table is not in the expected format.

So can i someone please help me with this error.

Regards
Karen


View 6 Replies


ADVERTISEMENT

External Table Is Not In The Expected Format

Aug 9, 2007

Hi,
   I am trying to import an excel spreadsheet to the sql server database, I have 7 spreadsheets. in that 6 of them work fine, but when i try to import the 7th i am getting and error called
External Table is not in the expected format
System.Data.OleDb.OleDbException: External table is not in the expected format
 
Any help will be appreciated.
Regards,
Karen

View 8 Replies View Related

How To Convert A Date Into The Format Expected By SQL Server.

Jun 2, 2007

I need to open and read a text file using C#. Once I have read all of the values from the text file, I will need to insert some of those values into a SQL Server database table. One of the values read from the text file will be a date in the format YearMonthDay. For example: 20070601
So my problem is that I don't know how to convert this date into the date format that is expected for a SQL server datetime value.
Can someone help me out. How do I do this?
 
 

View 2 Replies View Related

Urgent (The Property ‘DefaultValue’ Of Report Parameter ‘split’ Doesn't Have The Expected Type.)

Aug 2, 2007



Hi,
I have declared an internal paramter and given the default non queried value as



select @split = case when max(rowid)%2 = 1 then (max(rowid)/2) + 1 else max(rowid)/2 end



and when i run it i am getting the error -- The property 'DefaultValue' of report parameter split doesnt have the expected type. What should i do to solve it. Any help is appreciated.

Regards,
Karen

View 1 Replies View Related

External Images Appear Broken In MHTML Format

May 2, 2007

Server Software:

SQL Server 2000 (all sps up to date)

SSRS 2000 (all sps up to date)



We have an issue when emailing a report that contains external images.



When the email recipient receives the report the images appear broken.



Although when we check the report in the report server it displays correctly and all images are intact.



We also noticed that if we manually "export" the report to the MHTML format the images appear broken but if we immediately export the report again the images appear perfectly. (the report must be exported to MHTML twice in order to work. This has been verified and is repeateable)



It is almost as if the MHTML format cannot reference the images fast enough to include them in the report on the first try. But, it is able to do so on the second attempt...



We would rather use external images as we have 700+ reports and manually updating embedded images takes quite a while. Then we can update a single image to change all reports.



Any help would be appreciated.

Thank you.

View 1 Replies View Related

Help Needed Little Urgent---how To Convert The String Date To Standard Date Format In SQL Table

Sep 28, 2007

Using DTS package in 2000 version, I am dumping TXT file contents into SQL Table,

I have one column having date in format YYYYMMDD(20070929) and corresponding column in SQL is datetime, but it fails on data type mismatch.

I have no choice of making date column in SQL to string or Varchar etc,

is there any way to make that date column in SQL to convert the value upon transformation from format (YYYYMMDD) to M/DD/YYYY (9/29/2007).

many many thanks,

View 2 Replies View Related

Urgent Format Que

Oct 31, 2007

 I have a urgent need for a date format.I have a third part
tool which inserts date into the database in the format  ( 7/1/2007 ) 
but what i want to display is 07/01/07).Kindly suggest fast as I have to put this to production shortly.Thanks

View 4 Replies View Related

Urgent Help On Date Display Format

Nov 12, 2007

Hi,
I am using a sql database and vb.net... In form i display a date from calendar as smalldatetime in the format of dd/MM/yyyy by changing the web.config file to culture = "en-GB"... It did display as dd/MM/yyyy with no doubt... Problem is in sql database it is display as mm/dd/yyyy, there4 when i retrieve it out, it is not the format i wan... so can any1 helps mi? I need do many adding and subtracting to the date so a constant dd/MM/yyyy format is veri important to mi... I cant save it as yyyy/mm/dd cos my boss dun wan it... If use Convert method when i select * out how to convert the date zzz
Helps would be greatly appreciated

View 9 Replies View Related

Urgent Help!-how To Format After Getting Rows? For Newline

Oct 8, 2004

while(@@FETCH_STATUS=0)
begin
print 'inside while'

set @BodyContents = @BodyContents+@gotclientName+@gotproductName
set @Body= @Body + @BodyContents + ','

fetch next from resultcursor into @gotclientName,@gotproductName

print 'going outside of while'
end --while end

Client Name Product Name
aaa test1
bbb test4

how to format row like above. currently i am getting
aaa,test1,bbb,test4

View 1 Replies View Related

Date Format: Urgent Help Needed

Dec 12, 2007


I have a date "4/25/2007"
but the requirement is to have data as "04/25/2007"
.if month is single digit it should have leading zeroes, same with date,
can some tell me how to convert this.

View 2 Replies View Related

Urgent Help : Date Format Conversion

Dec 12, 2007

I have a date "4/25/2007"
but the requirement is to have data as "04/25/2007" in datamart.
if month is single digit it should have leading zeroes, same with date,
can some tell me how to convert this.

View 7 Replies View Related

URGENT - CURRENCY STRING FORMAT IN SSAS

Apr 24, 2008



In cube created by me, there is one measure called SALES AMOUNT, i defined its Format String as Currency.
And there is one Calculated measure Average Sales Amount, that also have the Format string as Currency.

But for some strange reasons, when I Pull Sales Amount it is coming with $ prefix, but when I pull Average Sales Amount it is coming with prefix Rs....

can anyone help me, what is going wrong here.. its very urgent.

View 7 Replies View Related

Transact SQL :: How To Format A String In A Format Coming From A Table

Jun 4, 2015

I have a table which stores date-of-birth in varchar 19861231(yyyymmdd). A view takes this data. I want to store this date as mmddyyyy in the view. How can we achieve this?

View 18 Replies View Related

URGENT: UNIX DATE FORMAT CONVERION TO SQL SERVER

May 9, 2001

I HAVE ONE COLUMN CONTAING THE INTEGER TYPE DATA COMING in UNIX DATE FORMAT. NEED TO CONVERT IT TO THE SQL SERVER DATE TIME FORMAT.

NOTE: UNIX IS STORING THE DATE IN SECONDS STARTING FROM 1970/1/1

ANYBODY CAN HELP ME TO SOLVE THIS PROBLEM.

e.g. The UNIX date time is 973101026 need to be converted to SQL DATE TIME

View 2 Replies View Related

Code For Creating A Table In SQL From External Source

Nov 18, 2005

I'm looking to create a table in SQL using data from the as/400. I need some code that extracts it from as/400 using odbc and I need it to loop and create that table every minute.
The file its retrieving is a live file but not very large.. I need to basically poll the data. Does anyone have code for something like this..
thanks

View 8 Replies View Related

Stored Procedure Can't Access Table On External Database

Mar 6, 2005

Hi

I have created a .net application using visual studio .net and sql server destop edition on my pc. I have exported the database tables and stored procedures and imported them into a Sql Server database on a web hosting service. The web host does not allow me to access this database directly through visual studio .net.

My connection string to the external database works ok and I can access my stored procedures through my web pages. I know they accept parameters and that I can receive Return Values from them. However, whenever I try to access any of the tables on the external database through a stored procedure, I get a sqlException saying that the table cannot be found (Invalid object name 'UserList').

I have created a text type command which selects data from one of the tables and this runs through without any errors. I have also managed to Insert a row onto one of the external tables also by using a text type command. My only problem seems to be with commands using stored procedures.

Just in case this is the problem - the owner of the table/procedures on my desktop is shown as dbo but on the external database the owner of the tables is shown as [domainname].co.uk_dbuser while the stored procedures owner is still dbo.


Example of stored procedure on external database:

/****** Object: Stored Procedure dbo.AddUser Script Date: 01/03/2005 21:10:06 ******/
CREATE PROCEDURE dbo.AddUser
(
@Username Varchar(20),
@Password Varchar(20)
)
AS
(
Select User_ID From UserList
Where User_Username = @Username
)
GO

Have tried changing dbo.AddUser to [domain].co.uk_dbuser.Adduser but this would not save because there were too many full-stops!

Any help would be greatly appreciated as I am completely stuck.

John

View 2 Replies View Related

Unexpected Error From External Database Driver (MS Access Linked Table)

Oct 19, 2006

OS: Windows XP (SP2)
App: MS Access 2003 (SP2)
DB: MS SQL 2000 (SP4, 2040)

User is using MS Access linked table to query database (using ODBC). User can open the query in MS Access. However when the user goes to export the data to MS Excel format, the user receives the error message:

"Unexpected Error from External Database Driver (22)."

A trace on the db, reveals that the user process is attempting to log in to the server as 'Admin'. However, when the query with the linked table is first opened, the user is prompted for username/password (non Windows authentication).

Any ideas? I googled and found some stuff, but nothing directly related and most of what I found was supposedly fixed with SP1.

Any help appreciated. I am not an Access guru.

Regards,

hmscott

View 1 Replies View Related

Transact SQL :: Getting Data From External Assembly Into Temp Table Inside Stored Procedure

Oct 8, 2015

I have a Custom .net assembly which retrieves some data, basically just a set of data containing, ID, value,

I need my stored procedure to return this data as a table.

My question what/how is fastest way to do this, my assembly can return anything you want, a datatable, a hashtable, list of(class) etc..

View 3 Replies View Related

Inserting Distinct Data From One Table In Another Table, How?!?Really Urgent And Needing Help!!!

May 24, 2007

 Hi, I have a table in which I will insert several redundant data. Don't ask why, is Integration services, it only reads data and inserts it in a SQL table. THis way, I have a SQL table with several lines repeating them selves. What I want to do is create a procedure that reads the distinct data and inserts it in another table, but my problem is that I am not able to select data line by line on the original table to save it in local variables and insert it on the another table, I just can select the last line. I've tried a while cycle but no succeed. Here is my code: create proc insertLocalizationASdeclare @idAp int, @macAp varchar(20), @floorAp varchar(2), @building varchar(30), @department varchar(30)select @idAp = idAp from OLTPLocalization where idAp not in (select idAp from dimLocalization)select @macAp=macAp,@floorAp=floorAp,@building=building,@department=department from OLTPLocalizationif (@idAp <> null)beginInsert into dimLocalization VALUES(@idAp,@macAp,@floorAp,@building,@department)endGO This only inserts the last line in the "oltpLocalization" table. O the other hand, like this:create proc aaaaasdeclare @idAp as int, @macAp as varchar(50), @floorAp as int, @building as varchar(50), @department as varchar(50)while exists (select distinct(idAp) from OLTPLocalization)begin    select @idAp =idAp from OLTPLocalization  where idAp not in (select idAp from dimLocalization)    select @macAp = macAp from OLTPLocalization where idAp = @idAp    select @building = building from OLTPLocalization where idAp = @idAp    select @department = department from OLTPLocalization where idAP = @idApif (@idAp <> null)begin    insert into dimLocalization values(@idAp,@macAp,@floorAp,@building,@department)endendgo this retrieves every distinct idAp in each increment on the while statement. The interess of the while is really selecting each different line in the OLTPLocalization table. I did not find any foreach or for each statement, is there any way to select distinct line by line in a sql table and save each column result in variables, to then insert them in another table? I've also thought about web service, that reads the distinct data from the oltpLocalization into a dataset, and then inserts this data into the dimLocalization table. Is there anything I can do?Any guess?Really needing a hand here!Thanks a lot!

View 1 Replies View Related

Selecting From A Table, When I Only Have The Table Name As A String Format

Apr 7, 2006

is there some way to allow this to execute, using a function or something?:



select * from 'cusomer'



you see i have a list of about 300 table name in string format, and i want to use a cursor to itterate through each table, but as all the table names are in string format i cant get it done. any ideas??

View 1 Replies View Related

Expected Value Not Being Set.

Aug 17, 2004

Hi,
I'm trying to set the value of the variable @prvYearMonth thru this sp. In the query analyzer I execute the following code to the see the results of my 'CabsSchedule_GetPrevYearMonth' SP, but the only see "The Command(s) completed successfully in the result. What am I missing??

Thanks in advance


CREATE PROCEDURE CabsSchedule_GetPrevYearMonth
(
@prvYearMonth int OUTPUT
)

AS
BEGIN
SET @prvYearMonth = (SELECT MAX(YearMonth) FROM CabsSchedule)
END
GO

View 3 Replies View Related

RIGHT(string, #), I Am Not Getting What's Expected!

Jul 1, 2004

I have the following

SELECT @cLastBarcode
SELECT @tmpCount = RIGHT(@cLastBarcode, 4)
SELECT @tmpCount


SELECT @cLastBarcode returns '14001DT0010006'

BUT

SELECT @tmpCount returns nothing. The RIGHT(....) function does not render any results. I am expecting '0006'.

I read that the data type must be compatible with varchar. The @cLastBarcode was declare as char(25). I have even tried casting the @cLastBarcode char string to type varchar.

Any hints?

Mike B

View 2 Replies View Related

DTA: Expected Improvement 0%

Dec 31, 2007

I did a trace on a production DB for many hours, and got more than 7 million of "RPC:Completed" and "SQL:BatchCompleted" trace records. Then I grouped them and obtained only 545 different events (just EXECs and SELECTs), and save them into a new workload file.

To test the workload file, I run DTA just for 30 minutes over a restored database on a test server, and got the following:
Date 28-12-2007
Time 18:29:31
Server SQL2K5
Database(s) to tune [DBProd]
Workload file C:Tempfiltered.trc
Maximum tuning time 31 Minutes
Time taken for tuning 31 Minutes
Expected percentage improvement 20.52
Maximum space for recommendation (MB) 12874
Space used currently (MB) 7534
Space used by recommendation (MB) 8116
Number of events in workload 545
Number of events tuned 80
Number of statements tuned 145
Percent SELECT statements in the tuned set 77
Percent INSERT statements in the tuned set 13
Percent UPDATE statements in the tuned set 8
Number of indexes recommended to be created 15
Number of statistics recommended to be created 50
Please note that only 80 of the 545 events were tuned and 20% of improvement is expected if 15 indexes and 50 statistics are created.

Then, I run the same analysis for an unlimited amount of time... After the whole weekend, DTA was still running and I had to stop it. The result was:
Date 31-12-2007
Time 10:03:09
Server SQL2K5
Database(s) to tune [DBProd]
Workload file C:Tempfiltered.trc
Maximum tuning time Unlimited
Time taken for tuning 2 Days 13 Hours 44 Minutes
Expected percentage improvement 0.00
Maximum space for recommendation (MB) 12874
Space used currently (MB) 7534
Space used by recommendation (MB) 7534
Number of events in workload 545
Number of events tuned 545
Number of statements tuned 1064
Percent SELECT statements in the tuned set 71
Percent INSERT statements in the tuned set 21
Percent DELETE statements in the tuned set 1
Percent UPDATE statements in the tuned set 5
This time DTA processed all the events, but no improvement is expected! Neither indexes/statistics creation recomendation.

It does not seem that Tuning Advisor crashed... Usage reports are fine and make sense to me.

What's happening here? It looks like DTA applied the recomendations and iterated, but no new objects where found in DB.

I guess that recomendations from the first try with only 80 events were invalidated by the remaining from the long run.

I couldn't google an answer for this. Help!!!

Thanks in advance.

++Vitoco

View 1 Replies View Related

SQL CLR Udf Returns 0 Instead Of Expected Value

Dec 12, 2007

My first foray into the SQL CLR world is a simple function to return the size of a specified file.
I created the function in VS2005, where it works as expected.
Running the function in SSMS, however, returns a value of zero, regardless of the file it is pointed at.

Here's the class member code:


Public Shared Function GetFileSize(ByVal strTargetFolder As String, ByVal strTargetFile As String) As Long

' Returns the size of the specified file.

' Parameters: strTargetFolder = path to target file, strTargetFile = target file name.

Dim lngFileSize As Long

Dim objFileInfo As FileInfo

' Confirm file exists.

If Not File.Exists(strTargetFolder & "" & strTargetFile) Then

Return -1

End If

Try

objFileInfo = My.Computer.FileSystem.GetFileInfo(strTargetFolder & "" & strTargetFile)

lngFileSize = objFileInfo.Length

Catch

' TODO: add error handling; system folders cause error during processed.

End Try

Return lngFileSize

End Function

In SSMS (sp2), here's my assembly steps (this is my local dev machine; I have admin rights):


sp_configure 'clr enabled', 1

GO

RECONFIGURE

GO

-- For file system access, set the database TRUSTWORTHY property.

ALTER DATABASE dba_use

SET TRUSTWORTHY ON

CREATE ASSEMBLY GetFolderInfo

FROM 'C:ProjectsGetFolderInfoGetFolderInfoinDebugGetFolderInfo.dll'

WITH PERMISSION_SET = UNSAFE


Here's the udf declaration:


CREATE FUNCTION dbo.udfGetFileSize( @strTargetFolder nvarchar(200), @strTargetFile nvarchar(50) )

RETURNS bigint

AS EXTERNAL NAME

GetFolderInfo.[GetFolderInfo.clsFolderInfo].GetFileSize


And the function call - note the target file is on a remote server. Actual file name differs slightly:


SELECT dbo.udfGetFileSize('\SomeServerName$MSSQL2000MSSQLData', 'SomeDBName_Data.MDF')


This always returns zero with no error displayed. Running Profiler was little help and there's not much in the Event Log.
The function returns correct values in VS2005.
The assembly is created with UNSAFE because using EXTERNAL_ACCESS resulted in a security error that prevented the assembly from being created, let alone running. Security is, I suspect, at the root of this issue as well, but I'm not sure what or where to look to verify this.

Any assistance is greatly appreciated.

View 3 Replies View Related

Is This Expected Behavior?

Aug 8, 2007

I posted this at the asp.net forums but somone suggested I post it here. So:




Try this in sql server:

select COALESCE(a1, char(254)) as c1 from

(select 'Z' as a1 union select 'Ya' as a1 union select 'Y' as a1 union select 'W' as a1) as b1

group by a1

with rollup order by c1

select COALESCE(a1, char(255)) as c1 from

(select 'Z' as a1 union select 'Ya' as a1 union select 'Y' as a1 union select 'W' as a1) as b1

group by a1

with rollup order by c1



The only difference is that the first one uses 254 and the second one uses 255. The first sorts like this:

W
Y
Ya
Z
þ

The second one sorts like this:

W
Y
ÿ
Ya
Z

Is this expected behavior?

View 1 Replies View Related

More Reads Then Expected

Jul 18, 2006

So I€™m at a dead-end looking for the reason behind the following behavior. Just to make sure no one misses it, the 'behavior' is the difference in the number of reads between using sp_executesql and not.

The following statements are executed against a SQL 2000 database that contains >1,000,000 records in the act_item table. They are run using Query Analyzer and the Duration and Reads come from SQL Profiler

SQL 1:
exec sp_executesql N'update act_item set Priority = @Priority where activity_code = @activity_code', N'@activity_code nvarchar(40),@Priority int', @activity_code = N'46DF335F-68F7-493F-B55E-5F9BC6CEBC69', @Priority = 0

Reads: ~22000
Duraction: 250-350 ms

SQL 2:
DECLARE @Priority int
DECLARE @Activity_Code char(36)

SET @Priority = 0
SET @Activity_Code = '46DF335F-68F7-493F-B55E-5F9BC6CEBC69'
update act_item set Priority = @Priority where activity_code = @activity_code

Reads: ~160
Duration: 0 ms

Random information:

Activity_code is an indexed field on the table, although it is not the primary key. There are a total of four indexes on the table, none of which include the priority as one of the fields.
There are two triggers on the table, neither of which is executed for this SQL statement (there is an IF UPDATE(fieldname) surrounding the code in the trigger)
There are no foreign relationships
I checked (using perfmon) to see if a compilation/recompilation was happening. No it's not.
Any suggestions as to avenues that could be examined would be appreciated.

TIA

View 3 Replies View Related

Print Table Format

Feb 25, 2004

I want to print the format of a table. You can view the format from enterprise manager under properties, but I was wondering if there is some command that you can use in query analyser to view the stucture of a table?

View 5 Replies View Related

Changing Table Format

Mar 11, 2004

Hi there,

Its probably easier to draw this problem than describe it, so here goes:

I have a sales forecast table (A,B,C are products [PRODUCT], the dates refer to the month for which the forecast is [FORECAST_DATE], and the integer is the forecast sales qty [FORECAST_QTY])

A 01/03/2004 30
B 01/03/2004 28
C 01/03/2004 24
A 01/04/2004 11
B 01/04/2004 09
C 01/04/2004 41

I need to convert the table into a more sensible format, like this:

(NB ...Dots are just there to help with formatting - basically I'm talking about a field for FORECAST_03_2004, FORECAST_04_2004 etc etc)


........ 01/03/2004....01/04/2004
A..........30..................11
B..........28..................09
C..........24..................41


I'm really no t-SQL guru, and I can't seem to get any joy out of BOL. It must only be a tiny bit of code. Can anyone help?

Thanks very much

Sam

View 4 Replies View Related

Change Format Of Table

May 14, 2008

Hi,

I imported data into a fact table in sql with SSIS.
The fact table looks like this:

Question Answer Type
q1 a1 t1
q2 a2 t2
q3 a3 t3

The problem is that i have to insert the data from the fact table into another table that looks like this:

art_id template_id Body
23 1 q1
23 2 a1
23 3 t1
25 1 q2
25 2 a2
25 3 t2

How can i insert the data correctly?

Thanks



Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

View 1 Replies View Related

Table Schema In XML Format

Apr 4, 2014

I have a table.

Column AColumn BColumn C
123
456

I need the schema details of the table like datatype of column A , B and C as well as default values of all the columns and all other details of those 3 columns.

View 2 Replies View Related

Exporting A Table In CSV Format

Jun 14, 2006

hi

i wanted to export a table with all the results in it into a csv file...

is there any script that i can use to achieve this?

View 2 Replies View Related

How To Format Number In A Table

Jun 1, 2015

I have a table that has an Associate id for each agent.   The id is 8 digits sometimes starting with 0 (06956461) and sometimes not 0 (98039585).   How would I format this?

View 2 Replies View Related

Create New Table Format

Jan 11, 2006

Im new / novice user to writing SQL scripting. I have two tables and table1 has two columns

uslid and groupid,

uslid1, group1

uslid1, group2

uslid1, group3

uslid3, group1

uslid3, group3          etc

the second table2 has three columns

uslid, name, and location

uslid1, john, bldg 4

uslid2, jane, accounting

uslid3, joe, mail room

. I am trying to do a inner join but not working and not sure if this will work to create new temp table.  

example of what i am trying to get in new temp table on one row:

uslid, name, location, groupid1, groupid2, groupid3

uslid1, john, bldg 4, group1, group2, group3

uslid3, joe, mail room, goup1,          , group3

Hope i explained this ok. Im using 2003 server sql 2000 ver 7,  thank you in advance.

 

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved