Role Manager Feature? What is it? Error in Connecting to db

I keep getting this error. I am using Visual Web Developer Express 2005 (Visual Basic) and SQL Server 2005 Express.

"There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled."

This is in the ASP.NET Configuration.

How do I enable role manager?

Hi, you can take a look at this link:

http://msdn2.microsoft.com/en-us/library/ms178402.aspx

RMO Error with Web replication

I am testing Web merge replication between SQL Server and SQL Server Express.

If I use Windows Synchronization (mobsync.exe) everything goes well.

If have created a simple program in VB.net 2005, using sample code from this forum and MSDN and I always get this error: "The Merge Agent process could not write Web synchronization metadata in the message header. Verify that there is sufficient disk space in the Web synchronization directory. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199467)". All parameters are identical to the one used with mobsync.exe

Does anyone know what this error mean and how can I avoid it ?

Thanks in advance,
Riccardo Sabadini

1. Do you mind posting your parameters used in mobsync.exe here? (Please take care of the actual password).

2. Can you check if you have the appropriate port opened on distributor/publisher or subscriber machines?

3. I assume when you run VB.net application, it is on the subscriber machine, rgiht? Do you specify the InternetLogin and Internet password so the app is able to connect to the web sync virtual directory?

Thanks.

This posting is provided AS IS with no warranties, and confers no rights.

rights issues with linked server

I have a web server and database server which are not on a domain. The
web server has an app that is running on under the IUSR account and
there is a corresponding IUSR account on the database server. The web
app connects to the SQL server with the windows authentication and
everything works fine.

I also have a stored proc which does a file search using a linked
server to an index server on the very same web server. For now assume
that cannot change, the files must be on the web server (TRA is the
machine name). When attempting to execute the following query from the
database server, doing a RunAs on the SQL 2005 Management studio as the
IUSR account from the database server, everything works fine:

SELECT
Q.*
FROM
OPENQUERY(FTIndexPM, 'SELECT path, characterization, rank, hitcount
FROM TRA.CatalogTest..SCOPE(''DEEP TRAVERSAL OF "C:\Program
Files\dir1\Files\Project757\DocMgmt"'') WHERE CONTAINS(''"test"'') > 0
OR FileName LIKE ''%test%'' ORDER BY rank DESC') AS Q

The IUSR_TRA account is an administrator on both the database and web
server for debugging reasons until we figure this out, but clearly it
shouldn't be a rights issue if both users are administrators on both
machines.

Now for the tricky, part. When the same query as above is executed from
the web server, it fails with the the below error:

OLE DB provider "MSIDXS" for linked server "FTIndexPM" returned message
"Invalid catalog name 'CatalogTest'. SQLSTATE=42000 ".

Using SQL Profiler, I have verified that both queries are actually
running under the IUSR_TRA account. Any ideas why the exact same query
running under the exact same NT login would behave differently when
executed from a web app as opposed to through a clinet request from a
web app?As for the last question, I meant through the SQL 2005 query tool as
opposed to the web app. Same command, same executing windows user,
different results.|||Anyone know where this should be posted where it might get a response?|||pb648174 (google@.webpaul.net) writes:
> Anyone know where this should be posted where it might get a response?

Maybe an IIS or ASP group. It's completely beyond my domain, so I don't
really know.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Could you perhaps comment on the last question, i.e. if in SQL Profiler
they are both running under the same login, could anything be different
about their access rights? In SQL 2005, when I try to change the
authentication for a linked server, it seems to have no effect as well.
I can change the user/password to sdfsdf/sdfsdf and it still works...
Seems like there are issues with linked server authentication in SQL
2005.|||pb648174 (google@.webpaul.net) writes:
> Could you perhaps comment on the last question, i.e. if in SQL Profiler
> they are both running under the same login, could anything be different
> about their access rights? In SQL 2005, when I try to change the
> authentication for a linked server, it seems to have no effect as well.
> I can change the user/password to sdfsdf/sdfsdf and it still works...
> Seems like there are issues with linked server authentication in SQL
> 2005.

There is still a web server involved. And probably some Windows networking.
Both fall outside my field of expertise.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I fixed it by doing the following:

Instead of trying to use Index server on web server, just used index
server on database server and mapped drive for database server index
server to get at files on web server. Works like a champ.

Erland Sommarskog wrote:
> pb648174 (google@.webpaul.net) writes:
> > Could you perhaps comment on the last question, i.e. if in SQL Profiler
> > they are both running under the same login, could anything be different
> > about their access rights? In SQL 2005, when I try to change the
> > authentication for a linked server, it seems to have no effect as well.
> > I can change the user/password to sdfsdf/sdfsdf and it still works...
> > Seems like there are issues with linked server authentication in SQL
> > 2005.
> There is still a web server involved. And probably some Windows networking.
> Both fall outside my field of expertise.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Right way to retrieve data from sql-server

Last night I face performace problems in web site. I got like 100 000 users and site fails because of sql-connections.

Currently I'm making a new connection for every query, and my code looks like this:

Public Shared Function executeQueryReturnDataset(ByVal strsqlAs String)As DataSetDim sqlConnectionAs New SqlConnection(Connstr)Dim sqlCommandAs New SqlCommand(strsql, sqlConnection)Dim sqlDataSetAs New DataSetTry sqlConnection.Open()Dim myDataAdapterAs New SqlDataAdapter myDataAdapter.SelectCommand = sqlCommand myDataAdapter.Fill(sqlDataSet)Catch eAs Exception Console.Write(e.ToString())Finally sqlConnection.Close()End Try Return sqlDataSetEnd Function

I was wondering would it be better to save an instance from connection object to memory and use that same connection for all querys?

No. SQL connection pooling is automatic.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconConnectionPoolingForSQLServerNETDataProvider.asp

Which version of SQL are you running?

As a by note, a SQLAdapter will open its connection if it is closed, and if it did open it, close it immediately after it finishes.

The only real improvement above would be the use of a SQLDataReader if applicable, and if not, the instantiation of the SQLDataAdapter, and assignment of its command, prior to opening the connection.

|||

I do not think that saving connection will help you to much because you use connection pooling anyway
try to modify your code to look like this:

Public Shared Function executeQueryReturnDataset(ByVal strsqlAs String)As DataSetDim sqlConnectionAs New SqlConnection(Connstr)Dim sqlCommandAs New SqlCommand(strsql, sqlConnection)

Dim myDataAdapterAs New SqlDataAdapter myDataAdapter.SelectCommand = sqlCommandDim sqlDataSetAs New DataSetTry sqlConnection.Open() myDataAdapter.Fill(sqlDataSet)Catch eAs Exception Console.Write(e.ToString())Finally sqlConnection.Close()End Try Return sqlDataSetEnd Function

So now your connection should be use for a little shorter period of time and it should allow other web user to access data,
you can also try to optimize your query to be processed in shortest amount of time. Another question is:

 Do you really need to return dataset? maybe returning table will be more efficient (less resources to use)?

Public Shared Function executeQueryReturnDataset(ByVal strsqlAs String)As DataTableDim sqlConnectionAs New SqlConnection(Connstr)Dim sqlCommandAs New SqlCommand(strsql, sqlConnection)
Dim myDataAdapterAs New SqlDataAdapter myDataAdapter.SelectCommand = sqlCommandDim ResultTableAs New DataTableTry sqlConnection.Open() myDataAdapter.Fill(ResultTable)Catch eAs Exception Console.Write(e.ToString())Finally sqlConnection.Close()End Try Return ResultTableEnd Function
 
You can also try to use different way for reading your data DataReader?
Thanks

|||

But sometimes is safe to open and close connection by hands in case you would like to use the same connection object for multiple database access, because adapter sometimes does not close connection and reader inside it if it fails so you can have problem if you would like to use the same connection object after adapter. But if you use connection object for one call you can allow adapter to take care about opening/closing connection.

Thanks

|||

Thanks you!

Automatic pooling was new to me, I'm such a newbie :-)

Thanks

Reverse Wildcard Searches Impossible?

I have researched newsgroups and the web very thoroughly and
unsuccessfully for a solution to what I believe is a very common
problem. I know it's easy to do wildcard match against data in DB
(using LIKE and "%" and "?").

But is it possible to match a concrete string against a database of
wildcarded data? ("%" and LIKE do not work). For example:

CREATE TABLE blacklist (
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
pattern VARCHAR(255) NOT NULL
/* ... */
);

INSERT INTO blacklist (pattern) VALUES ('%foobar.com');

Is there a select query that would match address mars2.foobar.com
against this row?

Some people claim that the following query will work. I have tried it
and it's not true on either Oracle or SQL server.

SELECT * FROM blacklist WHERE 'mars2.foobar.com' LIKE pattern;

Some people suggest breaking up the blacklist table into N varchar
fields for each domain segment and then representing a wildcard
character as a NULL and use isNull to match it. This does work to an
extent. However, a) it seems really ugly, b) does not allow arbitrary
wildcarding (eg %mars%foobar.com), and c) this is something the DB
should do out of the box.

Please help! Humanity will be greatful as there's currently no
solution to this anywhere on newsgroups."Robert Brown" <robertbrown1971@.yahoo.com> wrote in message
news:240a4d09.0404301119.467bd1e1@.posting.google.c om...
> I have researched newsgroups and the web very thoroughly and
> unsuccessfully for a solution to what I believe is a very common
> problem. I know it's easy to do wildcard match against data in DB
> (using LIKE and "%" and "?").
> But is it possible to match a concrete string against a database of
> wildcarded data? ("%" and LIKE do not work). For example:
>
> CREATE TABLE blacklist (
> id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
> pattern VARCHAR(255) NOT NULL
> /* ... */
> );
> INSERT INTO blacklist (pattern) VALUES ('%foobar.com');
> Is there a select query that would match address mars2.foobar.com
> against this row?
> Some people claim that the following query will work. I have tried it
> and it's not true on either Oracle or SQL server.
> SELECT * FROM blacklist WHERE 'mars2.foobar.com' LIKE pattern;
> Some people suggest breaking up the blacklist table into N varchar
> fields for each domain segment and then representing a wildcard
> character as a NULL and use isNull to match it. This does work to an
> extent. However, a) it seems really ugly, b) does not allow arbitrary
> wildcarding (eg %mars%foobar.com), and c) this is something the DB
> should do out of the box.
> Please help! Humanity will be greatful as there's currently no
> solution to this anywhere on newsgroups.

Works fine for me on Oracle:

C:\>sqlplus
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0

SQL> create table blacklist (pattern varchar2(255));

Table created

SQL> insert into blacklist (pattern) values ('%foobar.com');

1 row inserted

SQL> select * from blacklist where 'mars2.foobar.com' like pattern;

PATTERN
-----------------------
--
%foobar.com|||David Best (davebest@.usa.net) writes:
> Works fine for me on Oracle:
> C:\>sqlplus
> Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
> SQL> create table blacklist (pattern varchar2(255));
> Table created
> SQL> insert into blacklist (pattern) values ('%foobar.com');
> 1 row inserted
> SQL> select * from blacklist where 'mars2.foobar.com' like pattern;

And the same example (save the funny varchar2) works on MS SQL Server too.

And should work on about any DBMS, as this is core SQL.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns94DC31A1B00CYazorman@.127.0.0.1...
> David Best (davebest@.usa.net) writes:
> > Works fine for me on Oracle:
> > C:\>sqlplus
> > Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
> > SQL> create table blacklist (pattern varchar2(255));
> > Table created
> > SQL> insert into blacklist (pattern) values ('%foobar.com');
> > 1 row inserted
> > SQL> select * from blacklist where 'mars2.foobar.com' like pattern;
> And the same example (save the funny varchar2) works on MS SQL Server too.
> And should work on about any DBMS, as this is core SQL.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

It should work, but the query is going to be very inefficient.
Jim|||Jim Kennedy (kennedy-downwithspammersfamily@.attbi.net) writes:
> It should work, but the query is going to be very inefficient.

Yes, if there is an index on pattern it is not going to be useful,
since the match is at the end of the string. But that is not really
the same that it is very ineffecient. If you have a million entries,
you will certainly notice the toll. But with thousand? Not very much.
And thousand is a more likely number than a million.

For this particular case there exists a possible way to speed things up.
Since we search for the end of the string, you could have:

CREATE TABLE blacklist (pattern varchar(225) NOT NULL PRIMARY KEY,
revpattern AS reverse(pattern));
CREATE UNIQUE INDEX revix ON blacklist (revpattern);
go
INSERT blacklist VALUES ('%@.example.com')
go
SELECT pattern FROM blacklist
WHERE reverse('spammer@.example.com') LIKE revpattern

However computed columns is not standard SQL, and may not work on all
DBMSs. The above works in SQL Server.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The query will still need a table (or index) scan, because the column is
to the right of the LIKE operator...

Gert-Jan

Erland Sommarskog wrote:
> Jim Kennedy (kennedy-downwithspammersfamily@.attbi.net) writes:
> > It should work, but the query is going to be very inefficient.
> Yes, if there is an index on pattern it is not going to be useful,
> since the match is at the end of the string. But that is not really
> the same that it is very ineffecient. If you have a million entries,
> you will certainly notice the toll. But with thousand? Not very much.
> And thousand is a more likely number than a million.
> For this particular case there exists a possible way to speed things up.
> Since we search for the end of the string, you could have:
> CREATE TABLE blacklist (pattern varchar(225) NOT NULL PRIMARY KEY,
> revpattern AS reverse(pattern));
> CREATE UNIQUE INDEX revix ON blacklist (revpattern);
> go
> INSERT blacklist VALUES ('%@.example.com')
> go
> SELECT pattern FROM blacklist
> WHERE reverse('spammer@.example.com') LIKE revpattern
> However computed columns is not standard SQL, and may not work on all
> DBMSs. The above works in SQL Server.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

--
(Please reply only to the newsgroup)

Reusing an SQLconnection

Hi all,
I am accessing one database a bunch of different times all throughout my code...in various functions and different web pages. Is there a a way to create an sqlconnection that I can access all the time, instead of constanting hardcoding which database to go to? I've tried putting the info in another file and just including it where I want the database to open, but I can't use <!-- #INCLUDE --> inside of the server scripts.
Can anyone helpTake a look @.Using Web.Config in ASP.NET and ASP

Returning Scope_Identity() for GUID

I'm stumped... I'm trying to get the GUID value returned from an insert (I
really need to use a GUID because this is a web app and I want the records t
o
be difficult to find i.e. identity key within range is too easy to browse)
Declare @.Identity nvarchar(30)
Create Table #T (I uniqueidentifier, LName nvarchar(10))
Insert INTO #T (LName) Values('Rosner')
Set @.Identity = Scope_IDENTITY()
SELECT @.IDENTITY
This returns null while
Drop Table #T
Declare @.Identity uniqueidentifier
Create Table #T (I uniqueidentifier, LName nvarchar(10))
Insert INTO #T (LName) Values('Rosner')
Set @.Identity = Scope_IDENTITY()
SELECT @.IDENTITY
returns error - "Operand type clash: numeric is incompativle with
uniqueidentifier.
Suggestions would be greatly appreciated. Thanks in advance.
- Abe1. scope_identity() only returns the last (scoped) identity value - this
value datatype has to be numeric.
2. the first batch returns NULL because there is no identity column in your
table. Thus, the last insert generates a NULL identity (INT) value. The
implicit conversion from INT to nvarchar is allowed. Hence, no error is
raised.
3. the second batch fails because of the implicit conversion. You can't
convert a numeric to uniqueidentifier.
Here is a quick proof of the conversion error:
select convert(uniqueidentifier,1)
-oj
"AbeR" <AbeR@.discussions.microsoft.com> wrote in message
news:5BD89A94-9006-48F3-B73C-426C845E4729@.microsoft.com...
> I'm stumped... I'm trying to get the GUID value returned from an insert (I
> really need to use a GUID because this is a web app and I want the records
> to
> be difficult to find i.e. identity key within range is too easy to browse)
> Declare @.Identity nvarchar(30)
> Create Table #T (I uniqueidentifier, LName nvarchar(10))
> Insert INTO #T (LName) Values('Rosner')
> Set @.Identity = Scope_IDENTITY()
> SELECT @.IDENTITY
> This returns null while
> Drop Table #T
> Declare @.Identity uniqueidentifier
> Create Table #T (I uniqueidentifier, LName nvarchar(10))
> Insert INTO #T (LName) Values('Rosner')
> Set @.Identity = Scope_IDENTITY()
> SELECT @.IDENTITY
> returns error - "Operand type clash: numeric is incompativle with
> uniqueidentifier.
> Suggestions would be greatly appreciated. Thanks in advance.
> - Abe|||"Returns the last IDENTITY value inserted into an IDENTITY column in the
same scope. A scope is a module -- a stored procedure, trigger, function, or
batch" from BOL .Identity columns need to be of type bigint, int or smallint
and are not compatible with type uniqueidentifier .Therefore,to ask SQL
Server to give you last identity inserted and then assigning that to a
variable previously declared as a uniqueidentifier, equals compatibility
error
To return it do something like:
Declare @.Identity uniqueidentifier
Select @.Identity = newID()
Create Table #T (I uniqueidentifier, LName nvarchar(10))
Insert INTO #T (I,LName) Values(@.identity,'Rosner')
SELECT @.IDENTITY
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"AbeR" <AbeR@.discussions.microsoft.com> wrote in message
news:5BD89A94-9006-48F3-B73C-426C845E4729@.microsoft.com...
> I'm stumped... I'm trying to get the GUID value returned from an insert (I
> really need to use a GUID because this is a web app and I want the records
to
> be difficult to find i.e. identity key within range is too easy to browse)
> Declare @.Identity nvarchar(30)
> Create Table #T (I uniqueidentifier, LName nvarchar(10))
> Insert INTO #T (LName) Values('Rosner')
> Set @.Identity = Scope_IDENTITY()
> SELECT @.IDENTITY
> This returns null while
> Drop Table #T
> Declare @.Identity uniqueidentifier
> Create Table #T (I uniqueidentifier, LName nvarchar(10))
> Insert INTO #T (LName) Values('Rosner')
> Set @.Identity = Scope_IDENTITY()
> SELECT @.IDENTITY
> returns error - "Operand type clash: numeric is incompativle with
> uniqueidentifier.
> Suggestions would be greatly appreciated. Thanks in advance.
> - Abe|||Hi,
How about capturing the GUID into a local variable and using it for insert
and then as return value as well.
Declare @.PersistGUID uniqueidentifier
Select @.PersistGUID = newid()
Now you could use @.PersistGUID as per your need.
Best Regards
Vadivel
http://vadivel.blogspot.com
"AbeR" wrote:

> I'm stumped... I'm trying to get the GUID value returned from an insert (I
> really need to use a GUID because this is a web app and I want the records
to
> be difficult to find i.e. identity key within range is too easy to browse)
> Declare @.Identity nvarchar(30)
> Create Table #T (I uniqueidentifier, LName nvarchar(10))
> Insert INTO #T (LName) Values('Rosner')
> Set @.Identity = Scope_IDENTITY()
> SELECT @.IDENTITY
> This returns null while
> Drop Table #T
> Declare @.Identity uniqueidentifier
> Create Table #T (I uniqueidentifier, LName nvarchar(10))
> Insert INTO #T (LName) Values('Rosner')
> Set @.Identity = Scope_IDENTITY()
> SELECT @.IDENTITY
> returns error - "Operand type clash: numeric is incompativle with
> uniqueidentifier.
> Suggestions would be greatly appreciated. Thanks in advance.
> - Abe

Returning ListChildren Name and Path

I am writting a custom web app that I need to get all available reports from the report server into an arraylist and then populate a drop down with that arraylist. I am getting back "myAppName.MyArrayName+ReportItem". I get this line for each available report in the dropdownlist but not the name or path...which I am requesting. What am I doing wrong? Does it have something to do with the dropdownlist capability? Please advise. Thanks so much!
CurtisKYou want to extend the ListItem that the drop down list uses to support your
CatalogItem types. This can be down by writing a few lines of code. For an
example of how this is done, see the FindRenderSave or RSExplorer sample
applications that ship with Reporting Services.
Bryan
--
Bryan Keller
Developer Documentation
SQL Server Reporting Services
A friendly reminder that this posting is provided "AS IS" with no
warranties, and confers no rights.
"CurtisK" <CurtisK@.discussions.microsoft.com> wrote in message
news:2FC2AECB-FE8A-4615-AD7D-5A724BA4FC9C@.microsoft.com...
> I am writting a custom web app that I need to get all available reports
from the report server into an arraylist and then populate a drop down with
that arraylist. I am getting back "myAppName.MyArrayName+ReportItem". I get
this line for each available report in the dropdownlist but not the name or
path...which I am requesting. What am I doing wrong? Does it have something
to do with the dropdownlist capability? Please advise. Thanks so much!
> CurtisK

Returning last row only

I have a web application that is data driven from a SQL 2005 database. The application needs to prompt the user to enter some information that will be logged into a SQL table.

It should always be the last row in the table that is being worked on at any one time. Over a period the user will need to enter various fields. Once the data is entered into a field they will not have access to amend it.

Therefore I need to be able to SELECT the last row of a table and present the data to the user with the 'next field' to be edited.

As I'd like to do this as a stored procedure which can be called from an ASP page I wonder if anyoen might be able to help me with some T-SQL code that might achieve it?

Regards

Clive

I will assume that the table has an integer identity primary key. The SQL then becomes very simple:

SELECT A,B,C FROM TABLENAME WHERE ID = (SELECT MAX(Id) FROM TABLENAME)

|||

Yes the ID field is exactly as you say. That works perfectly - many thanks

Clive

|||

Another way to do is:

select top 1 visitid from medications order by visitid desc

It will return the last row only.