SQL Query Problem For Reporting [SOLVED]

May 30, 2008

hi guys, im having a sql problem for reporting here. here is the table

CREATE TABLE `tblasset` (
`AssetID` int(11) NOT NULL,
`AssetName` varchar(50) NOT NULL,
PRIMARY KEY (`AssetID`)
);

INSERT INTO `tblasset` (`AssetID`, `AssetName`) VALUES
(1, 'IPOD'),
(2, 'Laptop'),
(3, 'Sony PS3'),
(4, 'Wooden Cupboard'),
(5, 'Fridge');


CREATE TABLE `tblassetplacement` (
`PlacementID` int(11) NOT NULL,
`LocationID` int(11) NOT NULL,
`AssetID` int(11) NOT NULL,
`PlacementDate` bigint(20) NOT NULL,
`OfficerInChargeID` varchar(50) DEFAULT NULL,
PRIMARY KEY (`PlacementID`)
);

INSERT INTO `tblassetplacement` (`PlacementID`, `LocationID`, `AssetID`, `PlacementDate`, `OfficerInChargeID`) VALUES
(1, 3, 1, 1209628156, 'John'),
(2, 6, 1, 1209800956, 'Susan'),
(3, 3, 3, 1209714556, 'Erik'),
(4, 4, 3, 1210405756, 'Albert'),
(5, 5, 3, 1211096956, 'Fred');


CREATE TABLE `tbllocation` (
`LocationID` int(11) NOT NULL,
`LocationName` varchar(50) NOT NULL,
`ParentID` int(11) DEFAULT NULL,
PRIMARY KEY (`LocationID`),
KEY `ParentID` (`ParentID`)
);

INSERT INTO `tbllocation` (`LocationID`, `LocationName`, `ParentID`) VALUES
(1, 'Building A', NULL),
(2, 'Building B', NULL),
(3, 'Room 1', 1),
(4, 'Room 2', 1),
(5, 'Partition 1', 4),
(6, 'Room 1', 2);

basically the location looks like this

Location Tree View
---
.___ Building A
. .___ Room 1
. .___ Room 2
. .___ Partition 1
.
.___ Building B
. .___ Room 1

on tblAssetPlacement, the column PlacementDate is a unix timestamp stored as bigint.
the higher of its value means it is the latest placement.

so i had created a sql to pull the data like this:

SELECT
tblasset.AssetName,
tblassetplacement.OfficerInChargeID,
tblassetplacement.Locationid,
tbllocation.LocationName
FROM
tblasset,tblassetplacement, tbllocation,
(select tblassetplacement.AssetID,tblassetplacement.LocationID,MAX(tblassetplacement.PlacementDate) AS LatestDate from tblassetplacement group by tblassetplacement.AssetID) temptbl
where
tblasset.AssetID = tblassetplacement.AssetID
and tblassetplacement.PlacementDate = temptbl.LatestDate
and tblassetplacement.AssetID = temptbl.AssetID
and tbllocation.LocationID=tblassetplacement.LocationID
;

this will output:
---
AssetNameOfficerInChargeIDLocationIDLocationName
IPODSusan6Room 1
Sony PS3Fred5Partition 1

i would need to have a column ParentLocationName that directly shows the name of the parent's location as below:
---
AssetNameOfficerInChargeIDLocationIDLocationNameParentLocationName
IPODSusan6Room 1Building B
Sony PS3Fred5Partition 1Building A

is there a way to pull the parent's location name in one sql syntax?

View 4 Replies


ADVERTISEMENT

SOLVED: Need Help With This Count() Query

Mar 23, 2008

Hello,uery - I'm Stuck

I'm very rusty with my SQL, and could use a little assistance on building this query. Thanks for taking the time to help me. It should be a relatively simple Count() query but I'm not getting the right results for some reason and I'm hoping somebody can point out my error to me.

I'm attempting to count the number of times each m_id is returned after running this query:

SELECT m_id
FROM taglink
WHERE m_id <> '25'
AND t_id
IN (
SELECT t_id
FROM taglink
WHERE m_id = '25'
)

which returns:
m_id
33
34
34
35
35
35
36

I want to count the number of times that m_id is returned so that the results of my query will be:

m_id | count
33 | 1
34 | 2
35 | 3
36 | 1

In my attempt to do this, I run the following query:

SELECT m_id, count( m_id ) AS "count"
FROM taglink
WHERE m_id
IN (
SELECT m_id
FROM taglink
WHERE m_id <> '25'
AND t_id
IN (
SELECT t_id
FROM taglink
WHERE m_id = '25'
)
)
GROUP BY m_id

The problem I'm having is that the query returns:

m_id | count
33 | 3
34 | 2
35 | 3
36 | 3

It seems to return the count of "3" for fields that should count "1," but count correctly if the fields count "2" or "3."

Perhaps somebody can spot my error... I can't seem to wrap my brain around this one. Thanks so much for your time!

View 4 Replies View Related

Simplish Query Problem *SOLVED*

May 28, 2008

First, I'm not sure what format to post the SQL query in, but I'm using it in the Pawn programming language, so it's all on one line. Sorry if it's hard to read :(

Ok, I am supplying you guys with my database that I am having problems with. Basically, I'm telling it to ORDER BY ASC but it's not ordering it correctly. It's almost correct, but not perfect. I'm not sure if SQLite is much different than SQL standard, that's why I differentiate.

Here is the SQLite database:
*deleted*

The problem comes in when you execute this query:

SELECT p.alias, min(s.fin_time), s.cps, s.gcs, s.boosts, s.wpns, s.score, s.score_id FROM climb_scores s JOIN climb_players p ON s.user_id = p.user_id JOIN (SELECT user_id, fin_time, score_id FROM climb_scores WHERE map_name="j2s_4floors" AND cps = 0) g ON g.score_id=s.score_id GROUP BY s.user_id ORDER BY s.fin_time ASC LIMIT 20


The end goal is to have fin_time sorted in ascending order, but it's not working right. Can someone clue me in to the problem?

thanks!

View 1 Replies View Related

Nm Solved

Mar 26, 2007

nm solved

View 1 Replies View Related

Solved

Sep 20, 2006

Hi,

Now it´s working fine!!!!

On the Firewall of the Server, I´ve added the default port 1433 on the exceptions.

thanx!!!!

View 1 Replies View Related

Problem Solved

Feb 21, 2008



The problem is solved but still I would like to know what is going on behind the scenes. I was always thinking that the native client was involved and not oledb.

Using the Export/Import wizard you still end up going via SQL Server Agent that's where the Credential/Proxy items show up.

View 3 Replies View Related

My Problem Also Not Solved Yet

Jun 24, 2006

Upper case sentece in normal case sentence

View 6 Replies View Related

Simple Problem But Cannot Solved

Feb 25, 2003

I'm just beginning to use DTS. I have a test table which has only two column, char (10) and decimal (5,0). I want to do a simple transformation by adding the second column by 100 with the following statement, but I receive a type mismatch error!

Function Main()
DTSDestination("a")=DTSSource("a")
DTSDestination("b")=DTSSource("b").Value + 100
Main = DTSTransformStat_OK
End Function

or

Function Main()
dim x
DTSDestination("a")=DTSSource("a")
x = DTSSource("b").Value + 100
DTSDestination("b").Value = x
Main = DTSTransformStat_OK
End Function

View 2 Replies View Related

An Importanat Problem To Be Solved

Dec 8, 1999

Hello friends I need a very ergent help!!!!!!!!!!!!!
Problem: my sql server on production had everything grayed out, when I want to administer this server as a SA. eg: I can't add users, I can't backup and so on.
but the database is still running and users are accessing it.
This SQL database is accessed by an application(it can also create users on
the backend). I don't know how it happend ( i cant even get to errorlog).
I beleive somehow sa user rights were taken away from him. Can anyone tell me how to restore sa with all rights.

Please help me on this matter, because I have to do lots of important tasks on this server.

I appreciate all of your help.

Thanks
Ragul

View 2 Replies View Related

Compare Counts [SOLVED]

Mar 12, 2008

Hi, hope someone can help. I have two tables

Test1 that lists all the training courses that i can count to find out the total as below.

select count (distinct Training_Course) as total
from Test1

Then Test2 lists all our customers and courses they have attended. I count the courses attended and then group by their ID.

select Cust_id, count (Attended) as TotalAttend
from Test2
Group by Cust_id

What i am now trying to do, without any luck, is find out which Customers have attended all training sessions by comparing the two queries and only bringing back the cust_id where it matches the total count from the Test1 query.

Make any sense? Any help/suggestions gratefully recieved.

View 4 Replies View Related

Object Must Implement IConvertible Is A BUG Non Solved?

Aug 9, 2006

 
 
i try to do a simple insert using SQLDataSource and a Stored Procedure
I try all night and always have this error
 
Object Must Implement IConvertible
 
I can’t understand where I wrong..
 
Looking around I understand is maybe a typecasting problema but I not find nothing more understeable..
Could someone tell me why Microsoft release this SQLdatasorce making more difficult a simple insert?
If someone could help me to fix this problem please
 
Here is the code
------------------------------------------------------------------------------------------------------------
 
Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click
 
 
        Try
            SqlDataSource1.Insert()
 
        Catch ex As Exception
            Response.Write(ex.ToString)
        Finally
            SqlDataSource1.Dispose()
 
 
        End Try
 
End Sub
------------------------------------------------------------------------------------------------------------
 
     <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CommerceTemplate %>"
            InsertCommandType="StoredProcedure" InsertCommand="dbo.StoredProcedure1">
            <InsertParameters>
                <asp:ControlParameter ControlID="DropDownListCategory" Name="@CategoryID" PropertyName="SelectedValue"
                    Type="Int32" />
                <asp:ControlParameter ControlID="DropDownListCategory" Name="@ModelNumber" PropertyName="SelectedValue"
                    Type="String" />
                <asp:ControlParameter ControlID="txtModelName" Name="@ModelName" PropertyName="Text"
                    Type="String" />
                <asp:ControlParameter ControlID="txtProductImage" Name="@ProductImage" PropertyName="Text"
                    Type="String" />
                <asp:ControlParameter ControlID="txtUnitCost" Name="@UnitCost" PropertyName="Text"
                    Type="Decimal" />
                <asp:ControlParameter ControlID="txtDescription" Name="@Description" PropertyName="Text"
                    Type="String" />
                <asp:ControlParameter ControlID="txtWeight" Name="@Weight" PropertyName="Text" Type="Decimal" />
                <asp:ControlParameter ControlID="txtDiscount" Name="@Discount" PropertyName="Text"
                    Type="Decimal" />
                <asp:ControlParameter ControlID="chkIsActive" Name="@isActive" PropertyName="Checked"
                    Type="Boolean" />
                <asp:ControlParameter ControlID="DropDownListAuthors" Name="@IDAuthor" PropertyName="SelectedItem"
                    Type="Int32" />
            </InsertParameters>
        </asp:SqlDataSource>
------------------------------------------------------------------------------------------------------------
 
 
ALTER PROCEDURE dbo.StoredProcedure1
 
      @CategoryID int,
      @ModelNumber nvarchar(50),
      @ModelName nvarchar(50),
      @ProductImage nvarchar(50),
      @UnitCost money,
      @Description varchar(500),
      @weight money,
      @IsActive bit,
      @DiscountPerCent int,
      @IDAuthor int
 
AS
 
INSERT INTO dbo.CMRC_Products (
 
      CategoryID,
      ModelNumber,
      ModelName,
      ProductImage,
      UnitCost,
      Description,
      weight,
      IsActive,
      DiscountPerCent,
      IDAuthor
)
 
VALUES (
 
      @CategoryID,
      @ModelNumber,
      @ModelName,
      @ProductImage,
      @UnitCost,
      @Description,
      @weight,
      @IsActive,
      @DiscountPerCent,
      @IDAuthor
)
 
SELECT @@IDENTITY as [NewID]
 ------------

View 4 Replies View Related

Urgent , Execute Permission Not Solved

Aug 9, 1999

i have a table "employee" , i have given grant all rights to a user
in sql 6.5 server . still whenever he tries to access the table from VB he gets an error "execute permission denied on table employee, owner dbo"
. if i log in the server with this user name and his password i am able to interact with the table . please help.

View 1 Replies View Related

Error In Attaching .mdf Files.. How Can This Be Solved?

May 11, 2006

We've tried attaching the mdf files given to us by some customers using the script they've provided, too.

I created the database with the corresponding names, and tried to run their script. However, I get the message "The media family on device 'D:DatabaseDVVAD01.mdf' is incorrectly formed. SQL Server cannot process this media family."

What could possibly have gone wrong? Is it also possible that the file was not properly detached, or what?

View 1 Replies View Related

Run-Time Running Total *(SOLVED)

May 14, 2008

I am re-writing a old query that update's it's value based on values at run time. Is there anyway to accomplish the following query where column s1.AI referances the values that were updated during run-time.

I want to avoid a Loop (i.e. cursor or a CTE).

Please help!



Declare @Stage Table(StartDate datetime,BenefitInterestID INT PRIMARY KEY, Amount MONEY, InterestAmount MONEY, Interest DECIMAL(10, 4), ai DECIMAL(10, 4))
Insert Into @Stage
Select
convert(datetime,'2006-12-01 00:00:00.000',101) as StartDate,1 as BenefitInterestID,1701.00 as amount,79.605 as InterestAmount ,0.1000 as Interest,0.0000 as ai
Union all
select '2007-12-01 00:00:00.000',2,172.80,7.92,0.0500,0
Union all
select '2008-12-01 00:00:00.000',4,0.00,0.00,0.0700,0




UPDATEs1
SETs1.ai = s1.Interest * coalesce((SELECT SUM(coalesce(s2.Amount,0) + coalesce(s2.InterestAmount,0)+coalesce(s2.ai,0)) FROM @Stage AS s2 WHERE s2.StartDate < s1.StartDate ),0)

FROM@Stage AS s1

select * from @Stage

My desired Results are


2006-12-01 00:00:00.00011701.0079.6050.10000.0000
2007-12-01 00:00:00.0002172.807.920.050089.0303
2008-12-01 00:00:00.00040.000.000.0700143.5300


But I am geting

2006-12-01 00:00:00.00011701.0079.6050.10000.0000
2007-12-01 00:00:00.0002172.807.920.050089.0303
2008-12-01 00:00:00.00040.000.000.0700137.2928


Due to as you can see on row 3 that the amount does not factor in the previous Years ai column when I sum, due to the update takes place at runtime.

Please help!

View 15 Replies View Related

Suppress Output For One Column...SOLVED

Sep 14, 2006

Is there a way to supress output on one column in a SP, using data from the same row?

Like This:
SELECT Last, First, DOP, dbo.fnDueDate(DOP, 3, GETDATE()) AS NextQDue, dbo.fnDueDate(DOP, 6, GETDATE()) AS NextNSPDue, DATEADD(m, 1, DOP)AS InitialNSPDue, DATEADD(m, 1, DOP) AS InitialAssessDue, DOT, DisReason, DATEADD(m, 1, DOT) AS DisSummDue, Facility, Active
FROM dbo.tblResidents

But which returns null for some of the columns if DOT is not null?
DOT is the Termination Date, so the only columns that have any meaning once there is data in the DOT column are DisReason and DisSummDue. Also, if DOT *is* null, then the above columns also have no meaning.
I tried several variations of the following, but I can't figure it out

CREATE PROCEDURE [dbo].[spTesting] AS
BEGIN
SELECT Last, First, DOP, dbo.fnDueDate(DOP, 3, GETDATE()) AS NextQDue, dbo.fnDueDate(DOP, 6, GETDATE()) AS NextNSPDue, DATEADD(m, 1, DOP) AS InitialNSPDue, DATEADD(m, 1, DOP) AS InitialAssessDue, Facility
FROM dbo.tblResidents A
WHERE DOT IS NULL
UNION
SELECT Last, First, DOP, DOT, DisReason, DATEADD(m, 1, DOT) AS DisSummDue, Facility
FROM dbo.tblResidents I
END
GO

----------------
-Stephen

View 4 Replies View Related

Shrinking 27GB LDF File (solved)

Jan 8, 2007

Hi,

I have an interesting problem here. I am running SQL Server 2005 and have a large database. After running some scalability tests over the weekend my MDF file size went up to 25GB and LDF file size went up to 27GB filling up all available disk space. I didn't do any backups, so it is understandable why the LDF file size is so large.

Now I am trying to shrink the LDF file. However, DBCC LOGINFO indicated that all LVFs within the LDF are active (Status = 2). I detached the database to make sure no active connections exist and ran CHECKPOINT, but still DBCC LOGINFO lists all LVFs with status 2. Since all LVFs are up to date I can't shrink my LDF file.

Why are all LVFs still active after detach/attach and checkpoint?
How can I shrink the LDF file?

Thanks.

Alec

View 1 Replies View Related

Problem With Roles And Rights (solved)

Jan 8, 2008

Hello everybody,

I have a database, where all tables and stored procedures look like this:
dbo.table
dbo.sp

Is this naming because of the db owner creating the tables and stored procedures?

The db runs correctly in my asp.net aplication when i use a user that is db owner, but i want it to run with a 'normal' user that is in public role and has all rights except db_owner...

How can i solve my problem!

Using the user without db_owner rights brings in my application the error:
The EXECUTE permission was denied on the object 'myStoredProcedure', database 'myDatabase', schema 'dbo'.

Thanks for help!!!!

Steven****


--------------------------------------

Solution:

I have to give the User without DB_Owner Rights the schema permissions for dbo objects! This only works in sql server 2005!

View 1 Replies View Related

Timeout Issues Solved By Recompilation

Jul 23, 2005

Hello,We have an ADO.NET application using .NET version 1.1.4322 SP1. It iscalling stored procedures in a database that it never written to. Theonly thing the stored procedures do is a select statement on a fewtables that are joined. In the last few weeks we have experiencedissues where, two or three of these stored procedures consistentlytimeout. When we recompile the stored procedures, the problem goesaway for a period of time (anywhere between a few hours and a fewdays), then it reoccurs with the same two or three stored procedures.We have tried running the stored procedures with the same parametersagainst the same server using query analyzer with the same user andconnection settings while we are having the timeout issues, and theyperformed normally (in the 5 second range). We have also put thedatabase into "read only" mode, which has improved performanceduring normal times, but the issue continues to occur.Our trace data shows that during the timeouts periods, the storedprocedures perform the same number of reads and writes, and using thesame amount of CPU as during normal times, but the duration increasesfrom 5-6 seconds to 30 seconds (when the server receives the timeoutrequest).We have found no blocking on any of the tables (they are, after all,read only).Finally, we compared execution plans for the stored procedures when wehad timeouts to right after we recompile and alleviate the issue, andthe plans are identical.What could be causing this problem? Does recompiling a storedprocedure affect the Sql Server .Net Data provider?Thanks in advance

View 1 Replies View Related

Osql Doesn't Work - SOLVED

Sep 3, 2005

Sory for starting a new message, it won't let me reply to the original.Problem has been solved.osql -U sa -s localhostBALTDDoes the trick.Thanks for everyone who had a look at it anyway!Enjoy your weekend!

View 1 Replies View Related

Weird SQL Problem Solved By Reboot

Mar 29, 2008

I have 15 websites running on my server, which also has SQL Express installed.
every other day my webpages whch connect by sql connection string, give an error.

Event Type: Failure Audit
Event Source: MSSQL$SQLEXPRESS
Event Category: (4)
Event ID: 18456
Date: 29/03/2008
Time: 18:20:07
User: N/A
Computer: DSVR006063
Description:
Login failed for user 'db'. [CLIENT: <local machine>]
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 18 48 00 00 0e 00 00 00 .H......
0008: 16 00 00 00 44 00 53 00 ....D.S.
0010: 56 00 52 00 30 00 30 00 V.R.0.0.
0018: 36 00 30 00 36 00 33 00 6.0.6.3.
0020: 5c 00 53 00 51 00 4c 00 .S.Q.L.
0028: 45 00 58 00 50 00 52 00 E.X.P.R.
0030: 45 00 53 00 53 00 00 00 E.S.S...
0038: 07 00 00 00 6d 00 61 00 ....m.a.
0040: 73 00 74 00 65 00 72 00 s.t.e.r.
0048: 00 00

Restarting SQL Service does not resolve, however rebooting server, without making any changed sorts the problem.
does my sql account need access to the master?

Is this weird or am i missing something really obvious.

View 8 Replies View Related

Inserting A Record For Each Separate Aggregate (solved)

Jul 23, 2005

Hi,As I wrote my message the solution came to me, so I thought I wouldpost anyway for others to see in case it was useful:Here is some sample DDL for this question:CREATE TABLE Source (my_value INT NOT NULL )GOINSERT INTO Source VALUES (1)INSERT INTO Source VALUES (2)INSERT INTO Source VALUES (3)GOCREATE TABLE Destination (value_type VARCHAR(10) NOT NULL,value INT )GOI would like to fill the destination with a row for the COUNT, SUM,MIN, and MAX. My own problem is of course much more complex than this,but this is the basic stumbling block for me now. So, the rows that Iwould expect to see in Destination are:value_type value---------- -----COUNT 3SUM 6MIN 1MAX 3The solution that I came up with was to add a Value_Types table:CREATE TABLE Value_Types (value_type VARCHAR(10) NOT NULL )GOINSERT INTO Value_Types VALUES ('COUNT')INSERT INTO Value_Types VALUES ('SUM')INSERT INTO Value_Types VALUES ('MAX')INSERT INTO Value_Types VALUES ('MIN')GONow the SQL is pretty simple:SELECT V.value_type,CASE V.value_typeWHEN 'COUNT' THEN COUNT(*)WHEN 'SUM' THEN SUM(S.my_value)WHEN 'MAX' THEN MAX(S.my_value)WHEN 'MIN' THEN MIN(S.my_value)ENDFROM Source SINNER JOIN Value_Types V ON 1=1-Tom.P.S. - I know that I did not include primary or foreign keys in my DDL.I'll leave it as an excercise to the reader to figure those out. Ithink the code adequately explains the concept.

View 3 Replies View Related

SOLVED: How To Call Scalar Function From JDBC

Jan 28, 2008

As an example, I have a scalar function called TRIM that takes a VARCHAR parameter, does a LTRIM(TRIM(VARCHAR)), and returns the result.

How can I call this function from java using JDBC? I have only had luck calling basic stored procedures, but I need to call functions as well.

Thanks, Ken

View 1 Replies View Related

Choose Main Or Preferred Supplier ( Was A Thing That Cannot Be Solved...?)

Jan 24, 2005

I have a table which contains suppliers that can supply certain articles.
There can be many suppliers to each article, and therefore there is a field that tells you if this supplier is the main supplier for the article, this field is called "arthuvudavt". However, you aren't forced to use this field in the table.
As a alternative you can use priority. Each supplier has given priority where
0 means "most preferred" and any other means "less preferred"
What I want to do is to pick the right supplier for a given article in one SQL-statement regardless of if you choose to use the priority or if you choose to use the main supplier field.

Abbreviated description of the table, called AL.

SuppNo Article Arthuvudavt Prio
10101 A-10 NULL 0
10202 A-10 1 0
10303 A-10 NULL 1
10101 B-10 NULL 0
10202 B-10 NULL 0
10303 B-10 NULL 1
10101 C-10 NULL 1
10202 C-10 NULL 0
10303 C-10 NULL 1

In the above case, I'd like the statement to return supplier 10202 for article A-10 (the one that has the arthuvudavt checked), and
only one row for B-10, which one doesn't really matter, since someone has been making a fault here, this should not happen, but my SQL-statement should break because of this scenario. For C-10 supplier 10303 should be returned.

Is there any way this could be done?

The reason I ask is that I try to learn new ways of solving things, and this problem was kind'a easily solved using variables, but I was wondering if it was possible to do it without it.

Thanks in advance
Jonas

Sorry for the poor formatting, I didn't have the time to make it right.

View 14 Replies View Related

[solved]Delete A MatrixRow Element On The Design Of A Report

May 2, 2008

Hi,

I have a report in which a developper has put a matrix which has 4 MatrixRows.
I want to delete 2 of them.
When I righlt click a row and click Delete there is message about rows groups and columns groups and the row is still there.
When I right click the matrix and go to properties / group, i see Row group = [Static group] and I cannot modify it.

How can I delete a MatrixRow ?

a+, =)
-=Clement=-

Configuration :
SQL Server 2005

edit :
I found the solution : click on the cell in the 2nd column, not in the first, then right click and delete.
I would suggest Microsoft IT s to put less humor in their softs (you click delete in the wrong place, you get a weird message instead of a 'try another cell' )

View 1 Replies View Related

Store The Output Of Sp_executesql - Solved With Managed Code

Apr 18, 2006

hi

I am trying to store the output of sp-executesql into a variable to implement it as a user defined function later

The function is

ALTER function [dbo].[UnitsAvailable] (@id int)

returns int

as

begin

declare @sql nvarchar(100)

declare @params nvarchar(500)

declare @count nvarchar(10)

set @sql = N'Select count(*) from units where projectid=' + convert(varchar,@id) + 'and sold=0 and displayunit=1'

set @params = N'@countOUT nvarchar(10) OUTPUT';

exec sp_executesql @sql, @params, @countOUT=@count OUTPUT;

return @count

end

The result is that I am able to parameterize the sql end execute with the right result. The only problem is that the value is not stored in the variable @count. I could get to the same result using managed code in sql 2005 but still I am curious to find out where the problem is ....

Can you please help?

Thanks Alex

View 6 Replies View Related

SOLVED: TDSSNIClient Initialization Failed With Error 0x7e

Nov 13, 2005

solution found here:

View 1 Replies View Related

Locks Caused By Long Lasting SELECT, Could It Be Solved By Read Uncommitted?

Jan 15, 2008

Hi,

Yesterday, we have had a sudden load in our SQL Server 2000 which resulted in several locks. There was not too much time to investigate as we had to rush. A team member had reviewed the processes in EM, Manegement, Current Activity. Looking for blocking processes and killed them.

She told me that as soon as the blocking SPID was killed, another one arose and she had to repeat the operation a dozen of time. When done, the server activity was back to normal. She noticed that more than half of the blocking processes showed that they executed the stored Proc "P_SearchProducts".

We don't own the server and the information on what had happened at that time (batches or resource intensive operations, etc.) is not available for now.

The team suggests that we set the Transaction Isolation Level to Read UNCOMMITTED for this SP. I would like to know better about locks before I go ahead.

P_SearchProducts returns 5 recordsets each one could contains from 1 to 200 rows. To achieve the results, it creates about 10 intermediate tables (SELECT ... INTO #TableX) these temp tables are then used progressively to arrive to the final results. Roughly the volume of these temp tables could be double than the final results. The developer who wrote this SP is not a guru in SQL, there is room for improvement. But here are my questions:

Q1. Could the series SELECT ... INTO #TableX in P_SearchProducts prevent or lock another connection from executing the same SP? If yes, under which conditions?

Q2. Let's assume that P_SearchProducts has a slow execution time. Could it prevent another connection from updating the Product table? And thus leading to a deadlock situation? Something like another transaction (by User2) has obtained lock on most of Product tables, except the Product table which were being slowly read by User1 executing P_SearchProducts. But User1 cannot read the other product tables b/c there are locks by User2.

Q3. If the contention issue was provoked by the slow execution time of many request to exec
P_SearchProducts (let's assume there were suddenly 50 users on the web hitting the search product feature at the same time). Could the Read Uncommitted magically resolve the contention issue, providing we accept the consequences of the dirty read.

Sorry for the long post and thank you in advance for any help.

View 2 Replies View Related

Why The Query Works In Query Analyser And Doesn't Work In Reporting Service ?

Apr 30, 2007



Hello everybody,



I'm developing a report using the following structure :



declare @sql as nvarchar(4000)

declare @where as nvarchar(2000)



set @sql = 'select ....'



If <conditional1>

begin

set @where = 'some where'

end



If <conditional2>

begin

set @where = 'some where'

end



set @sql = @sql + @where



exec(@sql)



I run it in query analyser and works fine, but when I try to run in Reporting Services, Visual studio stops responding and the cpu reaches 100 %.



I realize that when I cut off the if clauses, then it works at Reporting services.

Does anybody know what is happening?

Why the query works in query analyser and doesn't work in Reporting Service ?



Thanks,



MaurĂ­cio

View 2 Replies View Related

Do Somebody Know How Long (in Chars) Script(command) Can Be Solved By SQL Command?

Aug 30, 2004

Do somebody know how long (in chars) script(command) can be solved by SQL Command?
Thanks

View 1 Replies View Related

Need Help On A Reporting Query

Jun 12, 2008

I adopted a site that stores items in a database that have a date associated with when those items were developed.  However, it wasn't stored as an actual date, but rather a jumble of characters from which you can infer atleast the quarter.  Thing is, there is no standard length, but I know I always want the last 5 characters.  The format looks something like this:
ab_q1_03
The "ab" could be various lengths of whatever characters.  "q1_03" means the first quarter of 2003, and this information will always be the last 5 characters of this column.  I'd like to get a count of items for each quarter, but I'm not exactly sure how to group that.  Any ideas?

View 4 Replies View Related

SQL Reporting Query

Jan 7, 2008

im using reporting services to genarate a report and its calling S.Procedure to get the results. When i ran this report from the report server, it took about 10 mins to generate it. but when i ran the same SP in query analyzer it took only 1 second. Reporting server is on different box and database on different ( separate boxes) when i check the performance monitor (in Database box) its gone high when the report is running(for about 10 mins)

How can i optimized the same query for reporting ?

Thanks.

-----------------
E!

View 1 Replies View Related

Help W/ SQL Query And Reporting

Apr 21, 2008



I have created a report that lists a work request ID, work type, short description and the engineer that is assigned to the request. All the information about the requests are in the Request table. I want the user to be able to click on the work request ID (1002) and see the entire record. I cannot come up with an SQL query that will display the specific work request. I know select * from Request will show everything in the table but how do I pull only the information for ID 1002? Thanks.






Work Request ID

Work Type

Short Description

Engineer


1002

Windows

Install Vista on new PCs

R. Williams

View 5 Replies View Related

Need Help On SQL Query On Reporting Services

Dec 10, 2007

Although not totally related to this RS cat but since I'm using the query in Reporting Services perhaps I can get a work around from someone. I've created a Rolling 2 year 12 month comparison report that compare sales figures from current and previous years. The query works fine however the query fails to return 0's for no sales for a particular month with no records. Is there anyway I can return a 0 instead. As you can see I tried "isNull to no avail.

Query==========>

SELECT Customer.Country, MetaDates.Month, MetaDates.Year, isnull(SUM(Order_Line_Invoice.Sales),0) AS Sales, SUM(Order_Line_Invoice.Cost) AS Cost,
SUM(Order_Line_Invoice.Sales) - SUM(Order_Line_Invoice.Cost) AS GM, 'Current 12 Months' AS yearNum


FROM Order_Line_Invoice INNER JOIN
Customer ON Order_Line_Invoice.CustId = Customer.CustId INNER JOIN
MetaDates ON Order_Line_Invoice.InvoiceDate = MetaDates.Date LEFT OUTER JOIN
Product ON Order_Line_Invoice.ProdId = Product.ProdId
WHERE (Customer.country = @GroupByFieldFilter) and (Order_Line_Invoice.InvoiceDate BETWEEN dateadd(m, datediff(m, 0, DATEADD(month,-11,@EndDate) ), 0) AND @EndDate)

GROUP BY MetaDates.Year, MetaDates.Month, Customer.country

UNION

SELECT Customer.Country , MetaDates.Month, MetaDates.Year, isnull(SUM(Order_Line_Invoice.Sales),0) AS Sales, SUM(Order_Line_Invoice.Cost) AS Cost,
SUM(Order_Line_Invoice.Sales) - SUM(Order_Line_Invoice.Cost) AS GM, 'Previous 12 Months' AS yearNum

FROM Order_Line_Invoice INNER JOIN
Customer ON Order_Line_Invoice.CustId = Customer.CustId INNER JOIN
MetaDates ON Order_Line_Invoice.InvoiceDate = MetaDates.Date LEFT OUTER JOIN
Product ON Order_Line_Invoice.ProdId = Product.ProdId
WHERE (Customer.country = @GroupByFieldFilter) and (Order_Line_Invoice.InvoiceDate BETWEEN dateadd(m, datediff(m, 0, DATEADD(month,-23,@EndDate) ), 0) AND dateadd(m, datediff(m, 0, DATEADD(month,-11,@EndDate) ), -1))

GROUP BY MetaDates.Year, MetaDates.Month, Customer.country
order by MetaDates.Year, MetaDates.Month asc

End Query=========>

Any help would be much appreciated.

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved