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.
I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?Thanks in advance.
View 4 Replies View RelatedHi,I am working on SQL Server.
I have a table with columns that need to be broken down. I nees to break this table:
TelNo1 TelNo2 TelNo3
555-44-33 555-43-88 555-46-89
into:
Tel_Number
555-44-33
555-43-88
555-46-89
I tried using the union statement but i just did'nt succed. Can anyone help me,please
Hi,
I have 4 tables :
Code Block
Create Table #Request ( [requestid] int , [stateno] nvarchar(5) , [cityno] int , Callid int, UniqueNo int);
Create Table #RequestDetail(reqdetailid int, [customername] Varchar(20), [customerage] int, requestid int);
Create Table #Call(Callid int,Calltype int,callDetailid int )
Create Table #CallDetail(callDetailId int,empid int)
Insert into #CallDetail VALUES(12123,1)
Insert into #CallDetail VALUES(53423,1)
Insert into #CallDetail VALUES(6532,1)
Insert into #CallDetail VALUES(82323,1)
Insert into #CallDetail VALUES(124235,1)
Insert Into #Call VALUES(111,1,12123)
Insert Into #Call VALUES(112,1,53423)
Insert Into #Call VALUES(114,1,6532)
Insert Into #Call VALUES(123,2,6532)
Insert Into #Call VALUES(134,1,124235)
Insert Into #request Values('324234','SA023',12,111,0);
Insert Into #request Values('223452','SA023',12,112,0);
Insert Into #request Values('456456','SA024',12,114,0);
Insert Into #request Values('22322362','SA024',44,123,0);
Insert Into #request Values('22654392','SA023',12,134,0);
Insert into #requestdetail values(1, 'Bill',23,'324234')
Insert into #requestdetail values(2, 'Tom',25,'223452')
Insert into #requestdetail values(3, 'Bobby',27,'456456')
Insert into #requestdetail values(4, 'Guck',29,'22322362')
Insert into #requestdetail values(5, 'Bobby',33,'22654392')
1. My stored proc will take the CallDetailID.
2. I have to find out the requests made on this calldetail.
3. After getting the request, i want to take the Customername, cityno of the request/requestdetail and pass it again to #request table to search for any duplicates within the request.
4. If found return the details of the original record :
[A similar requirement was solved earlier, but the structure has changed. This is a seperate requirement with different table strucure. Hence a new post. ]
thanks
I have installed the Adobe iFilter 11 64 bit and set the path to the bin folder. I still cannot find any text from the pdf files. I suspect I am missing something trivial because I don't find much when I Bing for this so it must not be a common problem.Here is the code.
--Adobe iFilter 11 64 bit is installed
--The Path variable is set to the bin folder for the Adobe iFilter.
--SQL Developer version 64 bit on both Windows 7 and Windows 8.
USE master;
GO
DROP DATABASE FileTableStudy;
GO
CREATE DATABASE FileTableStudy
ON PRIMARY
[code]....
Hi Guys
I have 4 databases in the same space.My users use all of them and use the same username and password to log into these 4 databases.In each of these databases,i have put a control table to allow me to keep track of all users that have to reset their passwords.
The control table consists of the username and flag fields.When the flag is ON(1) the user is forced to reset thier password and if the flag is OFF(0) they are not.
When a user logs into any one of these databases and they have to reset thier password,how do i make sure that all the other tables in other databases are also updated to make sure that the user is not forced to reset their password again when they log into those other databases later since they are using the same username and password for all databases.
I am planning to use a stored procedure which i will put into all the four databases and when a user logs in and has to reset their password,that sproc is called and automatically updates all the other 3 tables in other 3 databases.
Some SQL examples will be very appreciated.
Thanks.
I have a table that contains words that will be used to search another table where FullText index has been created on searchable columns. I'm basically trying to run something like this:
SELECT t1.col1, t2.col3
FROM tbl1 t1, tbl2 t2
WHERE CONTAINS (t1.col1, t2.col1)
I know this won't work but is there a way to join these two tables so the words (t2.col1) can be passed as search conditions? There is no common key on both tables so normal join won't work. I'm trying to find a way to pass the search words from one table to another.
I have a number of complex search stored procedures that use the following syntax to try to simplify the code.
WHERE @SearchParam IS NULL OR SearchCol = @SearchParam
unfortunately it appears that this is really inefficient as far as the database is concerned.
If I run the following query on the AdventureWorks database (SQL Server 2005 with SP2 and fixes up to v3054)
Code Block
SET STATISTICS IO ON
Declare @CustomerID int
SET @CustomerID = 1
SELECT SalesOrderID
FROM Sales.SalesOrderHeader
WHERE @CustomerID IS NULL OR CustomerID = @CustomerID
SELECT SalesOrderID
FROM Sales.SalesOrderHeader
WHERE CustomerID = @CustomerID
Is see that the first select results in 45 logical reads, whereas the second results in only 2 logical reads.
Does anyone have any idea how I can get the benefit of a search procedure that does not have loads of IF blocks, or dynamic SQL without this major performance issue?
Cheers,
Justin
I have a stored proc which is doing a search from a few tables by doing an inner join. When I enter a new search string, the proc takes some time to get the results. Whereas when I run this again after a few times, it gives results immediately. I know it is due to proc cache but still is there any way to sor this out.
Another problem is that, if a enter a string which is not available in the database itself then it takes a very long time. But takes relatively less time if the string exists in the database.
Any help is appreciated.
Cheers,
SpaceWars.
I am facing some performance issues in a Stored Procedure. The procedure
needs to return a resultset based on some search criteria. There are around
20 possible search criteria. Below is the SQL query used in my Stored
procedure. Any help to optimize the search will be great:
--get LOV details in table variables
INSERT INTO @tblLov (LovCode, LovDesc, ParamCode)
SELECT LovCode, LovDesc, ParamCode FROM tp_Lov WITH (NOLOCK)
WHERE ParamCode IN('FileSrc', 'CommTrailInd', 'CommTxnStatus',
'AgencyPrincipalInd','ProdSubType','AuditTransStatus')
--get commission transaction according to the search criteria
INSERT INTO @tblSearchResults
SELECT l1.LovDesc AS TransSource,
l2.LOVDesc AS CommTrailInd,
r.RemitCode as RemitNumber,
t.IntTransId as TransNumber,
CONVERT(VARCHAR, t.TrdDt, 110) AS TradeDate,
CONVERT(VARCHAR, t.SettlementDt, 110) AS SettlementDate,
rp.RepCode,
(ISNULL(rp.LstNm,'') + ', ' + ISNULL(rp.FstNm,'')) AS RepName,
(CASE WHEN ISNULL(t.IntClntId,0)=0
THEN ISNULL(t.ClntShortNM, '') +
(CASE WHEN (t.TransSrc = 'NSM' OR (t.TransSrc = 'MCE' AND
ISNULL(t.ProdType,'') <> 'VA')) AND ISNULL(t.FundAcctNum,'')<>'' THEN ' - ' +
ISNULL(t.FundAcctNum,'')
WHEN (t.TransSrc = 'NSV' OR (t.TransSrc = 'MCE' AND ISNULL(t.ProdType,'')
= 'VA')) AND ISNULL(t.PolicyNum,'')<>'' THEN ' - ' + ISNULL(t.PolicyNum,'')
WHEN t.TransSrc IN('PSH','MSR') AND ISNULL(t.ClrHouseAcctNum,'')<>'' THEN
' - ' + ISNULL(t.ClrHouseAcctNum,'')
ELSE '' END)
ELSE dev.udf_COMM_PCD_GetClientName(t.IntClntId, t.IntTransId)
END) AS Client,
(CASE WHEN ISNULL(t.CUSIP,'')='' THEN t.ProdName ELSE p.ProdNm END) AS
[Product],
t.InvAmt AS InvestmentAmt,
t.GDC AS GDC,
t.ClrChrg AS ClearingCharge,
t.NetComm AS NetCommission,
(CASE WHEN t.Status IN(@strLov_TxnStatus_Tobepaid, @strLov_TxnStatus_Paid)
THEN dev.udf_COMM_PCD_GetPayoutRateString(t.IntTransId) ELSE '' END) AS
PayoutRate,
(CASE WHEN t.Status IN(@strLov_TxnStatus_Tobepaid, @strLov_TxnStatus_Paid)
THEN dev.udf_COMM_PCD_GetPayoutAmountString(t.IntTransId) ELSE '' END) AS
Payout,
l3.LOVDesc AS TransStatus,
t.Comments,
t.OrderMarkup AS BDMarkup,
t.IntTransId,
rp.IntRepId,
sch.SchCode,
t.IntClntId,
t.CUSIP,
t.RepIdValue AS RepAlias,
t.RepIdType,
t.SplitInd,
l4.LOVDesc AS AgencyPrincipalInd,
t.AgencyPrincipalFee,
t.EmployeeTradeInd,
t.ShareMarkup,
t.UnitsTraded,
s.SponsorNm,
CASE WHEN t.TransSrc = 'NSM' OR (t.TransSrc = 'MCE' AND
ISNULL(t.ProdType,'') <> 'VA') THEN ISNULL(t.FundAcctNum,'') --Production
Defect #873 & 877
WHEN t.TransSrc = 'NSV' OR (t.TransSrc = 'MCE' AND ISNULL(t.ProdType,'') =
'VA') THEN ISNULL(t.PolicyNum,'')
ELSE t.ClrHouseAcctNum END,
CASE WHEN ISNULL(t.ProdSubType,'') IN ('', 'Z') THEN 'Not Defined'
ELSE l6.LovDesc END AS ProdSubType, --t.ProdSubType,
l5.LOVDesc AS TransAuditStatus, --t.TransAuditStatus,
t.TransAuditStatus AS TransAuditStatusCode,
t.OriginalTransId,
t.RowId,
t.Status,
t.intParentTransId,
t.CancelTrdInd,
t.ClrChrgOverrideInd,
9999 AS AuditKey
FROM tr_CommTrans t WITH (NOLOCK)
INNER JOIN @tblLov l1 ON t.TransSrc = l1.LOVCode and l1.ParamCode = 'FileSrc'
INNER JOIN @tblLov l2 ON t.CommTrailInd = l2.LOVCode and l2.ParamCode =
'CommTrailInd'
INNER JOIN @tblLov l3 ON t.Status = l3.LOVCode and l3.ParamCode =
'CommTxnStatus'
INNER JOIN td_Remit r WITH (NOLOCK) ON t.IntRemitId = r.IntRemitId
LEFT OUTER JOIN @tblLov l4 ON t.AgencyPrincipalInd = l4.LOVCode and
l4.ParamCode = 'AgencyPrincipalInd'
LEFT OUTER JOIN @tblLov l5 ON t.TransAuditStatus = l5.LOVCode AND
l5.ParamCode = 'AuditTransStatus'
LEFT OUTER JOIN @tblLov l6 ON t.ProdSubType = l6.LOVCode AND l6.ParamCode =
'ProdSubType'
LEFT OUTER JOIN tm_BDProd p WITH (NOLOCK) ON t.CUSIP = p.CUSIP
LEFT OUTER JOIN tm_BDSponsors s WITH (NOLOCK) ON t.IntBDSponsorId =
s.IntBDSponsorId
LEFT OUTER JOIN tm_Reps rp WITH (NOLOCK) ON t.IntRepId = rp.IntRepId
LEFT OUTER JOIN tm_PayoutSch sch WITH (NOLOCK) ON t.IntSchId = sch.IntSchId
WHERE t.IntTransId = (CASE WHEN @intTransId IS NULL THEN t.intTransId ELSE
@intTransId END) AND
t.TransSrc = @strTransSrc AND
r.RemitCode = (CASE WHEN ISNULL(@strRemitCode,'')='' THEN r.RemitCode ELSE
@strRemitCode END) AND
ISNULL(t.SettlementDt,'01-01-1900') BETWEEN @dtmFromSettlementDt AND
@dtmToSettlementDt AND
ISNULL(t.TrdDt,'01-01-1900') BETWEEN @dtmFromTradeDt AND @dtmToTradeDt AND
t.CommTrailInd = (CASE WHEN @chrShowTrails='Y' THEN t.CommTrailInd ELSE 'C'
END) AND
t.Status = (CASE WHEN ISNULL(@strStatus,'')='' THEN t.Status ELSE
@strStatus END) AND
ISNULL(t.ClrHouseAcctNum,'') LIKE (CASE WHEN ISNULL(@strAccountId,'')=''
THEN ISNULL(t.ClrHouseAcctNum,'')
WHEN (@strTransSrc = 'PSH' OR @strTransSrc = 'MSR' OR @strTransSrc
= 'MSA') THEN @strAccountId
ELSE ISNULL(t.ClrHouseAcctNum,'') END) AND
ISNULL(t.FundAcctNum,'') LIKE (CASE WHEN ISNULL(@strAccountId,'')='' THEN
ISNULL(t.FundAcctNum,'')
WHEN @strTransSrc = 'NSM' THEN @strAccountId
WHEN @strTransSrc = 'MCE' AND ISNULL(t.ProdType,'')<>'VA' THEN
@strAccountId
ELSE ISNULL(t.FundAcctNum,'') END) AND
ISNULL(t.PolicyNum,'') LIKE (CASE WHEN ISNULL(@strAccountId,'')='' THEN
ISNULL(t.PolicyNum,'')
WHEN @strTransSrc = 'NSV' THEN @strAccountId
WHEN @strTransSrc = 'MCE' AND ISNULL(t.ProdType,'')='VA' THEN
@strAccountId
ELSE ISNULL(t.PolicyNum,'') END) AND
ISNULL(t.IntBDSponsorId,-1) = (CASE WHEN @intSponsorId IS NULL THEN
ISNULL(t.IntBDSponsorId,-1) ELSE @intSponsorId END) AND
ISNULL(t.ProdType,'') = (CASE WHEN ISNULL(@strProdType,'')='' THEN
ISNULL(t.ProdType,'') ELSE @strProdType END) AND
ISNULL(t.ProdSubType,'') = (CASE WHEN ISNULL(@strProdSubType,'') ='' THEN
ISNULL(t.ProdSubType,'') ELSE @strProdSubType END) AND
ISNULL(t.CUSIP,'') = (CASE WHEN ISNULL(@strCUSIP,'')='' THEN
ISNULL(t.CUSIP,'') ELSE @strCUSIP END) AND
ISNULL(rp.SSN, 0) = (CASE WHEN @numRepSSN IS NULL THEN ISNULL(rp.SSN, 0)
ELSE @numRepSSN END) AND
ISNULL(rp.RepCode,'') = (CASE WHEN ISNULL(@strRepCode,'')='' THEN
ISNULL(rp.RepCode,'') ELSE @strRepCode END) AND
ISNULL(rp.LstNm, '') = (CASE WHEN ISNULL(@strRepLstNm,'')='' THEN
ISNULL(rp.LstNm,'') ELSE @strRepLstNm END) AND
ISNULL(rp.FstNm, '') = (CASE WHEN ISNULL(@strRepFstNm,'')='' THEN
ISNULL(rp.FstNm,'') ELSE @strRepFstNm END) AND
ISNULL(rp.RepStatus,'') <> (CASE WHEN @chrIncludeTerminated='Y' THEN 'Z'
ELSE 'T' END) AND
ISNULL(t.IntClntId,-1) = (CASE WHEN @intClientId IS NULL THEN
ISNULL(t.IntClntId,-1) ELSE @intClientId END) AND
( (@chrAuditReportFlag = 'N' AND
t.Status NOT IN(@strLov_TxnStatus_Loaded, @strLov_TxnStatus_Cancelled) AND
ISNULL(TransAuditStatus,@strLov_TransAuditStatus_Active) =
@strLov_TransAuditStatus_Active
)
OR
(@chrAuditReportFlag = 'Y' AND
t.Status NOT IN(@strLov_TxnStatus_Loaded)
DefectID# 880,895
IN(@strLov_TransAuditStatus_Active, @strLov_TransAuditStatus_Cancelled)
)
)
have an excel file which contains lots of sheets. Some of them are named as DW-<day>-<month> (for e.g; DW-1-July). Like this I have sheets for the whole month. I have other sheets too with a different name. I would like to import data from these sheets only (DW ones). Upon my research I have found that this can be achieved via For Each Loop Container.
View 7 Replies View RelatedI am about to heavily index a table and have to include atleast 3 to 4 olumns in the fulltext index for this table.
The table is updated very frequently and the also the columns that are involve in the fulltext indexing undergo frequent updates.
As of now, I can't avoid using full text indexing as these columns are very very lengthy and basically contail text.
The users of the database will give some key words as the search criteria
to get infomation as to what they are looking for.
How frequently should I update my full text catalog.
This is a scenario where the full text is operating on various tables and
each of thses table might be containingaround 300,000 to 800,00 rows.
I would appreciate an intelligent siggestion as I need it as soon as possible.
Thanks and have a wonderful time ahead.
I have a table with 3M rows that contains a varchar(2000) field withvarious keywords. Here is the table structure:PKColumnImageIDFullTextColumnThere is an association table:ImageIDContractIDNow, I want to do a query where the ContractID = x and Contains someword in the FullTextColumn. There is an association table that mapsImages to Contracts - so I can't use the trick of putting the Contractcode in the FullTextColumn.I'm finding that first the FTS service is performing a search on theKeyword (which can take a long time if 100K rows are returned) thenjoining to the association table for the particular contract.Is there anyway to make this faster by telling the FTS service, onlysearch this subset of rows for the keyword based on the contract.Sorry if this sounds convoluted. Appreciate any help you can suggest.Thanks!
View 1 Replies View RelatedWaiting my my 431 study package turning up, any offers on any addional books etc that would help me pass this exam?
Cheers up front.
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
Does anyone know how to how to performance case-insensitive search onXML data type in SQLServer 2005? Or I have to convert all the xml datato lower case before I store it?Thanks in advance.John
View 2 Replies View RelatedI 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.
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 RelatedI 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.
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
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
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,
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 RelatedI 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!
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
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.
I have one control table to store all related table name
Table ID TableName
1 TableA
2 TableB
In Table A:
RecordID Value
1 1
2 2
3 3
In Table B:
RecordID Value
1 1
2 2
3 3
How can I get the result by select the Table list first and then combine the data in table A and table B?
Thank you!
I'm just wonder if this is a bug in MS Search or am I doing something wrong.
I have a query below
declare @search_clause varchar(255)
set @Search_Clause = ' "hepatitis b" and "hepatocellular carcinoma"'
select * from results
where contains(finding,@search_clause)
I don't get the correct result at all.
If I change my search_clause to "hepatitis" and "hepatocellular carcinoma -- without the "b"
then i get the correct result.
It seems MS Search doesn't like the phrase contain one letter or some sort or is it a know bug?
Anyone know?
Thanks
Hey Gurus,
I have a problem on getting the sql statement which will group similar column name and sum their number together(another column).
A million thanks in advance.
Hi!
What is the difference in performance if I use a Temp-table or a local-table variable in a storedprocedure?
Why?
//Daniel
Hello,
I have an SQL database with a table. This table has two fields: [Title] and [Description].
In my web site I have a text box where the user inserts some keywords, as follows: 1 > book english 2 > book AND english 3 > book OR english
Is it possible to create an SQL procedure which looks which records have the words "book" and "english" in the fields [Title] or [Description]?
And is it possible to somehow also use the words "AND" and "OR"?
I never did something like this and I just would like to create a simple search in my web site which looks into my documents table.
Thank You Very Much,Miguel
Is there a simple script to search an entire table for a particular string (ex. a name) then list the column there in
View 1 Replies View Relatedhas anyone figure out how to do this?
the closes ive come is this (found on google):
declare @sql nvarchar(4000)
set @sql = ''
select @sql = @sql + 'exec ' + name + '..sp_msforeachtable @command1='' print ''''use ' + name + ' select * from ^'''' '', @replacechar=''^''' + char(13)
from master..sysdatabases where dbid > 4
exec (@sql)
i want it to actually execute whats being printed? so that way i can search through each table in each database for something im after..
either way i dont think itll help
im trying to search all databases for a certain value... a personss name