SSIS Versus Coding.
Jun 25, 2007
This question probably overlaps a few different topic areas.
As I will be required to work with both Oracle and SQL Server I will be in a difficult position with SSIS(due to it's change in distribution).
Therefore I am having to look at alternatives.
With coding a can open a text file and parse it reasonably to my satisfaction. However getting the data into the database is incredibly slow.
I am using an Insert into for each line, which I am sure everone will shake their head over. This seems to be pretty slow even using transactions.
Is there any scope in using data tables or have the read on one thread and write on another.
Other than that is there an Oracle equivalent of SSIS which comes (probably get shot for asking that on a microsoft web site, but would probably get shot if I asked on Oracle forums as well).
In the past we had reasonable results in outputting to csv and then doing some sort of bulk insert, messy and irritating though this may be.
Any ideas on this area will be gratefully accepted.
View 2 Replies
ADVERTISEMENT
Jun 20, 2007
Hello,
I wonder if anyone else out there has the same impression that I have: I find that DTS works much better than SSIS.
I find that DTS is so easy to use and reliable: it gets the job done and fast! On the other hand, SSIS seems to be so needlessly complex that it takes hours of troubleshooting just to get it to work, and sometimes it doesn't work at all. For example I have just spent hours trying to get SSIS to import a flat file with 300,000 rows. It just crashes and doesn't even give an error message so that one can fix it. On the other hand I have just now successfully accomplished the same task with DTS and it took me 5 minutes!
I honestly don't see a valid reason for using SQL Server 2005 instead of 2000. So far it's much more productive to use 2000.
I hope Microsoft can clarify this issue.
Regards,
Jerome Smith
View 8 Replies
View Related
Apr 25, 2008
Hello
I've been given the task of migrating a DTS package to SSIS (neither of which I am particularly familar with). The first job in the DTS package is to read a .ini file and set a bunch of variables. These variables are then used throughout the DTS package. After running the DTS package through the SSIS migration wizard this job turns into an execute script task and I can't see if it is still reading the .ini file. However, the only real purpose of this step is to allow different parameters to be passed in development, test, production etc. So I am thinking this whole step can be removed and effectively replaced with a package configuration (I'll probably use an XML file). My understanding is that by selecting the name/value pairs as appropriate in the XML package configuration file means this values will be passed in at runtime and achieve the same functionality. Is this the correct way to do this in SSIS, or do I still need the .ini file and variables?
Thanks for any advice on this issue.
Regards, John
View 7 Replies
View Related
May 8, 2015
I have a stored proc that is returning the results I need for output to .txt file.
Is there a way in SSIS to commit 50K (or whatever number) row batches at a time or should I just handle this in the stored proc?
select * into #TempTable
from SomeTable
[WHILE LOOP] --throttle commit batches of 50K rowcount
select *
from #TempTable
[END LOOP]
drop table #TempTable
But If I'm doing this in SSIS, I can't drop the #temp table otherwise I have nothing to output right?
View 6 Replies
View Related
Jun 23, 2006
Dim objConn As New SqlConnection Dim objCmd As New SqlCommand Dim Value As String = EventCmb.SelectedItem.ToString() objConn.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename='C:Documents and SettingsHPMy DocumentsVisual Studio 2005WebSitesFYP2App_DataEvent.mdf';Integrated Security=True;Connect Timeout=30;User Instance=True" Try objConn.Open() objCmd.Connection = objConn objCmd.CommandType = CommandType.Text objCmd.CommandText = "SELECT EventTel FROM Event WHERE (EventID = @Value)" ' See how i changed Value to @Value. This is called a Named Parameter objCmd.Parameters.AddWithValue("@Value", Value) ' Add the @value withthe actual value that should be put. This makes it securer Dim RetVal As Object = objCmd.ExecuteScalar() ' This returns the First Column of the first row regardless of how much data is returned. If Not ((RetVal Is Nothing) Or (RetVal Is DBNull.Value)) Then ContactLbl.Text = RetVal.ToString() Else ' noting was returned End If Catch ex As Exception Throw ex Finally objConn.Close() End Try
There's an error for in line "Dim RetVal As Object = objCmd.ExecuteScalar() "
Error Message is as follow "Conversion failed when converting the nvarchar value 'LTA' to data type int."
It is due to "ExecuteScalar() " can only store int? I just need to display one data, and Value data comes form a combo box "EventCmb", which i wanted to find the selected String, compared to the "Event" database to get the "EventTel" data How do i solved this problem, any advice? Thanks!
View 1 Replies
View Related
Jul 2, 2002
I'm attempting to copy data from an Epicore server with multiple company
databases to another server. All this data will be placed in a single
table on the receiving server with the addition of an database identifier,
so that the receiving department will know, to which company the data
belongs. I have code that will dynamically create the SQL and then
execute it using EXEC (@SQL), and this works just fine acrosss multiple
databases. I've read in my Transact-SQL Programming book that using
EXEC() is not limited to 255 characters, but it still appears that my
dynamically created SQL is being shortened. The insert statement and the
select statement list each column, and here is that code:
SELECT @CMD = "INSERT ##APMaster(Company, timestamp, vendor_code,
pay_to_code, address_name,
short_name, addr1, addr2, addr3, addr4, addr5, addr6, addr_sort1,
addr_sort2, addr_sort3, address_type,
status_type, attention_name, attention_phone, contact_name, contact_phone,
tlx_twx, phone_1, phone_2,
tax_code, terms_code, fob_code, posting_code, location_code,
orig_zone_code, customer_code,
affiliated_vend_code, alt_vendor_code, comment_code, vend_class_code,
branch_code, pay_to_hist_flag,
item_hist_flag, credit_limit_flag, credit_limit, aging_limit_flag,
aging_limit, restock_chg_flag,
restock_chg, prc_flag, vend_acct, tax_id_num, flag_1099, exp_acct_code,
amt_max_check, lead_time,
doc_ctrl_num, one_check_flag, dup_voucher_flag, dup_amt_flag, code_1099,
user_trx_type_code, payment_code,
limit_by_home, rate_type_home, rate_type_oper, nat_cur_code,
one_cur_vendor, cash_acct_code, city, state,
postal_code, country, freight_code, url, note) SELECT '" + @DBName + "' AS
Company, timestamp, vendor_code,
pay_to_code, address_name, short_name addr1, addr2, addr3, addr4, addr5,
addr6,
addr_sort1, addr_sort2, addr_sort3, address_type, status_type,
attention_name, attention_phone, contact_name,
contact_phone, tlx_twx, phone_1, phone_2, tax_code, terms_code, fob_code,
posting_code, location_code,
orig_zone_code, customer_code, affiliated_vend_code, alt_vendor_code,
comment_code, vend_class_code,
branch_code, pay_to_hist_flag, item_hist_flag, credit_limit_flag,
credit_limit, aging_limit_flag,
aging_limit, restock_chg_flag, restock_chg, prc_flag, vend_acct,
tax_id_num, flag_1099, exp_acct_code,
amt_max_check, lead_time, doc_ctrl_num, one_check_flag, dup_voucher_flag,
dup_amt_flag, code_1099,
user_trx_type_code, payment_code, limit_by_home, rate_type_home,
rate_type_oper, nat_cur_code, one_cur_vendor,
cash_acct_code, city, state, postal_code, country, freight_code, url, note
FROM " + @DBName + "..APMaster"
Can anyone provide any insight to my problem?
TIA,
Chris
View 4 Replies
View Related
Oct 31, 2001
Hello,
I have the following code and it seems that it is not comparing the request_close_date. What I want to do is compare the request_close_date between two tables and if it is less than the date from the TEAM3B_PULL_TOTAL_TST then insert the data into RDD_UPDATE_TST. Any help will be appreciated. It also needs to insert data into RDD_UPDATE_TST if a request exists in REQUEST_BUS_REQ and BUS_REQ_DESCRIPTION. I think that I have that coded correctly but I am not certain.
Thanks in advance,
Anne
begin
Insert into RDD_UPDATE_TST(request,business_req_id,test_case_i d,test_case_descr,request_close_date)
select distinct request,business_req_id,test_case_id,test_case as test_case_descr,request_close_date
from TEAM3B_PULL_TOTAL_TST A
where not exists (select * from BUS_REQ_DESCRIPTION_TST B where A.REQUEST = B.REQUEST)
and not exists (select * from REQUEST_BUS_REQ_TST C where A.REQUEST = C.REQUEST)
and not exists (select * from RDD_UPDATE_TST D where A.REQUEST = D.REQUEST)
and exists (select * from RDD_UPDATE_TST where REQUEST_CLOSE_DATE <>B.REQUEST_CLOSE_DATE)
end
View 1 Replies
View Related
Apr 28, 2003
Hi im an newbee so im not the best but try to get there some day..
CREATE TABLE table_members
(id int(11) NOT NULL auto_increment,
fornamn char(60) default NULL,
efternamn char(60) default NULL,
address char(60) default NULL,
personnr INT(11) default 0,
smeknamn char(60) default NULL,
epost char(60) default NULL, i must be a @ value in it! any ides?
kon ENUM('M','K','V') default 'M', is this right, must only be enable to type in this Alphabets sings MKV!
losenord char(60) default NULL, verlosenord char(60) default NULL,
How do i get a or fix a retype field for this losenord and verlosenord thay must be the same value to be insered in to the sql!
PRIMARY KEY (id)
UNIQUE ('smeknamn');
Any ides are good
View 1 Replies
View Related
Mar 12, 2007
Help me...
I need C# Language ASP.Net coding...
i've two column... One for msg another for msgLastposted
in label control view msg(last visitor)....... that msg stored in my db.
Example Table Data
---------------------------------------------
MSg|MsgLastPosed
How edit forum |2007-03-12 10:50:25.747
How to Value Change|2007-03-12 10:56:36.373
Sql Command|2007-03-12 11:00:25.047
User Control|2007-03-12 11:02:10.793
How I can uninstall|2007-03-12 13:07:51.233
-----------------------------------------------
In table have many record..
label control display msg based on last visitor time.. after that again msg
changed( based on next last visitor time).. Continue for upto first 5 record msg automatically changed..
View 1 Replies
View Related
Aug 28, 2007
Hi all,
I want codeing for to find (subtotal & total)the following
Input
productname qty_A_grade Qty_B_grade
abc 10 5
def 5 5
subtotal
gh 10 10
ab 10 10
Total
output
productname qty_A_grade Qty_B_grade
abc 10 5
def 5 5
subtotal 15 10
gh 10 10
ab 10 10
Total 20 20
View 3 Replies
View Related
Jul 25, 2006
:This is a segement of VBscript for checking lastest Backup in SQLServer 2000:
'temporary variables to useDim sTemp1Dim i, sTemp2
'read start arguments' 1 => servername' 2 => path for logfile' . => excluded databasesDim objArgsSet objArgs = WScript.ArgumentsIf objArgs.Count < 2 Then 'Not enough parameters WScript.Quit 2 end If
If the above VBScript is converted to VB.NET, how can the lines containing a boldfaced WScript be coded? What namespace in .NET should be used for that? Thanks?
View 3 Replies
View Related
Jun 14, 2007
hi im a little bit confused. are the two pieces of code similar? what are the differences. i really need to know that coz i wont get access to a SQL machine until monday.
selectlastname
fromemp
wheresex = 'F' and
salary>(selectavg(salary)
fromemp
group by sex
havingsex='M')
selectlastname
fromemp
wheresex = 'F' and
salary>(selectavg(salary)
fromemp
wheresex='M')
also is it wise to use Group by and having in sub-queries?
View 2 Replies
View Related
Jul 20, 2005
Hi;Is there an **easy** way to tell tsql apart from standard sql?Will sqlserver run scripts written only in standard sql?What about variable definitions?Thanks in advanceSteve
View 8 Replies
View Related
May 16, 2007
I am attempting to build a query that will allow me to fetch data from a table for only the last seven days. This will be used for a daily report that will need to update the day to current date every time it is run. Any help would be greatly appreciated.
Thanks
Andrew
View 2 Replies
View Related
Nov 2, 2004
Hello,
I'm coding a stored procedure. I am creating a search function where I search multiple resultsets of data. I'm getting stuck on one part of the query, and I don't know what the best option is. Basically the query looks like this:
select * from Table where ...
and Official in (... problem area ...)
What I want is if the parameter passed in is null, return all of the valid values from the table that references this field (for example, if null is passed in, I want to pass in 'T', 'F', etc.). If the value is there, I want to pass in the value. I've been stuck on this and can't figure it out.
Any ideas? I don't want to use a dynamic query. Thanks,
Brian
View 5 Replies
View Related
May 5, 2006
Hey it's not often the blindman asks for advice on sql coding (never, I think), so here is an opportunity to solve a problem I've been knocking my head against for two days.
Here is sample code for setting up the problem:create table #blindman
(pkey smallint primary key,
fkey char(1),
updateddatetime)
insert into #blindman (pkey, fkey, updated)
select1, 'A', '1/1/2006'
UNION
select2, 'B', '1/1/2006'
UNION
select3, 'A', '1/2/2006'
UNION
select4, 'B', '1/2/2006'
UNION
select5, 'A', '1/4/2006'
UNION
select6, 'B', '1/2/2006'
UNION
select7, 'A', '1/3/2006'
UNION
select8, 'B', '1/3/2006'
UNION
select9, 'A', '1/5/2006'
UNION
select10, 'B', '1/5/2006'
drop table #blindman
Notice that for fkey 'B', there are two entries with '1/2/2006', and for fkey 'A' the updated values are not in synch with the order of the primary key.
The challenge: determine the next pkey for each pkey value, ordered by [updated], and using pkey as a tie-breaker when two records have the same [updated] value.
Here is the desired output for the sample data:pkey fkey updated nextpkey
------ ---- ---------- --------
1 A 2006-01-01 3
3 A 2006-01-02 7
7 A 2006-01-03 5
5 A 2006-01-04 9
2 B 2006-01-01 4
4 B 2006-01-02 6
6 B 2006-01-02 8
8 B 2006-01-03 10
Records 9 and 10 are missing because they have not succeeding records, though I'd be just has satisfied to include them with NULL as their nextpkey value.
Ideally, I want this as a VIEW.
Who's up for the challenge?
View 14 Replies
View Related
Oct 10, 2006
ok this is for a class assignment so if anyone doesnt want to help thats cool, but its just a small extra credit assignment and we havent gone over it in class and and the book, well is confusing to say the least so here are my questions:
What is the total weight of different colored parts?
ok so i have a parts table, with pcolor and pweight in it but i am unsure how to code that, here is kinda what i think it might be, i have no real way to check to see if it is correct though:
Select sum pweight
from parts
where distinct pcolor
looks horribly wrong so any help is appreciated. here is another one:
What colored part has total weight greater than 8 units?
select pcolor
from parts
where sum pweight > 8
???
i dunno lol.
there is a hint that says to use "GROUP BY" and "HAVING" but i dont see how that fits in... any ideas?
View 2 Replies
View Related
Nov 14, 2006
Hello
Is it possible to grant permissions like SELECT,INSERT,DELETE permissions to a database in SQL Server 2005 as we give it through SQL Server Management Studio.
Is it possible to grant permissions without specifying the username,passwd
Thanks In Advance
Poornima
View 16 Replies
View Related
Jun 22, 2007
I am new to MS SQL coding and I am having a problem with date conversions. In PL/SQL, I could convert numeric months into character months in their own columns by using the DECODE function. An example would be:
DECODE(to_char(M.My_Table, 'MM'),
'04', 'Apr', '05', 'May', '06', 'Jun', '07',
'Jul', '08', 'Aug', '09', 'Sep', '10', 'Oct',
'11', 'Nov', '12', 'Dec', '01', 'Jan', '02',
'Feb', '03', 'Mar'),
DECODE(to_char(M.My_Table, 'MM'),
'04', 'Q1', '05', 'Q1', '06', 'Q1',
'07', 'Q2', '08', 'Q2', '09', 'Q2',
'10', 'Q3', '11', 'Q3', '12', 'Q3',
'01', 'Q4', '02', 'Q4', '03', 'Q4'),
How does one convert something like this in MS SQL?
View 15 Replies
View Related
Jun 11, 2007
Hi!If you like to spend some moments on my code examples,please look at it - usage is free (-:http://www.codeproject.com/useritems/TSQL_coding.aspFeedback welcome.GreetingsBjorn
View 1 Replies
View Related
Nov 1, 2007
Hi. I'm looking at a problem and I can't find any solution short ofcoding.I have a pool of, say, 1000 PINS. I have 7 tables (buckets). Any ofthe 1000 PINS can be in 0, 1, 2, or... or all 7 buckets. So let's saythat-bucket A has 100 PINS-bucket B has 300 PINS-bucket C has 600 PINS-bucket D has 200 PINS-bucket E has 500 PINS-bucket F has 350 PINS-bucket G has 700 PINSI need to know, for each PIN, the number of buckets (tables) itbelongs to, and which ones, i.e:- PIN 1 belongs to A, C, D, so it belongs to 3 buckets- PIN 2 belongs to A, C, D, F, so it belongs to 4 buckets- PIN 3 belongs to A, so it belongs to 1 bucket- PIN 4 belongs to A, B, C, D, G, so it belongs to 5 buckets- PIN 5 belongs to ..., so it belongs to 0 bucketsetc, etcWhat would be the simplest way to achieve that, please ?Thank you very muchAlex.
View 5 Replies
View Related
Mar 26, 2008
Hi List,I am searching franticly for a solution (or the procedure) to settingthe coding of a new DB to UTF-8. I can find no setting in the ServerManager, during creation of the DB, to influence this. Can someoneplease show me the way? Thanks--Shawn
View 3 Replies
View Related
May 25, 2007
I need just testing function that retreive image from SQL SERVER 2005 with data type "image" so i need to know another way to insert image into SQL SERVER2005 without coding. Because In MICROSOFT ACCESS, I just copy image and then, paste into column.
View 3 Replies
View Related
May 25, 2007
I need just testing function that retreive image from SQL SERVER 2005 with data type "image" so i need to know another way to insert image into SQL SERVER2005. Because In MICROSOFT ACCESS, I just copy image and then, paste into column.
View 1 Replies
View Related
May 9, 2007
GLOBAL STRING Line1_BatchID
GLOBAL STRING Line1_CompletedDate
GLOBAL INT Line1_CompletedQty
GLOBAL STRING Line1_CompletedTime
GLOBAL STRING Line1_ExpiryDate
GLOBAL INT Line1_Qty
GLOBAL STRING Line1_OrderID
GLOBAL INT Line1_BatchID_ID
GLOBAL INT Line1_CompletedDate_ID
GLOBAL INT Line1_CompletedQty_ID
GLOBAL INT Line1_CompletedTime_ID
GLOBAL INT Line1_ExpiryDate_ID
GLOBAL INT Line1_OrderStatus_ID
GLOBAL STRING Line2_BatchID
GLOBAL STRING Line2_CompletedDate
GLOBAL INT Line2_CompletedQty
GLOBAL STRING Line2_CompletedTime
GLOBAL STRING Line2_ExpiryDate
GLOBAL STRING Line2_OrderStatus
GLOBAL INT Line2_Qty
GLOBAL STRING Line2_OrderID
GLOBAL INT Line2_BatchID_ID
GLOBAL INT Line2_CompletedDate_ID
GLOBAL INT Line2_CompletedQty_ID
GLOBAL INT Line2_CompletedTime_ID
GLOBAL INT Line2_ExpiryDate_ID
GLOBAL INT Line2_OrderStatus_ID
GLOBAL STRING Line3_BatchID
GLOBAL STRING Line3_CompletedDate
GLOBAL INT Line3_CompletedQty
GLOBAL STRING Line3_CompletedTime
GLOBAL STRING Line3_ExpiryDate
GLOBAL STRING Line3_OrderStatus
GLOBAL INT Line3_Qty
GLOBAL STRING Line3_OrderID
GLOBAL INT Line3_BatchID_ID
GLOBAL INT Line3_CompletedDate_ID
GLOBAL INT Line3_CompletedQty_ID
GLOBAL INT Line3_CompletedTime_ID
GLOBAL INT Line3_ExpiryDate_ID
GLOBAL INT Line3_OrderStatus_ID
GLOBAL STRING result
GLOBAL STRING result2
FUNCTION getOrderDetail(STRING Product_ID)
INT counter = 0
INT statusSQL, sqlResult;
STRING Sql1
STRING Sql2
STRING Sql3
result = ""
statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success
Sql1 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'productid') AND (DataValue = '" + Product_ID + "')"
sqlResult = SQLExec(statusSQL, Sql1);
IF sqlResult = 0 THEN //If SQL Success
WHILE SQLNext(statusSQL) = 0 DO
IF result <> "" THEN
result = result + ","
END
result = result + SQLGetField(statusSQL, "SetId")
END
END
Sql2 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'order status') AND (DataValue = 'pending') AND (SetId IN (" + result + ")) Order by SetID"
sqlResult = SQLExec(statusSQL, Sql2);
IF sqlResult = 0 THEN //If SQL Success
result = ""
IF SQLNext(statusSQL) = 0 THEN
//Select first SetId
result = SQLGetField(statusSQL, "SetId")
END
END
Sql3 = "SELECT Id, DataValue FROM ProductionDataField WHERE (SetId IN (" + result + ")) and (IsActive = 1) Order by field"
sqlResult = SQLExec(statusSQL, Sql3);
IF sqlResult = 0 THEN //If SQL Success
WHILE SQLNext(statusSQL) = 0 DO
IF Product_ID = "Biscuit" THEN
IF counter < 9 THEN
IF counter = 0 THEN
Line1_BatchID_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 1 THEN
Line1_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line1_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line1_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line1_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line1_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line1_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line1_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN
END
IF counter = 8 THEN
Line1_Qty = SQLGetField(statusSQL , "DataValue")
END
END
END
IF Product_ID = "ChocolateBiscuit" THEN
IF counter = 0 THEN
Line2_BatchID_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 1 THEN
Line2_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line2_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line2_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line2_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line2_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line2_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line2_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN
END
IF counter = 8 THEN
Line2_Qty = SQLGetField(statusSQL , "DataValue")
END
END
IF Product_ID = "PeanutButterBiscuit" THEN
IF counter = 0 THEN
Line3_BatchID_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 1 THEN
Line3_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line3_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line3_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line3_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line3_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line3_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line3_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN
END
IF counter = 8 THEN
Line3_Qty = SQLGetField(statusSQL , "DataValue")
END
END
counter = counter + 1
END
END
END
SQLEnd(statusSQL)
SQLDisconnect("DSN=SQLSRV_TBLS")
END
FUNCTION UpdateBatchID(STRING Product_ID)
INT statusSQL, sqlResult;
STRING Sql1
statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success
IF Product_ID = "Biscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line1_BatchID + "' WHERE [Id] = " + IntToStr(Line1_BatchID_ID)
END
IF Product_ID = "ChocolateBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line2_BatchID + "' WHERE [Id] = " + IntToStr(Line2_BatchID_ID)
END
IF Product_ID = "PeanutButterBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line3_BatchID + "' WHERE [Id] = " + IntToStr(Line3_BatchID_ID)
END
sqlResult = SQLExec(statusSQL, Sql1);
END
SQLEnd(statusSQL)
SQLDisconnect("DSN=SQLSRV_TBLS")
END
FUNCTION UpdateCompletedQty(STRING Product_ID)
INT statusSQL, sqlResult;
STRING Sql1
statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success
IF Product_ID = "Biscuit" THEN
Sql1 = "UPDATE ProductionDataField SET DataValue = '" + IntToStr(Line1_CompletedQty) + "' WHERE [Id] = " + IntToStr(Line1_CompletedQty_ID)
END
IF Product_ID = "ChocolateBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET DataValue = '" + IntToStr(Line2_CompletedQty) + "' WHERE [Id] = " + IntToStr(Line2_CompletedQty_ID)
END
IF Product_ID = "PeanutButterBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET DataValue = '" + IntToStr(Line3_CompletedQty) + "' WHERE [Id] = " + IntToStr(Line3_CompletedQty_ID)
END
sqlResult = SQLExec(statusSQL, Sql1);
END
SQLEnd(statusSQL)
SQLDisconnect("DSN=SQLSRV_TBLS")
END
FUNCTION UpdateCompletedOrder(STRING Product_ID)
INT statusSQL, sqlResult;
STRING Sql1
statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success
IF Product_ID = "Biscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line1_CompletedDate + "' WHERE [Id] = " + IntToStr(Line1_CompletedDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line1_CompletedTime + "' WHERE [Id] = " + IntToStr(Line1_CompletedTime_ID)
sqlResult = SQLExec(statusSQL, Sql1);
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line1_ExpiryDate + "' WHERE [Id] = " + IntToStr(Line1_ExpiryDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);
Sql1 = "UPDATE ProductionDataField SET [DataValue] = 'Completed' WHERE [Id] = " + IntToStr(Line1_OrderStatus_ID)
sqlResult = SQLExec(statusSQL, Sql1);
END
IF Product_ID = "ChocolateBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line2_CompletedDate + "' WHERE [Id] = " + IntToStr(Line2_CompletedDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line2_CompletedTime + "' WHERE [Id] = " + IntToStr(Line2_CompletedTime_ID)
sqlResult = SQLExec(statusSQL, Sql1);
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line2_ExpiryDate + "' WHERE [Id] = " + IntToStr(Line2_ExpiryDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);
Sql1 = "UPDATE ProductionDataField SET [DataValue] = 'Completed' WHERE [Id] = " + IntToStr(Line2_OrderStatus_ID)
sqlResult = SQLExec(statusSQL, Sql1);
END
IF Product_ID = "PeanutButterBiscuit" THEN
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line3_CompletedDate + "' WHERE [Id] = " + IntToStr(Line3_CompletedDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line3_CompletedTime + "' WHERE [Id] = " + IntToStr(Line3_CompletedTime_ID)
sqlResult = SQLExec(statusSQL, Sql1);
Sql1 = "UPDATE ProductionDataField SET [DataValue] = '" + Line3_ExpiryDate + "' WHERE [Id] = " + IntToStr(Line3_ExpiryDate_ID)
sqlResult = SQLExec(statusSQL, Sql1);
Sql1 = "UPDATE ProductionDataField SET [DataValue] = 'Completed' WHERE [Id] = " + IntToStr(Line3_OrderStatus_ID)
sqlResult = SQLExec(statusSQL, Sql1);
END
END
SQLEnd(statusSQL)
SQLDisconnect("DSN=SQLSRV_TBLS")
END
INT FUNCTION getLine1_Qty()
RETURN Line1_Qty
END
INT FUNCTION getLine1_CompletedQty()
RETURN Line1_CompletedQty
END
STRING FUNCTION getLine1_OrderID()
RETURN Line1_OrderID
END
STRING FUNCTION getLine1_BatchID()
RETURN Line1_BatchID
END
INT FUNCTION getLine2_Qty()
RETURN Line2_Qty
END
INT FUNCTION getLine2_CompletedQty()
RETURN Line2_CompletedQty
END
STRING FUNCTION getLine2_OrderID()
RETURN Line2_OrderID
END
STRING FUNCTION getLine2_BatchID()
RETURN Line2_BatchID
END
INT FUNCTION getLine3_Qty()
RETURN Line3_Qty
END
INT FUNCTION getLine3_CompletedQty()
RETURN Line3_CompletedQty
END
STRING FUNCTION getLine3_OrderID()
RETURN Line3_OrderID
END
STRING FUNCTION getLine3_BatchID()
RETURN Line3_BatchID
END
FUNCTION dbTest()
INT statusSQL, sqlResult;
INT counter = 0
STRING Sql1
STRING Sql2
STRING Sql3
STRING Product_ID = "Biscuit"
result = ""
statusSQL = SQLConnect("DSN=SQLSRV_TBLS;SRVR=localhost;DB=FYPJ Integration of SAP NetweaverData;UID=labuser;PWD=success;");
IF statusSQL <> -1 THEN //If Connection Success
Sql1 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'productid') AND (DataValue = '" + Product_ID + "')"
sqlResult = SQLExec(statusSQL, Sql1);
IF sqlResult = 0 THEN //If SQL Success
WHILE SQLNext(statusSQL) = 0 DO
IF result <> "" THEN
result = result + ","
END
result = result + SQLGetField(statusSQL, "SetId")
END
END
Sql2 = "SELECT SetId FROM ProductionDataField WHERE (Field = 'order status') AND (DataValue = 'pending') AND (SetId IN (" + result + "))"
sqlResult = SQLExec(statusSQL, Sql2);
IF sqlResult = 0 THEN //If SQL Success
result = ""
IF SQLNext(statusSQL) = 0 THEN
result = SQLGetField(statusSQL, "SetId")
END
END
Sql3 = "SELECT Id, DataValue FROM ProductionDataField WHERE (SetId IN (" + result + ")) and (IsActive = 1) Order by field"
sqlResult = SQLExec(statusSQL, Sql3);
IF sqlResult = 0 THEN //If SQL Success
WHILE SQLNext(statusSQL) = 0 DO
IF Product_ID = "Biscuit" THEN
IF counter = 0 THEN
Line1_BatchID_ID = SQLGetField(statusSQL , "ID")
Line1_BatchID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 1 THEN
Line1_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line1_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line1_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line1_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line1_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line1_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line1_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN
END
IF counter = 8 THEN
Line1_Qty = SQLGetField(statusSQL , "DataValue")
END
END
IF Product_ID = "ChocolateBiscuit" THEN
IF counter = 0 THEN
Line2_BatchID_ID = SQLGetField(statusSQL , "ID")
Line2_BatchID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 1 THEN
Line2_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line2_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line2_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line2_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line2_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line2_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line2_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN
END
IF counter = 8 THEN
Line2_Qty = SQLGetField(statusSQL , "DataValue")
END
END
IF Product_ID = "PeanutButterBiscuit" THEN
IF counter = 0 THEN
Line3_BatchID_ID = SQLGetField(statusSQL , "ID")
Line3_BatchID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 1 THEN
Line3_CompletedDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 2 THEN
Line3_CompletedQty_ID = SQLGetField(statusSQL , "ID")
Line3_CompletedQty = SQLGetField(statusSQL , "DataValue")
END
IF counter = 3 THEN
Line3_CompletedTime_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 4 THEN
Line3_ExpiryDate_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 5 THEN
Line3_OrderStatus_ID = SQLGetField(statusSQL , "ID")
END
IF counter = 6 THEN
Line3_OrderID = SQLGetField(statusSQL , "DataValue")
END
IF counter = 7 THEN
END
IF counter = 8 THEN
Line3_Qty = SQLGetField(statusSQL , "DataValue")
END
END
counter = counter + 1
END
END
END
SQLEnd(statusSQL)
SQLDisconnect("DSN=SQLSRV_TBLS")
END
STRING FUNCTION getresult()
RETURN result
END
STRING FUNCTION getresult2()
RETURN result2
END
View 2 Replies
View Related
Aug 24, 2007
I have a table where I am needing to concatenate several columns. Some of the columns might have NULL values, so I need to concatenate only those fields where there is data and not concatenate the NULLs.
For Example:
My columns are ID, Effdt, keyword01, keyword02, keyword03, keyword04, keyword05, keyword06, keyword07, etc.
I need to concatenate all of the keyword columns into one field (keyword01 + '/' + keyword02 + '/' +keyword03 + '/' +...). However, keyword06 and on might be NULL (there is never an instance where, say, keyword02 has data, keyword03 is NULL, and keyword04 has data. If a keyword field, greater than keyword01 (it always has data in it), has data in it... then every keyword field before it does also.)
TABLE:
ID EffDT Keyword01 Keyword02 Keyword03 Keyword04 Keyword05
110 8/2/2005 12345 67890 NULL NULL NULL
111 8/2/2005 ABCDE FGHIJ KLMNO NULL NULL
112 8/15/2005 ABCDE FGHIJ NULL NULL NULL
113 8/15/2005 12345 67890 12345 67890 12345
Result:
ID EffDT Keyword
110 8/2/2005 12345 / 67890
111 8/2/2005 ABCDE / FGHIJ / KLMNO
112 8/15/2005 ABCDE / FGHIJ
113 8/15/2005 12345 / 67890 / 12345 / 67890 / 12345
How do I get the concantenation to go without the NULLs, without getting "12345 / 67890 / / / / / / "?
View 5 Replies
View Related
Oct 31, 2007
Dear all,
Can anybody provide sample t-sql procedures coding to do the following tasks?
I want to call a sql stored procedures with parameter passing. I write down the pseudo codes as follows:
In the SQL server side, write a stored procedure which performs the following:
With input parameter "ABCDE";
execute SQL statement "select * from table A where tableA.field B = "ABCDE";
For each record from the result of the above SQL statement,
"select * from tableB where tableB.field C=tableA.fieldX"
if the record can be found in tableB,
"update tableB set tableB.fieldD=something";
if the record cannot be found in tableB,
"insert a new record in tableB"
My actual application requirement is that I have an input file of over hundred thousand records which acts as a primary file to update or to add records to another file. My client side application, which is VB.NET, call a sql procedures once to perform this task. I don't want the VB.NET program to loop through all the input records and call a sql procedure a hundred thousand times!
thanks in advance!
View 8 Replies
View Related
Nov 3, 2007
Hi everyone!
I'm currently working on an application that needs to store address information.
I figured a database of some sort would be the perfect thing to use.
The thing is:
The application will work locally, there will be no server running on a network
There shouldn't be any login and password protection for the db
When I later on install the application on a computer, a fresh and clean db should be created within the applications folde
How can I create and access a db like that using c# and MS SQL Server?
Any hints, tips, example code, links...anything?
Many thanks in advance!!!
PS. I've been working some with visual c# express and recently installed SQL Server & Manager. I've previusly made some php web pages using MySQL but I´m a total noob when it comes to working with SQL and c# (= I'm stuck!).
View 5 Replies
View Related
Sep 22, 2006
Hi guys,
It's been awhile since I have posted. I have a situation for the group here. I am new to working with cursors. I have a simple one here that I wish to use to update NULL fields in a table called rpt_Scr_B0000_MiniFinancials. I know for a fact that there are NULLs in this table. When I run the select query from the information_schema I get some 60 some odd fields. Anyway, when I run this I get 0 records affected which I know is incorrect. It appears that my cursor is only processing for the first field. I tried changing the @@FETCH_STATUS = 0 to @@FETCH_STATUS > 0 and that didn't work either. What am I doing wrong? Thx.
DECLARE @FieldName char (25)
DECLARE cursor_update_rpt_Scr_B0000_MiniFinancials CURSOR For
select column_name
from information_schema.columns
where table_name = 'rpt_Scr_B0000_MiniFinancials'
open cursor_update_rpt_Scr_B0000_MiniFinancials
FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @FieldName
update rpt_Scr_B0000_MiniFinancials
set @FieldName = 0
where @FieldName is null
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM cursor_update_rpt_Scr_B0000_MiniFinancials
INTO @FieldName
END
CLOSE cursor_update_rpt_Scr_B0000_MiniFinancials
DEALLOCATE cursor_update_rpt_Scr_B0000_MiniFinancials
View 8 Replies
View Related
Jan 4, 2012
Is there a Microsoft published document for SQL database coding standards and guidelines/T-SQL coding standards available??
View 7 Replies
View Related
Apr 17, 2008
This should be terribly simply, but I just can't seem to figure out where I'm going wrong. I have a table that logs usage of a few software licenses in my division, so it has columns for the username, the license, and the date/time checked out and date/time checked in. I have written a quick SQL query that totals how much time all the users have spent in three of the licenses during this work week. It works fine. But now I just want to limit that query to those users who have used those licenses for more than two hours.
Sounds simple enough, but I must be overlooking something obvious. I have tried [Minutes]>120 in both a WHERE and a HAVING statement, but neither worked. I have also tried changing the name of the column to something other than Minutes in the off case that there might be some kind of keyword confusion, but it didn't work. I have also tried various other unlikely fixes but continually get the error "Invalid Column Name 'Minutes'".
Any help would be very appreciated since this is driving me nuts. The code that works is below.
Declare @datMin datetime
Declare @datMax datetime
set @datMin=DateAdd(mi,
-(DatePart(hh,Getdate())*60 + DatePart(mi,GetDate())),--Gets the current minute of the day
DateAdd(dd,-datepart(dw,GetDate())+1,GetDate()))--Gets First Day of the Week at this time
set @datMax=DateAdd(mi,
1439-(DatePart(hh,Getdate())*60 + DatePart(mi,GetDate())),
GetDate())
Select UserName, Sum(Case
When DateOut<@datMin and DateIn<@datMax and DateIn>@datMax
Then datediff(n, @datMin, DateIn)
When DateOut<@datMin and DateIn>@datMax
Then datediff(n, @datMin, @datMax)
When DateOut>@datMin and DateOut<@datMax and DateIn>@datMax
Then datediff(n, DateOut, @datMax)
Else datediff(n, DateOut, DateIn)
End) as [Minutes]
From [Log]
Where ((DateOut>=@datMin and DateOut<=@datMax)
or (DateIn>=@datMin and DateIn<=@datMax)
or (DateOut<@datMin and DateIn>@datMax))
And (License='Viewer' or License='ARC/INFO' or License = 'Editor')
--And ([Minutes]>120)
Group by UserName
--Having ([Minutes]>120)
Order by [Minutes] desc
View 2 Replies
View Related
May 17, 2006
I have a big presentation tomorrow and I have a query I'm trying to
make a little more presentable. query text itself formatted (filtering data)
Tables:
--tpeople
--tsynclog
The query:
select Firstname+' '+Lastname as [FirstName LastName],synctime as
[Nestle synctime],sum(DurationInMinutes)
from tpeople
join tsynclog on people_id = id
where synctime > '2006-05-05'
group by
tpeople.FirstName,tsynclog.people_id,tpeople.LastName,tsynclog.SyncTime
having sum(DurationInMinutes) > 100
order by synctime desc
As you can see I need some alias added and maybe the whole thing formatted a little
different. Can anyone help?
Thanks.
View 2 Replies
View Related
Aug 28, 2006
Hi all,
I am looking for some study meaterial focussed on performance based transact SQL development. I am a fairly well seasoned (3+ years self taught), and I am getting into situations where different query constructs yield the same results, but the performance (execution time and disk I/O) varies.
I have found that sometimes nested select statements execute faster than joins, but usually the opposite is true, for example, and I would like to learn why.
Info would be appreciated.
View 4 Replies
View Related
