Please Help Me With This Query?? I'm Desperate.
Aug 26, 2002Can someone help me. here is a diagram of what I need:
http://www.sfasource.com/stuff/relationship.gif
Thanks in advance,
Kevin
ADVERTISEMENT
SQL Server - Desperate!
Jul 19, 2007Hi all I've written an app using VWDE and SSE 2005 but it wont work on my hosts. I'm sure the connection string is correct and my hosts say it's all setup ok over there. I get this message: The SSE Provider did not find the database file specified
in the connection string. At the configured trust level (below High
trust level), the SSE provider can not automatically create the
database file. And my hosts says that the db should be fine but some of the VB might not work, this is the line that's causing the issues (red): Line 15: Session("username") = loggedInNameLine 16: Line 17: a = Roles.GetRolesForUser(loggedInName)Line 18: Line 19: For Each j In aThis works fine in SSE 2005, should it work on SQL Server too? Can anyone suggest an alternative? ThanksMark
DESPERATE Help Needed....
Jul 20, 2005Hello all,I am a total newbie to SQL. I created this sp and then with C++,called it. My return value was 100 (which it was)CREATE PROCEDURE sp_StoreIPs@IPSource varchar(16),@IPTarget varchar(16),@TimeDate varchar(20),@Name varchar(250)asdeclare @iReturn intSet @iReturn = 100return @iReturnGOThen when I added a INSERT statement like ...CREATE PROCEDURE sp_StoreIPs@IPSource varchar(16),@IPTarget varchar(16),@TimeDate varchar(20),@Name varchar(250)asdeclare @iReturn intInsert into LookUP (IPSource, IPTarget,TimeDate, Name) Values(@IPSource,@IPTarget,@TimeDate,@Name)Set @iReturn = 100return @iReturnGOMy return value was 0. I am assuming the the INSERT statement isreturning the 0 but how can I get around this?ThanksRalph Kraussewww.consiliumsoft.comUse the START button? Then you need CSFastRunII...A new kind of application launcher integrated in the taskbar!ScreenShot - http://www.consiliumsoft.com/ScreenShot.jpg
View 2 Replies View RelatedDesperate On Installing SP 1
Aug 1, 2006after we download sp1 and hit runj, we got "Unspecific Error Occur" popup window, with message:The following unexpected error occur:" then without any detail. here is the hotfix log:
Please help
08/01/2006 14:33:51.967 ================================================================================
08/01/2006 14:33:51.967 Hotfix package launched
08/01/2006 14:33:51.967 Successfully opened registry key: SOFTWAREMicrosoftWindowsCurrentVersion
08/01/2006 14:33:51.967 Successfully read registry key: CommonFilesDir, string value = C:Program Files (x86)Common Files
08/01/2006 14:33:51.967 Successfully opened registry key: SOFTWAREMicrosoftWindowsCurrentVersion
08/01/2006 14:33:51.967 Successfully read registry key: ProgramFilesDir, string value = C:Program Files (x86)
08/01/2006 14:33:52.014 Successfully opened registry key: SOFTWAREMicrosoftWindowsCurrentVersion
08/01/2006 14:33:52.014 Successfully read registry key: CommonFilesDir, string value = C:Program Files (x86)Common Files
08/01/2006 14:33:52.014 Successfully opened registry key: SOFTWAREMicrosoftWindowsCurrentVersion
08/01/2006 14:33:52.014 Successfully read registry key: ProgramFilesDir, string value = C:Program Files (x86)
08/01/2006 14:33:52.014 Local Computer:
08/01/2006 14:33:52.014 Target Details: ISSQL05
08/01/2006 14:33:52.014 commonfilesdir = C:Program Files (x86)Common Files
08/01/2006 14:33:52.014 lcidsupportdir = e:1af2e497cde069647713331033
08/01/2006 14:33:52.014 programfilesdir = C:Program Files (x86)
08/01/2006 14:33:52.014 supportdir = \ISSQL05e$1af2e497cde06964771333
08/01/2006 14:33:52.014 supportdirlocal = e:1af2e497cde06964771333
08/01/2006 14:33:52.014 windir = C:WINDOWS
08/01/2006 14:33:52.014 winsysdir = C:WINDOWSsystem32
08/01/2006 14:33:52.014
08/01/2006 14:33:52.014 Enumerating applicable products for this patch
08/01/2006 14:33:53.530 The patch installation could not proceed due to unexpected errors
08/01/2006 14:33:53.530
08/01/2006 14:33:53.530 Product Status Summary:
08/01/2006 14:33:53.780 Hotfix package closed
Attach Db ( Desperate Seeking For Help )
Apr 29, 2002A database with 1 mdf and 2 ldf has been detached from SQL Server 7.0 . Then removed the log files ( they are gone , unable to recover ) and there's no backup at all . Now I want to attach the database with the same mdf , but got error msg - 'Device activation error'. It seems like it's looking for one of the log files.
Is there any way to recover the db ?
I guess NOT , isn't it ?
I don't understand why it doesn't work with sp_attach_single_file_db and sp_attach_db . I actually tested it with a dummy database with 1 log file , and it worked - a new log file was recreated. Thus I performed in production server. Don't understand why it doesn't work.
thanks in advance.
Desperate - Cannot Connect To SQL 2K Server
Jul 2, 2003I have been down for 10 days now and don’t know where else to turn. If you can’t help, pls point me to where I can get help.
Here are the facts I have posted several times to the MS SQL NGs.
(Server: Win2K Adv Server, SQL 2K w/sp3a,
Wkstn: Win2K pro, SQL 2K w/sp3a)
1) SQL server is running and I can access all the dbs when on the server.
2) All the workstations login to the server just fine. Server is configured for Active Directory.
3) SQL server has only the default instance installed and is in mixed authentication mode.
4) I have registered SQL Server in the Active Directory, (right click on the server node in the SQL Server Enterprise Manager and select the Properties item from the popup menu … Active Directory tab etc - as per the article "SQL Server 2000 Active Directory Integration”.
5) When in EM on the workstation, I try to create a sql server registration
a) With Windows authentication - but I get this error:
SQL server registration failed because of the connection failure
displayed below. Do you wish to register anyway?
Login failed for user '(null)'. Reason: Not associated with a trusted SQL server connection.
b) With user = sa and pwd = sa (both are the correct values and case)
SQL server registration failed because of the connection failure
displayed below. Do you wish to register anyway?
Login failed for user 'sa'
5) I have checked out
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B269587 and done
everything it says.
Help - I haven't been able to connect for several days.
I have tried deleting the registrations several times - same results.
I have tried uninstalling sql 2K and reinstalling 3x and tested before and
after installing sp3a - same results.
Anyone have any ideas?
Desperate For Even Partial Recovery
Feb 12, 2008I have been asked to see what I can recover from a development server whose database became suspect during a power failure.
One developer from another group who's time is limited tried to repair the database using checkdb, but it is still suspect.
There are no recent backups of anything whatsoever. Needless to say, we are lacking in DBA skills here. At this point, we don't care whether we get the data back, but we are desperate to recover the table definitions, user-defined functions and stored procedures -- if not all of them than most of them; if not most of them than some.
What are our options here?
Are their any good third-party tools to help us with this problem?
Sp_msforeachdb I Know Its Undocummented Im Getting Desperate
Oct 1, 2007sp_msforeachdb @command1= 'USE ? exec sp_Depends "dbo.style"'
So im trying to return everything that uses that dbo.style im getting this error I just want to show where that table is being used and suggestions. I know that feature is undocumented. This also doesnt seem to be searching all the Databases when I search it.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'master'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'tempdb'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'msdb'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'GoldMine_Sales_and_Marketing'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'CustomerConcerns'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'Aggregate'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'OLD_BUDGET'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'CreditCardData'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'DELSA'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'COZUM'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'DelSolNet'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'DelSolNet2'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'DYNAMICS'.
Object does not reference any object, and no objects reference it.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'HUNTB'.
In the current database, the specified object is referenced by the following:
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'MASDEV'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'MASDEVSmall'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'OLDOnlineOrdering'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'OnlineRMA'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'OnlineStore'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'DELSO'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'Reporting'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'SSE'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'STTHO'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'TMPLT'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'WHAVL'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'QSCHQ'.
In the current database, the specified object is referenced by the following:
In the current database, the specified object is referenced by the following:
In the current database, the specified object is referenced by the following:
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'SOLKD_DATA'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'SOLKD_NET'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'ALOHA_DATA'.
Server: Msg 15009, Level 16, State 1, Procedure sp_depends, Line 28
The object 'dbo.style' does not exist in database 'ALOHA_NET'.
I Need Desperate Attention........please Guys
Mar 29, 2007hello ppl.......
've installed sql 2005 xpress with advanced services......also installed the toolkit that came along..
all 'm interested in is to perform certain data mining operations on the adventureworks db..
;ve also installed the advn works db.........
now here is the prob.......
jus like how the sql online book instructed me i first started off with the datamining tutorial..where i was told to first OPEN BIDS select new PROJECT then make sure that the analysis Services template was selected and then go ahead with selection of the particular sln file....
the problem is that I DON'T SEE ANY ANALYSIS SERVICE TEMPLATE....all i c is a REPORT template..
i really need help guys..... 'm new to .net really feeling helpless...
please hurry guys....
thx a ton..
hanish
Linked Server On SQL2000 To DB2 - Desperate...
Jul 20, 2005Hi All,I am close before throwing the machine out of the window.I tried to establish a linked server on our SQL2000 server pointing to a DB.EXEC sp_addlinkedserver@server = 'DB2',@srvproduct = 'IBM OLE DB Provider for DB2 Servers',@provider = 'IBMDADB2',@provstr='DSN=JDE_PROD;UID=XXXX;PWD=XXX;DATABASE=J DE_PROD;'EXEC sp_addlinkedsrvlogin 'DB2', false, NULL, 'XXX', 'XXXX'If I query the LS using query analyzer or the Enterprise Manager both startto hang, never responding again.To be honest I am quite new to this topic but I read a lot and also tried alot ;)Some articles talk about SNA Servers to be installed first, others aboutsome client tools.I am confused by now...Could anybody please be so kind and tell me how to connect the DB2 as alinked server?Is my statement wrong, and if so how should it look like? Why does theapplication start to hang? Do I really need to install additional softwareto do so?Our DB2 expert also taked about software necessary to access the DB2mainframe. Any ideas?Thanks a lot in advance and belive me, any hints are highly appreciated ;)Seize the DayAndyAndreas BretlJoin Bytes!
View 4 Replies View RelatedDesperate With SQL2000 Log Shipping , Out Of Sync
May 11, 2007After try and error for several months, finally my SQL2000 LS works.
i gave my self a little celebration for that.
but after running well for a day, just one day !
this morning it give me Out of Sync.
error messages:
failure /4305
activity : Load
[Microsoft SQL-DMO (ODBC SQLState: 42000)]
Error 4305: [Microsoft][ODBC SQL Server Driver][SQL Server]The log in this backup set
begins at LSN 7000000026200001, which is too late to apply to the database. An earlier
log backup that includes LSN 6000000015100001 can be restored.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.
activity : Copy
failure / 32
The process cannot access the file because it is being used by another process.
i just dont understand, how come the load & copy process failed, after it works for a day.
Service Broker Needs Master Key - DESPERATE
Mar 5, 2007Hi ,
Technically i am a network administrator, and know very little about SQL 2005, but it has come under my responsibility to manage this box and a lot depends on fixing this problem for me.
Error:
Service Broker needs to access the master key in the database 'Prototype'. Error code:25. The master key has to exist and the service master key encryption is required.
Also, i get the following both in windows event viewer and SQL logs;
Error:
Source: Logon
Message
Error: 18456, Severity: 14, State: 5.
Login failed for user 'logadmin'. [CLIENT: 192.168.1.2] Event ID 18456;
Login failed for user 'admin'. [CLIENT: 192.168.1.34] Event ID 18456
Symptoms:
While users are working and try to run a new query, their credentials are not accepted anymore. The account they are using is has "sysadmin" rights.
Once they restart the machine, they can run multiple queries, and then the same thign over again.
Please help me with this one, for i have pretty much exhausted google etc. I mean there are various blogs with same prblem but not a straighforward fix.
I would appreciate your help very very much.
Best Regards,
Umer.
Desperate! Aspnet.mdf Screwed Up; User Login Impossible...
Aug 31, 2006I hope anyone can help me with this. I am having problems connecting to the aspnet database. It
all started when I noticed that I could not log in to my webapplication
anymore under debug mode, although I was able to log in when remotely
accessing the website. I got the error message that the machineaspnet
user did not have access to the database: "Login failed for user
'GM-FEDORSASPNET' "This was highly peculiar and rather annoying so I tried several things to straighten this out, but it only made things worse!I
read that this had to with permissions of the database in question, al
though I could not understand why permissions suddenly had changed. I
tried to change the rights by attaching it to Management Studio Express
and defining an aspnet login, but that failed because a login under
that name was not accepted. I tried to make a copy of aspnet.mdf, delete the original and rename the copy to aspnet.mdf. When that didn't work, I then deleted the aspnet.mdf, counting on it that it would be regenerated automatically, but alas. I located the aspnet_regsql.exe tool to regenerate the database, but got the following error message: System.Web.HttpException:
Unable to connect to SQL Server database. --->
System.Data.SqlClient.SqlException: An error has occurred while
establishing a connection to the server. When connecting to SQL Server
2005, this failure may be caused by the fact that under the default
settings SQL Server does not allow remote connections. (provider: Named
Pipes Provider, error: 40 - Could not open a connection to SQL Server)What can I do?
In Desperate Need To Solve A Problem - SQL-DMO (ODBC SQLSTATE: 42000) ERROR 156!
Jan 18, 2008Good morning all. I need some help with a stored proc that is driving me up a wall. It's probably something stairing me right in the face but I can't see it!
I keep getting the following error on the procedure that I'm working on:
SQL-DMO (ODBC SQLSTATE: 42000)
ERROR 156: Incorrect syntax near keyword 'AS'
Must declare the variable '@signid'
Must declare the variable '@signid'
Here's the code:
CREATE PROCEDURE [dbo].[ws_savesignature2db]
@xml as text='',
@image as image='',
@imageformat as varchar(20)='',
@imagename as varchar(40)='',
@imagesize as int=0,
AS
insert into signaturetable
([image], imageformat, imagename, imagesize)
values
(@image, @imageformat,@imagename,@imagesize)
/*
if your XML field datatype is Text or nTEXT, use the code below in Sql Server 2000.
If you use Sql Server 2005, you can use varchar(max) or nvarchar(max) which will be
much easier and you do not need the code.
*/
select @signid = @@IDENTITY
DECLARE @ptrval binary(16)
SELECT @ptrval = TEXTPTR([xml])
FROM ws_signature
WHERE signid= @signid
if @ptrval is not null
WRITETEXT ws_signature.xml @ptrval @xml
GO
Any help you can give would be a life saver!!!!
Thanks in advance.
Jim
Desperate! Problem With Installing Office Enterprise 2007; Wont Read Disk
Aug 5, 2007I own a DELL dimesnsion 4600 desktop. I purchased it with windows xp HOME although I have upgraded to windows xp professional. I am trying to install Office Enterprise 2007 with the disk i purchased. My computer will not pick it up though. I know the cd works because I tried it on another computer but didnt intsall it. That computer read the disk. My computer will not. any help? desperate need
View 1 Replies View RelatedDesperate! - Need Simple MSSQL Server Monitoring Scripts, Ideas, Etc That Can Be Performed By Operator Types (non-programmers)
Jul 20, 2005Does anyone have any basic, simple scripts of sp's that I can give mycomputer operators to use to monitor for serious conditions on our sqlservers? We are new in the ms-sql arena, a small shop and we cantreally purchase any tools to monitor these servers, but we need somebasic checks that we can use to make sure a server is performing in areasonable fashion, no blocks, cpu<xx%, memory not pegged, IO working,etc...and we need to be able to do this without bugging ourprogrammers every time we are wondering why things are slowwnig down.We are a split shop between as400s and sql servers, and our operationsstaff has no problem performing this type of duty on the 400s. I thinksince the sql servers are so easy to deploy into production, the ideaof how to monitor these things health got lost in the shuffle andallowing the programmers to do it just serves to destabilize ourenvironment.Please help! Our shop has turned to chaos since we went live withMSSQL centric applications and everytime one programmer "resolves anissue" some other programmers application starts to act up.
View 6 Replies View RelatedQuery Runs Fine In Query Analyser But Not The Query Debugger
Dec 19, 2003I'm running a query, actually its an insert that works when using the TSQL below.
However when I try to use the debugger to step through and using the exact same values as those below I get the following error:
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
Its Killing me because everything else works, but this. Can somebody help.
DECLARE @NoteID INT,-- NULL OUTPUT,
@Note_Description NVARCHAR(3000),-- = NULL,
@Date DateTime,-- = NULL OUTPUT,
@ByWho NVARCHAR(30),-- = NULL,
@FK_Action_Performed NVARCHAR(40),-- = NULL,
@FK_UserID INT,-- = NULL,
@FK_JobID INT,-- = NULL,
@Job_Date DateTime,-- = NULL,
@Start DateTime,-- = NULL,
@Finish DateTime,-- = NULL,
@BeenRead NVARCHAR(10),-- = NULL
@FK_UserIDList NVARCHAR(4000)-- = NULL
--SET @NoteID = 409 --NULL OUTPUT,
SET @Note_Description = 'Tetsing'
--SET @Date DateTime = NULL OUTPUT,
SET @ByWho = 'GeorgeAgaian'
SET @FK_Action_Performed = 'Worked hard'
SET @FK_UserID = 5
SET @FK_JobID = 29
SET @Job_Date = 28/01/03
SET @Start = '1:00:20 PM'
SET @Finish = '1:00:20 PM'
SET @BeenRead = 'UnRead'
SET @FK_UserIDList = '1,2,3'
--AS
--SET NOCOUNT ON
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRANSACTION
SET @Date = GETDATE()
-- Insert Values into the customer table
INSERT Note (Note_Description,
Date,
ByWho,
FK_Action_Performed,
FK_UserID,
FK_JobID,
Job_Date,
Start,
Finish)
SELECT --@NoteID,
@Note_Description,
@Date,
@ByWho,
@FK_Action_Performed,
@FK_UserID,
@FK_JobID,
@Job_Date,
@Start,
@Finish
-- Get the new Customer Identifier, return as OUTPUT param
SELECT @NoteID = @@IDENTITY
-- Insert new notes for all the users that the note pertains to, in this case this will be by the assigned
-- users.
IF @FK_UserIDList IS NOT NULL
EXECUTE spInsertNotesByAssignedUsers @NoteID, @FK_UserIDList
-- Insert New Address record
-- Retrieve Address reference into @AddressId
-- EXEC spInsertForUserNote
-- @FK_UserID,
--@NoteID,
-- @BeenRead
-- @Fax,
-- @PKId,
-- @AddressId OUTPUT
COMMIT TRANSACTION
--------------------------------------------------
GO
Query Diff Results From Ent Manager Query And Query Analizer
May 28, 2008ok can someone tell me why i get two different answers for the same query. (looking for last day of month for a given date)
SELECT DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, CAST('12/20/2006' AS datetime)) + 1, 0)) AS Expr1
FROM testsupplierSCNCR
I am getting the result of 01/01/2007
but in query analizer I get the result of
12/31/2006
Why the different dates
[Query] - Query Designer Encountered A Query Error:Unspecified Error
Jan 22, 2001Hi,
I get this error dialog when I try to open all the rows of any table from Enterprise manager..
Any help would be really appreciated..
Thanks,
-Srini.
Error: 8624 Internal Query Processor Error: The Query Processor Could Not Produce A Query Plan.
May 24, 2007SQL Server 2005 9.0.3161 on Win 2k3 R2
I receive the following error:
"Error: 8624, Severity: 16, State: 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."
I have traced this to an insert statement that executes as part of a stored procedure.
INSERT INTO ledger (journal__id, account__id,account_recv_info__id,amount)
VALUES (@journal_id, @acct_id, @acct_recv_id, @amount)
There is also an auto-increment column called id. There are FK contraints on all of the columns ending in "__id". I have found that if I remove the contraint on account__id the procedure will execute without error. None of the other constraints seem to make a difference. Of course I don't want to remove this key because it is important to the database integrity and should not be causing problems, but apparently it confuses the optimizer.
Also, the strange thing is that I can get the procedure to execute without error when I run it directly through management studio, but I receive the error when executing from .NET code or anything using ODBC (Access).
Query Works In 'test Query' But Refuses To Show Up In The Datagrid On A Web Page - Urgent!
Mar 28, 2007Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows.
SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches
FROM dbo.MAKES INNER JOIN
dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN
dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN
dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN
dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID
WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )
GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID
HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2
ORDER BY count(*) DESC
Here is the page source
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="	SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches 	FROM dbo.MAKES INNER JOIN 				 dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN 				 dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN 				 dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN 				 dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID 	WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) 	GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID 	HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 	ORDER BY count(*) DESC ">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" />
<asp:Parameter Name="ATT_ID2" />
<asp:Parameter Name="VAL2" />
<asp:Parameter Name="ATT_ID3" />
<asp:Parameter Name="VAL3" />
<asp:Parameter Name="ATT_ID4" />
<asp:Parameter Name="VAL4" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource>
<br />
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"
DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID">
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False"
ReadOnly="True" SortExpression="DERIVATIVE_ID" />
<asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" />
</Columns>
</asp:GridView>
</asp:Content>
AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback).
Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage.
Please help!
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
SQL Server 2012 :: Adding Count To Query Without Duplicating Original Select Query
Aug 5, 2014I have the following code.
SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,
[code]...
However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.
Opening Up Odbc Data Source In The Query Query Inside Of The Server Manager
Jun 15, 2007I'm trying to find the command to open up an odbc conection inside sql2005 express. I only have ues of an odbc connector, we're conection to remedy. We will eventually be using stored procedures to extract the data we need from remedy and doing additional data crunching. I'm a foxpro programmer so once I get the correct syntax for making the odbc connector I shold be ok. Also I need a really good advanced book on sql2005. The type of book that would have my odbc answer. I've spent all morning trying to find this information and was unable to.
Thanks in advance
Daniel Buchanan.
If this was the wrong forum to post this on, please move this question to the correct one. I need this answer soon.
Master Data Services :: Error - Query Processor Could Not Produce A Query Plan
Jul 19, 2015We have a issue with a MDS server that have been over us for a couple of days, the original error msg from SQL Server Engine is the one "The query processor could not produce a query plan" but the ones we get on the Excel-Addin are "Sequece contains no elements" or "The value cannot be null" T
• Using Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) for 6months on this server without issues
• Two weeks ago we started to have 2 errors: "Sequence Contains No Elements" | "The Value Cannot Be Null"
• We are using the last version of Excel Add-in
• We try to reinstall the MDS feature
• If I backup/restore MDS database to other server it works
• We updated to SQL 2012 SP2 + CU4 but the error persisted ...
Looking at the MDSTraceLog we are routed to the this msg
SQL Error Debug Info: Number: 8624, Message: Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services., Server: bbdvsql03inst01, Proc: udpMetadataEntityGetDetailsXML, Line: 28
At line 28 udpMetadataEntityGetDetailsXML is calling udfMetadataEntityGetDetailsXML … and here is where we stopped
** Error found when try to get data from a entity using Excel add-in **
===================================
Sequence contains no elements
------------------------------
Program Location:
  at Microsoft.MasterDataServices.AsyncEssentials.AsyncResultBase.EndInvoke()
  at Microsoft.MasterDataServices.ExcelAddInCore.AsyncProviderBase`1.EndOperation(IAsyncResult ar)
[code]....
SQL Server 2012 :: How To Pull Value Of Query And Not Value Of Variable When Query Using Select Top 1 Value From Table
Jun 26, 2015how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...
create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)
[code]...
Query Fails With Invalid Column Name But Succeed As Sub-query With Unexpected Results
Sep 22, 2015-- The 3rd query uses an incorrect column name in a sub-query and succeeds but rows are incorrectly qualified. This is very DANGEROUS!!!
-- The issue exists is in 2008 R2, 2012 and 2014 and is "By Design"
set nocount on
go
if object_id('tempdb.dbo.#t1') IS NOT NULL drop table #t1
if object_id('tempdb.dbo
[code]....
This succeeds when the invalid column name is a valid column name in the outer query. So in this situation the sub-query would fail when run by itself but succeed with an incorrectly applied filter when run as a sub-query. The danger here is that if a SQL Server user runs DML in a production database with such a sub-query which then the results are likely not the expected results with potentially unintended actions applied against the data. how many SQL Server users have had incorrectly applied DML or incorrect query results and don't even know it....?
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
DB Engine :: Multiple Execution Of Query Pattern Generates Same Query Plan
Oct 6, 2015SQL Server 2012 Performance Dashboard Main advices me this:
Since the application is from a vendor and I have no control over its code, how can improve this sitation?
Transact SQL :: Adding Results Of Query To Another Query Via Dynamically Added Columns
Jul 30, 2015For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
I want to do it using SELECT. Is it possible?
Can A Calc'd Query Column Be Compared Against A Multi Value Variable Without A Nested Query?
Nov 15, 2007do i need to nest a query in RS if i want a calculated column to be compared against a multi value variable? It looks like coding WHERE calcd name in (@variable) violates SQL syntax. My select looked like
SELECT ... ,CASE enddate WHEN null then 1 else 0 END calcd name
FROM...
WHERE ... and calcd name in (@variable)
SQL Server Admin 2014 :: Estimated Query Plan For A Stored Procedure With Multiple Query Statements
Oct 30, 2015When viewing an estimated query plan for a stored procedure with multiple query statements, two things stand out to me and I wanted to get confirmation if I'm correct.
1. Under <ParameterList><ColumnReference... does the xml attribute "ParameterCompiledValue" represent the value used when the query plan was generated?
<ParameterList>
<ColumnReference Column="@Measure" ParameterCompiledValue="'all'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
2. Does each query statement that makes up the execution plan for the stored procedure have it's own execution plan? And meaning the stored procedure is made up of multiple query plans that could have been generated at a different time to another part of that stored procedure?
Stored Procedure Dbo.SalesByCategory Of Northwind Database: Enter The Query String - Query Attempt Failed. How To Do It Right?
Mar 25, 2008Hi all,
In the Programmability/Stored Procedure of Northwind Database in my SQL Server Management Studio Express (SSMSE), I have the following sql:
USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[SalesByCategory] Script Date: 03/25/2008 08:31:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SalesByCategory]
@CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'
AS
IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998'
BEGIN
SELECT @OrdYear = '1998'
END
SELECT ProductName,
TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)
FROM [Order Details] OD, Orders O, Products P, Categories C
WHERE OD.OrderID = O.OrderID
AND OD.ProductID = P.ProductID
AND P.CategoryID = C.CategoryID
AND C.CategoryName = @CategoryName
AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
From an ADO.NET 2.0 book, I copied the code of ConnectionPoolingForm to my VB 2005 Express. The following is part of the code:
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Text
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Diagnostics
Public Class ConnectionPoolingForm
Dim _ProviderFactory As DbProviderFactory = SqlClientFactory.Instance
Public Sub New()
' This call is required by the Windows Form Designer.
InitializeComponent()
' Add any initialization after the InitializeComponent() call.
'Force app to be available for SqlClient perf counting
Using cn As New SqlConnection()
End Using
InitializeMinSize()
InitializePerfCounters()
End Sub
Sub InitializeMinSize()
Me.MinimumSize = Me.Size
End Sub
Dim _SelectedConnection As DbConnection = Nothing
Sub lstConnections_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles lstConnections.SelectedIndexChanged
_SelectedConnection = DirectCast(lstConnections.SelectedItem, DbConnection)
EnableOrDisableButtons(_SelectedConnection)
End Sub
Sub DisableAllButtons()
btnAdd.Enabled = False
btnOpen.Enabled = False
btnQuery.Enabled = False
btnClose.Enabled = False
btnRemove.Enabled = False
btnClearPool.Enabled = False
btnClearAllPools.Enabled = False
End Sub
Sub EnableOrDisableButtons(ByVal cn As DbConnection)
btnAdd.Enabled = True
If cn Is Nothing Then
btnOpen.Enabled = False
btnQuery.Enabled = False
btnClose.Enabled = False
btnRemove.Enabled = False
btnClearPool.Enabled = False
Else
Dim connectionState As ConnectionState = cn.State
btnOpen.Enabled = (connectionState = connectionState.Closed)
btnQuery.Enabled = (connectionState = connectionState.Open)
btnClose.Enabled = btnQuery.Enabled
btnRemove.Enabled = True
If Not (TryCast(cn, SqlConnection) Is Nothing) Then
btnClearPool.Enabled = True
End If
End If
btnClearAllPools.Enabled = True
End Sub
Sub StartWaitUI()
Me.Cursor = Cursors.WaitCursor
DisableAllButtons()
End Sub
Sub EndWaitUI()
Me.Cursor = Cursors.Default
EnableOrDisableButtons(_SelectedConnection)
End Sub
Sub SetStatus(ByVal NewStatus As String)
RefreshPerfCounters()
Me.statusStrip.Items(0).Text = NewStatus
End Sub
Sub btnConnectionString_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnConnectionString.Click
Dim strConn As String = txtConnectionString.Text
Dim bldr As DbConnectionStringBuilder = _ProviderFactory.CreateConnectionStringBuilder()
Try
bldr.ConnectionString = strConn
Catch ex As Exception
MessageBox.Show(ex.Message, "Invalid connection string for " + bldr.GetType().Name, MessageBoxButtons.OK, MessageBoxIcon.Error)
Return
End Try
Dim dlg As New ConnectionStringBuilderDialog()
If dlg.EditConnectionString(_ProviderFactory, bldr) = System.Windows.Forms.DialogResult.OK Then
txtConnectionString.Text = dlg.ConnectionString
SetStatus("Ready")
Else
SetStatus("Operation cancelled")
End If
End Sub
Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click
Dim blnError As Boolean = False
Dim strErrorMessage As String = ""
Dim strErrorCaption As String = "Connection attempt failed"
StartWaitUI()
Try
Dim cn As DbConnection = _ProviderFactory.CreateConnection()
cn.ConnectionString = txtConnectionString.Text
cn.Open()
lstConnections.SelectedIndex = lstConnections.Items.Add(cn)
Catch ex As Exception
blnError = True
strErrorMessage = ex.Message
End Try
EndWaitUI()
If blnError Then
SetStatus(strErrorCaption)
MessageBox.Show(strErrorMessage, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
SetStatus("Connection opened succesfully")
End If
End Sub
Sub btnOpen_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnOpen.Click
StartWaitUI()
Try
_SelectedConnection.Open()
EnableOrDisableButtons(_SelectedConnection)
SetStatus("Connection opened succesfully")
EndWaitUI()
Catch ex As Exception
EndWaitUI()
Dim strErrorCaption As String = "Connection attempt failed"
SetStatus(strErrorCaption)
MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
Sub btnQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click
Dim queryDialog As New QueryDialog()
If queryDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
Me.Cursor = Cursors.WaitCursor
DisableAllButtons()
Try
Dim cmd As DbCommand = _SelectedConnection.CreateCommand()
cmd.CommandText = queryDialog.txtQuery.Text
Using rdr As DbDataReader = cmd.ExecuteReader()
If rdr.HasRows Then
Dim resultsForm As New QueryResultsForm()
resultsForm.ShowResults(cmd.CommandText, rdr)
SetStatus(String.Format("Query returned {0} row(s)", resultsForm.RowsReturned))
Else
SetStatus(String.Format("Query affected {0} row(s)", rdr.RecordsAffected))
End If
Me.Cursor = Cursors.Default
EnableOrDisableButtons(_SelectedConnection)
End Using
Catch ex As Exception
Me.Cursor = Cursors.Default
EnableOrDisableButtons(_SelectedConnection)
Dim strErrorCaption As String = "Query attempt failed"
SetStatus(strErrorCaption)
MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
Else
SetStatus("Operation cancelled")
End If
End Sub
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I executed the code successfully and I got a box which asked for "Enter the query string".
I typed in the following: EXEC dbo.SalesByCategory @Seafood. I got the following box: Query attempt failed. Must declare the scalar variable "@Seafood". I am learning how to enter the string for the "SQL query programed in the subQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click" (see the code statements listed above). Please help and tell me what I missed and what I should put into the query string to get the information of the "Seafood" category out.
Thanks in advance,
Scott Chang
SP To Perform Query Based On Multiple Rows From Another Query's Result Set
Nov 7, 2007I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w. one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included) INSERT INTO @users SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is: SELECT u.userName,u.userID
FROM tableU u
LEFT JOIN tableW w ON w.workgroupID = u.workgroupID
WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!