Expert's Critique On Package Design
Aug 31, 2007
All:
This proably is an unsual request. I have developed a package that runs fine and does what it is supposed to do. I am jsut not sure if I have developed it in the most efficient way. I have several years of ETL experience but only about 6 months with SSIS. I know I can benefit a lot if I had my package reviewed by an expert.
I realize that I am asking for some time committment on your part and so would understand if I do not get any takers. But if you would like to review my package and offer suggestions on its improvement please let me know. We can work on the logistics of getting the package to you.
Thanks!
View 7 Replies
ADVERTISEMENT
Apr 4, 2007
Hello friends,I have to design a database for banking application,but it is my first db design so i need some expert advice.
Data base has some performance constraint i.e high output ,minimum query time,need to process 2 to 5 million transaction per day.
Further specification can be revealed if appropriate help is provided.
your advice will be of great help for me.
Thx
View 4 Replies
View Related
Aug 8, 2007
I would like to create a database that keeps track of our companies subject matter experts. I have roughed out some of the tables. I would appreciate any feedback on if this is the right approach and if there might be any issues when I start writting a front-end (probably VB 2005).
What makes this interesting (at least for me) is that a subject has an owner and at least 1 "expert", possibly up to 3. Here is what I am thinking for tables:
tblEmployee
EmployeeID (PK)
LastName
FirstName
etc......
tblSubject
SubjectID (PK)
Description
tblOwner
SubjectID (FK)
EmployeeID (FK)
tblExpert1
SubjectID (FK)
EmployeeID (FK)
tblExpert2
SubjectID (FK)
EmployeeID (FK)
tblExpert3
SubjectID (FK)
EmployeeID (FK)
Does this make sense? Would I run into any issues when trying to display this on a form in VB?
Thanks in advance for any help!!!!!
Cal
View 8 Replies
View Related
May 21, 2004
Here is a trigger I wrote to check for duplicate taxid's in my producer table. Duplicate taxid's with an '' value are allowed. I'm green when it comes to trigger coding so would anyone care to review my code and reply if there's a more efficient way to implement the trigger or handle errors.
Thanks PLJ
create trigger dbo.tib_producer on dbo.producer for insert as
begin
declare
@numrows int,
@errno int,
@errmsg varchar(255)
select @numrows = @@rowcount
if @numrows = 0
return
/* Check for duplicate tax_id if inserted is not null */
if update(tax_id)
begin
if exists(select tax_id
from producer
where tax_id in (select tax_id
from inserted)
and tax_id <> ''
group by tax_id having count(*)>1)
begin
select @errno = 50002,
@errmsg = 'Tax ID already Exists'
goto error
end
end
return
/* Errors handling */
error:
raiserror @errno @errmsg
rollback transaction
end
View 3 Replies
View Related
Feb 23, 2007
Hi,
I would really appreciate any constructive criticism of my sql script (see end of message).
I have been working for a week to pull data from a database that has evolved over the years. It might have been designed to begin with, but it has had a lot of changes made on the fly, which makes pulling some data from it very hard.
One example is that they wanted Primary Service for a given client but this data was not stored in a base client table, but was instead on a table with multiple rows per client. To pick the row that referenced the correct Primary Service I had to:
1. Join the Client table with the view on client number and primary program number
2. From the records returned look at another column (proc_cde) in the view and pick the one that had a higher priority than the others. The precedence order of the proc_cdes was determined by a list my boss gave me. I used this forum to help me figure this part out.
Anyway … I finally got it done and it works … but just because it works doesn't mean it is well written. What I would like is if some of you could take a look at the sql and critique it for good and/or bad practices.
The design of the database is a given (and not my doing) so I am not looking for a critique of it.
An Example of what I am asking for would be:
I ended up using temp tables to collect some of the more complicated data so that I could write a very straightforward final query. Was this a good or bad thing and if bad why?
I really want to get good at sql and avoid developing any bad habits, so please critique my sql.
Also please ask me any questions that could help you evaluate the code.
Thanks,
Laurie
Note: the comments at the top were all the specs I got for creating this report (they came in an email).
/*detail- primary clincian/provider priemp_num joined to facemp Y
primary service (act,cm,opt), pripgm_num primary program,
select proc_cde from v_autsvc join svc
on proc_cde where pgm_num=pripgm_num
H0039 then T1017 t1016 then H0036
location of service, pgm_num of 90862 auth
date of auth v_autsvc.aut_dte
cons name/number: CLTCLT
auth date range v_autsvc.autbeg_dte v_autsvc.autend_dte
number of claims in the last fiscal year v_clmsvc, count(*)
primary psycchiatrist. cltdmo.pspemp_num join phy on phy_num
select clt_num, count(*) as num_claims from v_clmsvc
where beg_dte between '10/1/2005' and '9/30/2006 23:59'
group by clt_num
tables: v_autsvc, pgm, cltdmo.population='mi adult'
proc_cde in ('90862','90801')
autend_Dte>'10/1/06'
I need a report showing all Adult MI consuemrs receiving psych services by agency- like the
aggregate and detail of who gets them at where-bridgeways, sr srv... including our clinic.
i would need in the detail- primary clincian/provider, primary service (act,cm,opt), location of service,
date of auth, cons name/number, auth date range/# of units and number of claims in the last fiscal year and
primary psycchiatrist. if xxx asks for something similar, let me know as we might be duplicating.
i know that xxx is going to be asking for a report that is for just our clinic that has more to do with
insurance type. if it would be easier, you could combine the 2 reports as long as we have the capacity
to sort and print by provider. thanks*/
/**********************************************************************************
Primary Service
***********************************************************************************/
-- select primary proc_cde for primary service Candidates
drop table #ConsumerProcCodes
select distinct a.clt_num,
a.proc_cde,
a.aut_dte,
a.autbeg_dte,
a.autend_dte
into #ConsumerProcCodes
from cltdmo d join v_autsvc a on a.clt_num = d.clt_num
and d.pripgm_num = a.pgm_num
and a.autsts = 1
and getdate() between a.autbeg_dte and a.autend_dte
-- create table of ProcCde Precedence
drop table #ProcCde_Precedence
create table #ProcCde_Precedence
(proc_cde varchar(10),
Precedence int)
insert #ProcCde_Precedence
(proc_cde,Precedence)
select 'H0039', 10 union all
select 'T1017', 20 union all
select 'T1016', 30 union all
select 'H0036', 40 union all
select 'T2011', 50 union all
select '90806', 60 union all
select '90862', 70 union all
select 'T1002', 80 union all
select 'H2031', 90 union all
select 'H2023', 100 union all
select 'H2015', 110 union all
select 'T1005', 120
-- assign precedence to proc_cde
drop table #ConsumerProcCodesWPrec
select distinct c.clt_num,
c.aut_dte,
c.autbeg_dte,
c.autend_dte,
case when p.Precedence is null then 1000
else p.Precedence
end as Precedence,
left(c.proc_cde,5) as proc_cde
into #ConsumerProcCodesWPrec
from #ConsumerProcCodes c left join #ProcCde_Precedence p on p.proc_cde = left(c.proc_cde,5)
-- select primary proc_cde for each consumer and get associated PrimaryService
drop table #PrimaryService
select distinct p1.clt_num,
p1.proc_cde,
p1.aut_dte,
p1.autbeg_dte,
p1.autend_dte,
s.acttyp_des as PrimaryService
into #PrimaryService
from #ConsumerProcCodesWPrec p1 join svc s on p1.proc_cde = s.proc_cde
where Precedence = (select min(Precedence)
from #ConsumerProcCodesWPrec p2
where p1.clt_num = p2.clt_num)
-- Find Consumers with more than one primary Proc_cde
/*
select clt_num, proc_cde, aut_dte, autbeg_dte, autend_dte
from #PrimaryService
where clt_num in (select clt_num
from #PrimaryService
group by clt_num
having sum(1) > 1)
*/
/**********************************************************************************
Primary Location
***********************************************************************************/
-- select pgm_num for Primary location Candidates
drop table #ConsumerLOS
select distinct a.clt_num,
a.pgm_num,
p.pgm_nme
into #ConsumerLOS
from v_autsvc a join pgm p on a.pgm_num = p.pgm_num
where a.autsts = 1
and getdate() between a.autbeg_dte and a.autend_dte
and a.proc_cde = '90862'
-- create table of pgm_num for Location of Services Precedence
drop table #LOS_Precedence
create table #LOS_Precedence
(pgm_num int,
Precedence int)
insert #LOS_Precedence
(pgm_num,Precedence)
select 5600, 10 union all
select 6200, 20 union all
select 6000, 30 union all
select 1611, 40 union all
select 1612, 50 union all
select 1601, 60
-- assign precedence to pgm_num
drop table #ConsumerLOSwPrecedence
select distinct c.clt_num,
case when p.Precedence is null then 1000
else p.Precedence
end as Precedence,
c.pgm_num
into #ConsumerLOSwPrecedence
from #ConsumerLOS c left join #LOS_Precedence p on p.pgm_num = c.pgm_num
-- select pgm_num for primary location of Services for each consumer
drop table #PrimaryLOS
select distinct p1.clt_num,
case n.NumRows
when 1 then cast(p1.pgm_num as varchar(10))
else cast(p1.pgm_num as varchar(10)) + '*'
end as LocationOfService
into #PrimaryLOS
from #ConsumerLOSwPrecedence p1 join (select clt_num,count(*) as NumRows
from #ConsumerLOSwPrecedence
group by clt_num) n on p1.clt_num = n.clt_num
where Precedence = (select min(Precedence)
from #ConsumerLOSwPrecedence p2
where p1.clt_num = p2.clt_num)
-- Find Consumers with more than one primary pgm_cde for Location of Services.
/*select distinct clt_num from #PrimaryLOS
select * from #PrimaryLOS
where clt_num in (select clt_num
from #PrimaryLOS
group by clt_num
having sum(1) > 1)*/
/**********************************************************************************
Put it all together
***********************************************************************************/
drop table #FinalResults
select distinct c.clt_num,
c.fst_nme + ' ' + c.lst_nme as ConsumerName,
f.fst_nme + ' ' + f.lst_nme as PrimaryClinician,
p.fst_nme + ' ' + p.lst_nme as PrimaryPsychiatrist,
ps.PrimaryService,
COALESCE(n.num_claims,0) as num_claims,
los.LocationOfService,
ps.aut_dte,
ps.autbeg_dte,
ps.autend_dte
into #FinalResults
from cltctl c join cltdmo d on c.clt_num = d.clt_num and d.population = 'mi adult'
join v_autsvc a on c.clt_num = a.clt_num
and a.autsts = 1
and getdate() between a.autbeg_dte and a.autend_dte
and a.proc_cde in ('90862','90801')
join facemp f on d.priemp_num = f.facemp_num
left join phy p on d.pspemp_num = p.phy_num
left join #PrimaryService ps on ps.clt_num = c.clt_num
left join (select clt_num, count(*) as num_claims
from v_clmsvc
where beg_dte between '10/1/2005' and '9/30/2006 23:59'
group by clt_num) n on c.clt_num = n.clt_num
left join #PrimaryLOS los on c.clt_num = los.clt_num
order by c.clt_num
select * from #FinalResults
/*
select * from #FinalResults
where PrimaryService is null
select * from #FinalResults
where LocationOfService is null
select * from #FinalResults
where PrimaryPsychiatrist is null
select * from #FinalResults
where PrimaryClinician is null
select * from #FinalResults
where num_claims = 0
*/
edited to make word wrap work
View 5 Replies
View Related
Jul 23, 2005
I just finished a new query where I summarized detail information. I'mwondering if I did this really awkwardly or is this a common way towrite SQL? I've cross referenced the end results and the data seemsconsistant, so I am happy with the results.TIASELECTSESSION_ID,CAMPUS_ID,SUM(STUDENT_COUNT) AS STUDENT_COUNT,SUM(NEW_STUDENT) AS NEW_STUDENT_COUNTFROM (SELECTSESSION_ID,STUDENT_ID,CAMPUS_ID ,STUDENT_COUNT ,STUDENT_STARTING_SESSION_ID,NEW_STUDENT = CASE WHEN SESSION_ID=STUDENT_STARTING_SESSION_IDTHEN (1) ELSE (0) ENDFROM (selectSESSION_ID,STUDENT_ID,CAMPUS_ID ,STUDENT_COUNT ,STUDENT_STARTING_SESSION_IDFROM(selectSESSION_ID,STUDENT_ID,CAMPUS_ID = (SELECT STUDENT_CAMPUS_ID FROMD_BI_STUDENT WHERE A.STUDENT_SKEY=D_BI_STUDENT.STUDENT_SKEY) ,STUDENT_COUNT = DAY0_CLASS_COUNT,(select student_starting_session_id fromf_bi_student_statistics where A.student_id =f_bi_student_statistics.student_id) as 'STUDENT_STARTING_SESSION_ID'from f_bi_registration_tracking_summary A) AS XWHERE STUDENT_COUNT > 0GROUP BY SESSION_ID, STUDENT_ID, CAMPUS_ID, STUDENT_COUNT,STUDENT_STARTING_SESSION_ID) AS Y) AS ZGROUP BY SESSION_ID, CAMPUS_ID
View 3 Replies
View Related
Jun 6, 2008
I was building out my db and then ran into a problem updating a primary key value. I then started to doubt whether or not I even did this right. To simplify, I"m going to just create a smaller similar scenario and I just want to know if its right
Lets say I have three tables
Order Tableordernum (PK)orderdateorderamt
Phone TablePhonenumber (PK)ordernum (FK)NumbertypeInternet TableCircuitID (PK)Phonenumber(PK)IPSMGW
now, Instead of creating a seperate column for just an incrementing numeric value as a PK i used the order number, phone number, and circuit id's as the Primary keys since every single value will be unique. Plus, I also did it to prevent duplicates. Now the problem I'm having is, when I do Update db set [phonenumber] = @phonenumber, ordernum = @ordernum, numbertype = @numbertype where phonenumber = @phonenumber as wrong as that looks.
It looks wrong to me.. problably why it doesn't work. How do you change the values of primary keys.. or should I have created a column called ID with incrementing numbers. How would I prevent duplicates then?
View 5 Replies
View Related
Jan 23, 2001
Hello
This is my 1st attempt at designing a database, and I have not finished
it completely, but I would like somebody to review and critique for me.
I really don't want to make any mistakes with this and I would appreciate any expertise out there to direct, recommend, suggest improvements and/or changes, PLEASE.
Thank you for considering this,,,if you provide me with your e-mail, I can send you a script.
take care,
~Iris~
:)
View 1 Replies
View Related
Oct 24, 2006
Hi everyone,
Is Microsoft thinking over the possibility to implement Control+Z in order to avoid drawbacks when you're writing a package? I mean, when you drop a container then you aren't able to retrieve again
.
That same behaviour happened with Sql2k-dts.
Thanks in advance and regards,
View 3 Replies
View Related
Oct 8, 2007
Can anybody give me design ideas on the following?
I have numerous tasks, any one of which can fail. I want to point them all (via 'Failure' constraint) to a SendMail task for a "failure notification" email. This I have setup, and is working fine. Now, I want to have a changing message for the email's body (MessageSource) to say "Process A has failed", or "Process B has failed", etc.
My initial thought was to add a variable, then add a ScriptTask between each task and the single SendMailTask, have the script update the variable, and have the MessageSource (body of email) mapped to the variable. Is this the proper way to go about this? Seems like if I have 20 processes that could fail, then I'll have to add 20 script files; this becomes a bit unwieldy the more processes that I want to monitor.
Is there a better way to accmoplish this?
Thx,
Mojo
View 4 Replies
View Related
May 1, 2001
Hi,
Does any one have a workaround this issue. If I designed a package from SQL2000 and save it on SQL 7, I can't open/execute it from SQL 7.0. My workstation is SQL 2000 and the server is SQL 7.0, even if I have the same SQL 7 but different SP.
Thanks
View 2 Replies
View Related
Nov 23, 2005
Hi all,I'm fairly new to this...I't trying to design a DTS package on SQL Server 2000`, which willconnect to the server, export some table date into a .txt file (basedon a select statement) and then delete the data from the table (basedon a delete statement) upon successful completion of the export.So far so good..Now, upon the successful deleteion, I want to export some more data andthen (upon the successful export) to delete the data. I want to repeatthis process approx. 10 times.Though, when I try to link the deletion to the new export on success, Iget the message: "Defining the precedences between the selected itemsis not valid."Any idea on how I can accomplish what I want to do?thanks a lot
View 3 Replies
View Related
Feb 21, 2007
Hello,
I have designed a quite small SSIS package and everything worked just fine .
Untill I moved the SSIS project folder to another solution folder and added whole solution to Visual source safe.
Since then opening the package for design has become unbelievably slow, debugging it even worse.
I think the cause is in moving the SSIS project folder. But I have no idea how to fix this.
Any suggestions ?
Janez
View 10 Replies
View Related
May 15, 2008
Hi
I am busy designing a ETL solution and have a question about how to design the packages.
We have over 30 source systems for different customers. We are building a WH that will combine all this data for analysis. The main issue is that these systems are always at different versions of the software. When a patch is released, it usually goes to one or two for a Beta process before it moves to the rest. These patches can affect the DB design, and we would want to be able to extract any new data as it comes available from the systems that have it.
Solution 1 - Package Versions
The idea was to create the SSIS packages with a version number in their name. For each change, you would create a new version. The Batch control application that is being developed will store which system needs to use which version of the package.
Solution 2 - Multiple paths within a package
This was to create a single package for each table, with a conditional split as the first task. The batch system will still provide which path the package needs to take with different Data Flow tasks containing the different column mappings.
Both have pros and cons, but was wondering if anyone has experience with this type of setup and which way worked best, or if there are any other options.
Many Thanks
Michael
View 3 Replies
View Related
Nov 28, 2006
Greetings SSIS friends,
I am in the process of designing a package for populating a Dimension table for my new data warehouse. I would like to discuss with you my proposed solution in the hope that some of you will be able to advise me on any mistakes that I have made so far.
I have 3 source tables which are used to populate my Dimension table (I will restrict each source table to a few columns to keep this simple).
The source tables are :
Meetings (MeetID, NameID, Meeting_Start_Date, Meeting_End_Date, Meeting_Time_Stamp)
Events (EventID, MeetID, Event_Start_Date, Event_End_Date, Event_Time_Stamp)
Names (NameID, Long_Name, Short_Name, Names_Time_Stamp)
My Dimension table structure is very basic as follows :
Product_DIM (EventID, MeetID, Meeting_Long_Name, Event_Long_Name..... Meeting_Time_Stamp, Event_Time_Stamp)
Please note that I haven't included the start and end dates to keep this simple, but in my real solution, the Dimension table has many more columns.
The Meetings and Events tables contains hundreds of thousands of records so when my package is run, I should only retrieve information that has changed since the last time my Dimension table was populated. This is why I store the time stamps in my Dimension table.
So when my package runs, I get max(Meeting_Time_Stamp) and max(Event_Time_Stamp) from my Dimension table then retrieve all records from the source table where their timestamps are GREATER than the max values.
The problem is this :
If in the source system, an event is changed, the time stamp associated with that record will change value but the meeting record associated with that meeting will not have its time stamp changed.
How do I ensure that when my package is run, I retrieve all meeting and events information even if one hasn't changed?
Should I build a table made of IDs?! And if I do need to build a table made up of IDs, should I store this as a permanent table in my database or is there an SSIS data structure I can use to hold this information?
I hope this makes sense!
View 5 Replies
View Related
Oct 20, 2006
We are downloading 4 large (500mb) zip files in the package. Those come from 4 different FTP sites. Sometimes the FTP download on one of those fails.
Zip files contain images which need to be uploaded for existing listings. Each zip file is processed the exact same way. The question is how can I make the SSIS package so that each downloaded zip file can be processed immediately after the download - and not duplicating the processing logic?
View 3 Replies
View Related
Apr 2, 2008
Hello,
I am new to SSIS. I need to transfer data from SQL Server 2005 Operational Database to SQL Server 2005 Report Database. The upload needs to work every night. There are few master tables and remaining are transactions tables.
I am planning to create 2 packages one for master tables and other for transactions tables.
Is it the good approach?
Also few of transaction tables are heavy in terms of number of records. Will it better if i further break them in many packages?
I am using book "Microsoft SQL Server 2005 Integration Services Step by Step".
Can you suggest any other good book?
Regards,
Shailendra Shelar
View 7 Replies
View Related
Nov 21, 2015
Win 7 SP1 x64 PC. I installed SQL Server 2014 Dev Edition + Visual Studio 2015.
I'd like to create some basic ETL SSIS packages, and I worked very comfortably in 2008R2.
For 2014, I started with this tutorial:[URL]However, it says to go to Start->All Programs->Microsoft SQL Server->SQL Server Data Tools.Â
I did explicitly install SSDT when I installed VS2015. I also installed it separately. I see SSDT listed in Programs, and SSIS is running according to SQL Server Config Manager, and Services. Half of Microsoft's docs seem to be 2012 era, which is a shame because 2014 is out and it's nearly 2016...
how do I get to the GUI where I can design ETL packages?Â
View 7 Replies
View Related
Jun 8, 2007
OK, I was able to successfully migrate all of my DTS packages to SSIS, for SQL 2005. I can log into intergration services and see my packages listed under:
servername --> stored packages --> msdb. Now my question is, how can I open these packages, not run them open them in a design mode like you can in SQL 2000, you can double click on the package name and view the design of the package. how can i do that now that I have them in SQL 2005?
View 6 Replies
View Related
Jun 14, 2002
I am looking into a position requiring "SQL PLUS Expert" - anbody out there heard of this - is it a querying tool a la query analyzer of PL SQL.
View 2 Replies
View Related
Jun 14, 2002
I am looking into a position requiring a "SQL PLUS Expert" - anybody out ther heard of this - is it a querying tool a la query analyzer or PL SQL ?
View 1 Replies
View Related
Dec 11, 2006
Hello,
Lets just say that I have really only logged into phpmyadmin once and messed up my forum.
What I was trying to do? Install a shoutbox (chatroom type software).
When I logged into phpmyadmin I clicked the phpbb_config tab on the left hand side. Then I clicked the SQL tab and pasted the code required for the shoutbox accordingly and hit the "Go" button. But what I did next is what created problems.
I thought I may have put extra spaces or loaded the code wrong in the SQL area because the shoutbox did not work properly after everything was installed. So while in phpmyadmin and after clicking phpbb_config agian, instead of hitting SQL, I hit the "Empty" button thinking that I could just erase what I had previously inserted and then try inserting it again to make sure the code was inserted properly.
If you havn't figured it out by now, I clearly don't have a clue what I am doing, what exactly I did, or what I can do to fix it.
Click on the link to see the error..
http://www.cflzone.com/forum/index.php
Can someone that knows what they are doing please help me???
thanks,
- Reider
View 3 Replies
View Related
Feb 27, 2007
Hello,Consider I have a String:Dim MyString As String = "Hello"or an Integer:Dim MyInteger As Integer = 100or a class which its properties:Dim MyClass As New MyCustomClass
MyClass.Property1 = "Hello"
MyClass.Property2 = Unit.Pixel(100)
MyClass.Property3 = 100Or even a control:Dim MyLabel As Label
MyLabel.Id = "MyLabel"
MyLabel.CssClass = "MyLabelCssClass" Is there a way to save a String, an Integer, a Boolean, a Class, a Control in an SQL database?Something like: Define something (Integer, String, Class, Control, etc) Save in SQL 2005 Database Later in code Retrive from database given its IDIs this possible?How should I do this?What type of SQL 2005 table field should be used to store the information?Thanks,Miguel
View 1 Replies
View Related
Feb 20, 2001
Has anyone any suggestions how I might find the most recent of a series of dates from different tables? I am joining tables which all have a date_altered field and I am only interested in displaying the most recent of these. Eg.
select a.x,b.y,c.z, a.date_altered, b.date_altered, c.date_altered
from a inner join b on (a.id=b,id) inner join c on (b.id=c.id)
What I would like is in effect a function Highest() Eg.
select a.x,b.y,c.z, highest(a.date_altered, b.date_altered, c.date_altered) as last_alteration
from a inner join b on (a.id=b,id) inner join c on (b.id=c.id)
I am using SQL Server 7 so cannot write myself a function to achieve this - frustrating, as it is something I could have done 4 years ago when I used mostly Oracle.
Many thanks
View 2 Replies
View Related
Jul 12, 2004
Fact is:
I have two tables Orderposreg and Temp1 (both in [My Database]), Orderposreg is from 1994, Temp1 from 1995 and i need to Update the old table with the new one.
This should my Query do: I need it to Update old primary keys, or if the row does not exist to insert the new primary key into the Orderposreg.
But if I start the Query i get this failure message:
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo.Temp1' does not match with a table name or alias name used in the query.
It seems that the colums do not have the same name, but the column of both tables have exactly the same column name and data type just the table name is different.
For your information the whole Code:
Code:
declare @error varchar, @rowcount varchar
select count(*) from dbo.temp1
if (@@error <> 0)
begin
Print 'An error occurred reading the temporary import table'
goto exitpoint
end
begin transaction
update dbo.Orderposreg
set dbo.Orderposreg.Ordernr = dbo.Temp1.Ordernr,
dbo.Orderposreg.Pos = dbo.Temp1.Pos,
dbo.Orderposreg.Produktnr = dbo.Temp1.Produktnr,
dbo.Orderposreg.Artbenämn = dbo.Temp1.Artbenämn,
dbo.Orderposreg.Artikelgrupp = dbo.Temp1.Artikelgrupp,
dbo.Orderposreg.Förs_pris = dbo.Temp1.Förs_pris,
dbo.Orderposreg.Kvant = dbo.Temp1.Kvant,
dbo.Orderposreg.Total_Intäkt = dbo.Temp1.Total_Intäkt,
dbo.Orderposreg.SSort = dbo.Temp1.Sort,
dbo.Orderposreg.Datum = dbo.Temp1.Datum,
dbo.Orderposreg.Utskriven = dbo.Temp1.Utskriven,
dbo.Orderposreg.Levtid_Ö = dbo.Temp1.Levtid_Ö,
dbo.Orderposreg.Levtid_L = dbo.Temp1.Lev_kvant,
dbo.Orderposreg.Inköpskostnad = dbo.Temp1.Inköpskostnad,
dbo.Orderposreg.Vikt_tol = dbo.Temp1.Vikt_tol,
dbo.Orderposreg.AntalSt = dbo.Temp1.AntalSt,
dbo.Orderposreg.Spec_nr = dbo.Temp1.Spec_nr,
dbo.Orderposreg.Spec_utgåva = dbo.Temp1.Spec_utgåva,
dbo.Orderposreg.ID_Beräknad = dbo.Temp1.ID_Beräknad,
dbo.Orderposreg.Bredd = dbo.Temp1.Bredd,
dbo.Orderposreg.Tjocklek = dbo.Temp1.Längd,
dbo.Orderposreg.ValsnGrad_kod = dbo.Temp1.ValsnGrad_kod,
dbo.Orderposreg.Kant_Kod = dbo.Temp1.Kant_Kod,
dbo.Orderposreg.Planhets_kod = Temp1.Yt_kod,
dbo.Orderposreg.LevForm_kod = dbo.Temp1.LevForm_kod,
dbo.Orderposreg.Rakhets_kod = dbo.Temp1.Rakhets_kod,
dbo.Orderposreg.BreddTol_kod = dbo.Temp1.Breddtol_kod,
dbo.Orderposreg.TjocklTol_kod = dbo.Temp1.TjockTol_kod,
dbo.Orderposreg.LängdTol_kod = dbo.Temp1.LängdTol_kod,
dbo.Orderposreg.Stål_kod = dbo.Temp1.Stäl_kod,
dbo.Orderposreg.TotaltSek = dbo.Temp1.TotaltSek,
dbo.Orderposreg.Tullstatnr = dbo.Temp1.Tullstatnr,
dbo.Orderposreg.Fakturera = dbo.Temp1.Fakturera,
dbo.Orderposreg.Leveransstatus = dbo.Temp1.Leveransstatus,
dbo.Orderposreg.Momsbelopp = dbo.Temp1.Momsbelopp,
dbo.Orderposreg.Total_inkl_moms = dbo.Temp1.Total_inkl_moms,
dbo.Orderposreg.KloMPS = dbo.Temp1.KloMPS,
dbo.Orderposreg.ShopFloor = dbo.Temp1.ShopFloor,
dbo.Orderposreg.Status = dbo.Temp1.Status,
dbo.Orderposreg.Stålkod = dbo.Temp1.Stålkod,
dbo.Orderposreg.Kontonyckel = dbo.Temp1.Kontonyckel,
dbo.Orderposreg.PlanLevDat = dbo.Temp1.PlanLevDat,
dbo.Orderposreg.Legtillägg = dbo.Temp1.Legtillägg,
dbo.Orderposreg.StålGrp = dbo.Temp1.StålGrp,
dbo.Orderposreg.KundNr = dbo.Temp1.KundNr,
dbo.Orderposreg.FKundNr = dbo.Temp1.FKundNr,
dbo.Orderposreg.Godsmärke = dbo.Temp1.Godsmärke,
dbo.Orderposreg.LagerMtr = dbo.Temp1.LagerMtr,
dbo.Orderposreg.Sortkvant = dbo.Temp1.Sortkvant,
dbo.Orderposreg.Sortpris = dbo.Temp1.Sortpris,
dbo.Orderposreg.KundsProdNr = dbo.Temp1.KundsProdNr,
dbo.Orderposreg.Godsmärke2 = dbo.Temp1.Godsmärke2,
dbo.Orderposreg.RegDatum = dbo.Temp1.RegDatum,
dbo.Orderposreg.PlanBetDag = dbo.Temp1.PlanBetDag,
dbo.Orderposreg.Säkring = dbo.Temp1.Säkring
where dbo.Orderposreg.Ordernr_o_pos = dbo.Temp1.ordernr_o_pos
select @error = @@error,@rowcount=@@rowcount
if @error <> 0
begin
print ' Error updating - Error number '+@error+'. Rolling back'
--this reverses your updates
rollback
--this jumps to the end
goto exitpoint
end
print @rowcount+' rows updated'
insert into dbo.Orderposreg
(Ordernr, Pos, Ordernr_o_pos, Produktnr, Artbenämn, Artikelgrupp, Förs_pris, Kvant, Total_Intäkt, Sort, Datum, Utskriven, Levtid_Ö, Levtid_L, Lev_kvant, Inköpskostnad, Vikt_tol, AntalSt, Spec_nr, Spec_utgåva, ID_Beräknad, Bredd, Tjocklek, Längd, ValsnGrad_kod, Kant_kod, Planhets_kod, Yt_kod, LevForm_kod, Rakhets_kod, BreddTol_kod, TjocklTol_kod, LängdTol_kod, Stål_kod, TotaltSek, Tullstatnr, Fakturera_kvant, Leveransstatus, Fri_text, Momsbelopp, Total_inkl_moms, KloMPS, ShopFloor, Status, Stålkod_text, Kontonyckel, PlanLevDat, Legtillägg, StålGrp, KundNr, FKundNr, Godsmärke1, LagerMtr, Sortkvant, Sortpris, KundsProdNr, Godsmärke2, RegDatum, PlanBetDag, Säkring)
select
Ordernr, Pos, Ordernr_o_pos, Produktnr, Artbenämn, Artikelgrupp, Förs_pris, Kvant, Total_Intäkt, Sort, Datum, Utskriven, Levtid_Ö, Levtid_L, Lev_kvant, Inköpskostnad, Vikt_tol, AntalSt, Spec_nr, Spec_utgåva, ID_Beräknad, Bredd, Tjocklek, Längd, ValsnGrad_kod, Kant_kod, Planhets_kod, Yt_kod, LevForm_kod, Rakhets_kod, BreddTol_kod, TjocklTol_kod, LängdTol_kod, Stål_kod, TotaltSek, Tullstatnr, Fakturera_kvant, Leveransstatus, Fri_text, Momsbelopp, Total_inkl_moms, KloMPS, ShopFloor, Status, Stålkod_text, Kontonyckel, PlanLevDat, Legtillägg, StålGrp, KundNr, FKundNr, Godsmärke1, LagerMtr, Sortkvant, Sortpris, KundsProdNr, Godsmärke2, RegDatum, PlanBetDag, Säkring
from dbo.Temp1
where dbo.Temp1.Ordernr_o_pos not in (select Ordernr_o_pos from dbo.Orderposreg)
select @error = @@error,@rowcount=@@rowcount
if @error <> 0
begin
print ' Error inserting - Error number '+@error
print ' Rolling back updates and inserts'
--this reverses your updates and inserts
rollback
--this jumps to the end
goto exitpoint
end
print @rowcount+' rows were inserted'
--this saves your data
commit
exitpoint:
Thanks a million for somebody who helps me!!!
View 2 Replies
View Related
Mar 16, 2004
Hi !
I have been worked with VC++, MS SQL SERVER, Transact-SQL for
3 years. I made an axtended stored procedure (xp_test) which returns
an recordset.
From Query Analizer, I can see the recordest : exec xp_test
I want to make an User Defined Function - MyTest which return
the recordset that it is returned by xp_test after its execution.
Something like that :
CREATE function dbo.MyTest ( )
RETURNS @table ...
AS
BEGIN
exec master.. xp_test table1 output -- can I do this ?
RETURN table1
END
Table and table1 are the same design.
Thank you very much !
View 4 Replies
View Related
Feb 27, 2007
Hello,
I am using Enterprise Library Data Access and SQL 2005 application block and I need to know if it is possible to do the following:
Consider I have a String:
Dim MyString As String = "Hello"
or an Integer:
Dim MyInteger As Integer = 100
or a class which its properties:
Dim MyClass As New MyCustomClass
MyClass.Property1 = "Hello"
MyClass.Property2 = Unit.Pixel(100)
MyClass.Property3 = 100
Or even a control:
Dim MyLabel As Label
MyLabel.Id = "MyLabel"
MyLabel.CssClass = "MyLabelCssClass"
Is there a way to save a String, an Integer, a Boolean, a Class, a Control in an SQL database?
Something like:
Define something (Integer, String, Class, Control, etc)
Save in SQL 2005 Database
Later in code Retrive from database given its ID
Is this possible?
How should I do this?
What type of SQL 2005 table field should be used to store the information?
Thanks,
Miguel
View 4 Replies
View Related
Oct 16, 2007
Is there a expert here on full text indexing on 2005 sql server express????
View 8 Replies
View Related
Jul 23, 2005
I do a lot of hiring for my company and a lot of the people I interviewsay that they are experts at SQL queries, but when I give them somethingsimple just beyond the typical SELECT type of queries, they choke.For example I have a table that looks like this:PK_ID - primary keyPARENT_ID - a FK to another row in the same tableThis essentially is a tree structure. I will ask interviewees to writea query that will return all the rows that have 2 direct children. Noone knows how to do this.I don't get it. I have done queries which boggle the mind and they arefar more complex than this.Am I asking too much?--* Don VaillancourtDirector of Software Development**WEB IMPACT INC.*phone: 416-815-2000 ext. 245fax: 416-815-2001email: Join Bytes! <mailto:donv@webimpact.com>web: http://www.web-impact.com/ This email message is intended only for the addressee(s)and contains information that may be confidential and/orcopyright. If you are not the intended recipient pleasenotify the sender by reply email and immediately deletethis email. Use, disclosure or reproduction of this emailby anyone other than the intended recipient(s) is strictlyprohibited. No representation is made that this email orany attachments are free of viruses. Virus scanning isrecommended and is the responsibility of the recipient./
View 15 Replies
View Related
Jan 28, 2008
Hi folks, I have a very typical database for an ASP.net application. There is a table which will contain a hierarchical data..much like files-folders structure of a file system.
But we know that the table will be a giant one in production. There will be a huge collection of data need to persist in it. we are already facing some performance problem with some queries during the QA/test machine.
Currently there is a table which is keeping all file and folder information and another table maintaing their hierarchy relation using two column namely, parentID and childID.
My first question is, would it be better to keep this hierarchy relation into the same table rather using a different one? (much like managerID and empID in AdventureWorks sample?)
My Second question, what is the best way to design this kind of structure to get the highest performance benifit?
All kind of thoughts will be appreciated much! thanks
View 26 Replies
View Related
Oct 26, 2007
Hi,
I have a table describing items with 3 rows:
ID : its an identity Primary key
Type : nvarchar string for itemtype
ItemData : numeric data for an item
I would like to create a query to get the results where each returned row contains
- Type
- number of items for the given type
- concatenated string of the ItemData numbers for all items for the given type
ex: Items
1,"Big",1
2,"Big",56
3,"Small",45
4,"Big",22
Expected result:
"Big",3,"1 56 22"
"Small",1,"45"
I started with this query:
SELECT Type , COUNT(Type) AS Amount FROM Items GROUP BY Type
but i cannot figure out how to do the string concatenation (its like SUM but we dont need to add the values but we need to concatenate). I was thinking some stored procedure but this query will run on Compact SQL so thats not possible according to Compact SQL specs. Can any SQL expert help, how to do this, or is it possible to do anyhow?
Thanks for the help.
View 1 Replies
View Related
Apr 15, 2004
First i am designing a small database fo similar types of books ( Stories table , Short stories table , ...etc ) so i the columns in each table of them is similar , so instead of using a Stored Procedure for every table to select the TOP book i used one SP with variable table name and it works well as i pass the table name from the page , here it is :
---------------------------------------------
CREATE PROCEDURE Top1
@tblname Varchar(255)
AS
EXEC ( 'SELECT TOP 1 *
FROM ' + @tblname + '
WHERE Status="OLD"
ORDER BY ID DESC')
GO
-----------------------------------------------
The Problem now that i want to make the same thing with the INSERT STATEMENT to input new books to the table by passing the table name from the page like the SELECT one , and i `ve tried this one but gives me errors :
--------------------------------------------------------------------------------------------------
CREATE PROCEDURE AddNewStory
@tblname varchar(50),
@title varchar(50),
@Content varchar(5000) ,
@Picture varchar(200) ,
@date datetime
AS
EXEC('INSERT INTO' + @tblname + '( Sttitle , StContent, StPic, StDate )
VALUES ( '+ @Title + ',' + @Content+ ',' + @Picture+ ',' + @date + ')' )
GO
----------------------------------------------------------------------------------------------------
& th error is
Server: Msg 128, Level 15, State 1, Line 2
The name 'Dickens' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
Stored Procedure: db1sql.dbo.AddNewStory
Return Code = 0
SO PLEASE HELPPPPPPPPPPP
View 4 Replies
View Related
Jun 15, 2000
Is anybody out there using SQL Expert Pro? Can you give me some feedback please?
I've been evaluating it for two weeks now and am very pleased with the results but I'm a bit hesitant to spend $5000.
Thanks!
Pete Karhatsu
View 3 Replies
View Related
