Dropping Integer Values

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


ADVERTISEMENT

Transact SQL :: Convert Comma Separated String Values Into Integer Values

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

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 View Related

Errors With Null Integer Values

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

Sorting A Parameter With Integer Values

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

Converting Integer Values To Datetimes

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

Convert Nvarchar Values To Integer

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

How To Re Number Integer Field Values In A Table

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

T-SQL (SS2K8) :: Converting Integer Values To Date-time?

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

SQL Server 2012 :: Script For Integer Values Used In A Table

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

Analysis :: Filter Numeric Values With Integer Value In SSAS

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

SQL Server 2008 :: Comparing Integer Values Across Tables And Database Servers?

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

Integration Services :: Replace Blank Strings Values To NULL And Convert To Integer Data Type

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

DB Design :: Convert Integer Date (MMDDYY) And Integer Time (HHMMSS) To DateTime Format?

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

Dropping Tables

May 28, 2002

Is there a way we can prevent a object owner from dropping his tables. Example:

There is a user called 'tom' who is given create table permissions, 'tom' creates the table completes his dev and then the table is moved into production where 'tom' is still the owner. However, in production, 'tom' does not have the create table privs. Because 'tom' is the owner of the table, he is still able to drop the table in production. This is what I am trying to avoid. I would like to retain 'tom' as the owner and want to take away his create/drop privs.

View 1 Replies View Related

Dropping The Statistics

Nov 17, 2000

Does anyone have any generic scripts that Drop all the statistics that SQL auto generates?? I have hundreds of '_WA_....'
indicies that are auto created by SS7 and I just want to get rid of ALL of them. Thanks!

View 3 Replies View Related

DRopping Primary Key.

Dec 7, 1999

Simple heres the syntax Alter Table XX Drop PRIMARY KEY.

This is not working whats up?

Thanks,
Phil

View 3 Replies View Related

Dropping A Column

Apr 12, 2001

Whats the quickest way to drop a column from a table with 1 million rows.
This is for SQL Server 6.5

Thanks for all the responses,
Chan

View 1 Replies View Related

Dropping Connections - 6.5

May 22, 2001

I need to drop a database but cannot because there are 2 open connections that I cannot drop via EM. Is there a way to do this without starting and stopping??

View 2 Replies View Related

Dropping Columns

Jul 20, 1998

Hi,

I`m new at SQL Server so excuse me if this question sounds dumb.

Does anybody know how to drop columns in a table. I know I can drop and re-build the table,
but I would prefer and easier way.

Thanks

-Z

View 1 Replies View Related

Dropping Database

Dec 21, 1998

Hi,

After I drop the database, I backed up the master database, I recreate the database with new name. When I tried to do the import on the command level,
It gives me the error:
Attempt to locate entry in the sysdatabases for database 'db1' by name,
failed -- no entry found in that name.

What should I do? Thanks.

View 1 Replies View Related

Dropping Defaults

Jul 6, 2001

I have run the following script to create a default on a table:

alter table TableXYY
add default 0 for ColumnX

Now, how do I delete this default (without re-creating the table)??

Thanks

View 1 Replies View Related

Dropping Users

Jul 23, 2001

Anyone know how to drop all users associated with a particular login not matter what database they're contained in?

ie login AdminUser / User AdminUser (in databases master, Dev1)

....
Thus, is there a way to drop the AdminUser login above and associated user
AdminUser in databases master, Dev1 in example above.

Thanks

View 1 Replies View Related

Dropping Database

Sep 7, 2007

Hi friends,

How I can drop a database insatance from a different server ( data base is SQL Server 2005) from my code

Hoping a early reply ....
Thanks :)

View 5 Replies View Related

Dropping A Set Of Tables

Feb 22, 2004

Hi
I am using SQL server 7 database and ASP as front end. I run an application where a text file is loaded into database. After this is done procedures are run to create a set of tables that have snapshots of the data in the text file.. each time i load a new text file i want to create the snapshots.. i hve written a stored procedure to create tables and insert values into the tables.. however how do i delete the tables i created the previous time.. the number of snapshots and their names will depend on the size of the text file.. how do i refer to all the snapshots created and drop them all before creating new ones?
plese guide
regds

View 1 Replies View Related

Dropping All Indexes

Jan 3, 2007

Looking for suggestions.

I have a database that is giving me a bad Index error. When I go to drop the necessary Index it is telling me that it either does not exist or cannot be dropped. However when I try to build that index, it tells me one already exists.

Is there any way to drop all of the indexes or at the very least see what the indexes are? This particular database is using 2005 Express.

Any help would be great!
Shawn

View 4 Replies View Related

Dropping Constraints

Oct 15, 2007

How to remove the constraints on a table in sql server 2000? I need to drop the column, so i need to drop the constraint before that.

Alter Table Table_Name NO CHECK constraints ALL.

Once I execute the above one, it says, "Successfully Executed" but when I try to drop it doesnt allow me and gives me the following error:

Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF_Users_Created_by' is dependent on column 'CREATED_BY'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN CREATED_BY failed because one or more objects access this column.

Any ideas?

Thanks,

View 2 Replies View Related

Dropping A Constraint

Sep 20, 2006

I think the answer to this question will be something like 'you'll have to re-initialize the subscribers', but I need to ask anyway...

I created a publication where my foreign key constraint we NOT created with NOT FOR REPLICATION. My publication is configured to replicate DDL changes. Is there any way I can drop and re-create the constraint in the publisher and get replication to push the change to the subscribers?

Thanks for your help

Graham

View 1 Replies View Related

Trouble Dropping A Job

Mar 10, 2007

I have trouble dropping a job that was a maintence plan.

I get the following error. Is there a way around this?

TITLE: Microsoft SQL Server Management Studio
------------------------------

Drop failed for Job 'Maintenne Plan'.  (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'.
The statement has been terminated. (Microsoft SQL Server, Error: 547)

 

View 3 Replies View Related

Dropping A Database From ASP.NET

Jan 16, 2008

I am trying to run a dymanic DROP DATABASE <dbname> command in a stored procedure called from ASP.NET. I typically connect from the .net using a SQL builtin account. I am getting this error:

System.Data.SqlClient.SqlException: Cannot drop the database 'db_testco', because it does not exist or you do not have permission.

The database is there for sure asI can browse it via the Studio manager. What permission do I need to set for my .net login to allow it to drop ? I tried adding the Delete permission to the database and still no go...

Setup: SQL 2005 SP2 2 Node cluster.

Thanks.

View 3 Replies View Related

Best Way Of Dropping The Column

Oct 11, 2007

Hi,

I have a table with multiple column and has millions of rows in it(say about 50 millions ) and its in production.
What is the best way of dropping the a column from the table without impacting the systems performance. Remeber this table will be used some application to get some data out of it.


~Mohan

View 1 Replies View Related

Need Help Dropping Master Key

Apr 11, 2007

I am having following issue recreating my service broker



This could be a trivial fix...



when i execute the following query..



open master key decryption by password = 'pass1234$'

alter master key drop encryption by password = 'pass1234$'

close master key

drop master key

go



I get the following result..



Msg 15558, Level 16, State 1, Line 3

Cannot drop encryption by password '********'.

Msg 15580, Level 16, State 1, Line 5

Cannot drop master key because certificate 'BackofficeServiceAccount' is encrypted by it.



But 'BackofficeServiceAccount' was already dropped.



Can any one help me how to drop master key?



I appreciate any input..



regards...

View 3 Replies View Related

Trigger Dropping

Apr 12, 2006

Hi:

I think I am doing something wrong. I am trying to make the trigger fire when I want to drop the same trigger. For example I have the following trigger defined:

USE AdventureWorks2000

GO

CREATE TRIGGER Incheckstock

ON DATABASE

FOR DROP_TRIGGER

AS

PRINT 'You must disable Trigger "Incheckstock" to drop Trigger!'

ROLLBACK

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

ENABLE TRIGGER Incheckstock ON DATABASE



DROP TRIGGER Incheckstock

ON DATABASE

I created the trigger Incheckstock and i want to see if the same trigger fires itself if when i am trying to drop the trigger with the same name (kind of recursive one). Is this possible in SQL 2005?. If I asked a dumb question, please correct me.

Thanks

View 1 Replies View Related







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