Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







BCP Utility - Skip Rows..


Hello All,

Does the BCP utility enable you to selectively import rows from a flat
file to a table ?

For example:

The first column in my flat file contains a record type - 1, 2..7
I only need to import types 1, 2, & 3

Can this be specified in the .fmt file ?



Thanks in advance
hharry


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Skip Rows In Excel
Hi,

How to skip my 12 header rowsfrom XLS input source?

(before the Excel driver reads (by default, 8 rows) in the specified source to guess at the data type of each column.)

thx,

f.sor

View Replies !   View Related
Footer Rows To Skip
OK. We know there is Header rows to skip options and it works great.

I've got the file that has a "footer". Here is an example:

.
PSC
filename=table1
records=0000000000525
ldbname=db1
timestamp=2006/02/07-16:25:00
numformat=44,46
dateformat=mdy-1910
map=NO-MAP
cpstream=ISO8859-1
.
0000260611


It's ALWAYS last 12 rows.

Is there a way to split at this point and put the 12 rows in a different location? The task is twofold - I don't need these control rows in my data and I need value of "records" to verify loaded number of rows.

UPDATED: After some testing I found out that the Flat File source does not see that footer at all. This is good and bad - I do want to load this metedat into some other tables.

Dima.

 

View Replies !   View Related
Data Rows To Skip Does Nothing
I have put an expression on the data rows to skip in a flat file connection, however it is having no affect. I have tried setting delay validation but still nothing.

Any tips

View Replies !   View Related
Skip Rows Where The First Column With Letter 'S' - BCP
Hi All,I have this data file with fix length(see below). I am able to insertit into the database using bcp, but now I want to skip (do not insert)the row which start with letter 'S' into the database. Is there away todo it? By the way I am using -F2 option to skip the first record.Here is my data:Record 1 04XXX2 13106900240120042003040045061 Testing N POLYDOROS TRUSTEEE2 12621241640280041004040045633 What are they MARTIN &XXXXXS C1000003200400409850000059611000000500001000000001 9613000000576497500S X1000003200000209850000059613000000000000000000001 9613000000573497000Thanks for your help.Ted Lee

View Replies !   View Related
Skip Rows Affect The Results
Dear all,


In Flat File Source properties windows there's Preview node, when we check that node there's an option to skip the data in how many rows. Is it affect the result ?

Best regards,

Hery

View Replies !   View Related
WANT TO SKIP ROWS AAND ALSO UNPIVOT
hI,

i have this particular problem with the unpivot.The below is my flat file source.The dates can go upto 130 columns.this countcan also vary.SM,SR,SB are again values repeating for diff instrument.They are the values of the instrument on the particular dates.This is a snap shot of one feed.Other feeds may have the dates differing.How do i read this file.

Problem 1:If i skip the first row and unpivot the 2nd row,then with the new feed,with new dates my SSIS package will bomb as it will not find the col names.

Problem 2:IF i uncheck the "Use first row as column headers" then the problem 1 is solved but the o/p will be

20080101

20061102

20061103
1.2

1.3

1.2.

1.5

.....and so on..

IS there any other way to fix this.These are feeds with the spread values of instruments on particular dates.Please help.



RUN 2.01E+11 132238 0 45
INSTRID DATATYPES 20081101 20061102 20061103
Z03369 SM 1.1 1.2 1.3
Z03369 SB 1.31.3 1.7

Z03369 SR 2 3 4

Z81910 SM 1.1 1.2 1.3
Z81910 SB 1.31.3 1.7

View Replies !   View Related
XLS Import: How To Skip Empty Or NULL Rows
Hi,
I am working with my first spreadsheet as a data souce within SSIS. It has multiple worksheets with a table of values on each Rows are marked with dates and the columns are marked as intervals (1m, 3m, 2y, etc.). I have no control over the fact that the monthly format of the XLS file will be such that:
1. There are some blank rows between the start of the worksheet and the column headers. (the number may never change)
2. Most cells have decimals to represent interest rates, but one or two rows are mostly blank with one cell populated and some 'helpful text' in the same row describing the data.
3. The main worksheet has two tables side by side giving a 5-year moving average of data - one for one company, and another for a second company.
4. The monthly version of the file will have data added to the end (no historical changes to previous data).


I think my main problem is configuring the package to gracefully 'skip' to the first populated row in each worksheet. How do I do that? The 'column headers in first row' option is checked, and that helps, but doesn't allow to skip N number of data rows like a flat file connection manager.

View Replies !   View Related
Bulk Insert, Skip Rows With Duplicate Key Error?
Does sql server have a way to handle errors in a sproc which would allowone to insert rows, ignoring rows which would create a duplicate keyviolation? I know if one loops one can handle the error on a row by rowbasis. But is there a way to skip the loop and do it as a bulk insert?It's easy to do in Access, but I'm curious to know if SQL Server propercan handle like this. I am guessing that a looping operation would beslower to execute?

View Replies !   View Related
Header Rows To Skip On Flat File Import/Export
Just attempting to import a simple tab delimited text file into my SQL Server 2005 database using the SQL Server Import and Export wizard. Column names are specified within the first line of the file. The Header Rows to Skip field value is listed as 0, but the wizard indicates that "The field, Header rows to skip, does not contain a valid numeric value".

Why isn't zero (0) a valid numeric value? I don't want to skip any rows. PLUS, I get the same error when trying to export to a text file although the header rows to skip field does not exist. I can increase the number to 1 or more, but the wizard will skip part of my data .. unacceptable.

What am I missing here? I installed SP1 of SQL server 2005, but that did not help.

Thanks in advance.





View Replies !   View Related
Changing &&"header Rows To Skip&&" Property In Flat File Connection During Runtime
Hi all

I have a flat file.I am trying to set the value for the property "HeaderRowsToSkip" during runtime.I have set an expression for this in my "flat fileconnection manager". But this is not working.The connection manager is not able to take the value during runtime.

My expression is as follows:

DataRowsToSkip : @[user:: Var]

where "Var" is my variable which gets the value from the rowcount component and trying to set it back to the "HeaderRowsToskip" property.

I ve even tried setting the value to the "HeaderRowsToSkip" property in the expression builder.

Its not working....

Can anyone help me out in solving this????

Thanks in advance

Regards

Suganya

View Replies !   View Related
How To Check The Number Of Rows Transfered In The Dataflow Task By Using Dtexec Utility
We run the SSIS through tidal scheduling agentusing the dtexec utility. We want to see the number of rows transfered while running or after it has run our package.We require answers for the following:

1) How to see the number of rows transfered while running the packageusing dtexec utility

2) what parameter should be used in dtexec command line toget the number of rows transfered in the Log file after execution.



Thanks

Subhash Subramanyam

View Replies !   View Related
Skip 1st And Last Row In DTS
Does any one know, how to skip the first and last row of a text file while importing using DTS.

ThankX in advance.

View Replies !   View Related
Do While Skip In Selecting...
Hello:

I have one table and it contains a column named ID Number, and a column named Date. I have a Do While statement that runs a SQL select statement a few times based on the number of records with the same ID Number. During the Do While statement the information is copied into another table and deleted from the old table. After I look at the results, I see that at the second Do While loop, the data was not selected and the Select statement did not run... so the old variable value from varValue is used again... Any reasons on why?

Here is a code snippet of what is going on:
Do While varCount < varRecordCount
conSqlConnect.Open()
cmdSelect = New SqlCommand ("Select * From temp_records_1 where [id number]=@idnumber and date<@date", conSqlConnect)
cmdSelect.Parameters.Add( "@accountnumber", "10000" )
cmdSelect.Parameters.Add( "@date", dtnow )
dtrdatareader = cmdSelect.ExecuteReader()
While dtrdatareader.Read()
If IsDbNull(dtrdatareader("value")) = false Then
varValue = dtrdatareader("value")
End If
End While
dtrdatareader.Close()
conSqlConnect.Close()

'#####The information above is copied to another table here
'#####The record where the information was received is deleted.

varCount = varCount + 1
LoopAny ideas?

View Replies !   View Related
RPC - Skip Tables
I was attempting to use BCP today via xp_cmdshell. I have never done anything with BCP before, so it was very enlightening. However, I ran across a problem that maybe someone could help explain to me a little more.

I am using the "queryout" option, and when I run it, the error I get is that you "can't skip fields except for on inserts" or something like that.

The reason I was trying to use bcp is the ability to dynamically generate a filename, i.e. filename = 04182004 (the date). Because in the file name argument, I can use a variable. Make sense?

Since I apparently can't ignore fields, I am thinking of taking all of the information I need daily out, and into a seperate table, then I can use the xp_cmdshell to run a bcp that creates a file with the date as a filename, and I won't be ignoring any fields because I have just put the information I need in the new table. Am I making sense? Does this sound like an appropriate thing to do?

View Replies !   View Related
Skip A Step In DTS
Hi all,
This is my first post here so here it goes...

I'm building a DTS package and I got 7 different path in it. It starts out with a dynamic property task that reads some values from some ini files, then I got a ActiveX script that puts those values into my text files as file names and the email address i email the results to.

I am creating 6 reports that will run every day and they are fine. A datatask runs a scrips, makes a cvs file, zip it and email it. No problem. But now i'm trying to put a report that only should run once a month. So i'm testing to see if it is the first of the month and then: DTSGlobalVariables("SkipTask").Value = True so the SkipTask value is true if it is the first of the month. Now I'm trying to skip the datapump task, the cvs file task and zip file task that belong to that report, but still email the other reports. Is this possible?

I'm been able to skip the tasks using the script from here (http://www.sqldts.com/default.aspx?214,2) but the email task does not run so it looks like it thinks my last report didn't succeed.

Any way I can do this? I really needs some help with this thing :(

View Replies !   View Related
To Skip Recovery
Hello somebody,

I submitted an update query on a table of 80 million rows, in the weekend. When I returned on Monday, the transaction was still running. I thought some thing wrong happened and cancelled the transaction. It was taking long time to rollback the transaction. I recycled the SQL Server assuming it will do faster recovery. Now I realised that anyway it is going to take lot of time. And SQL server is not going to be up till the database is recoverd completely.
Now can any body suggest me any thing to faster this process or skip this process. I dont know how long it is going to take rollback the transaction which ran for more than 70 hours.


Thanx in advance.

View Replies !   View Related
Skip Footer Row
Hi,


There is an option in ssis to skip one or more header rows, but there isn't any thing to skip one or more footer rows.

Example:

headerbla bla
1;"Joe";24;"New York"
2;"John";54;"Washington"
3;"Phil";36;"San Francisco"
footer bla bla


I skip the first record in the source definition. So I have left 4 records. How do I skip the fourth (last) record? The value contains some statistics so I cann't look for a special value. Is there a way to skip the last record with a script component?

Joost

View Replies !   View Related
Skip The Error


Hello

I am tryung to execute a Store Proc using Execute SQL Task.

I am very aware that if there is any errors occur I have handled it sufficiently.

All I want to do is, when ever there are any errors in teh Store Proc then this Execute SQL task should not fail and it should go for the next Task in teh control flow.

How can I do this?

View Replies !   View Related
CSV To SQL Import, Skip First Row (two Functions)
I have this code. It works, but inserts the header row into the gridview. I need to avoid the first row.   Protected Sub excelimport(ByVal dataSrc As SqlDataSource, ByVal fileName As String)        Dim intFileNameLength As Integer        Dim strFileNamePath As String        Dim strFileNameOnly As String        Dim strpath As String        If Not (uploadfile.PostedFile Is Nothing) Then            strFileNamePath = uploadfile.PostedFile.FileName            intFileNameLength = InStr(1, StrReverse(strFileNamePath), "")            strFileNameOnly = Mid(strFileNamePath, (Len(strFileNamePath) - intFileNameLength) + 2)            'If File.Exists(paths & strFileNameOnly) Then            'lblMessage.Text = "Image of Similar name already Exist,Choose other name"            'Else            If uploadfile.PostedFile.ContentLength > 40000 Then                lblmessage.Text = "The Size of file is greater than 4 MB"            ElseIf strFileNameOnly = "" Then                Exit Sub            Else                'strfilename = uploadfile.FileName.Substring(0, (InStr(uploadfile.FileName, ".") - 1))                strFileNameOnly = fileName & ".csv"                strpath = "/sites/marketing/apps/disposition/content/excel/" '& strFileNameOnly                uploadfile.PostedFile.SaveAs(Server.MapPath(strpath) & strFileNameOnly)                'lblmessage.Text = "File Upload Success."                'Session("Img") = strFileNameOnly                Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & (Server.MapPath(strpath)) & ";Extended Properties=""Text;HDR=No;FMT=Delimited"""                '"Provider=Microsoft.Jet.OLEDB.4.0;" & _                '"Data Source=" & "/" & strFileNameOnly & ";" & _                '"Extended Properties=Excel 8.0;"                Dim conn As New OleDb.OleDbConnection(strConn)                Dim myData As New OleDbDataAdapter("SELECT * FROM " & strFileNameOnly, conn)                Dim myDatatable As New System.Data.DataTable                Dim mySqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("BDOConnectionString").ToString())                ''You must use the $ after the object you reference in the spreadsheet                myData.Fill(myDatatable)                InsertData(myDatatable, mySqlConnection)                'System.IO.File.Delete(Server.MapPath(strpath))                GridView1.DataBind()                upload.Visible = False            End If        End If        ' GridView1.DataSource = myDataset.Tables(0).DefaultView        ' GridView1.DataBind()    End Sub    Private Shared Sub InsertData(ByVal sourceTable As System.Data.DataTable, ByVal destConnection As SqlConnection)        ' old method: Lots of INSERT statements         ' first, create the insert command that we will call over and over:         destConnection.Open()        Using ins As New SqlCommand("INSERT INTO [tblAppointmentDisposition] ([contactdate], [dnbnumber], [prospectname], [businessofficer], [phonemeeting], [followupcalldate2], [phonemeetingappt], [followupcalldate3], [appointmentdate], [appointmentlocation], [appointmentkept], [applicationgenerated], [applicationgenerated2], [applicationgenerated3], [comments], [newaccount], [futureopportunity]) VALUES (@contactdate, @dnbnumber, @prospectname, @businessofficer, @phonemeeting, @followupcalldate2, @phonemeetingappt, @followupcalldate3, @appointmentdate, @appointmentlocation, @appointmentkept, @applicationgenerated, @applicationgenerated2, @applicationgenerated3, @comments, @newaccount, @futureopportunity)", destConnection)            ins.CommandType = CommandType.Text            ins.Parameters.Add("@contactdate", SqlDbType.Text)            ins.Parameters.Add("@dnbnumber", SqlDbType.Text)            ins.Parameters.Add("@prospectname", SqlDbType.Text)            ins.Parameters.Add("@businessofficer", SqlDbType.NVarChar)            ins.Parameters.Add("@phonemeeting", SqlDbType.Text)            ins.Parameters.Add("@followupcalldate2", SqlDbType.Text)            ins.Parameters.Add("@phonemeetingappt", SqlDbType.Text)            ins.Parameters.Add("@followupcalldate3", SqlDbType.Text)            ins.Parameters.Add("@appointmentdate", SqlDbType.Text)            ins.Parameters.Add("@appointmentlocation", SqlDbType.Text)            ins.Parameters.Add("@appointmentkept", SqlDbType.Text)            ins.Parameters.Add("@applicationgenerated", SqlDbType.Text)            ins.Parameters.Add("@applicationgenerated2", SqlDbType.Text)            ins.Parameters.Add("@applicationgenerated3", SqlDbType.Text)            ins.Parameters.Add("@comments", SqlDbType.Text)            ins.Parameters.Add("@newaccount", SqlDbType.Text)            ins.Parameters.Add("@futureopportunity", SqlDbType.Text)            ' and now, do the work:             For Each r As DataRow In sourceTable.Rows                If sourceTable.Rows.IndexOf(sourceTable.Rows(0)) Then                    'do nothing                Else                    For i As Integer = 0 To 16                        ins.Parameters(i).Value = r(i)                    Next                    ins.ExecuteNonQuery()                    'If System.Threading.Interlocked.Increment(rowscopied) Mod 10000 = 0 Then                    'Console.WriteLine("-- copied {0} rows.", rowscopied)                    'End If                End If            Next        End Using        destConnection.Close()    End Sub 

View Replies !   View Related
Skip First Row In A Datatable At Insert Into SQL...
Hi, I have the code below. I need to skip the first row in the datatable as it has the headers. This works now, but my gridview gets the header row inserted as a record.Private Shared Sub InsertData(ByVal sourceTable As System.Data.DataTable, ByVal destConnection As SqlConnection)        ' old method: Lots of INSERT statements         ' first, create the insert command that we will call over and over:         destConnection.Open()        Using ins As New SqlCommand("INSERT INTO [tblAppointmentDisposition] ([contactdate], [dnbnumber], [prospectname], [businessofficer], [phonemeeting], [followupcalldate2], [phonemeetingappt], [followupcalldate3], [appointmentdate], [appointmentlocation], [appointmentkept], [applicationgenerated], [applicationgenerated2], [applicationgenerated3], [comments], [newaccount], [futureopportunity]) VALUES (@contactdate, @dnbnumber, @prospectname, @businessofficer, @phonemeeting, @followupcalldate2, @phonemeetingappt, @followupcalldate3, @appointmentdate, @appointmentlocation, @appointmentkept, @applicationgenerated, @applicationgenerated2, @applicationgenerated3, @comments, @newaccount, @futureopportunity)", destConnection)            ins.CommandType = CommandType.Text            ins.Parameters.Add("@contactdate", SqlDbType.Text)            ins.Parameters.Add("@dnbnumber", SqlDbType.Text)            ins.Parameters.Add("@prospectname", SqlDbType.Text)            ins.Parameters.Add("@businessofficer", SqlDbType.NVarChar)            ins.Parameters.Add("@phonemeeting", SqlDbType.Text)            ins.Parameters.Add("@followupcalldate2", SqlDbType.Text)            ins.Parameters.Add("@phonemeetingappt", SqlDbType.Text)            ins.Parameters.Add("@followupcalldate3", SqlDbType.Text)            ins.Parameters.Add("@appointmentdate", SqlDbType.Text)            ins.Parameters.Add("@appointmentlocation", SqlDbType.Text)            ins.Parameters.Add("@appointmentkept", SqlDbType.Text)            ins.Parameters.Add("@applicationgenerated", SqlDbType.Text)            ins.Parameters.Add("@applicationgenerated2", SqlDbType.Text)            ins.Parameters.Add("@applicationgenerated3", SqlDbType.Text)            ins.Parameters.Add("@comments", SqlDbType.Text)            ins.Parameters.Add("@newaccount", SqlDbType.Text)            ins.Parameters.Add("@futureopportunity", SqlDbType.Text)            ' and now, do the work:             For Each r As DataRow In sourceTable.Rows                For i As Integer = 0 To 16                    ins.Parameters(i).Value = r(i)                Next                ins.ExecuteNonQuery()                'If System.Threading.Interlocked.Increment(rowscopied) Mod 10000 = 0 Then                'Console.WriteLine("-- copied {0} rows.", rowscopied)                'End If            Next        End Using        destConnection.Close()    End Sub

View Replies !   View Related
Sqldatareader Reads From Second Row Skip The First Row.
Hello,
im using sqldatareader to read my data and whenever time i loop through the reader it starts from second row why is that?
here is my code:while (reader.Read()){hinfo.Name = reader["_name"].ToString();hi.Add(hinfo);}
i look at the database and i have two rows but its reading only the second row, skiping the first row 
 

View Replies !   View Related
How To Skip Warning Message?
Msg 4039, Level 10, State 1
Warning, file <1> on device 'd:mssqlackupdefaultdata.dat' was dumped from database 'defaultdata'.
Msg 603, Level 19, State 1


I have created a backup from one default database and I am loading it for all the new databases which I am creating (Database creation and loading the backup every thing through ASP). Some time, because of this warning I am unable to proceed. Please help to overcome this problem.

Thanks in advance

Rajasekar

View Replies !   View Related
Skip A Row In The Flatfile Source
I am importing a flatfile and cannot seem to deal with an issue that seems quite simple.

The files have a header row with column names and those rows start with '#'

However sometimes this header row will also be present in the middle of the file.

The Source tries to parse this row and fails

Is there any way to tell the flafile source to skip rows that start with a particular character like comment rows?

View Replies !   View Related
How To Skip The First Row In An Excel Workbook


Hi All,

I have a excel workbook with many sheets, in each sheet the first row has to be skipped and the second row contains the column information and thereafter are the records.



The Excel Source in SSIS just gives an option: check if the first row has column names.

But the first row for me is junk -- a link to parent or first sheet-- and has to be skipped and the second row has the column info.



How can this be accomplished .... any suggestions would be of great help!!!



Sample:






Main








id

desc

price

date


1

apple

1.0

1/1/1900


2

banana

2.0

1/1/2000



Main in the first row is actually a hyperlink ... once we click this it takes us to the first sheet in the workbook which has all sheet names as contents.



I am stuck and not sure how to skip this!!

View Replies !   View Related
How Can I Skip An Initialize Snapshot?
Hence you have a database which huge tables and a transactional replication (push subscriptions).

Now my question:

1. if I have to initialize a snapshot but I would like to do it without the snapshot agent, what methods are available?



2. Usually the distribution agent will request an initialize snapshot. How can I tell him, that I would like to use an alternative method and that the distribution agent should NOT request a snapshot?



3. Any suggestions about a good practive for materializing huge and big tables wihtout using the disitrbution agent (e.g. "switch off" replication, bcp table out of the primary site and bcp it into the target site, "start" distribution agent so that it doesn't request a snapshot).



Regards

Nobsay

View Replies !   View Related
Use GOTO To Skip Part Of A Script?
I have a script that creates and populates several tables. However I only want this to occur if one table has a row count greater than zero. I'm trying to use GOTO to script to the end of the script. However I get the message "A GOTO statment references the label 'MYLABEL' but the label has not been declared." How can I do this.


I have something similiar to the following in my script:
IF (SELECT COUNT(*) FROM MYTABLE) = 0
BEGIN
PRINT 'NO ROWS FOUND'
GOTO MYLABEL
END

CREATE TABLE X...

SELECT INTO X FROM Y ...

ETC. ETC.

MYLABEL:
PRINT 'END SCRIPT'

View Replies !   View Related
What If User Skip An Integer Parameter In SP ?
I've a stored Procdure that gets following 5 inputs:

UserID: (Varchar) ... automatically provided by the application
PartnerID: (int) ... automatically provided by the application
PartnerURL: (varchar) ... optionally provided by User
Partner's Earning int) ... optionally provided by User
Partner Since (Datetime) ... optionally provided by User

User can search the Partners List on any one or more of the inputs provided by user. The problem is with the default values of these parameters, I know I can set '%' for the varchar parameters, so that if user has provided no value to search in the PartnerURL parameter, it will search all the entries in it.

But what about the Integer Parameters (Partner's Earning for exapmle), if I set it default to 0 or 1 or any other number, and if user leaves it blank, it automatically searches for the records where the Partner's Earning is equal to that specific number, and the result set it returns is obviously not the one that user wishes to see.

Right now, I'm writting a Dynamic Query inside the procedure to handle this problem, so that if the user has skipped an integer parameter its not included in the query and it works fine, but I was just wondering that there should be some other way to do that also.

Can someone help me in this problem ...

Thanks in advance ...

View Replies !   View Related
Skip Row - Script Transformation Editor
Howdy!

I am reading in a deliminated file. In the Script Transformation Editor, if the UPC does not past the checksum test, I want to throw the row out right then. I am not sure how to do that...but it is probably really simple.]
Thanks,
Linda



Here is my script:



' Microsoft SQL Server Integration Services user script component

' This is your new script component in Microsoft Visual Basic .NET

' ScriptMain is the entrypoint class for script components

'Option Strict Off

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper



Public Class ScriptMain

Inherits UserComponent

Private Function DoubleTest(ByVal Value As String) As Boolean

Dim d As Double

If Not Double.TryParse(Value, d) Then

'Windows.Forms.MessageBox.Show(Value + " is not numeric")

Return False

End If

If Double.IsNaN(d) Then

'Windows.Forms.MessageBox.Show(Value + " is NaN")

Return False

End If

'Windows.Forms.MessageBox.Show(Value + " = " + d.ToString())

Return True

End Function

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim d As Double

Dim CheckDigit As Integer = 0

Dim CheckOdd As Integer

Dim CheckEven As Integer

' Copy each source column to the destination column

Row.WholesalerCode = Trim(Row.WholesalerCode)

If Row.UPCNumber.Length = 12 Then

' 12 Digit Checksum

CheckOdd = Convert.ToInt16(Row.UPCNumber.Substring(0, 1), 10) + Convert.ToInt16(Row.UPCNumber.Substring(2, 1), 10) + Convert.ToInt16(Row.UPCNumber.Substring(4, 1), 10) + Convert.ToInt16(Row.UPCNumber.Substring(6, 1), 10) + Convert.ToInt16(Row.UPCNumber.Substring(8, 1), 10) + Convert.ToInt16(Row.UPCNumber.Substring(10, 1), 10) * 3

CheckEven = Convert.ToInt16(Row.UPCNumber.Substring(1, 1), 10) + Convert.ToInt16(Row.UPCNumber.Substring(3, 1), 10) + Convert.ToInt16(Row.UPCNumber.Substring(5, 1), 10) + Convert.ToInt16(Row.UPCNumber.Substring(7, 1), 10) + Convert.ToInt16(Row.UPCNumber.Substring(9, 1), 10)

If ((CheckOdd + CheckEven) + 1.0) / 10.0 = Round(((CheckOdd + CheckEven) + 1.0) / 10.0, 0) Then CheckDigit = 1

If ((CheckOdd + CheckEven) + 2.0) / 10.0 = Round(((CheckOdd + CheckEven) + 2.0) / 10.0, 0) Then CheckDigit = 2

If ((CheckOdd + CheckEven) + 3.0) / 10.0 = Round(((CheckOdd + CheckEven) + 3.0) / 10.0, 0) Then CheckDigit = 3

If ((CheckOdd + CheckEven) + 4.0) / 10.0 = Round(((CheckOdd + CheckEven) + 4.0) / 10.0, 0) Then CheckDigit = 4

If ((CheckOdd + CheckEven) + 5.0) / 10.0 = Round(((CheckOdd + CheckEven) + 5.0) / 10.0, 0) Then CheckDigit = 5

If ((CheckOdd + CheckEven) + 6.0) / 10.0 = Round(((CheckOdd + CheckEven) + 6.0) / 10.0, 0) Then CheckDigit = 6

If ((CheckOdd + CheckEven) + 7.0) / 10.0 = Round(((CheckOdd + CheckEven) + 7.0) / 10.0, 0) Then CheckDigit = 7

If ((CheckOdd + CheckEven) + 8.0) / 10.0 = Round(((CheckOdd + CheckEven) + 8.0) / 10.0, 0) Then CheckDigit = 8

If ((CheckOdd + CheckEven) + 9.0) / 10.0 = Round(((CheckOdd + CheckEven) + 9.0) / 10.0, 0) Then CheckDigit = 9

If CheckDigit = Convert.ToInt16(Row.UPCNumber.Substring(11, 1), 10) Then

Row.UPCNumber = String.Concat("00", Row.UPCNumber)

Else

'Throw out row because checksum did not match. <=== what do i do here???????????????

End If



ElseIf Row.UPCNumber.Length = 14 Then

' 14 Digit Checksum

Else

' Throw out row because checksum did not match. <=== what do i do here???????????????

End If

If Not DoubleTest(Row.RetailPrice) Then

Row.RetailPrice_IsNull = True

'Row.RetailPrice = String.Empty

End If

End Sub

End Class

View Replies !   View Related
How To Skip Records In Script Component
In DTS activex task, you can use skipinsert to skip a row, how to do that in script component?

View Replies !   View Related
Skip The Collation Of SQL In Reporting Services?
hi there,

i m using SQL server 2000, now i have a problem that i created a DB with 'traditional chinese' codepage, but user input the 'simplified chinese' data.

hence when i retrieve the data by means of the 2000 reporting service, those char turns to unreadable.

anyway i can skip the [collation], so that the data can be displayed properly?

tks in advance.

JL

View Replies !   View Related
How To Skip Rendering Blank Reports?
In our current application, we have many instances where users create ad-hoc requests. Alot of these requests result in blank reports (just due to the data requested).

Rather than returning blank reports, our application returns a message saying there's no report to show.

We have a solution in place, where the stored procedures used for the datasource contain this line -- IF @@ROWCOUNT=0 THEN RaiseError 'NoDataFound'. This error gets rethrown by SSRS and caught by the application which catches it and translates it to a user message.

My question is this:

1.) This way of doing it breaks most programming axioms about not using error handling for flow control. Also, when I look through the error logs they're full of errors from this, making it harder to find real errors.

2.) Is there a better way of doing this? I'd really like to move away from this way of doing this if I can show a better way.

Thanks!

View Replies !   View Related
Skip Alphanumeric Values (or Check If Value Is Numeric)
Is it possible to write a sql statement to skip aplpha numeric values? I got a field containing these values; 20, 70, 150, 140, 100, KORT, 90, 180, 160. And I'm trying to check if any value is bigger than 175 (@Limit), but I want to skip the value 'KORT'. So is it possible to check if a value is numeric or not? ISNULL( CONVERT(int, ProductVariant.Size), 0) > @Limit  Regards, Sigurd 

View Replies !   View Related
How To Skip Lines While Procedure Executing By Other User ?
Hi,
how to skip lines while procedure executing by other user ?

example

create sp_test
as
--#1
delete from x where a = 1
--#2
delete from x where a = 2
--#3
delete from x where a = 3

--#4
select * from x

user A executing sp_test
if user B executing sp_test at the same time run sp_test but skip #1; #3

Thanks

Alex

View Replies !   View Related
Load Data With BulkInsert In DTS, How To Skip The First Line?
I can skip the first line with Transform Data Task, it look like can not skip the first line in BulkInsert.
But bulkinsert is faster, anybody can help?

Thanks

View Replies !   View Related
How To Skip Applying The Current Remaining Logs?
Hi,

My replication application need to be able to skip the from-last-stop remaining logs (that means to skip the logs generated from the last stop time of my replication application), how can i realize it? thanks.



View Replies !   View Related
SKIP & NOSKIP Back No Differnce To Backup Duration
Hi

I run a full backup once a month and a transaction log backup every 10 mins through working hours for the remainder of the month until the backup is re-initialised at th start of the following month.

I backup to virtual sql disk object devices.

As the month progresses the backup takes longer and longer whilst the amount of data being backed up every 10 minutes cycle is always roughly the same. its about 5 seconds for a backup on day 1, up to 5 mins at the end of the month.

I noticed that NOSKIP was being used in the command - i changed this to SKIP, but it made no difference in the amount of time the backup takes. Isnt this suppoed to stop some sort of integrity scan on all other backup sets in the archive?

full syntax of the backup command that runs now is:

BACKUP LOG [objectstore] TO [Backup_objectstore] WITH NOFORMAT , NOINIT , NOUNLOAD , NAME = N'objectstore backup', SKIP , REWIND , STATS = 10

but as I say, the SKIP command instead of NOSKIP makes no difference.

whats going on here?

any help appreciated.

Thanks

Alastair.

"A computer once beat me at chess - but it was no match for me at kick boxing" - Emo Phillips.

View Replies !   View Related
Skip The First Line Of The Data File - Bulk Insert
Hi,
I have a data file and the contents of it are as follows

2 -- This is the header indicating the no of records in my files
1001|s1
1006|s2

The content of format file is as follows. This is to skip first column of the all the rows and get only Subs (i.e s1 and s2 )


9.0

2
1 SQLCHAR 0 100 "|" 0ID""

2 SQLCHAR 0 100 "
" 1Subs""


Here is my query to get all the Subs from my data file


SELECT * FROM OPENROWSET( BULK 'datafile.txt',

FORMATFILE = 'FormatFile.fmt',

FIRSTROW = 2 ) AS a

But this query retuns only s2 where i was expeting s1 and s2. The reason being is that the firts row i.e header doesn't follow the format
Can any one please let me know how to skip the first line in the data file and get the result as required

~Mohan

View Replies !   View Related
Is It Possible To Skip All Steps Following The Script Task Results (Step 1) In A For Each Container?
Is it possible to skip all steps following the script task results (Step 1) in a For Each container. I am iterating thru all the files in a For Each container and parsing a few lines of the file and based on the result I want to force the For Each loop to get to the next file instead of executing the next steps. Is it possible to force the for each loop to get the to the next file if the test criteria in the very first step (Script Task) fails. Any inputs will be much appreciated.



THanks,

MShah

View Replies !   View Related
How To Skip Snapshot In Transaction Replication (from SQL Server 2005 To 2000)
We have two SQL Server 2005 production DB at remote sites. Due to network bandwidth issue, we need to replicate these DBs (publishers and distributers) to central corporate SQL 2000 DB (subscriberfor backup and possible reporting (and in rare case as a failover server).

We would start out with backup from SQL 2000 db restored on remote SQL 2005 DBs. When we have DB issue on remote 2005 DB, we want to restore it from central corp. 2000 DB backup. Since two DBs are replicating to central DB, we DO NOT want combined db back up data on restored remote 2005 db. We can restore the db and delete unwanted data before we turn on replication from this restored server. So, this is not a problem.

The real problem is how to avoid snapshot replication (during initialization) when we create a transaction replication on this restored server to avoid over writing data on the central subcriber sql 2000 DB???

HELP!!





View Replies !   View Related
Skip Field Terminator While Inserting Data To A Table-Bulk Insert
Hi,
I have a data file which consists of data as below,
4
PPU_FFA7485E0D||
T_GLR_DET_11||

While iam inserting into table using bulkinsert,this pipe(||) is also getting inserted into the table,
here is my query iam using to insert the data using bulk insert.

BULK INSERT TABLE_NAME FROM FILE_PATH
WITH (FIELDTERMINATOR = ''||'''+',KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '''')




Can any one help on this.

Thanks,
-Badri

View Replies !   View Related
I'm Sure This Is An Easy One...Error Trap To Skip Over A &"bad&" Object.
Hello, I have the following code to iterate through each view in a SQLServer and call the "sp_refreshview" command against it. It worksgreat until it finds a view that is damaged, or otherwise cannot berefreshed. Then the whole routine stops working.Can someone please help me re-write this code so that any views thatfail the "sp_refreshview" command get skipped. I'm sure it's just amatter of putting some basic error trapping into the loop, but I've hada few goes at it and failed.Many thanks.DECLARE @DatabaseObject varchar(255)DECLARE ObjectCursor CURSORFOR SELECT table_name FROM information_schema.tables WHERE table_type ='view'OPEN ObjectCursorFETCH NEXT FROM ObjectCursor INTO @DatabaseObjectWHILE @@FETCH_STATUS = 0BEGINEXEC sp_refreshview @DatabaseObjectPrint @DatabaseObject + ' was successfully refreshed.'FETCH NEXT FROM ObjectCursor INTO @DatabaseObjectENDCLOSE ObjectCursorDEALLOCATE ObjectCursorGO

View Replies !   View Related
Regarding BCP Utility
Greetings,Just wanted to know if there is any parameter in BCPutility that can ignore triggers, indexes and constraint defined for atable?any help will be greatly appreciatedTIA

View Replies !   View Related
Bcp Utility
I'm using SQL 2005 to export data. I would like to use the bcp utilityto export data to an Excel file.I have to generate quite a few files and the names are dynamic. Theideal would be to loop through records in a stored procedure to createa file name to use in the bcp. My question is how can I use the bcpfrom a stored procedure? I know how to run it from the command prompt.Is there a way to control the command prompt from a stored procedure?Thanks all

View Replies !   View Related
BCP Utility
Hi all,

I want to use the BCP utility to import data from a .dat file into my database. The .dat file contains a table called xv_Appointments containing the following fields:

AppointmentKey
SurgerySlotKey
PatientKey
Cancelled
Continuation
Deleted
TimeArrived

I would like to import only two of these fields into a table called tbl_Appointments e.g.

AppointmentKey
TimeArrived

I can't seem to get the BCP util to do this. It only works if I import all of the fields from xv_Appointments. Does anyone know if this is possible?

Thanks

View Replies !   View Related
BCP Utility
In the process of exporting data from SQL data file to text file through BCP utility I am not getting the Column names.How can I get the column names through BCP utility?
I used this script
exec master..xp_cmdShell 'bcp "select * from regulator.dbo.TEMPTBLBRANCHNOTUPLOAD" QueryOUT \indiadbftprootCLIENT_BRANCH_UPLOADranchnotup loaded.csv -S indiadb -U sa -P sasocrates -k -r -c -t "," -q'

View Replies !   View Related
DTS Utility
Hello,
I am using the DTS utility available with Enterprise Manager in MSSQL server 2000. I can transfer the tables and views without any issues but when i try to transfer the stored procedures it always gives an error. I have tried transferring individual objects too but it does'nt works. The error given is "the user <username> cannot perform the following action". Any help is appreciated.
Thanks in Advance

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved