hi,
I wrote a dll in c#. It creates a bitmap file. the dll is appended to sql
server 2005 as an assembly. when I call that stored procedure it creates a
bitmap file on my local disk. the problem is, that the file cant be deleted.
I get error saying that the file is being used by another person or
application. but it isnt!. moreover, when I create an exe file and launch it,
everything works ok. then, the exe creates the bitmap which can be easy
deleted.
how to check what application is using the bitmap (a resource)? what is the
difference in creating the bitmap by launching a stored sql procedure than by
launching an exe application?
Hello Chris,
FileMon from WinTernals comes to mind. See http://www.winternals.com/
Chances are that the SQL Server is holding a lock on that file because you
didn't close the file handle on it. Can you post the bit of code that writes
the bitmap to the file?
Thanks,
Kent Tegels
DevelopMentor
Showing posts with label creates. Show all posts
Showing posts with label creates. Show all posts
Sunday, March 11, 2012
a stored procedure in C# creates a file (the file cant be deleted)
hi,
I wrote a dll in c#. It creates a bitmap file. the dll is appended to sql
server 2005 as an assembly. when I call that stored procedure it creates a
bitmap file on my local disk. the problem is, that the file cant be deleted.
I get error saying that the file is being used by another person or
application. but it isnt!. moreover, when I create an exe file and launch it
,
everything works ok. then, the exe creates the bitmap which can be easy
deleted.
how to check what application is using the bitmap (a resource)? what is the
difference in creating the bitmap by launching a stored sql procedure than b
y
launching an exe application?Hello Chris,
FileMon from WinTernals comes to mind. See http://www.winternals.com/
Chances are that the SQL Server is holding a lock on that file because you
didn't close the file handle on it. Can you post the bit of code that writes
the bitmap to the file?
Thanks,
Kent Tegels
DevelopMentor
I wrote a dll in c#. It creates a bitmap file. the dll is appended to sql
server 2005 as an assembly. when I call that stored procedure it creates a
bitmap file on my local disk. the problem is, that the file cant be deleted.
I get error saying that the file is being used by another person or
application. but it isnt!. moreover, when I create an exe file and launch it
,
everything works ok. then, the exe creates the bitmap which can be easy
deleted.
how to check what application is using the bitmap (a resource)? what is the
difference in creating the bitmap by launching a stored sql procedure than b
y
launching an exe application?Hello Chris,
FileMon from WinTernals comes to mind. See http://www.winternals.com/
Chances are that the SQL Server is holding a lock on that file because you
didn't close the file handle on it. Can you post the bit of code that writes
the bitmap to the file?
Thanks,
Kent Tegels
DevelopMentor
a stored procedure in C# creates a file (the file cant be deleted)
hi,
I wrote a dll in c#. It creates a bitmap file. the dll is appended to sql
server 2005 as an assembly. when I call that stored procedure it creates a
bitmap file on my local disk. the problem is, that the file cant be deleted.
I get error saying that the file is being used by another person or
application. but it isnt!. moreover, when I create an exe file and launch it,
everything works ok. then, the exe creates the bitmap which can be easy
deleted.
how to check what application is using the bitmap (a resource)? what is the
difference in creating the bitmap by launching a stored sql procedure than by
launching an exe application?Hello Chris,
FileMon from WinTernals comes to mind. See http://www.winternals.com/
Chances are that the SQL Server is holding a lock on that file because you
didn't close the file handle on it. Can you post the bit of code that writes
the bitmap to the file?
Thanks,
Kent Tegels
DevelopMentor
I wrote a dll in c#. It creates a bitmap file. the dll is appended to sql
server 2005 as an assembly. when I call that stored procedure it creates a
bitmap file on my local disk. the problem is, that the file cant be deleted.
I get error saying that the file is being used by another person or
application. but it isnt!. moreover, when I create an exe file and launch it,
everything works ok. then, the exe creates the bitmap which can be easy
deleted.
how to check what application is using the bitmap (a resource)? what is the
difference in creating the bitmap by launching a stored sql procedure than by
launching an exe application?Hello Chris,
FileMon from WinTernals comes to mind. See http://www.winternals.com/
Chances are that the SQL Server is holding a lock on that file because you
didn't close the file handle on it. Can you post the bit of code that writes
the bitmap to the file?
Thanks,
Kent Tegels
DevelopMentor
Thursday, March 8, 2012
A Simple Insert statement in European version of SQL
I have a client that uses my utility program to insert a record into a
table. Once my program receives the values, creates an insert statement
with comma separated values. Ie:
Insert into T (a,b) values (1.578, 2)
I now understand that those numeric values could have ',' in place of
decimal point for European version. So, the above values would look like
1,578 and 2.
How does the insert statement would know ',' is not a separator in this
case ? Ie:
Insert into T (a,b) values (1,578, 2) --> resulting
into syntax error
Should I be using a different value separator character ?
TIA.
MacI am not the one producing the values with ',' in place of decimal points.
It is SQL Server of European version that returns the data I am collecting
with embedded comma. So, when I submit a query of "Select a from T"
( a is defined to be a real type number), it returns 2,476 instead of
2.476. So, my question is how do I take these returned value with
embedded comma and insert them back into say another field in a table ? I
do not have such SQL version in my site to see what is going on and how to
accomplish such inserts.
- Mac
""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
news:PyBEKO#aDHA.2108@.cpmsftngxa06.phx.gbl...
> Hi Mac,
> Please do not use comma as decimal separator. It will cause problems. Use
> period as decimal point.
> Character expressions being converted to an exact numeric data type must
> consist of digits, a decimal point, and an optional plus (+) or minus (-).
> Leading blanks are ignored. Comma separators (such as the thousands
> separator in 123,456.00) are not allowed in the string.
>
> Bill Cheng
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> --
> | From: "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com>
> | Newsgroups: microsoft.public.sqlserver.server
> | Subject: A Simple Insert statement in European version of SQL
> | Date: Mon, 25 Aug 2003 16:49:15 -0700
> | Organization: Unisys - Roseville, MN
> | Lines: 20
> | Message-ID: <bie79r$1rmm$1@.si05.rsvl.unisys.com>
> | NNTP-Posting-Host: 192.59.171.175
> | X-Trace: si05.rsvl.unisys.com 1061855355 61142 192.59.171.175 (25 Aug
> 2003 23:49:15 GMT)
> | X-Complaints-To: news@.rsvl.unisys.com
> | NNTP-Posting-Date: 25 Aug 2003 23:49:15 GMT
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
> | Path:
>
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix.com!newsfeed.cwix.co
>
m!feed2.news.rcn.net!rcn!news-out.visi.com!petbe.visi.com!ash.uu.net!bbnews1
> .unisys.com!trsvr.tr.unisys.com!si05!not-for-mail
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:303093
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | I have a client that uses my utility program to insert a record into a
> | table. Once my program receives the values, creates an insert
statement
> | with comma separated values. Ie:
> | Insert into T (a,b) values (1.578, 2)
> |
> | I now understand that those numeric values could have ',' in place of
> | decimal point for European version. So, the above values would look
> like
> | 1,578 and 2.
> |
> | How does the insert statement would know ',' is not a separator in this
> | case ? Ie:
> | Insert into T (a,b) values (1,578, 2) --> resulting
> | into syntax error
> |
> | Should I be using a different value separator character ?
> |
> | TIA.
> | Mac
> |
> |
> |
>|||Are you using Visual basic ?
VB does use the client-settings to format numbers, dates,...
You'll have to use a format function to convert to a propre string.
e.g. strsql = "insert into table1 (col1) values (" & Format(numcol,
"###0.00") & ")"
jobi
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:big51p$5vu$1@.si05.rsvl.unisys.com...
> I am not the one producing the values with ',' in place of decimal points.
> It is SQL Server of European version that returns the data I am collecting
> with embedded comma. So, when I submit a query of "Select a from T"
> ( a is defined to be a real type number), it returns 2,476 instead of
> 2.476. So, my question is how do I take these returned value with
> embedded comma and insert them back into say another field in a table ?
I
> do not have such SQL version in my site to see what is going on and how to
> accomplish such inserts.
> - Mac
>
> ""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
> news:PyBEKO#aDHA.2108@.cpmsftngxa06.phx.gbl...
> > Hi Mac,
> >
> > Please do not use comma as decimal separator. It will cause problems.
Use
> > period as decimal point.
> >
> > Character expressions being converted to an exact numeric data type must
> > consist of digits, a decimal point, and an optional plus (+) or minus
(-).
> > Leading blanks are ignored. Comma separators (such as the thousands
> > separator in 123,456.00) are not allowed in the string.
> >
> >
> >
> > Bill Cheng
> > Microsoft Online Partner Support
> >
> > Get Secure! - www.microsoft.com/security
> > This posting is provided "as is" with no warranties and confers no
rights.
> > --
> > | From: "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com>
> > | Newsgroups: microsoft.public.sqlserver.server
> > | Subject: A Simple Insert statement in European version of SQL
> > | Date: Mon, 25 Aug 2003 16:49:15 -0700
> > | Organization: Unisys - Roseville, MN
> > | Lines: 20
> > | Message-ID: <bie79r$1rmm$1@.si05.rsvl.unisys.com>
> > | NNTP-Posting-Host: 192.59.171.175
> > | X-Trace: si05.rsvl.unisys.com 1061855355 61142 192.59.171.175 (25 Aug
> > 2003 23:49:15 GMT)
> > | X-Complaints-To: news@.rsvl.unisys.com
> > | NNTP-Posting-Date: 25 Aug 2003 23:49:15 GMT
> > | X-Priority: 3
> > | X-MSMail-Priority: Normal
> > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
> > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
> > | Path:
> >
>
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix.com!newsfeed.cwix.co
> >
>
m!feed2.news.rcn.net!rcn!news-out.visi.com!petbe.visi.com!ash.uu.net!bbnews1
> > .unisys.com!trsvr.tr.unisys.com!si05!not-for-mail
> > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:303093
> > | X-Tomcat-NG: microsoft.public.sqlserver.server
> > |
> > | I have a client that uses my utility program to insert a record into a
> > | table. Once my program receives the values, creates an insert
> statement
> > | with comma separated values. Ie:
> > | Insert into T (a,b) values (1.578, 2)
> > |
> > | I now understand that those numeric values could have ',' in place of
> > | decimal point for European version. So, the above values would look
> > like
> > | 1,578 and 2.
> > |
> > | How does the insert statement would know ',' is not a separator in
this
> > | case ? Ie:
> > | Insert into T (a,b) values (1,578, 2) -->
resulting
> > | into syntax error
> > |
> > | Should I be using a different value separator character ?
> > |
> > | TIA.
> > | Mac
> > |
> > |
> > |
> >
>|||As jobi point out: You have to differentiate between input and output. Just because a client tool
formats something that SQL Server outputs with a comma doesn't mean that SQL Server accepts that as
a valid input format.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"jobi" <jobi@.reply2.group> wrote in message news:bihkck$boq$1@.reader08.wxs.nl...
> Are you using Visual basic ?
> VB does use the client-settings to format numbers, dates,...
> You'll have to use a format function to convert to a propre string.
> e.g. strsql = "insert into table1 (col1) values (" & Format(numcol,
> "###0.00") & ")"
> jobi
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:big51p$5vu$1@.si05.rsvl.unisys.com...
> > I am not the one producing the values with ',' in place of decimal points.
> > It is SQL Server of European version that returns the data I am collecting
> > with embedded comma. So, when I submit a query of "Select a from T"
> > ( a is defined to be a real type number), it returns 2,476 instead of
> > 2.476. So, my question is how do I take these returned value with
> > embedded comma and insert them back into say another field in a table ?
> I
> > do not have such SQL version in my site to see what is going on and how to
> > accomplish such inserts.
> >
> > - Mac
> >
> >
> > ""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
> > news:PyBEKO#aDHA.2108@.cpmsftngxa06.phx.gbl...
> > > Hi Mac,
> > >
> > > Please do not use comma as decimal separator. It will cause problems.
> Use
> > > period as decimal point.
> > >
> > > Character expressions being converted to an exact numeric data type must
> > > consist of digits, a decimal point, and an optional plus (+) or minus
> (-).
> > > Leading blanks are ignored. Comma separators (such as the thousands
> > > separator in 123,456.00) are not allowed in the string.
> > >
> > >
> > >
> > > Bill Cheng
> > > Microsoft Online Partner Support
> > >
> > > Get Secure! - www.microsoft.com/security
> > > This posting is provided "as is" with no warranties and confers no
> rights.
> > > --
> > > | From: "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com>
> > > | Newsgroups: microsoft.public.sqlserver.server
> > > | Subject: A Simple Insert statement in European version of SQL
> > > | Date: Mon, 25 Aug 2003 16:49:15 -0700
> > > | Organization: Unisys - Roseville, MN
> > > | Lines: 20
> > > | Message-ID: <bie79r$1rmm$1@.si05.rsvl.unisys.com>
> > > | NNTP-Posting-Host: 192.59.171.175
> > > | X-Trace: si05.rsvl.unisys.com 1061855355 61142 192.59.171.175 (25 Aug
> > > 2003 23:49:15 GMT)
> > > | X-Complaints-To: news@.rsvl.unisys.com
> > > | NNTP-Posting-Date: 25 Aug 2003 23:49:15 GMT
> > > | X-Priority: 3
> > > | X-MSMail-Priority: Normal
> > > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
> > > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
> > > | Path:
> > >
> >
> cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix.com!newsfeed.cwix.co
> > >
> >
> m!feed2.news.rcn.net!rcn!news-out.visi.com!petbe.visi.com!ash.uu.net!bbnews1
> > > .unisys.com!trsvr.tr.unisys.com!si05!not-for-mail
> > > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:303093
> > > | X-Tomcat-NG: microsoft.public.sqlserver.server
> > > |
> > > | I have a client that uses my utility program to insert a record into a
> > > | table. Once my program receives the values, creates an insert
> > statement
> > > | with comma separated values. Ie:
> > > | Insert into T (a,b) values (1.578, 2)
> > > |
> > > | I now understand that those numeric values could have ',' in place of
> > > | decimal point for European version. So, the above values would look
> > > like
> > > | 1,578 and 2.
> > > |
> > > | How does the insert statement would know ',' is not a separator in
> this
> > > | case ? Ie:
> > > | Insert into T (a,b) values (1,578, 2) -->
> resulting
> > > | into syntax error
> > > |
> > > | Should I be using a different value separator character ?
> > > |
> > > | TIA.
> > > | Mac
> > > |
> > > |
> > > |
> > >
> >
> >
>|||Mac,
I've checked my vb-code again, and found this extra.
'Aparently the format still uses the client-setting for decimal point !!!
replace$(string, ",",".")
so you'll have to come up to this :
e.g. strsql = "insert into table1 (col1) values (" &
Replace$(Format(numcol, "###0.00"),",",".") & ")"
I guess you don't need the ###-part, in fact, you only need the format if
you want control of the format, else you can use the cstr-function.
Replace$(CStr(numcol), ",", ".")
jobi
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:bijdkf$2fsd$1@.si05.rsvl.unisys.com...
> Jobi,
> Yes. I am using VB. Also when I am collecting the data, I use Format
> function to make sure I only get 2 digits decimal point. The format
> sysntax I use is following:
> Format(numValue, "0.00").
> Is this not correct ? Do I need the ### in front of them ? More like
aVB
> question...
> By using the Format function I thought I am also forcing the decimal point
> to show up as decimal point despite the local setting of the computer.
This
> way then I can turn around and use the result in another insert statement
> without further formatting.
> Thanks for the input.
> Mac
> "jobi" <jobi@.reply2.group> wrote in message
> news:bihkck$boq$1@.reader08.wxs.nl...
> > Are you using Visual basic ?
> >
> > VB does use the client-settings to format numbers, dates,...
> > You'll have to use a format function to convert to a propre string.
> >
> > e.g. strsql = "insert into table1 (col1) values (" & Format(numcol,
> > "###0.00") & ")"
> >
> > jobi
> > "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> > news:big51p$5vu$1@.si05.rsvl.unisys.com...
> > > I am not the one producing the values with ',' in place of decimal
> points.
> > > It is SQL Server of European version that returns the data I am
> collecting
> > > with embedded comma. So, when I submit a query of "Select a from
T"
> > > ( a is defined to be a real type number), it returns 2,476 instead
of
> > > 2.476. So, my question is how do I take these returned value with
> > > embedded comma and insert them back into say another field in a table
?
> > I
> > > do not have such SQL version in my site to see what is going on and
how
> to
> > > accomplish such inserts.
> > >
> > > - Mac
> > >
> > >
> > > ""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
> > > news:PyBEKO#aDHA.2108@.cpmsftngxa06.phx.gbl...
> > > > Hi Mac,
> > > >
> > > > Please do not use comma as decimal separator. It will cause
problems.
> > Use
> > > > period as decimal point.
> > > >
> > > > Character expressions being converted to an exact numeric data type
> must
> > > > consist of digits, a decimal point, and an optional plus (+) or
minus
> > (-).
> > > > Leading blanks are ignored. Comma separators (such as the thousands
> > > > separator in 123,456.00) are not allowed in the string.
> > > >
> > > >
> > > >
> > > > Bill Cheng
> > > > Microsoft Online Partner Support
> > > >
> > > > Get Secure! - www.microsoft.com/security
> > > > This posting is provided "as is" with no warranties and confers no
> > rights.
> > > > --
> > > > | From: "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com>
> > > > | Newsgroups: microsoft.public.sqlserver.server
> > > > | Subject: A Simple Insert statement in European version of SQL
> > > > | Date: Mon, 25 Aug 2003 16:49:15 -0700
> > > > | Organization: Unisys - Roseville, MN
> > > > | Lines: 20
> > > > | Message-ID: <bie79r$1rmm$1@.si05.rsvl.unisys.com>
> > > > | NNTP-Posting-Host: 192.59.171.175
> > > > | X-Trace: si05.rsvl.unisys.com 1061855355 61142 192.59.171.175 (25
> Aug
> > > > 2003 23:49:15 GMT)
> > > > | X-Complaints-To: news@.rsvl.unisys.com
> > > > | NNTP-Posting-Date: 25 Aug 2003 23:49:15 GMT
> > > > | X-Priority: 3
> > > > | X-MSMail-Priority: Normal
> > > > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
> > > > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
> > > > | Path:
> > > >
> > >
> >
>
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix.com!newsfeed.cwix.co
> > > >
> > >
> >
>
m!feed2.news.rcn.net!rcn!news-out.visi.com!petbe.visi.com!ash.uu.net!bbnews1
> > > > .unisys.com!trsvr.tr.unisys.com!si05!not-for-mail
> > > > | Xref: cpmsftngxa06.phx.gbl
microsoft.public.sqlserver.server:303093
> > > > | X-Tomcat-NG: microsoft.public.sqlserver.server
> > > > |
> > > > | I have a client that uses my utility program to insert a record
into
> a
> > > > | table. Once my program receives the values, creates an insert
> > > statement
> > > > | with comma separated values. Ie:
> > > > | Insert into T (a,b) values (1.578, 2)
> > > > |
> > > > | I now understand that those numeric values could have ',' in
place
> of
> > > > | decimal point for European version. So, the above values would
> look
> > > > like
> > > > | 1,578 and 2.
> > > > |
> > > > | How does the insert statement would know ',' is not a separator
in
> > this
> > > > | case ? Ie:
> > > > | Insert into T (a,b) values (1,578, 2) -->
> > resulting
> > > > | into syntax error
> > > > |
> > > > | Should I be using a different value separator character ?
> > > > |
> > > > | TIA.
> > > > | Mac
> > > > |
> > > > |
> > > > |
> > > >
> > >
> > >
> >
> >
>
table. Once my program receives the values, creates an insert statement
with comma separated values. Ie:
Insert into T (a,b) values (1.578, 2)
I now understand that those numeric values could have ',' in place of
decimal point for European version. So, the above values would look like
1,578 and 2.
How does the insert statement would know ',' is not a separator in this
case ? Ie:
Insert into T (a,b) values (1,578, 2) --> resulting
into syntax error
Should I be using a different value separator character ?
TIA.
MacI am not the one producing the values with ',' in place of decimal points.
It is SQL Server of European version that returns the data I am collecting
with embedded comma. So, when I submit a query of "Select a from T"
( a is defined to be a real type number), it returns 2,476 instead of
2.476. So, my question is how do I take these returned value with
embedded comma and insert them back into say another field in a table ? I
do not have such SQL version in my site to see what is going on and how to
accomplish such inserts.
- Mac
""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
news:PyBEKO#aDHA.2108@.cpmsftngxa06.phx.gbl...
> Hi Mac,
> Please do not use comma as decimal separator. It will cause problems. Use
> period as decimal point.
> Character expressions being converted to an exact numeric data type must
> consist of digits, a decimal point, and an optional plus (+) or minus (-).
> Leading blanks are ignored. Comma separators (such as the thousands
> separator in 123,456.00) are not allowed in the string.
>
> Bill Cheng
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> --
> | From: "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com>
> | Newsgroups: microsoft.public.sqlserver.server
> | Subject: A Simple Insert statement in European version of SQL
> | Date: Mon, 25 Aug 2003 16:49:15 -0700
> | Organization: Unisys - Roseville, MN
> | Lines: 20
> | Message-ID: <bie79r$1rmm$1@.si05.rsvl.unisys.com>
> | NNTP-Posting-Host: 192.59.171.175
> | X-Trace: si05.rsvl.unisys.com 1061855355 61142 192.59.171.175 (25 Aug
> 2003 23:49:15 GMT)
> | X-Complaints-To: news@.rsvl.unisys.com
> | NNTP-Posting-Date: 25 Aug 2003 23:49:15 GMT
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
> | Path:
>
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix.com!newsfeed.cwix.co
>
m!feed2.news.rcn.net!rcn!news-out.visi.com!petbe.visi.com!ash.uu.net!bbnews1
> .unisys.com!trsvr.tr.unisys.com!si05!not-for-mail
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:303093
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | I have a client that uses my utility program to insert a record into a
> | table. Once my program receives the values, creates an insert
statement
> | with comma separated values. Ie:
> | Insert into T (a,b) values (1.578, 2)
> |
> | I now understand that those numeric values could have ',' in place of
> | decimal point for European version. So, the above values would look
> like
> | 1,578 and 2.
> |
> | How does the insert statement would know ',' is not a separator in this
> | case ? Ie:
> | Insert into T (a,b) values (1,578, 2) --> resulting
> | into syntax error
> |
> | Should I be using a different value separator character ?
> |
> | TIA.
> | Mac
> |
> |
> |
>|||Are you using Visual basic ?
VB does use the client-settings to format numbers, dates,...
You'll have to use a format function to convert to a propre string.
e.g. strsql = "insert into table1 (col1) values (" & Format(numcol,
"###0.00") & ")"
jobi
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:big51p$5vu$1@.si05.rsvl.unisys.com...
> I am not the one producing the values with ',' in place of decimal points.
> It is SQL Server of European version that returns the data I am collecting
> with embedded comma. So, when I submit a query of "Select a from T"
> ( a is defined to be a real type number), it returns 2,476 instead of
> 2.476. So, my question is how do I take these returned value with
> embedded comma and insert them back into say another field in a table ?
I
> do not have such SQL version in my site to see what is going on and how to
> accomplish such inserts.
> - Mac
>
> ""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
> news:PyBEKO#aDHA.2108@.cpmsftngxa06.phx.gbl...
> > Hi Mac,
> >
> > Please do not use comma as decimal separator. It will cause problems.
Use
> > period as decimal point.
> >
> > Character expressions being converted to an exact numeric data type must
> > consist of digits, a decimal point, and an optional plus (+) or minus
(-).
> > Leading blanks are ignored. Comma separators (such as the thousands
> > separator in 123,456.00) are not allowed in the string.
> >
> >
> >
> > Bill Cheng
> > Microsoft Online Partner Support
> >
> > Get Secure! - www.microsoft.com/security
> > This posting is provided "as is" with no warranties and confers no
rights.
> > --
> > | From: "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com>
> > | Newsgroups: microsoft.public.sqlserver.server
> > | Subject: A Simple Insert statement in European version of SQL
> > | Date: Mon, 25 Aug 2003 16:49:15 -0700
> > | Organization: Unisys - Roseville, MN
> > | Lines: 20
> > | Message-ID: <bie79r$1rmm$1@.si05.rsvl.unisys.com>
> > | NNTP-Posting-Host: 192.59.171.175
> > | X-Trace: si05.rsvl.unisys.com 1061855355 61142 192.59.171.175 (25 Aug
> > 2003 23:49:15 GMT)
> > | X-Complaints-To: news@.rsvl.unisys.com
> > | NNTP-Posting-Date: 25 Aug 2003 23:49:15 GMT
> > | X-Priority: 3
> > | X-MSMail-Priority: Normal
> > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
> > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
> > | Path:
> >
>
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix.com!newsfeed.cwix.co
> >
>
m!feed2.news.rcn.net!rcn!news-out.visi.com!petbe.visi.com!ash.uu.net!bbnews1
> > .unisys.com!trsvr.tr.unisys.com!si05!not-for-mail
> > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:303093
> > | X-Tomcat-NG: microsoft.public.sqlserver.server
> > |
> > | I have a client that uses my utility program to insert a record into a
> > | table. Once my program receives the values, creates an insert
> statement
> > | with comma separated values. Ie:
> > | Insert into T (a,b) values (1.578, 2)
> > |
> > | I now understand that those numeric values could have ',' in place of
> > | decimal point for European version. So, the above values would look
> > like
> > | 1,578 and 2.
> > |
> > | How does the insert statement would know ',' is not a separator in
this
> > | case ? Ie:
> > | Insert into T (a,b) values (1,578, 2) -->
resulting
> > | into syntax error
> > |
> > | Should I be using a different value separator character ?
> > |
> > | TIA.
> > | Mac
> > |
> > |
> > |
> >
>|||As jobi point out: You have to differentiate between input and output. Just because a client tool
formats something that SQL Server outputs with a comma doesn't mean that SQL Server accepts that as
a valid input format.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"jobi" <jobi@.reply2.group> wrote in message news:bihkck$boq$1@.reader08.wxs.nl...
> Are you using Visual basic ?
> VB does use the client-settings to format numbers, dates,...
> You'll have to use a format function to convert to a propre string.
> e.g. strsql = "insert into table1 (col1) values (" & Format(numcol,
> "###0.00") & ")"
> jobi
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:big51p$5vu$1@.si05.rsvl.unisys.com...
> > I am not the one producing the values with ',' in place of decimal points.
> > It is SQL Server of European version that returns the data I am collecting
> > with embedded comma. So, when I submit a query of "Select a from T"
> > ( a is defined to be a real type number), it returns 2,476 instead of
> > 2.476. So, my question is how do I take these returned value with
> > embedded comma and insert them back into say another field in a table ?
> I
> > do not have such SQL version in my site to see what is going on and how to
> > accomplish such inserts.
> >
> > - Mac
> >
> >
> > ""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
> > news:PyBEKO#aDHA.2108@.cpmsftngxa06.phx.gbl...
> > > Hi Mac,
> > >
> > > Please do not use comma as decimal separator. It will cause problems.
> Use
> > > period as decimal point.
> > >
> > > Character expressions being converted to an exact numeric data type must
> > > consist of digits, a decimal point, and an optional plus (+) or minus
> (-).
> > > Leading blanks are ignored. Comma separators (such as the thousands
> > > separator in 123,456.00) are not allowed in the string.
> > >
> > >
> > >
> > > Bill Cheng
> > > Microsoft Online Partner Support
> > >
> > > Get Secure! - www.microsoft.com/security
> > > This posting is provided "as is" with no warranties and confers no
> rights.
> > > --
> > > | From: "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com>
> > > | Newsgroups: microsoft.public.sqlserver.server
> > > | Subject: A Simple Insert statement in European version of SQL
> > > | Date: Mon, 25 Aug 2003 16:49:15 -0700
> > > | Organization: Unisys - Roseville, MN
> > > | Lines: 20
> > > | Message-ID: <bie79r$1rmm$1@.si05.rsvl.unisys.com>
> > > | NNTP-Posting-Host: 192.59.171.175
> > > | X-Trace: si05.rsvl.unisys.com 1061855355 61142 192.59.171.175 (25 Aug
> > > 2003 23:49:15 GMT)
> > > | X-Complaints-To: news@.rsvl.unisys.com
> > > | NNTP-Posting-Date: 25 Aug 2003 23:49:15 GMT
> > > | X-Priority: 3
> > > | X-MSMail-Priority: Normal
> > > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
> > > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
> > > | Path:
> > >
> >
> cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix.com!newsfeed.cwix.co
> > >
> >
> m!feed2.news.rcn.net!rcn!news-out.visi.com!petbe.visi.com!ash.uu.net!bbnews1
> > > .unisys.com!trsvr.tr.unisys.com!si05!not-for-mail
> > > | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:303093
> > > | X-Tomcat-NG: microsoft.public.sqlserver.server
> > > |
> > > | I have a client that uses my utility program to insert a record into a
> > > | table. Once my program receives the values, creates an insert
> > statement
> > > | with comma separated values. Ie:
> > > | Insert into T (a,b) values (1.578, 2)
> > > |
> > > | I now understand that those numeric values could have ',' in place of
> > > | decimal point for European version. So, the above values would look
> > > like
> > > | 1,578 and 2.
> > > |
> > > | How does the insert statement would know ',' is not a separator in
> this
> > > | case ? Ie:
> > > | Insert into T (a,b) values (1,578, 2) -->
> resulting
> > > | into syntax error
> > > |
> > > | Should I be using a different value separator character ?
> > > |
> > > | TIA.
> > > | Mac
> > > |
> > > |
> > > |
> > >
> >
> >
>|||Mac,
I've checked my vb-code again, and found this extra.
'Aparently the format still uses the client-setting for decimal point !!!
replace$(string, ",",".")
so you'll have to come up to this :
e.g. strsql = "insert into table1 (col1) values (" &
Replace$(Format(numcol, "###0.00"),",",".") & ")"
I guess you don't need the ###-part, in fact, you only need the format if
you want control of the format, else you can use the cstr-function.
Replace$(CStr(numcol), ",", ".")
jobi
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:bijdkf$2fsd$1@.si05.rsvl.unisys.com...
> Jobi,
> Yes. I am using VB. Also when I am collecting the data, I use Format
> function to make sure I only get 2 digits decimal point. The format
> sysntax I use is following:
> Format(numValue, "0.00").
> Is this not correct ? Do I need the ### in front of them ? More like
aVB
> question...
> By using the Format function I thought I am also forcing the decimal point
> to show up as decimal point despite the local setting of the computer.
This
> way then I can turn around and use the result in another insert statement
> without further formatting.
> Thanks for the input.
> Mac
> "jobi" <jobi@.reply2.group> wrote in message
> news:bihkck$boq$1@.reader08.wxs.nl...
> > Are you using Visual basic ?
> >
> > VB does use the client-settings to format numbers, dates,...
> > You'll have to use a format function to convert to a propre string.
> >
> > e.g. strsql = "insert into table1 (col1) values (" & Format(numcol,
> > "###0.00") & ")"
> >
> > jobi
> > "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> > news:big51p$5vu$1@.si05.rsvl.unisys.com...
> > > I am not the one producing the values with ',' in place of decimal
> points.
> > > It is SQL Server of European version that returns the data I am
> collecting
> > > with embedded comma. So, when I submit a query of "Select a from
T"
> > > ( a is defined to be a real type number), it returns 2,476 instead
of
> > > 2.476. So, my question is how do I take these returned value with
> > > embedded comma and insert them back into say another field in a table
?
> > I
> > > do not have such SQL version in my site to see what is going on and
how
> to
> > > accomplish such inserts.
> > >
> > > - Mac
> > >
> > >
> > > ""Bill Cheng [MSFT]"" <billchng@.online.microsoft.com> wrote in message
> > > news:PyBEKO#aDHA.2108@.cpmsftngxa06.phx.gbl...
> > > > Hi Mac,
> > > >
> > > > Please do not use comma as decimal separator. It will cause
problems.
> > Use
> > > > period as decimal point.
> > > >
> > > > Character expressions being converted to an exact numeric data type
> must
> > > > consist of digits, a decimal point, and an optional plus (+) or
minus
> > (-).
> > > > Leading blanks are ignored. Comma separators (such as the thousands
> > > > separator in 123,456.00) are not allowed in the string.
> > > >
> > > >
> > > >
> > > > Bill Cheng
> > > > Microsoft Online Partner Support
> > > >
> > > > Get Secure! - www.microsoft.com/security
> > > > This posting is provided "as is" with no warranties and confers no
> > rights.
> > > > --
> > > > | From: "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com>
> > > > | Newsgroups: microsoft.public.sqlserver.server
> > > > | Subject: A Simple Insert statement in European version of SQL
> > > > | Date: Mon, 25 Aug 2003 16:49:15 -0700
> > > > | Organization: Unisys - Roseville, MN
> > > > | Lines: 20
> > > > | Message-ID: <bie79r$1rmm$1@.si05.rsvl.unisys.com>
> > > > | NNTP-Posting-Host: 192.59.171.175
> > > > | X-Trace: si05.rsvl.unisys.com 1061855355 61142 192.59.171.175 (25
> Aug
> > > > 2003 23:49:15 GMT)
> > > > | X-Complaints-To: news@.rsvl.unisys.com
> > > > | NNTP-Posting-Date: 25 Aug 2003 23:49:15 GMT
> > > > | X-Priority: 3
> > > > | X-MSMail-Priority: Normal
> > > > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
> > > > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
> > > > | Path:
> > > >
> > >
> >
>
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwix.com!newsfeed.cwix.co
> > > >
> > >
> >
>
m!feed2.news.rcn.net!rcn!news-out.visi.com!petbe.visi.com!ash.uu.net!bbnews1
> > > > .unisys.com!trsvr.tr.unisys.com!si05!not-for-mail
> > > > | Xref: cpmsftngxa06.phx.gbl
microsoft.public.sqlserver.server:303093
> > > > | X-Tomcat-NG: microsoft.public.sqlserver.server
> > > > |
> > > > | I have a client that uses my utility program to insert a record
into
> a
> > > > | table. Once my program receives the values, creates an insert
> > > statement
> > > > | with comma separated values. Ie:
> > > > | Insert into T (a,b) values (1.578, 2)
> > > > |
> > > > | I now understand that those numeric values could have ',' in
place
> of
> > > > | decimal point for European version. So, the above values would
> look
> > > > like
> > > > | 1,578 and 2.
> > > > |
> > > > | How does the insert statement would know ',' is not a separator
in
> > this
> > > > | case ? Ie:
> > > > | Insert into T (a,b) values (1,578, 2) -->
> > resulting
> > > > | into syntax error
> > > > |
> > > > | Should I be using a different value separator character ?
> > > > |
> > > > | TIA.
> > > > | Mac
> > > > |
> > > > |
> > > > |
> > > >
> > >
> > >
> >
> >
>
Tuesday, March 6, 2012
A script to delete views
Hi,
The script below will delete all dbo-owned views that begin with 'Search'.
However, creating/deleting objects from normal application code is not
secure and often an indication of an application design flaw.
I need a script that I can run from ASP .Net that will delete all
views that start with "Search". My site creates them on the fly and
they tend to accumulate as more users visit the site. Is there a good
SQL help web site that I can refer to that will be me started?
Thanks,
Bill
Cincinnati, OH USAI need a script that I can run from ASP .Net that will delete all
Quote:
Originally Posted by
views that start with "Search". My site creates them on the fly and
they tend to accumulate as more users visit the site. Is there a good
SQL help web site that I can refer to that will be me started?
The script below will delete all dbo-owned views that begin with 'Search'.
However, creating/deleting objects from normal application code is not
secure and often an indication of an application design flaw.
SET NOCOUNT ON
DECLARE @.DropStatement nvarchar(4000)
DECLARE @.LastError int
DECLARE DropStatements
CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
--views
SELECT
N'DROP VIEW ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) AS DropStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = N'VIEW'
AND OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMSShipped') = 0
AND TABLE_SCHEMA = N'dbo'
AND TABLE_NAME LIKE N'Search%'
OPEN DropStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM DropStatements INTO @.DropStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
EXECUTE sp_ExecuteSQL @.DropStatement
SET @.LastError = @.@.ERROR
IF @.LastError 0
BEGIN
BREAK
END
END
END
CLOSE DropStatements
DEALLOCATE DropStatements
--
Hope this helps.
Dan Guzman
SQL Server MVP
<namewitheldbyrequest@.gmail.comwrote in message
news:1159647523.344352.188610@.c28g2000cwb.googlegr oups.com...
Quote:
Originally Posted by
Hi,
>
I need a script that I can run from ASP .Net that will delete all
views that start with "Search". My site creates them on the fly and
they tend to accumulate as more users visit the site. Is there a good
SQL help web site that I can refer to that will be me started?
>
Thanks,
>
Bill
Cincinnati, OH USA
>
Subscribe to:
Posts (Atom)