Anyone know the origin of sql 2005's datatype naming convention or even why SSIS has defined its own special data types? (Before SSIS I had never seem types like DT_UI1 except in programming languages.)
TIA,
barker
I'm not a DBA but I'm doing what is turning into way too much work... Yes I understand you want to do memory intensive data conversions and SQL server is not tolerant, etc....
But, I have to get my data from a flat source into a table in SQL server. I know DT_I1 is a 1 byte signed integer, I see System.Byte, but surprise, NONE of the TYPES in defining my table in SQL Server Management Studio correspond to any type that it accepts.
What am I doing wrong?
I have a table in SQL Server. I know DT_I8 is Bigint, I know DT_I4 is Int. Bigint and Int, for example, are two types I have access to when I'm building my table. But what on EARTH is DT_I1? It is NOT a TYPE that I can dropdown and choose.
My SSIS package has a Flat File Source that points to my comma delimeted file on my PC, it has a Data Conversion element that seems to do nothing..., and of course a SQL Server Destination which is my table.
I would rather just have the SSIS package create the right table on the fly.
DTS was much easier, I know, not as efficient for 1,000,000 rows, but guess what, there are still small to medium size businesses that don't need that efficiency to get in 1MM rows +, we just need to get our jobs done.
Help...
P.S. I'm creating the SSIS in MS Visual Studio Professional.
I created a simple ragged file with 3 lines:
1
2
3
and coupled it to a Flat File Source. The Flat File Source suggested that the type was DT-I1.
Next I created a €œSQL Server Destination task and connected the output of the Flat File to the Sql Destination. In the Destination editor I created a table, whose one column was declared to be a sql smallint.
Here€™s the problem: the Sql Server Destination now displays a red circle with a white €œx€?. The tooltip says that: €œThe column €œColumn 0€? can€™t be inserted because the conversion between types DT_I1 and DT_I2 is not supported€?
Can anyone explain why the conversion is not supported?
TIA,
Barkingdog
P.S. It's not kidding! When I changed the Flat File Source type to DT-I2 everything worked fine.
Okay, this is probably my own stupidity but here goes. I am trying to make a very rudimentary package. I've got a data flow that has a flat file source. This source has fields delimited by the | symbol. I give the fields in the flat file source names. The first field is always exactly 1 character. When I use the advanced editor to look at the fields' properties, I see the first field, called ProductLine, is considered to be of SSIS internal data type DT_STR with a length of 50. I change the 50 to 1 because that is as long as that field is.
No matter what I do, when I close the advanced editor and then open it again, the field length for ProductLine is again set to 50. The field is 1 long, not 50 long. How can I make the setting of 1 be retained? Nothing I do changes it. What is the point of allowing the length to be editable if the edit is not retained?
Thanks in advance,
SW
I have a OLE DB Source going to a flat file destination. My source is a sql variable with "select * from tablename" which have varchar datatypes. Yet I'm getting trailing spaces at the end of some of my columns (for instance, my address column).
I've checked the data by doing a "select Max(Len(address)) from tablename" and the max is only like 34 chars, yet each of them have 100 chars total.
Taking a look at my flat file connection, the outputColumnWidth is 100, and datatype is string [DT_STR]. Am I crazy? What's the problem here? Is the DT_STR datatype the equivalent of char, and not varchar?
Any help, of course, will be appreciated.
Can anyone tell me how to convert a string variable to boolean
Ihave a variable named VarLoc which is string datatype
In expression I want to give condition like
VarLoc == "1234"?False:True
its giving error because of converting string datatype to boolean.
Please help.
Thank You
Our shop's DTS packages usually use ActiveX scripts to dynamically name vendor text file inputs by date (YYYYMMDD), which we load Monday through Friday to our databases. For our 2005 server I am trying to dynamically name a 'flat file source' filename in SSIS as described in the article at the url http://www.mssqltips.com/tip.asp?tip=1084.
I am writing the expression (DT_STR, 4,1252) YEAR(DATEADD( "dd",0, getdate() ) to create a variable named FileName to capture today's 4 character year. If I could get a good preview of this value I would concatenate the month and day strings to it and use the resultant variable as part of the fully qualifeid name.
For the variable I've marked the 'evaluateasexpression' true and put the value type as str. But when I preview this expression I get the error message, 'expression has the result type of DT_STR which can not be converted to a supported type.'
What supported type? Does anyone see what the problem is?
Hi,
Having problem with OLE-DB connection to informix.
Created a flat file has two columns and 2 row:
1|a
2|b
tried to load it to a informix table:
create table t
( c1 int,
c2 char
);
2 rows inserted, but only column c1 got data.
Changed c2 from char to varchar then ok.
thanks for any help.
gg
Hi, I am trying to derive a column from:
mm/dd/yyyy hh:mms.fff to dt_dbtimestamp as:
(dt_dbtimestamp)(colum_name,1, 23) when I include the period and three digits for milliseconds the package fails if I leave it out it is successfull. I need to include milliseconds for my datawarehouse project. I tried many different ways and always with failure
01/23/2007 12:23:15.234 is the date(example) derived: (dt_dbtimestamp)(column_name,1,23) fails
01/23/2007 12:23:15.234 is the date(example) derived: (dt_dbtimestamp)(column_name,1,19) succeeds
Thanks
The Lookup Transformation fails to match this datatype when full caching is enabled. When partial caching is activated (Edit > Advanced, Enable Memory Restrictions > Enable Caching) the lookup works.
This appears to be a bug to me.
Hi all I am trying to convert the string "(null)" in the [PASSWORD] column of my table to an actual NULL value. I have tried to use two different forms of a conditional operator to achieve this end. However I am getting the below errors both can be summed up with the following statement.
DT_STR operand cannot be used with the conditional operation. The expression directly below however is using a type DT_I4 in the conditional clause as this is what FINDSTRING returns. Hence the equivalencey test to the literal integer 0. So I must say I am somewhat confused by this. Does anyone know why neither of the below statements are working?
Also is there an easy way to accomplish what I am trying to do - convert the string "(null)" in the [PASSWORD] column of my table to an actual NULL value?
FINDSTRING([PASSWORD], "(null)", 1) == 0 ? [PASSWORD] : NULL(DT_STR, 255, 1252)
Error at Administrator Data Flow Task [Derived Column [1985]]: For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operations. The expression "FINDSTRING(PASSWORD,"(null)",1) == 0 ? PASSWORD : NULL(DT_STR,255,1252)" has a DT_STR operand that is not an input column or the result of a cast, and cannot be used with the conditional operation. To perform this operation, the operand needs to be explicitly cast with a cast operator.
LOWER( TRIM( [PASSWORD] ) ) != "(null)" ? [PASSWORD] : NULL(DT_STR, 255, 1252)
Error at Administrator Data Flow Task [Derived Column [1985]]: For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operations. The expression "LOWER(TRIM(PASSWORD)) != "(null)" ? PASSWORD : NULL(DT_STR,255,1252)" has a DT_STR operand that is not an input column or the result of a cast, and cannot be used with the conditional operation. To perform this operation, the operand needs to be explicitly cast with a cast operator.
Hi there
I sorry if I have placed this query in the wrong place.
I'm getting to grips with ASP.net 2, slowly but surely!
When i try to access my site which uses a Sql Server 2005 express DB i am receiving the following error:
Server Error in '/jarebu/site1' Application.
Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists.Could not attach file 'd:hostingmemberjarebusite1App_DataASPNETDB.MDF' as database 'ASPNETDB'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists.Could not attach file 'd:hostingmemberjarebusite1App_DataASPNETDB.MDF' as database 'ASPNETDB'.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists.
Could not attach file 'd:hostingmemberjarebusite1App_DataASPNETDB.MDF' as database 'ASPNETDB'.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735075
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
System.Web.UI.Control.EnsureChildControls() +87
System.Web.UI.Control.PreRenderRecursiveInternal() +41
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
This is the connection string that I am using:
<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;Initial Catalog=ASPNETDB;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
The database is definitly in the folder that the error message relates to.
What I'm finding confusing is that the connection string seems to be finding "aranga"s database.
Is it something daft?
Many thanks.
James
Hopefully this is the right place to post this, I am trying to learn ADO.Net programming using VWD Express with SQL Express... I tried to do the walk-through tutorial on ADO.Net through the asp.net tutorial. The walk-through asked you to download and install a script (check) then it said to open a command window with sql server running (check) and then to copy all of this special script code into to bind the database to the web page (not check). Everything pretty much went down hill after that part. I couldn't get the Update/Cancel/Delete buttons. After that dissapointing attempt at learning I decided to just try and make my own database. All was going pretty well until I try to add the UPDATE/DELETE fields after adding the WHERE clause. No go.
So, I don't know if I'm just totally flubbing this up or if this is a common problem. Do I need the two or three pages of custom script like the Microsoft example to make a database work?
Please help!!!
Syntax Override
1 Hi, I'm a complete newbie at this, and need some help I want to import a text file into a DB. I know how to set it up in the DTS, however when I check the DB each line from the text file is in a seperate row. What have I missed? I want the file just in one row, like its a memo.
2 What do I use to connect to a SQL DB on the web so i can see the DB and all the tables
3 Also if I want to add data to a DB is that done on the server, or do I change my copy of the DB and re-upload
Thanks for any help
Meltdown
Is it possible to change from integrated security to standard security? Is it something easy or does it take a reinstall.
View 1 Replies View RelatedHi,
I've imported an access .mbd table into SQL 7.0, and need to know the best way to overwrite my SQL 7.0 table with the new Updated information that is contained in the access table. I've read a little bit about update tables, but is there an easy way to overwrite an entire table without having to specify each row and column?
Thanks in advance for any help.
Ok, not sure if this is where i should post this, but here goes.
I am trying to setup a database to handle RMA stuff at work. I need to be able to access the DB from numerous systems at once. Now i know NOTHING about SQL or any other DB for the most part. Messed around with Access some, but nothing major.
Is there anyone that could help me, explain how, or at least point me in the right direction?
I have MS SQL server 2000. Any help, ideas, or suggestions would be great!!
Thanks in advance :)
Hey! I',m totally new to Ms SQL Server. I'm currently trying to import a database from LINUX using SQL+ (Oracle) to MS SQL Server. Any suggestions on how this could be achieved would be most helpful
View 1 Replies View RelatedI need to know how to write a sql statement for VB to Microsoft Access for the following criteria.
TblEmployees only Field to be concerned with is EmployeeID -Its PK
TblEmpAttendane has these fields:
EntryID - Pk
EmployeeID - Fk
Date- A text form date validated through vb to avoid hassle
Value-A single digit value that can be alpha OR numeric
To get the Date range for the current month I use variables to store the First and last day of the month(in a CUSTOM calendar control).
I have been trying to use this Sql statement(that wont work):
SqlString = "SELECT * FROM TblEmpAttendance WHERE Date BETWEEN '" & FirstDay & "' AND '" & LastDay & "'"
I need to:
1.)using the current EmployeeID (from TblEmployees)
2.) find the same EmployeeID in TblEmpAttendance
3.)Get the date range for the current month and year(valid days currnt mo.)
-these date are supplied by the variables "FirstDay" AND "LastDay" as seen in above SqlString
4.) Find the Values associated with the dates and EmployeeID
I have a table with 10 records: 10, 9, 8, 7 ... and so on. I want to SELECT the top 3 records starting with the second record on the list (which in this case is 9.) So the result should be 9, 8, and 7. What would be the correct SQL statement for this?
I tried "SELECT TOP 3 * FROM tableXXX WHERE (FieldID<>(SELECT TOP 1 * FROM tableXXX ORDER BY FieldID DESC) ORDER BY FieldID DESC" but sure as heck it didn't work. Can anyone help?
Hi,
just for understanding a simple iif statement:
I like to set a numeric value to 0, if it is smaller than 0.
This expression shall be valid over all dimensions for a specific measure
like Iif([Measures].[Provision Ankauf]<0,0,[Measures].[Provision Ankauf] )
it's not working, Pivottable returns ##VALUE everywhere
Hi
I have two problems. I am currently using SQL Server express and SQL Server Management Studio Express locally. i have a DotNetNuke website running on the web with IHostASP.Net
Story:
I noticed that my SQL Server 2005 database with IHostASP.net was 350MB. It was only 22 2 months ago and I have had about 100 users sign up. I then connected to my live database via SQL Server Management Studio Express and shrunk the database. No errors and the database was reduced to 24MB - Great I thought!
I went and checked the website and all was great for about an hour then the site went down with the following error:
Error The transaction log for database 'classcampers' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
Anyone know what to do.
Anyway, I have downloaded a .bak of my database as supplied by my host. However I cant figure out to restore this file back to my live database. What are the steps involved?
Do I create a new local database from the .bak and then somhow restore this back to my live one, or do i apply this .bak file strainght to my live database using SQLMSE. Any tutorials becuase I have been trying to apply this restore all day
Thansk for any advice in advance
cheers
Albertramsbottom:eek:
Hi,
which is current running mostly used version of sql server and what was the previous version?
how can we track down a query bad performance in sql server(like explain plan,stats..etc..in oracle)
Thank you.
Hi guys,
Ive been learning SQL slowly and I need help for a projet I am doing.
I currently would like to compare identical files in a 2 seperate folder. Not only would I like to compare if the files are the same name, but when they are I would like to compare the file contents.
SO basically this is what I need to do:
-Compare file names in folder A with files in folder B
-If a match is found, compare file contents
-If file contents are same, do nothing. If file contents are different, move file to folder C.
Can anyone help me!! :O
Thanks guys!
Gino
I finally decided to do our ASP application using VB, .NET and MSSQL.
My questions are simple, but I cannot find all the answers, so anything you can offer will be greatly appreciated.
Please explain how licenses (CALS) work with regard to Windows Server and MSSQL. For example, suppose we have 200 clients all logged into our application, but only say 40 are currently active (meaning they are adding data, running reports, etc....), but they are not all clicking the "DO" button at the same time. So how many Windows server and/or MSSQL seats, licenses, CALS, cpu's etc.... (whatever they are called) will I need?
How many users do you think a "well designed" ASP application built using AJAX, ActiveX, VB, .NET and MSSQL can a single cpu reasonably support? This application will serve east coast to west coast with heavy traffic from say 7am to 9am and 2pm to 4pm in each time zone. At what point (or what should I look for) to say, you need more memory, more processors, more servers, etc......????
How does one protect (or build in) a method to deal with index corruptions, etc???? Does that occur in MSSQL? If so, generally, what are causes and fixes?
The application will serve many chapters. Many chapters will be solo (by themselves), but we also expect district wide, where multiple chapters (25 to 75) in one district will use the application. We can expect, during time frames mentioned above, that each chapter will have 200 to 400 transactions per am time and same per pm time.
has anyone had great success with IronSpeed?
Lastly, can anyone suggest some good windows web hosting companies?
Thank you.
Hi i have 2 columns in the same table.....one column retrieves all registered students, the other column retrieved registered students but who have not completed a required survey.
The 2 sql statements are almost the same except that the 2nd one does another comparison at another table....(i.e. where student_id not in (select student_id from other_t).
I am trying to combine the 2 sql statements into a derived table where results will be displayed as follows:
registered students | students(incomplete surveys)
studA, studB, studC | studB
studC, studD, studE | studD, studE
is there any way to do it?
The questions in this forum are way beyond me!!!
Need a lower level forum.
This is on my own computer for the purpose of testing web pages.
I have Visual Studio installed. I can start it. Then it tells be to boot to it. I see no options to boot to it during computer start up.
I have no idea where to go to run ASP pages.
All help is appreciated.
Thanks
Hello,
I have a database and in my database there are 5 different tables....
One of the tables is called "dvds" and another one is calles "reports"
What I want to do is, I would like to copy and paste everything in "reports" (column name: dvd) to "dvds" table (column name: name).
And I wan the SQL parameter to check everytime before it writes to "name" column in case that entry is already there. If the entry is already there then ignore and do not write, if it is not there then write it.
I am very sorry about my english and my knowledge on SQL.
Can anyone please help me to product this SQL command?
thank you so much.
Aareth
hi i am new to mysql...and please can someone help me to tell a command how to delete a table frm database??
(spam removed)
Hi,
View 3 Replies View Related
Hi I am new to SQL Server,
I have questions regarding theses files TRN, LDF, MDF and BAK.
I have a maintenance plan that backups the .TRN and .BAK everynight in a folder e:/mssqlsrv/MSSQL/Bakup.
When I look at the date modified in windows explorer they are exactly like I programmed it in the maintenance plan.
Now when I look in the folder e:/mssqlsrv/MSSQL/Data I see the .LDF and .MDF files and they are way out of date.
How do they get copied there? With the maintenance plan? What is the difference between the TRN and the LDF?
How does a restore work? I am so confused? Please explain to me?
I just went through Steve Lasker's slide show on Everywhere (which was very informative) and noticed that when he setup the connection to the Everywhere database it had a .sdf extension. However, when I look at my database using SQL Server Management Studio Express it shows me as having the standard .mdf and .ldf files and *no* sdf file. I thought for a moment that I might be using Express but when I look at the connection properties of my (or what I think is my Everywhere) database I see:
Product Name: Microsoft SQL Server Desktop Engine
Product Version: 8.0.818 SP3
Instance Name: MICROSOFTBCM
If I look at an Express database, I see connection properties of:
Product Name: Microsoft SQL Server Express Edition with Advanced Services
Product Version: 9.0.2047 SP1
Instance Name: SQLEXPRESS
So it appears that I am not use Express but yet, no .sdf files. Can someone tell me about the different file extensions and which ones I might be using? I would assume with the product name of "Microsoft SQL Server Desktop Engine" I am using Everywhere - but maybe not. Please advise.
Thanks!
Hello All.
I'm really new to SQL Server 2K5 and SSIS. I have a task that seems very simple, but being that I'm very rusty with visual studio and SQL, I'm having all sorts of problems.
Here's the scenario:
I have a text file that has 2 columns. AD LOCATION and UPC, I get the text file by combining two spreadsheets in Excel. I need to examine the rows of the flat file and output that data to multiple files. It's a parent child relationship and the output would simply be the children of the parent in it's own text file. It looks something like this:
TEXT FILE 1.TXT
ADLOCATION UPC
101 XXXXXXXXX1
101 XXXXXXXXX2
101 XXXXXXXXX3
102 XXXXXXXXX1
102 XXXXXXXXX2
103 XXXXXXXXX1
The output files should result in:
TEXT FILE 101.TXT
XXXXXXXXX1
XXXXXXXXX2
XXXXXXXXX3
TEXT FILE 102.TXT
XXXXXXXXX1
XXXXXXXXX2
TEXT FILE 103.TXT
XXXXXXXXX1
Again, I'm brand new to SSIS, have minimal DTS experience and really need to get this automated. Any help at all would be very much appreciated.
Thanks!
Woody