Odd Things...I'm Totally Stuck
Aug 6, 2007
Hi all of you,
I've got a .dtproj project along with 10 dtsx packages. Up to here everything is fine, every package have its own source files, sql destinations and so on..
Issue comes when I try create a flat file source connection for the eleven one.
I get this message:
The component has detected potential metadata corruption during validation.
Error at Data Flow Task [Flat File Source [1]]: Failed to find any non-special error column in output "Flat File Source Error Output" (3).
It may not be possible to recover to a valid state using a component-specific editor. Do you want to use the Advanced Editor dialog box for editing this component?
Otherwise if I'm gonna to open a new .dtproj project separately no problem at all, I mean, appears the suitable window, "Connection Manager", "Column", "Error output" on the left and so on..
It's very strange. The rest of the packages, the same situation. Fortunately, all of them are stored on the server.
I don't get the point at all. Could you please tell me what the hell is happening?
Thanks in advance and regards,
View 3 Replies
ADVERTISEMENT
Jan 4, 2007
I know that the answer to my problem is something that lies right infront my eyes but I got totally stuck. I cant understand why, when I deploy a database application I've made, when the application is run for the second time, the database doesn't have the data I have entered the first time... Is it something I have to tell my application?
It must be something trivial but I just can't put my finger on it.
View 5 Replies
View Related
Jan 5, 2004
i'm using query analyzer,
simply trying to use smallmoney as
In my table I have declared as:
payrate smallmoney,
Inserting values into table like this:
'258'
Error recieved is:
Disallowed implicit conversion from data type varchar to data type smallmoney,
What am I doing wrong? Also, ideally i'd like the 258 to be expressed as £258 (english pounds) if anyone can help with that also.
Thanks in advance
View 4 Replies
View Related
Jun 10, 2008
Waiting my my 431 study package turning up, any offers on any addional books etc that would help me pass this exam?
Cheers up front.
View 2 Replies
View Related
Feb 17, 2008
Hi folks,
This is what I want to do,but is it possible.
I think I have got the hang of queries and stuff like that but here is what I want to do.
I am using the 2008 express freebie from microsoft with Sql server 8 freebie.
Here is waht I want to do. Place a load of buttons on a screen each one with a list in it.
so for example box one will specify all the days of the week,the others all the colors of the rainbow and the others all the month in the years.
So the the boxes will feed through to a tiny little sql server database with three tables...days..colours...months.
I want somebody to be able top come along and then click on a particular day of the week...let's say thursday...then click on a colur.lets say pink...then click on a month,lets say september..
Then a table will appear with that info in it..i.e. thursday,yellow september.
I know I could write a little prog in VB or C++ to do this but I am trying to relate it to a database.
I think the bottom line here is that I am trying to get the user to create the query and get the result of that query out onto a page.
As you can see I am just below novice level and I think the question I am really asking is that is it possible that the person using my program is actually making up a query(based on my criteria)and can get a result,rather than me have top store a query(or stored procedure) that just responds to a click on the box.
If this lot makes any sense what so ever,then could someone kind soul point me it the right direction(or any direction) :)
Thanks
Jimm222
View 1 Replies
View Related
Jul 19, 2004
I am looking thru SQL Books, browsing the forum, but still unclear of the best method to perform the following calculation. I am really struggling with this and any helpful hints would be greatly appreciated.
Situation
Table 1 = customer information. Contains customer #, name, address, etc.
Table 2 = default price list. Contains item #, item desc, price
Table 3 = Allowed discounts based on customer types. Hospital gets $1 off, Doctors get 25 cents off, individuals get no discount.
Further discounts are given for multiple purchases. The 1st item is 100%, 2nd item is 75% and 3rd and subsequent items get 50% off purchase that day.
Here is a basic query result set of customer number, price, item ordered, item description and customer type.
Cust Price ItemNo Item Type
1 $5.50 23432 Moisturizer Hospital
2 $5.55 23453 Moisturizer 2 Indvidual
2 $29.00 23243 Lotion Pkg Indvidual
3 $29.00 23243 Lotion Pkg Doctor
3 $21.25 23232 Gloves Doctor
3 $3.00 23235 Drape Doctor
3 $2.00 27821 Lube Doctor
Calculation
Customer 1 ordered 1 item @ 5.50, but because they are a hospital they get a $1.00 discount. They only ordered 1 item so they pay 100% of the discounted price.
(5.50 - 1.00) * 100% = 4.50
Customer 2 ordered 2 items @ 5.55 and 29.00 respectively. They get no discount because they are an individual client, but do qualify for the multiple item discount of 25% for the second item.
29.00 + 5.55 * 75% = $33.16
Customer 3 ordered 4 items @ 29.00, 21.25, 2.00 and 3.00 respectively. They get a 25 cent discount off each item for being a doctor. They also get a multiple item discount of 25% off the second item and and 50% off items 3 and 4.
The math ideally will look like this
(29.00 - .25)*100% + (21.25 - .25)*75% + (3.00-.25)*50% + (2.00 - .25) * 50% = $47
Problem
For customer 2 if someone entered the lower valued item first the math would look like this
5.55 + 29.00 * 75% = $27.30 (loss of revenue)
Possible Solution
The prices need to be sorted in descending order to maximize revenue prior to discounts being taken for a given day.
Should I use a "rank by clause" statement?
Then I need to multiply the first item by 100%, the 2nd by 75% and the remaining items by 50% to get a grand total of money due. Any pointers would be greatly appreciated.
I am not at all sure how to accomplish this.
View 3 Replies
View Related
Jul 20, 2005
Hi there...I am having a whole bunch of trouble trying to design a certain query. Ihave two tables (see a representation of them here:http://www.plankmeister.org.uk/tables.html ) one called menu_data and onecalled page_data.menu_data contains a list of defined menus (menu_group) which may be sortedaccording to the menu_group_display_order column.page_data contains a list of page definitions. Each page belongs to amenu_group. All the pages in a given group may be sorted according to thepage_display_order column. (for instance, a query to order all pages with amenu_group equal to 1 in ascending order would produce:CatsDogsFishSquirrels)What I am trying to do is to design a query that will tell me thepage_user_name of all the pages that sit at the 'top' of their menu group,but ordered by the menu_group_display_order column.So basically, it will order the data in menu_data by themenu_group_display_order colum, then comparing menu_data.menu_group topage_data.menu_group, will return the 'first' row after being ordered bypage_display_order, so that in the case of the data given, the return wouldbe:HorsesCatsBirdsRabbitsI've tried UNION, GROUP BY, TOP 1, INNER JOIN, OUTER JOIN, SLIGHTLY TO THELEFT JOIN, ONLY IF ITS A WEDNESDAY JOIN... etc etc... But I just can't getit to work! It either returns 40 rows, or one. I've spent a total of about10 hours trying to design this query, and am losing what precious littlehair I have left at an alarming rate.Hope someone can provide some insight!Plankmeister.
View 3 Replies
View Related
Jul 23, 2005
This is a shameless plug. My new book, SQL PROGRAMMING STYLE is outnow.Joe Celko's SQL Programming Styleby Joe CelkoSave 20% and get Free Shipping if you order by Friday May 6.Place your order now or learn more below_______________________________________If you're looking for a book with a collection of tricks & tips andrules for formatting and writing portable, readable, maintainable SQLcode, you'll find what you need here!Are you an SQL programmer that, like many, came to SQL after learningand writing procedural or object-oriented code? Or have switched jobsto where a different brand of SQL is being used, or maybe even beentold to learn SQL yourself?If even one answer is yes, then you need this book. A "Manual of Style"for the SQL programmer, this book is a collection of heuristics andrules, tips, and tricks that will help you improve SQL programmingstyle and proficiency, and for formatting and writing portable,readable, maintainable SQL code. Based on many years of experienceconsulting in SQL shops, and gathering questions and resolving hisstudents' SQL style issues, Joe Celko can help you become an evenbetter SQL programmer. More specifically, this book will give youexpert advice from a noted SQL guru and award-winning columnist.Table of Contents_______________________________________FeaturesHelps you write Standard SQL without an accent or a dialect that isused in another programming language or a specific flavor of SQL, codethat can be maintained and used by other people.Enables you to give your group a coding standard for internal use, toenable programmers to use a consistent style.Gives you the mental tools to approach a new problem with SQL as yourtool, rather than another programming language-one that someone elsemight not know!Place your order here:http://books.elsevier.com/us//mk/us...r=&community=mkVolume discounts available, contact Join Bytes!
View 1 Replies
View Related
Jan 30, 2008
Hello everyone. I need some assistance here. I have a web form to get information about user details. The web form has two parts. the first part is user personal information where there is few textboxes, dropdownlist , radiobuttons , checkbox etc. this is where user inputs his personal information. the second part contains the office information where there is few textboxes, dropdownlist , radiobuttons , checkbox etc too. but the second part is automatically populated by data from the database(lets say table1). this happens as soon as user selects an option from dropdownlist in second part(here an option for dropdownlist is name of the office) . there is also a save and cancel button at the end of the form.now what i want here is, whenever i click a save button in my webform,i want the data of first part of my webform (ie user personal information) to get stored in new table(say table2).but i dont want the data of secont part of my webform(ie office information) to get stored in table2.(coz its already stored in table1.thats from whereit got populated earlier).But i want a reference of second part of my web form(ie office info) in table2.(only the reference id of related data from table1.not the whole data)and finally i want to display data from both the tables in one single details view.(both personal and office information)i hope u people are gettin it?i know i have to use foriegn keys.but i dont know how to use in this case.(sql express)and can u plz explain how to implement this in cs file with example including source code.(c#, vwd2005 express, sql express). thanks. jack.
View 5 Replies
View Related
Apr 26, 2006
I downloaded MS SQL Server 2005 Express Edition. The MS site said that it was "easy to install, use and manage." However I'm totally confused. I'm not a total neophyte but I can't even seem to launch the program.
I have an MS SQL database powering my website. I view the database on my home computer using MS Access 2000 with an ODBC connection. MS Access 2000 however, does not let me make changes to the structure of the database. I can't add, delete or modify fields in my current tables. I can't add or delete tables. Is MS SQL Server 2005 Express Edition the proper software to perform these functions? If it is, is there a guide to helping me launch the program, connect to my MS SQL database and perform these functions? If this is not the right software, what is?
I would greatly appreciate any guidance anyone can offer.
Thanks!
View 1 Replies
View Related
Jan 26, 2006
okay so i have query which sure does run fine if i drop it into a SQL server's console. however i try and put this into ACCESS so the accounting department can use it and it returns no values.
not sure if i should drop in the vb forum or what but im about to build a page in c# and have them get it that way.
Any one have any suggestions
Code:
select Doc.Dat, Doc.DocNo, Doc.IdPers, Pos.ItemNo, Pos.[Description], Pos.Quantity, Pos.SinglePrice, Pos.Discount from Pos, Doc where Doc.Dat like '%2005%' AND IdPers = (select IdPers from Pers where match = 'Aec One Stop Group, Inc.') AND Pos.IdDoc in ( select idDoc from Doc where Type = 8 ) ORDER by Dat, DocNo DESC
View 8 Replies
View Related
Aug 4, 2015
I've been parachuted into a place with a few dozen servers, several thousand external users (and scheduled to grow). Right now the whole place - IT and non-IT alike - l logs in with SA.....
(where's the 'Puking' icon??)
They've got Windows logins, but that's about as tight as it gets. Honestly I've never seen anywhere as insecure....and not sure where to start.
View 0 Replies
View Related
Sep 20, 2007
I have two table A and B:
A 2000000 Rows 569288KB 8KB index
B 3000000 Rows 853712KB 8KB index
But when do "SELECT COUNT(*) FROM A/B", table B is significantly slower than A:
A 0 secend
B 8 seconds
Does anyone know why? So I can boost the performance to search table B.
Thanks in advance.
View 8 Replies
View Related
Apr 19, 2002
I am writting a T-SQL script to install my database. I have figured out all the sets except 2 areas.
1. Give login db_datareader / db_datawriter permissions to a DB
2. Add a DTS Package, it uses a script, and schedule it.
Here's how I do them in EM....
Issue 1: Security - Logins,
Right click on login, select properties
In database access tab, check Permit for DB
In database role, check public, db_datareader, db_datawriter.
Issue 2: DTS - Local Packages
Right click on local package, new package.
Create SQL connection & exe SQL task (attach seperate SQL script)
Save
Right click on newly created package and schedule
View 2 Replies
View Related
Dec 6, 2007
Hello everybody ,
I want you to explain me something that i find a little strange.
Yesterday i had a problem with a huge log file and a friend in here helped me fix this by making a log back up and then shrinking it to 200 MB.Everything went fine.When i returned home i tried something else in a test environment..Here what happened:
I Got a full database back up.
I dropped the database.
I created a new one with 1GB mdf and 250MB ldf.
I vanished the log back -up
I restored the mdf file on the new database
The log file again had the same huge size after the restore.
How did this happen?
I sell my mother in law.Is anybody interested?
View 6 Replies
View Related
Feb 12, 2008
Hi all,
a question that has been in my mind for a while:
A HEAP is a table that has no clustered index.
Now consider a table that has a unique clustered index (primary key) on an identity column ?
For example:
create table Test
(
TestId int identity(1,1) not null,
...,
constraint PK_Test primary key clustered (TestId)
)
Is there a recognised term for such a table ?
Thanks
Graham
View 4 Replies
View Related
Dec 5, 2005
One interviewer has asked me the following question:
What are the things that you consider while designing database?
I have told about integrity constraints, and normal forms.
but he has added 15 more concepts like
1. indexers
2. Table columns
3. Table rows
4. search facilities
6.......
Can any one give full Idea on this question?
Thanking you Ashok kumar.
View 1 Replies
View Related
Aug 23, 2005
A book is like a garden carried in the pocket.
<edit> links removed </edit>
View 1 Replies
View Related
Oct 28, 2005
SQL 2000I have inherited an application where many of the automated processescall a proc that simply returns the number of records with a NEWstatus.In watching the process in SQL, I see this ends up blocking a lot ofprocesses - many like this are called every 5-30 seconds ...I wish to replace COUNT(*) with EXISTS if that will make things operatefaster with no locks ...Thoughts ...Thanks everyone !!Craig
View 1 Replies
View Related
Jan 24, 2007
You can use a Select top @variable in sql server 2005:Thus:--'Throttle' the result set:Select Top (@MaxBatchSize)KeyID, LId, ArrivalDt, CaptureDt, Lat, LongFrom GPSDataOrder By CaptureDt DescMakes messing with Set Rowcount *so* redundant :-)
View 1 Replies
View Related
May 11, 2007
I have made a report in SRS and am not sure of how to do some of the functions I need.
In SQL I can use UPPER to convert to upper case, SRS doesn't seem to like this. The report runs but the line that is to be upper case is missing. If I remove the UPPER word from my query it works fine just displaying in lower case. Am I doing something wrong?
In Excel 2007 (I am converting my report from Excel to SRS) there is a function called NETWORKDAYS (Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.) Is there something similar I can use in SRS?
Thanks.
View 3 Replies
View Related
Jul 24, 2007
Okay,
I'm creating a small application for my local school district that will allow them to do a small management of inventory.
Is there a way to create an executable script that will create the default databases the first time? If so, what all do I need to do for the script and to make it execute?
I want to make a section in my OPTIONS area where users can put the path to their db (will not be hosted on the same PC as the software) along with the username and password for the db. I know I can store this information in a predetermined variable in the program so that it can reference it off and on.
Could someone please help me with this..... If it's in the wrong forum please let me know.
Thanks,
QWERTYtech
View 5 Replies
View Related
Jun 16, 2007
Hello.
I am a newbe in this and I tried to find the information on the msdn but could not find a god answer.
I am using VisualStudio Express 2005 and a SQL Server Express version.
In a Visual Basic application I use "radio buttons", "tick boxes", "Combo Boxes" and "Numeric Up Down".
But I cant figure out what data type to use to save the data in a SQL server.
It would be nice to have a document that maps the different data types to use in SQL Server according to what object you are using in the Visual Studio.
Thanks in advance.
/Martin
View 1 Replies
View Related
Jan 14, 2008
Good afternoon,
I've here a shell plugin and it's compiling fine and can be viewed in BI Dev Studio when choosing the DM technique using the proper wizard.
I also have here a K-Means implementation that estimates the number of clusters using a statistical semi-empiric index (the PBM index).
This implementation is done in C# and works fine. But it has to receive all the data of the database (all variables for each row) in order to do the proper vectorial calculations in a CSR (Compact Sparse Rows) way.
Besides, as you know, K-Means needs all the data at once because of the clusters mean (centroid) calculation.
So, I have some questions:
1) Where to place the call to the K-Means implementation in the shell passing as argument an object holding all the data ?
2) After this call, with the data clustered, what other objects must be modified in order to use Microsoft Cluster Viewer ?
3) I will need to create a new column or a new table on the database to specify which data belongs to which cluster. Can I open an ADO connection as I normally do in other programs from inside the plugin or is there another (easier/better) way to do so ?
Thanks a lot once more.
Best regards,
-Renan Souza
View 1 Replies
View Related
Oct 16, 2006
http://blogs.conchango.com/jamiethomson/default.aspx has a lot of great tid-bits for SQL 2005. I am currently on a tight deadine for 25 SSIS packages that need to be able to move from Dev to QA to Staging to Prod. For the life of me I cannot get any of the packages to *READ* the config files created with the package config wizard. All I want to do is move the connection string out of the package so we can change the config file and not have to touch (hand edit) each package. Any help is appreciated!
View 13 Replies
View Related
Nov 11, 2007
Ive got a table of notes people have created, with a field called "timecreated" which has a default value of "GETDATE()" Im trying to write an SQL statement that will count up all of the notes that people have created today/ yesturday etc. i could do it if the timecreated value was a "short date string" styled date, but its set up like : 11/11/2007 18:51:46 is there way of converting it before counting? if theres a simple way of doing this i would appricate any help thanks John
View 7 Replies
View Related
Apr 11, 2008
I was told I can use them with SQLDataSources, but I have no clue how to do it.
I believe I have managed to set up the querry in the datasource correctly, but what do I need to do to actually use it in my VB code?
Any help is appreciated, and any tutorials you've found to be usefull on the subject are sure to help.
Thanks
View 3 Replies
View Related
Apr 2, 1999
Hi Friends
I have a identity column in a table, while adding records,
it fills the column with different values, for eg. 75 after 90.
In which situation, it may occur and how to solve this.
Thanks in advance.
Rajasekar
View 2 Replies
View Related
Feb 16, 2007
Hello,
Here is the code as it now stands:
select TECHN, RESCODE,
(CASE
WHEN TECHN IS NOT NULL AND RESCODE IS NOT NULL AND COMPLET IS NULL THEN 'TOTAL RESCODE'
when TECHN IS NOT NULL AND RESCODE IS NULL THEN 'TOTAL TECH'
ELSE CONVERT(VARCHAR, CAST(COMPLETE AS DATETIME), 105) END) COMPLETE, DESCRIPTION, RES_NAME, ADDRESS, JOB, TYP, NUM, OPR, sum(QTYW) QTY, sum(NCommissionAMT) CommissionAUT
FROM
(select ir_tech_ojb TECHN
, substring (compl_cde_ojb, 1, 3) RESCODE
, compl_dte_ojb COMPLET
, CodeDes DESCRIPTION
, res_name_sbb RES_NAME
, ADDR1_HSE ADDRESS
, job_typ_ojb JOB
, job_class_ojb TYP
, order_no_ocr NUMB
, ls_chg_op_id_ocr OPR
, CommissionAMT NCommissionAMT
, COUNT(DISTINCT order_no_ocr) QTYW
from Parser.dbo.Parser_ocr
Inner Join Parser.dbo.Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN Parser.dbo.RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE
Inner JOIN csg_hist.dbo.sbb_base on hse_key_ocr = hse_key_sbb
Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 1, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 1, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT
union all
select ir_tech_ojb TECHN
, substring (compl_cde_ojb, 4, 3) RESCODE
, compl_dte_ojb COMPLET
, CodeDes DESCRIPTION
, res_name_sbb RES_NAME
, ADDR1_HSE ADDRESS
, job_typ_ojb JOB
, job_class_ojb TYP
, order_no_ocr NUMB
, ls_chg_op_id_ocr OPR
, CommissionAMT NCommissionAMT
, COUNT(DISTINCT order_no_ocr) QTYW
from Parser_ocr
Inner Join Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE
Inner JOIN csg_hist.dbo.sbb_base on hse_key_ocr = hse_key_sbb
Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 4, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 4, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT
union all
select ir_tech_ojb TECHN
, substring (compl_cde_ojb, 7, 3) RESCODE
, compl_dte_ojb COMPLET
, CodeDes DESCRIPTION
, res_name_sbb RES_NAME
, ADDR1_HSE ADDRESS
, job_typ_ojb JOB
, job_class_ojb TYP
, order_no_ocr NUMB
, ls_chg_op_id_ocr OPR
, CommissionAMT NCommissionAMT
, COUNT (DISTINCT order_no_ocr) QTYW
from Parser_ocr
Inner Join Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE
Inner JOIN csg_hist.dbo.sbb_base on hse_key_ocr = hse_key_sbb
Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 7, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 7, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT
union all
select ir_tech_ojb TECHN
, substring (compl_cde_ojb, 10, 3) RESCODE
, compl_dte_ojb COMPLET
, CodeDes DESCRIPTION
, res_name_sbb RES_NAME
, ADDR1_HSE ADDRESS
, job_typ_ojb JOB
, job_class_ojb TYP
, order_no_ocr NUMB
, ls_chg_op_id_ocr OPR
, CommissionAMT NCommissionAMT
, COUNT(DISTINCT order_no_ocr) QTYW
from Parser_ocr
Inner Join Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE
Inner JOIN csg_hist.dbo.sbb_base on hse_key_ocr = hse_key_sbb
Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 10, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 10, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT
union all
select ir_tech_ojb TECHN
, substring (compl_cde_ojb, 13, 3) RESCODE
, compl_dte_ojb COMPLET
, CodeDes DESCRIPTION
, res_name_sbb RES_NAME
, ADDR1_HSE ADDRESS
, job_typ_ojb JOB
, job_class_ojb TYP
, order_no_ocr NUMB
, ls_chg_op_id_ocr OPR
, CommissionAMT NCommissionAMT
, COUNT(DISTINCT order_no_ocr) QTYW
from Parser_ocr
Inner Join Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE
Inner JOIN csg_hist.dbo.sbb_base on hse_key_ocr = hse_key_sbb
Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 13, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 13, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT
union all
select ir_tech_ojb TECHN
, substring (compl_cde_ojb, 16, 3) RESCODE
, compl_dte_ojb COMPLET
, CodeDes DESCRIPTION
, res_name_sbb RES_NAME
, ADDR1_HSE ADDRESS
, job_typ_ojb JOB
, job_class_ojb TYP
, order_no_ocr NUMB
, ls_chg_op_id_ocr OPR
, CommissionAMT NCommissionAMT
, COUNT(DISTINCT order_no_ocr) QTYW
from Parser_ocr
Inner Join Parser_ojb on order_no_ocr = order_no_ojb
Inner JOIN RGV_Codes on substring (compl_cde_ojb, 1, 3) = CODE
Inner JOIN csg_hist.dbo.sbb_base on hse_key_ocr = hse_key_sbb
Inner JOIN csg_hist.dbo.hse_base on hse_key_ocr = hse_key_hse
where substring (compl_cde_ojb, 16, 3) = CODE
and ir_tech_ojb between 950 and 999
and compl_dte_ojb BETWEEN '2007-01-09' AND '2007-01-22'
and prin_ocr = 8600
GROUP BY ir_tech_ojb, substring (compl_cde_ojb, 16, 3),compl_dte_ojb,CodeDes, res_name_sbb, ADDR1_HSE,
job_typ_ojb, job_class_ojb, order_no_ocr, ls_chg_op_id_ocr, CommissionAMT)
group by rollup(TECHN, RESCODE,(COMPLET, DESCRIPTION, RES_NAME, ADDRESS, JOB, TYP, NUMB, OPR))
order by 1, 2,3, 5;
Msg 156, Level 15, State 1, Line 150
Incorrect syntax near the keyword 'group'.
Could the error message be in the INNER JOIN?
What do I need to check for next.
Thanks for eveyrone's help so far.
Kurt
View 20 Replies
View Related
Feb 28, 2008
I've got a problem with this query. I'm not great at joins and so I'm not sure how to fix this.
I have items not in the CMO table (the last join), but I still want them to at least show up. They're being excluded:
SELECT OM.DOCNUM, OM.NAME_LAST, OM.NAME_FIRST, LV.DESCRIPTION AS Sex, ' ' AS Mandatory, CASE WHEN (LSI.Q1 = 1 OR
LSI.Q2 = 1 OR
LSI.Q3 = 1 OR
LSI.Q4 = 1 OR
LSI.Q5 = 1 OR
LSI.Q6 = 1 OR
LSI.Q7 = 1 OR
LSI.Q8 = 1 OR
LSI.Q9 = 1 OR
LSI.Q10 = 1 OR
LSI.Q12 = 1 OR
LSI.Q13 = 1 OR
LSI.Q14 = 1 OR
LSI.Q15 = 1 OR
LSI.Q16 = 1 OR
LSI.Q33 = 1 OR
LSI.Q34 = 1 OR
LSI.Q39 = 1 OR
LSI.Q39 = 0 OR
LSI.Q40 = 1 OR
LSI.Q40 = 0 OR
LSI.Q51 = 1 OR
LSI.Q51 = 0) THEN 'Y' ELSE ' ' END AS Optional, OM.CASE_MANAGER_ID_U AS CASE_MANAGER_ID,
CASE WHEN (CMO.REMOVAL_RSN_ID_LV = 1022) THEN 'Y' ELSE ' ' END AS Active, CASE WHEN (CMO.REMOVAL_RSN_ID_LV = 1015)
THEN 'Y' ELSE ' ' END AS Complete
FROM dbo.OFFENDER_MAIN AS OM LEFT OUTER JOIN
dbo.LOOKUP_VALUES AS LV ON LV.ID = OM.GENDER_ID_LV LEFT OUTER JOIN
dbo.LSI_ANSWERS AS LSI ON LSI.DOCNUM = OM.DOCNUM LEFT JOIN
dbo.COMMUNITY_MOD_OBJ AS CMO ON CMO.DOCNUM = OM.DOCNUM
WHERE (LSI.COMIT_ACTIVE = 1) AND (CMO.OBJECTIVE_CODE_ID_LV = 1011) AND (CMO.REMOVAL_RSN_ID_LV = 1022 OR
CMO.REMOVAL_RSN_ID_LV = 1015)
AND (OM.FACILITY_ID_F <> 1)
View 1 Replies
View Related
Oct 15, 2007
I have a data migration script that takes about an hour half to complete. The script conists of several insert statements that pull data from another sqlserver database while doing a fair amount of manipulation.
If I put a "go" in after every insert statement. The job finishes in 30Seconds. can some explain this huge performance change.
View 1 Replies
View Related
Jul 31, 2007
Hello,
I have finish building my ASP.NET site using VS 2005 and it compile just fine. Than I try hosting it to the web via IIS v5.1 and I am getting permission error from SQL express.
I have posted a detail question in experts-exchange.com and since you need a user account to even view the thread, I have taken the liberty to save the thread as .html file and host it to my ISP site. You can see the detail problem I am having here: http://users.accesscomm.ca/mm/EEdetail.html
I am going to list out some stuff I have done:
1. I have two database that need access, the ASPNETDB.mdf for user login and another database (PhotoDataBase.mdf) to store information relating to the photo I uploaded to my ASP.NET site.
2. I have already follow this How to site: http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx and setup:
- TCP/IP enable, as shown here:
- I have started SQL Browser service
- The firewall part I am not so sure about. I am currently using Zonealarm....
3. I have installed SQL Server Management Studio Express(SSMSE) and attach the two database to it.
4. I have set SSMSE server properties to accept both SQL and window authentication.
5. I have made sure that both database "read only" setting to false
6. I have setup SQL login with User ID = SQLLOGIN
Here is the original connection in web.config:
<add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|Da
taDirectory|PhotoDataBase.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
and here is the new connection I just type in web.config that replace the original one:
<add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|PhotoDataBase.mdf; Server=MINGDESKTOP; Integrated Security=False; uid=SQLLOGIN; Password=XXXXXX;" providerName="System.Data.SqlClient" />
and the error i get are the follow:
Server Error in '/' Application.An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.Exception Details: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)Source Error:An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
7. I did haven't done much to ASPNETDB.mdf yet and the error i get right now are the following:
________________________________________
__________________
Server Error in '/' Application.
Cannot open user default database. Login failed.
Login failed for user 'MINGDESKTOPASPNET'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot open user default database. Login failed.
Login failed for user 'MINGDESKTOPASPNET'.
_______________________________________________
Please help! I am really running out of ideas.... and I need to have this setup in a few days....
Thanks!
View 4 Replies
View Related
Feb 21, 2008
Hello,
A while ago I installed SQL CE on my decive. Unfortunately, it crapped out at one point so I'm reinstalling what needs to be put back on. I'm pretty sure I just used the CAB files to do this (but I might be mistaken). In any case, I remember there being an application that allowed me to do queries, browse data, etc. on my device's databases... I'm pretty sure it was just something included with SQL CE.
I already have the CF installed. I have all of the SQL CE CABs installed as well (I think)... but for some reason I simply cannot remember what CAB or other installation that would contain the browser, query creation thing, and so on...
Anyone have any idea what I'm rambling on about? Thanks!
View 1 Replies
View Related
