Hello:
We are running SQL Server 2000 RS. We are just starting to deploy reports
and we are running into a strange problem.
We have given users access to their reports via various Windows security
groups. When an Sysadmin runs a report, it's runs fine and displays
everything in a timely fashion.
When a regular user runs a report, there is a significant delay while the
Report is Being Generated icon spins. Finally, the report displays but some
graphics are not displayed and are replaced by a box with a red X.
This behavior is consistent on any user who is not a sysadmin.
Any suggestions on how to correct this problem would be appreciated.
ThanksBrennan,
Do the images exist on a location where the group to which the user's
belong does not have permission?
I bet if you set the permission on the image to 'Everyone'/Anonymous
you'll get the images in the report.
Andy Potter
Showing posts with label strange. Show all posts
Showing posts with label strange. Show all posts
Wednesday, March 28, 2012
Wednesday, March 21, 2012
Problems Concatenating Columns
Hi All,
I am getting some rather strange results when concatenating columns.
Please see my inline comments below...
SELECT
'http://www.oursite.co.uk/scripts/prodview.asp?idproduct=' +
CAST(products.idProduct AS Char(4)) AS product_url,
('Replacement '
+
Case When CharIndex(';',Products.description) > 0 Then
Left(Products.description,CharIndex(';',Products.description)-1)
Else
Products.description
End
+
' for '
+
Case When CharIndex('[',allProducts.CatDescMake) > 0 Then
Left(allProducts.CatDescMake,CharIndex('[',allProducts.CatDescMake) - 1)
+'' -- If I dont add this empty string, the concatenation seems to stop
after this point (not so much of a problem, but why?)
Else
allProducts.CatDescMake + '' -- Ditto!
End
--+ allProducts.CatDescModel + products.description -- This fails, only
the
first column ends up in the concatenated column
+ products.description + allProducts.CatDescModel -- This works OK, both
columns are in the concatenated column
) AS name,
products.DescriptionLong as Description,
'http://www.oursite.co.uk/prodimages/' + products.imageURL as image_url,
products.Price as price
FROM Categories_Products
INNER JOIN allProducts ON Categories_Products.idCategory =
allProducts.CatIDModel
INNER JOIN products ON Categories_Products.idProduct = products.idProduct
WHERE products.idProduct IN (select idProduct from products where
description like '%;%')
Any help will be appreciated! :)
Simon.
PS: I know that string concatnation should be done on the
client/application
end, but ASP is not happy about doing with with the amount of records we
have (Times out) where as from what I have seen so far, SQL Server does
the
job ALOT faster!
---
I am using the free version of SPAMfighter for private users.
It has removed 2988 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com for free now!Hi
Check that those columns you are having problems with do not contain nulls.
string + NULL results in NULL
You might need to add to your code ISNULL(column, '') replaces a null with
an empty string.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
news:eEeSzzvAGHA.3456@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am getting some rather strange results when concatenating columns.
> Please see my inline comments below...
> SELECT
> 'http://www.oursite.co.uk/scripts/prodview.asp?idproduct=' +
> CAST(products.idProduct AS Char(4)) AS product_url,
> ('Replacement '
> +
> Case When CharIndex(';',Products.description) > 0 Then
> Left(Products.description,CharIndex(';',Products.description)-1)
> Else
> Products.description
> End
> +
> ' for '
> +
> Case When CharIndex('[',allProducts.CatDescMake) > 0 Then
> Left(allProducts.CatDescMake,CharIndex('[',allProducts.CatDescMake) - 1)
> +'' -- If I dont add this empty string, the concatenation seems to stop
> after this point (not so much of a problem, but why?)
> Else
> allProducts.CatDescMake + '' -- Ditto!
> End
> --+ allProducts.CatDescModel + products.description -- This fails, only
> the
> first column ends up in the concatenated column
> + products.description + allProducts.CatDescModel -- This works OK, both
> columns are in the concatenated column
> ) AS name,
> products.DescriptionLong as Description,
> 'http://www.oursite.co.uk/prodimages/' + products.imageURL as image_url,
> products.Price as price
> FROM Categories_Products
> INNER JOIN allProducts ON Categories_Products.idCategory =
> allProducts.CatIDModel
> INNER JOIN products ON Categories_Products.idProduct = products.idProduct
> WHERE products.idProduct IN (select idProduct from products where
> description like '%;%')
> Any help will be appreciated! :)
> Simon.
> PS: I know that string concatnation should be done on the
> client/application
> end, but ASP is not happy about doing with with the amount of records we
> have (Times out) where as from what I have seen so far, SQL Server does
> the
> job ALOT faster!
>
> ---
> I am using the free version of SPAMfighter for private users.
> It has removed 2988 spam emails to date.
> Paying users do not have this message in their emails.
> Try www.SPAMfighter.com for free now!
>sql
I am getting some rather strange results when concatenating columns.
Please see my inline comments below...
SELECT
'http://www.oursite.co.uk/scripts/prodview.asp?idproduct=' +
CAST(products.idProduct AS Char(4)) AS product_url,
('Replacement '
+
Case When CharIndex(';',Products.description) > 0 Then
Left(Products.description,CharIndex(';',Products.description)-1)
Else
Products.description
End
+
' for '
+
Case When CharIndex('[',allProducts.CatDescMake) > 0 Then
Left(allProducts.CatDescMake,CharIndex('[',allProducts.CatDescMake) - 1)
+'' -- If I dont add this empty string, the concatenation seems to stop
after this point (not so much of a problem, but why?)
Else
allProducts.CatDescMake + '' -- Ditto!
End
--+ allProducts.CatDescModel + products.description -- This fails, only
the
first column ends up in the concatenated column
+ products.description + allProducts.CatDescModel -- This works OK, both
columns are in the concatenated column
) AS name,
products.DescriptionLong as Description,
'http://www.oursite.co.uk/prodimages/' + products.imageURL as image_url,
products.Price as price
FROM Categories_Products
INNER JOIN allProducts ON Categories_Products.idCategory =
allProducts.CatIDModel
INNER JOIN products ON Categories_Products.idProduct = products.idProduct
WHERE products.idProduct IN (select idProduct from products where
description like '%;%')
Any help will be appreciated! :)
Simon.
PS: I know that string concatnation should be done on the
client/application
end, but ASP is not happy about doing with with the amount of records we
have (Times out) where as from what I have seen so far, SQL Server does
the
job ALOT faster!
---
I am using the free version of SPAMfighter for private users.
It has removed 2988 spam emails to date.
Paying users do not have this message in their emails.
Try www.SPAMfighter.com for free now!Hi
Check that those columns you are having problems with do not contain nulls.
string + NULL results in NULL
You might need to add to your code ISNULL(column, '') replaces a null with
an empty string.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Simon Harris" <too-much-spam@.makes-you-fat.com> wrote in message
news:eEeSzzvAGHA.3456@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am getting some rather strange results when concatenating columns.
> Please see my inline comments below...
> SELECT
> 'http://www.oursite.co.uk/scripts/prodview.asp?idproduct=' +
> CAST(products.idProduct AS Char(4)) AS product_url,
> ('Replacement '
> +
> Case When CharIndex(';',Products.description) > 0 Then
> Left(Products.description,CharIndex(';',Products.description)-1)
> Else
> Products.description
> End
> +
> ' for '
> +
> Case When CharIndex('[',allProducts.CatDescMake) > 0 Then
> Left(allProducts.CatDescMake,CharIndex('[',allProducts.CatDescMake) - 1)
> +'' -- If I dont add this empty string, the concatenation seems to stop
> after this point (not so much of a problem, but why?)
> Else
> allProducts.CatDescMake + '' -- Ditto!
> End
> --+ allProducts.CatDescModel + products.description -- This fails, only
> the
> first column ends up in the concatenated column
> + products.description + allProducts.CatDescModel -- This works OK, both
> columns are in the concatenated column
> ) AS name,
> products.DescriptionLong as Description,
> 'http://www.oursite.co.uk/prodimages/' + products.imageURL as image_url,
> products.Price as price
> FROM Categories_Products
> INNER JOIN allProducts ON Categories_Products.idCategory =
> allProducts.CatIDModel
> INNER JOIN products ON Categories_Products.idProduct = products.idProduct
> WHERE products.idProduct IN (select idProduct from products where
> description like '%;%')
> Any help will be appreciated! :)
> Simon.
> PS: I know that string concatnation should be done on the
> client/application
> end, but ASP is not happy about doing with with the amount of records we
> have (Times out) where as from what I have seen so far, SQL Server does
> the
> job ALOT faster!
>
> ---
> I am using the free version of SPAMfighter for private users.
> It has removed 2988 spam emails to date.
> Paying users do not have this message in their emails.
> Try www.SPAMfighter.com for free now!
>sql
Monday, March 12, 2012
Problems accessing data from recordset (SQL Server 2000)
I'm having a strange problem and I was hoping someone could give me
some guidance. I am accessing a Microsoft SQL 2000 server through ASP
scripts on our webserver. The sql server and web server are on the
same network.
This code used to work, and started acting strangely after I moved it
to a new webserver and SQL server (from testing
environment->production). Previously the web server and sql database
were running on the same machine, the software versions are all the
same.
When I do a SELECT statement to retrieve data, I then pull data from
each of the fields I need. The problem is certain fields cause a
strange behavior. When I pull the data from one field, all subsiquent
uses of the RecordSet object return empty when retrieveing data from
other fields. (even though I know all fields contain information)
For example:
set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
response.write rs("FieldA") & "//" & rs("FieldB") & "//" & rs("FieldC")
& "//" & rs("FieldD")
Outputs "AAA//////" whereas:
set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
response.write rs("FieldD") & "//" & rs("FieldC") & "//" & rs("FieldB")
& "//" & rs("FieldA")
Outputs "DDD/////"
Any ideas? Thanks for your help - it is much appreciated!
Lee
CrazyAtlantaGuy wrote:
> When I do a SELECT statement to retrieve data, I then pull data from
> each of the fields I need. The problem is certain fields cause a
> strange behavior. When I pull the data from one field, all subsiquent
> uses of the RecordSet object return empty when retrieveing data from
> other fields. (even though I know all fields contain information)
> For example:
> set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
http://www.aspfaq.com/show.asp?id=2096
> response.write rs("FieldA") & "//" & rs("FieldB") & "//" &
> rs("FieldC") & "//" & rs("FieldD")
> Outputs "AAA//////" whereas:
> set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
> response.write rs("FieldD") & "//" & rs("FieldC") & "//" &
> rs("FieldB") & "//" & rs("FieldA")
> Outputs "DDD/////"
OK, I guess you are using ODBC, and FieldD is a Text column, right? You are
likely running into an old ODBC bug which is described here:
http://www.aspfaq.com/show.asp?id=2188
Switch to using the native SQL OLE DB provider (SQLOLEDB) and this problem
should go away.
http://www.aspfaq.com/show.asp?id=2126
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
|||Thanks Bob!
I'm glad I asked, that was the problem exactly. I've swapped out the
connection strings and using the SQLOLEDB now. Works like a champ.
The quick response is much appreciated,
Lee
some guidance. I am accessing a Microsoft SQL 2000 server through ASP
scripts on our webserver. The sql server and web server are on the
same network.
This code used to work, and started acting strangely after I moved it
to a new webserver and SQL server (from testing
environment->production). Previously the web server and sql database
were running on the same machine, the software versions are all the
same.
When I do a SELECT statement to retrieve data, I then pull data from
each of the fields I need. The problem is certain fields cause a
strange behavior. When I pull the data from one field, all subsiquent
uses of the RecordSet object return empty when retrieveing data from
other fields. (even though I know all fields contain information)
For example:
set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
response.write rs("FieldA") & "//" & rs("FieldB") & "//" & rs("FieldC")
& "//" & rs("FieldD")
Outputs "AAA//////" whereas:
set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
response.write rs("FieldD") & "//" & rs("FieldC") & "//" & rs("FieldB")
& "//" & rs("FieldA")
Outputs "DDD/////"
Any ideas? Thanks for your help - it is much appreciated!
Lee
CrazyAtlantaGuy wrote:
> When I do a SELECT statement to retrieve data, I then pull data from
> each of the fields I need. The problem is certain fields cause a
> strange behavior. When I pull the data from one field, all subsiquent
> uses of the RecordSet object return empty when retrieveing data from
> other fields. (even though I know all fields contain information)
> For example:
> set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
http://www.aspfaq.com/show.asp?id=2096
> response.write rs("FieldA") & "//" & rs("FieldB") & "//" &
> rs("FieldC") & "//" & rs("FieldD")
> Outputs "AAA//////" whereas:
> set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
> response.write rs("FieldD") & "//" & rs("FieldC") & "//" &
> rs("FieldB") & "//" & rs("FieldA")
> Outputs "DDD/////"
OK, I guess you are using ODBC, and FieldD is a Text column, right? You are
likely running into an old ODBC bug which is described here:
http://www.aspfaq.com/show.asp?id=2188
Switch to using the native SQL OLE DB provider (SQLOLEDB) and this problem
should go away.
http://www.aspfaq.com/show.asp?id=2126
Bob Barrows
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
|||Thanks Bob!
I'm glad I asked, that was the problem exactly. I've swapped out the
connection strings and using the SQLOLEDB now. Works like a champ.
The quick response is much appreciated,
Lee
Problems accessing data from recordset (SQL Server 2000)
I'm having a strange problem and I was hoping someone could give me
some guidance. I am accessing a Microsoft SQL 2000 server through ASP
scripts on our webserver. The sql server and web server are on the
same network.
This code used to work, and started acting strangely after I moved it
to a new webserver and SQL server (from testing
environment->production). Previously the web server and sql database
were running on the same machine, the software versions are all the
same.
When I do a SELECT statement to retrieve data, I then pull data from
each of the fields I need. The problem is certain fields cause a
strange behavior. When I pull the data from one field, all subsiquent
uses of the RecordSet object return empty when retrieveing data from
other fields. (even though I know all fields contain information)
For example:
set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
response.write rs("FieldA") & "//" & rs("FieldB") & "//" & rs("FieldC")
& "//" & rs("FieldD")
Outputs "AAA//////" whereas:
set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
response.write rs("FieldD") & "//" & rs("FieldC") & "//" & rs("FieldB")
& "//" & rs("FieldA")
Outputs "DDD/////"
Any ideas? Thanks for your help - it is much appreciated!
LeeCrazyAtlantaGuy wrote:
> When I do a SELECT statement to retrieve data, I then pull data from
> each of the fields I need. The problem is certain fields cause a
> strange behavior. When I pull the data from one field, all subsiquent
> uses of the RecordSet object return empty when retrieveing data from
> other fields. (even though I know all fields contain information)
> For example:
> set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
http://www.aspfaq.com/show.asp?id=2096
> response.write rs("FieldA") & "//" & rs("FieldB") & "//" &
> rs("FieldC") & "//" & rs("FieldD")
> Outputs "AAA//////" whereas:
> set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
> response.write rs("FieldD") & "//" & rs("FieldC") & "//" &
> rs("FieldB") & "//" & rs("FieldA")
> Outputs "DDD/////"
OK, I guess you are using ODBC, and FieldD is a Text column, right? You are
likely running into an old ODBC bug which is described here:
http://www.aspfaq.com/show.asp?id=2188
Switch to using the native SQL OLE DB provider (SQLOLEDB) and this problem
should go away.
http://www.aspfaq.com/show.asp?id=2126
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks Bob!
I'm glad I asked, that was the problem exactly. I've swapped out the
connection strings and using the SQLOLEDB now. Works like a champ.
The quick response is much appreciated,
Lee
some guidance. I am accessing a Microsoft SQL 2000 server through ASP
scripts on our webserver. The sql server and web server are on the
same network.
This code used to work, and started acting strangely after I moved it
to a new webserver and SQL server (from testing
environment->production). Previously the web server and sql database
were running on the same machine, the software versions are all the
same.
When I do a SELECT statement to retrieve data, I then pull data from
each of the fields I need. The problem is certain fields cause a
strange behavior. When I pull the data from one field, all subsiquent
uses of the RecordSet object return empty when retrieveing data from
other fields. (even though I know all fields contain information)
For example:
set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
response.write rs("FieldA") & "//" & rs("FieldB") & "//" & rs("FieldC")
& "//" & rs("FieldD")
Outputs "AAA//////" whereas:
set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
response.write rs("FieldD") & "//" & rs("FieldC") & "//" & rs("FieldB")
& "//" & rs("FieldA")
Outputs "DDD/////"
Any ideas? Thanks for your help - it is much appreciated!
LeeCrazyAtlantaGuy wrote:
> When I do a SELECT statement to retrieve data, I then pull data from
> each of the fields I need. The problem is certain fields cause a
> strange behavior. When I pull the data from one field, all subsiquent
> uses of the RecordSet object return empty when retrieveing data from
> other fields. (even though I know all fields contain information)
> For example:
> set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
http://www.aspfaq.com/show.asp?id=2096
> response.write rs("FieldA") & "//" & rs("FieldB") & "//" &
> rs("FieldC") & "//" & rs("FieldD")
> Outputs "AAA//////" whereas:
> set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
> response.write rs("FieldD") & "//" & rs("FieldC") & "//" &
> rs("FieldB") & "//" & rs("FieldA")
> Outputs "DDD/////"
OK, I guess you are using ODBC, and FieldD is a Text column, right? You are
likely running into an old ODBC bug which is described here:
http://www.aspfaq.com/show.asp?id=2188
Switch to using the native SQL OLE DB provider (SQLOLEDB) and this problem
should go away.
http://www.aspfaq.com/show.asp?id=2126
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks Bob!
I'm glad I asked, that was the problem exactly. I've swapped out the
connection strings and using the SQLOLEDB now. Works like a champ.
The quick response is much appreciated,
Lee
Problems accessing data from recordset (SQL Server 2000)
I'm having a strange problem and I was hoping someone could give me
some guidance. I am accessing a Microsoft SQL 2000 server through ASP
scripts on our webserver. The sql server and web server are on the
same network.
This code used to work, and started acting strangely after I moved it
to a new webserver and SQL server (from testing
environment->production). Previously the web server and sql database
were running on the same machine, the software versions are all the
same.
When I do a SELECT statement to retrieve data, I then pull data from
each of the fields I need. The problem is certain fields cause a
strange behavior. When I pull the data from one field, all subsiquent
uses of the RecordSet object return empty when retrieveing data from
other fields. (even though I know all fields contain information)
For example:
set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
response.write rs("FieldA") & "//" & rs("FieldB") & "//" & rs("FieldC")
& "//" & rs("FieldD")
Outputs "AAA//////" whereas:
set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
response.write rs("FieldD") & "//" & rs("FieldC") & "//" & rs("FieldB")
& "//" & rs("FieldA")
Outputs "DDD/////"
Any ideas? Thanks for your help - it is much appreciated!
LeeCrazyAtlantaGuy wrote:
> When I do a SELECT statement to retrieve data, I then pull data from
> each of the fields I need. The problem is certain fields cause a
> strange behavior. When I pull the data from one field, all subsiquent
> uses of the RecordSet object return empty when retrieveing data from
> other fields. (even though I know all fields contain information)
> For example:
> set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
http://www.aspfaq.com/show.asp?id=2096
> response.write rs("FieldA") & "//" & rs("FieldB") & "//" &
> rs("FieldC") & "//" & rs("FieldD")
> Outputs "AAA//////" whereas:
> set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
> response.write rs("FieldD") & "//" & rs("FieldC") & "//" &
> rs("FieldB") & "//" & rs("FieldA")
> Outputs "DDD/////"
OK, I guess you are using ODBC, and FieldD is a Text column, right? You are
likely running into an old ODBC bug which is described here:
http://www.aspfaq.com/show.asp?id=2188
Switch to using the native SQL OLE DB provider (SQLOLEDB) and this problem
should go away.
http://www.aspfaq.com/show.asp?id=2126
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks Bob!
I'm glad I asked, that was the problem exactly. I've swapped out the
connection strings and using the SQLOLEDB now. Works like a champ.
The quick response is much appreciated,
Lee
some guidance. I am accessing a Microsoft SQL 2000 server through ASP
scripts on our webserver. The sql server and web server are on the
same network.
This code used to work, and started acting strangely after I moved it
to a new webserver and SQL server (from testing
environment->production). Previously the web server and sql database
were running on the same machine, the software versions are all the
same.
When I do a SELECT statement to retrieve data, I then pull data from
each of the fields I need. The problem is certain fields cause a
strange behavior. When I pull the data from one field, all subsiquent
uses of the RecordSet object return empty when retrieveing data from
other fields. (even though I know all fields contain information)
For example:
set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
response.write rs("FieldA") & "//" & rs("FieldB") & "//" & rs("FieldC")
& "//" & rs("FieldD")
Outputs "AAA//////" whereas:
set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
response.write rs("FieldD") & "//" & rs("FieldC") & "//" & rs("FieldB")
& "//" & rs("FieldA")
Outputs "DDD/////"
Any ideas? Thanks for your help - it is much appreciated!
LeeCrazyAtlantaGuy wrote:
> When I do a SELECT statement to retrieve data, I then pull data from
> each of the fields I need. The problem is certain fields cause a
> strange behavior. When I pull the data from one field, all subsiquent
> uses of the RecordSet object return empty when retrieveing data from
> other fields. (even though I know all fields contain information)
> For example:
> set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
http://www.aspfaq.com/show.asp?id=2096
> response.write rs("FieldA") & "//" & rs("FieldB") & "//" &
> rs("FieldC") & "//" & rs("FieldD")
> Outputs "AAA//////" whereas:
> set rs = db.execute("SELECT * FROM myTable WHERE ID=1")
> response.write rs("FieldD") & "//" & rs("FieldC") & "//" &
> rs("FieldB") & "//" & rs("FieldA")
> Outputs "DDD/////"
OK, I guess you are using ODBC, and FieldD is a Text column, right? You are
likely running into an old ODBC bug which is described here:
http://www.aspfaq.com/show.asp?id=2188
Switch to using the native SQL OLE DB provider (SQLOLEDB) and this problem
should go away.
http://www.aspfaq.com/show.asp?id=2126
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks Bob!
I'm glad I asked, that was the problem exactly. I've swapped out the
connection strings and using the SQLOLEDB now. Works like a champ.
The quick response is much appreciated,
Lee
Problemm setting permissions
Hello everybody,
I ran into a strange kind of problem in SQL2K. I tryto give a user
select permissions on fn_trace_getinfo, but then I apply the new
permissions I get the message "Invalid object name
system_functionschema.fn_trace_getinfo". How is this possible. I see
the function and as sysadmin I can use it, but when you try to change
permissions it says the object doesn't exist ?
Does anybody know why this is happening and if there is some way
around.
Tia MarkusHi
fn_trace_getinfo is a system function.
What happaned if the user run
SELECT *
FROM ::fn_trace_getinfo(trace_id)
WHERE Property=4
<m.bohse@.quest-consultants.com> wrote in message
news:1136540572.050234.16160@.g14g2000cwa.googlegroups.com...
> Hello everybody,
> I ran into a strange kind of problem in SQL2K. I tryto give a user
> select permissions on fn_trace_getinfo, but then I apply the new
> permissions I get the message "Invalid object name
> system_functionschema.fn_trace_getinfo". How is this possible. I see
> the function and as sysadmin I can use it, but when you try to change
> permissions it says the object doesn't exist ?
> Does anybody know why this is happening and if there is some way
> around.
> Tia Markus
>
I ran into a strange kind of problem in SQL2K. I tryto give a user
select permissions on fn_trace_getinfo, but then I apply the new
permissions I get the message "Invalid object name
system_functionschema.fn_trace_getinfo". How is this possible. I see
the function and as sysadmin I can use it, but when you try to change
permissions it says the object doesn't exist ?
Does anybody know why this is happening and if there is some way
around.
Tia MarkusHi
fn_trace_getinfo is a system function.
What happaned if the user run
SELECT *
FROM ::fn_trace_getinfo(trace_id)
WHERE Property=4
<m.bohse@.quest-consultants.com> wrote in message
news:1136540572.050234.16160@.g14g2000cwa.googlegroups.com...
> Hello everybody,
> I ran into a strange kind of problem in SQL2K. I tryto give a user
> select permissions on fn_trace_getinfo, but then I apply the new
> permissions I get the message "Invalid object name
> system_functionschema.fn_trace_getinfo". How is this possible. I see
> the function and as sysadmin I can use it, but when you try to change
> permissions it says the object doesn't exist ?
> Does anybody know why this is happening and if there is some way
> around.
> Tia Markus
>
Saturday, February 25, 2012
Problem with VPN connection on SQL server
Hi,
my customer has a strange problem. In the LAN-enviroment the application can
connect the local SQL server as well as on the central database. The problem
occurs when a laptopuser wants to connect the central server via VPN.
The server (evnapp) and the disks of the server are accessible. A ping gives
the correct IP address.
But when connecting the database via:
Provider=sqloledb;driver={SQL Server};Network=DBMSSOCN;uid=xxx;
server=evnapp;database=xxx and so on the local engine is connected ?
There is a host table on the pc (laptops) which maps evnapp to the correct
IP.
When using the IP addres in stead of the server name the same thing happens.
A local connection.
The only time it does work is when the host table is removed after the
connection is estabilshed.
Is it the host table? Or the VPN-software?(Tunix/VPN for windows) . What can
it be. But stations (desktops) who were installed several months ago are
using the same host table and do not have any problems.
All pc's are running win2000. SQL 2000 is used.
Does this ring a bell ? If so please reply
Ton
begin 666 LMHOSTS.dat
M,3(W+C$V."XQ.# N-#@.)979N9&,P,0T*,3(W+C$V."XQ.# N-30)979N9&,P
M,@.T*,3(W+C$V."XQ.# N,S4)979N9G,P,0T*,3(W+C$V."XQ.# N-#,);FAL
M<W)V,#0-"C$R-RXQ-C@.N,3@.T+C0R"65V;F1C<F0-"C$R-RXQ-C@.N,3@.P+C0W
D"65V;F%P<# R#0HQ,C<N,38X+C$X,"XS- EN:&QS<G8Q. T*
`
end
begin 666 hosts.dat
M(R!(;W-T($-O<'ER:6=H="!.:65U=R!(;VQL86YD<V4@.3&QO>60@.,3DM,#8M
M,C P,@.T*#0HQ,C<N,"XP+C$@.(" @.(" @.;&]C86QH;W-T#0H-"B,@.<VUT< T*
M,3(W+C$V."XQ.# N-#,);FAL<W)V,#0-"@.T*(R!(5%10#0HQ,C<N,38X+C$X
M,"XS,0EN:&QS<G8Q.0T*,3(W+C$V."XQ.# N,S$):6YT<F%N970N;FAL+79E
M<GIE:V5R:6YG96XN;FP-"@.T*(R!!4R\T,# -"C$R-RXQ-C@.N,3@.P+C(S"5,T
M-$$V,#(V#0HQ,C<N,38X+C$X,"XR- E3-#1&,38Q. T*,3(W+C$V."XQ.# N
M,C8)4S0T,4%#.4$-"@.T*(R!305 -"C$R-RXQ-C@.N,3@.P+C8P"65V;G-A<'!R
M9 T*,3(W+C$V."XQ.# N,S<)979N<V%P86-C#0HQ,C<N,38X+C$X,"XU.0EE
M=FYS87!O;G0-"C$R-RXQ-C@.N,3@.P+C,V"65V;G-A<&1E=@.T*#0HC($YE=$))
M3U,-"C$R-RXQ-C@.N,3@.P+C0X"65V;F1C,#$-"C$R-RXQ-C@.N,3@.P+C4T"65V
M;F1C,#(-"C$R-RXQ-C@.N,3@.P+C,U"65V;F9S,#$-"C$R-RXQ-C@.N,3@.P+C0V
M"65V;F9S,#(-"C$R-RXQ-C@.N,3@.P+C0U"65V;FEI<S Q#0HQ,C<N,38X+C$X
M,"XV,0EE=FYI<V$-"C$R-RXQ-C@.N,3@.P+C,T
my customer has a strange problem. In the LAN-enviroment the application can
connect the local SQL server as well as on the central database. The problem
occurs when a laptopuser wants to connect the central server via VPN.
The server (evnapp) and the disks of the server are accessible. A ping gives
the correct IP address.
But when connecting the database via:
Provider=sqloledb;driver={SQL Server};Network=DBMSSOCN;uid=xxx;
server=evnapp;database=xxx and so on the local engine is connected ?
There is a host table on the pc (laptops) which maps evnapp to the correct
IP.
When using the IP addres in stead of the server name the same thing happens.
A local connection.
The only time it does work is when the host table is removed after the
connection is estabilshed.
Is it the host table? Or the VPN-software?(Tunix/VPN for windows) . What can
it be. But stations (desktops) who were installed several months ago are
using the same host table and do not have any problems.
All pc's are running win2000. SQL 2000 is used.
Does this ring a bell ? If so please reply
Ton
begin 666 LMHOSTS.dat
M,3(W+C$V."XQ.# N-#@.)979N9&,P,0T*,3(W+C$V."XQ.# N-30)979N9&,P
M,@.T*,3(W+C$V."XQ.# N,S4)979N9G,P,0T*,3(W+C$V."XQ.# N-#,);FAL
M<W)V,#0-"C$R-RXQ-C@.N,3@.T+C0R"65V;F1C<F0-"C$R-RXQ-C@.N,3@.P+C0W
D"65V;F%P<# R#0HQ,C<N,38X+C$X,"XS- EN:&QS<G8Q. T*
`
end
begin 666 hosts.dat
M(R!(;W-T($-O<'ER:6=H="!.:65U=R!(;VQL86YD<V4@.3&QO>60@.,3DM,#8M
M,C P,@.T*#0HQ,C<N,"XP+C$@.(" @.(" @.;&]C86QH;W-T#0H-"B,@.<VUT< T*
M,3(W+C$V."XQ.# N-#,);FAL<W)V,#0-"@.T*(R!(5%10#0HQ,C<N,38X+C$X
M,"XS,0EN:&QS<G8Q.0T*,3(W+C$V."XQ.# N,S$):6YT<F%N970N;FAL+79E
M<GIE:V5R:6YG96XN;FP-"@.T*(R!!4R\T,# -"C$R-RXQ-C@.N,3@.P+C(S"5,T
M-$$V,#(V#0HQ,C<N,38X+C$X,"XR- E3-#1&,38Q. T*,3(W+C$V."XQ.# N
M,C8)4S0T,4%#.4$-"@.T*(R!305 -"C$R-RXQ-C@.N,3@.P+C8P"65V;G-A<'!R
M9 T*,3(W+C$V."XQ.# N,S<)979N<V%P86-C#0HQ,C<N,38X+C$X,"XU.0EE
M=FYS87!O;G0-"C$R-RXQ-C@.N,3@.P+C,V"65V;G-A<&1E=@.T*#0HC($YE=$))
M3U,-"C$R-RXQ-C@.N,3@.P+C0X"65V;F1C,#$-"C$R-RXQ-C@.N,3@.P+C4T"65V
M;F1C,#(-"C$R-RXQ-C@.N,3@.P+C,U"65V;F9S,#$-"C$R-RXQ-C@.N,3@.P+C0V
M"65V;F9S,#(-"C$R-RXQ-C@.N,3@.P+C0U"65V;FEI<S Q#0HQ,C<N,38X+C$X
M,"XV,0EE=FYI<V$-"C$R-RXQ-C@.N,3@.P+C,T
problem with varbinary(max) in SQL Server 2005
Hi, we have stucked with strange problem. Please read comments in the following script.
CREATE TABLE ItemsTable1(
[ItemId] [int] NOT NULL DEFAULT 0,
[ItemNameCode] [varbinary] (max) NULL
)
CREATE TABLE ItemsTable2(
[ItemId] [int] NOT NULL DEFAULT 0,
[ItemNameCode] [varbinary] (20) NULL
)
-- insert same values in both tables
INSERT INTO ItemsTable1(ItemId, ItemNameCode)
VALUES(1, CAST('Item 1' AS varbinary(20)))
INSERT INTO ItemsTable2(ItemId, ItemNameCode)
VALUES(1, CAST('Item 1' AS varbinary(20)))
--query 1 returns nothing. SQL Server thinks that values are different. Why?
SELECT *
FROM ItemsTable1 as it1, ItemsTable2 as it2
WHERE it1.ItemNameCode = it2.ItemNameCode
--query 2 returns 'Equals!!!'.
SELECT CASE WHEN it1.ItemNameCode = it2.ItemNameCode THEN 'Equals!!!'
ELSE 'Not equals!!!' END
FROM ItemsTable1 as it1, ItemsTable2 as it2
WHERE it1.ItemId = it2.ItemId
--query 3 after casting all works fine.
SELECT *
FROM ItemsTable1 as it1, ItemsTable2 as it2
WHERE CAST(it1.ItemNameCode as varbinary(20))
= CAST(it2.ItemNameCode as varbinary(20))
DROP TABLE ItemsTable1
DROP TABLE ItemsTable2
Note: If we use nvarchar(20) and nvarchar(max) all works fine. So may be this is a bug?
Looks like a bug to me|||This is a bug. And it is not fixed in a recent build of SQL Server 2005 SP1 either. Could you please file a bug for this in the MSDN Product Feedback Center? Thanks.
Monday, February 20, 2012
Problem with updating a mobile sql database
This is strange. Cause earlier I could update the mobile sql server with no problem, but now I am getting this parsing error
it says Token line number = 1, ....
The windows mobile emulator works, but not in the actual pda.
The sql query is following
sql2 = "update flexi_pump set difference = " + divergence + " where tag = '" + tag + "'";
selecting data from the database works fine, no problem with it. only writing to it is the problem 
I would recommend using command parameters to avoid occasional string concatenation mishaps and possible SQL injection attacks on your code. If the tag variable is a string, it may contain a quote and it will break the syntax.
Subscribe to:
Posts (Atom)