Problem with SqlParameterCollection - Looking for advice

I have a site which works fine with an Access DB, I now want to upsize it to use a SQL DB... I have changed my OleDB Commands etc... And Used SqlCommands and everything seems fine..

Only one problem I have and its really stumped me... I always used SQLDataSource for the app even with the AccessDB, as I knew I would be upsizing at some point. We I have the below SQLDataSource

1 <asp:SqlDataSource ID="RandomBusinessDataSource" runat="server"
2 ConnectionString="<%$ ConnectionStrings:SQLDataBaseConnectionString%>"
3 SelectCommand="SELECT TOP (1) intBusinessID, txtBusinessName, intSubCatID, intCatID, txtTelNo, txtPostCode, txtWebAdd, txtReferred, bitBusinessShow, txtLong, txtLat, memBusinessDesc, intIPAddress, bitBusinessPaid FROM tblBusiness WHERE (intBusinessID = @.Param1)" OnSelecting="RandomBusinessDataSource_Selecting">
4 <SelectParameters>
5 <asp:Parameter Name="Param1" Type="Int32" />
6 </SelectParameters>
7 </asp:SqlDataSource>

Very Simple... I have my function that is creating my random number and returning a VALID intbusinessID ... And now I have this OnSelecting event

1 Protected Sub RandomBusinessDataSource_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs)
2 e.Command.Parameters("Param1").Value = MyNewRandomNum
3 End Sub

But when I try and run the page I get the following error (Don't forget this page IS working using the SQLDataSource and an Access DB??)

An SqlParameter with ParameterName 'Param1' is not contained by this SqlParameterCollection.

I am a bit confused?? why would it say this now... Yet it works fine if I just change the connectionString to the access DB?? Any helps very much appreciated... Thanks

Try changing "Param1" to "@.Param1" in the parameter declaration and in the code where you set the value.|||

Hi TGnat ... thanks for the help...

Unfortunately that gave the same error - Although I did manage get sort it, a chap I know at Cre8asites gave me the answer

( thread herehttp://www.cre8asiteforums.com/forums/index.php?showtopic=44722&hl= )

I had to use the following and it worked fine...

e.Command.Parameters(0).Value = MyNewRandomNum

|||Hey TGnat ... Actually you were pretty much right in the end - I printed out the parameter name and it was @.Param1 ... Although I just had the leave the selectparameter name as Param1 ... Weird hey!!... Thanks again

Problem with SQL string using MS Access and OleDbConnection (ASP .NET)

The string concatination below works in the Query builder built-in to MS Access, but when I try it as an OleDbCommand it doesn't work.

SELECT ID, LastName + ', ' + FirstName AS Names FROM AgentNames;

Is there any other ways to return a combination like this using an OleDbCommand?

Thanks,

GrierOriginally posted by grier_allen
The string concatination below works in the Query builder built-in to MS Access, but when I try it as an OleDbCommand it doesn't work.

SELECT ID, LastName + ', ' + FirstName AS Names FROM AgentNames;

Is there any other ways to return a combination like this using an OleDbCommand?

Thanks,

Grier

Shot in the dark here but try [LastName + ',' + FirstName] as Names. If not, I dont know why that won't work.

problem with SQL Server Migration Assistant for Access

I'm trying to upsize a client's Access 2003 database to SQL 2005.
I've been trying to use MS's SQL Server Migration Assistant for Access (which
seems to work better than the Access 2007 upsizing wizard), but I've got a few
errors that I'm stumped on.
Out of maybe 40 tables, all copied over and linked, except for issues with 2
tables.
One table showed erors when copying the data, none of the data made it.
Another table copied 95% of the rows, but failed on 5%.
The error log showed this for the table that completely failed:
--
Preparing table theDb.[Job_Budgets]...
Migrating data for the table Job_Budgets from the database theDb...
Error occurred during data migration.
See the log for the detailed information.
Errors: Error occurred during data migration.
See the log for the detailed information.
Data migration complete: 0 rows of 19394 migrated.
--
I've looked at the only .log files created during this time, and none give me
any useful information as to why the data failed to be copied.
There is a primary key, no duplicate records, etc.
How can I determine where the problem lies?
--
Thanks in advance, Les CaudleHello Les,
To isolate the issue, I suggest that you try to imort the table directly by
using Import wizard in management studio. If you still encounter issues,
please provide me with a sample Access database so that I could reproduce
on my side.
Please remove "online" from my displayed email address. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Peter - yes, that is giving me more info, and I'm able to resolve issues with
one table.
However, on a 2nd table, I'm getting this error msg:
Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB
error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult:
0x80004005 Description: "OLE DB provider 'STREAM' for linked server '(null)'
returned invalid data for column '[!BulkInsert].Dep1_%'. ".
(SQL Server Import and Export Wizard)
Dep1_% in Access 2003 (access 2000 db format) is a number, field size: double,
required = no, decimal places = 2.
So, somewhere in the table this field has some invalid values.
How can I find those and correct for them? Hard to write a query to locate
something invalid (which should never be there to begin with. You'd think
compact and repair would deal with those values).
Thanks, Les Caudle
On Fri, 26 Oct 2007 05:47:41 GMT, petery@.online.microsoft.com ("Peter
Yang[MSFT]") wrote:
>Hello Les,
>To isolate the issue, I suggest that you try to imort the table directly by
>using Import wizard in management studio. If you still encounter issues,
>please provide me with a sample Access database so that I could reproduce
>on my side.
>Please remove "online" from my displayed email address. Thank you.
>Best Regards,
>Peter Yang
>MCSE2000/2003, MCSA, MCDBA
>Microsoft Online Community Support
>==================================================>Get notification to my posts through email? Please refer to
>http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
>ications
><http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
>Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
>where an initial response from the community or a Microsoft Support
>Engineer within 1 business day is acceptable. Please note that each follow
>up response may take approximately 2 business days as the support
>professional working with you may need further investigation to reach the
>most efficient resolution. The offering is not appropriate for situations
>that require urgent, real-time or phone-based interactions or complex
>project analysis and dump analysis issues. Issues of this nature are best
>handled working with a dedicated Microsoft Support Engineer by contacting
>Microsoft Customer Support Services (CSS) at
><http://msdn.microsoft.com/subscriptions/support/default.aspx>.
>==================================================>This posting is provided "AS IS" with no warranties, and confers no rights.|||Less,
The problem is the name of the column. You cannot use % in a column
name.
Robert
On Oct 26, 11:00 am, Les Caudle <DotNetWann...@.newsgroup.nospam>
wrote:
> Peter - yes, that is giving me more info, and I'm able to resolve issues with
> one table.
> However, on a 2nd table, I'm getting this error msg:
> Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB
> error has occurred. Error code: 0x80004005.
> An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult:
> 0x80004005 Description: "OLE DB provider 'STREAM' for linked server '(null)'
> returned invalid data for column '[!BulkInsert].Dep1_%'. ".
> (SQL Server Import and Export Wizard)
> Dep1_% in Access 2003 (access 2000 db format) is a number, field size: double,
> required = no, decimal places = 2.
> So, somewhere in the table this field has some invalid values.
> How can I find those and correct for them? Hard to write a query to locate
> something invalid (which should never be there to begin with. You'd think
> compact and repair would deal with those values).
> Thanks, Les Caudle|||Robert - yes, very strange column name, but that actually was not the problem.
Apparently Access, even with compact and repair, will allow invalid values
(garbage) to to stored.
I cleared out the ones I could see by sorting the column asc desc by hand, and
resolved the problem.
One more reason to get my client away from storing his data in Access!
BUT - I wish there were an automated way to located and fix the invalid data in
his database. I found invalid data all over the place, but had to remove it all
by hand.
Regards, Les Caudle
On Fri, 26 Oct 2007 14:32:35 -0700, raibeart <raibeart@.gmail.com> wrote:
>Less,
>The problem is the name of the column. You cannot use % in a column
>name.
>Robert
>On Oct 26, 11:00 am, Les Caudle <DotNetWann...@.newsgroup.nospam>
>wrote:
>> Peter - yes, that is giving me more info, and I'm able to resolve issues with
>> one table.
>> However, on a 2nd table, I'm getting this error msg:
>> Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB
>> error has occurred. Error code: 0x80004005.
>> An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult:
>> 0x80004005 Description: "OLE DB provider 'STREAM' for linked server '(null)'
>> returned invalid data for column '[!BulkInsert].Dep1_%'. ".
>> (SQL Server Import and Export Wizard)
>> Dep1_% in Access 2003 (access 2000 db format) is a number, field size: double,
>> required = no, decimal places = 2.
>> So, somewhere in the table this field has some invalid values.
>> How can I find those and correct for them? Hard to write a query to locate
>> something invalid (which should never be there to begin with. You'd think
>> compact and repair would deal with those values).
>> Thanks, Les Caudle|||Hello Les,
I think there is no automated method way to clean invalid data currently.
Please rest assured that your feedback on this is routed to the product
team. Thank you.
Best Regards,
Peter Yang
Microsoft Online Community Support
Get Secure! - www.microsoft.com/security

Problem with SQL Server and odbc, please help me!

I have Visual Studio Dot.Net on Win XP and use the MSDE to access to Sql
Server.
This morning I've attempt to create a new ODBC DSN for Sql Server so
I've executed the wizard for a new System DSN by the ODBC administrator
of Windows. But when it attempts to connect to the server (the third
step of wizard) it give me somewhat like:
Connection impossible.
SQLState: '01000'
SQL Server error: 10049
SQLState: '08001'
SQL Server error: 17
I've tried to go forward with the manual configuration (uncheck the item
"Connect to an SQL Server for the default settings.." and press the Next
button), but at the end, when I press "Verify the data source", I have
no connection. So I've canceled the wizard.
The problem is that after that moment I have no connection with the server.
The console of MSDE indicate that the server is normally running, but I
can't connect to it neither by the Server Explorer of VS nor by osql.
I have always a message like:
[DBNETLIB][ConnectionOpen(Connect()).]Server non found or access denied.
Can someone help me?
Thanks
Tonio Tanzi
P.s. the messages of the server are probably a little different by the
ones indicate by me because I've translated them from the italian version.Tonio,
Is this a development server? If so try stopping the SQL Server service and
restarting it.
HTH
Jerry
"Tonio Tanzi" <vi.tan.gr@.inwind.it> wrote in message
news:m8c%e.3633$EZ.98724@.twister1.libero.it...
>I have Visual Studio Dot.Net on Win XP and use the MSDE to access to Sql
>Server.
> This morning I've attempt to create a new ODBC DSN for Sql Server so I've
> executed the wizard for a new System DSN by the ODBC administrator of
> Windows. But when it attempts to connect to the server (the third step of
> wizard) it give me somewhat like:
> Connection impossible.
> SQLState: '01000'
> SQL Server error: 10049
> SQLState: '08001'
> SQL Server error: 17
> I've tried to go forward with the manual configuration (uncheck the item
> "Connect to an SQL Server for the default settings.." and press the Next
> button), but at the end, when I press "Verify the data source", I have no
> connection. So I've canceled the wizard.
> The problem is that after that moment I have no connection with the
> server.
> The console of MSDE indicate that the server is normally running, but I
> can't connect to it neither by the Server Explorer of VS nor by osql.
> I have always a message like:
> [DBNETLIB][ConnectionOpen(Connect()).]Server non found or access denied.
>
> Can someone help me?
> Thanks
> Tonio Tanzi
> P.s. the messages of the server are probably a little different by the
> ones indicate by me because I've translated them from the italian version.|||Jerry Spivey ha scritto:
> Tonio,
> Is this a development server? If so try stopping the SQL Server service a
nd
> restarting it.
Yes. I've just stopped and restarted both Sql Server and computer many
times widhout any result.
Other suggest?
Bye
Tonio Tanzi

Problem with SQL Server And Access 2003

Hi, everyone.
I trying to connect a SQL Server 2005 to an Access 2003 frontend. Now
in the past I have not had any trouble, but today seems to be a
different day.
Every time I go to try and open a dao.database object I get the error
"Run-Time error '3000'': Reserved error(-1038); there is no message
for this error."
I am not really sure what the issue is here. Below is the line of code
Set dbSQLServer = DBEngine.OpenDatabase("", dbDriverNoPrompt, False,
gSQLServConnectionString)
Just so everything is clear gSQLServConnectionString =
ODBC;DSN=SQLServer;Trusted_Connection=ye
s;DATABASE=DicomSQLServer;
Now, I have tried a simlar connection string and it works just fine for
another server. When I test the connection in the ODBC manager it
connects to the server with a problem, but when I try to get access to
do it. I get the aforementioned error.
Thanks for all your help .
-ThanasYou may get an better response from:
microsoft.public.access.adp.sqlserver
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Thanas" <atsioplakis@.gmail.com> wrote in message
news:1156275843.206425.221240@.i42g2000cwa.googlegroups.com...
> Hi, everyone.
> I trying to connect a SQL Server 2005 to an Access 2003 frontend. Now
> in the past I have not had any trouble, but today seems to be a
> different day.
> Every time I go to try and open a dao.database object I get the error
> "Run-Time error '3000'': Reserved error(-1038); there is no message
> for this error."
> I am not really sure what the issue is here. Below is the line of code
> Set dbSQLServer = DBEngine.OpenDatabase("", dbDriverNoPrompt, False,
> gSQLServConnectionString)
> Just so everything is clear gSQLServConnectionString =
> ODBC;DSN=SQLServer;Trusted_Connection=ye
s;DATABASE=DicomSQLServer;
> Now, I have tried a simlar connection string and it works just fine for
> another server. When I test the connection in the ODBC manager it
> connects to the server with a problem, but when I try to get access to
> do it. I get the aforementioned error.
> Thanks for all your help .
> -Thanas
>|||Thanks I'll give it a try.
Arnie Rowland wrote:[vbcol=seagreen]
> You may get an better response from:
> microsoft.public.access.adp.sqlserver
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Thanas" <atsioplakis@.gmail.com> wrote in message
> news:1156275843.206425.221240@.i42g2000cwa.googlegroups.com...

Problem with SQL Server And Access 2003

Hi, everyone.
I trying to connect a SQL Server 2005 to an Access 2003 frontend. Now
in the past I have not had any trouble, but today seems to be a
different day.
Every time I go to try and open a dao.database object I get the error
"Run-Time error '3000'': Reserved error(-1038); there is no message
for this error."
I am not really sure what the issue is here. Below is the line of code
Set dbSQLServer = DBEngine.OpenDatabase("", dbDriverNoPrompt, False,
gSQLServConnectionString)
Just so everything is clear gSQLServConnectionString = ODBC;DSN=SQLServer;Trusted_Connection=yes;DATABASE=DicomSQLServer;
Now, I have tried a simlar connection string and it works just fine for
another server. When I test the connection in the ODBC manager it
connects to the server with a problem, but when I try to get access to
do it. I get the aforementioned error.
Thanks for all your help .
-ThanasYou may get an better response from:
microsoft.public.access.adp.sqlserver
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Thanas" <atsioplakis@.gmail.com> wrote in message
news:1156275843.206425.221240@.i42g2000cwa.googlegroups.com...
> Hi, everyone.
> I trying to connect a SQL Server 2005 to an Access 2003 frontend. Now
> in the past I have not had any trouble, but today seems to be a
> different day.
> Every time I go to try and open a dao.database object I get the error
> "Run-Time error '3000'': Reserved error(-1038); there is no message
> for this error."
> I am not really sure what the issue is here. Below is the line of code
> Set dbSQLServer = DBEngine.OpenDatabase("", dbDriverNoPrompt, False,
> gSQLServConnectionString)
> Just so everything is clear gSQLServConnectionString => ODBC;DSN=SQLServer;Trusted_Connection=yes;DATABASE=DicomSQLServer;
> Now, I have tried a simlar connection string and it works just fine for
> another server. When I test the connection in the ODBC manager it
> connects to the server with a problem, but when I try to get access to
> do it. I get the aforementioned error.
> Thanks for all your help .
> -Thanas
>|||Thanks I'll give it a try.
Arnie Rowland wrote:
> You may get an better response from:
> microsoft.public.access.adp.sqlserver
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "Thanas" <atsioplakis@.gmail.com> wrote in message
> news:1156275843.206425.221240@.i42g2000cwa.googlegroups.com...
> > Hi, everyone.
> > I trying to connect a SQL Server 2005 to an Access 2003 frontend. Now
> > in the past I have not had any trouble, but today seems to be a
> > different day.
> >
> > Every time I go to try and open a dao.database object I get the error
> > "Run-Time error '3000'': Reserved error(-1038); there is no message
> > for this error."
> >
> > I am not really sure what the issue is here. Below is the line of code
> >
> > Set dbSQLServer = DBEngine.OpenDatabase("", dbDriverNoPrompt, False,
> > gSQLServConnectionString)
> >
> > Just so everything is clear gSQLServConnectionString => > ODBC;DSN=SQLServer;Trusted_Connection=yes;DATABASE=DicomSQLServer;
> >
> > Now, I have tried a simlar connection string and it works just fine for
> > another server. When I test the connection in the ODBC manager it
> > connects to the server with a problem, but when I try to get access to
> > do it. I get the aforementioned error.
> >
> > Thanks for all your help .
> >
> > -Thanas
> >

Problem with SQL select with duplicate field names

I am converting a large application so that it can run with SQL server and
Access via ADO into Delphi.
There are many places where it does "select table1.*, table2.* etc"
With Access if there are duplicate field names (e.g. "description") they are
returned fully qualified and you can reference them as "table1.description"
and "table2.description".
I have just discovered to my horror that SQL server will not qualify the
names for you and returns these field names as "description" and
"description1".
Is there any way of altering this behaviour to return the fully qualified
field names as with Access ?
I know I should rewrite the queries to give aliases to the fields but there
maybe 1000's of places in the code and I want to avoid this.
Thanks for any helpyou must do it manually (and you always should in SQL)
Access protects the users from themselves. SQL doesnt protect you as much.
You can alias any field name with the "AS" key word:
SELECT MyField AS ThisName
Hope this helps
Greg Jackson
PDX, Oregon

Problem with SQL generated from MS Access

SELECT DISTINCT product.product_id AS Expr1
FROM [SELECT product.product_id, product.item_name, product.display,
product.selling_price, product.smallpicture, product.main_size,
product.main_dinnerware, dbo_coupon_special.coupon_id,
dbo_coupon_special.special_price, coupon.effective_date,
coupon.expiration_date, dbo_ssc_product.mc_id
FROM dbo_ssc_product LEFT JOIN ((product LEFT JOIN dbo_coupon_special
ON product.product_id = dbo_coupon_special.product_id) LEFT JOIN
coupon ON dbo_coupon_special.coupon_id = coupon.coupon_id) ON
dbo_ssc_product.product_id = product.product_id
GROUP BY product.product_id, product.item_name, product.display,
product.selling_price, product.smallpicture, product.main_size,
product.main_dinnerware, dbo_coupon_special.coupon_id,
dbo_coupon_special.special_price, coupon.effective_date,
coupon.expiration_date, dbo_ssc_product.mc_id
HAVING (((product.item_name) Like "*coaster*") AND
((product.display)="y"))]. AS [Alias]

This is the error I get:

The identifier that starts with 'SELECT product.product_id...' is too
long. Maximum lenght is 128

Incorrect syntax near the keyword 'AS'

Any ideas?

ThanksReplace [...]. with (...)
Replace " with '

SELECT DISTINCT product.product_id AS Expr1
FROM (SELECT product.product_id, product.item_name, product.display,
product.selling_price, product.smallpicture, product.main_size,
product.main_dinnerware, dbo_coupon_special.coupon_id,
dbo_coupon_special.special_price, coupon.effective_date,
coupon.expiration_date, dbo_ssc_product.mc_id
FROM dbo_ssc_product LEFT JOIN ((product LEFT JOIN dbo_coupon_special
ON product.product_id = dbo_coupon_special.product_id) LEFT JOIN
coupon ON dbo_coupon_special.coupon_id = coupon.coupon_id) ON
dbo_ssc_product.product_id = product.product_id
GROUP BY product.product_id, product.item_name, product.display,
product.selling_price, product.smallpicture, product.main_size,
product.main_dinnerware, dbo_coupon_special.coupon_id,
dbo_coupon_special.special_price, coupon.effective_date,
coupon.expiration_date, dbo_ssc_product.mc_id
HAVING (((product.item_name) Like '*coaster*') AND
((product.display)='y'))) AS [Alias]

--
David Portas
SQL Server MVP
--|||That worked great...

Thanks for the help

Problem with SQL from Access

SELECT DISTINCT product.product_id AS Expr1
FROM [SELECT product.product_id, product.item_name, product.display,
product.selling_price, product.smallpicture, product.main_size,
product.main_dinnerware, dbo_coupon_special.coupon_id,
dbo_coupon_special.special_price, coupon.effective_date,
coupon.expiration_date, dbo_ssc_product.mc_id
FROM dbo_ssc_product LEFT JOIN ((product LEFT JOIN dbo_coupon_special
ON product.product_id = dbo_coupon_special.product_id) LEFT JOIN
coupon ON dbo_coupon_special.coupon_id = coupon.coupon_id) ON
dbo_ssc_product.product_id = product.product_id
GROUP BY product.product_id, product.item_name, product.display,
product.selling_price, product.smallpicture, product.main_size,
product.main_dinnerware, dbo_coupon_special.coupon_id,
dbo_coupon_special.special_price, coupon.effective_date,
coupon.expiration_date, dbo_ssc_product.mc_id
HAVING (((product.item_name) Like "*coaster*") AND
((product.display)="y"))]. AS [Alias]
This is the error I get:
The identifier that starts with 'SELECT product.product_id...' is too
long. Maximum lenght is 128
Incorrect syntax near the keyword 'AS'
Any ideas?
Thanks
FROM [SELECT product.product...
...
((product.display)="y"))]. AS ...
Should be:
FROM (SELECT product.product...
...
((product.display)="y"))). AS ...
Parenthesis instead of square brackets. Apparently Access doesn't adhere to the ANSI SQL standard
for how you enclose a derived table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Allan" <proflicker@.hotmail.com> wrote in message
news:7b5b0602.0409271247.5a340b82@.posting.google.c om...
> SELECT DISTINCT product.product_id AS Expr1
> FROM [SELECT product.product_id, product.item_name, product.display,
> product.selling_price, product.smallpicture, product.main_size,
> product.main_dinnerware, dbo_coupon_special.coupon_id,
> dbo_coupon_special.special_price, coupon.effective_date,
> coupon.expiration_date, dbo_ssc_product.mc_id
> FROM dbo_ssc_product LEFT JOIN ((product LEFT JOIN dbo_coupon_special
> ON product.product_id = dbo_coupon_special.product_id) LEFT JOIN
> coupon ON dbo_coupon_special.coupon_id = coupon.coupon_id) ON
> dbo_ssc_product.product_id = product.product_id
> GROUP BY product.product_id, product.item_name, product.display,
> product.selling_price, product.smallpicture, product.main_size,
> product.main_dinnerware, dbo_coupon_special.coupon_id,
> dbo_coupon_special.special_price, coupon.effective_date,
> coupon.expiration_date, dbo_ssc_product.mc_id
> HAVING (((product.item_name) Like "*coaster*") AND
> ((product.display)="y"))]. AS [Alias]
> This is the error I get:
> The identifier that starts with 'SELECT product.product_id...' is too
> long. Maximum lenght is 128
> Incorrect syntax near the keyword 'AS'
>
> Any ideas?
> Thanks
|||Hi Tibor,
thanks for your help, you're right about the parenthesis and I also
found that you need to also remove the dot.
the code below ended up working:
FROM (SELECT product.product...
...
((product.display)="y"))) AS ...
without the dot.
Thanks again
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:<OCMZv4SpEHA.536@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
> FROM [SELECT product.product...
> ...
> ((product.display)="y"))]. AS ...
> Should be:
> FROM (SELECT product.product...
> ...
> ((product.display)="y"))). AS ...
> Parenthesis instead of square brackets. Apparently Access doesn't adhere to the ANSI SQL standard
> for how you enclose a derived table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Allan" <proflicker@.hotmail.com> wrote in message
> news:7b5b0602.0409271247.5a340b82@.posting.google.c om...
sql

Problem with SQL from Access

SELECT DISTINCT product.product_id AS Expr1
FROM [SELECT product.product_id, product.item_name, product.display,
product.selling_price, product.smallpicture, product.main_size,
product.main_dinnerware, dbo_coupon_special.coupon_id,
dbo_coupon_special.special_price, coupon.effective_date,
coupon.expiration_date, dbo_ssc_product.mc_id
FROM dbo_ssc_product LEFT JOIN ((product LEFT JOIN dbo_coupon_special
ON product.product_id = dbo_coupon_special.product_id) LEFT JOIN
coupon ON dbo_coupon_special.coupon_id = coupon.coupon_id) ON
dbo_ssc_product.product_id = product.product_id
GROUP BY product.product_id, product.item_name, product.display,
product.selling_price, product.smallpicture, product.main_size,
product.main_dinnerware, dbo_coupon_special.coupon_id,
dbo_coupon_special.special_price, coupon.effective_date,
coupon.expiration_date, dbo_ssc_product.mc_id
HAVING (((product.item_name) Like "*coaster*") AND
((product.display)="y"))]. AS [Alias]
This is the error I get:
The identifier that starts with 'SELECT product.product_id...' is too
long. Maximum lenght is 128
Incorrect syntax near the keyword 'AS'
Any ideas?
ThanksFROM [SELECT product.product...
...
((product.display)="y"))]. AS ...
Should be:
FROM (SELECT product.product...
...
((product.display)="y"))). AS ...
Parenthesis instead of square brackets. Apparently Access doesn't adhere to the ANSI SQL standard
for how you enclose a derived table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Allan" <proflicker@.hotmail.com> wrote in message
news:7b5b0602.0409271247.5a340b82@.posting.google.com...
> SELECT DISTINCT product.product_id AS Expr1
> FROM [SELECT product.product_id, product.item_name, product.display,
> product.selling_price, product.smallpicture, product.main_size,
> product.main_dinnerware, dbo_coupon_special.coupon_id,
> dbo_coupon_special.special_price, coupon.effective_date,
> coupon.expiration_date, dbo_ssc_product.mc_id
> FROM dbo_ssc_product LEFT JOIN ((product LEFT JOIN dbo_coupon_special
> ON product.product_id = dbo_coupon_special.product_id) LEFT JOIN
> coupon ON dbo_coupon_special.coupon_id = coupon.coupon_id) ON
> dbo_ssc_product.product_id = product.product_id
> GROUP BY product.product_id, product.item_name, product.display,
> product.selling_price, product.smallpicture, product.main_size,
> product.main_dinnerware, dbo_coupon_special.coupon_id,
> dbo_coupon_special.special_price, coupon.effective_date,
> coupon.expiration_date, dbo_ssc_product.mc_id
> HAVING (((product.item_name) Like "*coaster*") AND
> ((product.display)="y"))]. AS [Alias]
> This is the error I get:
> The identifier that starts with 'SELECT product.product_id...' is too
> long. Maximum lenght is 128
> Incorrect syntax near the keyword 'AS'
>
> Any ideas?
> Thanks|||Hi Tibor,
thanks for your help, you're right about the parenthesis and I also
found that you need to also remove the dot.
the code below ended up working:
FROM (SELECT product.product...
...
((product.display)="y"))) AS ...
without the dot.
Thanks again
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message news:<OCMZv4SpEHA.536@.TK2MSFTNGP11.phx.gbl>...
> FROM [SELECT product.product...
> ...
> ((product.display)="y"))]. AS ...
> Should be:
> FROM (SELECT product.product...
> ...
> ((product.display)="y"))). AS ...
> Parenthesis instead of square brackets. Apparently Access doesn't adhere to the ANSI SQL standard
> for how you enclose a derived table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Allan" <proflicker@.hotmail.com> wrote in message
> news:7b5b0602.0409271247.5a340b82@.posting.google.com...
> > SELECT DISTINCT product.product_id AS Expr1
> > FROM [SELECT product.product_id, product.item_name, product.display,
> > product.selling_price, product.smallpicture, product.main_size,
> > product.main_dinnerware, dbo_coupon_special.coupon_id,
> > dbo_coupon_special.special_price, coupon.effective_date,
> > coupon.expiration_date, dbo_ssc_product.mc_id
> > FROM dbo_ssc_product LEFT JOIN ((product LEFT JOIN dbo_coupon_special
> > ON product.product_id = dbo_coupon_special.product_id) LEFT JOIN
> > coupon ON dbo_coupon_special.coupon_id = coupon.coupon_id) ON
> > dbo_ssc_product.product_id = product.product_id
> > GROUP BY product.product_id, product.item_name, product.display,
> > product.selling_price, product.smallpicture, product.main_size,
> > product.main_dinnerware, dbo_coupon_special.coupon_id,
> > dbo_coupon_special.special_price, coupon.effective_date,
> > coupon.expiration_date, dbo_ssc_product.mc_id
> > HAVING (((product.item_name) Like "*coaster*") AND
> > ((product.display)="y"))]. AS [Alias]
> >
> > This is the error I get:
> >
> > The identifier that starts with 'SELECT product.product_id...' is too
> > long. Maximum lenght is 128
> >
> > Incorrect syntax near the keyword 'AS'
> >
> >
> >
> > Any ideas?
> >
> > Thanks

Problem with Search engine access?

Hi,
We're running a Windows 2000/2003 network.
On Servers running Windows 2003 with or without SP1 and SQL Server 2000 SP3a
or 4, we cannot create any full Text Search Catalogs.
Whatever login we try to use, if we try to create a new catalog, from
Enterprise Manager, we get a dialog box stating "The Microsoft Search Engine
cannot be administered under the present user account"
Running profiler we can see the creation process starting. It appears to run
alright until it gets to the "sp_fulltext_database DBCC CALLFULLTEXT
(7,@.dbid) -- FTDropAllCatalogs("@.dbid") and then generates an Exception
"Error: 7635, Severity: 16, State: 1"
Help!
John,
The error "The Microsoft Search Engine cannot be administered under the
present user account" typically indicates that the SQL login
BUILTIN\Administrator has been removed or altered. Have you or anyone else
removed or altered the SQL Server login BUILTIN\Administrator? If so, can
you add or alter it back with the same parameters? As the MSSearch service
needs all of the default parameters to login to SQL Server, if not you can
use the following SQL code:
exec sp_grantlogin N'NT Authority\System'
exec sp_defaultdb N'NT Authority\System', N'master'
exec sp_defaultlanguage N'NT Authority\System','us_english'
exec sp_addsrvrolemember N'NT Authority\System', sysadmin
See "SQL Server 2000 Full-Text Search Resources and Links" for more SQL FTS
info & KB articles at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"John S" <js162@.newsgroup.nospam> wrote in message
news:0C9748C6-AB94-4AC3-AEED-4F97C1FCC49B@.microsoft.com...
> Hi,
> We're running a Windows 2000/2003 network.
> On Servers running Windows 2003 with or without SP1 and SQL Server 2000
SP3a
> or 4, we cannot create any full Text Search Catalogs.
> Whatever login we try to use, if we try to create a new catalog, from
> Enterprise Manager, we get a dialog box stating "The Microsoft Search
Engine
> cannot be administered under the present user account"
> Running profiler we can see the creation process starting. It appears to
run
> alright until it gets to the "sp_fulltext_database DBCC CALLFULLTEXT
> (7,@.dbid) -- FTDropAllCatalogs("@.dbid") and then generates an Exception
> "Error: 7635, Severity: 16, State: 1"
> Help!

Problem with 'sa' user and password

Hello.
I have problem with connection to my SQL server 2000 through 'sa' user
without password.
On my windows XP with Access XP I created ODBC Source, user DSN source for
default user 'sa'. On SQL this user has not a password. Now, in Access, when
I try to open linked (from SQL Server) tables the dialog window appears and
system tells me to enter the password (which is empty). Then I must only
press Enter, and table is being opened. What should I do to avoid pressing
Enter, in other words, What should I do in order to get access to tables
without this appearing dialog window to enter the password?
Thank you very much for help.
PS. Without solution for this problem I can not run my batch jobs, and it is
very bad, and I'm getting nervous ;)
I am not sure that you can do this. The password, or in this case, the lack
of password, does not get stored in the DSN. So the dialog box has to open
so you cna supply it.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Problem with 'sa' user and password

Hello.
I have problem with connection to my SQL server 2000 through 'sa' user
without password.
On my Windows XP with Access XP I created ODBC Source, user DSN source for
default user 'sa'. On SQL this user has not a password. Now, in Access, when
I try to open linked (from SQL Server) tables the dialog window appears and
system tells me to enter the password (which is empty). Then I must only
press Enter, and table is being opened. What should I do to avoid pressing
Enter, in other words, What should I do in order to get access to tables
without this appearing dialog window to enter the password?
Thank you very much for help.
PS. Without solution for this problem I can not run my batch jobs, and it is
very bad, and I'm getting nervous ;)I am not sure that you can do this. The password, or in this case, the lack
of password, does not get stored in the DSN. So the dialog box has to open
so you cna supply it.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Problem with ReportBuilder

Hi,
I have this problem:
When I try to access to report builder in HTTP:
http://dwh.site.local/ReportServer/ReportBuilder/reportbuilder.application
all work fine.
When I try to access in HTTPS:
https://dwh.site.local/ReportServer/ReportBuilder/reportbuilder.application
In some client I have this error:
PLATFORM VERSION INFO
Windows : 5.1.2600.131072 (Win32NT)
Common Language Runtime : 2.0.50727.832
System.Deployment.dll : 2.0.50727.832 (QFE.050727-8300)
mscorwks.dll : 2.0.50727.832 (QFE.050727-8300)
dfdll.dll : 2.0.50727.42 (RTM.050727-4200)
dfshim.dll : 2.0.50727.42 (RTM.050727-4200)
SOURCES
Deployment url : https://dwh.site.local/ReportServer/ReportBuilder/reportbuilder.application
ERROR SUMMARY
Below is a summary of the errors, details of these errors are listed
later in the log.
* Activation of https://dwh.site.local/ReportServer/ReportBuilder/reportbuilder.application
resulted in exception. Following failure messages were detected:
+ Downloading https://dwh.site.local/ReportServer/ReportBuilder/reportbuilder.application
did not succeed.
+ The underlying connection was closed: Could not establish trust
relationship for the SSL/TLS secure channel.
+ The remote certificate is invalid according to the validation
procedure.
I have added this configuration in machine.config and web.config of
reportserver/reportmanager:
<system.net>
<settings>
<servicePointManager checkCertificateName="false"
checkCertificateRevocationList="false" />
</settings>
</system.net>
without success...
Help me... pleaseeeeeeeeeeee!
Bye!On Nov 16, 10:29 am, "public int N0UXXZ(){return 0};"
<nous...@.gmail.com> wrote:
> Hi,
> I have this problem:
> When I try to access to report builder in HTTP:http://dwh.site.local/ReportServer/ReportBuilder/reportbuilder.applic...
> all work fine.
> When I try to access in HTTPS:https://dwh.site.local/ReportServer/ReportBuilder/reportbuilder.appli...
> In some client I have this error:
> PLATFORM VERSION INFO
> Windows : 5.1.2600.131072 (Win32NT)
> Common Language Runtime : 2.0.50727.832
> System.Deployment.dll : 2.0.50727.832 (QFE.050727-8300)
> mscorwks.dll : 2.0.50727.832 (QFE.050727-8300)
> dfdll.dll : 2.0.50727.42 (RTM.050727-4200)
> dfshim.dll : 2.0.50727.42 (RTM.050727-4200)
> SOURCES
> Deployment url :https://dwh.site.local/ReportServer/ReportBuilder/reportbuilder.appli...
> ERROR SUMMARY
> Below is a summary of the errors, details of these errors are listed
> later in the log.
> * Activation ofhttps://dwh.site.local/ReportServer/ReportBuilder/reportbuilder.appli...
> resulted in exception. Following failure messages were detected:
> + Downloadinghttps://dwh.site.local/ReportServer/ReportBuilder/reportbuilder.appli...
> did not succeed.
> + The underlying connection was closed: Could not establish trust
> relationship for the SSL/TLS secure channel.
> + The remote certificate is invalid according to the validation
> procedure.
> I have added this configuration in machine.config and web.config of
> reportserver/reportmanager:
> <system.net>
> <settings>
> <servicePointManager checkCertificateName="false"
> checkCertificateRevocationList="false" />
> </settings>
> </system.net>
> without success...
> Help me... pleaseeeeeeeeeeee!
> Bye!
This might help.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=820552&SiteID=1
Regards,
Enrique Martinez
Sr. Software Consultant|||On 18 Nov, 01:38, EMartinez <emartinez...@.gmail.com> wrote:
> On Nov 16, 10:29 am, "public int N0UXXZ(){return 0};"
[...]
> This might help.http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=820552&SiteID=1
> Enrique Martinez
thank you very much! problem solved.|||On Nov 19, 11:51 am, "public int N0UXXZ(){return 0};"
<nous...@.gmail.com> wrote:
> On 18 Nov, 01:38, EMartinez <emartinez...@.gmail.com> wrote:
> > On Nov 16, 10:29 am, "public int N0UXXZ(){return 0};"
> [...]
> > This might help.http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=820552&SiteID=1
> > Enrique Martinez
> thank you very much! problem solved.
You're welcome. Let me know if I can be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant|||The information in that linked post is a little to vague for some of us who
are not up-to-date on the server side of life. Can anyone elaborate? I am
running into the same problem when I try to run Report Builder from one of
our internal web servers.
Thanks!
"EMartinez" wrote:
> On Nov 19, 11:51 am, "public int N0UXXZ(){return 0};"
> <nous...@.gmail.com> wrote:
> > On 18 Nov, 01:38, EMartinez <emartinez...@.gmail.com> wrote:
> >
> > > On Nov 16, 10:29 am, "public int N0UXXZ(){return 0};"
> > [...]
> > > This might help.http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=820552&SiteID=1
> > > Enrique Martinez
> >
> > thank you very much! problem solved.
>
> You're welcome. Let me know if I can be of further assistance.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||On 19 Nov, 23:40, sjauld <sja...@.discussions.microsoft.com> wrote:
> The information in that linked post is a little to vague for some of us who
> are not up-to-date on the server side of life. Can anyone elaborate? I am
> running into the same problem when I try to run Report Builder from one of
> our internal web servers.
> Thanks!
>
> "EMartinez" wrote:
> > On Nov 19, 11:51 am, "public int N0UXXZ(){return 0};"
> > <nous...@.gmail.com> wrote:
> > > On 18 Nov, 01:38, EMartinez <emartinez...@.gmail.com> wrote:
> > > > On Nov 16, 10:29 am, "public int N0UXXZ(){return 0};"
> > > [...]
> > > > This might help.http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=820552&SiteID=1
> > > > Enrique Martinez
> > > thank you very much! problem solved.
> > You're welcome. Let me know if I can be of further assistance.
> > Regards,
> > Enrique Martinez
> > Sr. Software Consultant- Nascondi testo tra virgolette -
> - Mostra testo tra virgolette -
Report Builder in HTTPS check the entire certificate tree.
If client not have the root certificate installed, Report Builder not
start.
If in IIS you have the certificate SSL that is not the root CERT, in
the web client Report Builder not start.

Problem with Report Pack for sharepoint's, rpdataextraction parser

hi,

when i try to execute the rpdataextraction parser, i am getting the following message - Unable to access site collection: User cannot be found.

it will be of immense help for me if any one helps me on this or directs me to right resource.

thanks

hello,

I have the same problem, so yesterday I found a possible solution for this problem, I'm trying to follow it:

There are a french blog for this issue: http://fmdain.blogspot.com/2006/06/reporting-pack-sps.pour-sql2k5.html.

The solution consist in:

1 - Verify that the tables DimDate and DimTime was complete filled. In my case the DimTime wasn't.

2 - an edition on procedures on database dbSPSReporting, to comment's lines : WHERE dd.DateFull and DATEADD, for all procedures on database.

I localized only those below:

PROCEDURE dbo.uspStorage_AreaGrowthTrend
PROCEDURE dbo.uspStorage_ListGrowthTrend
PROCEDURE dbo.uspStorage_SiteCollectionTrend
PROCEDURE dbo.uspStorage_SizeTrend
PROCEDURE dbo.usp_StartDateEndDate_Retrieve

If you use this solution and works, please send me a confirmation and any other comments about, or other solution.

Regards,

Ernani

Problem with Report Pack for sharepoint's, rpdataextraction parser

hi,

when i try to execute the rpdataextraction parser, i am getting the following message - Unable to access site collection: User cannot be found.

it will be of immense help for me if any one helps me on this or directs me to right resource.

thanks

hello,

I have the same problem, so yesterday I found a possible solution for this problem, I'm trying to follow it:

There are a french blog for this issue: http://fmdain.blogspot.com/2006/06/reporting-pack-sps.pour-sql2k5.html.

The solution consist in:

1 - Verify that the tables DimDate and DimTime was complete filled. In my case the DimTime wasn't.

2 - an edition on procedures on database dbSPSReporting, to comment's lines : WHERE dd.DateFull and DATEADD, for all procedures on database.

I localized only those below:

PROCEDURE dbo.uspStorage_AreaGrowthTrend
PROCEDURE dbo.uspStorage_ListGrowthTrend
PROCEDURE dbo.uspStorage_SiteCollectionTrend
PROCEDURE dbo.uspStorage_SizeTrend
PROCEDURE dbo.usp_StartDateEndDate_Retrieve

If you use this solution and works, please send me a confirmation and any other comments about, or other solution.

Regards,

Ernani

Problem with remote index server (no result)

Hello,
from SQL Server 2005 I access a index server an another computer (cp-
tkl)
select * from openquery(IDXNAME, 'select filename from "cp-
tkl".idxc..scope()')
When I use windows authentification it works and I have a result. But
when I use SQL Server I have no result (and no error message). I try
some user mapping from the SQL User to the domain user (a user with
that works when I use windows authentication) in the security window
for the linked server, but I found no solution.
It look likes a simple authentification problem. What can I do?
Joachim
Give the everyone group full access to this registry key
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Contro l\ContentIndex
"Joachim" <joachim.dehm@.gmx.de> wrote in message
news:1179465460.587412.54360@.n59g2000hsh.googlegro ups.com...
> Hello,
> from SQL Server 2005 I access a index server an another computer (cp-
> tkl)
> select * from openquery(IDXNAME, 'select filename from "cp-
> tkl".idxc..scope()')
> When I use windows authentification it works and I have a result. But
> when I use SQL Server I have no result (and no error message). I try
> some user mapping from the SQL User to the domain user (a user with
> that works when I use windows authentication) in the security window
> for the linked server, but I found no solution.
> It look likes a simple authentification problem. What can I do?
> Joachim
>

problem with re adding a login

I dropped a login from securities list. and when i try to re add that
login and give the user access to a particular database the error
message
"Error 15023: User or role "user" already exists in the current
database" .
upon enquiry i found out that the PUBLIC role in the databse still
contains that user in its list. I cant seem to remove this user from
the PUBLIC role. and so im not able to readd this user and give him
access to a particular databse... PLease help

Thank You in advancesailesh (sail_96@.yahoo.com) writes:
> I dropped a login from securities list. and when i try to re add that
> login and give the user access to a particular database the error
> message
> "Error 15023: User or role "user" already exists in the current
> database" .
> upon enquiry i found out that the PUBLIC role in the databse still
> contains that user in its list. I cant seem to remove this user from
> the PUBLIC role. and so im not able to readd this user and give him
> access to a particular databse... PLease help

Did you try sp_dropuser to get rid of him? sp_change_users_login is another
alternative, which permits you to do it without dropping the user.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Problem with Performance Monitor

Hi all,

I′m trying to start the Performance Monitor to collect data
of the counters of SQL Server:

- Access Methods
- Buffer Manager
- General Statistics
- Locks
- SQL Statistics

But when I perform the start, it occurs an error.
When I see the Events Log, the only information that
appears is a warning that indicates that the service could not start.

I′m using Windows Server 2003 with SQL Server 2000.

thanks!!!!Hi,

I forgot.
In the log event, the warnig indicates that the service could not add counters to log.

thanks