data with apostrophe in it

How do I add data that has an apostrophe in it?
For example,
update logon set question='Mother's maiden name'
won't work as there is an apostrophe in it. I need to be able to allow
apostrophes in some of my fields.
Thanks,
TomDelimit with a second apostrophe:
update logon set question='Mother''s maiden name'
If you're passing this in through a client, then use a parameter, which you
assign the string to. That will handle the apostrophes for you.|||Hi tshad
for this u need to use 2 single apostrophies
update logon set question='Mother''s maiden name'
best Regards,
Chandra
---
"tshad" wrote:

> How do I add data that has an apostrophe in it?
> For example,
> update logon set question='Mother's maiden name'
> won't work as there is an apostrophe in it. I need to be able to allow
> apostrophes in some of my fields.
> Thanks,
> Tom
>
>|||Hi tshad,
If you are passing eg. via a client VB, for those parameters, parse with a r
eplace string function 1st
Example:
Dim sSQL as string
Dim sParam as string
sParam = replace(Mother's maiden name, '"'", "''") -- note: the
bold are double quotes while unbold are single quote
sSQL = "update logon set question='" & sParam & "'" -- note: the bold ar
e double quotes while unbold are single quote
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message news:23556560-7C78-481D-A84F
-C59DC9C0F8CB@.microsoft.com...
> Hi tshad
> for this u need to use 2 single apostrophies
>
> update logon set question='Mother''s maiden name'
>
>
> --
> best Regards,
> Chandra
> ---
>
>
>
> "tshad" wrote:
>|||... or use a command object with parameter objects. That will take care of
it for you.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kriste L" <whiteegg@.hotmail.com> wrote in message news:e24XPHFVFHA.628@.tk2m
sftngp13.phx.gbl...
Hi tshad,
If you are passing eg. via a client VB, for those parameters, parse with a r
eplace string function
1st
Example:
Dim sSQL as string
Dim sParam as string
sParam = replace(Mother's maiden name, '"'", "''") -- note: the
bold are double quotes
while unbold are single quote
sSQL = "update logon set question='" & sParam & "'" -- note: the bold ar
e double quotes while
unbold are single quote
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:23556560-7C78-481D-A84F-C59DC9C0F8CB@.microsoft.com...
> Hi tshad
> for this u need to use 2 single apostrophies
> update logon set question='Mother''s maiden name'
>
> --
> best Regards,
> Chandra
> ---
>
> "tshad" wrote:
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e4GFjKHVFHA.3544@.TK2MSFTNGP12.phx.gbl...
> ... or use a command object with parameter objects. That will take care of
> it for you.
It did.
Thanks,
Tom

> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Kriste L" <whiteegg@.hotmail.com> wrote in message
> news:e24XPHFVFHA.628@.tk2msftngp13.phx.gbl...
> Hi tshad,
> If you are passing eg. via a client VB, for those parameters, parse with a
> replace string function 1st
> Example:
> Dim sSQL as string
> Dim sParam as string
> sParam = replace(Mother's maiden name, '"'", "''") -- note:
> the bold are double quotes while unbold are single quote
> sSQL = "update logon set question='" & sParam & "'" -- note: the bold
> are double quotes while unbold are single quote
>
>
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:23556560-7C78-481D-A84F-C59DC9C0F8CB@.microsoft.com...
>|||"Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
news:82BCBE73-3EF2-4A48-AF62-0D296EAB0C9D@.microsoft.com...
> Delimit with a second apostrophe:
> update logon set question='Mother''s maiden name'
>
I assume I would do a replace on all my strings that a user would put
apostrophes, first.
Tom

> If you're passing this in through a client, then use a parameter, which
> you
> assign the string to. That will handle the apostrophes for you.
I would use both.
Thanks,
Tom

Data Warehousing Learning Sample

Greetings,
I'm new with data warehousing. Just wondering, do any of you know of a
free sample/example that would help me learn the ABC's of data
warehousing?
The SQL 2000 sample is only available with SQL Enterprise..
Thanks in advance,
Don
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
The Analysis Services installation comes with a sample database called Food
Mart 2000 which is an OLAP database that sits on top of an .mdb file. Is
this the one that you have already investigated?
Jay Nathan, MCP
http://www.jaynathan.com/blog
"don larry" <donlarry17@.hotmail.com> wrote in message
news:O4t01EM3EHA.3932@.TK2MSFTNGP12.phx.gbl...
> Greetings,
> I'm new with data warehousing. Just wondering, do any of you know of a
> free sample/example that would help me learn the ABC's of data
> warehousing?
> The SQL 2000 sample is only available with SQL Enterprise..
> Thanks in advance,
> Don
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Greetings,
Hi Jay, it is a great sample. Just what I was looking for.
Thank you,
Don
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Data Types (char, varchar, nchar, nvarchar, ...)

Could someone please help me by explaining which one is best to use and when? For example, storing the word "Corona Del Mar" - which Data Type would be suggested?

Thanks.

I would suggest Varchar(#).

ntype data types support Unicode (non-standard characters). So unless you're expecting complex input (or different languages) char, varchar, and text are what you'll typically be using.

char = fixed length string. If you create a char(10) field and enter "test" the field will actually stored "test" PLUS 6 blank spaces (4 characters in test + 6 blank spaces = 10). So when you output your field, you'll actually get "test ".

varchar = variable length string. This is the bread and butter of most databases. If you create varchar(10) and enter "test", it will store test. The limitation to this field is that it can only store up to 8,000 characters.

text = unlimited text field. Downsides: Text fields are stored OUTSIDE the database record (only a pointer is stored inside the record) so retrieving text fields is slower then retrieving a varchar record. It's also not compatible with some database commands (group etc.)

|||

Chris Pebble:

I would suggest Varchar(#).

ntype data types support Unicode (non-standard characters). So unless you're expecting complex input (or different languages) char, varchar, and text are what you'll typically be using.

char = fixed length string. If you create a char(10) field and enter "test" the field will actually stored "test" PLUS 6 blank spaces (4 characters in test + 6 blank spaces = 10). So when you output your field, you'll actually get "test ".

varchar = variable length string. This is the bread and butter of most databases. If you create varchar(10) and enter "test", it will store test. The limitation to this field is that it can only store up to 8,000 characters.

text = unlimited text field. Downsides: Text fields are stored OUTSIDE the database record (only a pointer is stored inside the record) so retrieving text fields is slower then retrieving a varchar record. It's also not compatible with some database commands (group etc.)

Thank you, that was a very good explaination.

|||

I have a list box that has a few different values "information a, information b, information c" and when any or all are chosen they are stored in my database in one column. What data type would you suggest for this? Thanks.

Data type validation

Hi all,

I want to make a conditional split based on the data type provided by the input.

For example : If the comming (Column x) is of data type (numeric) then pass , else do not pass.

(pass = Case 1

Do not pass = Case 2).

Is there any way for doing so ?

You can use conditional split transformation for this.

The following link will help you get started
http://technet.microsoft.com/en-us/library/ms137886.aspx

Thanks

|||

I think Data Convertion transform is the easiest way to do this, see this for details:

http://technet.microsoft.com/en-us/library/ms186792.aspx

You would read the data as string, then try to convert it to numeric. Configure Error Disposition to redirect row. The "pass" will go to default (green) output, the rows that do not pass will go to error output.

Data Type Problem with VB and CSharp...I believe

I am attempting to use VB.Net to interface to a CSharp DLL to print reports.
I have taken the CSharp Example that I have seen reported in this newsgroup
and made it into a DLL. I am trying to use the Reporting Services report
parameter and getting the following error in VB.Net:
TestApp.vb(613): Value of type '1-dimensional array of
TestApp.ParameterValue' cannot be converted to '1-dimensional array of
ParameterValue' because 'TestApp.ParameterValue' is not derived from
'ParameterValue'.
In the public function in the CSharp DLL, I am looking for three parameters.
public bool PrintReport(string reportPath, ParameterValue[]
reportParameters, string printer)
In VB, I am calling the function like this:
PrintServer.PrintReport(ReportURL, ReportParameters, Printer)
Here is how I have declared the ReportParameters object:
Private ReportParameters(4) as ParameterValue
Can someone please explain to me the error message and how to fix it?Hi. Try fully declaring your ParameterValue[] parameter with the same
namespace as that of the cSharp library.
Regards,
Tim Ellison, MCP
Ironworks Consulting, LLC
(m) 804.405.4874
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:2DF92759-B44A-4D8B-A2D5-028EE17FF7FB@.microsoft.com...
> I am attempting to use VB.Net to interface to a CSharp DLL to print
reports.
> I have taken the CSharp Example that I have seen reported in this
newsgroup
> and made it into a DLL. I am trying to use the Reporting Services report
> parameter and getting the following error in VB.Net:
> TestApp.vb(613): Value of type '1-dimensional array of
> TestApp.ParameterValue' cannot be converted to '1-dimensional array of
> ParameterValue' because 'TestApp.ParameterValue' is not derived from
> 'ParameterValue'.
> In the public function in the CSharp DLL, I am looking for three
parameters.
> public bool PrintReport(string reportPath, ParameterValue[]
> reportParameters, string printer)
> In VB, I am calling the function like this:
> PrintServer.PrintReport(ReportURL, ReportParameters, Printer)
> Here is how I have declared the ReportParameters object:
> Private ReportParameters(4) as ParameterValue
> Can someone please explain to me the error message and how to fix it?
>|||Thanks for the response Tim, but I am not sure what you mean by declcaring
ParameterValue[] parameter with the same namespance as that of the cSharp
library.
I tried what I believe you were saying in my VB declaration:
Private ReportParameters(4) as SQLPrintServer.localhost.ParameterValue, with
SQLPrintServer being the namespace of my cSharp library, and got the same
error message.
TestApp.vb(570): Value of type '1-dimensional array of
SQLPrintServer.localhost.ParameterValue' cannot be converted to
'1-dimensional array of TestApp.ParameterValue' because
'SQLPrintServer.localhost.ParameterValue' is not derived from
'TestApp.ParameterValue'.
"TIM ELLISON" wrote:
> Hi. Try fully declaring your ParameterValue[] parameter with the same
> namespace as that of the cSharp library.
>
> --
> Regards,
> Tim Ellison, MCP
> Ironworks Consulting, LLC
> (m) 804.405.4874
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:2DF92759-B44A-4D8B-A2D5-028EE17FF7FB@.microsoft.com...
> > I am attempting to use VB.Net to interface to a CSharp DLL to print
> reports.
> > I have taken the CSharp Example that I have seen reported in this
> newsgroup
> > and made it into a DLL. I am trying to use the Reporting Services report
> > parameter and getting the following error in VB.Net:
> >
> > TestApp.vb(613): Value of type '1-dimensional array of
> > TestApp.ParameterValue' cannot be converted to '1-dimensional array of
> > ParameterValue' because 'TestApp.ParameterValue' is not derived from
> > 'ParameterValue'.
> >
> > In the public function in the CSharp DLL, I am looking for three
> parameters.
> >
> > public bool PrintReport(string reportPath, ParameterValue[]
> > reportParameters, string printer)
> >
> > In VB, I am calling the function like this:
> > PrintServer.PrintReport(ReportURL, ReportParameters, Printer)
> >
> > Here is how I have declared the ReportParameters object:
> > Private ReportParameters(4) as ParameterValue
> >
> > Can someone please explain to me the error message and how to fix it?
> >
> >
>
>|||Your web reference is going to create a namespace inside which the proxy for
the web service resides. I'm assuming from your code that it's
"SQLPrintServer.localhost".
You also have a test application whose default namespace appears to be
"TestApp".
A fully qualified name for a class is "NameSpace.ClassName". Therefore,
"SQLPrintServer.localhost.Parameter" is not equivalent to
"TestApp.Parameter".
Can you provide the code please? That might help.
--
Regards,
Tim Ellison, MCP
Ironworks Consulting, LLC
(m) 804.405.4874
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:B1D28046-E92D-4336-8B1E-AD826C084505@.microsoft.com...
> Thanks for the response Tim, but I am not sure what you mean by declcaring
> ParameterValue[] parameter with the same namespance as that of the cSharp
> library.
> I tried what I believe you were saying in my VB declaration:
> Private ReportParameters(4) as SQLPrintServer.localhost.ParameterValue,
with
> SQLPrintServer being the namespace of my cSharp library, and got the same
> error message.
> TestApp.vb(570): Value of type '1-dimensional array of
> SQLPrintServer.localhost.ParameterValue' cannot be converted to
> '1-dimensional array of TestApp.ParameterValue' because
> 'SQLPrintServer.localhost.ParameterValue' is not derived from
> 'TestApp.ParameterValue'.
> "TIM ELLISON" wrote:
> > Hi. Try fully declaring your ParameterValue[] parameter with the same
> > namespace as that of the cSharp library.
> >
> >
> > --
> > Regards,
> >
> > Tim Ellison, MCP
> > Ironworks Consulting, LLC
> > (m) 804.405.4874
> > "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> > news:2DF92759-B44A-4D8B-A2D5-028EE17FF7FB@.microsoft.com...
> > > I am attempting to use VB.Net to interface to a CSharp DLL to print
> > reports.
> > > I have taken the CSharp Example that I have seen reported in this
> > newsgroup
> > > and made it into a DLL. I am trying to use the Reporting Services
report
> > > parameter and getting the following error in VB.Net:
> > >
> > > TestApp.vb(613): Value of type '1-dimensional array of
> > > TestApp.ParameterValue' cannot be converted to '1-dimensional array of
> > > ParameterValue' because 'TestApp.ParameterValue' is not derived from
> > > 'ParameterValue'.
> > >
> > > In the public function in the CSharp DLL, I am looking for three
> > parameters.
> > >
> > > public bool PrintReport(string reportPath, ParameterValue[]
> > > reportParameters, string printer)
> > >
> > > In VB, I am calling the function like this:
> > > PrintServer.PrintReport(ReportURL, ReportParameters, Printer)
> > >
> > > Here is how I have declared the ReportParameters object:
> > > Private ReportParameters(4) as ParameterValue
> > >
> > > Can someone please explain to me the error message and how to fix it?
> > >
> > >
> >
> >
> >|||Actually, I think that is what I need. I will take what you gave me and try
it. If it does not work, I will add another post. Thanks for explaining it
further.
"TIM ELLISON" wrote:
> Your web reference is going to create a namespace inside which the proxy for
> the web service resides. I'm assuming from your code that it's
> "SQLPrintServer.localhost".
> You also have a test application whose default namespace appears to be
> "TestApp".
> A fully qualified name for a class is "NameSpace.ClassName". Therefore,
> "SQLPrintServer.localhost.Parameter" is not equivalent to
> "TestApp.Parameter".
> Can you provide the code please? That might help.
> --
> Regards,
> Tim Ellison, MCP
> Ironworks Consulting, LLC
> (m) 804.405.4874
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:B1D28046-E92D-4336-8B1E-AD826C084505@.microsoft.com...
> > Thanks for the response Tim, but I am not sure what you mean by declcaring
> > ParameterValue[] parameter with the same namespance as that of the cSharp
> > library.
> >
> > I tried what I believe you were saying in my VB declaration:
> > Private ReportParameters(4) as SQLPrintServer.localhost.ParameterValue,
> with
> > SQLPrintServer being the namespace of my cSharp library, and got the same
> > error message.
> >
> > TestApp.vb(570): Value of type '1-dimensional array of
> > SQLPrintServer.localhost.ParameterValue' cannot be converted to
> > '1-dimensional array of TestApp.ParameterValue' because
> > 'SQLPrintServer.localhost.ParameterValue' is not derived from
> > 'TestApp.ParameterValue'.
> >
> > "TIM ELLISON" wrote:
> >
> > > Hi. Try fully declaring your ParameterValue[] parameter with the same
> > > namespace as that of the cSharp library.
> > >
> > >
> > > --
> > > Regards,
> > >
> > > Tim Ellison, MCP
> > > Ironworks Consulting, LLC
> > > (m) 804.405.4874
> > > "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> > > news:2DF92759-B44A-4D8B-A2D5-028EE17FF7FB@.microsoft.com...
> > > > I am attempting to use VB.Net to interface to a CSharp DLL to print
> > > reports.
> > > > I have taken the CSharp Example that I have seen reported in this
> > > newsgroup
> > > > and made it into a DLL. I am trying to use the Reporting Services
> report
> > > > parameter and getting the following error in VB.Net:
> > > >
> > > > TestApp.vb(613): Value of type '1-dimensional array of
> > > > TestApp.ParameterValue' cannot be converted to '1-dimensional array of
> > > > ParameterValue' because 'TestApp.ParameterValue' is not derived from
> > > > 'ParameterValue'.
> > > >
> > > > In the public function in the CSharp DLL, I am looking for three
> > > parameters.
> > > >
> > > > public bool PrintReport(string reportPath, ParameterValue[]
> > > > reportParameters, string printer)
> > > >
> > > > In VB, I am calling the function like this:
> > > > PrintServer.PrintReport(ReportURL, ReportParameters, Printer)
> > > >
> > > > Here is how I have declared the ReportParameters object:
> > > > Private ReportParameters(4) as ParameterValue
> > > >
> > > > Can someone please explain to me the error message and how to fix it?
> > > >
> > > >
> > >
> > >
> > >
>
>

Data Transformation: copying a table to a table... How does it work?

Hello,

Probabaly a silly question yet as a DOTNET developer, I'm trying to simulate DTS when for example, I don't have permission to perform DTS on a production server.

In particular and interested regards caching of rows before the service decides to flush the buffer and write to the target table. Safe to assume DTS is cursor based?

What type of cursors and where? Not really sure where that comes from, as this is not really ADO or T-SQL which is were I have com eaccross cursors before.

SSIS and DTS shouldn't use cursors for extract or loading SQL Server, just look at the connections used, standard OLE-DB stuff. All stock sources and destinations are geared towards standard connections, and they use the common providers.

The buffer is a SSIS engine feature, so not cursors either.

If you do not have permissions that allows SSIS or DTS to run then you will not be able to simulate it either, as permission requirements will be the same. If you extract data you need read permissions to run your own code or a SSIS package that does the same thing for example.

Data transformation services

Hi

I was told that using DTS will allow me to schedule stored procedures to keep an sql database up to date. For example if a user registers but does not activate the registration, his details will be removed by a stored procedure which is scheduled to run every 24 hours. I use to use the global.asax file to fire a update by using a file containing a the date of the last update and then by adding 24 hours to it, it would execute a SP to delete unwanted data.

I have tried to install DTS with no success. I am running the following

Visual web studio express

SQL 2005 Express. (From SQLExpr_exe) and I have told it to install all the extra components

Installed SQLEXPR_Toolkit.exe with all its options

Installed SQLServer2005_DTS.MSI

When I go into the sql server using MS SQL Server Management Studio Express. I cannot see the Data transformation services node. I have also just installed server reports which I had no problems installing.

Can somebody please help me.

DTS is a SQL2000 component; SQL2005 hasa totally rewritten equivalent called SSIS. An SSIS (SQL Server Integration Sercvices) job amongst other things will run stored procedures for you. However it is SQL Agent that provides the scheduling capability.

|||

Hi

Thanks for the reply. I need to know where to download the ssis installation application. The other thing is my service provider that I use uses SQL2000. Im developing in SQL Express 2005. How will I deploy the scheduled jobs to there server if im using the newer version.

Regards