Deadlocks Involving Parallelism
Jul 23, 2005
We're experiencing a large number of deadlocks since we began running
SQL Server 2000 Enterprise Edition SP3 on a Dell 6650 with hyper
threading intel processors. We don't have the same problem on Dell
6650's w/o the hyper threading. If I turn off the parallel query
processing option the deadlocks stop. I've tried all of the suggestions
from the Microsoft Knowledge Base under the following link -
http://support.microsoft.com/?kbid=837983
The only suggestion that actually yielded results was turning off
parallel query processing but I don't want to give up what should be a
performance advantage if it wasn't for the deadlocks. Query tuning and
index tuning hasn't helped. Any suggestions? I haven't applied SP4
yet. I'm wondering if anyone has seen the same problem resolved with
SP4.
*** Sent via Developersdex http://www.developersdex.com ***
View 4 Replies
ADVERTISEMENT
Apr 20, 2006
I know you can change the max degree parallelism server wide, but can you do it on the fly for one query? I know... trust the query processor but when I turn it off for this one sp, my query goes from 3 seconds to 0 and I got this ex-MS guy in here telling me there is a way, but he does not remember how.
I want him to simplify the sp or have his project's DBA do it, and I even offered to take a hack but.... you know.
View 2 Replies
View Related
Oct 29, 2001
Does anyone know about sqlserver's Parallelism.
a query without parallelism takes much less time as the one with parallelism, in my case it's 6 times faster without parallelism. If that's the true.
What do we need parallelism for?
Any ideas
Thanks
View 2 Replies
View Related
Jul 23, 2005
I have a function that returns a table of information aboutresidential properties. The main input is a property type anda location in grid coordinates. Because I want to get only acertain number of properties, ordered by distance from thelocation, I get the properties from a cursor ordered by distance,and stop when the number is reached. (Not really possible todetermine the distance analytically in advance.) The cursor alsoinvolves joins to a table of grid coordinates vs. postcodes (theproperties are identified mainly by postcode), and to a tablethat maps the input property type into what types to search for.Opening the cursor typically results in the creation of six toeight parallel threads, and takes approx 1 second, which is abouthalf of the total time for the function.Recently the main property table grew from 4 million to 6.5million records, and suddenly the parallelism is lost. Takingthe identical code and executing it as a script gives parallelism.Turning it into a SP that inserts into a #temp table and thenselects * from that table as the last statement also givesparallelism. But when it's in the form of a function, there isonly one thread -- and the execution time has gone from ~2 secto ~8 sec. I updated the statistics on the table, but stillno parallelism.I could turn it into a SP easily enough, but that would involvea change to the C++ program that calls it, which takes a whileto get through the pipeline. In the meantime, is there some wayto induce the optimizer to use parallelism? It used to.
View 3 Replies
View Related
Dec 15, 2006
hi,i've set 'max degree of parallelism' to 1 because some sql request hanged.Now when i connect, how can i set the parallelism to 4 for a session.Is there a command like this :'alter session set max degree of parallelism 4' ?ThanksPaul
View 6 Replies
View Related
Jul 20, 2005
If SQL Server is designed for multi processor systems, how can runninga query in parallel make such a dramatic difference to performance ?We have a reasonably simple query which brings in data from a few nonecomplex views. If we run it on our 2x2.4Ghz Xeon server it takes 6minutes plus to run. If we run this on the same server withOPTION(MAXDOP 1) at the end of the same query it takes less than asecond.Examining the execution plan, the only difference I have been able tosee is that parallelism is taking up 96% of the run time when usingtwo processors. This drops when using the one so a sort takes up thevast majority of the time for the query to run.OK, so running in parallel should mean that it's run in various partsand then 'joined up' later for performance gains, but how can it getit so wrong (timewise) ?If this is the case, will I see a significant difference changing ourserver to use a single processor, which seems completely the wrongapproach (or should I do this on each query in each app - eek) ?Do we have a problem that we don't know about that causes it to takethis long ?What can we do ? Ideally, using both processors would seem to bepreferrable.
View 2 Replies
View Related
Oct 16, 2006
Hi,
I would just like to confirm something with you guys...
Am I correct in saying that you dont need multiple connections to the same DB in a SSIS package in order to achieve parallel processing across multiple SQL tasks. In other words, I have 2 SQL tasks executing different stored procedures on the same DB that I want to run in parallel. They should be able to share one connection and still process in parallel, correct?
With that in mind, would the processing be faster if they each had their own connection?
Thanks in advance.
View 1 Replies
View Related
Mar 16, 2007
after running query at first time working all processes
but later 2-3 sec. working only one
SQL 2005
Hewlett Packard DL580 (16 processes)
What is ideas?
View 6 Replies
View Related
Sep 2, 2015
I have SQL Server Version:-
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Â Jun 28 2012 08:36:30 Â Copyright (c) Microsoft Corporation Express Edition with Advanced Services (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
Â
This is just an UAT server which has OS and hardware detail below:-
OS :- Windows Server 2008 R2 Standard
SP:- SP1
Processor :- Intel(R) Xeon(R) CPU Â X5650 @2.67GHz 2.66 GHz
RAM : - 4 GB
Bit - 64 bit
I want to set the value to max degree of parallelism, what value should i configure for the same?
Below is the snap property of SQL instance >> Processor
View 3 Replies
View Related
Jul 23, 2005
Hi,I have a sql 2000 server with 8 processors, server settings are asdefault. I read on Technet that it is good practise to remove thehighest no. processors from being used for parallelism, correspondingto the no. of NICs in the server. One of our 3rd party developers hasrecommended only allowing one processor to be used as there is aperformance hit by the server working out which processor to use. Doesanyone have a definitive answer to this? I suspect he's wrong but I'dlike some hard evidence if possible, thanks.Kev.
View 3 Replies
View Related
Jun 16, 2006
Hi,
Is it possible to achieve partition parallelism in SSIS? What I am asking is, In DataStage, if I load some data like 'data reader -> trans1 -> trans2 -> destination' (and assume that I have 4 nodes configured), the tool divides the data into 4 different datasets and executes the package as 4 instances. This way the data load is very fast. Is it possible in SSIS?
Of course we can divide the dataset and load them thru multiple instances? But then dividing the dataset will differ for every load and so we need to modify the package all the time. Even if we divide the dataset, I am not sure 4 instances will run in 4 different nodes or in a same node? So anybody has any idea about it?
Thanks.
View 4 Replies
View Related
Jul 7, 2006
Hi,
In my package I have a source, a script component to make some changes to that and a destination. To speed up the process, within a data flow, I have created 6 copies of the above components and running them in parallel. Each source takes different set of data. I have divided the data using the record no such that, each set will read 1million records.
Now, my question is, though each pipleline is supposed to process exactly 1million records, they are not running at the same speed. For example, 1 pipeline completes processing all 1million records whereas another pipeline processed only 250000 records in that time. I don't see any reason for why one should run slow while another is running fast considering that both are doing the same thing?
Do you have any idea about this?
Thanks.
View 6 Replies
View Related
May 6, 2008
Hi,
actually a sever has a parallelism of 4 I would like to set the parallelism for a specific user to 2 without changing the code of the users application.
Is this possible.
As far as I understand with plan guides you just provide sql statements.
Need I to find all queries from the user, and add plan guides for all the queries, or tis there a more elegant way to do it.
br
fari
View 1 Replies
View Related
Dec 9, 2004
Hello,
I have a DTS package that migrates data from Excel to Access to SQL Server. Before that occurs, at the very beginning, I have a delete script that deletes the existing data in the Access database. When I hit that first step, it gives me the error:
Microsoft JET Database Engine (-2147467259) - The Microsoft Jet database engine cannot open the file '<path to access .mdb>'. It is already opened exclusively by another user, or you need permission to view its data.
I checked the permissions and the account that the web site is running under (impersonated) has full permissions to the file. Also, I am running the web site on my machine, I'm triggering the DTS package through .NET code, the access database is on a file/print server, and the SQL Server is a separate machine.
I'm thinking that maybe the issue is that the access database is on a separate machine, but I'm not quite sure.
Brian
View 2 Replies
View Related
Apr 30, 2008
Hi All,
I have a table called [History] with the following fields and types.
Date(datetime), PriceZone(varchar), ProductID(varchar), ProductName(varchar), Category(varchar), SubCategory(varchar), Price(decimal 2 places), Cost(decimal 2 places), Units(Big Int), DollarSales(decimal 2 places), Margin(decimal 2 places), Profit(decimal 2 places)
Each record represent information for each ProductID, per PriceZone, per Date. There's always one record per product; per zone; per Date.
Here is the sample of the records (Just for example I am just using one product in one zone)
Date, PriceZone, ProductID, ProductName, Category, SubCategory, Price, Cost, Units, DollarSales, Margin, Profit
2008-04-30,Zone1,001,Lays Chips,Snacks,Chips,2,1.5,10,20,25,5
2008-04-29,Zone1,001,Lays Chips,Snacks,Chips,2,1.5,8,16,25,4
2008-04-28,Zone1,001,Lays Chips,Snacks,Chips,2,1.5,15,30,25,7.5
2008-04-25,Zone1,001,Lays Chips,Snacks,Chips,2,1.5,13,26,25,6.5
2008-04-22,Zone1,001,Lays Chips,Snacks,Chips,2,1.5,11,22,25,5.5
2008-04-21,Zone1,001,Lays Chips,Snacks,Chips,1.98,1.5,15,29.7,24.24,7.2
2008-04-20,Zone1,001,Lays Chips,Snacks,Chips,1.98,1.5,15,29.7,24.24,7.2
2008-04-19,Zone1,001,Lays Chips,Snacks,Chips,1.95,1.5,18,35.1,23.08,8.1
2008-04-16,Zone1,001,Lays Chips,Snacks,Chips,1.9,1.5,20,38,21.05,8
2008-04-15,Zone1,001,Lays Chips,Snacks,Chips,1.8,1.5,40,72,16.67,12
2008-04-10,Zone1,001,Lays Chips,Snacks,Chips,1.8,1.5,32,57.6,16.67,9.6
2008-04-09,Zone1,001,Lays Chips,Snacks,Chips,1.8,1.5,27,48.6,16.67,8.1
2008-04-08,Zone1,001,Lays Chips,Snacks,Chips,1.8,1.4,29,52.2,22.22,11.6
2008-04-01,Zone1,001,Lays Chips,Snacks,Chips,1.8,1.4,30,54,22.22,12
Now using the table information, I want to run a script to get the following fields;
Date, PriceZone, ProductID, ProductName, Category, SubCategory, Price, Cost,UnitsSum_ForLast4Dates, UnitsSum_ForLast12Dates, DollarSalesSum_ForLast4Dates, DollarSalesSum_ForLast12Dates, AvgMargin_ForLast4Dates, AvgMargin_ForLast12Dates, ProfitSum_ForLast4Dates, ProfitSum_ForLast12Dates
So output in the above example would be;
Date, PriceZone, ProductID, ProductName, Category, SubCategory, Price, Cost,UnitsSum_ForLast4Dates, UnitsSum_ForLast12Dates, DollarSalesSum_ForLast4Dates, DollarSalesSum_ForLast12Dates, AvgMargin_ForLast4Dates, AvgMargin_ForLast12Dates, ProfitSum_ForLast4Dates, ProfitSum_ForLast12Dates
2008-04-30,Zone1,001,Lays Chips,Snacks,Chips,2,1.5,46,224,92.00,424.70,25,22.30,5.00,88.70
Here;
Date is the maximum date available in the entire [History] table. Meaning the "Date" value will be same for all the records in the output.
Price is the Price for that product in that zone on Date
Cost is the cost for that product in that zone on Date
About "Last4Dates" & "Last12Dates":
"Last4Dates" is the last 4 dates in the entire table from 'Date" value , including "Date" value and prior 3 dats before the "Date" value. In the above data set example it would be from "2008-04-25" to "2008-04-30"
"Last12Dates" is the last 12 dates in the entire table from 'Date" value, including "Date" value and prior 11 Dates before the "Date" value. In the above data set example it would be from "2008-04-09" to "2008-04-30"
UnitsSum_ForLast4Dates and UnitsSum_ForLast12Dates is the sum of Units for 4 and 12 weeks respectively; per product, per zone
DollarSalesSum_ForLast4Dates and DollarSalesSum_ForLast12Dates is the sum of DollarSales for 4 and 12 weeks respectively; per product, per zone
AvgMargin_ForLast4Dates and AvgMargin_ForLast12Dates is the Average of DollarSales for 4 and 12 weeks respectively; per product, per zone
ProfitSum_ForLast4Dates and ProfitSum_ForLast12Dates is the sum of Profit for 4 and 12 weeks respectively; per product, per zone
How Can I achieve this in SQL?
Can someone please help me as soon as possible.
Thanks,
Zee
View 3 Replies
View Related
Jul 20, 2005
Hi,I have a tricky sql statment I have to write (tricky for me) and I amstuck. I'm having trouble with the following problem.Table1 (Column a, Column b, Column c)Table2 (Column a, Column b, Column c)Table3 (Column a, Column b, Column c)Table1 contains a row of value (1, 2, 3)Table2 contains a row of value (4, 5, 6)Table3 contains a row of value (7, 8, 9)What I need to do is write a single SELECT statement (embedded selectsand so forth are allowed) to produce the table:TableNew (Column a, Column b, Column c)TableNew contains a row of value (1, 2, 3)and a row of value (4, 5, 6)and a row of value (7, 8, 9)I've been playing with the FULL OUTER JOIN and I getTableNew (Table1.Column a, Table1.Column b, Table1.Column c,Table1.Column a, Table1.Column b, Table1.Column c,Table1.Column a, Table1.Column b, Table1.Column c)TableNew contains a row of value (1, 2, 3, null, null, null, null,null, null)and a row of value (null, null, null, 4, 5, 6, null,null, null)and a row of value (null, null, null, null, null, null,7, 8, 9)Or I get only 1 row of data that looks like (1,2,3,4,5,6,7,8,9)If someone has the solution to this problem, please let me know.Thanks.
View 4 Replies
View Related
Oct 26, 2007
Hi,
I need some urgent help on a conceptual question.
Requirement:
I have a simple package with one dataflow task. In that I need to read from a sql table and for every row in that table loop through n times and generate new output rows based on certain conditions (which are best evaluated in custom script as they are rather complex). Hence, if I have 100 rows in the table as my input, I may end up with 100*n rows as output.
My Design:
To implement this I have used an OLE DB Source which outputs to a Script Transform (ST). In the ST I intend to loop through in custom code and generate new rows using the .AddRow feature when I need new rows. This ST then feeds into another OLE DB Destination which writes the data to the table. Simple!
I am using the default buffer settings. All I have tweaked is the Synchronous... property on the script transform (otherwise I do not get to the Output0Buffer within the script!).
Problem:
I wish to do as much as possible in parallel. So I would expect the OLE DB Source to provide more than one row at a time to the script transform and that should process more than one input row simultaneously. It seems the script componenet is serializing input, so it seems to take one row at a time from the OLE DB source, loop through and process in the script transform).
AM I RIGHT IN THINKING THAT THE SCRIPT TRANSFORM IS EXECUTING THE INPUT IN A SEQUENTIAL MANNER?
CAN I PARALLELISE THIS?
If so, how?
Thanks,
Tirthankar
View 3 Replies
View Related
Jan 8, 2005
Hello, this is probably the most helpful forum I have found on the Net in awhile and you all helped me create a DB for my application and I have gotten kind of far since then; creating stored procedure and so forth. This is probably very simple but I do not yet know the SQL language in depth to figure this problem out. Basically I have a printer monitor application that logs data about who is printing (via logging into my app with a passcode, which is located in the SQL DB), what printer they are using, and the number of pages. I have 3 tables, one called 'jobs' which acts like a log of each print-job, a user table (which has data like Name=HR, Passcode=0150) and table listing the printers. Each table uses an integer ID field which is used for referencing and so forth. Tables were created with this command sequence:
create table [User_Tbl](
[ID] int IDENTITY(1,1) PRIMARY KEY,
[Name] varchar(100),
[Password] varchar(100),
)
go
create table [Printer_Tbl(
[ID] int IDENTITY(1,1) PRIMARY KEY,
[Name] varchar(100),
[PaperCost] int
)
go
create table jobs(
[JobID] int IDENTITY(1,1) PRIMARY KEY.
[User_ID] int,
Printer_ID int,
JobDateTime datetime,
NumberPrintedPages int,
CONSTRAINT FK_User_Tbl FOREIGN KEY ([User_ID])
REFERENCES [User_Tbl]([ID]),
CONSTRAINT FK_Printer_Tbl FOREIGN KEY ([Printer_ID])
REFERENCES Printer_Tbl([ID])
)
go
I need display some data in a datagrid (or whatever way I present it) by using a query. I can do simple things and have used a query someone on here suggested for using JOINS, and I understand but I can't figure out how to make this particular query. The most necessary query I need for my report needs to look like this: (this will be from a data range @MinDate - @MaxDate)
Username PagesOnPrinter1 PagesOnPrinter2 TotalPagesPrinted Cost
--------- ---------------- --------------- ---------------- ----
HR 5 7 12 .84
Finance 10 15 25 1.75
So it gives the username, how many pages printed on each printer, the total pages printed, and the total cost (each printer has a specific paper cost, so it is like adding the sum of the costs on each printer). This seems rather simple, but I cannot figure out how to translate this to SQL.
One caveat I have is that the number of printers is dynamic, so that means the the columns are not really static. Can this be done? And if so how can I go about it? Thanks everyone!
View 2 Replies
View Related
May 11, 2007
I have three tables:
1. RubricReport 2. RubricReportDetail and 3. RubricReportTemplate
RubricReportDetail has columns ReportID and Age
RubricReportTemplate has columns IndicatorID and Salary
I want to insert a row in RubricReport Table with ReportID = @ReportID (RubricReportDetail) and IndicatorID = @IndicatorID (of RubricReportTemplate). How can I do this?
Kindly help me with this. Thanks in advance.
View 1 Replies
View Related
Oct 25, 2007
I am one very frustrated beginner. If it were not for wonderful information in this forum I would have taken early retirement by now!
Table contains information about new and departing computer and phone users in several departments which we support. This is an existing table which I'm trying to clean up.
The essential part:
CREATE TABLE [dbo].[HelpDesk_NewUser](
[AutoNumber] [int] IDENTITY(1,1) NOT NULL,
[SubmittedDate] [datetime] NULL,
[FirstName] [varchar](100) NOT NULL,
[LastName] [varchar](100) NOT NULL,
[Department] [varchar](100) NOT NULL,
[StartDate] [datetime] NULL,
[DepartDate] [datetime] NULL
[RequestedBy] [varchar](100) NOT NULL,
[UpdatedBy] [varchar](16) NOT NULL,
[CurrentStatus] [varchar](10) NOT NULL ,
[DateCurrentStatus] [datetime] NULL,
[FormType] [varchar](10) NOT NULL,
) ON [PRIMARY]
There can be more than one record per FirstName, LastName. FormType can be N for new or D for departing.
I want to do this for each record:
Read the FormType and Department from the record with the most recent activity (SubmittedDate) for each user
Convert Department to a 4 character department number
Update CurrentStatus with (FormType concatenated with the 4 character dept number) in all records for that user.
I have created another table called UserMostRecent which contains the most recent record for each user.
I have written a query to do this by brute force (read a record, set local variables, update a record), but I would like learn a simpler way to do it. I don't understand the syntax of CASE because it seems to change depending on where it is used.
Here is what I have tried that does not work. Error is "Incorrect syntax near word CASE"
UPDATE HelpDesk_NewUser
SET DateCurrentStatus = b.DateMostRecent,
CurrentStatus = (b.FormType + a.Department
CASE
WHEN 'Roads Department' THEN '3000'
WHEN 'Engineering and Survey Services' THEN '1900'
WHEN 'Waste Management' THEN '8999'
WHEN 'Kern Air Pollution Control District' THEN '9149'
WHEN 'Environmental Health' THEN '4113'
WHEN 'Building Inspection' THEN '2625'
WHEN 'Animal Control' THEN '2760'
WHEN 'Planning Department' THEN '2750'
WHEN 'Community and Economic Development' THEN '5940'
WHEN 'Resource Management Agency' THEN '2730'
WHEN 'Code Compliance' THEN '2620'
WHEN 'Roads Kern Regional Transit' THEN '8998'
END)
FROM HelpDesk_NewUser a JOIN UserMostRecent b
ON (a.LastName = b.LastName and a.FirstName = b.FirstName)
Thank you, forum participants. You are the best!
View 4 Replies
View Related
Jan 6, 2008
Here are the tables I am dealing with:
Tables
license TABLE
---------------
license_id int PK
vendor_id int FK
po_id int FK Nulls
license_type_id int FK
lic_user_id int FK Nulls
location_id int FK Nulls
lic_start_date smalldatetime Nulls
days_allowed int Nulls
serial_num varchar(50) Nulls
activation_key varchar(50) Nulls
max_users int Nulls
comments varchar(1000) Nulls
software TABLE
------------------
software_id int PK
os_id int
software_name varchar(150)
comments varchar(1000) Nulls
software_license TABLE
--------------------------
license_id int PK , FK
software_id int PK , FK
comments varchar(1000) Nulls
dept_license TABLE
---------------------
dept_id int PK , FK
license_id int PK , FK
comments varchar(1000) Nulls
purch_order TABLE
---------------------
po_id int PK
po_number varchar(50)
po_date smalldatetime
comments varchar(1000) Nulls
dept_purch_order TABLE
----------------------------
po_id int PK , FK
dept_id int PK , FK
comments varchar(1000) Nulls
I am using the following stored proceedure to try to create a useful report from those tables:
Code Block
ALTER PROCEDURE [dbo].[ListDepartmentLicenses]
@Department int,
@Software int
AS
BEGIN
SET NOCOUNT ON;
SELECT dept_license.license_id,
license.lic_start_date,
department.dept_name,
purch_order.po_number,
software.software_name
FROM dept_license
JOIN license
ON dept_license.license_id = license.license_id
JOIN department
ON dept_license.dept_id = department.dept_id
JOIN dept_purch_order
ON dept_purch_order.dept_id = dept_license.dept_id
JOIN purch_order
ON dept_purch_order.po_id = purch_order.po_id
JOIN software_license
ON dept_license.license_id = software_license.license_id
JOIN software
ON software_license.software_id = software.software_id
WHERE (department.dept_id = @Department or @Department Is Null) and
(software.software_id = @Software or @Software Is Null)
ORDER BY license.lic_start_date
END
The sproc compiles fine but I get no rows returned.
View 7 Replies
View Related
May 7, 2007
Hi All,
I have written ETL software that runs on SQL Server. We are running it for the first time on a 4cpu (2 x dual core) machine on sql server 2005.
One of the things this software does is perform a 'select * from tablename' to validate that the tables passed to it as parameters exist. This has worked fine on previous releases and on single cpu machines because what the optimiser decides to do is to return just the first page of data and then fetch more. I guess it even works in 2005 standard edition.
However, 2005 enterprise edition allows parallelism. And what the optimiser is deciding to do with such a query is to parallelise it and fetch all rows and then give the result back to the program. So, instead of seeing a fraction of a second to return the first page of data we are seeing up to 90 seconds and the database goes and fetches 15M rows in parallel.
Obviously, what we would like to do is to somehow tell the optimiser that this set of programs should not perform any parallel queries. Or, we would like to turn parallelism off on the specific tables we are dealing with for the period of running these ETL programs....they have no need of parallel processing at the database level for virtually all the calls that are performed.
Would someone please be so kind as to advise us if we can do something like pass a parameter to ODBC to stop parallelism or if we can issue commands against specific tables to stop parallelism for a period and then turn it back on?
Thanks in Advance.
Peter Nolan
www.peternolan.com
View 4 Replies
View Related
Jun 4, 2008
I seem to have a little problem with my SQL. I'm attempting to use a an .NET grid-view to page a list of search results using an ObjectDataSource (ODS). It's a very simple search setup. The ODS retrieves the Search keyword from the query-string sending it to the BLL's GetResult method which then utilises the DAL's version and retrieves the result. Not all of them of course because I'm paging so it only retrieves the first page (Lets say the first 10 items). This part of the SQL works fine as the same SQL method is used else where on the site and it's well tested. But to to use the Paging function I'm using the SelectCountMethod attribute of the ODS. Again this gets the Search term from the QueryString and sends it too the BLL which retrieves the information from the DAL. Now this is where it starts playing up. The Sql count method is as follows and is run as a Stored Procedure: 1 ALTER PROCEDURE dbo.StoreName_Store_GetProductSearchCount
2 (
3 @SearchQuery nvarchar
4 )
5 AS
6 SET NOCOUNT ON
7
8 SELECT COUNT(*)
9 FROM StoreName_Products
10 WHERE Title LIKE '%' + @SearchQuery + '%'
Nice and Easy. Not a Problem... Isn't it? When I run this query by selecting the database in the Server Explorer in VS2005 and running a 'New Query', it retrieves the correct results. (I only run this query from the Start of the SELECT Query in this mode, don't add the stuff above it)When I run this query through the website as intended and retrieves the int from the Stored Procedure, it always returns the entire count of the number of products in the table.At first I thought It might be a simple error with my caching system. Nope, it's not as after I purged it, it still returns the same value. I thought, well maybe just maybe the BLL or the DAL are some point converting it to this number but I'm quite sure it wasn't... and nope it wasn't that either because the ExecuteScalar method was found to be returning this number. So I ran the Stored Procedure by opening it up and simply right clicking the SQL Query and Executing it adding a value for @SearchQuery when it requested. Bingo. It's returning the full number of the products.So my question is... What's the deal? How can the same query return two completely different results? and most of all... How do I overcome this? Is this because it's in a stored procedure and I should be executing it as a SqlCommand from my DAL? I'm a bit confused. Is there a problem running this sort of query in a stored procedure that I don't know about and probably really should?Thankful for any answers,Regards,Luke
View 11 Replies
View Related
Mar 7, 2005
I am trying to write a stored procedure which would execute following logic:
- The stored procedure takes 2 optional parameters @StartDate and @EndDate
@StartDate Datetime = null
@EndDate Datetime = null
- Since the parameters are optional user can enter either one or can leave both blank.
- If user doesnot enter any values for SD (start date) and ED (end date), stored procedure should run select query replacing those values with wildcard character '%' or NULL
- If user enters SD, query should use @StartDate as the SD and GetDate() as the ED
- If user enters ED, query should use @EndDate as the ED and MIN() of the Date field as SD
I was able to write query which did almost everything as is stated above expect for incorporating NULLs
The query is as below
CREATE PROCEDURE SearchDocumentTable
@FName varchar(100) = null,
@LName varchar(25) = null,
@ID varchar(9) = null,
@StartDate Datetime = null,
@EndDate Datetime = null
AS
IF ( @StartDate IS NULL)
Select @StartDate = MIN(DateInputted) from Document
Select
FName as 'First Name',
LName as 'Last Name',
ID as 'Student ID',
Orphan as 'Orphan',
DocumentType as 'Document Type',
DocDesc as 'Description of the Document',
DateInputted as 'Date Entered',
InputtedBy as 'Entered by'
From Document,DocumentTypeCodes
Where FName LIKE ISNULL(@FName,'%')
AND LName LIKE ISNULL(@LName,'%' + NULL)
AND ID LIKE ISNULL(@ID,'%' + NULL)
AND (DateInputted BETWEEN @StartDate AND ISNULL(@EndDate,GETDATE()) OR DateInputted IS NULL)
AND Document.DocTypeCode = DocumentTypeCodes.DocTypeCode
GO
Any help would be appreciated
Thanks in advance :)
View 7 Replies
View Related
Feb 22, 2006
I want to create an UPDATE statement that updates a value in a table indatabase A based on the results of an inner join between tables eachresiding in deifferent databases.What is the correct syntax for doing this?The following should give you an idea of what I'm trying to do:UPDATE A.dbo.tblCarsSET A.dbo.tblCars.Car = 'Ferrari'FROMA.dbo.tblCars INNER JOIN B.dbo.tblHouses ONA.dbo.tblCars.RecID = B.dbo.tblHouses.RecIDWHERE (B.dbo.tblHouses = 'Mansion')
View 7 Replies
View Related
Apr 8, 2008
Hello friends,
I am inserting non-english strings into my database table from my java program.
Code Snippet
sql = "insert into static_string1 values (?)";
PreparedStatement statement=connection.prepareStatement(sql);
statement.setString(1,statString);
where, statString is a string variable containing Hebrew characters.
Till here, my code works fine. i.e, Hebrew characters are properly inserted to the database.
The problem is when I try to retrieve the String_Id based upon the statString I inserted to the table static_string1.
Code Snippet
String sql = "select String_Id from Static_String1 where String like ('" + statString +"')";
Statement statement=connection.createStatement();
ResultSet rs=statement.executeQuery(sql);
rs.next();
int stringId=rs.getInt("String_Id");
I tried hardcoding the string in the query and to execute it from the SQL Server Management Studio as below
Code Snippetselect String_Id from Static_String1 where String like( ' ×”×–×—' );
But even this is returning null rows, even though the entry is present in the table Please help me out asap.
Please pardon me if this is not the right section to post my doubt. I didnt find any other relevant section here.
View 14 Replies
View Related
May 11, 2007
Table 1
ID PID From To Code
1 1 14/02/07 17/02/07 X
2 1 17/02/07 19/02/07 X
3 1. 19/02/07 23/02/07 E
4 1 26/02/07 28/02/07 X
5 1 1/4/07 1/5/07 E
6 2 01/03/07 03/03/07 X
7 2 04/03/07 10/03/07 X
8 2 10/03/07 14/03/07 E
Result
ID PID Date
4 1 26/02/07
7 2 04/03/07
I want to be able to create a select query on the above table. The table will show ID, PersonID (PID), From and to date, and code. If the code is X then the next €˜from record€™ should be the same date as the €˜to date€™. If the code is E then the next €˜to€™ date can be anytime after the previous €˜to€™ date.
I want to be able to report on all record where there is a day difference between the previous €˜to€™ date. I.e. ID 4 and 7 €“ the previous records both have an X and there is at least a days difference between the dates.
View 4 Replies
View Related
Sep 28, 2007
Hi all,
I am quite surprise by getting wrong resultset from a simple query like:
select Order_No from Delivery_Order where cust_id = 5 and do_date >= '6/15/2008' and do_date <= '6/31/2008'
In the database, there are data since the last two years. There is no data beyond today's date, in fact. But when I tried to query for 'Order_No' with specified cust_id within above date range (which data is not in the DB), the result will be the 'Order_No' from '6/15/2007' to '6/31/2007'. Isn't it supposed to return null? simply because there is no such date as 2008 yet in the DB.
Help from anyone is needed. Thanks in advance.
Sha.
View 11 Replies
View Related
Mar 19, 2008
Is it possible to have an AND within an inner join statment? The below query works, except for the line marked with --*--.
The error I get is the "multipart identifier pregovb.cellname could no be bound", which usually means that SQL server can't find what I'm talking about, but it's puzzling, as I've created the temp table with such a column in it.
Is there a different way i should be structuring my select statement?
SELECT [Survey Return].SurveyReturnID, '1', #temp_pregovb.paidDate, #temp_pregovb.email
FROM #temp_pregovb, [Survey Return]
INNER JOIN SelectedInvited ON
[Survey Return].SelectedID = SelectedInvited.SelectedID
--*-- AND [SelectedInvited].cellref=#temp_pregovb.cellname
INNER JOIN [panelist Contact]
ON SelectedInvited.PanelistID=[Panelist Contact].PanelistID
WHERE
[panelist contact].email=#temp_pregovb.email
AND SelectedInvited.CellRef IN (
SELECT surveycell
FROm [Survey Cells]
WHERe SurveyRef='5')
View 3 Replies
View Related
Apr 1, 2006
I'm attempting to create a complex query and i'm not sure exactly how i need to tackle I have a series of tables:
[tblEmployee]
empID
empName
deptID
jobtID
[tblDept]
deptID
deptNum
deptName
[tblJobTitle]
jobtID
jobtNam
[tblTrainng]
trnID
trnName
[tblTrnRev]
trnrevID
trnID
trnrevRev
trnrevDate
[tblEduJob]
ejID
jobtID
trnID
[tblEducation]
eduD
empID
trnrvID
eduDate
The jist of this database is for storage of training. The Training table is used for storing a list of training classes. The TrnRev links and shows each time the training was updated. The EduJob table links each Job title (position) in the company to each trainng class that position should be trained on. The Education table links each employee to which revision of a class they have attended.
What i need to do is create a query that for each employee, based on their job title, wil show what classes they are required to be trained on. I want the query to return the employee, the training, the latest revision of that class, and then show if a) the person's trainig is current for that revision, b) the person has been trained on that topic but not the latest revision, or c) they've had no training at all on that topic.
i'm somewhat at a loss of where to begin.
View 6 Replies
View Related
May 22, 2008
NOTE: I apologize to anyone (especially moderators) who may notice that I am basically repeating a question that was already posted by me in another recent thread. The reason why I am reposting is because I want to filter my question down to its crux because the other question may not have been asked in the most clear way.
The Question:
The sceanrio is this.
(1) I have a sales person dimension with a hierarchy.
(2) In this hierarchy, Bill and Ted roll up to John.
(3) Bill sells 10 units, Ted sells 8 units, and John sells 5 units.
When you process this hierarchy, what would you expect the total to be for John?
(A) 23, which is the sum for Bill, Ted, and John
or
(B) 18, which is the sum of Bill and Ted only and overwritting John's number
I say (A) and I think most will choose the same, and all the examples I've been reflects (A).
However, in my simple cube I get (B).
Is there a setting I need to adjust to get (A)?
View 16 Replies
View Related
Sep 1, 2015
I'm currently looking at refactoring an existing, large SSIS 2012 implementation that consists of about 55 projects and 360+ packages. The ETL framework that is in use has a "main" control package that reads from a database table and determines which packages are ready to execute (based on some dependency logic) and then uses an Execute Process task within a loop that calls dtexec with the arguments:Â /C start Dtexec /SQL "Some Package Path" /SERVER "someserver"Â
This design allows the loop to execute a package and then immediately iterate because it doesn't wait for the package to respond (aka complete with a failure or success) so it can quickly kick off as many packages are ready to execute. A SQL Agent job is used to call this package every few minutes so that it can pick up any packages that have had their dependencies satisfied since the last execution and kick those off.It's a a clever design but has some problems such as decentralized exception handling (since the parent package is unaware of what is happening in the "asynchronous" dtexec calls.My biggest concern is that by executing packages, not with the Execute Package Task but with the Execute Process Task, and spinning up many dtexecs, the framework is not leveraging SSIS's ability to handle threading, memory consumption, etc. across all running packages and executables because it is simply unaware of them. It's essentially like using an Execute Package Task with the ExecuteOutOfProcess property set to true.
View 6 Replies
View Related
Jan 20, 2007
I'm a newbe in the realm of database reporting. At my current position, I'm reporting off of CRM databases using Crystal V-11. Previously I'd experience working with HR databases using the same reporting tool. I am interested in progressing to work with database design and scripting. Any suggestion from anyone on which certifications to pursue?
Thanks.
View 1 Replies
View Related
