CASE statement

Hello,
I created a view in SQL Server Express 2005 that contained CASE END
statements.
I use this view in a .NET 2005 project, which works fine on my machine.
When I deploy my project to a machine that is running SQL Server 200, I get
the following error:
The Query Designer does not support the CASE SQL contruct.
Any way around this'
TIA,
Amber"amber" <amber@.discussions.microsoft.com> wrote in message
news:C5E9214E-628F-4CC3-8CC4-4DD7A5347999@.microsoft.com...
> Hello,
> I created a view in SQL Server Express 2005 that contained CASE END
> statements.
> I use this view in a .NET 2005 project, which works fine on my machine.
> When I deploy my project to a machine that is running SQL Server 200, I
> get
> the following error:
> The Query Designer does not support the CASE SQL contruct.
> Any way around this'
> TIA,
> Amber
>
If you could include the CASE statement that you used, and any other
relevant DDL we could be more helpful.
Rick Sawtell|||Which SQL 200x? :) 2000 or 2005?
The way around this: don't use the Query Designer. Script it and run the
code in Query Analyzer.
amber wrote:
> Hello,
> I created a view in SQL Server Express 2005 that contained CASE END
> statements.
> I use this view in a .NET 2005 project, which works fine on my machine.
> When I deploy my project to a machine that is running SQL Server 200, I ge
t
> the following error:
> The Query Designer does not support the CASE SQL contruct.
> Any way around this'
> TIA,
> Amber
>|||"amber" wrote:
> The Query Designer does not support the CASE SQL contruct.
> Any way around this'
> TIA,
> Amber
>
Yes. Learn to write SQL instead of using the Query Designer. The designer is
a crutch that will do you know favours in the long run. If you want to use
anything more than very basic stuff then you must avoid it.
David Portas
SQL Server MVP
--

case sensitive SQL - pls help a noob

I just created my first Asp.net app. I had to install it to a corporate server. What I found is that the corporate SQL Server 2000 was case sensitive in the stored procedures while my installation was not!
How can I set my SQL Server 2000 to be case sensitive as well?Case sensitivity depends on the codepage you select at install.
If you want to do it afterwards you need to rebuild the master table.
See 'Rebuilding the master database' in the SQL Server
books online.

Regards
Fredr!k

Case Insensitivity is on server wide: tables render case sensative...

Hello:

I have created an SQL server table in the past on a server that was all case sensative. Over time I found out that switching to a server that is not case sensative still caused my data to become case sensative. I read an article that said you should rebuild your master database then re-create your tables. So after rebuilding the master database, a basic restore would not be sufficient? I would have to go and manually re-create every single table again?

Any suggestions?Have a look at "Using SQL Collations" in SQL Server Books Online|||Hello:

I went ahead and took a look inside books online and pretty much found things that I already know. My current collation is: SQL_Latin1_General_CP1_CI_AI.. So the server is actually CaSe insensative... But some of my tables still act like case sensitivity is turned on in most areas where I have not done anything on my part to cause this behavior. So I am guessing that since the tables were moved from a CS_AS to a CI_AI, the tables caused were rendered case sensative although it is not in a CS_AS enviornment... My question is that would I need to rebuild the database manually from scratch or is their an easier way to do this? Thank you.|||If the AS collations appear only on a few tables you can alter those tables only - something like:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_819v.asp|||Hello:

Yes I followed that link it was more helpful... looks like a daunting manual task.. thank you

Cascading parameters

Hi,
I have created a dataset say "DSMasterList" which has the list of items say
product list and also I have another dataset say "DSDetails" which will have
details of the products for a given productID.
My requirement is to have a report that will print ALL the products from
"DSMasterList" and its appropirate details using "DSDetails".
I tried using cascading parameters but then user will have a select the
product and then will create the product detail report for the selected
product. Is there any way where it will show the product details for all the
products available in the "DSMasterList" dataset without user selecting them.
Thanks & Regards,
RajCreate two independent reports and embed one as a subreport inside the other,
like this:
Create a report with the product details. Use productid as parameter.
Create a report with the master product list. You can use list or table data
region, because they can contain other data regions. Drag a subreport control
inside the list or data region. Go to properties of the subreport and specify
the report which has your product details. You also specify the link
parameter here, so that the product details appear before the correct product.
HTH
Charles Kangai, MCT, MCDBA
"Raj" wrote:
> Hi,
> I have created a dataset say "DSMasterList" which has the list of items say
> product list and also I have another dataset say "DSDetails" which will have
> details of the products for a given productID.
> My requirement is to have a report that will print ALL the products from
> "DSMasterList" and its appropirate details using "DSDetails".
> I tried using cascading parameters but then user will have a select the
> product and then will create the product detail report for the selected
> product. Is there any way where it will show the product details for all the
> products available in the "DSMasterList" dataset without user selecting them.
> Thanks & Regards,
> Raj|||Thanks for the reponse Charles.
I tired using the subreports, but it seems there is some issue with
exporting such reports to excel. It give a error saying "Subreports with
table/matrix cells are ignored." Any Ideas on how to handle this error?
Best Regards,
Rajiv
"Charles Kangai" wrote:
> Create two independent reports and embed one as a subreport inside the other,
> like this:
> Create a report with the product details. Use productid as parameter.
> Create a report with the master product list. You can use list or table data
> region, because they can contain other data regions. Drag a subreport control
> inside the list or data region. Go to properties of the subreport and specify
> the report which has your product details. You also specify the link
> parameter here, so that the product details appear before the correct product.
> HTH
> Charles Kangai, MCT, MCDBA
> "Raj" wrote:
> > Hi,
> >
> > I have created a dataset say "DSMasterList" which has the list of items say
> > product list and also I have another dataset say "DSDetails" which will have
> > details of the products for a given productID.
> >
> > My requirement is to have a report that will print ALL the products from
> > "DSMasterList" and its appropirate details using "DSDetails".
> >
> > I tried using cascading parameters but then user will have a select the
> > product and then will create the product detail report for the selected
> > product. Is there any way where it will show the product details for all the
> > products available in the "DSMasterList" dataset without user selecting them.
> >
> > Thanks & Regards,
> > Raj|||Raj,
I have a report that has salesperson data and underneath in a subreport the
sales details of the salesperson are listed. I even have picture of the
salesperson in the main part. I have used a subreport control inside a list
data region. And it is working. After reading your post I tried exporting it
to Excel, and it exported OK. there may be an issue I am not aware of if you
had a table inside a table. Mine was a table inside a list.
There is another option that also works. You can create master/detail report
by writing one join query to include both the master rows and the detail
rows. You can use a table data region and group by the fields from the master
record and show the detail rows underneath each one. But the first option I
showed you should work, as far as I know.
Cheers!
Charles Kangai, MCT, MCDBA
"Raj" wrote:
> Thanks for the reponse Charles.
> I tired using the subreports, but it seems there is some issue with
> exporting such reports to excel. It give a error saying "Subreports with
> table/matrix cells are ignored." Any Ideas on how to handle this error?
> Best Regards,
> Rajiv
>
> "Charles Kangai" wrote:
> > Create two independent reports and embed one as a subreport inside the other,
> > like this:
> >
> > Create a report with the product details. Use productid as parameter.
> >
> > Create a report with the master product list. You can use list or table data
> > region, because they can contain other data regions. Drag a subreport control
> > inside the list or data region. Go to properties of the subreport and specify
> > the report which has your product details. You also specify the link
> > parameter here, so that the product details appear before the correct product.
> >
> > HTH
> >
> > Charles Kangai, MCT, MCDBA
> >
> > "Raj" wrote:
> >
> > > Hi,
> > >
> > > I have created a dataset say "DSMasterList" which has the list of items say
> > > product list and also I have another dataset say "DSDetails" which will have
> > > details of the products for a given productID.
> > >
> > > My requirement is to have a report that will print ALL the products from
> > > "DSMasterList" and its appropirate details using "DSDetails".
> > >
> > > I tried using cascading parameters but then user will have a select the
> > > product and then will create the product detail report for the selected
> > > product. Is there any way where it will show the product details for all the
> > > products available in the "DSMasterList" dataset without user selecting them.
> > >
> > > Thanks & Regards,
> > > Raj

Carriage Returns

Hi,
I have an access database that has been created by the export utility
from Outlook contacts. In outlook contacts there is a free text field
where you can enter paragraphs, bold text, etc. Now this field in
Access is there without the styles but the carriage returns still
exist. My problem is when importing this access table to sql server, it
removes all this carriage returns and puts a blank instead.
Any ideas?
Thanks in advance,
Shahid<shahid.juma@.gmail.com> wrote in message
news:1121110803.051125.153310@.o13g2000cwo.googlegroups.com...
> Hi,
> I have an access database that has been created by the export utility
> from Outlook contacts. In outlook contacts there is a free text field
> where you can enter paragraphs, bold text, etc. Now this field in
> Access is there without the styles but the carriage returns still
> exist. My problem is when importing this access table to sql server, it
> removes all this carriage returns and puts a blank instead.
If this is a one-time shot, write a script that replaces the CR/LF in all
the memo fields with some other textual. Once imported in SQL, replace it
with CR/LF again.
This is just a work-around since I neither know the explanation and nor a
"real" solution ;-)
Christoph

Carriage Returns

Hi,
I have an access database that has been created by the export utility
from Outlook contacts. In outlook contacts there is a free text field
where you can enter paragraphs, bold text, etc. Now this field in
Access is there without the styles but the carriage returns still
exist. My problem is when importing this access table to sql server, it
removes all this carriage returns and puts a blank instead.
Any ideas?
Thanks in advance,
Shahid
<shahid.juma@.gmail.com> wrote in message
news:1121110803.051125.153310@.o13g2000cwo.googlegr oups.com...
> Hi,
> I have an access database that has been created by the export utility
> from Outlook contacts. In outlook contacts there is a free text field
> where you can enter paragraphs, bold text, etc. Now this field in
> Access is there without the styles but the carriage returns still
> exist. My problem is when importing this access table to sql server, it
> removes all this carriage returns and puts a blank instead.
If this is a one-time shot, write a script that replaces the CR/LF in all
the memo fields with some other textual. Once imported in SQL, replace it
with CR/LF again.
This is just a work-around since I neither know the explanation and nor a
"real" solution ;-)
Christoph

Carriage Returns

Hi,
I have an access database that has been created by the export utility
from Outlook contacts. In outlook contacts there is a free text field
where you can enter paragraphs, bold text, etc. Now this field in
Access is there without the styles but the carriage returns still
exist. My problem is when importing this access table to sql server, it
removes all this carriage returns and puts a blank instead.
Any ideas?
Thanks in advance,
Shahid<shahid.juma@.gmail.com> wrote in message
news:1121110803.051125.153310@.o13g2000cwo.googlegroups.com...
> Hi,
> I have an access database that has been created by the export utility
> from Outlook contacts. In outlook contacts there is a free text field
> where you can enter paragraphs, bold text, etc. Now this field in
> Access is there without the styles but the carriage returns still
> exist. My problem is when importing this access table to sql server, it
> removes all this carriage returns and puts a blank instead.
If this is a one-time shot, write a script that replaces the CR/LF in all
the memo fields with some other textual. Once imported in SQL, replace it
with CR/LF again.
This is just a work-around since I neither know the explanation and nor a
"real" solution ;-)
Christoph

Capture name of stored procedure within itself?

Is there any way of capturing the name of a stored procedure during
execution?
I've created a generic stored procedure to handle much of my error
handling. I would like to be able to have the calling stored
procedure to pass its name as one of the parameters to the error
handling proc to be logged on certain error messages events.
I could easily hard code the stored procedure name on all of the
calls, but I am trying to create a series of generic code blocks to
add to the stored procedures (one for simple SELECTS, UPDATE, INSERTS,
ETC), so I was hoping to find a function or create a function that
would give the name of the proc.
Any thoughts would be appreciated.
SELECT OBJECT_NAME(@.@.PROCID) AS ProcName
"Sean O'Thule" <othule@.hotmail.com> wrote in message
news:63840202.0408300912.2d9c1f2c@.posting.google.c om...
> Is there any way of capturing the name of a stored procedure during
> execution?
> I've created a generic stored procedure to handle much of my error
> handling. I would like to be able to have the calling stored
> procedure to pass its name as one of the parameters to the error
> handling proc to be logged on certain error messages events.
> I could easily hard code the stored procedure name on all of the
> calls, but I am trying to create a series of generic code blocks to
> add to the stored procedures (one for simple SELECTS, UPDATE, INSERTS,
> ETC), so I was hoping to find a function or create a function that
> would give the name of the proc.
> Any thoughts would be appreciated.
|||Great, thanks

Capture name of stored procedure within itself?

Is there any way of capturing the name of a stored procedure during
execution?
I've created a generic stored procedure to handle much of my error
handling. I would like to be able to have the calling stored
procedure to pass its name as one of the parameters to the error
handling proc to be logged on certain error messages events.
I could easily hard code the stored procedure name on all of the
calls, but I am trying to create a series of generic code blocks to
add to the stored procedures (one for simple SELECTS, UPDATE, INSERTS,
ETC), so I was hoping to find a function or create a function that
would give the name of the proc.
Any thoughts would be appreciated.SELECT OBJECT_NAME(@.@.PROCID) AS ProcName
"Sean O'Thule" <othule@.hotmail.com> wrote in message
news:63840202.0408300912.2d9c1f2c@.posting.google.com...
> Is there any way of capturing the name of a stored procedure during
> execution?
> I've created a generic stored procedure to handle much of my error
> handling. I would like to be able to have the calling stored
> procedure to pass its name as one of the parameters to the error
> handling proc to be logged on certain error messages events.
> I could easily hard code the stored procedure name on all of the
> calls, but I am trying to create a series of generic code blocks to
> add to the stored procedures (one for simple SELECTS, UPDATE, INSERTS,
> ETC), so I was hoping to find a function or create a function that
> would give the name of the proc.
> Any thoughts would be appreciated.|||Great, thanks

Capture name of stored procedure within itself?

Is there any way of capturing the name of a stored procedure during
execution?
I've created a generic stored procedure to handle much of my error
handling. I would like to be able to have the calling stored
procedure to pass its name as one of the parameters to the error
handling proc to be logged on certain error messages events.
I could easily hard code the stored procedure name on all of the
calls, but I am trying to create a series of generic code blocks to
add to the stored procedures (one for simple SELECTS, UPDATE, INSERTS,
ETC), so I was hoping to find a function or create a function that
would give the name of the proc.
Any thoughts would be appreciated.SELECT OBJECT_NAME(@.@.PROCID) AS ProcName
"Sean O'Thule" <othule@.hotmail.com> wrote in message
news:63840202.0408300912.2d9c1f2c@.posting.google.com...
> Is there any way of capturing the name of a stored procedure during
> execution?
> I've created a generic stored procedure to handle much of my error
> handling. I would like to be able to have the calling stored
> procedure to pass its name as one of the parameters to the error
> handling proc to be logged on certain error messages events.
> I could easily hard code the stored procedure name on all of the
> calls, but I am trying to create a series of generic code blocks to
> add to the stored procedures (one for simple SELECTS, UPDATE, INSERTS,
> ETC), so I was hoping to find a function or create a function that
> would give the name of the proc.
> Any thoughts would be appreciated.|||Great, thanks

Cant use stored procedures with a sqldatasource

Hi all, I've been trying to use the stored procedures created by asp (i.e. aspnet_membership_createuser)... but they don't show up when trying to use them in a sqldatasource. Are they blocked or am I doing something wrong?

Thanks in advance.The membership tables and stored procedures will be in the ASPNETDB database by default. This is the database you'll need to log into if you want to do this.

Having said that, I doubt it's particularly good practice to be calling these stored procedures directly. You'd be better off looking into using a CreateUserWizard control or adding an event to your page and using the Membership API.|||They are hidden from you, yes. You can still type them in if you want to, and they should still work.|||Thanks for the answers :), I''m going to try that, and I'll let you know how it goes...|||The wizards automatically hides any object that begins with "aspnet" (Ok, actually it hides tables that begin with aspnet, stored procedures that begin with aspnet, and views that begin with vw_aspnet). I think it was for convience so that beginners didn't get confused and try to access them directly. Moderate/Average users don't want them popping up because 99%+ of the time you don't want one of those and they just get in the way. Advanced users will figure out they can still use them if they need to, just don't use the wizard ;-)

can't update view

Hi all ,

I have a problem to insert new data to a view which created from 2 tables.

I am trying to so from run time

Any help will be appreciated

use an instead of INsert trigger for that.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Itamarqza, did the Instead Of resolve your issue? If not could you please provide more information.

Thanks,

Derek