SQL About AMO: string

Showing posts with label string. Show all posts

Thursday, March 22, 2012

Access a in house sql server from a remote web server

does anybody know how I would create a connection string in asp.net 2.0express to access a database in a different location from the webserver. I have a static IP address on the web server, so I've allowedaccess from the web server through the firewall. I have a user name andpassword, and I've made sure that I have the correct database. Would Iaccess the database using the network IP address and if so, where do Iput it. Thank you to anyone that can give me some insight to thisproblem, I'm very new to asp.net so, about a weeks worth of knowledge,so if you could explain as such I would appreciate it.
Seewww.connectionstrings.com. You put the IP where you normally put the Server name, all else should be the same. That said, hitting a SQL Server on the other side of a firewall may not be the best thing to do.|||thanks
"That said, hitting a SQL Server on the other side of a firewall may not be the best thing to do."
what you recommend then, like I said, I'm pretty new to this. Theproject that I'm working on requires me to create a web form, that ison an outsourced web server, and import the data into our database thatis behind a firewall. Would this be a security issue?

|||Yes, it can be. If SQL Server was perfect, it would not be a problem to leave the SQL port open. SQL Server is not perfect (recall the Slammer virus). So, in the circumstances you describe, perhaps it is your only option. However, I would make very certain that the database server is always patched up to date.sql

Thursday, March 8, 2012

absolutely weird String Concatenation problem, and really daring!

Hi,
I'm having this realy weird stuff going on here with a string concatenation
I do in a Cursor. It seems my string (NVARCHAR(4000)) is somewherel imited
to a number of concatenations or stuff like hat. Instead of doing everytime
the "SET @.strSQL = @.strSQL + ' + ' + ''') AND (''' + ' + ' + @.strField "
everytime, it suddenly stops adding the @.strField, even when there is a
value in it...
Just check it out, I added the returned values...
Anybody who can help with this would be reallt appreciated, i can't find
anything about this on google :-(
Pieter
***************************************** My Query
CREATE TABLE #tmp
(COLUMN_NAME nvarchar(100),DATA_TYPE varchar(20))
INSERT INTO #tmp
EXEC spSyncKeyColumns 'STOCK2'
/* Make the cursor:*/
DECLARE curC CURSOR
FOR
SELECT /*col.COLUMN_NAME, col.DATA_TYPE, */
CASE col.DATA_TYPE
WHEN 'char' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + ''' + ' + '
+ col.COLUMN_NAME + ' + ' + ''' + ' )'
WHEN 'nchar' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + ''' + ' +
' + col.COLUMN_NAME + ' + ' + ''' + ' )'
WHEN 'varchar' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + ''' + '
+ ' + col.COLUMN_NAME + ' + ' + ''' + ' )'
WHEN 'nvarchar' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + ''' + '
+ ' + col.COLUMN_NAME + ' + ' + ''' + ' )'
WHEN 'text' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + ''' + ' + '
+ col.COLUMN_NAME + ' + ' + ''' + ' )'
WHEN 'ntext' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + ''' + ' +
' + col.COLUMN_NAME + ' + ' + ''' + ' )'
WHEN 'uniqueidentifier' THEN '(''' + col.COLUMN_NAME + ' = '' + ' +
''' + ' + ' + col.COLUMN_NAME + ' + ' + ''' + ' )'
WHEN 'datetime' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + ''' + '
+ CAST(CONVERT(datetime, ' + col.COLUMN_NAME + ', 126) AS NVARCHAR) + ' +
''' + ' )'
WHEN 'smalldatetime' THEN '(''' + col.COLUMN_NAME + ' = '' + ' +
''' + ' + CAST(CONVERT(smalldatetime, ' + col.COLUMN_NAME + ', 126)
AS NVARCHAR) + ' + ''' + ' )'
WHEN 'timestamp' THEN '(''' + col.COLUMN_NAME + ' = '' + ' +
col.COLUMN_NAME + ' )'
ELSE '(''' + col.COLUMN_NAME + ' = '' + CAST(' + col.COLUMN_NAME + ' AS
NVARCHAR) )'
END AS Syntax
FROM #tmp col
/* Open the Cursor*/
OPEN curC
/* Declaration of the variables*/
DECLARE @.strSQL nvarchar(4000), @.strField NVARCHAR(100)
SET @.strSQL = ''
/*FIRST*/
FETCH NEXT FROM curC INTO @.strField
BEGIN
SET @.strSQL = @.strField
SELECT @.strField
END
/*THE OTHERS*/
FETCH NEXT FROM curC INTO @.strField
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.strSQL = @.strSQL + ' + ' + ''') AND (''' + ' + ' + @.strField
SELECT @.strField
/* Take next Table*/
FETCH NEXT FROM curC INTO @.strField
END
/* Fermes le Cursor*/
CLOSE curC
DEALLOCATE curC
DROP TABLE #tmp
SET @.strSQL = '( ''('' + ' + @.strSQL + ' + '')'' ) AS TWhere'
SELECT COALESCE(@.strSQL, 'NULL AS TWhere')
GO
***************************************** End of My Query
***************************************** The Results:
(5 row(s) affected)
----
--
('CODE_SOCIETE = ' + '''' + CODE_SOCIETE + '''' )
(1 row(s) affected) -> My First @.strField
----
--
('ID_TYPE = ' + CAST(ID_TYPE AS NVARCHAR) )
(1 row(s) affected) -> My 2nd @.strField
----
--
('NUM_SITE = ' + CAST(NUM_SITE AS NVARCHAR) )
(1 row(s) affected) -> My 3nd @.strField
----
--
('ID_CONTAINER = ' + CAST(ID_CONTAINER AS NVARCHAR) )
(1 row(s) affected) -> My 4th @.strField
----
--
('DATE_TIME = ' + DATE_TIME )
(1 row(s) affected) -> My 5th @.strField
----
----
----
--
( '(' + ('CODE_SOCIETE = ' + '''' + CODE_SOCIETE + '''' ) + ') AND (' +
('ID_TYPE = ' + CAST(ID_TYPE AS NVARCHAR) ) + ') AND (' + ('NUM_SITE = ' +
CAST(NUM_SITE AS NVARCHAR) ) + ') AND (' + ('ID_CONTAINER = ' +
CAST(ID_CONTAINER AS NVARCHAR) ) + ') AND ('
(1 row(s) affected) -> The final @.strSQL... As you can see: the
5th @.strField isn't added, although the ") AND (" is added...
Can anybody help me with this absolutely weird stuff? Thank a lot in
advance!!When using string concatenation you should always keep in mind the option
that a column / variable can be null --> ISNULL(SomeColumn,'')
--
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"DraguVaso" wrote:
> Hi,
> I'm having this realy weird stuff going on here with a string concatenation
> I do in a Cursor. It seems my string (NVARCHAR(4000)) is somewherel imited
> to a number of concatenations or stuff like hat. Instead of doing everytime
> the "SET @.strSQL = @.strSQL + ' + ' + ''') AND (''' + ' + ' + @.strField "
> everytime, it suddenly stops adding the @.strField, even when there is a
> value in it...
> Just check it out, I added the returned values...
> Anybody who can help with this would be reallt appreciated, i can't find
> anything about this on google :-(
> Pieter
> ***************************************** My Query
> CREATE TABLE #tmp
> (COLUMN_NAME nvarchar(100),DATA_TYPE varchar(20))
> INSERT INTO #tmp
> EXEC spSyncKeyColumns 'STOCK2'
> /* Make the cursor:*/
> DECLARE curC CURSOR
> FOR
> SELECT /*col.COLUMN_NAME, col.DATA_TYPE, */
> CASE col.DATA_TYPE
> WHEN 'char' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + ''' + ' + '
> + col.COLUMN_NAME + ' + ' + ''' + ' )'
> WHEN 'nchar' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + ''' + ' +
> ' + col.COLUMN_NAME + ' + ' + ''' + ' )'
> WHEN 'varchar' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + ''' + '
> + ' + col.COLUMN_NAME + ' + ' + ''' + ' )'
> WHEN 'nvarchar' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + ''' + '
> + ' + col.COLUMN_NAME + ' + ' + ''' + ' )'
> WHEN 'text' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + ''' + ' + '
> + col.COLUMN_NAME + ' + ' + ''' + ' )'
> WHEN 'ntext' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + ''' + ' +
> ' + col.COLUMN_NAME + ' + ' + ''' + ' )'
> WHEN 'uniqueidentifier' THEN '(''' + col.COLUMN_NAME + ' = '' + ' +
> ''' + ' + ' + col.COLUMN_NAME + ' + ' + ''' + ' )'
> WHEN 'datetime' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + ''' + '
> + CAST(CONVERT(datetime, ' + col.COLUMN_NAME + ', 126) AS NVARCHAR) + ' +
> ''' + ' )'
> WHEN 'smalldatetime' THEN '(''' + col.COLUMN_NAME + ' = '' + ' +
> ''' + ' + CAST(CONVERT(smalldatetime, ' + col.COLUMN_NAME + ', 126)
> AS NVARCHAR) + ' + ''' + ' )'
> WHEN 'timestamp' THEN '(''' + col.COLUMN_NAME + ' = '' + ' +
> col.COLUMN_NAME + ' )'
> ELSE '(''' + col.COLUMN_NAME + ' = '' + CAST(' + col.COLUMN_NAME + ' AS
> NVARCHAR) )'
> END AS Syntax
> FROM #tmp col
> /* Open the Cursor*/
> OPEN curC
> /* Declaration of the variables*/
> DECLARE @.strSQL nvarchar(4000), @.strField NVARCHAR(100)
> SET @.strSQL = ''
> /*FIRST*/
> FETCH NEXT FROM curC INTO @.strField
> BEGIN
> SET @.strSQL = @.strField
> SELECT @.strField
> END
> /*THE OTHERS*/
> FETCH NEXT FROM curC INTO @.strField
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.strSQL = @.strSQL + ' + ' + ''') AND (''' + ' + ' + @.strField
> SELECT @.strField
> /* Take next Table*/
> FETCH NEXT FROM curC INTO @.strField
> END
> /* Fermes le Cursor*/
> CLOSE curC
> DEALLOCATE curC
> DROP TABLE #tmp
> SET @.strSQL = '( ''('' + ' + @.strSQL + ' + '')'' ) AS TWhere'
> SELECT COALESCE(@.strSQL, 'NULL AS TWhere')
> GO
> ***************************************** End of My Query
>
> ***************************************** The Results:
> (5 row(s) affected)
>
> ----
> --
> ('CODE_SOCIETE = ' + '''' + CODE_SOCIETE + '''' )
> (1 row(s) affected) -> My First @.strField
>
> ----
> --
> ('ID_TYPE = ' + CAST(ID_TYPE AS NVARCHAR) )
> (1 row(s) affected) -> My 2nd @.strField
>
> ----
> --
> ('NUM_SITE = ' + CAST(NUM_SITE AS NVARCHAR) )
> (1 row(s) affected) -> My 3nd @.strField
>
> ----
> --
> ('ID_CONTAINER = ' + CAST(ID_CONTAINER AS NVARCHAR) )
> (1 row(s) affected) -> My 4th @.strField
>
> ----
> --
> ('DATE_TIME = ' + DATE_TIME )
> (1 row(s) affected) -> My 5th @.strField
>
> ----
> ----
> ----
> --
> ( '(' + ('CODE_SOCIETE = ' + '''' + CODE_SOCIETE + '''' ) + ') AND (' +
> ('ID_TYPE = ' + CAST(ID_TYPE AS NVARCHAR) ) + ') AND (' + ('NUM_SITE = ' +
> CAST(NUM_SITE AS NVARCHAR) ) + ') AND (' + ('ID_CONTAINER = ' +
> CAST(ID_CONTAINER AS NVARCHAR) ) + ') AND ('
> (1 row(s) affected) -> The final @.strSQL... As you can see: the
> 5th @.strField isn't added, although the ") AND (" is added...
>
> Can anybody help me with this absolutely weird stuff? Thank a lot in
> advance!!
>
>
>|||Dragu
What do you want to accomplish?
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:OOZz9SzlFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm having this realy weird stuff going on here with a string
> concatenation
> I do in a Cursor. It seems my string (NVARCHAR(4000)) is somewherel imited
> to a number of concatenations or stuff like hat. Instead of doing
> everytime
> the "SET @.strSQL = @.strSQL + ' + ' + ''') AND (''' + ' + ' + @.strField "
> everytime, it suddenly stops adding the @.strField, even when there is a
> value in it...
> Just check it out, I added the returned values...
> Anybody who can help with this would be reallt appreciated, i can't find
> anything about this on google :-(
> Pieter
> ***************************************** My Query
> CREATE TABLE #tmp
> (COLUMN_NAME nvarchar(100),DATA_TYPE varchar(20))
> INSERT INTO #tmp
> EXEC spSyncKeyColumns 'STOCK2'
> /* Make the cursor:*/
> DECLARE curC CURSOR
> FOR
> SELECT /*col.COLUMN_NAME, col.DATA_TYPE, */
> CASE col.DATA_TYPE
> WHEN 'char' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + ''' + ' +
> '
> + col.COLUMN_NAME + ' + ' + ''' + ' )'
> WHEN 'nchar' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + ''' + ' +
> ' + col.COLUMN_NAME + ' + ' + ''' + ' )'
> WHEN 'varchar' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + ''' + '
> + ' + col.COLUMN_NAME + ' + ' + ''' + ' )'
> WHEN 'nvarchar' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + ''' +
> '
> + ' + col.COLUMN_NAME + ' + ' + ''' + ' )'
> WHEN 'text' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + ''' + ' +
> '
> + col.COLUMN_NAME + ' + ' + ''' + ' )'
> WHEN 'ntext' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + ''' + ' +
> ' + col.COLUMN_NAME + ' + ' + ''' + ' )'
> WHEN 'uniqueidentifier' THEN '(''' + col.COLUMN_NAME + ' = '' + ' +
> ''' + ' + ' + col.COLUMN_NAME + ' + ' + ''' + ' )'
> WHEN 'datetime' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + ''' +
> '
> + CAST(CONVERT(datetime, ' + col.COLUMN_NAME + ', 126) AS NVARCHAR) + ' +
> ''' + ' )'
> WHEN 'smalldatetime' THEN '(''' + col.COLUMN_NAME + ' = '' + ' +
> ''' + ' + CAST(CONVERT(smalldatetime, ' + col.COLUMN_NAME + ',
> 126)
> AS NVARCHAR) + ' + ''' + ' )'
> WHEN 'timestamp' THEN '(''' + col.COLUMN_NAME + ' = '' + ' +
> col.COLUMN_NAME + ' )'
> ELSE '(''' + col.COLUMN_NAME + ' = '' + CAST(' + col.COLUMN_NAME + ' AS
> NVARCHAR) )'
> END AS Syntax
> FROM #tmp col
> /* Open the Cursor*/
> OPEN curC
> /* Declaration of the variables*/
> DECLARE @.strSQL nvarchar(4000), @.strField NVARCHAR(100)
> SET @.strSQL = ''
> /*FIRST*/
> FETCH NEXT FROM curC INTO @.strField
> BEGIN
> SET @.strSQL = @.strField
> SELECT @.strField
> END
> /*THE OTHERS*/
> FETCH NEXT FROM curC INTO @.strField
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> SET @.strSQL = @.strSQL + ' + ' + ''') AND (''' + ' + ' + @.strField
> SELECT @.strField
> /* Take next Table*/
> FETCH NEXT FROM curC INTO @.strField
> END
> /* Fermes le Cursor*/
> CLOSE curC
> DEALLOCATE curC
> DROP TABLE #tmp
> SET @.strSQL = '( ''('' + ' + @.strSQL + ' + '')'' ) AS TWhere'
> SELECT COALESCE(@.strSQL, 'NULL AS TWhere')
> GO
> ***************************************** End of My Query
>
> ***************************************** The Results:
> (5 row(s) affected)
>
> ----
> --
> ('CODE_SOCIETE = ' + '''' + CODE_SOCIETE + '''' )
> (1 row(s) affected) -> My First @.strField
>
> ----
> --
> ('ID_TYPE = ' + CAST(ID_TYPE AS NVARCHAR) )
> (1 row(s) affected) -> My 2nd @.strField
>
> ----
> --
> ('NUM_SITE = ' + CAST(NUM_SITE AS NVARCHAR) )
> (1 row(s) affected) -> My 3nd @.strField
>
> ----
> --
> ('ID_CONTAINER = ' + CAST(ID_CONTAINER AS NVARCHAR) )
> (1 row(s) affected) -> My 4th @.strField
>
> ----
> --
> ('DATE_TIME = ' + DATE_TIME )
> (1 row(s) affected) -> My 5th @.strField
>
> ----
> ----
> ----
> --
> ( '(' + ('CODE_SOCIETE = ' + '''' + CODE_SOCIETE + '''' ) + ') AND (' +
> ('ID_TYPE = ' + CAST(ID_TYPE AS NVARCHAR) ) + ') AND (' + ('NUM_SITE = ' +
> CAST(NUM_SITE AS NVARCHAR) ) + ') AND (' + ('ID_CONTAINER = ' +
> CAST(ID_CONTAINER AS NVARCHAR) ) + ') AND ('
> (1 row(s) affected) -> The final @.strSQL... As you can see: the
> 5th @.strField isn't added, although the ") AND (" is added...
>
> Can anybody help me with this absolutely weird stuff? Thank a lot in
> advance!!
>
>|||Yes but is not null! As you can see the 5th time is has a value!
"Jens Süßmeyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
wrote in message news:5B086194-C01B-4016-95DD-76B2438BA400@.microsoft.com...
> When using string concatenation you should always keep in mind the option
> that a column / variable can be null --> ISNULL(SomeColumn,'')
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "DraguVaso" wrote:
> > Hi,
> >
> > I'm having this realy weird stuff going on here with a string
concatenation
> > I do in a Cursor. It seems my string (NVARCHAR(4000)) is somewherel
imited
> > to a number of concatenations or stuff like hat. Instead of doing
everytime
> > the "SET @.strSQL = @.strSQL + ' + ' + ''') AND (''' + ' + ' + @.strField
"
> > everytime, it suddenly stops adding the @.strField, even when there is a
> > value in it...
> >
> > Just check it out, I added the returned values...
> >
> > Anybody who can help with this would be reallt appreciated, i can't find
> > anything about this on google :-(
> >
> > Pieter
> >
> > ***************************************** My Query
> > CREATE TABLE #tmp
> > (COLUMN_NAME nvarchar(100),DATA_TYPE varchar(20))
> > INSERT INTO #tmp
> > EXEC spSyncKeyColumns 'STOCK2'
> >
> > /* Make the cursor:*/
> > DECLARE curC CURSOR
> > FOR
> > SELECT /*col.COLUMN_NAME, col.DATA_TYPE, */
> > CASE col.DATA_TYPE
> > WHEN 'char' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + ''' + '
+ '
> > + col.COLUMN_NAME + ' + ' + ''' + ' )'
> > WHEN 'nchar' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + ''' +
' +
> > ' + col.COLUMN_NAME + ' + ' + ''' + ' )'
> > WHEN 'varchar' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + '''
+ '
> > + ' + col.COLUMN_NAME + ' + ' + ''' + ' )'
> > WHEN 'nvarchar' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + '''
+ '
> > + ' + col.COLUMN_NAME + ' + ' + ''' + ' )'
> > WHEN 'text' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + ''' + '
+ '
> > + col.COLUMN_NAME + ' + ' + ''' + ' )'
> > WHEN 'ntext' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + ''' +
' +
> > ' + col.COLUMN_NAME + ' + ' + ''' + ' )'
> > WHEN 'uniqueidentifier' THEN '(''' + col.COLUMN_NAME + ' = '' + ' +
> > ''' + ' + ' + col.COLUMN_NAME + ' + ' + ''' + ' )'
> > WHEN 'datetime' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + '''
+ '
> > + CAST(CONVERT(datetime, ' + col.COLUMN_NAME + ', 126) AS NVARCHAR) + '
+
> > ''' + ' )'
> > WHEN 'smalldatetime' THEN '(''' + col.COLUMN_NAME + ' = '' + ' +
> > ''' + ' + CAST(CONVERT(smalldatetime, ' + col.COLUMN_NAME + ',
126)
> > AS NVARCHAR) + ' + ''' + ' )'
> > WHEN 'timestamp' THEN '(''' + col.COLUMN_NAME + ' = '' + ' +
> > col.COLUMN_NAME + ' )'
> > ELSE '(''' + col.COLUMN_NAME + ' = '' + CAST(' + col.COLUMN_NAME + '
AS
> > NVARCHAR) )'
> > END AS Syntax
> > FROM #tmp col
> >
> > /* Open the Cursor*/
> > OPEN curC
> > /* Declaration of the variables*/
> > DECLARE @.strSQL nvarchar(4000), @.strField NVARCHAR(100)
> >
> > SET @.strSQL = ''
> >
> > /*FIRST*/
> > FETCH NEXT FROM curC INTO @.strField
> > BEGIN
> > SET @.strSQL = @.strField
> > SELECT @.strField
> > END
> >
> > /*THE OTHERS*/
> > FETCH NEXT FROM curC INTO @.strField
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > SET @.strSQL = @.strSQL + ' + ' + ''') AND (''' + ' + ' + @.strField
> > SELECT @.strField
> > /* Take next Table*/
> > FETCH NEXT FROM curC INTO @.strField
> > END
> >
> > /* Fermes le Cursor*/
> > CLOSE curC
> > DEALLOCATE curC
> >
> > DROP TABLE #tmp
> >
> > SET @.strSQL = '( ''('' + ' + @.strSQL + ' + '')'' ) AS TWhere'
> > SELECT COALESCE(@.strSQL, 'NULL AS TWhere')
> > GO
> > ***************************************** End of My Query
> >
> >
> > ***************************************** The Results:
> >
> > (5 row(s) affected)
> >
> >
> ----
--
> > --
> > ('CODE_SOCIETE = ' + '''' + CODE_SOCIETE + '''' )
> >
> > (1 row(s) affected) -> My First @.strField
> >
> >
> ----
--
> > --
> > ('ID_TYPE = ' + CAST(ID_TYPE AS NVARCHAR) )
> >
> > (1 row(s) affected) -> My 2nd @.strField
> >
> >
> ----
--
> > --
> > ('NUM_SITE = ' + CAST(NUM_SITE AS NVARCHAR) )
> >
> > (1 row(s) affected) -> My 3nd @.strField
> >
> >
> ----
--
> > --
> > ('ID_CONTAINER = ' + CAST(ID_CONTAINER AS NVARCHAR) )
> >
> > (1 row(s) affected) -> My 4th @.strField
> >
> >
> ----
--
> > --
> > ('DATE_TIME = ' + DATE_TIME )
> >
> > (1 row(s) affected) -> My 5th @.strField
> >
> >
> ----
--
> ----
--
> ----
--
> > --
> > ( '(' + ('CODE_SOCIETE = ' + '''' + CODE_SOCIETE + '''' ) + ') AND (' +
> > ('ID_TYPE = ' + CAST(ID_TYPE AS NVARCHAR) ) + ') AND (' + ('NUM_SITE = '
+
> > CAST(NUM_SITE AS NVARCHAR) ) + ') AND (' + ('ID_CONTAINER = ' +
> > CAST(ID_CONTAINER AS NVARCHAR) ) + ') AND ('
> >
> > (1 row(s) affected) -> The final @.strSQL... As you can see:
the
> > 5th @.strField isn't added, although the ") AND (" is added...
> >
> >
> > Can anybody help me with this absolutely weird stuff? Thank a lot in
> > advance!!
> >
> >
> >
> >
> >|||Well, I want to concatenated all those things, to create the WHERE clause of
a SELECT-query.
It's kidn of complicated the whole project: I'm making this for some kind of
replication-database: When Deleteing a record, the delete-trigger makes me
the WHERE clause of the delete-statement. I need that delete-statement to go
delete the same record in another database :-)
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uaIHWazlFHA.3256@.tk2msftngp13.phx.gbl...
> Dragu
> What do you want to accomplish?
>
> "DraguVaso" <pietercoucke@.hotmail.com> wrote in message
> news:OOZz9SzlFHA.2472@.TK2MSFTNGP15.phx.gbl...
> > Hi,
> >
> > I'm having this realy weird stuff going on here with a string
> > concatenation
> > I do in a Cursor. It seems my string (NVARCHAR(4000)) is somewherel
imited
> > to a number of concatenations or stuff like hat. Instead of doing
> > everytime
> > the "SET @.strSQL = @.strSQL + ' + ' + ''') AND (''' + ' + ' + @.strField
"
> > everytime, it suddenly stops adding the @.strField, even when there is a
> > value in it...
> >
> > Just check it out, I added the returned values...
> >
> > Anybody who can help with this would be reallt appreciated, i can't find
> > anything about this on google :-(
> >
> > Pieter
> >
> > ***************************************** My Query
> > CREATE TABLE #tmp
> > (COLUMN_NAME nvarchar(100),DATA_TYPE varchar(20))
> > INSERT INTO #tmp
> > EXEC spSyncKeyColumns 'STOCK2'
> >
> > /* Make the cursor:*/
> > DECLARE curC CURSOR
> > FOR
> > SELECT /*col.COLUMN_NAME, col.DATA_TYPE, */
> > CASE col.DATA_TYPE
> > WHEN 'char' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + ''' + '
+
> > '
> > + col.COLUMN_NAME + ' + ' + ''' + ' )'
> > WHEN 'nchar' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + ''' + '
+
> > ' + col.COLUMN_NAME + ' + ' + ''' + ' )'
> > WHEN 'varchar' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + ''' +
'
> > + ' + col.COLUMN_NAME + ' + ' + ''' + ' )'
> > WHEN 'nvarchar' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + '''
+
> > '
> > + ' + col.COLUMN_NAME + ' + ' + ''' + ' )'
> > WHEN 'text' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + ''' + '
+
> > '
> > + col.COLUMN_NAME + ' + ' + ''' + ' )'
> > WHEN 'ntext' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + ''' + '
+
> > ' + col.COLUMN_NAME + ' + ' + ''' + ' )'
> > WHEN 'uniqueidentifier' THEN '(''' + col.COLUMN_NAME + ' = '' + ' +
> > ''' + ' + ' + col.COLUMN_NAME + ' + ' + ''' + ' )'
> > WHEN 'datetime' THEN '(''' + col.COLUMN_NAME + ' = '' + ' + '''
+
> > '
> > + CAST(CONVERT(datetime, ' + col.COLUMN_NAME + ', 126) AS NVARCHAR) + '
+
> > ''' + ' )'
> > WHEN 'smalldatetime' THEN '(''' + col.COLUMN_NAME + ' = '' + ' +
> > ''' + ' + CAST(CONVERT(smalldatetime, ' + col.COLUMN_NAME + ',
> > 126)
> > AS NVARCHAR) + ' + ''' + ' )'
> > WHEN 'timestamp' THEN '(''' + col.COLUMN_NAME + ' = '' + ' +
> > col.COLUMN_NAME + ' )'
> > ELSE '(''' + col.COLUMN_NAME + ' = '' + CAST(' + col.COLUMN_NAME + ' AS
> > NVARCHAR) )'
> > END AS Syntax
> > FROM #tmp col
> >
> > /* Open the Cursor*/
> > OPEN curC
> > /* Declaration of the variables*/
> > DECLARE @.strSQL nvarchar(4000), @.strField NVARCHAR(100)
> >
> > SET @.strSQL = ''
> >
> > /*FIRST*/
> > FETCH NEXT FROM curC INTO @.strField
> > BEGIN
> > SET @.strSQL = @.strField
> > SELECT @.strField
> > END
> >
> > /*THE OTHERS*/
> > FETCH NEXT FROM curC INTO @.strField
> > WHILE @.@.FETCH_STATUS = 0
> > BEGIN
> > SET @.strSQL = @.strSQL + ' + ' + ''') AND (''' + ' + ' + @.strField
> > SELECT @.strField
> > /* Take next Table*/
> > FETCH NEXT FROM curC INTO @.strField
> > END
> >
> > /* Fermes le Cursor*/
> > CLOSE curC
> > DEALLOCATE curC
> >
> > DROP TABLE #tmp
> >
> > SET @.strSQL = '( ''('' + ' + @.strSQL + ' + '')'' ) AS TWhere'
> > SELECT COALESCE(@.strSQL, 'NULL AS TWhere')
> > GO
> > ***************************************** End of My Query
> >
> >
> > ***************************************** The Results:
> >
> > (5 row(s) affected)
> >
> >
> ----
--
> > --
> > ('CODE_SOCIETE = ' + '''' + CODE_SOCIETE + '''' )
> >
> > (1 row(s) affected) -> My First @.strField
> >
> >
> ----
--
> > --
> > ('ID_TYPE = ' + CAST(ID_TYPE AS NVARCHAR) )
> >
> > (1 row(s) affected) -> My 2nd @.strField
> >
> >
> ----
--
> > --
> > ('NUM_SITE = ' + CAST(NUM_SITE AS NVARCHAR) )
> >
> > (1 row(s) affected) -> My 3nd @.strField
> >
> >
> ----
--
> > --
> > ('ID_CONTAINER = ' + CAST(ID_CONTAINER AS NVARCHAR) )
> >
> > (1 row(s) affected) -> My 4th @.strField
> >
> >
> ----
--
> > --
> > ('DATE_TIME = ' + DATE_TIME )
> >
> > (1 row(s) affected) -> My 5th @.strField
> >
> >
> ----
--
> ----
--
> ----
--
> > --
> > ( '(' + ('CODE_SOCIETE = ' + '''' + CODE_SOCIETE + '''' ) + ') AND (' +
> > ('ID_TYPE = ' + CAST(ID_TYPE AS NVARCHAR) ) + ') AND (' + ('NUM_SITE = '
+
> > CAST(NUM_SITE AS NVARCHAR) ) + ') AND (' + ('ID_CONTAINER = ' +
> > CAST(ID_CONTAINER AS NVARCHAR) ) + ') AND ('
> >
> > (1 row(s) affected) -> The final @.strSQL... As you can see:
the
> > 5th @.strField isn't added, although the ") AND (" is added...
> >
> >
> > Can anybody help me with this absolutely weird stuff? Thank a lot in
> > advance!!
> >
> >
> >
> >
>|||Hi There,
Ther is nothing wrong with the approach . You should check the
settings under
Tools ->Options -->Results(Tab) -->Maximum Characters per Column set it
to 4000 or so.
I hope it will solve your problem.
With warm regards
Jatinder Singh|||Thanks! that did indeed the trick!!
Damn, Ihave been searching for hours on this problem :-(
Really thanks a lot!
<jatinder.singh@.clovertechnologies.com> wrote in message
news:1122970837.535624.191310@.o13g2000cwo.googlegroups.com...
> Hi There,
> Ther is nothing wrong with the approach . You should check the
> settings under
> Tools ->Options -->Results(Tab) -->Maximum Characters per Column set it
> to 4000 or so.
> I hope it will solve your problem.
> With warm regards
> Jatinder Singh
>

Tuesday, March 6, 2012

About unicode string comparison

Hi,
Can anyone here explain why the 2 Japanese strings are equal?
select case when N'あきよ' = N'ア_ヨ' then 'true' else 'false' end
select convert(varbinary,N'あきよ')
select convert(varbinary,N'ア_ヨ')
Thanks,
JamesJames,
They are equal under a Kana-insensitive collation. If you need to
distinguish them, use a Kana-sensitive collation. Equality of strings
is based on the rules of the collation in effect, not the strings' binary
representations.
select
case when N'あきよ' = N'ア_ヨ' collate Latin1_General_CS_AS_KS_WS
then 'true' else 'false'
end
This returns 'false'
Steve Kass
Drew University
James Ma wrote:

>Hi,
>Can anyone here explain why the 2 Japanese strings are equal?
>select case when N'あきよ' = N'ア_ヨ' then 'true' else 'false' end
>
>select convert(varbinary,N'あきよ')
>select convert(varbinary,N'ア_ヨ')
>Thanks,
>James
>|||Thank you so much.
"Steve Kass" wrote:

> James,
> They are equal under a Kana-insensitive collation. If you need to
> distinguish them, use a Kana-sensitive collation. Equality of strings
> is based on the rules of the collation in effect, not the strings' binary
> representations.
> select
> case when N'あきよ' = N'ア_ヨ' collate Latin1_General_CS_AS_KS_WS
> then 'true' else 'false'
> end
> This returns 'false'
> Steve Kass
> Drew University
> James Ma wrote:
>
>

Saturday, February 25, 2012

about SQL String (security question)

I have a SQL Query String like below..

string SQLUpd = "UPDATE Member SET Member_pwd = '" + pwd + "',Member_nickname = '" + NickName + "',Member_mail = '" + Mail + "',Member_birthday = '" + BDay + "', Member_gende_no = " + Gender + ",Member_mobile = '" + Mobile + "' ,Member_tel_day = '" + DTel + "',Member_tel_night = '" + NTel + "',Member_occupy_no = " + occupy + ",Member_national_no = " + National + ",Member_area_no = " + Area + ",Member_address = '" + Address + "' WHERE (Member_no = " + MemberNo + " )";

in my program it does work perfectly ... but now I just recalled my teacher have told.. it may cause security problem in that way...

maybe I need to change 【UPDATE Member SET Member_pwd = '" + pwd +】 to【UPDATE Member SET Member_pwd = '" + @.pwd +】

is there any difference between pwd and @.pwd?

thank you very much

I think what your teacher suggested is to use Parameterized Queries. There are a few benefits to it - primarily - security, maintenance/ease of coding. Please google for more info.I can type up a couple of lines but you will learn more from the articles already published.|||

Using this dynamic query building with strings is bad. Users can potentially inject malicious code into your queries. I have included a helpful link for you to review:http://www.4guysfromrolla.com/webtech/092601-1.shtml

Good Luck!

|||

thanks for you all (very much)

do you have a example for C#... shame on me I'm not similiar with VB...

I just trying to fix my original code . can you give me some suggestion?

except I have to change pwd to @.pwd.. is something else I've to add or edit?

thank you very much

================start of original code ============================

string strUpd = "data Source=x.x.x.x;user=sa;password=1234 ;initial catalog=English";
string SQLUpd = "UPDATE Member SET Member_pwd = '" + pwd + "',Member_nickname = '" + NickName + "',Member_mail = '" + Mail + "',Member_birthday = '" + BDay + "', Member_gende_no = " + Gender + ",Member_mobile = '" + Mobile + "' ,Member_tel_day = '" + DTel + "',Member_tel_night = '" + NTel + "',Member_occupy_no = " + occupy + ",Member_national_no = " + National + ",Member_area_no = " + Area + ",Member_address = '" + Address + "' WHERE (Member_no = " + MemberNo + " )";
SqlConnection connUPD = new SqlConnection(strUpd);
SqlCommand cmdUpd = new SqlCommand(SQLUpd, connUPD);
cmdUpd.CommandType = CommandType.Text;

connUPD.Open();
cmdUpd.ExecuteNonQuery();

connUPD.Dispose();
connUPD.Close();

================end of original code ============================

================fixed code ===================================

string strUpd = "data Source=x.x.x.x;user=sa;password=1234 ;initial catalog=English";
string SQLUpd = "UPDATE Member SET Member_pwd = '" + @.pwd + "',Member_nickname = '" + NickName + "',Member_mail = '" + Mail + "',Member_birthday = '" + BDay + "', Member_gende_no = " + Gender + ",Member_mobile = '" + Mobile + "' ,Member_tel_day = '" + DTel + "',Member_tel_night = '" + NTel + "',Member_occupy_no = " + occupy + ",Member_national_no = " + National + ",Member_area_no = " + Area + ",Member_address = '" + Address + "' WHERE (Member_no = " + MemberNo + " )";
SqlConnection connUPD = new SqlConnection(strUpd);
SqlCommand cmdUpd = new SqlCommand(SQLUpd, connUPD);
cmdUpd.CommandType = CommandType.Text;

connUPD.Open();
cmdUpd.ExecuteNonQuery();

connUPD.Dispose();
connUPD.Close();

|||The post is marked as answered. So I am assuming you got your solution?|||

no no no... I'm sorry

should I post a new topic for extra question?

I still have many wonder on this filed...

thank you

|||

Hi,

You will need to add the parameters for all the criterias that is used in the query. Not only the pwd.

A typical sample might look like

SqlCommand cmd = new SqlCommand("Select * from Table WHERE [name]=@.name", this.connection);
cmd.Parameters.Add("@.name", SqlDbType.NVarChar, 20);
cmd.Parameters.Value = "Kevin";
SqlDataReader sdr = cmd.ExecuteReader();

Monday, February 13, 2012

about intergrated security

hi all

i am setting the sqlconnection string right this:

server="myserver";Intergrated security = "sspi"; database="mydb";

do i need to add ASPNET account to the sql server in order to get it works?

any dangerous do i need to face when i set the connection string right that?

thanks

Using Integrated Security, the Windows ASPNET user must be a user in the database.

Thursday, March 22, 2012

Access a in house sql server from a remote web server

Thursday, March 8, 2012

absolutely weird String Concatenation problem, and really daring!

absolutely weird String Concatenation problem, and really daring!

absolutely weird String Concatenation problem, and really daring!

Tuesday, March 6, 2012

About unicode string comparison

Saturday, February 25, 2012

about SQL String (security question)

Monday, February 13, 2012

about intergrated security

SQL About AMO

Blog Archive

About Me