MS SQL Server :: Function Returing As Double But When Called Giving An Error

Retrieving Result Set From Dynamically Called Stored Procedure Or Function In A Function
Is there any way I can retrieve the result set of a Stored Procedurein a function.ALTER FUNCTION dbo.fn_GroupDeviceLink(@groupID numeric)RETURNS @groupDeviceLink TABLE (GroupID numeric, DeviceID numeric)ASBEGINDeclare @command nvarchar(255)SELECT @command = Condition// @command is an SQL string or stored procedue nameFROM DeviceGroupWHERE GroupID = @groupIDINSERT @groupDeviceLinkEXEC @commandRETURNENDIs there any way i can do anything like this. @command is a variableholding the name of a stored produre. I need to run that storedprocure and return the values in such a way that they can be used in aSELECT StatementMy goal is SELECT * FROM Device INNER JOINdbo.fn_GroupDeviceLink(@groupID) ON ....this fn_GroupDeviceLink should run the proper stored procedure andreturn the values. What i also want to do is play with that result setof the specific stored procedure before i return it. Is this possible?If not, what is the work arround?ThanksMark

View Replies !

Grant All Function Giving Errors
I'm migrating from 2000 to 2005, what is the best way to handle the following error:

The ALL permission is deprecated and maintained only for compatibility. It DOES NOT imply ALL permissions defined on the entity

The code is below:

DECLARE @sp_name AS sysname;
DECLARE syscursor CURSOR FOR
SELECT name FROM sysobjects
WHERE (xtype = 'P' or xtype='V') AND ((status & 0x80000000) = 0);

OPEN syscursor;
FETCH NEXT FROM syscursor INTO @sp_name;

WHILE (@@FETCH_STATUS = 0)
BEGIN
EXECUTE ('GRANT all ON ' + @sp_name + ' TO Public');
FETCH NEXT FROM syscursor INTO @sp_name;
END

CLOSE syscursor;
DEALLOCATE syscursor;

View Replies !

Is It Possible To Set The Query Parameter By Giving A Function Name
I am trying to do a query similar to this:

SELECT * FROM TRANSACCTION_HISTORY WHERE TXN_DATE=@RepDate

For the query parameter @RepDate, I would like to pass a function to it. The function is a calculation of date based on today's date (e.g. Today() function to be simple). So that users don't have to type in the date every time.

Now the question is: is it really possible to do that in RS? Because I am getting a type conversion error, when I put Today() in the Define Query Parameters disalog box.

Thanks for helping!

View Replies !

DeriveParameters Throws When Called Against A C# Function?
When I call DeriveParameters against a function written in SQLCLR function it throws an exception. I've been working on this a little while and haven't found a fix. I understand that I could write additional code to do the same work DeriveParameters does, but it seems like this should work.

This is the exception thrown:

[InvalidOperationException: The stored procedure 'GSI.Utils.IsMatch' doesn't exist.]

The function is defined as

CREATE FUNCTION [Utils].[IsMatch](@Value [nvarchar](4000), @RegularExpression [nvarchar](4000))
RETURNS [bit] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [RegularExpressionsHelper].[UserDefinedFunctions].[IsMatch]

The C# function is defined as:

[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic=true)]
public static SqlBoolean IsMatch(SqlString Value, SqlString RegularExpression)
{
Regex rx = new Regex( RegularExpression.ToString() );
string s = Value.ToString();

return new SqlBoolean(rx.IsMatch(s));
}

This is the code I'm using to call DeriveParameters:

static void Main(string[] args)
{
    using (SqlConnection conn = new SqlConnection(m_GetConnectionString()))
    {
        conn.Open();

        SqlCommand myCommand = new SqlCommand("GSI.Utils.IsMatch", conn);
        myCommand.CommandType = System.Data.CommandType.StoredProcedure;
        SqlCommandBuilder.DeriveParameters(myCommand);
    }
}

I found that DeriveParameters seems to call sp_procedure_params_managed and when I call it myself it returns the parameters correctly for T-SQL functions, but returns no records when I specify a SQLCLR function.

DECLARE    @procedure_name     sysname;
DECLARE    @group_number       int;
DECLARE    @procedure_schema   sysname;
DECLARE    @parameter_name     sysname;

SET    @procedure_name     = 'IsMatch';
SET    @group_number       = 1;
SET    @procedure_schema   = 'Utils';
SET    @parameter_name     = null;

DECLARE @RC int

EXECUTE @RC = [GSI].[Sys].[sp_procedure_params_managed]
   @procedure_name
,@group_number
,@procedure_schema
,@parameter_name

I'm able to execute the function without issue and I'm able to use DeriveParameters against everything in the database except C# based functions (and I've tried others besides IsMatch). I'm attempting to run DeriveParameters from a console application and from ASP.NET, both running .NET 2.0.

I've experienced the same behavior in these versions:

SQL Server 2005 Enterprise Edition RTM (MSDN Image on Virtual Server)
SQL Server 2005 Enterprise Edition SP1 (MSDN Image on Virtual Server)
SQL Server 2005 Enterprise Edition SP1 + Hotfix kb918222 (MSDN Image on Virtual Server)
SQL Server 2005 Developer Edition SP1

Has anyone else seen similar behavior?

Any advice would be greatly appreciated -- Thanks

Steve

View Replies !

Converting Result Of Aggregate Function Calculation To A Double (C#)
I've put a SelectCommand with an aggregate function calculation and AS into a SqlDataSource and was able to display the result of the calculation in an asp:BoundField in a GridView; there was an expression after the AS (not sure what to call it) and that expression apparently took the calculation to the GridView (so far so good).
If I write the same SELECT statement in a C# code behind file, is there a way to take the aggregate function calculation and put it into a double variable? Possibly, is the expression after an AS something
that I can manipulate into a double variable? My end goal is to insert the result of the calculation into a database.
What I have so far with the SelectCommand, the SqlDataSource and the GridView is shown below in case this helps:
<asp:GridView class="gridview" ID="GridView2" runat="server" AutoGenerateColumns="False" DataSourceID="lbsgalDataSource">
<Columns>
<asp:BoundField DataField="Formulation" HeaderText="Formulation" SortExpression="Formulation" />
<asp:BoundField DataField="lbs" HeaderText="lbs" SortExpression="lbs" />
<asp:BoundField DataField="gal" HeaderText="gallons" SortExpression="gal" />
<asp:BoundField DataField="density" HeaderText="density" SortExpression="density" />

</Columns>
</asp:GridView>

<asp:SqlDataSource ID="lbsgalDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT
a.Formulation,
SUM (a.lbs) AS lbs,
SUM ((a.lbs)/(b.density)) AS gal,
( ( SUM (a.lbs) ) / ( SUM ((a.lbs)/(b.density)) ) ) AS density
FROM Formulations a INNER JOIN Materials b
ON a.Material=b.Material WHERE Formulation=@Formulation GROUP BY Formulation">

<selectparameters>
<asp:controlparameter controlid="DropDownList1" name="Formulation" propertyname="SelectedValue" type="String" />
</selectparameters>

</asp:SqlDataSource>

View Replies !

Can A Stored Procedure Called From An Inline Table-Valued Function
Hi,

I'm trying to call a Stored Procedure from a Inline Table-Valued Function. Is it possible? If so can someone please tell me how? And also I would like to call this function from a view. Can it be possible? Any help is highly appreciated. Thanks

View Replies !

Calling CLR Stored Procedure From Within A CLR Table-valued Function Giving Errors
We are trying to create a TVF that executes a CLR Stored Procedure we wrote to use the results from the SP and transform them for the purposes of returning to the user as a table.

Code Snippet

[SqlFunction ( FillRowMethodName = "FillRow",

TableDefinition = "CustomerID nvarchar(MAX)",

SystemDataAccess = SystemDataAccessKind.Read,

DataAccess = DataAccessKind.Read,

IsDeterministic=false)]

public static IEnumerable GetWishlist () {

using (SqlConnection conn = new SqlConnection ( "Context Connection=true" )) {

List<string> myList = new List<string> ();

conn.Open ();

SqlCommand command = conn.CreateCommand ();

command.CommandText = "GetObject";

command.Parameters.AddWithValue ( "@map", "Item" );

command.CommandType = System.Data.CommandType.StoredProcedure;

using ( SqlDataReader reader = command.ExecuteReader ( System.Data.CommandBehavior.SingleRow )) {

if (reader.Read ()) {

myList.Add ( reader[0] as string );

}

}

return (IEnumerable)myList;

}

}

When command.ExecuteReader is called, I am getting an "Object not defined" error. However, the stored procedure can be used in SQL Management Studio just fine.

Code SnippetEXEC GetObject 'Item'

Is there some sorf of trick I am missing?

Thank you!

View Replies !

Sp Giving Error
i have to get the maximum into a output parameter. its giving error. whats the problem with this code

SET @supplier_code as EXECUTE (SELECT MAX(supplier_code)+1 AS Supp_Id FROM supplier)

suji

View Replies !

BCP Giving Error "unexpected Eof"
Hi ,

I am trying to import a .csv file to SQL server 6.5, I get the db library error "Unexpected EOF encountered in BCP data-file"

What can be the problem with the file ?
Its very important to load this data.

Any help is appreciated.

Thanks
Ajay

View Replies !

My DTS Package Is Giving An Error When Trying Run From The JOB
Hi,

I have imported a DTS package to sql 2005. I am trying to run this DTS package from the JOB using DTSRun. and i am getting the following error. I thought it was some issue with the SQL Agent serivice account which this is running, i have created a proxy which has super privilages.. but still the job is failing. When i run the DTS package directly it's running fine.

I am confused !!!!!

Message
Executed as user: AMRsql_seasdv. DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147467259 (80004005) Error string: Login failed for user ''. The user is not associated with a trusted SQL Server connection. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 18452 (4814) Error string: Login failed for user ''. The user is not associated with a trusted SQL Server connection. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
Dev

View Replies !

Check This With It Is Giving An Error.

WITH ProccessedYesNO AS

(

select ClaimNumber, Surname, FirstName, CASE WHEN New= 0 THEN 'Yes' ELSE 'No' END AS Processed

from EntryTable)
Select * from ProcessedYesNo Where Ignore_dupe='Yes';

why I am getting an syntex error I check the query it is ok atleast the inner query is running but when I apply the with
than I am getting Syntax error near 'WITH'

View Replies !

Returing The NT User Name
If a user connects to SQL Server through say QA using a trusted connection.
What would the function be to return the NT username? I tried this (I am a
system admin) and it returned dbo. I was looking for my NTusername which I can see when I query processes

View Replies !

CoInitialize Has Been Called Error
I sometimes get the subject error when trying to add a table to an existing view in SS7. Anyone have a clue as to what it means?

Thanks -- g.

View Replies !

Union Giving Me Error Msg About Distinct Use
when i try to run this following query, i get an error message:

Microsoft OLE DB Provider for SQL Server error '80040e14'

The ntext data type cannot be selected as DISTINCT because it is not comparable.

/resultados_termo.asp, line 176

the query:

select * from view_veiculos where ativo='1' and ( nome_marc like
'%fiat%' or nome_mod like '%fiat%' or estado like '%fiat%' or cidade
like '%fiat%' or ano like '%fiat%' ) and ( nome_marc like '%brava%' or
nome_mod like '%brava%' or estado like '%brava%' or cidade like
'%brava%' or ano like '%brava%' ) and ( nome_marc like '%2004%' or
nome_mod like '%2004%' or estado like '%2004%' or cidade like '%2004%'
or ano like '%2004%' ) union
select * from view_veiculos where ativo='1' and ( nome_marc like
'%fiat%' or nome_mod like '%fiat%' or estado like '%fiat%' or cidade
like '%fiat%' or ano like '%fiat%' ) and ( nome_marc like '%brava%' or
nome_mod like '%brava%' or estado like '%brava%' or cidade like
'%brava%' or ano like '%brava%' ) union
select * from view_veiculos where ativo='1' and ( nome_marc like
'%fiat%' or nome_mod like '%fiat%' or estado like '%fiat%' or cidade
like '%fiat%' or ano like '%fiat%' ) union
select * from view_veiculos where ativo='1' and ( nome_marc like
'%brava%' or nome_mod like '%brava%' or estado like '%brava%' or cidade
like '%brava%' or ano like '%brava%' ) and ( nome_marc like '%2004%' or
nome_mod like '%2004%' or estado like '%2004%' or cidade like '%2004%'
or ano like '%2004%' ) union
select * from view_veiculos where ativo='1' and ( nome_marc like
'%brava%' or nome_mod like '%brava%' or estado like '%brava%' or cidade
like '%brava%' or ano like '%brava%' ) union
select * from view_veiculos where ativo='1' and ( nome_marc like
'%2004%' or nome_mod like '%2004%' or estado like '%2004%' or cidade
like '%2004%' or ano like '%2004%' )

when i use UNION ALL, i get repeated rows. i need the select distinct on it.

please, help.

thanks in advance.

View Replies !

Select Row_Number() Giving Me An Error
here it is:

SELECT * FROM ( SELECT ROW_NUMBER() Over (Order By LastActivity ASC) As rn UserName) FROM aspnet_usersWhere rn = 1

it's saying: "Incorrect syntax near UserName"
all column/table names are correct

View Replies !

Distinct Count With Over Giving An Error
We have a table of students with fields for the ID of the school district and their school name. For example:

DistrictID
StudentName
SchoolName

10001
John Smith
Washington Elementary

10001
Jane Smith
Lincoln Middle

10002
David White
Hill High

...
...
...

I want is a listing with the school district ID, the student name, and the number of schools in the district.

I believe the following statement should give me what I want:

SELECT DistrictID, StudentName, COUNT(DISTINCT SchoolName) OVER (PARTITION BY DistrictID)

FROM StudentData

However, I get the error:

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'distinct'.

If I take out the DISTINCT it runs fine, however it counts all the rows as expected, which is not what I want.
If I remove the OVER clause it runs fine, however, it counds the schools across all the districts, which is not what I want.
I can remove the OVER clause and use GROUP BY, but not while also retrieving StudentName.

The SQL 2005 Online Books don't seem to indicate that my statement is invalid. Here are the links just for reference:
Count: http://msdn2.microsoft.com/en-us/library/ms175997.aspx
Over: http://msdn2.microsoft.com/en-us/library/ms189461.aspx

Anyone have an idea one what I'm doing wrong or how to go about getting this information?
Thanks!
Matt Penner

View Replies !

SQL Sever Not Installing - Giving Error - Plz Help
Hi,

I'm trying to install Sql Server 2005 Express Edition making it as one of the pre-requisities of an windows app using click once deployment tech and in one system every time i'm getting an error that "An error occured attempting to install myappname". Here its pointing to an log file called install.log which is as follows-

The following properties have been set:
Property: [AdminUser] = true {boolean}
Property: [ProcessorArchitecture] = Intel {string}
Property: [VersionNT] = 5.1.2 {version}
Running checks for package 'Microsoft Data Access Components 2.8', phase BuildList
Reading value 'FullInstallVer' of registry key 'HKLMSoftwareMicrosoftDataAccess'
Read string value '2.81.1117.0'
Setting value '2.81.1117.0 {string}' for property 'MDACVersion'
The following properties have been set for package 'Microsoft Data Access Components 2.8':
Property: [MDACVersion] = 2.81.1117.0 {string}
Running checks for command 'MDAC28mdac_typ.exe'
Result of running operator 'VersionGreaterThanOrEqualTo' on property 'MDACVersion' and value '2.80': true
Result of checks for command 'MDAC28mdac_typ.exe' is 'Bypass'
'Microsoft Data Access Components 2.8' RunCheck result: No Install Needed
Running checks for package '.NET Framework 2.0', phase BuildList
Running external check with command line "C:DOCUME~1JOEMER~1LOCALS~1TempVSD567.tmpdotnetfxdotnetchk.exe"
Process exited with code 1
Setting value '1 {int}' for property 'DotNetInstalled'
Reading value 'Version' of registry key 'HKLMSoftwareMicrosoftInternet Explorer'
Read string value '7.0.5730.11'
Setting value '7.0.5730.11 {string}' for property 'IEVersion'
The following properties have been set for package '.NET Framework 2.0':
Property: [DotNetInstalled] = 1 {int}
Property: [IEVersion] = 7.0.5730.11 {string}
Running checks for command 'dotnetfxinstmsia.exe'
Result of running operator 'ValueExists' on property 'VersionNT': true
Result of checks for command 'dotnetfxinstmsia.exe' is 'Bypass'
Running checks for command 'dotnetfxWindowsInstaller-KB893803-v2-x86.exe'
Result of running operator 'ValueExists' on property 'Version9x': false
Result of running operator 'VersionLessThan' on property 'VersionNT' and value '5.0.3': false
Result of running operator 'VersionGreaterThanOrEqualTo' on property 'VersionMsi' and value '3.0': true
Result of checks for command 'dotnetfxWindowsInstaller-KB893803-v2-x86.exe' is 'Bypass'
Running checks for command 'dotnetfxdotnetfx.exe'
Result of running operator 'ValueNotEqualTo' on property 'DotNetInstalled' and value '0': true
Result of checks for command 'dotnetfxdotnetfx.exe' is 'Bypass'
'.NET Framework 2.0' RunCheck result: No Install Needed
Running checks for package 'Windows Installer 3.1', phase BuildList
The following properties have been set for package 'Windows Installer 3.1':
Running checks for command 'WindowsInstaller3_1WindowsInstaller-KB893803-v2-x86.exe'
Result of running operator 'VersionGreaterThanOrEqualTo' on property 'VersionMsi' and value '3.1': true
Result of checks for command 'WindowsInstaller3_1WindowsInstaller-KB893803-v2-x86.exe' is 'Bypass'
'Windows Installer 3.1' RunCheck result: No Install Needed
Running checks for package 'SQL Server 2005 Express Edition', phase BuildList
Running external check with command line "C:DOCUME~1JOEMER~1LOCALS~1TempVSD567.tmpSqlExpressSqlExpressChk.exe"
Process exited with code 0
Setting value '0 {int}' for property 'SQLExpressInstalled'
The following properties have been set for package 'SQL Server 2005 Express Edition':
Property: [SQLExpressInstalled] = 0 {int}
Running checks for command 'SqlExpresssqlexpr32.exe'
Result of running operator 'ValueEqualTo' on property 'SQLExpressInstalled' and value '0': true
Result of checks for command 'SqlExpresssqlexpr32.exe' is 'Bypass'
Running checks for command 'SqlExpresssqlexpr32.exe'
Result of running operator 'ValueEqualTo' on property 'SQLExpressInstalled' and value '0': true
Result of checks for command 'SqlExpresssqlexpr32.exe' is 'Bypass'
'SQL Server 2005 Express Edition' RunCheck result: No Install Needed
Launching Application.
Running command 'http://reporterapp.stats.com/reporter/BasketballDataCollection.application' with arguments ''
ShellExecuteEx failed with error code 1155
Error: The following error occurred attempting to install 'http://reporterapp.stats.com/reporter/BasketballDataCollection.application':
"No application is associated with the specified file for this operation. "

View Replies !

Database Keeps Giving Timeout Error
Hi All,

since the last upgrade SQL 2000 --> 2005, the biggest database I am working on (1.4 GB, with loads of binary data) keeps freezin at least one time per week. When I say 'freezin' I mean that you cannot query some of the tables as it gives a timeout error when trying.

I am not a DBA and I don't have any idea of what to do, the only thing which comes to my mind is that this database is too big, as if I go to the preperties window it says Space Available: 38 MB (what's that? there's really only 38 MB on this DB?).
I tried re-writing all mainteinment plans (full backup every night, log backup every hour), but I realized that the database keeps freezing (and the backup jobs keep running untill you stop them).

anyone could give any hint?

Thanks in advance,

Giovanni Idili

View Replies !

RS Dataset Returing A Single Row
I created a report using RS with a SQL stored procedure (with date range parameters and a cursor to build the data) as the data source. When I execute the proc in SQL it returns over 300 records. When i go the the data tab on my report view the data, the proc and the preview only returns the first row. What am i doing wrong? I am new to RS.

View Replies !

Exit Activex Script Without Giving An Error?
I am using ActiveX Script tasks to branch out on the different tasks that I have within the DTS. When I want a particular branch of tasks not to be executed, I give a "Main = DTSStepScriptResult_DontExecuteTask"

This seems to be working fine in terms of functionality, but there is an error that is displayed saying "Task reported failure on execution".

How do I get rid of this? I need to exit the task, and still just get one message box at the end of it all that happily reads "Succesfully completed tasks"

View Replies !

Please Help! Sql Server 7.0 Crashes Giving A NT Event Log Error.
When I try to manually run one of my scheduled job, DR. Watson comes up and SQL Server crashes. the following error message is received in the NT event log. "Unable to read local Event log, reason the data area passed to a system call is too small". I am new to SQL and this is causing me some degree of stress!!!! Also please recommend a good book for a newbie like me.

Thanks in advance.

View Replies !

Running ActiveX Script Giving Error
Hi,

We are moving our maintenance jobs from SQL Server 2000 to SQL Server 2005 and getting following error when it tried to run ActiveX script at 1 of the steps:
"error creating security descriptor for shared objects(reason: No mapping between account names and security IDs was done)
I tried running the job as "SA" user and "Windows Admin" user but still the same issue, What need to be done to fix this issue?

Thanks
--rubs

View Replies !

Returing ADO Recordset From Stores Procedure
I have a Stored Procedure that I am calling from ADO 2.7 in Visual Basic. The process works fine when the stored procedure has records to return. However , when the recordset is return emtpy ADO does not recognize the recordset as being open and empty. For example:
VB Code to open recordset:
Set rstClass1 = New ADODB.Recordset
With rstClass1
.CursorLocation = adUseServer
Set .ActiveConnection = DBConn
.Source = strRecordSource
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
If Not .EOF Or Not .BOF Then

The program bombs on the If Not .EOF statement with Error 3704 -
"Operation is not allowed when the object is closed.

When I run the Stored Procedure In QA using an ID that I know will NOT return any records the result window returns nothing. I noticed that on some of my other SP's the result window will at least return the column titles. These SP's also do not produce the 3704 error in ADO.

Is there a command or a certain method to construct my SP so that is will return enough for ADO to know that the recordset did open its just empty?

The SP is ugly looking but I will post if need be.

View Replies !

MS SQL Query : Group By Returing Too Many Rows.
Hi,

I have posted this in the Experts Forum so I have put replies to questions in here as well (Hence the long post!!). Hope someone here can help!!

I am having trouble with a T-SQL query. I have three tables in a join and I need to limit the results returned by query using a group by.

All of the fields in the result group are the same except for two fields, a date field and a varchar field. What I want to achieve is to return the row that has the latest date, and I need the varchar field as that is the information I am after. The problem is if I include the varchar field in the group by, it is returned as a separate group, if I do not included it in the group by, Server: Msg 8120 (aggregate fn / group by) error occurs!

Any ideas how to get around this?

Sample code and results below:

Query:

select distinct s.id, [active-from], code, s.desc, [scheme], [market-id], [market-id]+'CODE'
from [coded] c join sec s on s.id = c.id
join [mkt-security] m on m.id = s.id
where ([market-id] = [scheme] or [market-id]+'CODE' = [scheme])
Group by s.id, [active-from], code, s.desc, [scheme], [market-id], [market-id]+'CODE'

Sample results:

id : [active-from] : code : desc : [scheme] : [market-id]
--------------------------------------------------------------------------------------------------------------------------
10449 :1993-07-21 : ADV : PXX Group Limited : ABCD : ABCD : ABCDCODE
10449 :2003-03-27 : PVO : PXX Group Limited : ABCD : ABCD : ABCDCODE
10469 :1986-10-24 : AQL : CO Ordinary Shares : DEFG CODE : DEFG : DEFG CODE
10469 :2000-10-02 : AQL : CO Ordinary Shares : DEFG CODE : DEFG : DEFG CODE
10469 :2001-09-21 : CER : CO Ordinary Shares : DEFG CODE : DEFG : DEFG CODE

The result I want to achieve is to only return these two rows:

10449 :2003-03-27 : PVO : PXX Group Limited : ABCD : ABCD : ABCDCODE
10469 :2001-09-21 : CER : CO Ordinary Shares : DEFG CODE: DEFG : DEFG CODE

i.e. those with the latest [active-from] date, but I must have the corresponding code value.

I have tried a correlated sub query on the [active-from] field, but it is possible to have different id’s with the same [active-from] date so it did not work.

The primary key for the [coded] table is the combination of [active-from], code and [scheme]. The tables are truncated and then imported back into the SQL database from a Progress database daily, so restructuring the table(s) is not a possibility.

I have rewritten the query to "AND [ACTIVE_FROM] in (SELECT MAX([ACTIVE_FROM]) FROM CODED AND MAX([CODE]) = S.[CODE] AND MAX([SCHEME]) = S.[SCHEME]", (Which I think is the T-SQL for you query), but it returned the error "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference."

Also I think the query need to use a Group by as the results I need returned are those with the latest [ACTIVE_FROM] date WITHIN the group of [ID]and [MARKET-ID]. I can't think of any conditions where I can filter the results in the where clause.

The [ ] in the T-SQL is to identify table names as some of the table names (from the Progress db) use characters (the "-") that are unsupported for table names in T-SQL.

I am using the group by as I want to return specific groups of results ( [ID] and [MARKET-ID] ), that have the latest active-from date. I have made a change to that posted before to illustrate:

All results:

id : [active-from] : code : desc : [scheme] : [market-id] : [market-id]+’CODE’

10449 :1993-07-21 : ADV : PXX Group Limited : ABCD : ABCD : ABCDCODE
10449 :2003-03-27 : PVO : PXX Group Limited : ABCD : ABCD : ABCDCODE
10769 :1986-10-24 : AQL : CO Ordinary Shares : WXYZ CODE : WXYZ : WXYZ CODE
10769 :2000-10-02 : AQL : CO Ordinary Shares : DEFG CODE : DEFG : DEFG CODE
10769 :2001-09-21 : CER : CO Ordinary Shares : DEFG CODE : DEFG : DEFG CODE

The result I want to achieve is to only return these three rows:

10449 :2003-03-27 : PVO : PXX Group Limited : ABCD : ABCD : ABCDCODE
10769 :2001-09-21 : CER : CO Ordinary Shares : DEFG CODE: DEFG : DEFG CODE
10769 :1986-10-24 : AQL : CO Ordinary Shares : WXYZ CODE : WXYZ : WXYZ CODE

The reason the last row needs to be returned is that it has a different market id to the other row, despite its active-from date being earlier.

I have tried the self join earlier, but it would then only return 1 of the rows that have an id of 10769!!

Hope this makes sense!!

Cheers,
Guytz

View Replies !

Double Quotes In Error Messages!
I have a problem when trying to display an error message from sql server 2005 on a web page with an alert (javascript command).

The Sql server 2005 returns a message like:

Insert statement conflicted with foreign key constraint "bla bla". The conflict occured indatabase "databasename", table "tablename", column 'columnname'.

In sql server 2000 the error message is the same except all names (constraint, database, table) is in single quotation marks; just like the columnname in the above example.

Is it a configurable issue on the sql server. I would prefer not to solve this issue on a number of different web pages!

Thanks in advance.

Futte

View Replies !

Error 834 Sev.21 Bufclean Called On Dirty Buffer
Hello group,

I am running a restore on a 32 gb database, SQL 6.5 sp4 , Compaq quad , 1 gb ram - SQL has 850 mb. This retore usually takes 1 hr, 39 minutes. The following message appeared in the errorlog after 1 hour, and the process has been running more than 2 hours now:

error 834 sev.21 bufclean called on dirty buffer (page 0x5cbd00, stat 0x81024/0x1, objid 0x63 , sstat 0)

Any ideas? Should I reboot and start over?

Thank you.

View Replies !

Error Opening Pdf Report Called Via SOAP
We've been opening pdf reports using SOAP for some time now and all of the sudden we're getting errors when the user is on IE6. IE7 is fine.

The error we're getting is from the Acrobat reader is:

"There was an error opening the document. The file cannot be found"

Here's the code that opens the report:

Dim report As Byte() = Nothing

Dim rs As report_engine2.ReportExecutionService = New report_engine2.ReportExecutionService

'credentials

rs.Credentials = New System.Net.NetworkCredential(rs_login, rs_password)

rs.PreAuthenticate = True

Dim reportpath As String = rs_folder & report_name

Dim zoom As String = "False"

Dim deviceInfo As String = Nothing

Select Case format

Case "HTML4.0", "HTML3.2"

deviceInfo = "<DeviceInfo>"

deviceInfo &= "<Toolbar>False</Toolbar>"

deviceInfo &= "<Parameters>False</Parameters>"

deviceInfo &= "<HTMLFragment>True</HTMLFragment>"

deviceInfo &= "<StyleStream>False</StyleStream>"

deviceInfo &= "<Section>0</Section>"

deviceInfo &= "<Zoom>" & zoom & "</Zoom>"

deviceInfo &= "</DeviceInfo>"

Case Else

deviceInfo = "<DeviceInfo></DeviceInfo>"

End Select

'array of parameters

Dim parameters(0) As report_engine2.ParameterValue '0 means one parameter in the report

Dim paramValue As report_engine2.ParameterValue = New report_engine2.ParameterValue

paramValue.Name = param1_name

paramValue.Value = param1_value

parameters(0) = paramValue

'variables for the remaining paramters

Dim historyID As String = Nothing

Dim credentials As report_engine2.DataSourceCredentials = Nothing

Dim showHideToggle As String = Nothing

Dim extension As String = GetExtension(mimeType)

Dim warnings() As report_engine2.Warning = Nothing

Dim reportHistoryParameters() As report_engine2.ParameterValue = Nothing

Dim streamIDS() As String = Nothing

Dim execInfo As New report_engine2.ExecutionInfo

Dim execHeader As New report_engine2.ExecutionHeader

rs.ExecutionHeaderValue = execHeader

execInfo = rs.LoadReport(reportpath, historyID)

rs.SetExecutionParameters(parameters, "en-us")

Try

'execute the report

report = rs.Render(format, deviceInfo, "", mimeType, "", warnings, streamIDS)

'set the filename

Dim fileName As String = savefilename & "." & extension

'write the report back to the response object

response.Clear()

response.ContentType = mimeType

'add the file name to the response if it is not a web browser format.

If mimeType <> "text/html" Then

response.AddHeader("Content-Disposition", "attachment; filename=" & fileName)

End If

'send the byte array containing the report

HttpContext.Current.Response.BinaryWrite(report)

HttpContext.Current.Response.End()

Catch ex As Exception

End Try

This used to work just fine until about a week ago. We're not sure if there was a MS update that caused this behavior. If the user saves the report to a local disk instead of opening it, they can save it successfully and open it later.

Any thoughts on where to go with this one?

Regards

View Replies !

SQL Agent Jobs Giving General Network Error
transferred datbases and SQLagentjobs to a new server. Trying to execute sqlagent jobs
gives

ConnectionRead (WrapperRead()). [SQLSTATE 01000] (Message 258) General network error. Check your network documentation. [SQLSTATE 08S01] (Error 11). The step failed.

External Comminications seem OK

Any ideas what to do about this

Thanks

View Replies !

Calling A .Net Assembly From Script Component Giving Error
Hi,

I am trying to access a .Net assembly in script component, which internally uses Microsoft Enterpise library dll's.

The problem I am facing is when I copy the config sections needed for the Enterprise library from web.config to dtsdebughost.exe.config file and run the package, It ends in failure with below message
"Error: The script files failed to load."

My dtsdebughost.exe.config looks like below:

Code Snippet
<configuration>
    <startup>
        <requiredRuntime version="v2.0.50727"/>
    </startup>
<configSections>
    <section name="loggingConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Logging.Configuration.LoggingSettings, Microsoft.Practices.EnterpriseLibrary.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
    <section name="exceptionHandling" type="Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.Configuration.ExceptionHandlingSettings, Microsoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</configSections>
<loggingConfiguration name="Logging Application Block" tracingEnabled="true"
    defaultCategory="" logWarningsWhenNoCategoriesMatch="true">
    <listeners>
      <add fileName="LogMedtrack-Error.log" rollSizeKB="5000" timeStampPattern="dd-MMM-yyyy"
        rollFileExistsBehavior="Overwrite" rollInterval="Day" formatter="Default Formatter"
        header="----------------------------------------" footer="----------------------------------------"
        listenerDataType="Microsoft.Practices.EnterpriseLibrary.Logging.Configuration.RollingFlatFileTraceListenerData, Microsoft.Practices.EnterpriseLibrary.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
        traceOutputOptions="None" type="Microsoft.Practices.EnterpriseLibrary.Logging.TraceListeners.RollingFlatFileTraceListener, Microsoft.Practices.EnterpriseLibrary.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
        name="Rolling Flat File Trace Listener" />
    </listeners>
    <formatters>
      <add template="Timestamp: {timestamp}
Message: {message}
Category: {category}
Priority: {priority}
EventId: {eventid}
Severity: {severity}
Title:{title}
Machine: {machine}
Application Domain: {appDomain}
Process Id: {processId}
Process Name: {processName}
Win32 Thread Id: {win32ThreadId}
Thread Name: {threadName}
Extended Properties: {dictionary({key} - {value}
)}"
        type="Microsoft.Practices.EnterpriseLibrary.Logging.Formatters.TextFormatter, Microsoft.Practices.EnterpriseLibrary.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
        name="Default Formatter" />
    </formatters>
    <logFilters>
      <add categoryFilterMode="AllowAllExceptDenied" type="Microsoft.Practices.EnterpriseLibrary.Logging.Filters.CategoryFilter, Microsoft.Practices.EnterpriseLibrary.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
        name="Category Filter" />
      <add minimumPriority="0" maximumPriority="2147483647" type="Microsoft.Practices.EnterpriseLibrary.Logging.Filters.PriorityFilter, Microsoft.Practices.EnterpriseLibrary.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
        name="Priority Filter" />
    </logFilters>
    <categorySources>
      <add switchValue="All" name="Tracing">
        <listeners>
          <add name="Rolling Flat File Trace Listener" />
        </listeners>
      </add>
    </categorySources>
    <specialSources>
      <allEvents switchValue="All" name="All Events">
        <listeners>
          <add name="Rolling Flat File Trace Listener" />
        </listeners>
      </allEvents>
      <notProcessed switchValue="All" name="Unprocessed Category" />
      <errors switchValue="All" name="Logging Errors & Warnings" />
    </specialSources>
</loggingConfiguration>
<exceptionHandling>
    <exceptionPolicies>
      <add name="Business Policy">
        <exceptionTypes>
          <add type="System.Exception, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089"
            postHandlingAction="NotifyRethrow" name="Exception">
            <exceptionHandlers>
              <add logCategory="Tracing" eventId="100" severity="Error" title="Agility Application Log."
                formatterType="Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.TextExceptionFormatter, Microsoft.Practices.EnterpriseLibrary.ExceptionHandling, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
                priority="0" type="Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.Logging.LoggingExceptionHandler, Microsoft.Practices.EnterpriseLibrary.ExceptionHandling.Logging, Version=3.1.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
                name="Logging Handler" />
            </exceptionHandlers>
          </add>
        </exceptionTypes>
      </add>
    </exceptionPolicies>
</exceptionHandling>
</configuration>

Please let me konw, If there is anything wrong I am doing or is there any other way to handle the situation

Regards,
Kalyan

View Replies !

NEED HELP With Windows Installer GIVING ERROR: 1067 When INSTALLING
I know this problem has been resolved, but it sure as hell hasn't for me. For whatever reason, the operation aborts every time I try to install or uninstall something. The same thing occurs whether installing software or using Add/Remove Programs. I've tried everything and read everything for the last 6 hours. It's 2:45am where I live. I'm pist. Someone, please, help me...

It gives be Error: 1067 for operation aborted. I've messed with every nook and cranny, even to teh point of almost adding a new string to the operation so as to bypass it. It didn't work by the way, because there was another file that was still there and had the intended name. I've checked teh settings as well. All is good. Tell me what I am doing wrong!!??

View Replies !

Why Is SQL 2005 Giving A Msg 511, Exceeded Max Row Size Of 8060 Error?
A simple alter statement to populate a column is giving the error below.
UPDATE AM_PROFILE_4101_0 SET _92284 = CONVERT(varchar(1000),fv.varcharValue)
FROM AM_PROFILE_4101_0 pt
INNER JOIN cyfield_value fv ON fv.fieldID = 92284 AND pt.AM_PROFILE_ID = fv.AMid

Cannot create a row of size 8125 which is greater than the allowable maximum of 8060.
The table as many varchar(max) columns such as _92284, and many are populated with around 1000 bytes of data. Everything I'm reading tells me SQL 2005 supports large row sizes. But why is this error still coming up.

Other usage info: These numbered columns (i.e. _92284) are dropped, added, and repopulated with data every night.

Any ideas would be appreciated.

Thank you!

View Replies !

Error Message In Sp To Be Called By JAVA Web Based Front End.
What should the error processing look like in a stored procedure that is written to be called from a front end written in JAVA? How can the message get back to the user's screen? Example, if an UPDATE fails, what shouold come next in the stored procedure?

UPDATE referral
set lastdate = today
where rfr_id = @jvnl_id

********IF @@ERROR <>0
NOW WHAT

View Replies !

Stored Procedure Giving Error When Searching On Date Range
I've a report whose columns are returned from a stored procedure. Now I want to display the report based on a date range. The date field is Received. It's in dbo.master. I added 2 parameters start date and end date. When I check the condition if dbo.master.Received>StartDate and dbo.master.Received < EndDate directly I'm getting error. Could someone tell me what mistake I'm doing? Thanks for your help!ALTER Procedure [dbo].[USP_Reports_NewTier1]

@ClientCode VARCHAR(7) = '',@UserID INT = 0
,@OrderID INT =0
,@StartDate datetime,@EndDate datetime

IF @ClientCode <> '' and dbo.master.Received > StartDate and dbo.master.Received<EndDateBEGIN SELECT --Root Select --ClientName @ClientName = (Select Name FROM dbo.customer c WHERE c.Customer = @ClientCode) ,@TotalDollarValue = (SELECT SUM(m.current1-m.paid1) FROM dbo.master m WHERE phase=1 AND m.Customer = @ClientCode AND M.Status <> 'PIE')
,@AverageAge = ISNULL((select avg(age) from (select datediff(day,Received,CASE WHEN clidlp>clidlc then clidlp else clidlc END)* -1 as age from dbo. master M WHERE phase=1 AND customer = @ClientCode AND M.Status <> 'PIE') x),0)END

View Replies !

OLEDB Command Giving Error For Decalre And Set Statements At The Top Of The SQL Script
Hi All,

I have an OLEDB command in my package that has to execute some SQL script.
But when I declare and set a variable at the top of all code, The OLEDB gives an error in column mappings tab.

My DQL script is as shown below
DECLARE @Cost AS money
SET @Cost=?
--Some update statements a table
OLEDB Command works if write the declare and set statements after update statements. Like below. But I don€™t need it.

--Some update statements a table
DECLARE @Cost AS money
SET @Cost=?

I also observer that,Oledb Command gives error for the code given below.
Just paste the following Script in OLEDB command, it gives error in column mapping tab
DECLARE @Cost AS money
SET @Cost=?
Any Idea on this behaviour?

Thanks in advance..

View Replies !

Group By And Count(*) A Single Column Returing Two Counted Values
I am trying to count a column field in a single table and return two count values as one record set using group by.

field1 = group by (department) nvarachar
field2 = count (closed) datetime

I have tried using derived tables with no luck getting the desired result.

field2 is a datetime field as indicated I want a count for two conditions

1. WHERE field2 is null
2. WHERE field2 is not null

End Results would like this
======
Department | OpenItems | ClosedItems
Department1 | 32 | 24
Departmnet2 | 87 | 46
Department3 | 42 | 76

=======

I got it *almost* working with derived tables, but the group by function was not putting the department as one single row. I was getting multiple rows for departments.

I realize this is probably a simple answer and I am making this a lot harder than it actually is....

Any suggestions?

View Replies !

Error Invalid Column Name (In Sqlserver 2005) While Giving Alias Column Name
ALTER procedure [dbo].[MyPro](@StartRowIndex int,@MaximumRows int)
As
Begin
Declare @Sel Nvarchar(2000)set @Sel=N'Select *,Row_number() over(order by myId) as ROWNUM from MyFirstTable Where ROWNUM
Between ' + convert(nvarchar(15),@StartRowIndex) + ' and ('+ convert(nvarchar(15),@StartRowIndex) + '+' + convert(nvarchar(15),@MaximumRows) + ')-1'
print @Sel
Exec Sp_executesql @Sel
End

--Execute Mypro 1,4 --->>Here I Executed
Error
Select *,Row_number() over(order by myId) as ROWNUM from MyFirstTable Where ROWNUM
Between 1 and (1+4)-1
Msg 207, Level 16, State 1, Line 1
Invalid column name 'ROWNUM'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'ROWNUM
Procedure successfully created but giving error while Excuting'.
Please anybody give reply
Thanks

View Replies !

SSIS Keeps Giving Me The &&"Visual Studio Is Busy&&" Error, Help!!!
I am working on a SSIS solution that has 4 packages. All 4 have run before and I have not modified any of them, however I keep getting the error message and it keeps locking up on one of my packages. Does anyone have any advice or a remedy for this?

Any help is appreciated!

View Replies !

Error While Creating Inline Function - CREATE FUNCTION Failed Because A Column Name Is Not Specified For Column 1.

Hi,

I am trying to create a inline function which is listed below.

USE [Northwind]

SET ANSI_NULLS ON

GO

CREATE FUNCTION newIdentity()

RETURNS TABLE

AS

RETURN

(SELECT ident_current('orders'))

GO

while executing this function in sql server 2005 my get this error

CREATE FUNCTION failed because a column name is not specified for column 1.

Pleae help me to fix this error

thanks

Purnima

View Replies !

Can Anyone Help Me With This? It's Giving Me A Bad Headache!
Hi All,

I have a table called Prizes. Here's how it looks in design view with some value placed inside for Illustration purposes.

PrizeID 1, 2, 3, 4, 5
PromotionID 1, 1, 1, 2, 1
PrizeName 10 Cash, 5 cash, 10 cash, 15 cash, 20 cash

My challenge is that I need to write a stored procedure for example, that will find the PrizeID associated with the 4th count of the PromotionID that equals 1 . So in this example, counting to the 4th PromotionID that equalls 1 give us a PrizeID of 5.

I hope I've made myself clear! Can anyone write out a mini SP on how to do this.

Many many thanks in advance,
Brad

View Replies !

SQL Is Giving Different Row Counts
Hi,

...giving a very 'summarized' scenario of the problem I have trying to
solve all day (make it 2 days now).

Below are the relevant DDLs... I am not listing the DDLs of my other tables:

CREATE TABLE [SalesFACT] (
[varchar] (10),
[TransDate] [varchar] (10),
[SaleAmt] [float],
[CustCode] [varchar] (10)
. . .
)

I populate the above table via a DTS and have checked and have verified that correct data is coming in... I also have a product master table; for business reasons we can have the same product created with different ProductCodes though the rest of the Product details are EXACTLY the same. We have covered this using a field named 'UniqueProdCode'.

CREATE TABLE ProdMaster(
[ProdCode] [varchar] (10),
[ProdName] [varchar] (35),

[UniqueProdCode] [varchar] (10),

... many other product fields e.g. unit price, category etc...
...
)

First a small Request:
Please note that I have NOT defined links between my tables (in the diagram editor) nor have I defined Primary keys (or any constraint) for any of the tables. When you kindly reply, please suggest I should define primary keys for the tables and also link them in the diagram editor.

[u]THE PROBLEM:
When I do a count(*) query on the table 'SalesFACT', I get the correct number of records.

If I create a view, add table 'SalesFACT' and table ProdMaster, link the
UniqueProdCode field of table 'SalesFACT' with the UniqueProdCode field of ProdMaster (so that I can also get the name, category, etc. for the products in the SalesFACT), and run a count(*) query I get a much higher and incorrect number of rows. The SQL for the view is:

SELECT dbo.SalesFACT.TransDate, dbo.SalesFACT.UniqueProdCode,
dbo.SalesFACT.SaleAmt
FROM dbo.SalesFACT INNER JOIN dbo.ProdMaster ON dbo.SalesFACT.UniqueProdCode = dbo.ProdMaster.UniqueProdCode

Kindly note that I have checked and the contents of the table SalesFACT' UniqueProdCode field DOES contain the correct data i.e. it contains the UniqueProdCode and NOT the ProdCode.

But if i link the "wrong fields", I get the correct count count :confused: i.e. I create a very similar view (as mentioned above) but instead link the UniqueProdCode of table SalesFACT with the ProdCode field (not the UniqueProdCode field) of ProdMaster
table I get the correct count. This is really driving me nuts and I just can't understand what's going on and why the "REVERSE" logic. For your convenience here is the SQL for the 2nd view:

SELECTdbo.SalesFACT.TransDate, dbo.SalesFACT.UniqueProdCode,
dbo.SalesFACT.SaleAmt
FROM dbo.SalesFACT INNER JOIN dbo.ProdMaster ON dbo.SalesFACT.UniqueProdCode = dbo.ProdMaster.ProdCode

Please guide... I have run out of all the things that I could check and thus this SOS and F1

Billions of thansk in advance.

View Replies !

Giving Permissions
Hi friends,

How can I give permission to a new user to all the tables in the Database.

I usually create New User and then give permission to each table One By One which takes lot of time.

regards,
zak.

View Replies !

Giving Up On This Now: RsLogonFailed
Hi there mates....

I have had my runnings with Vista and SSRS. I eventually got the reports server website to work by ways of lots and lots of butchering:

Making sure the RSWebApplication has got the following in:

<ReportServerUrl>http://m_vdberg_vista/ReportServer</ReportServerUrl> while having a blank in

<ReportServerVirtualDirectory></ReportServerVirtualDirectory>

Making sure that the reportserver's web.config doesn't have an xmlns or something tag in...

Pulling out hair

Pulling out some more hair

Eventually deciding to set the reports server and manager on the Default Web's application pool to make use of Classic .Net application pool.
It worked and I could finally browse my reports but every single one of them cannot be viewed and bombs out with:

Logon failed. (rsLogonFailed)

Logon failure: unknown user name or bad password. (Exception from HRESULT: 0x8007052E)

I have tried all sorts of combinations but none work..even Integrated Windows Authentication with the DB on my local machine. I have triple checked passwords etc and the SSRS configuration shows all of the statuses as green when i connect. Is there some sort of special setting that I have to set somewhere on Vista?? I have Vista Business

Cheers
Mike

View Replies !