Losing Integer Values
Jun 12, 2007
Following on from some problems I have been having with flat files, I seem to be stumbling from one issue to another.
I have coded my text file with some very contrived delimiters to ensure that my real data isn't tripping up the package. I am stripping all line break chars, tabs etc.
Now I seem to get all rows imported, but numerous integer columns within the file are being set to zero.
When I toggle the Keep Null options in the data flow task it will either import the row, but with the zeros, or not import the row at all. I have opened the file in text editors, and Excel and it all seems fine - in fact the same source file works OK with DTS/2000.
At a guess it seems as though these columns are seen as null by the package and so with the option switched on it defaults to zero - but they are most certainly not null in the file!! The table is a staging tabe and so is very basic in structure (no defaults or constraints)
SSIS seems a bit buggy or at the very least over sensitive to me - I am at the point of abandonment of it!!
Please has anyone seen similar issues?
View 6 Replies
ADVERTISEMENT
Jul 28, 2015
I have a string variable
string str1="1,2,3,4,5";
I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :
declare @id varchar(50)
set @id= '3,4,6,7'
set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype
select *from ehsservice where id in(@id)
But this query throws following error message:
Conversion failed when converting the varchar value '3,4,6,7' to data type int.
View 4 Replies
View Related
Aug 28, 2007
I have a simple flat file which I am trying to import which has 50k rows and about 50 columns. File is delimited (using a rather obscure multiple char delimiter) with text delimiting also.
When importing if I check the "Retain null values from the source" it will import the columns correctly but drop approx 7k of the rows.
If I uncheck this box it imports all rows, but on the second integer column in that target table all values are inserted as zeroes when there is at least 35k rows with a positive value.
I have put a data viewer on this import and it's also showing zeroes.
I'm tearing my hair out!!
View 3 Replies
View Related
Aug 3, 2006
I am new to SQL Server and am trying to figure out why it behaves the way it is regarding integers with null values.
I have a table that contains integer datatypes which can be null. If i insert the record with a blank field i receive an INCORRECT SYNTAX NEAR ',' error. If i surround the form value with '', it inserts a 0. Why does SQL Server behave like this? And whats the proper way to handle inserts such as this?
Thanks!
View 8 Replies
View Related
May 11, 2007
Just to be clear i'm using a cube here.
Okay if i understand correct if you want to sort your parameter list you have to write an MDX query.
Thats all good and well i've been able to sort my parameter list when its a string
WITH
MEMBER [Measures].[ParameterCaption] AS '[Time].[Week].CURRENTMEMBER.MEMBER_CAPTION'
MEMBER [Measures].[ParameterValue] AS '[Time].[Week].CURRENTMEMBER.UNIQUENAME'
MEMBER [Measures].[ParameterLevel] AS '[Time].[Week].CURRENTMEMBER.LEVEL.ORDINAL'
MEMBER [Measures].[DefaultValue] AS '[Time].[Week].CURRENTMEMBER.UNIQUENAME'
MEMBER [Measures].[DefaultBeginValue] AS ' ( "[Time].[Week].&[" + LEFT([Time].[Week].CURRENTMEMBER.MEMBER_CAPTION,4) + "-01]" ) '
SELECT {
[Measures].[ParameterCaption],
[Measures].[ParameterValue],
[Measures].[ParameterLevel],
[Measures].[DefaultValue],
[Measures].[DefaultBeginValue]
}
ON COLUMNS
, {
Filter ((ORDER({(Filter ([Time].[Week].MEMBERS,( [Time].[Week].CURRENTMEMBER.MEMBER_CAPTION ) <> 'Unknown'))},
([Time].[Week].CURRENTMEMBER.UNIQUENAME) , DESC)),
( [Time].[Week].CURRENTMEMBER.LEVEL.ORDINAL ) = 1)
}
ON ROWS FROM [Europe]
Now my problem is that this value of this string is actually an integer. The reason this is a data type string is because its a dimension and these are always string only measures are integer...
Can someone help me make this MDX query sort on integer value instead of string.
[Time].[Week].MEMBERS contains values like 8,11,20 but is declared are string because its a dimension please help me out because i'm getting the feeling this is impossible with this microsoft tool...
View 1 Replies
View Related
May 23, 2006
We have inherited an appointments database that has a table tblAppointments
Within this table there are 2 fields, ApptFrom & ApptTo, these are the appointment start & finish times.
My problem is, the values in these fields are held as integers ranging from 0 - 288. I have worked out the scale for the integers, it is based on a 24hr clock with each segment representing 5mins
Eg: 0 = 0:00
12 = 1.00
24 = 2.00
36 = 3.00
€¦
...
288 = 24:00
What I need is a piece of T_SQL that will translate these into times that can be read by a normal user
So, say I have a record with an ApptFrom = 36 & ApptTo = 42, I need to be able to show these as ApptFrom = 3.00 & ApptTo = 3.30
Any help is greatly appreciated.
Cheers,
Craig
View 6 Replies
View Related
Mar 13, 2007
I have imported a text file with various data into sql table. all these values have been imported as nvarchar. I need to convert these into Integer. the format of the values is 10length i.e. 0000000.00.
example of data:
0001028.99 - needs to be shown as 1028.99
222.00 - needs to be shown as 222.00
0000190.89 - needs to be shown as 190.89
2708.99 - needs to be shown as 2708.99
00000-50.99 - needs to be shown as -50.99
-109.79 - needs to be shown as -109.70
as you can see some of the values have leading zeros and some don't.
i have tried converting from nvarchar to int and i get the error cannot convert nvarchar to int, i believe it may be because the data contains negative values as well as positive values.
Is there a split function or position function which i can use to extract the data? or any other methods which i can use would be really helpful.
Thanks
View 4 Replies
View Related
Sep 22, 2014
I am facing problem with re numbering of field records in a table.I am having one table with below records.
sid(identity/primarykey) stickyId(Integer) UserId(integer)
102 0 171
103 1 171
104 2 171
105 3 171
here how to renumbering stickyId values when deleted particular stickyId from UI. Here stickyId field is Integer type only. not primarykey/identity field.
View 3 Replies
View Related
Jul 15, 2014
As a DBA, I am working on a project where an ETL process(SSIS) takes a long time to aggregate and process the raw data.
I figured out few things where the package selects the data from my biggest 200 GB unpartitioned table which has a datekey column but the package converts its each row to an integer value leading to massive scans and high CPU.
Example: the package passed two values 20140714 and 4 which means it wants to grab data from my biggest table which belongs between 20140714 04:00:00 and 20140714 05:00:00.
It leads to massive implicit conversions and I am trying to change this.
To minimize the number of changes, what I am trying to do is to convert 20140714 and 4 to a datetime format variable.
Select Convert(DATETIME, LEFT(20170714, 8)) which gives me a date value but I am stuck at appending time(HH:00:00) to it.
View 4 Replies
View Related
Dec 29, 2014
I'm looking for a script that identifies max values used in a table for all smallints, ints and bigints to determine if they are being used correctly.
View 3 Replies
View Related
Jun 3, 2015
I have SSAS cube with Fact that include values in kg (e.g. 25.3, 32.5, 18,3...).What kind of attribute or other solution should I create If I want to filter those kg's in browser with integer values e.g.:weight between 10 and 25
View 3 Replies
View Related
Mar 6, 2015
how best to approach a problem involving two tables across two different servers.
Table 1: Contains IP Address along with assessment findings. Lets say the fields are IPADDRESSSTR, FINDING
Table 2: Contains Subnet information stored in integer format. The fields are SITE_ID, LOW, and HIGH
What I'd like to do is load the IP range information into memory and then return the findings from table 1 where the IPADDRESSSTR is between the LOW and HIGH integer value.
1) Is there a way to load all of the ranges from table 2 into an array and then compare all the IP addresses (IPADDRESSSTR) from table 1?
2) How do I convert IPADDRESSSTR (a string) to an integer to perform the comparison.
View 0 Replies
View Related
Oct 5, 2015
I need to convert a a string column to integer. Before converting, I need to check if it has blank values then convert it to NULL. Someone told me that its easier to convert it to NULL before converting to integer.
View 5 Replies
View Related
Jul 20, 2015
Working on a new database where the Date and Time are stored in a Date Time Field.
Then working on an OLDER database file within the same SQL Database contains these 2 items as integers:
transDate = "71615" (July 16, 2015)
transTime = "12345" (01:23:45 AM)
How do we convert both of them into a single SQL DateTime field such as "2015-07-16 01:23:45.000" so that it can be used in a join restricting to a date time in a different SQL File that properly has the DateTime in it?
This works well for converting the transDate Part in the select statement:
  dbo.IntegerToDate(at.transDate) as transDate
  * That returns: "2015-07-16 00:00:00.000"
* The resulting data must work directly in a Microsoft SQL Server Management Studio Query using either using the "on" statement or part of the "where" clause. In other words, NOT as a stored procedure!
Also must be able to be used as a date difference calculation when comparing the 2 files Within say + or - 5 seconds.
View 3 Replies
View Related
Mar 15, 2007
I cannot remember how to pass a value to a stored procedure. I would work this through but I am really running out of time, any help greatly appreciated. This is my stored procedure and I need to pass CompanyID from the code behind page in for the stored procedure @C_ID value.
PROCEDURE dbo.EditCompanyInfo @C_ID int, @CS_CompanyName nchar(100), @CS_City nchar(50) AS UPDATE tblCompanyInfo_Submit SET CS_CompanyName = @CS_CompanyName, CS_City = @CS_City WHERE C_ID = @C_ID RETURN
This is my aspx. page:
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" Runat="Server" > <asp:TextBox ID="TextBox1" runat="server"> </asp:TextBox> <br /> <asp:TextBox ID="TextBox2" runat="server"> </asp:TextBox> <br /> <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br /> <asp:DetailsView ID="DetailsView1" runat="server" DataSourceID="SqlDataSource2" Height="50px" Width="125px"> <Fields> <asp:CommandField ShowEditButton="True" ShowInsertButton="True" /> </Fields> </asp:DetailsView> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>" InsertCommand="CompanyInfoSubmit" InsertCommandType="StoredProcedure" OnInserted="SqlDataSource2_Inserted" SelectCommand="SELECT CS_CompanyName, CS_City FROM tblCompanyInfo_Submit WHERE C_ID = @CompanyID " UpdateCommand="EditCompanyInfo" UpdateCommandType="StoredProcedure"> <UpdateParameters> <asp:Parameter Name="CS_CompanyName" Type="String" /> <asp:Parameter Name="CS_City" Type="String" /> </UpdateParameters> <InsertParameters> <asp:Parameter Direction="ReturnValue" Name="ReturnValue" Type="Int32" /> <asp:Parameter Name="CS_CompanyName" Type="String" /> <asp:Parameter Name="CS_City" Type="String" /> </InsertParameters> </asp:SqlDataSource> <br /></asp:Content>
CODE BEHIND:
public partial class aaatest : System.Web.UI.Page{ int CompanyID; protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { DetailsView1.ChangeMode(DetailsViewMode.Insert); TextBox1.Text = "insert"; TextBox3.Text = Convert.ToString(CompanyID); } } protected void SqlDataSource2_Inserted(object sender, SqlDataSourceStatusEventArgs e) { { foreach (System.Data.SqlClient.SqlParameter param in e.Command.Parameters) { string RValue = Server.HtmlEncode(param.Direction.ToString()); if ( RValue == "ReturnValue" && Page.IsPostBack) { TextBox1.Text = Server.HtmlEncode(param.Value.ToString()); TextBox2.Text = "Return"; CompanyID = Convert.ToInt16(TextBox1.Text); TextBox3.Text = Convert.ToString(CompanyID); } } } }}
View 1 Replies
View Related
Apr 1, 2008
I have a stored procedure and one of the fields in the SELECT is a datetime type. On SELECT I am getting the date and the time but no AM/PM returned. The datetime in the query is returned in this format:
2008-03-21 08:37:20.000
Any help is appreciated.
View 3 Replies
View Related
Jul 23, 2005
We're using SQL Server 7 with an Access 2000 MDB as a front end with ODBClinked tables. I recently created a new set of tables for the app, and usersare complaining that unsaved data is being lost when they move to a newrecord. This seems to be the case when there are multiple users. When thereis a single user using it, we don't seem to have that problem.It seems that we had this problem when we first converted from an MDB backend to a SQL 7 back end, years ago, but we haven't had this problem in awhile. These are the first "entirely new" tables created in several years,and we seem to be having that problem again.Is this something with SQL 7 when it's dealing with new tables? Any ideas onwhat to do?Thanks!Neil
View 32 Replies
View Related
Feb 13, 2008
I'm a newbie to both Windows Forms development and SQL Server CE, so please bear with me...
I am developing a vocabulary builder application, and my data keeps reverting to the data set I originally imported. I keep losing the records I've added using the application I'm developing, or from within VS2005. I know about the 'Copy To Output Directory' option in the Solution properties and I set this to 'Do not copy'.
What gives? Not sure what other information you might need, but I suspect it's something really basic...
Thanks!
View 9 Replies
View Related
Aug 1, 2000
. When I copy tables from one database to another (Using DTS Wizard) I lose my settings .. primary keys + default values !!
Any help would be appreciated..
.
Thanks
View 2 Replies
View Related
Mar 19, 2004
This isn't really DB related, except for the connection, but I was wondering if any of you guys had run into this before.
I have a ODBC System DSN Setup for a connection to my MSSQL2K Server. My users will occasionally lose the Default Database setting (Drop-down during DSN Setup), forcing me to go in and reset it. It's getting to be a pain in the ass and I was wonder if any of you guys had run into this before.
Thanks!
Ed
View 1 Replies
View Related
Feb 18, 2008
Hi, i am having a problem when i tried to execute a SSIS package from a job in sql server 2005
First of all, when i create a new connection in the designer, i complete with server, user and password, and test connecion succesfull.
Then, i execute all the package and it works fine.
After building it, i tried tu execute the package using the file that it ve just been created into bin folder, but the connecttions didnt have the tag password, so after user id i add ;password=xxxx in every connection and it works fine. But every time i wantex to execute, i have to write the password.
Also i tried openeing the text file that the DTS created, but when i open it again, the password wasnt there.
Finally, i tried to create a job that execute this file, and i have to write the password as i did when i execute directly from the file, but i couldnt run. It has an authentication problem. If i open again the step configuracion, the password is not there.
The same that happen every time, but now i can run the package
Any idea?
Thanks a lot
View 2 Replies
View Related
Apr 17, 2008
Hi - I am attempting to run an SSIS package through a SQL Agent job step. The package is fairly basic - I run some Execute SQL tasks, build an ADO recordset, enter into a For-Each loop, and perform some table updates based on the ADO recordset. So far so good. Approximately 1/3 of the records in my For-Each loop are processed, then I get a "Failed to acquire connection error". All I am doing for each iteration of the For-Each loop is updating one record in a table based upon the current value that is being processed from the ADO recordset. I am running the job on a 64-bit box, version 9.00.3050.00. I have also run this on two other 64-bit boxes (versions 9.00.1399.06 and 9.00.1406.00) and had the same issue. The owner of the Agent job is a sysadmin on the box. I don't understand why some items would be processed, then the connection drops mid-way through processing. What's even odder is the connection is dropped at the same point every time I run. The connection that I have defined in my package (I only have one connection defined in my package) is Provider type "Native OLEDBSQL Native Client", and I am using Windows authentication. The version of Visual Studio that I have developed this package in is 8.0.50727.42. Please help.
View 7 Replies
View Related
Aug 10, 2007
Hi,
I have written a little bit of VB.NET code that basically takes three strings, transforms them, and returns a single string to be stored in my table.
I am running into a strange problem, however... for some reason, a number of my processed rows are missing a pair of double quotes (").
The vast majority of the records are formatted properly, and have the double quotes in the expected locations.
The most frustrating thing about it is that I have included the offensive input strings in my Test.sql test script, and when I step through the entire routine, the return value is perfect...
i apologize for being the worst ever at posting questions here, please let me know if i can add anything
View 4 Replies
View Related
Jan 11, 2007
Hi,
I am facing a wierd problem while sync'ing. When I am do a synchonize from a mobile device to the server DB, one of my table column fails to update while all other columns get updated.
The table uses row level tracking (tried making it column level too) and the field which fails to update is a date field (Nullable).
This happens in case of syncing after updating >1 records on device. But syncing after updating just 1 record on device, this date field gets updated as expected.
Note: I have filters set for this table. The filter downloads fields only with this date field=null.
I need to downlaod all records meeting this filter condition but at the same time, this filter should not be applied while uploading changes as I think this is what the problem is.
If anyones has faced this and got a solution, please let me know.
Thanks.
View 3 Replies
View Related
Oct 26, 2007
We have several servers running SQL Server 2005. Each of these were upgraded from SQL Server 2000. Each server has about 50 databases on it. Each database has a table with the same name, containing login information to the database that the table resides in.
We have a VB 6.0 application that allows our Help Desk to do a SELECT on the table that contains login information. All it does is a SELECT; no UPDATEs, DELETEs, INSERTs, or DDL operations are performed by this application. The application logs into the databases using a Security Group login (let's call it MyGroup). Each member of the Help Desk team is a member of MyGroup.
About twice a month, the MyGroup security group loses SELECT permissions on all of the databases on a server. The server affected usually is different each time, but sometimes the same server will be affected two times in a row.
So far, we just run a script to update the SELECT permissions on the table in each of the databases, and this takes care of the problem (for now). But the problem seems to be recurring regularly.
What would make the server lose all permissions for a particular security group? The other logins continue to work fine. Only this one security group seems to be affected.
I just can't figure this one out! Help, please!
Nancy
View 4 Replies
View Related
Mar 5, 2008
Hi, I'm working on a project that connects to a database on every view of a site. It works fine for a few hours but eventually it just stops connecting to the database. There's nothing in the application logs to suggest where to look, it seems like the connections are just dying.
I am creating the connection in a pretty normal manner:SqlCommand myAwesomeCommand = new SqlCommand();myAwesomeCommand.Connection = new SqlConnection(myAwesomeConnectionString);myAwesomeCommand.CommandText = myAwesomeSQL;myAwesomeCommand.CommandType = CommandType.Text; // Otherwise known as awesomeText
myAwesomeCommand.ExecuteScalar();
I also use a SqlDataAdapter and fill it for some of my stuff for grabbing more complex data, but all happy System.Data.SqlClient stuff.
But yeah, the thing seems to just give up after a few hours. Everything I have read about connection pooling in ADO.Net says that the connections won't be reused. And yes, I believe I am closing all of my connections after I use them. The site is getting, oh, 7500 hits an hour maybe? Somewhere around that, maybe more maybe a little less.
Anyone have any ideas?
View 2 Replies
View Related
Aug 7, 2000
When I a set of databases from backup all login information is lost. The front end of the application provides a way to create new users, but it is a very long process. I am looking for a script or stored procedure that will help attach the users to the database.
View 3 Replies
View Related
Apr 12, 2000
I backed up and restored a database successfully except for the users. The only user that shows is dbo, but I can't add a user with the proper user name from my old server; I get the error that the user or role already exists in the current database.
When I go to role, name of public, there is the user I need to add. I can't remove it as it's a table owner, but I need to add it to the list of users, so I need to remove it then re-add it.
I tried using EXEC sp_changeobjectowner to no avail.
Thanks for any help/guidance.
View 2 Replies
View Related
May 1, 2007
I have 2 machines, each running the same version of SQL Server. I'm trying to use the dts to export my data from one to the other, and it works fine, except for the fact that I lose my unique index on a field. Is there a way I can keep this from happening, or am I going to have to recreate it each time? I wouldnt think I would have to, seems like bad design to me.
View 1 Replies
View Related
May 17, 2005
Does anyone know how to reset or reorder a primary key id column without losing any data in the table.
at the moment my id's are: -
7
9
19
21
and I want them to be: -
1
2
3
4
View 4 Replies
View Related
Sep 6, 2007
Hi,
I'm testing the use of application roles for security. The customer I work for has still a lot of ASP intranet applications running. We're migrating the databases to a SQL Server 2005 server.
I've changed the connection string to a user without any permissions but to log on. After that I use an application role for permission to select different tables and to execute Stored Procedures.
The first queries do execute but after that I get "Permission denied", like I haven't got the application role anymore.
Any ideas?
Adrian
View 6 Replies
View Related
Sep 11, 2007
On my SSIS package I have 2 connection managers, both are connecting to an Oracle db. I enter in the ID and Password then click 'test connection' I'm able to connect to the database fine. I then go to a data flow of one of my control flow task. Open up my OleDb Source and click preview. The SQL query executes with no issues. I can see the data from my connection manager source.
I then go and run the package and I get this error message:
[OLE DB Source [1]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "OracleConnection" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
I've been racking my brain on this for a few days and still no luck on getting this to work.
View 8 Replies
View Related
Feb 18, 2007
Hi
We got a server running SQL server 2000 SP4 with a database that€™s being replicated
There are 17 subscribers running MSDE SP4 using merge replication. Replication is started manualy
Initially we tested this with two subscriptions an everything went well, but now, since 3 months, we are facing a weird problem while sync'ing. We have massive data loss on records that where inserted at the subscribers. Records seem to disappear, but only record that have a foreign key constraint. What I mean is that for example a record is inserted at the table that holds our client records with primary key €˜ClientID€™ and then a record is inserted in a table with actions for that client with a foreign key €˜ClientID€™ referring to the client table. After sync€™ing that client record is inserted correctly in database on the publisher but the records in the table with actions are gone.
As far as I know the tables are correctly formed with identity set not for replication and so on.
Shortly, I can€™t find any problem, a specially when it doesn€™t happen always.
If anyones has faced this and got a solution, please let me know.
Thanks.
Raf
View 3 Replies
View Related
