Problem in Importing data from excel to sql server

I have excel file that has field named Purpose. Its max length is 400 character. I import this file to sql server database table. And also i change the purpose field in sql server database table with nvarchar 400. But when i run this job, it gave me error message:

Error at source for row number 1215. Errors encountereed so far in this task: 1.
Data for source column 18 ('Purpose') is too large for the specified buffer size.

What should i do so that i still can import the data from excel to sql server database?

Thanks for your help.I have far fewer problems when I take spreadsheets and put them in Access and then import them into sql server from access than I do just importing from excel.

problem in export of csv file into sql server

I am trying to export a CSV file into sql server. I wrote a query to perform select fields from csv file using OPENROWSET. I am getting all the values in a single column, but I need to get in three different columns. Any can body can help me



The query is as follows

Select * from OpenRowset ('MSDASQL','Driver= {Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=” Directory name” \;Extended properties=''ColNameHeader=True; Format=Delimited;''','select * from Sample.csv')



Sample.csv

col1, col2, col3 ---- all the values are in single cell of excel csv file
1, a, abc
2, sfasf, sdgagas



Output of query is

col1,col2,col3 --- all the values are coming in one column and with comma between them
1,a,abc
2,sfasf,sdgagas

can any body help me where i am going wrong?

Thanks in advance
suryamight be easier to just import the entire file to a staging table and the work with it from there.|||would u like to give me some sample code|||Doh - something wrong with my pc, please ignore 2 duplicate answers and refer the top one.|||Have you used DTS in this case to import the rows which is an easier solution or what is requirement to use OPENROWSET statement.|||Have you used DTS in this case to import the rows which is an easier solution or what is requirement to use OPENROWSET statement.
See this http://www.sql-server-helper.com/tips/read-import-excel-file-p01.aspx fyi.

Problem in Datatype Date Convertion

Hi,

My source is flat file and my destination is SQL SERVER 2005 using SSIS TOOL.

In my source file i got a date column which is in ISO standards ex: 20050131

I have taken source flat file data type as database date [DT_DBDATE] and in

destination table i declared data type as datetime.

When i start debugging i am getting an error saying that data conversion is not possible.

Can you please help me out how to solve the problem, what data types do i need to take in source and destination and is there any necessity of using Data Conversion Transformation.

If, so please tell me how to do.

With Regards

Satish

What is the full error message? It should tell you in which component the error is occurring.

-Jamie

sql

Problem in attaching Database (Microsoft Management Studio)

I want to change a database to different physical file. So i try the following step:

Detach the database

Attach the new different file

But i get an error "....... SQL server, error:5133".

It still needs the original file.

How to solve this problem?

many-many thanks,

JRLiem

Have you tried something like this?

USE MyDatabase

GO

ALTER DATABASE MyDatabase

MODIFY FILE (NAME = MyDatabase, FILENAME = 'H:SQLData\MyDatabase.mdf')

GO

ALTER DATABASE MyDatabase

MODIFY FILE (NAME = Mylog, FILENAME = 'G:SQLlogs\MyDatabase_log.ldf')

GO

Restart SQL Server |||

a database has at least two files

the .mdb and the .ldf files

you have to specify where these files are located during the attach

|||

Thanks, Andy.

It works.

|||Actually, the LDF file is really not necessary in some cases if you specify to SQL Server to generate a new LDF file during the attach process (provided that the MDF file is not severely damaged)|||Actually the LDF file is only needed if the database was not clearly shutdown. After a clean shutdown, the database is in a consistent state and can be "recovered" by adding another "clean" logfile.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Problem importing Excel data into SSMS

Hi,

When I try to use the Import and Export Wizard to import a good .xls file I keep getting this error:

"Could not find installable ISAM. (Microsoft JET Database Engine)"

It occurs in both SSMS or SSIS. It also occurs with other flat files like .csv.

The OS is XP, w/ office 2003.

here are the full error details:

at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at Microsoft.SqlServer.Dts.DtsWizard.DTSWizard.GetOpenedConnection(WizardInputs wizardInputs, String connEntryName)
at Microsoft.SqlServer.Dts.DtsWizard.Step1.OnLeavePage(LeavePageEventArgs e)

I don't understand why OleDb connection is involved to the Jet when this is SQL2K5 full version.

Please anyone help.

Carl

http://databases.aspfaq.com/database/how-do-i-solve-could-not-find-installable-isam-errors.html for all the issues to resolve.|||

Thanks Satya,

This link is dead. I had previously found this resource and chased down the Google cached version

http://72.14.253.104/search?q=cache:Vyh8_PKhHrwJ:databases.aspfaq.com/database/how-do-i-solve-could-not-find-installable-isam-errors.html+how-do-i-solve-could-not-find-installable-isam-errors.html&hl=en&lr=&strip=1

All it says is to reinstall the MDAC. Well, when I try to do that the system (XP) says there is no need for it as a more recent version is included as part of the OS.

This article describes registry changes to the Jet engine.

http://support.microsoft.com/default.aspx/kb/283881

This machine's registry does not have these keys at all, even though both Excel 2k3 and Access 2k3 work just fine. The fix is to reinstall or repair the Office Pro installation.

I have poor experiences with reinstalling Office 2003. Many customized settings are kept in the registry and wiped out in the reinstall/repair. Before I embark on this journey I don't understand why I even need Office for the operation I am trying to do?

BTW the same error occurred when I tried to import a csv file too, not just an .xls

I wrote a little c# class to convert the file to xml and then standard DAL to "import" the data into SQL Server db. Worked like a charm, but in the future I would still like to be able to do it in SSMS. Most likely on a remote server there would not even be any installation of Office.

Thanks again

Carl

|||See this blog entry too http://blogs.wdevs.com/Gaurang/archive/2005/06/15/5112.aspx and I appreciate your feedback.|||

Satya,

great tip here http://blogs.wdevs.com/Gaurang/archive/2005/06/15/5112.aspx

They talk about extended properties in a connection string.

If I were not doing the connection programmatically but using the SSMS Object explorer, where should I find the connection for the (local) server instance?

Databases -> MyDatabase ->Security ->Users ->dbo
Here there is a link in the lower left to view the connection properties but no extended properties field.

The only place I could find such a field is in
Databases -> System Databases -> master ->Security ->Users -> SERVERNAME\SQLServer2005MSFTEUser$SERVERNAME$MSSQLSERVER

That however is not the db I want to import to. I added an extended property anyway "Excel 8.0;IMEX=1" just for kicks but it didn't do any good.

Any more ideas?

Carl

|||

You can use SP_ADDEXTENDEDPROPERTY as per this http://msdn2.microsoft.com/en-us/library/ms190243.aspx link.

|||

My question was not how to do it programmatically but how to do it using the SSMS GUIs.

I guess the pros do not like using the GUIs too much, and I am starting to see why.

Thanks

Carl

problem importing csv delimited text file into a sql server 2005 table

I am using the Bulk Insert command and trying to import a CSV delimited text file into a table and I am having problems with the quote field delimiters ", " The command below works but it takes in all the "" quotes as well and the field delimiter comma , works only if the commas are the separators only. If I have a comma within a address field for example then the data gets imported into the wrong fields. What can I use to identify that the text qualifier is ". I don't see where I can use the bulk insert command to determine this. Is there another command that I can use or am I using this command incorrectly. I thank you in advance for any response or suggestion you may have.

BULK INSERT AdventureWorks.dbo.MbAddress

FROM 'a:\mbAddress.txt'

WITH (

DATAFILETYPE = 'char',

FIELDTERMINATOR=',',

ROWTERMINATOR='\n',

CODEPAGE = '1252',

KEEPIDENTITY,

KEEPNULLS,

FIRSTROW=2)

Here is a sample ascii file I am importing as well you can see that 6330 has a extra comma in the address line.

"AddressAutoID","Memkey","Type","BadAddress","Address1","Address2","Address3","City","State","Zip","Foreign","CarrierRoute","Dpbc","County","CountyNo","ErrorCode","ChangeDate","UserID"
6317,26517,1,0,"1403 W. Kline Ave","","","MILWAUKEE","WI","53221","","",0.00,"MILWAUKEE",79,"",1/25/2006 0:00:00,"admin"
6318,26225,1,0,"501 Dunford Dr","","","BURLINGTON","WI","53105","","",0.00,"RACINE",101,"",1/25/2006 0:00:00,"admin"
6319,20101,1,0,"2115 Cappaert Rd #35","","","MANITOWOC","WI","54220","","",0.00,"MANITOWOC",71,"",1/25/2006 0:00:00,"admin"
6320,23597,1,0,"728 Woodland Park Dr","","","DELAFIELD","WI","53018","","",0.00,"WAUKESHA",133,"",1/25/2006 0:00:00,"admin"
6321,23392,1,0,"7700 S. 51st St","","","FRANKLIN","WI","53132","","",0.00,"MILWAUKEE",79,"",1/25/2006 0:00:00,"admin"
6322,26537,1,0,"W188 S6473 GOLD DRIVE","","","MUSKEGO","WI","53150","","",0.00,"WAUKESHA",133,"",1/26/2006 0:00:00,"admin"
6323,25953,1,0,"3509 N. Downer Ave","","","MILWAUKEE","WI","53211","","",0.00,"MILWAUKEE",79,"",1/26/2006 0:00:00,"admin"
6324,19866,1,0,"10080 E. Mountain View Lake Rd. #145","","","SCOTTSDALE","AZ","85258","","",0.00,"MARICOPA",13,"",1/27/2006 0:00:00,"admin"
6325,25893,1,0,"W129 N6889 Northfield Dr. Apt 114","","","MENOMONEE FALLS","WI","53051-0517","","",0.00,"WAUKESHA",133,"",1/27/2006 0:00:00,"admin"
6326,26569,1,0,"8402 64th Street","","","KENOSHA","WI","53142-7577","","",0.00,"KENOSHA",59,"",1/27/2006 0:00:00,"admin"
6327,24446,4,0,"83 Sweetbriar Br","","","LONGWOOD","FL","32750","","",0.00,"SEMINOLE",117,"",1/30/2006 0:00:00,"admin"
6328,19547,1,0,"4359 MERCHANT AVENUE","","","SPRING HILL","FL","34608","","",0.00,"HERNANDO",53,"",2/8/2006 0:00:00,"admin"
6329,26524,1,0,"264 Lakeridge Drive","","","OCONOMOWOC","WI","53066","","",0.00,"WAUKESHA",133,"",2/10/2006 0:00:00,"admin"
6330,23967,1,0,"3423 HICKORY ST","100 Tangerine Blvd., Brownsville, TX 78521-4368","Texas Phone Number: 956-546-4279","SHEBOYGAN","WI","53081","","",0.00,"SHEBOYGAN",117,"",2/15/2006 0:00:00,"admin"
6331,25318,1,0,"3960 S. Prairie Hill Lane Unit 107","","","Greenfield","WI","53228","","",0.00,"MILWAUKEE",79,"",2/20/2006 0:00:00,"admin"
6332,24446,1,0,"83 Sweetbriar BR","","","LONGWOOD","FL","32750","","",0.00,"SEMINOLE",117,"",2/21/2006 0:00:00,"admin"
6333,26135,1,0,"P.O. Box 8 127 Main Street","","","CASCO","WI","54205","","",0.00,"KEWAUNEE",61,"",2/21/2006 0:00:00,"admin"

Hi

You can do this by using a formatfile. You can define each field in a formatfile and specify a differend field terminator for each field.

For example:

<?xml version="1.0"?>

<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<RECORD>

<FIELD ID="1" xsi:type="CharTerm" TERMINATOR="""," MAX_LENGTH="30"/>

<FIELD ID="2" xsi:type="CharTerm" TERMINATOR="""\n" MAX_LENGTH="30"/>

[....]

</RECORD> <ROW>

<COLUMN SOURCE="1" NAME="c1" xsi:type="SQLNVARCHAR"/>

<COLUMN SOURCE="2" NAME="c2" xsi:type="SQLNVARCHAR"/> </ROW> </BCPFORMAT>

=== Edited by Hatzi74 @. 25 Apr 2006 2:49 PM UTC===
Edit2: You could also use the Data Import Wizzard... Select "Flatfile" as source, and then match the fielddelimiters for every field the way you need them to be...

For example... After a Text -> Number you would need --",-- as a delimiter if you go from text -> text you will need --","-- as a delimiter and when coming from number -> text you need --,"--

Hope this makes sense. Also the row delimiter could be adjusted to match --"\n-- if the last field is a text field. But this will leave the field "dirty" (It will include a tailing ")

|||

Thank you for your quick reply. I did try a non xml format file using the bcp command and it came out like the info below. However, It worked on importing but I still had the same problem with the commas as the delimiter. I wanted to add the quotes as a delimiter but I could not figure out how to do that in this type of file.

I will try a xml format this time but I am not sure by your instructions how to tell it that the file has quotes for the text qualifiers. I think you are saying I need a "," as a delimiter

Thank you so much for your time and effort. I am going to try it.

Most sincere,

Sarah

my format file below (.fmt type)

9.0
18
1 SQLCHAR 0 100 "," 1 AddressAutoID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 100 "," 2 Memkey SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 100 "," 3 Type SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 100 "," 4 BadAddress SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 100 "," 5 Address1 SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 100 "," 6 Address2 SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 100 "," 7 Address3 SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 100 "," 8 City SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 100 "," 9 State SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 100 "," 10 Zip SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 100 "," 11 Foreign SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 100 "," 12 CarrierRoute SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 100 "," 13 Dpbc SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 100 "," 14 County SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 100 "," 15 CountyNo SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 100 "," 16 ErrorCode SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 100 "," 17 ChangeDate SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 100 "\r\n" 18 UserID SQL_Latin1_General_CP1_CI_AS

|||

Try this:

3 SQLCHAR 0 100 "," 3 Type SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 100 ",""" 4 BadAddress SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 100 """," 5 Address1 SQL_Latin1_General_CP1_CI_AS

|||

I am trying that, but it does not seem to be working.

Sarah

|||

I think it is getting confused with the quotes because they seem to be reserved for the program or something. I get an error.

do you know the bcp command I need to get the xml format? I will try that as well.

Sarah

|||

Hi

I just wanted to thank you for your reply and that you somewhat directed me into the right direction.

I just wanted to let you know that I finally figured it out. I had to strip the " quotes out of the ascii file and replace them with | pipes and then I created the fmt (format file) and imported the data. Here is a copy of the fmt file just for your own information. I had to tell it exactly where the pipes started and ended for each field delimter. I tried setting up the format file to look for the quotes but it had a problem with that because " " were being used in the format file.

Anyways I got it and now I am having a problem accessing the A:\ drive, using the bulk insert command. sql server thinks I am accessing the actual servers A:\ and not my local drive. wow what a nightmare just to read a simple ascii file into a table. Unfortunatley the operator has to import data from floppy diskettes. I will repost this problem.

Anyways, thank you very much for your imput.

Most sincere,

Sarah Diane Reid

Sample Format File below:

9.0
18
1 SQLCHAR 0 100 "," 1 AddressAutoID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 100 "," 2 Memkey SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 100 "," 3 Type SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 100 ",|" 4 BadAddress SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 100 "|,|" 5 Address1 SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 100 "|,|" 6 Address2 SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 100 "|,|" 7 Address3 SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 100 "|,|" 8 City SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 100 "|,|" 9 State SQL_Latin1_General_CP1_CI_AS
10 SQLCHAR 0 100 "|,|" 10 Zip SQL_Latin1_General_CP1_CI_AS
11 SQLCHAR 0 100 "|,|" 11 Foreign SQL_Latin1_General_CP1_CI_AS
12 SQLCHAR 0 100 "|," 12 CarrierRoute SQL_Latin1_General_CP1_CI_AS
13 SQLCHAR 0 100 ",|" 13 Dpbc SQL_Latin1_General_CP1_CI_AS
14 SQLCHAR 0 100 "|," 14 County SQL_Latin1_General_CP1_CI_AS
15 SQLCHAR 0 100 ",|" 15 CountyNo SQL_Latin1_General_CP1_CI_AS
16 SQLCHAR 0 100 "|," 16 ErrorCode SQL_Latin1_General_CP1_CI_AS
17 SQLCHAR 0 100 ",|" 17 ChangeDate SQL_Latin1_General_CP1_CI_AS
18 SQLCHAR 0 100 "|\r\n" 18 UserID SQL_Latin1_General_CP1_CI_AS

Problem generating error file using BULK INSERT or BCP thru xp_cmdshell.

BCP thru xp_cmdshell from stored procedure:

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE

EXEC sp_configure 'xp_cmdshell', 1;

RECONFIGURE

EXEC xp_cmdshell 'bcp database.dbo.table in c:\scheduled.csv -S SERVER\SQLEXPRESS -T -t, -r\n -c -e "error.txt"';

This is returning the following error code. I even tried placing the command in a seperate command file and calling that with no success. If I run this from the command line the error file generation does work.

=================================================================

SQLState = HY000, NativeError = 0

Error = [Microsoft][SQL Native Client]Unable to open BCP error-file

=================================================================

Error message when using BULK INSERT as follows:

BULK INSERT database.dbo.table from 'c:\unscheduled.csv' with

(FIELDTERMINATOR = ',', ERRORFILE = 'c:\error.txt');

Returns the following error message:

=================================================================

Msg 4861, Level 16, State 1, Procedure pro_cedure, Line 9

Cannot bulk load because the file "c:\error.txt" could not be opened. Operating system error code 80(The file exists.).

Msg 4861, Level 16, State 1, Procedure pro_cedure, Line 9

Cannot bulk load because the file "c:\error.txt.Error.Txt" could not be opened. Operating system error code 80(The file exists.).

=================================================================

The Bulk Insert actually creates a empty error.txt file (0kb) and never preforms the insert, I can not find any examples of anyone using the -ERRORFILE switch on BULK INSERT. Prolly some default security setting to allow file creation/modification I am missing. Anyone help me out? Thanks.

EDIT: SQL SERVER EXPRESS 2005 - WINXP PRO SP2

bcp or bulk insert will not overwrite or append to the error file. If you get an error the first time, and want to rerun your command, you need to either delete these files, or specify a new location for the error file.

|||

Unfourtunetly I am deleting the file, I'm still doing so by hand in testing.

On bcp thru xp_cmdshell it does not generate an errorfile at all (this actually works just fine from the command line just not thru a stored procedure), with bulk insert it generates a error file which is completely blank (even tho I know there is 6 rows that cannot be imported) of size 0kb, so totally empty. Also it does not actually execute the insert.

Most likely it is a security setting. I can not find it however. Another possiblity is maybe I disabled a required service.

Anyways, thanks for the reply.

Running SQL Express 2005 on WINXP PRO SP2.

|||

Does the service account which SQL Server is running under have write permissions to the disk?

According to the bulk insert error message, it does (your message says the file exists), but you say in the next post you're deleting it?

|||

Ah your the bomb.

SQLExpress service was running under network authority not local system account. I switched it to local system and now the bulk import with errorfile switch is working from sqlcmd. Write permissions or something, anyways I know where to look now, thanks!

Problem exporting to pdf

When I export a report to pdf I sometimes get the error 'There was an error
opening the document. The file is damaged and could not be repaired.' When I
give different parametervalues there is no problem with the export (of the
same report).
I examined the created pdf-file (with notepad) and noticed that at the end
there is the html-code for the following errormessage: Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown. (rrRenderingError); Exception of type
Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
thrown.; Index was out of range. Must be non-negative and less than the size
of the collection. Parameter name: indexThis may have already been fixed - make sure you have installed SP1.
If it is happening on an SP1 machine, please email me a copy of your .pdf,
.rdl, and .rdl.data so I can take a look.
Thank you.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"kabouterman" <kabouterman@.discussions.microsoft.com> wrote in message
news:DCC8F9A1-E00E-473A-9A95-37ADBF497649@.microsoft.com...
> When I export a report to pdf I sometimes get the error 'There was an
> error
> opening the document. The file is damaged and could not be repaired.' When
> I
> give different parametervalues there is no problem with the export (of the
> same report).
> I examined the created pdf-file (with notepad) and noticed that at the end
> there is the html-code for the following errormessage: Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown. (rrRenderingError); Exception of type
> Microsoft.ReportingServices.ReportRendering.ReportRenderingException was
> thrown.; Index was out of range. Must be non-negative and less than the
> size
> of the collection. Parameter name: index|||When I tried the export again there was no longer a problem with the pdf-file.
SP1 was already installed.

Problem exporting to CSV format

Hi,
When I export my report to csv format and then open the csv file, then I am getting the Data in my report. But in addition to that, I am getting a row that contains comma delimited list of the names of the controls(textboxes) that I have used in my Layout of RDL file for that report. Why does this happen? How do I avoid it?You can try using the NoHeader deviceinfo to suppress the header row http://msdn2.microsoft.com/en-us/library/ms155365.aspx.

Problem exporting data using Excel destination (wrong format)

Hi there,

I have designed a package that works perfectly well, exporting data to an excel file from an ole db source. The problem is that in the excel destination file, columns of data that originally were numbers, are formatted as text. It would be just annoying if it weren't because I use those figures in a pivot table that operates with them.

Any idea on how to tell Excel that those columns are numbers?

Thx in advance

What is the original data type in the OLE DB source of those columns? If they are strings you may need tu use the Data Conversion transformation. Does the excel file already exists? if so, what are the format of the columns?

I run a quick test loading 100 rows od AdventureWorks.Sales.SalesOrderDetail table into an Excel file and all the data types mapped fine. The only thing is that I used the Excel Destination in BIDS to create the file and the destination tab using the 'Name of the excel sheet' -->New... button; it generated an statement like:

CREATE TABLE `Excel Destination` (
`SalesOrderID` INTEGER,
`SalesOrderDetailID` INTEGER,
`CarrierTrackingNumber` NVARCHAR(25),
`OrderQty` SMALLINT,
`ProductID` INTEGER,
`SpecialOfferID` INTEGER,
`UnitPrice` MONEY,
`UnitPriceDiscount` MONEY,
`LineTotal` NUMERIC (38,6),
`rowguid` UNIQUEIDENTIFIER,
`ModifiedDate` DATETIME
)

I only had to adjust the Numeric data type precision and I worked just fine.

Rafael Salas

|||

Thx for your answer Rafael.

The numeric data from the ole db data source is in four-byte signed int [DT_I4]. I have a data conversion between the ole db data source and the excel destination and I've tried to both leave the numeric fields unchanged and "convert" them into the same type. Regarding the excel file, I use a template pre-formatted that i copy into a new file through a system file task in the control flow. I've tried several formatting in the columns (general, numeric, etc...) to no avail...

I've also tried in the excel destination to create the worksheets in which I export the data through that "new button" and with a command very similar to the one you posted, with type for the numeric columns as INTEGER. If it worked for you I'm really confused then :/, I was beginning to think it could be a bug in excel.

Thx again

|||

Loslor,

I just looked into the details of mi excel destination file and compared how the data types from BIDS got mapped. I found that all my DT_I4, DT_I2, DT_WSTR and Numeric in BDIS have 'general' when I looked into the format of the cells in the Excel file. I also used the data in the excel file to build a pivot table and worked fine. Are you getting the same behavior?

CREATE TABLE `Excel Destination` (
`SalesOrderID` INTEGER, --> mapped from an DT_I4 in the dataflow; loaded in excel as 'General' (when opening the execel file and look a the format of the cell)
`SalesOrderDetailID` INTEGER,

`CarrierTrackingNumber` NVARCHAR(25), --> mapped from an DT_WSTR in the dataflow; loaded in excel as 'General' (when opening the execel file and look a the format of the cell)
`OrderQty` SMALLINT, --> mapped from an DT_I2 in the dataflow; loaded in excel as 'General' (when opening the execel file and look a the format of the cell)
`ProductID` INTEGER,
`SpecialOfferID` INTEGER,
`UnitPrice` MONEY, --> mapped from an DT_CY in the dataflow; loaded in excel as 'Currency' (when opening the execel file and look a the format of the cell)
`UnitPriceDiscount` MONEY,
`LineTotal` NUMERIC (38,6), --> mapped from a Numeric in the dataflow; loaded in excel as 'General' (when opening the execel file and look a the format of the cell)
`rowguid` UNIQUEIDENTIFIER,--> mapped from an DT_DBTIMESTAMP in the dataflow; loaded in excel as 'Date' (when opening the execel file and look a the format of the cell)
`ModifiedDate` DATETIME
)

Rafael Salas

|||

Hi again,

Yes, my excell is doing the same. After exporting the data, all the cells have the format "General", though they are still treated as text. In fact, the numeric columns display a warning saying that "The number in the cell is formatted as text or preceded by apostrophe" (the last thing obviously being not true) and asks me if i want to reformat the cells, wich sounds a bit like a joke to me. The pivot table is still not getting right the data.

Thx Rafael

|||

Then it seems there is an issue in how excel treats the format 'general' in your side. As I told you I was able to generate the Pivot table regardless of the 'general' formating.

Sorry if I didnt hel you

Rafael Salas

|||

I'm beginning to fight in that front since I don't see any problem in the package.

Thx a lot for your time Rafael

Problem exporting data using Excel destination (wrong format)

Hi there,

I have designed a package that works perfectly well, exporting data to an excel file from an ole db source. The problem is that in the excel destination file, columns of data that originally were numbers, are formatted as text. It would be just annoying if it weren't because I use those figures in a pivot table that operates with them.

Any idea on how to tell Excel that those columns are numbers?

Thx in advance

What is the original data type in the OLE DB source of those columns? If they are strings you may need tu use the Data Conversion transformation. Does the excel file already exists? if so, what are the format of the columns?

I run a quick test loading 100 rows od AdventureWorks.Sales.SalesOrderDetail table into an Excel file and all the data types mapped fine. The only thing is that I used the Excel Destination in BIDS to create the file and the destination tab using the 'Name of the excel sheet' -->New... button; it generated an statement like:

CREATE TABLE `Excel Destination` (
`SalesOrderID` INTEGER,
`SalesOrderDetailID` INTEGER,
`CarrierTrackingNumber` NVARCHAR(25),
`OrderQty` SMALLINT,
`ProductID` INTEGER,
`SpecialOfferID` INTEGER,
`UnitPrice` MONEY,
`UnitPriceDiscount` MONEY,
`LineTotal` NUMERIC (38,6),
`rowguid` UNIQUEIDENTIFIER,
`ModifiedDate` DATETIME
)

I only had to adjust the Numeric data type precision and I worked just fine.

Rafael Salas

|||

Thx for your answer Rafael.

The numeric data from the ole db data source is in four-byte signed int [DT_I4]. I have a data conversion between the ole db data source and the excel destination and I've tried to both leave the numeric fields unchanged and "convert" them into the same type. Regarding the excel file, I use a template pre-formatted that i copy into a new file through a system file task in the control flow. I've tried several formatting in the columns (general, numeric, etc...) to no avail...

I've also tried in the excel destination to create the worksheets in which I export the data through that "new button" and with a command very similar to the one you posted, with type for the numeric columns as INTEGER. If it worked for you I'm really confused then :/, I was beginning to think it could be a bug in excel.

Thx again

|||

Loslor,

I just looked into the details of mi excel destination file and compared how the data types from BIDS got mapped. I found that all my DT_I4, DT_I2, DT_WSTR and Numeric in BDIS have 'general' when I looked into the format of the cells in the Excel file. I also used the data in the excel file to build a pivot table and worked fine. Are you getting the same behavior?

CREATE TABLE `Excel Destination` (
`SalesOrderID` INTEGER, --> mapped from an DT_I4 in the dataflow; loaded in excel as 'General' (when opening the execel file and look a the format of the cell)
`SalesOrderDetailID` INTEGER,

`CarrierTrackingNumber` NVARCHAR(25), --> mapped from an DT_WSTR in the dataflow; loaded in excel as 'General' (when opening the execel file and look a the format of the cell)
`OrderQty` SMALLINT, --> mapped from an DT_I2 in the dataflow; loaded in excel as 'General' (when opening the execel file and look a the format of the cell)
`ProductID` INTEGER,
`SpecialOfferID` INTEGER,
`UnitPrice` MONEY, --> mapped from an DT_CY in the dataflow; loaded in excel as 'Currency' (when opening the execel file and look a the format of the cell)
`UnitPriceDiscount` MONEY,
`LineTotal` NUMERIC (38,6), --> mapped from a Numeric in the dataflow; loaded in excel as 'General' (when opening the execel file and look a the format of the cell)
`rowguid` UNIQUEIDENTIFIER,--> mapped from an DT_DBTIMESTAMP in the dataflow; loaded in excel as 'Date' (when opening the execel file and look a the format of the cell)
`ModifiedDate` DATETIME
)

Rafael Salas

|||

Hi again,

Yes, my excell is doing the same. After exporting the data, all the cells have the format "General", though they are still treated as text. In fact, the numeric columns display a warning saying that "The number in the cell is formatted as text or preceded by apostrophe" (the last thing obviously being not true) and asks me if i want to reformat the cells, wich sounds a bit like a joke to me. The pivot table is still not getting right the data.

Thx Rafael

|||

Then it seems there is an issue in how excel treats the format 'general' in your side. As I told you I was able to generate the Pivot table regardless of the 'general' formating.

Sorry if I didnt hel you

Rafael Salas

|||

I'm beginning to fight in that front since I don't see any problem in the package.

Thx a lot for your time Rafael

|||

I'm having the same problem using Excel 2003 in SSIS 2005 and wonder if you discovered the solution. I'd appreciate any tips you may have. Thanks.

Dan

Problem exporter PDF.

Hello everybody:

I have a simple report (table), and when I make export in PDF, he(it) takes out to me a badly formated file PDF (columns moved and empty pages).

It is what the problem?

Thank you in advance.

Go to the report properties window from the report menu. There set the page layout, width, height, margins. Unfortunately there's no default landscape/portrait option you control this using width/height. These setting affect both print and PDF output.|||

Thank you for your reponse:

but I have any attempt but ùca marche not,

you can look at the .PDF file

http://ftf-166.yousendit.com/download/1/ED443F25437C8D89/f2aa0f49f46c00359990203df421f4b6476a6e19/Situation%20de%20compte%20MBE.pdf

and say to me it needs to put what to post(show) 4 columns.

Thank's

|||

I see 2 problems with the PDF.

Firstly, the table is too wide to fit within the margins, so either reduce the table width of the table or increase the page margins. This can be found in the report properties page, layout tab.

Secondly, the long result set will span many pages so it would be usefull to have the table header repeated on every page. In the table properties screen on the general tab there is a checkbox for this.

Problem Executing Stored Procedures

We have an application that uses SQL-DMO to get a directory/file list for
the server by calling extended stored procedures, xp_availablemedia and
xp_cmdshell.
The app can successfully navigate the directories on systems running MSDE on
Window 2000 Professional, however, it fails to do so on systems running
Windows XP Professional.
There are no differences in the environment the application runs in other
than the version of Windows. Some of the characteristics of the
installation are: The application and MSDE are installed using the same
scripts on both versions of Windows. The systems where the app is installed
are running as part of a workgroup rather than a domain. We have run
svrnetcn.exe and verified that named pipes and TCP/IP are both enabled. To
confirm SQLDMO is enabled SQLDMO.DLL has been registered successfully from
the command line. Also, we can successfully execute the stored procedures
using SQL statements so it appears that the problems are related to SQLDMO.
Is there something additional or different that needs to be done on XP than
on 2000? Does anyone have any suggestions about how to solve this problem?
Thanks,
Tom
hi Tom,
"news.microsoft.com" <tom@.nospam.waspbarcode.com> ha scritto nel messaggio
news:erdTNo4EEHA.2740@.TK2MSFTNGP11.phx.gbl...
> We have an application that uses SQL-DMO to get a directory/file list for
> the server by calling extended stored procedures, xp_availablemedia and
> xp_cmdshell.
> The app can successfully navigate the directories on systems running MSDE
on
> Window 2000 Professional, however, it fails to do so on systems running
> Windows XP Professional.
> There are no differences in the environment the application runs in other
> than the version of Windows. Some of the characteristics of the
> installation are: The application and MSDE are installed using the same
> scripts on both versions of Windows. The systems where the app is
installed
> are running as part of a workgroup rather than a domain. We have run
> svrnetcn.exe and verified that named pipes and TCP/IP are both enabled.
To
> confirm SQLDMO is enabled SQLDMO.DLL has been registered successfully from
> the command line. Also, we can successfully execute the stored procedures
> using SQL statements so it appears that the problems are related to
SQLDMO.
> Is there something additional or different that needs to be done on XP
than
> on 2000? Does anyone have any suggestions about how to solve this
problem?
I do currently use SQL-DMO with success both on Win2k, WinXP pro and Win2003
server std...
I never had the need to manually register this somponent when installing
MSDE, and both procedures you are mentioning are run with success...
just the usual caveat... does the account running SQL Server and SQL Server
Agent have the right privileges?
did you try some simple SQL-DMO code to test it?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||My guess is that it has nothing to do with SQL-DMO, but is instead a
permissions issue. xp_cmdshell has some rather strict permission issues when
being invoked (its all in the BOL). By default, XP has a tighter security
setup than Win2k, so this may be part of the root of your problem. The fact
that you are running in a Workgroup network may also be a factor, since its
security model is much different than a domain network. Have you tried
running the profiler against the system when running your application
against MSDE on XP?
Jim
"news.microsoft.com" <tom@.nospam.waspbarcode.com> wrote in message
news:erdTNo4EEHA.2740@.TK2MSFTNGP11.phx.gbl...
> We have an application that uses SQL-DMO to get a directory/file list for
> the server by calling extended stored procedures, xp_availablemedia and
> xp_cmdshell.
> The app can successfully navigate the directories on systems running MSDE
on
> Window 2000 Professional, however, it fails to do so on systems running
> Windows XP Professional.
> There are no differences in the environment the application runs in other
> than the version of Windows. Some of the characteristics of the
> installation are: The application and MSDE are installed using the same
> scripts on both versions of Windows. The systems where the app is
installed
> are running as part of a workgroup rather than a domain. We have run
> svrnetcn.exe and verified that named pipes and TCP/IP are both enabled.
To
> confirm SQLDMO is enabled SQLDMO.DLL has been registered successfully from
> the command line. Also, we can successfully execute the stored procedures
> using SQL statements so it appears that the problems are related to
SQLDMO.
> Is there something additional or different that needs to be done on XP
than
> on 2000? Does anyone have any suggestions about how to solve this
problem?
> Thanks,
> Tom
>
|||Andrea,
Both SQL Server and Agent are running under the Local System account so they
should have enough privileges.
The same application that is having problems with SQL-DMO also uses SQL-DMO
to do backups and restores and doesn't have any problems.
Tom
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:c43nsp$2cuegu$1@.ID-207518.news.uni-berlin.de...
> hi Tom,
> "news.microsoft.com" <tom@.nospam.waspbarcode.com> ha scritto nel messaggio
> news:erdTNo4EEHA.2740@.TK2MSFTNGP11.phx.gbl...
for
MSDE
> on
other
> installed
> To
from
procedures
> SQLDMO.
> than
> problem?
> I do currently use SQL-DMO with success both on Win2k, WinXP pro and
Win2003
> server std...
> I never had the need to manually register this somponent when installing
> MSDE, and both procedures you are mentioning are run with success...
> just the usual caveat... does the account running SQL Server and SQL
Server
> Agent have the right privileges?
> did you try some simple SQL-DMO code to test it?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Jim,
Unfortunately, we have no control over the environment where the application
is being run, but, it is usually installed on stand-alone systems or
pier-to-pier networks that are not part of a domain.
Since we can execute the extended stored procedures in SQL commands, we are
in the process of converting the application so it doesn't use SQL-DMO
except for the backup and restore.
Tom
"J Young" <thorium48@.hotmail.com> wrote in message
news:OTbnG4OFEHA.2052@.TK2MSFTNGP11.phx.gbl...
> My guess is that it has nothing to do with SQL-DMO, but is instead a
> permissions issue. xp_cmdshell has some rather strict permission issues
when
> being invoked (its all in the BOL). By default, XP has a tighter security
> setup than Win2k, so this may be part of the root of your problem. The
fact
> that you are running in a Workgroup network may also be a factor, since
its
> security model is much different than a domain network. Have you tried
> running the profiler against the system when running your application
> against MSDE on XP?
> Jim
> "news.microsoft.com" <tom@.nospam.waspbarcode.com> wrote in message
> news:erdTNo4EEHA.2740@.TK2MSFTNGP11.phx.gbl...
for
MSDE
> on
other
> installed
> To
from
procedures
> SQLDMO.
> than
> problem?
>

Problem emailing report

Hi All,
Below is an extract from my RSReportServer.Config file.
Can anyone see what the problem is with my configuration.
I get the following error in my subscription
"Failure sending mail: The server rejected the sender address. The server
response was: 550 Sender address is missing a domain "
Type="Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider,ReportingServicesEmailDeliveryProvider">
<MaxRetries>3</MaxRetries>
<SecondsBeforeRetry>900</SecondsBeforeRetry>
<Configuration>
<RSEmailDPConfiguration>
<SMTPServer>smtp.registeredsite.com</SMTPServer>
<SMTPServerPort></SMTPServerPort>
<SMTPAccountName></SMTPAccountName>
<SMTPConnectionTimeout></SMTPConnectionTimeout>
<SMTPServerPickupDirectory></SMTPServerPickupDirectory>
<SMTPUseSSL></SMTPUseSSL>
<SendUsing></SendUsing>
<SMTPAuthenticate></SMTPAuthenticate>
<From>pop.registeredsite.com</From>
<EmbeddedRenderFormats>
<RenderingExtension>MHTML</RenderingExtension>
</EmbeddedRenderFormats>
<PrivilegedUserRenderFormats></PrivilegedUserRenderFormats>
<ExcludedRenderFormats>
<RenderingExtension>HTMLOWC</RenderingExtension>
<RenderingExtension>NULL</RenderingExtension>
</ExcludedRenderFormats>
<SendEmailToUserAlias>True</SendEmailToUserAlias>
<DefaultHostName></DefaultHostName>
<PermittedHosts></PermittedHosts>
</RSEmailDPConfiguration>
</Configuration>
</Extension>
<Extension Name="NULL"
Thanks
SanjeevThe from tag should include an email address. I used ReportingServices,
so for you the line would be:
<From>ReportingServices@.registeredsite.com</From>
This doesn't have to be a valid address.|||Hi Pete,
I have done as you suggested. Unfortunately, I just a get a different error
message now.
"The e-mail address of one or more recipients is not valid."
Did you get this as well before sorting the problem out?
Thanks
Sanjeev
"Pete" <prahalski@.hotmail.com> wrote in message
news:1131569084.930928.236010@.g43g2000cwa.googlegroups.com...
> The from tag should include an email address. I used ReportingServices,
> so for you the line would be:
> <From>ReportingServices@.registeredsite.com</From>
> This doesn't have to be a valid address.
>

Problem displaying image data from SQL Server

Hello,

I'm having problems saving and then displaying binary data in sql server.

I have a form that takes a file specified by the user and inserts this into sql server:

protected void btnUploadFile_Click(object sender, EventArgs e)
{
if (theFile.PostedFile != null)
{
if (theFile.PostedFile.ContentLength > 0)
{
byte[] docBuffer = new byte[theFile.PostedFile.ContentLength];
Response.Write(theFile.PostedFile.ContentType.ToString());

if (docBuffer.Length > 0)
{
// save to db
DbAccess dbAccess = new DbAccess(); // my helper function for all db access etc

try
{
dbAccess.BuildCommand("Incentives_SaveDocument");
dbAccess.Parameters.Add("@.docImage", SqlDbType.Image).Value = docBuffer;
dbAccess.ExecuteNonQuery();
}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
}
}
}
}

Stored proc:

ALTER PROCEDURE Incentives_SaveDocument
@.docImage image
AS
SET NOCOUNT ON

INSERT INTO Table1 (theData) VALUES (@.docImage)

RETURN

This appears to work fine. I store the binary data in a image column and if I query the db it shows the row as <Binary>.

The problem I have is with retrieving the data and saving it to a file. The file saves OK but when I open it is contains lots of "squares" that I suppose are the binary - it doesn't show the text.

The code for retrieving/displaying the doc is:

protected void btnView_Click(object sender, EventArgs e)
{
DbAccess dbAccess = new DbAccess();
byte[] byteArray = null;

try
{
dbAccess.BuildCommand("Incentives_RetrieveDocument");
dbAccess.Parameters.Add("@.id", SqlDbType.Int).Value = 6; // id for the doc to return
SqlDataReader reader1 = dbAccess.ReturnDataReader();

while (reader1.Read())
{
if (reader1.HasRows)
{
byteArray = (byte[])reader1["theData"];
}
}
reader1.Close();

FileStream fs = new FileStream("file1", FileMode.CreateNew, FileAccess.Write);
fs.Write(byteArray, 0, byteArray.Length);
fs.Flush();
fs.Close();

FileInfo fileInfo = new FileInfo("file1");

HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileInfo.Name);
HttpContext.Current.Response.AddHeader("Content-Length", fileInfo.Length.ToString());
HttpContext.Current.Response.ContentType = "application/msword";
HttpContext.Current.Response.WriteFile(fileInfo.FullName);
HttpContext.Current.Response.End();

}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
finally
{
dbAccess.CloseDbConnection();
}

The stored proc:

ALTER PROCEDURE Incentives_RetrieveDocument
@.id int
AS
SET NOCOUNT ON

SELECT * FROM Table1 WHERE id = @.id

RETURN

I would be grateful for any advice on this - its the first time I've worked with BLOB data.

Thanks

SiWhen you recreate a file from the image stored on the database, does it have the same extension?|||

Hello,

I've worked out what is was. I was uploading a byte array without the binary data in it. I needed the following to get it to work:

Stream dataStream = theFile.PostedFile.InputStream;
byte[] docBuffer = new byte[theFile.PostedFile.ContentLength];
int n = dataStream.Read(docBuffer, 0, theFile.PostedFile.ContentLength);

Si

problem deleting file

Hi all,
I need to delete files using UNC path of the file in xp_cmdshell
procedure.
The @.FileUNCPath variable contains the UNC path of the file and it has
correct value.
The SQL server service and Sql server agent service are running under
CSQL5 windows account. CSQL5 has full access to the files I am trying to
delete. But when I try to delete the file using the following statements,
@.Result is always 1 and the file does not get deleted.
So please help. Thanks in advance.
SET @.ShellCommand = 'delete "'+@.FileUNCPath+'"'
EXEC @.Result = master..xp_cmdshell @.ShellCommandYour OS use "delete" or "del", check the correct command to be used.
AMB
"sql" wrote:

> Hi all,
> I need to delete files using UNC path of the file in xp_cmdshell
> procedure.
> The @.FileUNCPath variable contains the UNC path of the file and it has
> correct value.
> The SQL server service and Sql server agent service are running under
> CSQL5 windows account. CSQL5 has full access to the files I am trying to
> delete. But when I try to delete the file using the following statements,
> @.Result is always 1 and the file does not get deleted.
> So please help. Thanks in advance.
> SET @.ShellCommand = 'delete "'+@.FileUNCPath+'"'
> EXEC @.Result = master..xp_cmdshell @.ShellCommand
>
>|||Try these examples to see if you get any more info on what the command is
actually doing:
CREATE TABLE #Errors (Results VARCHAR(1000))
INSERT INTO #Errors (FName)
exec @.Return = master..xp_cmdshell @.Cmd
DECLARE @.cmd sysname, @.var sysname
SET @.var = 'Hello world'
SET @.cmd = 'echo ' + @.var + ' > var_out.txt'
EXEC master..xp_cmdshell @.cmd
Andrew J. Kelly SQL MVP
"sql" <donotspam@.nospaml.com> wrote in message
news:eKsGUWsOFHA.3072@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> I need to delete files using UNC path of the file in xp_cmdshell
> procedure.
> The @.FileUNCPath variable contains the UNC path of the file and it has
> correct value.
> The SQL server service and Sql server agent service are running under
> CSQL5 windows account. CSQL5 has full access to the files I am trying to
> delete. But when I try to delete the file using the following statements,
> @.Result is always 1 and the file does not get deleted.
> So please help. Thanks in advance.
> SET @.ShellCommand = 'delete "'+@.FileUNCPath+'"'
> EXEC @.Result = master..xp_cmdshell @.ShellCommand
>

Problem Creating Login ID

I restored a database called 'MyDatabase' from a backup file. It created a user for me with the same name but no login with the same name. When I tried to create a login id with the same name (MyDataBase), it does not let me the same login id have privile
ges to access that database. I tried to do this under the 'Database Access' tab. Please help.
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
Hi,
Execute "sp_change_users_login" procedure from the restored database. This
problem is because
the user inside the database will not have the appropriate Login master
database.
This link can be brought up by using the system procedure
sp_change_users_login .
See the details of sp_change_users_login procedure in books online.
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:OdY4$OXXEHA.3596@.tk2msftngp13.phx.gbl...
> I restored a database called 'MyDatabase' from a backup file. It created a
user for me with the same name but no login with the same name. When I tried
to create a login id with the same name (MyDataBase), it does not let me the
same login id have privileges to access that database. I tried to do this
under the 'Database Access' tab. Please help.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.
|||Hi,
Execute "sp_change_users_login" procedure from the restored database. This
problem is because
the user inside the database will not have the appropriate Login master
database.
This link can be brought up by using the system procedure
sp_change_users_login .
See the details of sp_change_users_login procedure in books online.
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:OdY4$OXXEHA.3596@.tk2msftngp13.phx.gbl...
> I restored a database called 'MyDatabase' from a backup file. It created a
user for me with the same name but no login with the same name. When I tried
to create a login id with the same name (MyDataBase), it does not let me the
same login id have privileges to access that database. I tried to do this
under the 'Database Access' tab. Please help.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

Problem Creating Login ID

I restored a database called 'MyDatabase' from a backup file. It created a u
ser for me with the same name but no login with the same name. When I tried
to create a login id with the same name (MyDataBase), it does not let me the
same login id have privile
ges to access that database. I tried to do this under the 'Database Access'
tab. Please help.
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.Hi,
Execute "sp_change_users_login" procedure from the restored database. This
problem is because
the user inside the database will not have the appropriate Login master
database.
This link can be brought up by using the system procedure
sp_change_users_login .
See the details of sp_change_users_login procedure in books online.
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:OdY4$OXXEHA.3596@.tk2msftngp13.phx.gbl...
> I restored a database called 'MyDatabase' from a backup file. It created a
user for me with the same name but no login with the same name. When I tried
to create a login id with the same name (MyDataBase), it does not let me the
same login id have privileges to access that database. I tried to do this
under the 'Database Access' tab. Please help.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.

problem creating DTS package

Hi,
I am trying to create a DTS package wherein the data from the sql
server is transfered to a text file. I am using the Transform Data Task
to transfer the data.In Transform Data task properties, I build the
query and able to preview the data but when i click OK in the Transform
Data task properties screen I get the following error.
"To continue , define transformations. You can make changes to the
default transformations"
I am not able to define the transformations. Has anyone had this
problem and know a way to resolve it. I would appreciate if anyone
could help me.
Thanks
MBare you working on the server or throught the EMC remotely?|||are you working on the server or through the EMC remotely?|||Mohan wrote:
> Hi,
> I am trying to create a DTS package wherein the data from the sql
> server is transfered to a text file. I am using the Transform Data Task
> to transfer the data.In Transform Data task properties, I build the
> query and able to preview the data but when i click OK in the Transform
> Data task properties screen I get the following error.
> "To continue , define transformations. You can make changes to the
> default transformations"
> I am not able to define the transformations. Has anyone had this
> problem and know a way to resolve it. I would appreciate if anyone
> could help me.
> Thanks
> MB
>
You need to define the transformations first before you can click OK.
The text file must be defined with column names and then the
transformations can be assigned.|||I am actually using the remote connection to work directly on the
server.
i was able to get it to work if i selected some of the columns, but not
all. Is there a limit on the number of columns?
Let me explain in more detail.
When i am setting up the transform data task, I build the query and
then go to the destination tab. I click the Execute button to define
the columns for the destination.
The problem is that when i select all the columns from the table then
nothing happens when i click Execute. There are no columns defined and
when i click on the Define Columns button it gives an error and
terminates Enterprise Manager.
I am not sure whats going on. Really appreciate your help
Thanks
MB|||Mohan wrote:
> I am actually using the remote connection to work directly on the
> server.
> i was able to get it to work if i selected some of the columns, but not
> all. Is there a limit on the number of columns?
> Let me explain in more detail.
> When i am setting up the transform data task, I build the query and
> then go to the destination tab. I click the Execute button to define
> the columns for the destination.
> The problem is that when i select all the columns from the table then
> nothing happens when i click Execute. There are no columns defined and
> when i click on the Define Columns button it gives an error and
> terminates Enterprise Manager.
> I am not sure whats going on. Really appreciate your help
> Thanks
> MB
>
What service pack is SQL Server running on? That may be part of the
issue if you have a large amount of columns to export.|||Its running on SP3.|||Mohan wrote:
> *Its running on SP3. *
I figured out a fix without having to upgrade to SP4. If you don't
upgrade your client tools to SP3 on your computer you don't have the
errors. I had to uninstall my client tools and re-install the client
tools on my machine (not on the server). There were no issues. Its
not a pretty fix, but I can always use any new functionality on the
server itself. Good luck.
dmasuda
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message2000028.html|||This thread is confusing... ..
What was the error you got was it "Invalid Class String" Error?
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"dmasuda" wrote:

> Mohan wrote:
> I figured out a fix without having to upgrade to SP4. If you don't
> upgrade your client tools to SP3 on your computer you don't have the
> errors. I had to uninstall my client tools and re-install the client
> tools on my machine (not on the server). There were no issues. Its
> not a pretty fix, but I can always use any new functionality on the
> server itself. Good luck.
>
> --
> dmasuda
> ---
> Posted via http://www.mcse.ms
> ---
> View this thread: http://www.mcse.ms/message2000028.html
>

problem creating DTS package

Hi,
I am trying to create a DTS package wherein the data from the sql
server is transfered to a text file. I am using the Transform Data Task
to transfer the data.In Transform Data task properties, I build the
query and able to preview the data but when i click OK in the Transform
Data task properties screen I get the following error.
"To continue , define transformations. You can make changes to the
default transformations"
I am not able to define the transformations. Has anyone had this
problem and know a way to resolve it. I would appreciate if anyone
could help me.
Thanks
MB
are you working on the server or throught the EMC remotely?
|||are you working on the server or through the EMC remotely?
|||Mohan wrote:
> Hi,
> I am trying to create a DTS package wherein the data from the sql
> server is transfered to a text file. I am using the Transform Data Task
> to transfer the data.In Transform Data task properties, I build the
> query and able to preview the data but when i click OK in the Transform
> Data task properties screen I get the following error.
> "To continue , define transformations. You can make changes to the
> default transformations"
> I am not able to define the transformations. Has anyone had this
> problem and know a way to resolve it. I would appreciate if anyone
> could help me.
> Thanks
> MB
>
You need to define the transformations first before you can click OK.
The text file must be defined with column names and then the
transformations can be assigned.
|||I am actually using the remote connection to work directly on the
server.
i was able to get it to work if i selected some of the columns, but not
all. Is there a limit on the number of columns?
Let me explain in more detail.
When i am setting up the transform data task, I build the query and
then go to the destination tab. I click the Execute button to define
the columns for the destination.
The problem is that when i select all the columns from the table then
nothing happens when i click Execute. There are no columns defined and
when i click on the Define Columns button it gives an error and
terminates Enterprise Manager.
I am not sure whats going on. Really appreciate your help
Thanks
MB
|||Mohan wrote:
> I am actually using the remote connection to work directly on the
> server.
> i was able to get it to work if i selected some of the columns, but not
> all. Is there a limit on the number of columns?
> Let me explain in more detail.
> When i am setting up the transform data task, I build the query and
> then go to the destination tab. I click the Execute button to define
> the columns for the destination.
> The problem is that when i select all the columns from the table then
> nothing happens when i click Execute. There are no columns defined and
> when i click on the Define Columns button it gives an error and
> terminates Enterprise Manager.
> I am not sure whats going on. Really appreciate your help
> Thanks
> MB
>
What service pack is SQL Server running on? That may be part of the
issue if you have a large amount of columns to export.
|||Its running on SP3.
|||

Quote:

Originally posted by Mohan
Its running on SP3.

I figured out a fix without having to upgrade to SP4. If you don't upgrade your client tools to SP3 on your computer you don't have the errors. I had to uninstall my client tools and re-install the client tools on my machine (not on the server). There were no issues. Its not a pretty fix, but I can always use any new functionality on the server itself. Good luck.|||This thread is confusing... ..
What was the error you got was it "Invalid Class String" Error?
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"dmasuda" wrote:

> Mohan wrote:
> I figured out a fix without having to upgrade to SP4. If you don't
> upgrade your client tools to SP3 on your computer you don't have the
> errors. I had to uninstall my client tools and re-install the client
> tools on my machine (not on the server). There were no issues. Its
> not a pretty fix, but I can always use any new functionality on the
> server itself. Good luck.
>
> --
> dmasuda
> Posted via http://www.mcse.ms
> View this thread: http://www.mcse.ms/message2000028.html
>