MS SQL Server :: Parameterized Order By Clause: Doesn't Work

Order By Clause In View Doesn't Order.
I have created view by jaoining two table and have order by clause.

The sql generated is as follows

SELECT     TOP (100) PERCENT dbo.UWYearDetail.*,  dbo.UWYearGroup.*
FROM         dbo.UWYearDetail INNER JOIN
                      dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId = dbo.UWYearGroup.UWYearGroupId
ORDER BY dbo.UWYearDetail.PlanVersionId, dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear, dbo.UWYearGroup.MandDFlag,
                      dbo.UWYearGroup.EarningsMethod, dbo.UWYearGroup.EffectiveMonth

If I run sql the results are displayed in proper order but the view only order by first item in order by clause.

Has somebody experience same thing? How to fix this issue?

Thanks,

View Replies !

ORDER BY Clause Does Not Work In SQL 2005?
Hi,
A quick background on the problem;
My company is in the process of a migration from Windows Advanced Server 2K, SQL 2K to Server 2003 and SQL 2005. I'm not certain of the exact process used by our DBA to convert the DB, but I can access it, and all my tables/views/sprocs appear to be in the right place.
I copied all my web files to our new server after the DBA was done with her job, made a new user on the new instance of SQL server, changed a few connection strings in my global.asa and global.asax, and ta-dah! Just like magic, the new site opened on our new servers without much resistance.
Except....
None of the content on our sites is sorted. I cannot seem to get ORDER BY statements to work at all. They appear to be disregarded by SQL server when not in MODIFY mode for a particular view (in SQL Server Management Studio).
So, when I MODIFY a view, add criteria (NOT SORT), save the changes, then OPEN, the criteria is respected. The filter is applied. BUT...
If I MODIFY a view and add a SORT using ORDER BY (by hand or with the Manager) the sort is NEVER respected when the view is Opened through the manager or in my code.
If I open any sorted view and then click MODIFY, and then RUN (without making ANY changes), the sort works with no problem whatsoever.
To summarize/restate my case, if I OPEN any view in the system that has an ORDER BY criteria, the sort is NOT APPLIED. If I instead right-click and MODIFY, then click RUN, the SORT is APPLIED.
I've tried sorting datetime and text fields, all with the same results - none. This single dumb issue has been delaying the migration of our servers for days!
Can anyone help?

Thanks always in advance,
Drew

View Replies !

'Order By' Clause Work Incorrect
when i try the following SQL batch, I get a result-set which is not order by
datetime column 'out_date',but if I delete clause INTO #fifo_temp, I get a correct result with correct order.

who can help me?thanks in advance
...
select tag,stuff_id,stuff_name,cast(out_id as char(10)) as out_id,out_number,out_date,out_qty,remark
INTO #fifo_temp from ##stuff_fifo UNION
select tag,stuff_id,stuff_name,out_id,null,out_date,quant ity,remark
from acc_cost.dbo.stuff_out where tag='A' and left(out_id,3) in ('XSA','TAP')
ORDER BY out_date

DROP TABLE ##stuff_fifo
select * from #fifo_temp

the following can get a correct result:

select tag,stuff_id,stuff_name,cast(out_id as char(10)) as out_id,out_number,out_date,out_qty,remark
from ##stuff_fifo UNION
select tag,stuff_id,stuff_name,out_id,null,out_date,quant ity,remark
from acc_cost.dbo.stuff_out where tag='A' and left(out_id,3) in ('XSA','TAP')
ORDER BY out_date

View Replies !

Order By Doesn't Work Properly When There Are Null Values?
Hello all,The followinq qurey returns sometimes values of null to some of this columns, byK,byT,byD. the column F wil not contains any nulls, and 0 will be populated in it at any case of .Now, the problem is that when sorting out F the sort will not work when there is null parameters in byK because teh fact that a 0 values is greater then NULL value, and the sort of F will not take in considiration. So I guess the question is how can I sort NULL values and 0 values to be the same weight in the sort by command? SELECT A.gym_id as gym,s_id, week, gym_name, boxer, league, sum(points)
points,sum(byK)as byK, sum(byT) as byT,sum(byPoints) as byPoints ,
sum(byD) as byD, count(C.gym) as F
FROM A inner JOIN B ON A.gym_id = B.gym_id
left JOIN C ON A.gym_id = C.gym
WHERE (B.l_id = ?text
group by A.gym_id
order by points DESC,byK DESC,byT DESC, byPoints DESC, byD DESC,F ASC

View Replies !

Same Order Doesn't Work - Invalid Descriptor Index
Hi all,

I'm getting this "invalid descriptor index" exception while trying to fetch a record from the table.
The query is "select * from <tablename> where <columnname> = 'xyz'". The column name is correct and indeed a record with 'xyz' value exists. The record is getting fetched too...! But I'm having this particular error while trying to retrieve a couple of fields with rs.getString().
The order of columns in the table is same as the order in which I'm retrieving them. And I'm not facing any problem retrieving another field which has width of 200 characters.
I'll be very grateful indeed if someone can help me out of this particular problem...

Cheers, mates!

View Replies !

Parameterized Where Clause
Hello,I have an add stored procedure in Yukon (would work in 2000 too), where I select the ID from the table to make sure that it doesn't already have the data. So it looks like:create procedure ....set transaction isolation level serializablebegin transactiondeclare @ID intselect @ID = rowid from tblBusinessInformation where Name = @Name and Rules = @Rulesif ( @ID is NULL ) begin insert into tblBusinessInformation (..) values (@Name, @Rules) endcommit transactionThe problem is the values could be:Name RulesNULL 'Test''Test' NULL'Test' 'Test'When one of the values was NULL, it would never select the ID, unless I changed it to "where Name is @Name", and then it worked, because where Name is NULL, which is correct in SQL; so how do I allow for both; I can use the CLR, but would like to avoid rewriting the proc if possible, and I thought that was to work...Thanks.

View Replies !

Parameterized SP In WHERE Clause Of Another SP
I've got this SP:CREATE PROCEDUREEWF_spCustom_AddProfiles_CompanyYear@prmSchoolYear char(11)ASSELECTContactIDFROMdbo.EWF_tblCustom_CompanyProfileWHERESchoolYear = @prmSchoolYearI'd like to be able to reference that in the where clause of anotherSP. Is that possible?I'd like to end up with something like this:CREATE PROCEDUREMyNewProc@prmSchoolYear2 char(11)ASSELECTContactID, SomeOtherFieldsFROMtblContactWHEREContactID IN (exec EWF_spCustom_AddProfiles_CompanyYear@prmSchoolYear2)How would I make that happen?If this isn't possible, what else might I try?Thanks much for any pointers.JeremyPS: I accidentally crossposted this in another group(http://tinyurl.com/gksq4) thinking it was this one. Sorry for that.

View Replies !

How To Use Parameterized Queries With IN Clause
Hi,
I need to use parameters with the IN clause in a SQL statement like:
select * from tableX where field IN (1,2,3,4)
I don't know how to do that.
I'm using SQLServer and OleDB.

Thanks for your help.

View Replies !

Using DTS Parameterized Query 'IN' Where Clause
I want to export an SQL Server table to an Excel Spreadsheet driven by a web interface.
I am using Cold Fusion to call a SQL Server Stored procedure. The SP accepts a variable (IDlist) from the web page and sets this to a Global Variable.

EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("outIDlist").Value', @outIDlist

The SP then executes a DTS package to export to Excel. The DTS package uses the Global variable in the SQL Query thus:

SELECT ...
FROM ...
WHERE tblPropertyRegister.IDProperty IN (?);

This works fine when I pass one single ID (@outIDlist = "20") into the stored procedure.
But it returns no records when I pass multiple IDs (@outIDlist = "19, 20, 21") into the stored procedure. It works fine also if I "hard code" the IDlist into the DTS query (eg WHERE tblPropertyRegister.IDProperty IN (19, 20, 21);).
The problem appears to be in the setting of the global variable in the stored procedure.

Has anyone had any experience with this? Any feed back would be greatly appreciated. TIA

Alan

View Replies !

Parameterized IN Clause In Dynamic SQL
Hi,

I am trying to build a parameterized query where I pass a set of integer values into the dynamic sql. Please see below example.

DECLARE @SQLQUERY NVARCHAR(4000)
DECLARE @PARAMDEF NVARCHAR(1000)
DECLARE @VALUES VARCHAR(100)

SET @PARAMDEF = N'@IN_VAL VARCHAR(100)'

SET @VALUES = '1,2,3,4'

SET @SQLQUERY = 'SELECT * FROM TABLEA WHERE COLUMNA IN (@IN_VAL)'

EXEC SP_EXECUTESQL @SQLQUERY,@PARAMDEF,@IN_VAL=@VALUES

This fails with the error "cannot convert varchar to numeric". I believe since ColumnA is numeric, its trying to convert the dynamic paramter to numeric leading to the failure.

has someone implemented an In clause as a parameter? Please do not tell me that I can append the values as string and construct a dynamic query. I want to use a parameterized version. I will be calling this repeatedly and dont want recompile overhead.

TIA

View Replies !

Inconsistent Sort Order Using ORDER BY Clause
I am getting the resultset sorted differently if I use a column number in the ORDER BY clause instead of a column name.

Product: Microsoft SQL Server Express Edition
Version: 9.00.1399.06
Server Collation: SQL_Latin1_General_CP1_CI_AS

for example,

create table test_sort
( description varchar(75) );

insert into test_sort values('Non-A');
insert into test_sort values('Non-O');
insert into test_sort values('Noni');
insert into test_sort values('Nons');

then execute the following selects:
select
*
from
test_sort
order by
cast( 1 as nvarchar(75));

select
*
from
test_sort
order by
cast( description as nvarchar(75));

Resultset1
----------
Non-A
Non-O
Noni
Nons

Resultset2
----------
Non-A
Noni
Non-O
Nons

Any ideas?

View Replies !

Select .. NOT IN Clause Doesn't Return Anything?
Hi there,

It's a very strange thing!
I havea a table called invoices, and a table calle customer payments which has the invoiceID of the payment.

I have many invoices that haven't been paid (so they don't have a record on the customer payments). I know this, as i can for example do:
select * from invoices where invoiceID = 302247 (and i'll get one result)
select * from customer_payments where invoice = 302247 (and i'll get none results)

however, if i do the following:
select * from invoices where invoice_id not in
(select invoice_id from customer_payments)
I get nothing!!!???

It doesn't make any sense, as I should get at least 300 (including the 302247) - both invoiceids fields are int... so i just don't understand what's wrong?

thank you so much for any help!

Grazi

View Replies !

Why Doesn't This Work
When I run the select its fine but I cannot delete..... i have done this many times and it has worked.... I cannot see the error what am i missing

select
eqnow.empnumber,
eqnow_names.empnumber,
eqnow_names.names
--delete
from
eqnow
inner join eqnow_names
on eqnow.empnumber = eqnow_names.empnumber
where
eqnow_names.names is null

i get this error
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'inner'.

View Replies !

DTS Doesn't Work Through Job
I'm pretty new to DTS, so forgive me if this is basic. I created a simple DTS package to run a query and export it to a text file. I can execute the package fine from my workstation through EM, but when I try to execute the job to run the package I get this error:
Error = -2147467259 (80004005) Error string: Error opening datafile: Access is denied.

I think that maybe SQL Agent doesn't have the right permissions to write to that network drive. What should the permissions be?

View Replies !

IIF Doesn't Work
This is probably very simple, but I can't get passed this problem.

I have a report in MS Access that uses info generated by a query. One of the text fields in the query contains either the word 'Select' or the name of a course.
The report should display a space if the value is 'Select', or the actual value of the field in any other case. The field can never contain a null value.

I've used:
=IIf([optVoc1]="Select","",[optVoc1])
in the text box on the report, but this only returns #error regardless of the actual content of the field.

What am I doing wrong?

Regards,

BD

View Replies !

Sql Job Doesn't Work
Hi all,

I create and schedule a SQL job to run every minute to update a table base on certain condition but it doesn't work. Job history says successful every time but the table doesn't get updated.

However if I move it to Query Analyzer and run it under dba, it will work. Thinking that it may have to do with the user the job run as, I then change run as user from self to dba. But still SQL job won't update my table.

Anything about user permission or security that I can check? Or it there any other possibility?

TIA

View Replies !

Doesn't Work

Msg 15123, Level 16, State 1, Procedure sp_configure, Line 78

The configuration option 'user instances enabled' does not exist, or it may be an advanced option.

Valid configuration options are

View Replies !

Help! LIKE Doesn't Work!!!
Hi to all, I'm building (and learn) an application with VB Express. In "edit dataset with designer" I've build this sql query:

SELECT     tbl_soggetto.[ID Soggetto], tbl_soggetto_tipo.Tipo, tbl_soggetto.[Cognome/Denominazione], tbl_soggetto.Nome, tbl_soggetto.Indirizzo, tbl_soggetto.CAP, tbl_soggetto.CittÃ , tbl_soggetto.Provincia, tbl_soggetto.[Telefono 1], tbl_soggetto.[Telefono 2], tbl_soggetto.[Telefono 3], tbl_soggetto.[Telefono 4], tbl_soggetto.[eM@il 1], tbl_soggetto.[eM@il 2], tbl_soggetto.Note
FROM         tbl_soggetto INNER JOIN tbl_soggetto_tipo ON tbl_soggetto.[ID Tipo] = tbl_soggetto_tipo.[ID Tipo]
WHERE     (tbl_soggetto.[Cognome/Denominazione] LIKE '%' + @Testo + '%')

The LIKE doesn't work!
I call the query with   Me.griglia.DataSource = Me.TA_tbl_soggetto_ricerca.Search_Cognome(Me.txt_trova.Text.Trim)

But with   LIKE '%ABC%'   work!

Me.griglia.DataSource = Me.TA_tbl_soggetto_ricerca.Search_Cognome()

Someone can help me? Thanks...

View Replies !

SET Doesn't Work
When I try to install the problem I get the following error.

The SQL Server service failed to start. For more information, see the SQL Server Books Online topics, "How to: View SQL Server 2005 Setup Log Files" and "Starting SQL Server Manually."

The log tells me nothing useful. I can't start the thing manually because after clicking cancel on the error message, the installer proceeds to roll back the installation.

How do I fix this problem?

View Replies !

Oracle Parameterized Queries To Update Oracle Table Do Not Work
Oracle and MS drivers do not support parameterized queries, so update table set column=? where primarykey=? does not work for Oracle.

Anyone knows how to update an Oracle table through SSIS?

Thanks!

Wenbiao

View Replies !

Sum In Subquery Doesn't Work Well
This is the autogenerated code from the SelectCommand of my DataAdapter, except the red text. This DataAdapter is used to fill a DataGrid. What I want to do, is to calculate the total memory (4 slots) / PC.This code makes the sum of all memory of all PC's together.I'm not sure if the group by clause is needed here ...Me.OleDbSelectCommand1.CommandText = "SELECT PC.ID, PC.Nummer, PC.Netwerknaam, Case_Type.Type AS Case_Type, Processor_T" & _"ype.Type AS Processor_Type, Processor_Snelheid.Snelheid AS Processor_Snelheid, " & _"(SELECT SUM(Memory) FROM Memory, PC, RAM WHERE RAM.PcID = PC.ID AND RAM.GrootteID = Memory.ID)" & _"AS Memory, OS.Naam AS OS, OS_SP.Nummer AS OS_SP, Gebru" & _"iker.Naam AS Gebruiker_Naam, Status.Status, PC.Tagged FROM (Status RIGHT OUTER J" & _"OIN ((((((((PC LEFT OUTER JOIN (RAM LEFT OUTER JOIN Geheugen ON RAM.GrootteID = " & _"Geheugen.ID) ON PC.ID = RAM.PcID) LEFT OUTER JOIN Case_Type ON PC.Case_TypeID = " & _"Case_Type.ID) LEFT OUTER JOIN OS_SP ON PC.OS_SpID = OS_SP.ID) LEFT OUTER JOIN Ge" & _"bruiker ON PC.GebruikersID = Gebruiker.ID) LEFT OUTER JOIN Processor_Snelheid ON" & _" PC.Processor_SnelheidID = Processor_Snelheid.ID) LEFT OUTER JOIN Processor_Type" & _" ON PC.Processor_TypeID = Processor_Type.ID) LEFT OUTER JOIN OS ON PC.OsID = OS." & _"ID) LEFT OUTER JOIN Switchbox_Details ON PC.ID = Switchbox_Details.PcID) ON Stat" & _"us.ID = PC.StatusID) GROUP BY PC.ID, PC.Nummer, PC.Netwerknaam, Case_Type.Type, " & _"Processor_Type.Type, Processor_Snelheid.Snelheid, OS.Naam, OS_" & _"SP.Nummer, Gebruiker.Naam, Status.Status, PC.Tagged"I would like to know how to calculate the total memory for each separate PC.Hope you can help me.

View Replies !

Osql Doesn't Work
Dear GroupI'd be grateful if you can help me with the following. I'm trying toconnect to an instance of MSDE called BALTD.The instance has SQL authentication enabled and the service is running.Somehow it doesn't what it should do according to MSDN.When I try connectiong with the following (and variations of it) itjust doesn't do anything. Any ideas why?osql -u sa -s localMSSQL$BALTDC:Documents and SettingsMartin>osql -u sa -s localMSSQL$BALTDusage: osql [-U login id] [-P password][-S server] [-H hostname] [-E trusted connection][-d use database name] [-l login timeout] [-t query timeout][-h headers] [-s colseparator] [-w columnwidth][-a packetsize] [-e echo input] [-I Enable QuotedIdentifiers][-L list servers] [-c cmdend] [-D ODBC DSN name][-q "cmdline query"] [-Q "cmdline query" and exit][-n remove numbering] [-m errorlevel][-r msgs to stderr] [-V severitylevel][-i inputfile] [-o outputfile][-p print statistics] [-b On error batch abort][-X[1] disable commands [and exit with warning]][-O use Old ISQL behavior disables the following]<EOF> batch processingAuto console width scalingWide messagesdefault errorlevel is -1 vs 1[-? show syntax summary]C:Documents and SettingsMartin>Thanks very much for your help and efforts!Martin

View Replies !

Identity Doesn't Work
I'm working with mssql 6.5

I have an primary key column with Identity property.
And at the moment server doesn't insert the proper value to this column.

Error message is following

Msg 2601, Level 14, State 3
Attempt to insert duplicate key row in object 'Spot' with unique index 'XPKSpot'
Command has been aborted.

The datatype of this column is int, and number of rows ~17000.
If I execute select @@identity it returns null.

View Replies !

Trigger - This One Doesn't Work - Why?
Hi,

I wanted to create a new trigger, but Enterprise Manager tells me about an "Incorrect syntax near @UpdatedByID, line 28". I double-checked everything, but it still does not work :mad: .

Any hints :confused: ?

TIA,

-Gernot

Here is the statement (line 28 is marked with ***):

CREATE TRIGGER TransferToABII ON [dbo].[CALGeneral]
FOR INSERT
AS
BEGIN TRANSACTION
BEGIN
DECLARE @Event varchar(255),
@BBaseUID int,
@StartDate smalldatetime,
@EndDate smalldatetime,
@Details varchar(255),
@AddressID int,
@ProjectID int,
@UpdatedByID int,
@ActID int,
@EventID int

SELECT @Event = Event,
@BBaseUID = BBaseUID,
@StartDate = StartDate,
@EndDate = EndDate,
@Details = Details,
@AddressID = AddressID,
@UpdatedByID = UpdatedBy,
@ProjectID = ProjectID
FROM INSERTED

BEGIN
EXEC BrainBase.dbo.BB_NEW_CREATE_NoteTask_Ret *** (@UpdatedByID,
@AddressID,
@ProjectID,
@BBaseUID,
@StartDate,
GetDate(),
@Event,
NULL,
NULL,
NULL,
NULL,
@Details text,
@ActID = @ActID OUTPUT,
@EventID = @EventID OUTPUT)
END
BEGIN
UPDATE CALGeneral SET ActID = @ActID WHERE ID = INSERTED.ID
END
END

IF @@ERROR <> 0
BEGIN
RAISERROR('Error occured',16,1)
ROLLBACK TRANSACTION
END
COMMIT TRANSACTION

View Replies !

Query Doesn't Work
Hello!
I created query which should update first 100 rows in the table.
When I run it I have the message:The command(s) completed successfully.
But when I run select*from sales I can see that nothing have been changed.
Where is my mistake in this query:

update sales
set qty = '2'
where convert (varchar(4),qty)in (select top 3 ord_num
from sales
where stor_id = '8042' and payterms = 'net 30')

Thank you,
Elena

View Replies !

Good DTS Doesn't Work As Job
Anyone have a clue how to fix this error. The dts package works fine when run outside the job but fails when run as a job. The job is run by the sql server admisistrator. Everything is local(sql agent, database, etc.). We are sure it is a permission problem but where?

DTSRun: Loading... Error: -2147008507 (80074005); Provider Error: 0 (0) Error string: Unspecified error Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts.hlp Help context: 713 Error: -2147467259 (80004005); Provider Error: 6 (6) Error string: [DBNMPNTW]Specified SQL server not found. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code 1. The step failed.ob. Everything is local and being run by the administrator. We are sure it is a permission problem but where?

View Replies !

HELP! Sp_attach_db Doesn't Work!
I had a SQL Server falure. I rebiuld Master and tried to attach my database
with sp_attach_db? but get an error

Location: pageref.cpp:3931
Expression: rowLog.RowCount () == 1 || pPage->IsEmpty ()
SPID: 10
Process ID: 119

Connection Broken

View Replies !

Attaching Db Doesn't Work
I try to copy a DB from one server to another. On the target server an older version of the DB has been deleted and I now try to attach the new version using "sp_attach_db DBname, Filelocation", but I always get an error "Device Activation error. The physical file name 'D:mssql7dataAgency_log.ldf' may be incorrect"
"Database 'Agency' cannot be Created"

To me it seems that the database is looking for the log files (now deleted).
I've tried forcing a new log file I created using the same locations for the mdfs. I've tried using create a new database and replace the mdf file, but nothing works.

View Replies !

WHERE With An Alias Doesn't Work
I'm combining first name, last name, middle name, and an ID number together into an alias. Then I need to match that alias with a variable passed to the page (its a search results page). The problem is it claims that there is no table with the name of my alias. Anyone know what I'm doing wrong?

A mockup of the SQL looks like this:

SELECT UserID, Last_Name + ', ' + First_Name + ' ' + Middle_Name + '.' AS name
FROM Table
WHERE name LIKE 'variable%'

Everything looks right with the results, if I take out the WHERE clause it has name displayed properly and joined together with the rest of the data in the results properly.

Thanks in advance for any help that can be provided!

View Replies !

VbCode Why Doesn't It Work.
Why doesn't my code work. I am working with vb.net 2003 and I was following an example in .net 2005.The code is very short. I am only having a problem with one line. If someone test this out on there 2003 version You must have an xml file in th same folder that you have you executable named cars.xml. This code was placed in a button click event. The xml file looks like this. The word Oldsmobole Should appear in the textbox. HEELLLLPPP!!! I keep having a problemn with line 4, of which I highlighted in red.

<? xml version="1.0" encoding ="utf-8" ?>

<carlot>

              <car><Make>Oldsmobile</Make>

              </car>

</carlot>

1      Dim sr As New System.IO.StreamReader("Cars.xml")

2      Dim xr AS New System.Xml.XmlTextReader(sr)

3      Dim CarLotDoc AS New System.Xml.XmlDocument

4      CarLotDoc.Load(xr)

5      Label1.Text = CarLotDoc.InnerText

View Replies !

Why Doesn't This DecryptByKeyAutoCert Work?
This works:

-- authenticator

CREATE FUNCTION [dbo].[cc2_Helper]( @SecretData VARBINARY(256), @cId INT )

RETURNS NVARCHAR(50)

WITH EXECUTE AS 'DBO'

AS

BEGIN

RETURN convert( NVARCHAR(50), decryptbykeyautocert( cert_id( 'cert_SecretTable_SecretData_Key' ), null, @SecretData, 1, convert(varbinary, @cId) ) )

END

go

CREATE VIEW [dbo].[cc2View2]

AS

SELECT CardID as CardID, [dbo].[cc2_Helper](CardNumber, CardID) as CardNumber FROM [dbo].[cc2]

go

GRANT SELECT ON [dbo].[cc2View2] TO [user_low_priv]

This doesn't:

-- auth2/view3

CREATE FUNCTION [dbo].[cc2_Helper2]( @SecretData VARBINARY(256), @vBin VARBINARY )

RETURNS NVARCHAR(50)

WITH EXECUTE AS 'DBO'

AS

BEGIN

RETURN convert( NVARCHAR(50), decryptbykeyautocert( cert_id( 'cert_SecretTable_SecretData_Key' ), null, @SecretData, 1, @vBin ) )

END

go

CREATE VIEW [dbo].[cc2View3]

AS

SELECT CardID as CardID, [dbo].[cc2_Helper2](CardNumber, convert(varbinary, CardID)) as CardNumber FROM [dbo].[cc2]

go

GRANT SELECT ON [dbo].[cc2View3] TO [user_low_priv]

WHY? Note that the conversion to VARBINARY was moved from the call to DecryptByKeyAutoCert to the call to cc2_Helper2. That is the only change...

But if I declare @vBin as VARBINARY(256) then it does work! Guess I'm a little confused on declaring vars...anyone can elucidate? Thanks.

View Replies !

Drill Through Doesn't Work.
I have a drill through that passes four parameters. Three are passed from the current selections in that reports parameters and the fourth needs to be the customer name they click on in the body of the report so it's passed as Fields!fieldname.Value. When I click on the customer name, the drillthough fires but the report simply doesn't load. If I remove the parameter from the field clicked on and just pass the three parameters, it goes to the drill through correctly and that fourth parameter just sets to the default for that parameter in that report.

I can then simply check that parameter and select the value from the list that is exactly the same as the value I was attempting to pass it in the drill through and report refreshes correctly.

Whatever it is, is something in the manner that the value is passed in the drill through specifically.

Any ideas?

View Replies !

Sp_send_dbmail Doesn't Work
hi all,
i made a stored procedure that uses the sp_send_dbmail to send mails. SQL server dislays the message "mail queued" but nothing is recieved

here is the code of the stored procedure i made
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Exams',
@recipients = 'me@domain.com',
@Body_format = 'HTML' ,
@subject = 'Room Preparation' ,
@body='hi there';
so can anyone help with this issue
thanks in advance

View Replies !

Using SP_ATTACH_SINGLE_FILE_DB Doesn't Work.
Hello,
I've rescued a MDF and LDF files off a client's old server, and I wanted to attach it to our own, but I can't seem to get the command to work, basically I have these two files, which I've dropped on our server:

C:Program FilesMicrosoft SQL ServerMSSQLDataMYCLIENTNAME_Data.MDF
C:Program FilesMicrosoft SQL ServerMSSQLDataMYCLIENTNAME_Data.LDF

So when I do a
SP_ATTACH_SINGLE_FILE_DB 'somedb','C:Program FilesMicrosoft SQL ServerMSSQLDataMYCLIENTNAME_Data.MDF'

It says the LDF path my be incorrect, and that there's two other files that are missing:
MYCLIENTNAME_LOG (no extension)
extra_log (no extension)

I thought the whole point of the command is that you only need a single file?
Its very hard to go back to the client's old server and try to find these two files, and it doesn't really matter if we loose a bit of data, so long as the bulk of it is available.

Update: I think I've found the answer...its not possible to do this, it really needs all the log files.
Any workarounds?

View Replies !

Linkserver Doesn't Work.
I have a query that doesn't work when i use 4 name convention instead of a openquery. The msg is below. Anyone know what is going on?
Both queries are the same but one doesn't work.

-- works
SELECT TOP 1 * FROM OPENQUERY(AS400_PROD, 'SELECT * FROM PPTREASUSA.ORDDET')

-- doesnt work
SELECT TOP 1 * FROM AS400_PROD.S1030Y3M.PPTREASUSA.ORDDET

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: Unspecified error]
[OLE/DB provider returned message: [IBM][iSeries Access ODBC Driver][DB2 UDB]CPF5715 - File ORDDET01 in library QTEMP not found.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBSchemaRowset::GetRowset returned 0x80004005: ].

http://www.sqlserverstudy.com

View Replies !

Where Clause ISNULL Problem, Doesn't Show Nulls
Hi All,
Can somebosy help with this dilema in my where clause I've got the followingWHERE (Customers.Owner = ISNULL(@Contract,Customers.Owner)) AND (Workorders.DateReceived = ISNULL(@DateReceived,Workorders.DateReceived)) AND (Workorders.DateRequired = ISNULL(@DateRequired,Workorders.DateRequired)) AND
(Workorders.EngineerID = ISNULL(@EngineerID,Workorders.EngineerID)) AND (Workorders.DateFinished = ISNULL(@DateFinished,Workorders.DateFinished)) AND (Workorders.JobTypeID = ISNULL(@JobTypeID,Workorders.JobTypeID)) AND
(Workorders.JobStatus = ISNULL(@JobStatus,Workorders.JobStatus)) AND (Workorders.PriorityID = ISNULL(@PriorityID,Workorders.PriorityID))
This is so if one of the parameters passed is null you still get a result is trouble is if the substitute value of the ISNULL statement is null no result is shown how can I get round this.
Any help much appreciated

View Replies !

Order By Clause
If I use the order by clause to sort on a date, where the date andtime stamp are the exact same for multiple records, how does SQLoutput the data?At random... or does it look at the primary key?

View Replies !

Order By Clause
Hello,
can any one tell me about the difference between the following queries.

1. SELECT * FROM Symp_User ORDER BY
2. SELECT * FROM Symp_User ORDER BY ASC

I don't think there is any difference in the above queries. kinldy make me clear on this.

thnkx,
rahul jha

View Replies !

Order By Clause
Hi there,
i'm trying to order the follow query:

SELECT count(nome) as CNT FROM utenti WHERE nome like '%" & rs("nome") & "%' ORDER BY CNT ASC

why i've this error:

Microsoft OLE DB Provider for ODBC Drivers errore "80040e10"
[Microsoft][Driver ODBC Microsoft Access] Parametri insufficienti. Previsto 1.

Thank you in advance...

View Replies !

Order By Clause
I am looking to get a cyclic order in the order by clause. how do I do this?

for example I have 5 customers with ids like
xyz 1
xyz 2
xyz 3
xyz 4
xyz 5

when I am selecting xyz 3 I want the list to show
xyz 4
xyz 5
xyz 1
xyz 2
xyz 3

How do I do this by using the order by clause?

View Replies !

Order By In() Clause ??
Hi!

My problem is this one:

I'm doing a simple query like this (simplified):

SELECT Me_id, Det_id, Det_val
FROM oFormsMsgDet
WHERE (Det_id IN (2411, 2409, 2410, 2408))

And I want the recordset order by my IN list. My In list is given dynamicly so I can't play with it and my query is returned in Det_id order. How can I do it?

Thanks a lot

Genviou

View Replies !

Reg. Order By Clause
Can you explain the below scenario

The ORDER BY clause can include items that do not appear in the select list. However, if SELECT DISTINCT is specified, or if the statement contains a GROUP BY clause, or if the SELECT statement contains a UNION operator, the sort columns must appear in the select list.

what is the reason behind this.

View Replies !