Dear All,
I know how to parse this in ACCESS but not clear how to do this through
stored procedure. I Googled with different search criteria but didn't get an
y
useful hit. Any one have something handy?
I would like to parse:
"strFullName" listed as Habibullah, Mohammad I to three fields as:
strLastName: Habibullah
strFirstName: Mohammad
strMiddleName: I
Or another example, I would like "strFullName" listed as Huang, Chong Xi to
parse as:
strLastName: Huang
strFirstName: Chong
strMiddleName: Xi
Assuming that table name is User and field name is strFullName.
Thanks.
Habibullah.Habibullah,
You can use the T-SQL string functions to achieve your goal. See String
Functions in the SQL BOL. (esp. CHARINDEX and SUBSTRING). Ideally you'd
want to perform this string manipulation at the middle-tier or on the
client.
HTH
Jerry
"Habibullah" <Habibullah@.discussions.microsoft.com> wrote in message
news:18A25EB1-FE23-400B-82B1-3ED108B1B8DD@.microsoft.com...
> Dear All,
> I know how to parse this in ACCESS but not clear how to do this through
> stored procedure. I Googled with different search criteria but didn't get
> any
> useful hit. Any one have something handy?
> I would like to parse:
> "strFullName" listed as Habibullah, Mohammad I to three fields as:
> strLastName: Habibullah
> strFirstName: Mohammad
> strMiddleName: I
>
> Or another example, I would like "strFullName" listed as Huang, Chong Xi
> to
> parse as:
> strLastName: Huang
> strFirstName: Chong
> strMiddleName: Xi
> Assuming that table name is User and field name is strFullName.
> Thanks.
> Habibullah.|||Hi
One way to do it would be to use
SELECT
LEFT(strFullName,CHARINDEX(',',strFullNa
me)-1) AS strLastname,
LTRIM(SUBSTRING(strFullName,CHARINDEX(',
',strFullName)+1,LEN(strFullName)-CH
ARINDEX('
',REVERSE(strFullName))-CHARINDEX(',',strFullName))) AS strFirstname,
RIGHT(strFullName,CHARINDEX(' ',REVERSE(strFullName))-1) AS strMiddlename
FROM User
But this depends on how consistent the data is.
John
"Habibullah" <Habibullah@.discussions.microsoft.com> wrote in message
news:18A25EB1-FE23-400B-82B1-3ED108B1B8DD@.microsoft.com...
> Dear All,
> I know how to parse this in ACCESS but not clear how to do this through
> stored procedure. I Googled with different search criteria but didn't get
> any
> useful hit. Any one have something handy?
> I would like to parse:
> "strFullName" listed as Habibullah, Mohammad I to three fields as:
> strLastName: Habibullah
> strFirstName: Mohammad
> strMiddleName: I
>
> Or another example, I would like "strFullName" listed as Huang, Chong Xi
> to
> parse as:
> strLastName: Huang
> strFirstName: Chong
> strMiddleName: Xi
> Assuming that table name is User and field name is strFullName.
> Thanks.
> Habibullah.
Showing posts with label access. Show all posts
Showing posts with label access. Show all posts
Wednesday, March 28, 2012
Friday, March 23, 2012
partitioning fact table
We have a very big fact table, almost 20million rows for each year, If I partition with each year in the warehouse how can I access all the fact partions as a single fact table when designing it in the data source view in BIDS?
Although I can partition the cube in analysis services by each year, this doesn't seem to solve the problem because the table is enormous and I need to partition it in the warehouse level. Can anyone shed some light?
I will greatly appreciate your feedbacks.
Rok
In SQL 2005 you can create partitioned tables and Indexes and the Query Engine is smart enough to direct requests to a specific partition. You can also store different partitions in different file groups if you want. You should be able to find plenty of documentation in BOL.
But you don't need to have all your data in one table for it to work with BIDS, You can pick one table to work with in the cube designer and then link other tables to specific partitions.
|||
This paper covers both relational and OLAP partitioning for Project REAL:
Project REAL: Data Lifecycle Partitioning
Overview
This paper provides a detailed discussion on how partitioning was implemented, both on the relational data warehouse and in the Analysis Services cubes. In addition to providing the general "how we did it" overview, we include specific code segments and lessons learned in the hopes that the reader will benefit from both our successes and failures. It is our hope that anyone planning or implementing a BI system based on SQL Server 2005 will find the reference implementation useful.
...
Tuesday, March 20, 2012
Parameters!FiledName in Custom Code
Hi All,
How I can access a Parameter field(eg - Parameters!vcReportVersion.Value)
within in Custom Code.
Public Shared Function GetParameterText() As String
Return Parameters!vcReportVersion.Value
End Function
This function gives the following error message :
"There is an error on line 37 of custom code: [BC30469] Reference to a
non-shared member requires an object reference."
What is the Object name that contain Parameter collection?
Thanks,
SamYou'll need to pass it as a parameter to the custom code.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Samuel" <samuel@.photoninfotech.com> wrote in message
news:uCAAkJBZEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> How I can access a Parameter field(eg - Parameters!vcReportVersion.Value)
> within in Custom Code.
> Public Shared Function GetParameterText() As String
> Return Parameters!vcReportVersion.Value
> End Function
> This function gives the following error message :
> "There is an error on line 37 of custom code: [BC30469] Reference to a
> non-shared member requires an object reference."
> What is the Object name that contain Parameter collection?
> Thanks,
> Sam
>
How I can access a Parameter field(eg - Parameters!vcReportVersion.Value)
within in Custom Code.
Public Shared Function GetParameterText() As String
Return Parameters!vcReportVersion.Value
End Function
This function gives the following error message :
"There is an error on line 37 of custom code: [BC30469] Reference to a
non-shared member requires an object reference."
What is the Object name that contain Parameter collection?
Thanks,
SamYou'll need to pass it as a parameter to the custom code.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Samuel" <samuel@.photoninfotech.com> wrote in message
news:uCAAkJBZEHA.1652@.TK2MSFTNGP09.phx.gbl...
> Hi All,
> How I can access a Parameter field(eg - Parameters!vcReportVersion.Value)
> within in Custom Code.
> Public Shared Function GetParameterText() As String
> Return Parameters!vcReportVersion.Value
> End Function
> This function gives the following error message :
> "There is an error on line 37 of custom code: [BC30469] Reference to a
> non-shared member requires an object reference."
> What is the Object name that contain Parameter collection?
> Thanks,
> Sam
>
Monday, March 12, 2012
Parameters on Reporting Services 2005 REPOST
Hi, I experiencing some problems to access the Parameters collection inside
a custom code to build a sql statement.
Below is my code (very simple):
DataSet:
=Code.SQL(Parameters)
Custom Code:
Public Function SQL(ByRef pars As Object) As String
Dim stmt as String
stmt = "SELECT * FROM customers WHERE ID = " & pars!ID.Value
return stmt
End Function
This code works just fine on the Preview(Designer) but if I test the report
on the
browser, it doesnt work and returns the following error:
a.. An error has occurred during report processing.
a.. Cannot set the command text for data set 'ExpoMedios'.
a.. Error during processing of the CommandText expression of dataset
'ExpoMedios'.
Doing some debugging the error message inside the function is:
Attempt to access the method failed.
Can anyone pleae explain why this is happening. Your help will be
appreciated.
Regards,
FabianHi,
have you tried to declare pars As Parameter and not as Object ?
"Fabian von Romberg" wrote:
> Hi, I experiencing some problems to access the Parameters collection inside
> a custom code to build a sql statement.
> Below is my code (very simple):
> DataSet:
> =Code.SQL(Parameters)
> Custom Code:
> Public Function SQL(ByRef pars As Object) As String
> Dim stmt as String
> stmt = "SELECT * FROM customers WHERE ID = " & pars!ID.Value
> return stmt
> End Function
>
> This code works just fine on the Preview(Designer) but if I test the report
> on the
> browser, it doesnt work and returns the following error:
> a.. An error has occurred during report processing.
> a.. Cannot set the command text for data set 'ExpoMedios'.
> a.. Error during processing of the CommandText expression of dataset
> 'ExpoMedios'.
> Doing some debugging the error message inside the function is:
> Attempt to access the method failed.
>
> Can anyone pleae explain why this is happening. Your help will be
> appreciated.
> Regards,
> Fabian
>
>|||Whatt!!!!!!!!!!!!,
I should have tried that before. It did it. I used this code on the older
version of Reporting Services and never got that error message, actually I
think I was not able to set a function parameter as type of Parameters but
Object. On 2005's seems to be the correct way to do it.
Thanks Cedric, I appreciated it.
Regards,
Fabian von Romberg
"Cedric" <Cedric@.discussions.microsoft.com> wrote in message
news:C8A38357-8A82-473A-8208-6DEEBF40DC9F@.microsoft.com...
> Hi,
> have you tried to declare pars As Parameter and not as Object ?
>
> "Fabian von Romberg" wrote:
> > Hi, I experiencing some problems to access the Parameters collection
inside
> > a custom code to build a sql statement.
> >
> > Below is my code (very simple):
> >
> > DataSet:
> > =Code.SQL(Parameters)
> >
> > Custom Code:
> >
> > Public Function SQL(ByRef pars As Object) As String
> > Dim stmt as String
> > stmt = "SELECT * FROM customers WHERE ID = " & pars!ID.Value
> > return stmt
> > End Function
> >
> >
> > This code works just fine on the Preview(Designer) but if I test the
report
> > on the
> > browser, it doesnt work and returns the following error:
> > a.. An error has occurred during report processing.
> > a.. Cannot set the command text for data set 'ExpoMedios'.
> > a.. Error during processing of the CommandText expression of
dataset
> > 'ExpoMedios'.
> >
> > Doing some debugging the error message inside the function is:
> >
> > Attempt to access the method failed.
> >
> >
> > Can anyone pleae explain why this is happening. Your help will be
> > appreciated.
> >
> > Regards,
> > Fabian
> >
> >
> >
> >
a custom code to build a sql statement.
Below is my code (very simple):
DataSet:
=Code.SQL(Parameters)
Custom Code:
Public Function SQL(ByRef pars As Object) As String
Dim stmt as String
stmt = "SELECT * FROM customers WHERE ID = " & pars!ID.Value
return stmt
End Function
This code works just fine on the Preview(Designer) but if I test the report
on the
browser, it doesnt work and returns the following error:
a.. An error has occurred during report processing.
a.. Cannot set the command text for data set 'ExpoMedios'.
a.. Error during processing of the CommandText expression of dataset
'ExpoMedios'.
Doing some debugging the error message inside the function is:
Attempt to access the method failed.
Can anyone pleae explain why this is happening. Your help will be
appreciated.
Regards,
FabianHi,
have you tried to declare pars As Parameter and not as Object ?
"Fabian von Romberg" wrote:
> Hi, I experiencing some problems to access the Parameters collection inside
> a custom code to build a sql statement.
> Below is my code (very simple):
> DataSet:
> =Code.SQL(Parameters)
> Custom Code:
> Public Function SQL(ByRef pars As Object) As String
> Dim stmt as String
> stmt = "SELECT * FROM customers WHERE ID = " & pars!ID.Value
> return stmt
> End Function
>
> This code works just fine on the Preview(Designer) but if I test the report
> on the
> browser, it doesnt work and returns the following error:
> a.. An error has occurred during report processing.
> a.. Cannot set the command text for data set 'ExpoMedios'.
> a.. Error during processing of the CommandText expression of dataset
> 'ExpoMedios'.
> Doing some debugging the error message inside the function is:
> Attempt to access the method failed.
>
> Can anyone pleae explain why this is happening. Your help will be
> appreciated.
> Regards,
> Fabian
>
>|||Whatt!!!!!!!!!!!!,
I should have tried that before. It did it. I used this code on the older
version of Reporting Services and never got that error message, actually I
think I was not able to set a function parameter as type of Parameters but
Object. On 2005's seems to be the correct way to do it.
Thanks Cedric, I appreciated it.
Regards,
Fabian von Romberg
"Cedric" <Cedric@.discussions.microsoft.com> wrote in message
news:C8A38357-8A82-473A-8208-6DEEBF40DC9F@.microsoft.com...
> Hi,
> have you tried to declare pars As Parameter and not as Object ?
>
> "Fabian von Romberg" wrote:
> > Hi, I experiencing some problems to access the Parameters collection
inside
> > a custom code to build a sql statement.
> >
> > Below is my code (very simple):
> >
> > DataSet:
> > =Code.SQL(Parameters)
> >
> > Custom Code:
> >
> > Public Function SQL(ByRef pars As Object) As String
> > Dim stmt as String
> > stmt = "SELECT * FROM customers WHERE ID = " & pars!ID.Value
> > return stmt
> > End Function
> >
> >
> > This code works just fine on the Preview(Designer) but if I test the
report
> > on the
> > browser, it doesnt work and returns the following error:
> > a.. An error has occurred during report processing.
> > a.. Cannot set the command text for data set 'ExpoMedios'.
> > a.. Error during processing of the CommandText expression of
dataset
> > 'ExpoMedios'.
> >
> > Doing some debugging the error message inside the function is:
> >
> > Attempt to access the method failed.
> >
> >
> > Can anyone pleae explain why this is happening. Your help will be
> > appreciated.
> >
> > Regards,
> > Fabian
> >
> >
> >
> >
Friday, March 9, 2012
Parameters in subqueries ?
Can anyone tell me if this is a known bug? When using a parameter in a
subquery I get an access violation
The query below reproduces the problem using Northwind.
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
[Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
It also happens when using OLEDB
SELECT * FROM Orders
WHERE CustomerID IN (SELECT customerID FROM Customers WHERE CompanyName =
?)
Microsoft SQL Server 2000 - 8.00.857 (Intel X86) Sep 2 2003 18:55:32
Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
What is question mark supposed to represent? Is this in a stored procedure?
Did you mean to use a named @.parameter?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"news.microsoft.com" <deo.is@.unknown.com> wrote in message
news:OZMXwpqPEHA.3708@.TK2MSFTNGP10.phx.gbl...
> Can anyone tell me if this is a known bug? When using a parameter in a
> subquery I get an access violation
> The query below reproduces the problem using Northwind.
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
> [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
> It also happens when using OLEDB
> SELECT * FROM Orders
> WHERE CustomerID IN (SELECT customerID FROM Customers WHERE CompanyName =
> ?)
> Microsoft SQL Server 2000 - 8.00.857 (Intel X86) Sep 2 2003 18:55:32
> Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on
Windows
> NT 5.0 (Build 2195: Service Pack 4)
>
|||The question mark represents and unnamed parameter.
I never use the @.param syntax for queries since Enterprise manager does
support them.
It is not in a stored procedure. It's being submitted from C# code but it
blows up just the same from Enterprise SQL Mangler
Seems to work ok with named parameters from Query Analyser.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uJgffxqPEHA.2128@.TK2MSFTNGP11.phx.gbl...
> What is question mark supposed to represent? Is this in a stored
procedure?[vbcol=seagreen]
> Did you mean to use a named @.parameter?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "news.microsoft.com" <deo.is@.unknown.com> wrote in message
> news:OZMXwpqPEHA.3708@.TK2MSFTNGP10.phx.gbl...
=
> Windows
>
|||> I never use the @.param syntax for queries since Enterprise manager does
> support them.
Why are you using Enterprise Manager for this?
> It is not in a stored procedure. It's being submitted from C# code but it
> blows up just the same from Enterprise SQL Mangler
If you're sending the code like that, why not fill in the parameter value in
C#?
> Seems to work ok with named parameters from Query Analyser.
As it should. Not all providers are going to understand the same funky
syntax that EM requires.
|||I use the ? style parameter since the query wizards in Visual Studio don't
support the @.param style. The VS.NET wizards must use the same codebase as
EM since they mangle queries in much the same fashion
Thanks for the help
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:O61yeArPEHA.3016@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Why are you using Enterprise Manager for this?
it
> If you're sending the code like that, why not fill in the parameter value
in
> C#?
>
> As it should. Not all providers are going to understand the same funky
> syntax that EM requires.
>
|||"news.microsoft.com" wrote:
> I use the ? style parameter since the query wizards in Visual Studio don't
> support the @.param style. The VS.NET wizards must use the same codebase as
> EM since they mangle queries in much the same fashion
> Thanks for the help
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:O61yeArPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> it
> in
>
>
|||I'm getting that error "[Microsoft][ODBC SQL Server Driver]Syntax error or access violation", and I'm just trying to create some tables from a script in Query Analyzer. I'm trying to create the tables in the pubs database. What's the deal
Microsoft SQL Server 7.00 - 7.00.961 (Intel X86)
Oct 24 2000 18:39:12
Copyright (c) 1988-1998 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
"news.microsoft.com" wrote:
> I use the ? style parameter since the query wizards in Visual Studio don't
> support the @.param style. The VS.NET wizards must use the same codebase as
> EM since they mangle queries in much the same fashion
> Thanks for the help
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:O61yeArPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> it
> in
>
>
|||I'm getting that message "[Microsoft][ODBC SQL Server Driver]Syntax error or access violation" and all I'm doing is trying to create some tables from a script in Query Analyzer. Can someone help me with this?
Microsoft SQL Server 7.00 - 7.00.961 (Intel X86)
Oct 24 2000 18:39:12
Copyright (c) 1988-1998 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
"news.microsoft.com" wrote:
> Can anyone tell me if this is a known bug? When using a parameter in a
> subquery I get an access violation
> The query below reproduces the problem using Northwind.
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
> [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
> It also happens when using OLEDB
> SELECT * FROM Orders
> WHERE CustomerID IN (SELECT customerID FROM Customers WHERE CompanyName =
> ?)
> Microsoft SQL Server 2000 - 8.00.857 (Intel X86) Sep 2 2003 18:55:32
> Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows
> NT 5.0 (Build 2195: Service Pack 4)
>
>
|||I'm going to try this again. I'm having the same problem. I'm getting that message "[Microsoft][ODBC SQL Server Driver]Syntax error or access violation" and all I'm doing is trying to create some tables in the pubs database from a script. I'm using Que
ry Analyzer. Can someone help me with this.
"news.microsoft.com" wrote:
> Can anyone tell me if this is a known bug? When using a parameter in a
> subquery I get an access violation
> The query below reproduces the problem using Northwind.
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
> [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
> It also happens when using OLEDB
> SELECT * FROM Orders
> WHERE CustomerID IN (SELECT customerID FROM Customers WHERE CompanyName =
> ?)
> Microsoft SQL Server 2000 - 8.00.857 (Intel X86) Sep 2 2003 18:55:32
> Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows
> NT 5.0 (Build 2195: Service Pack 4)
>
>
subquery I get an access violation
The query below reproduces the problem using Northwind.
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
[Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
It also happens when using OLEDB
SELECT * FROM Orders
WHERE CustomerID IN (SELECT customerID FROM Customers WHERE CompanyName =
?)
Microsoft SQL Server 2000 - 8.00.857 (Intel X86) Sep 2 2003 18:55:32
Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
What is question mark supposed to represent? Is this in a stored procedure?
Did you mean to use a named @.parameter?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"news.microsoft.com" <deo.is@.unknown.com> wrote in message
news:OZMXwpqPEHA.3708@.TK2MSFTNGP10.phx.gbl...
> Can anyone tell me if this is a known bug? When using a parameter in a
> subquery I get an access violation
> The query below reproduces the problem using Northwind.
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
> [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
> It also happens when using OLEDB
> SELECT * FROM Orders
> WHERE CustomerID IN (SELECT customerID FROM Customers WHERE CompanyName =
> ?)
> Microsoft SQL Server 2000 - 8.00.857 (Intel X86) Sep 2 2003 18:55:32
> Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on
Windows
> NT 5.0 (Build 2195: Service Pack 4)
>
|||The question mark represents and unnamed parameter.
I never use the @.param syntax for queries since Enterprise manager does
support them.
It is not in a stored procedure. It's being submitted from C# code but it
blows up just the same from Enterprise SQL Mangler
Seems to work ok with named parameters from Query Analyser.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uJgffxqPEHA.2128@.TK2MSFTNGP11.phx.gbl...
> What is question mark supposed to represent? Is this in a stored
procedure?[vbcol=seagreen]
> Did you mean to use a named @.parameter?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "news.microsoft.com" <deo.is@.unknown.com> wrote in message
> news:OZMXwpqPEHA.3708@.TK2MSFTNGP10.phx.gbl...
=
> Windows
>
|||> I never use the @.param syntax for queries since Enterprise manager does
> support them.
Why are you using Enterprise Manager for this?
> It is not in a stored procedure. It's being submitted from C# code but it
> blows up just the same from Enterprise SQL Mangler
If you're sending the code like that, why not fill in the parameter value in
C#?
> Seems to work ok with named parameters from Query Analyser.
As it should. Not all providers are going to understand the same funky
syntax that EM requires.
|||I use the ? style parameter since the query wizards in Visual Studio don't
support the @.param style. The VS.NET wizards must use the same codebase as
EM since they mangle queries in much the same fashion
Thanks for the help
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:O61yeArPEHA.3016@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Why are you using Enterprise Manager for this?
it
> If you're sending the code like that, why not fill in the parameter value
in
> C#?
>
> As it should. Not all providers are going to understand the same funky
> syntax that EM requires.
>
|||"news.microsoft.com" wrote:
> I use the ? style parameter since the query wizards in Visual Studio don't
> support the @.param style. The VS.NET wizards must use the same codebase as
> EM since they mangle queries in much the same fashion
> Thanks for the help
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:O61yeArPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> it
> in
>
>
|||I'm getting that error "[Microsoft][ODBC SQL Server Driver]Syntax error or access violation", and I'm just trying to create some tables from a script in Query Analyzer. I'm trying to create the tables in the pubs database. What's the deal
Microsoft SQL Server 7.00 - 7.00.961 (Intel X86)
Oct 24 2000 18:39:12
Copyright (c) 1988-1998 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
"news.microsoft.com" wrote:
> I use the ? style parameter since the query wizards in Visual Studio don't
> support the @.param style. The VS.NET wizards must use the same codebase as
> EM since they mangle queries in much the same fashion
> Thanks for the help
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:O61yeArPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> it
> in
>
>
|||I'm getting that message "[Microsoft][ODBC SQL Server Driver]Syntax error or access violation" and all I'm doing is trying to create some tables from a script in Query Analyzer. Can someone help me with this?
Microsoft SQL Server 7.00 - 7.00.961 (Intel X86)
Oct 24 2000 18:39:12
Copyright (c) 1988-1998 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
"news.microsoft.com" wrote:
> Can anyone tell me if this is a known bug? When using a parameter in a
> subquery I get an access violation
> The query below reproduces the problem using Northwind.
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
> [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
> It also happens when using OLEDB
> SELECT * FROM Orders
> WHERE CustomerID IN (SELECT customerID FROM Customers WHERE CompanyName =
> ?)
> Microsoft SQL Server 2000 - 8.00.857 (Intel X86) Sep 2 2003 18:55:32
> Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows
> NT 5.0 (Build 2195: Service Pack 4)
>
>
|||I'm going to try this again. I'm having the same problem. I'm getting that message "[Microsoft][ODBC SQL Server Driver]Syntax error or access violation" and all I'm doing is trying to create some tables in the pubs database from a script. I'm using Que
ry Analyzer. Can someone help me with this.
"news.microsoft.com" wrote:
> Can anyone tell me if this is a known bug? When using a parameter in a
> subquery I get an access violation
> The query below reproduces the problem using Northwind.
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
> [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
> It also happens when using OLEDB
> SELECT * FROM Orders
> WHERE CustomerID IN (SELECT customerID FROM Customers WHERE CompanyName =
> ?)
> Microsoft SQL Server 2000 - 8.00.857 (Intel X86) Sep 2 2003 18:55:32
> Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows
> NT 5.0 (Build 2195: Service Pack 4)
>
>
Parameters in subqueries ?
Can anyone tell me if this is a known bug? When using a parameter in a
subquery I get an access violation
The query below reproduces the problem using Northwind.
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
[Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
It also happens when using OLEDB
SELECT * FROM Orders
WHERE CustomerID IN (SELECT customerID FROM Customers WHERE CompanyName =
?)
Microsoft SQL Server 2000 - 8.00.857 (Intel X86) Sep 2 2003 18:55:32
Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)What is question mark supposed to represent? Is this in a stored procedure?
Did you mean to use a named @.parameter?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"news.microsoft.com" <deo.is@.unknown.com> wrote in message
news:OZMXwpqPEHA.3708@.TK2MSFTNGP10.phx.gbl...
> Can anyone tell me if this is a known bug? When using a parameter in a
> subquery I get an access violation
> The query below reproduces the problem using Northwind.
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violatio
n
> [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
> It also happens when using OLEDB
> SELECT * FROM Orders
> WHERE CustomerID IN (SELECT customerID FROM Customers WHERE CompanyName =
> ?)
> Microsoft SQL Server 2000 - 8.00.857 (Intel X86) Sep 2 2003 18:55:32
> Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on
Windows
> NT 5.0 (Build 2195: Service Pack 4)
>|||The question mark represents and unnamed parameter.
I never use the @.param syntax for queries since Enterprise manager does
support them.
It is not in a stored procedure. It's being submitted from C# code but it
blows up just the same from Enterprise SQL Mangler
Seems to work ok with named parameters from Query Analyser.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uJgffxqPEHA.2128@.TK2MSFTNGP11.phx.gbl...
> What is question mark supposed to represent? Is this in a stored
procedure?
> Did you mean to use a named @.parameter?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "news.microsoft.com" <deo.is@.unknown.com> wrote in message
> news:OZMXwpqPEHA.3708@.TK2MSFTNGP10.phx.gbl...
=[vbcol=seagreen]
> Windows
>|||> I never use the @.param syntax for queries since Enterprise manager does
> support them.
Why are you using Enterprise Manager for this?
> It is not in a stored procedure. It's being submitted from C# code but it
> blows up just the same from Enterprise SQL Mangler
If you're sending the code like that, why not fill in the parameter value in
C#?
> Seems to work ok with named parameters from Query Analyser.
As it should. Not all providers are going to understand the same funky
syntax that EM requires.|||I use the ? style parameter since the query wizards in Visual Studio don't
support the @.param style. The VS.NET wizards must use the same codebase as
EM since they mangle queries in much the same fashion
Thanks for the help
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:O61yeArPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> Why are you using Enterprise Manager for this?
>
it[vbcol=seagreen]
> If you're sending the code like that, why not fill in the parameter value
in
> C#?
>
> As it should. Not all providers are going to understand the same funky
> syntax that EM requires.
>|||"news.microsoft.com" wrote:
> I use the ? style parameter since the query wizards in Visual Studio don't
> support the @.param style. The VS.NET wizards must use the same codebase a
s
> EM since they mangle queries in much the same fashion
> Thanks for the help
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:O61yeArPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> it
> in
>
>|||I'm getting that error "[Microsoft][ODBC SQL Server Driver]Syntax er
ror or access violation", and I'm just trying to create some tables from a s
cript in Query Analyzer. I'm trying to create the tables in the pubs databa
se. What's the deal
Microsoft SQL Server 7.00 - 7.00.961 (Intel X86)
Oct 24 2000 18:39:12
Copyright (c) 1988-1998 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
"news.microsoft.com" wrote:
> I use the ? style parameter since the query wizards in Visual Studio don't
> support the @.param style. The VS.NET wizards must use the same codebase a
s
> EM since they mangle queries in much the same fashion
> Thanks for the help
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:O61yeArPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> it
> in
>
>|||I'm getting that message "[Microsoft][ODBC SQL Server Driver]Syntax
error or access violation" and all I'm doing is trying to create some tables
from a script in Query Analyzer. Can someone help me with this?
Microsoft SQL Server 7.00 - 7.00.961 (Intel X86)
Oct 24 2000 18:39:12
Copyright (c) 1988-1998 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
"news.microsoft.com" wrote:
> Can anyone tell me if this is a known bug? When using a parameter in a
> subquery I get an access violation
> The query below reproduces the problem using Northwind.
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violatio
n
> [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
> It also happens when using OLEDB
> SELECT * FROM Orders
> WHERE CustomerID IN (SELECT customerID FROM Customers WHERE CompanyName =
> ?)
> Microsoft SQL Server 2000 - 8.00.857 (Intel X86) Sep 2 2003 18:55:32
> Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Window
s
> NT 5.0 (Build 2195: Service Pack 4)
>
>|||I'm going to try this again. I'm having the same problem. I'm getting that
message "[Microsoft][ODBC SQL Server Driver]Syntax error or access
violation" and all I'm doing is trying to create some tables in the pubs dat
abase from a script. I'm using Que
ry Analyzer. Can someone help me with this.
"news.microsoft.com" wrote:
> Can anyone tell me if this is a known bug? When using a parameter in a
> subquery I get an access violation
> The query below reproduces the problem using Northwind.
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violatio
n
> [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
> It also happens when using OLEDB
> SELECT * FROM Orders
> WHERE CustomerID IN (SELECT customerID FROM Customers WHERE CompanyName =
> ?)
> Microsoft SQL Server 2000 - 8.00.857 (Intel X86) Sep 2 2003 18:55:32
> Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Window
s
> NT 5.0 (Build 2195: Service Pack 4)
>
>
subquery I get an access violation
The query below reproduces the problem using Northwind.
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
[Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
It also happens when using OLEDB
SELECT * FROM Orders
WHERE CustomerID IN (SELECT customerID FROM Customers WHERE CompanyName =
?)
Microsoft SQL Server 2000 - 8.00.857 (Intel X86) Sep 2 2003 18:55:32
Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)What is question mark supposed to represent? Is this in a stored procedure?
Did you mean to use a named @.parameter?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"news.microsoft.com" <deo.is@.unknown.com> wrote in message
news:OZMXwpqPEHA.3708@.TK2MSFTNGP10.phx.gbl...
> Can anyone tell me if this is a known bug? When using a parameter in a
> subquery I get an access violation
> The query below reproduces the problem using Northwind.
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violatio
n
> [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
> It also happens when using OLEDB
> SELECT * FROM Orders
> WHERE CustomerID IN (SELECT customerID FROM Customers WHERE CompanyName =
> ?)
> Microsoft SQL Server 2000 - 8.00.857 (Intel X86) Sep 2 2003 18:55:32
> Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on
Windows
> NT 5.0 (Build 2195: Service Pack 4)
>|||The question mark represents and unnamed parameter.
I never use the @.param syntax for queries since Enterprise manager does
support them.
It is not in a stored procedure. It's being submitted from C# code but it
blows up just the same from Enterprise SQL Mangler
Seems to work ok with named parameters from Query Analyser.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uJgffxqPEHA.2128@.TK2MSFTNGP11.phx.gbl...
> What is question mark supposed to represent? Is this in a stored
procedure?
> Did you mean to use a named @.parameter?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "news.microsoft.com" <deo.is@.unknown.com> wrote in message
> news:OZMXwpqPEHA.3708@.TK2MSFTNGP10.phx.gbl...
=[vbcol=seagreen]
> Windows
>|||> I never use the @.param syntax for queries since Enterprise manager does
> support them.
Why are you using Enterprise Manager for this?
> It is not in a stored procedure. It's being submitted from C# code but it
> blows up just the same from Enterprise SQL Mangler
If you're sending the code like that, why not fill in the parameter value in
C#?
> Seems to work ok with named parameters from Query Analyser.
As it should. Not all providers are going to understand the same funky
syntax that EM requires.|||I use the ? style parameter since the query wizards in Visual Studio don't
support the @.param style. The VS.NET wizards must use the same codebase as
EM since they mangle queries in much the same fashion
Thanks for the help
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:O61yeArPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> Why are you using Enterprise Manager for this?
>
it[vbcol=seagreen]
> If you're sending the code like that, why not fill in the parameter value
in
> C#?
>
> As it should. Not all providers are going to understand the same funky
> syntax that EM requires.
>|||"news.microsoft.com" wrote:
> I use the ? style parameter since the query wizards in Visual Studio don't
> support the @.param style. The VS.NET wizards must use the same codebase a
s
> EM since they mangle queries in much the same fashion
> Thanks for the help
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:O61yeArPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> it
> in
>
>|||I'm getting that error "[Microsoft][ODBC SQL Server Driver]Syntax er
ror or access violation", and I'm just trying to create some tables from a s
cript in Query Analyzer. I'm trying to create the tables in the pubs databa
se. What's the deal
Microsoft SQL Server 7.00 - 7.00.961 (Intel X86)
Oct 24 2000 18:39:12
Copyright (c) 1988-1998 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
"news.microsoft.com" wrote:
> I use the ? style parameter since the query wizards in Visual Studio don't
> support the @.param style. The VS.NET wizards must use the same codebase a
s
> EM since they mangle queries in much the same fashion
> Thanks for the help
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:O61yeArPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> it
> in
>
>|||I'm getting that message "[Microsoft][ODBC SQL Server Driver]Syntax
error or access violation" and all I'm doing is trying to create some tables
from a script in Query Analyzer. Can someone help me with this?
Microsoft SQL Server 7.00 - 7.00.961 (Intel X86)
Oct 24 2000 18:39:12
Copyright (c) 1988-1998 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
"news.microsoft.com" wrote:
> Can anyone tell me if this is a known bug? When using a parameter in a
> subquery I get an access violation
> The query below reproduces the problem using Northwind.
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violatio
n
> [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
> It also happens when using OLEDB
> SELECT * FROM Orders
> WHERE CustomerID IN (SELECT customerID FROM Customers WHERE CompanyName =
> ?)
> Microsoft SQL Server 2000 - 8.00.857 (Intel X86) Sep 2 2003 18:55:32
> Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Window
s
> NT 5.0 (Build 2195: Service Pack 4)
>
>|||I'm going to try this again. I'm having the same problem. I'm getting that
message "[Microsoft][ODBC SQL Server Driver]Syntax error or access
violation" and all I'm doing is trying to create some tables in the pubs dat
abase from a script. I'm using Que
ry Analyzer. Can someone help me with this.
"news.microsoft.com" wrote:
> Can anyone tell me if this is a known bug? When using a parameter in a
> subquery I get an access violation
> The query below reproduces the problem using Northwind.
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violatio
n
> [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
> It also happens when using OLEDB
> SELECT * FROM Orders
> WHERE CustomerID IN (SELECT customerID FROM Customers WHERE CompanyName =
> ?)
> Microsoft SQL Server 2000 - 8.00.857 (Intel X86) Sep 2 2003 18:55:32
> Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Window
s
> NT 5.0 (Build 2195: Service Pack 4)
>
>
Parameters in subqueries ?
Can anyone tell me if this is a known bug? When using a parameter in a
subquery I get an access violation
The query below reproduces the problem using Northwind.
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
[Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
It also happens when using OLEDB
SELECT * FROM Orders
WHERE CustomerID IN (SELECT customerID FROM Customers WHERE CompanyName = ?)
Microsoft SQL Server 2000 - 8.00.857 (Intel X86) Sep 2 2003 18:55:32
Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)What is question mark supposed to represent? Is this in a stored procedure?
Did you mean to use a named @.parameter?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"news.microsoft.com" <deo.is@.unknown.com> wrote in message
news:OZMXwpqPEHA.3708@.TK2MSFTNGP10.phx.gbl...
> Can anyone tell me if this is a known bug? When using a parameter in a
> subquery I get an access violation
> The query below reproduces the problem using Northwind.
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
> [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
> It also happens when using OLEDB
> SELECT * FROM Orders
> WHERE CustomerID IN (SELECT customerID FROM Customers WHERE CompanyName => ?)
> Microsoft SQL Server 2000 - 8.00.857 (Intel X86) Sep 2 2003 18:55:32
> Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on
Windows
> NT 5.0 (Build 2195: Service Pack 4)
>|||The question mark represents and unnamed parameter.
I never use the @.param syntax for queries since Enterprise manager does
support them.
It is not in a stored procedure. It's being submitted from C# code but it
blows up just the same from Enterprise SQL Mangler
Seems to work ok with named parameters from Query Analyser.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uJgffxqPEHA.2128@.TK2MSFTNGP11.phx.gbl...
> What is question mark supposed to represent? Is this in a stored
procedure?
> Did you mean to use a named @.parameter?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "news.microsoft.com" <deo.is@.unknown.com> wrote in message
> news:OZMXwpqPEHA.3708@.TK2MSFTNGP10.phx.gbl...
> > Can anyone tell me if this is a known bug? When using a parameter in a
> > subquery I get an access violation
> > The query below reproduces the problem using Northwind.
> >
> > [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
> > [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
> >
> > It also happens when using OLEDB
> >
> > SELECT * FROM Orders
> > WHERE CustomerID IN (SELECT customerID FROM Customers WHERE CompanyName
=> > ?)
> >
> > Microsoft SQL Server 2000 - 8.00.857 (Intel X86) Sep 2 2003 18:55:32
> > Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on
> Windows
> > NT 5.0 (Build 2195: Service Pack 4)
> >
> >
>|||> I never use the @.param syntax for queries since Enterprise manager does
> support them.
Why are you using Enterprise Manager for this?
> It is not in a stored procedure. It's being submitted from C# code but it
> blows up just the same from Enterprise SQL Mangler
If you're sending the code like that, why not fill in the parameter value in
C#?
> Seems to work ok with named parameters from Query Analyser.
As it should. Not all providers are going to understand the same funky
syntax that EM requires.|||I use the ? style parameter since the query wizards in Visual Studio don't
support the @.param style. The VS.NET wizards must use the same codebase as
EM since they mangle queries in much the same fashion :(
Thanks for the help
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:O61yeArPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> > I never use the @.param syntax for queries since Enterprise manager does
> > support them.
> Why are you using Enterprise Manager for this?
> > It is not in a stored procedure. It's being submitted from C# code but
it
> > blows up just the same from Enterprise SQL Mangler
> If you're sending the code like that, why not fill in the parameter value
in
> C#?
> > Seems to work ok with named parameters from Query Analyser.
> As it should. Not all providers are going to understand the same funky
> syntax that EM requires.
>|||"news.microsoft.com" wrote:
> I use the ? style parameter since the query wizards in Visual Studio don't
> support the @.param style. The VS.NET wizards must use the same codebase as
> EM since they mangle queries in much the same fashion :(
> Thanks for the help
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:O61yeArPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> > > I never use the @.param syntax for queries since Enterprise manager does
> > > support them.
> >
> > Why are you using Enterprise Manager for this?
> >
> > > It is not in a stored procedure. It's being submitted from C# code but
> it
> > > blows up just the same from Enterprise SQL Mangler
> >
> > If you're sending the code like that, why not fill in the parameter value
> in
> > C#?
> >
> > > Seems to work ok with named parameters from Query Analyser.
> >
> > As it should. Not all providers are going to understand the same funky
> > syntax that EM requires.
> >
> >
>
>
subquery I get an access violation
The query below reproduces the problem using Northwind.
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation
[Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
It also happens when using OLEDB
SELECT * FROM Orders
WHERE CustomerID IN (SELECT customerID FROM Customers WHERE CompanyName = ?)
Microsoft SQL Server 2000 - 8.00.857 (Intel X86) Sep 2 2003 18:55:32
Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)What is question mark supposed to represent? Is this in a stored procedure?
Did you mean to use a named @.parameter?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"news.microsoft.com" <deo.is@.unknown.com> wrote in message
news:OZMXwpqPEHA.3708@.TK2MSFTNGP10.phx.gbl...
> Can anyone tell me if this is a known bug? When using a parameter in a
> subquery I get an access violation
> The query below reproduces the problem using Northwind.
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
> [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
> It also happens when using OLEDB
> SELECT * FROM Orders
> WHERE CustomerID IN (SELECT customerID FROM Customers WHERE CompanyName => ?)
> Microsoft SQL Server 2000 - 8.00.857 (Intel X86) Sep 2 2003 18:55:32
> Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on
Windows
> NT 5.0 (Build 2195: Service Pack 4)
>|||The question mark represents and unnamed parameter.
I never use the @.param syntax for queries since Enterprise manager does
support them.
It is not in a stored procedure. It's being submitted from C# code but it
blows up just the same from Enterprise SQL Mangler
Seems to work ok with named parameters from Query Analyser.
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:uJgffxqPEHA.2128@.TK2MSFTNGP11.phx.gbl...
> What is question mark supposed to represent? Is this in a stored
procedure?
> Did you mean to use a named @.parameter?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "news.microsoft.com" <deo.is@.unknown.com> wrote in message
> news:OZMXwpqPEHA.3708@.TK2MSFTNGP10.phx.gbl...
> > Can anyone tell me if this is a known bug? When using a parameter in a
> > subquery I get an access violation
> > The query below reproduces the problem using Northwind.
> >
> > [Microsoft][ODBC SQL Server Driver]Syntax error or access violation
> > [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
> >
> > It also happens when using OLEDB
> >
> > SELECT * FROM Orders
> > WHERE CustomerID IN (SELECT customerID FROM Customers WHERE CompanyName
=> > ?)
> >
> > Microsoft SQL Server 2000 - 8.00.857 (Intel X86) Sep 2 2003 18:55:32
> > Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on
> Windows
> > NT 5.0 (Build 2195: Service Pack 4)
> >
> >
>|||> I never use the @.param syntax for queries since Enterprise manager does
> support them.
Why are you using Enterprise Manager for this?
> It is not in a stored procedure. It's being submitted from C# code but it
> blows up just the same from Enterprise SQL Mangler
If you're sending the code like that, why not fill in the parameter value in
C#?
> Seems to work ok with named parameters from Query Analyser.
As it should. Not all providers are going to understand the same funky
syntax that EM requires.|||I use the ? style parameter since the query wizards in Visual Studio don't
support the @.param style. The VS.NET wizards must use the same codebase as
EM since they mangle queries in much the same fashion :(
Thanks for the help
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:O61yeArPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> > I never use the @.param syntax for queries since Enterprise manager does
> > support them.
> Why are you using Enterprise Manager for this?
> > It is not in a stored procedure. It's being submitted from C# code but
it
> > blows up just the same from Enterprise SQL Mangler
> If you're sending the code like that, why not fill in the parameter value
in
> C#?
> > Seems to work ok with named parameters from Query Analyser.
> As it should. Not all providers are going to understand the same funky
> syntax that EM requires.
>|||"news.microsoft.com" wrote:
> I use the ? style parameter since the query wizards in Visual Studio don't
> support the @.param style. The VS.NET wizards must use the same codebase as
> EM since they mangle queries in much the same fashion :(
> Thanks for the help
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:O61yeArPEHA.3016@.TK2MSFTNGP10.phx.gbl...
> > > I never use the @.param syntax for queries since Enterprise manager does
> > > support them.
> >
> > Why are you using Enterprise Manager for this?
> >
> > > It is not in a stored procedure. It's being submitted from C# code but
> it
> > > blows up just the same from Enterprise SQL Mangler
> >
> > If you're sending the code like that, why not fill in the parameter value
> in
> > C#?
> >
> > > Seems to work ok with named parameters from Query Analyser.
> >
> > As it should. Not all providers are going to understand the same funky
> > syntax that EM requires.
> >
> >
>
>
Parameters in a VIEW
How do you go about creating a parameter in a view? In Access SQL, I know it is done like this:
parameter @.monthSelect as int;
SELECT tbl_xlat_beg.HCNumber, tbl_xlat_beg.LocationID, tbl_xlat_beg.BegValue, tbl_xlat_beg.Unit, tblbeg.use_id,
tbl_xlat_beg.Month_id
FROM tbl_xlat_beg
WHERE (((tbl_xlat_beg.Month) = [@.monthSelect]))
where monthSelect is requested. I'm trying to set up a group of queries to gather info but am not sure how to do this or for that matter test this in SQL. Any ideas would be great.Views cannot directly accept parameters. You may be able to achieve the same functionality with a user-defined function or a stored procedure, both of which are also more efficient than views.
blindman|||That's what the predicate is for...
I know in Access you can reference a control that doesn't exists
SELECT * FROM myTable WHERE myDate = [Please Enter a Date]
And you get a dialog...
You could just as easily have a control on a form the predicate references..
parameter @.monthSelect as int;
SELECT tbl_xlat_beg.HCNumber, tbl_xlat_beg.LocationID, tbl_xlat_beg.BegValue, tbl_xlat_beg.Unit, tblbeg.use_id,
tbl_xlat_beg.Month_id
FROM tbl_xlat_beg
WHERE (((tbl_xlat_beg.Month) = [@.monthSelect]))
where monthSelect is requested. I'm trying to set up a group of queries to gather info but am not sure how to do this or for that matter test this in SQL. Any ideas would be great.Views cannot directly accept parameters. You may be able to achieve the same functionality with a user-defined function or a stored procedure, both of which are also more efficient than views.
blindman|||That's what the predicate is for...
I know in Access you can reference a control that doesn't exists
SELECT * FROM myTable WHERE myDate = [Please Enter a Date]
And you get a dialog...
You could just as easily have a control on a form the predicate references..
Parameters in a VIEW
How do you go about creating a parameter in a view? In Access SQL, I know it is done like this:
parameter @.monthSelect as int;
SELECT tbl_xlat_beg.HCNumber, tbl_xlat_beg.LocationID, tbl_xlat_beg.BegValue, tbl_xlat_beg.Unit, tblbeg.use_id,
tbl_xlat_beg.Month_id
FROM tbl_xlat_beg
WHERE (((tbl_xlat_beg.Month) = [@.monthSelect]))
where monthSelect is requested. I'm trying to set up a group of queries to gather info but am not sure how to do this or for that matter test this in SQL. Any ideas would be great.You really shouldn't cross post
http://www.dbforums.com/t970215.html
parameter @.monthSelect as int;
SELECT tbl_xlat_beg.HCNumber, tbl_xlat_beg.LocationID, tbl_xlat_beg.BegValue, tbl_xlat_beg.Unit, tblbeg.use_id,
tbl_xlat_beg.Month_id
FROM tbl_xlat_beg
WHERE (((tbl_xlat_beg.Month) = [@.monthSelect]))
where monthSelect is requested. I'm trying to set up a group of queries to gather info but am not sure how to do this or for that matter test this in SQL. Any ideas would be great.You really shouldn't cross post
http://www.dbforums.com/t970215.html
Wednesday, March 7, 2012
Parameters collection
I have a function defined in Report->Report Properties-> Code Tab which
I want to access the parameters collection to look for a Debug flag.
I get the following error:
c:\Reports\ReportMockup.rdl There is an error on line 4 of custom code:
[BC30469] Reference to a non-shared member requires an object
reference.
How do I access the Parameters collection within my custom code?
report.Parameters!parameter
Thanks Tomson McCabe :)
Parameters collection
I have a function defined in Report->Report Properties-> Code Tab which
I want to access the parameters collection to look for a Debug flag.
I get the following error:
c:\Reports\ReportMockup.rdl There is an error on line 4 of custom code:
[BC30469] Reference to a non-shared member requires an object
reference.
How do I access the Parameters collection within my custom code?
report.Parameters!parameter
Thanks Tomson McCabe :)
Parameters collection
I have a function defined in Report->Report Properties-> Code Tab which
I want to access the parameters collection to look for a Debug flag.
I get the following error:
c:\Reports\ReportMockup.rdl There is an error on line 4 of custom code:
[BC30469] Reference to a non-shared member requires an object
reference.
How do I access the Parameters collection within my custom code?In your custom code, use:
Report.Parameters!parameter1.Value
"Paul H" wrote:
> I have a function defined in Report->Report Properties-> Code Tab which
> I want to access the parameters collection to look for a Debug flag.
> I get the following error:
> c:\Reports\ReportMockup.rdl There is an error on line 4 of custom code:
> [BC30469] Reference to a non-shared member requires an object
> reference.
> How do I access the Parameters collection within my custom code?
>
I want to access the parameters collection to look for a Debug flag.
I get the following error:
c:\Reports\ReportMockup.rdl There is an error on line 4 of custom code:
[BC30469] Reference to a non-shared member requires an object
reference.
How do I access the Parameters collection within my custom code?In your custom code, use:
Report.Parameters!parameter1.Value
"Paul H" wrote:
> I have a function defined in Report->Report Properties-> Code Tab which
> I want to access the parameters collection to look for a Debug flag.
> I get the following error:
> c:\Reports\ReportMockup.rdl There is an error on line 4 of custom code:
> [BC30469] Reference to a non-shared member requires an object
> reference.
> How do I access the Parameters collection within my custom code?
>
Saturday, February 25, 2012
Parameters
Hello everybody!
I have a question for you.
In access, you can make a query using parameters:
DELETE *
FROM tableName
WHERE myDate < [DATE]
[DATE] is a question asked to the user.
Can we do the same in SQL Store procedures?
I've tried the ? and %DATE% but it don't work.
Tanks all!
MarianneSQL Server runs on the server machine, so it cannot be made to pop up any
type of dialog on the client machine. I.e., you cannot have any type of user
interaction inside a stored procedure. Do this in the client application
instead and pass the value as a parameter to the stored procedure.
--
Tibor Karaszi
"Marianne Novello" <anonymous@.discussions.microsoft.com> wrote in message
news:077601c3a3aa$4dadf600$a001280a@.phx.gbl...
> Hello everybody!
> I have a question for you.
> In access, you can make a query using parameters:
> DELETE *
> FROM tableName
> WHERE myDate < [DATE]
> [DATE] is a question asked to the user.
> Can we do the same in SQL Store procedures?
> I've tried the ? and %DATE% but it don't work.
> Tanks all!
> Marianne
>|||Try:
CREATE PROCEDURE myproc
int @.myvariable
AS
SELECT @.myvariable
DELETE FROM mytable WHERE mytable.myfield = @.myvariable
GO
Take a look "CREATE PROCEDURE" in BOL.
Regards
---
All information provided above AS IS.
"Marianne Novello" <anonymous@.discussions.microsoft.com> wrote in message
news:077601c3a3aa$4dadf600$a001280a@.phx.gbl...
> Hello everybody!
> I have a question for you.
> In access, you can make a query using parameters:
> DELETE *
> FROM tableName
> WHERE myDate < [DATE]
> [DATE] is a question asked to the user.
> Can we do the same in SQL Store procedures?
> I've tried the ? and %DATE% but it don't work.
> Tanks all!
> Marianne
>|||Hello,
In SQL server you cant provide a parameter during run time. Instead you have
to execute the statement along with parameter value.
Sample:
declare @.date datetime
set @.date= getdate()
DELETE FROM tableName WHERE myDate < @.date
You can stored procedures also to do the similar stuff.
Thanks
Hari
US Technology
"SkyWalker" <tcp_43@.hotmail.com_TAKETHISOFF> wrote in message
news:erTWbu6oDHA.2964@.tk2msftngp13.phx.gbl...
> Try:
> CREATE PROCEDURE myproc
> int @.myvariable
> AS
> SELECT @.myvariable
> DELETE FROM mytable WHERE mytable.myfield = @.myvariable
> GO
> Take a look "CREATE PROCEDURE" in BOL.
> Regards
> ---
> All information provided above AS IS.
> "Marianne Novello" <anonymous@.discussions.microsoft.com> wrote in message
> news:077601c3a3aa$4dadf600$a001280a@.phx.gbl...
> > Hello everybody!
> > I have a question for you.
> >
> > In access, you can make a query using parameters:
> >
> > DELETE *
> > FROM tableName
> > WHERE myDate < [DATE]
> >
> > [DATE] is a question asked to the user.
> > Can we do the same in SQL Store procedures?
> > I've tried the ? and %DATE% but it don't work.
> >
> > Tanks all!
> > Marianne
> >
>
I have a question for you.
In access, you can make a query using parameters:
DELETE *
FROM tableName
WHERE myDate < [DATE]
[DATE] is a question asked to the user.
Can we do the same in SQL Store procedures?
I've tried the ? and %DATE% but it don't work.
Tanks all!
MarianneSQL Server runs on the server machine, so it cannot be made to pop up any
type of dialog on the client machine. I.e., you cannot have any type of user
interaction inside a stored procedure. Do this in the client application
instead and pass the value as a parameter to the stored procedure.
--
Tibor Karaszi
"Marianne Novello" <anonymous@.discussions.microsoft.com> wrote in message
news:077601c3a3aa$4dadf600$a001280a@.phx.gbl...
> Hello everybody!
> I have a question for you.
> In access, you can make a query using parameters:
> DELETE *
> FROM tableName
> WHERE myDate < [DATE]
> [DATE] is a question asked to the user.
> Can we do the same in SQL Store procedures?
> I've tried the ? and %DATE% but it don't work.
> Tanks all!
> Marianne
>|||Try:
CREATE PROCEDURE myproc
int @.myvariable
AS
SELECT @.myvariable
DELETE FROM mytable WHERE mytable.myfield = @.myvariable
GO
Take a look "CREATE PROCEDURE" in BOL.
Regards
---
All information provided above AS IS.
"Marianne Novello" <anonymous@.discussions.microsoft.com> wrote in message
news:077601c3a3aa$4dadf600$a001280a@.phx.gbl...
> Hello everybody!
> I have a question for you.
> In access, you can make a query using parameters:
> DELETE *
> FROM tableName
> WHERE myDate < [DATE]
> [DATE] is a question asked to the user.
> Can we do the same in SQL Store procedures?
> I've tried the ? and %DATE% but it don't work.
> Tanks all!
> Marianne
>|||Hello,
In SQL server you cant provide a parameter during run time. Instead you have
to execute the statement along with parameter value.
Sample:
declare @.date datetime
set @.date= getdate()
DELETE FROM tableName WHERE myDate < @.date
You can stored procedures also to do the similar stuff.
Thanks
Hari
US Technology
"SkyWalker" <tcp_43@.hotmail.com_TAKETHISOFF> wrote in message
news:erTWbu6oDHA.2964@.tk2msftngp13.phx.gbl...
> Try:
> CREATE PROCEDURE myproc
> int @.myvariable
> AS
> SELECT @.myvariable
> DELETE FROM mytable WHERE mytable.myfield = @.myvariable
> GO
> Take a look "CREATE PROCEDURE" in BOL.
> Regards
> ---
> All information provided above AS IS.
> "Marianne Novello" <anonymous@.discussions.microsoft.com> wrote in message
> news:077601c3a3aa$4dadf600$a001280a@.phx.gbl...
> > Hello everybody!
> > I have a question for you.
> >
> > In access, you can make a query using parameters:
> >
> > DELETE *
> > FROM tableName
> > WHERE myDate < [DATE]
> >
> > [DATE] is a question asked to the user.
> > Can we do the same in SQL Store procedures?
> > I've tried the ? and %DATE% but it don't work.
> >
> > Tanks all!
> > Marianne
> >
>
Monday, February 20, 2012
Parameterized queries - works in Access but not SQLS2k?
I have an application where users can enter data into any (or all) of 6 search fields,
to produce a filtered query.
This works fine using my Access version(see code below),
but as SQLS2k cannot use "IIF", I tried to replace these bits with
"CASE/WHEN/THEN/ELSE" lines, which does not work with numeric fields
as these cannot be "wild-carded" in the same way as Access allows.
Can anyone suggest a way forward that does not involve coding all the
possible permutations of "SELECT" blocks driven by lots of nested "IF/THEN/ELSE"s?
Hoping you can help
Alex
PARAMETERS
CurrentType Text,
CurrentCategoryID Long,
CurrentProductID Long,
CurrentClientID Long,
CurrentContractID Long,
FromDate DateTime,
ToDate DateTime;
SELECT
tAudit.AuditID,
tAudit.ActionType,
tAudit.ClientID,
tClients.ContactCompanyName,
tAudit.ContractID,
tContracts.ClientRef,
tAudit.ProductID,
tProducts.ProductName,
tAudit.CategoryID,
tCategories.CategoryName,
tAudit.Acknowledged,
tAudit.ValueAmount,
tAudit.DateStamp
FROM (((tAudit
LEFT JOIN tCategories
ON tAudit.CategoryID = tCategories.CategoryID)
LEFT JOIN tClients ON tAudit.ClientID = tClients.ClientID)
LEFT JOIN tContracts ON tAudit.ContractID = tContracts.ContractID)
LEFT JOIN tProducts ON tAudit.ProductID = tProducts.ProductID
WHERE (((tAudit.ActionType) Like IIf(IsNull([CurrentType]),"*",[CurrentType]))
AND ((tAudit.ClientID) Like IIf(IsNull([CurrentClientID]),"*",[CurrentClientID]))
AND ((tAudit.ContractID) Like IIf(IsNull([CurrentContractID]),"*",[CurrentContractID]))
AND ((tAudit.ProductID) Like IIf(IsNull([CurrentProductID]),"*",[CurrentProductID]))
AND ((tAudit.CategoryID) Like IIf(IsNull([CurrentCategoryID]),"*",[CurrentCategoryID]))
AND (([tAudit].[DateStamp]) Between [FromDate] And [ToDate]));(tAudit.ContractID) Like IIf(IsNull([CurrentContractID]),"*",[CurrentContractID])
Mdaaa...
tAudit.ContractID = isnull([CurrentContractID],tAudit.ContractID)
???|||Thanks Buser - works fine - much obliged to you!
Regards
Alex
to produce a filtered query.
This works fine using my Access version(see code below),
but as SQLS2k cannot use "IIF", I tried to replace these bits with
"CASE/WHEN/THEN/ELSE" lines, which does not work with numeric fields
as these cannot be "wild-carded" in the same way as Access allows.
Can anyone suggest a way forward that does not involve coding all the
possible permutations of "SELECT" blocks driven by lots of nested "IF/THEN/ELSE"s?
Hoping you can help
Alex
PARAMETERS
CurrentType Text,
CurrentCategoryID Long,
CurrentProductID Long,
CurrentClientID Long,
CurrentContractID Long,
FromDate DateTime,
ToDate DateTime;
SELECT
tAudit.AuditID,
tAudit.ActionType,
tAudit.ClientID,
tClients.ContactCompanyName,
tAudit.ContractID,
tContracts.ClientRef,
tAudit.ProductID,
tProducts.ProductName,
tAudit.CategoryID,
tCategories.CategoryName,
tAudit.Acknowledged,
tAudit.ValueAmount,
tAudit.DateStamp
FROM (((tAudit
LEFT JOIN tCategories
ON tAudit.CategoryID = tCategories.CategoryID)
LEFT JOIN tClients ON tAudit.ClientID = tClients.ClientID)
LEFT JOIN tContracts ON tAudit.ContractID = tContracts.ContractID)
LEFT JOIN tProducts ON tAudit.ProductID = tProducts.ProductID
WHERE (((tAudit.ActionType) Like IIf(IsNull([CurrentType]),"*",[CurrentType]))
AND ((tAudit.ClientID) Like IIf(IsNull([CurrentClientID]),"*",[CurrentClientID]))
AND ((tAudit.ContractID) Like IIf(IsNull([CurrentContractID]),"*",[CurrentContractID]))
AND ((tAudit.ProductID) Like IIf(IsNull([CurrentProductID]),"*",[CurrentProductID]))
AND ((tAudit.CategoryID) Like IIf(IsNull([CurrentCategoryID]),"*",[CurrentCategoryID]))
AND (([tAudit].[DateStamp]) Between [FromDate] And [ToDate]));(tAudit.ContractID) Like IIf(IsNull([CurrentContractID]),"*",[CurrentContractID])
Mdaaa...
tAudit.ContractID = isnull([CurrentContractID],tAudit.ContractID)
???|||Thanks Buser - works fine - much obliged to you!
Regards
Alex
parameterized pass-through queries from Access front-end?
Is there any easy way to pass (dynamically) parameters to pass-through
queries,
when working with MS Access as front-end for SQL Server ?
queries,
when working with MS Access as front-end for SQL Server ?
Thanks."Zlatko Mati" <zlatko.matic1@.sb.t-com.hr> wrote in message
news:d3ghk9$h7g$1@.ss405.t-com.hr...
> Is there any easy way to pass (dynamically) parameters to pass-through
> queries,
> when working with MS Access as front-end for SQL Server ?
> Thanks.
Your question seems to be about handling parameters in the front end, so you
might get a better answer in an Access group.
Simon
Subscribe to:
Posts (Atom)