[SQLSTATE 42000] (Error 22029).

We have SQL Server 2000 installed on Windows 2003
machine. Get following error while doing database
maintenance
Executed as user: server\sqladmin. sqlmaint.exe failed.
[SQLSTATE 42000] (Error 22029). The step failed.
SQL Server log doesn't say anything either.
Any idea on what could be wrong? Thanks.
You need to look at the maintenance plan history or the report file for that
job to find the error.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"anonymous" <anonymous@.discussions.microsoft.com> wrote in message
news:885c01c48531$710fd050$a601280a@.phx.gbl...
> We have SQL Server 2000 installed on Windows 2003
> machine. Get following error while doing database
> maintenance
> Executed as user: server\sqladmin. sqlmaint.exe failed.
> [SQLSTATE 42000] (Error 22029). The step failed.
> SQL Server log doesn't say anything either.
> Any idea on what could be wrong? Thanks.

[SQLSTATE 22003] (Error 8115)

Hi,
I have this job that runs sometimes it completes successfully, sometimes it
fails.
When it fails it gives this error message -->
Executed as user: sqlagent. Arithmetic overflow error converting expression
to data type int.
[SQLSTATE 22003] (Error 8115) The statement has been terminated. [SQLSTATE
01000] (Error 3621).
The step failed.
When I did the research on all the codes in the error looks like one of the
statements such as -->
SELECT ABS(convert(int, -2147483648))
is causing the problem. The problem I am having is that stored procedure
that runs has many 'converts' and I do not know which one is causing the
problem. Can I setup some kind of monitor that will catch exact cause or
declared 'int' that it is unable to convert to "int"?
How should I go about it?
Thanks
-D
You can run a Profiler trace, including stored procedure statement
starting/completed and exception events. Alternatively, you can run the
proc from Query Analyzer, using the debugger to step through the code
Hope this helps.
Dan Guzman
SQL Server MVP
"D''Animal" <DAnimal@.discussions.microsoft.com> wrote in message
news:E1F085A7-C312-4989-96D4-EF2DFA1FD835@.microsoft.com...
> Hi,
> I have this job that runs sometimes it completes successfully, sometimes
> it
> fails.
> When it fails it gives this error message -->
> --
> Executed as user: sqlagent. Arithmetic overflow error converting
> expression
> to data type int.
> [SQLSTATE 22003] (Error 8115) The statement has been terminated.
> [SQLSTATE
> 01000] (Error 3621).
> The step failed.
> --
> When I did the research on all the codes in the error looks like one of
> the
> statements such as -->
> SELECT ABS(convert(int, -2147483648))
> is causing the problem. The problem I am having is that stored procedure
> that runs has many 'converts' and I do not know which one is causing the
> problem. Can I setup some kind of monitor that will catch exact cause or
> declared 'int' that it is unable to convert to "int"?
> How should I go about it?
> Thanks
> -D
>

[SqlException (0x80131904): User does not have permission to perform this action.]

Folks,

This is driving me nuts. I posted the other day on a related issue, which I belive turned out to be a file permission. I am trying to test my asp.net site under IIS before deploying it. I am now getting the above message which breaks on the opening page on opening a connection to the sql server database. I have permissions set on the database for MachineName\ASPNET and NT AUTHORITY\NETWORK SERVICE as Ken Tucker suggested. Still getting the message. This certainly looks like a sqlserver permission issue but I am at a loss as to how to grant IIS or the specific web app permission to access the database.

Any assistance is truely appreciated. I have spent way to many hours on this. If you don't have the answer but can suggest a resource where I can research it that would help also.

Thanks

What permissions have you set for the ASPNET user?

bullpit

|||ASPNET user has connect permission.|||

I asked because I had a similar situation and what I did was to go to SQL Server Enterprise Manager, navigate down to the database I was using, and then to its "users" tab, and for the ASPNET user, I right clicked and set the all the permissions to Insert, Update, Delete, Select and Execute. I dont know if you already done this or not. If not, you can give this a try.

Good luck,

bullpit

|||

Also, if you know your application is not going to do any inserts or updates to the database then I would suggest you try with minimal permissions (probably only Select). This will make your database less vulnerable.

good luck...

bullpit

[SQL2k5] Dynamic SQL Query Select on all user tables

In one query, I would like to query every user table in a specified database for

SELECT TOP (3) COUNT_BIG([Event Count]) AS EventNum, [Target IP], MAX([Time]) as LastSeen
GROUP BY [Target IP]
ORDER BY EventNum DESC

How is this possible?

Please give examples, I am a beginner.

Assume every table has the same structure for columns event count, target ip, and time.

Forgot, for every table it selects from, the table name must be one of the returned columns.|||

Hi, try the following:

sp_msforeachtable 'SELECT *,''?'' AS TableName FROM (SELECT TOP (3) , COUNT_BIG([Event Count]) AS EventNum, [Target IP], MAX([Time]) as LastSeen
FROM ?
GROUP BY [Target IP]
ORDER BY EventNum DESC) SubQuery'

But keep in mind that this should be just used for administrative task and adhoc queries not for business logic as the procedure is not documented, therefore not supported and could be deprecated in the next version of SQL Server.

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

[SQL Server]Login failed for user

I am using Crystal Reports, VB.net, SQL Server 2000sp3, MDAC 8.0, and Windows XP. When I try to make a connection from a Crystal Report to SQL Server via an ODBC connection, I get the following message:

Details: 28000:[Microsoft][SQL Server Driver][SQL Server]Login failed for user domain/username

In the odbc, I specify SQL Server authentication and provide a username and password but these are being ignored. Why is Windows authentication being used in place of the SQL Server authentication that I specify in my odbc?

Thanks in advance,

MattThat's Crystal for you :)|||What version of Crystal Reports are you using?|||Originally posted by awsterling
What version of Crystal Reports are you using?

I am the version that came with Visual Studio .Net 2003. I don't know how to get the version number from it.

Thanks.|||Get ActiveReport from DataDynamics and you won't have to deal with these issues any more :)

[scripts] prompting user

please, how to promt user in mu sql 2005 script? For example, I need user to
confirm some operations.. .

Thx in advance"fireball" <fireball@.onet.kropka.euwrote in message
news:emdkdr$7ro$1@.nemesis.news.tpi.pl...

Quote:

Originally Posted by

please, how to promt user in mu sql 2005 script? For example, I need user
to confirm some operations.. .
>

Unless something has changed in T-SQL 2005 that I'm not aware of, you can't.

Quote:

Originally Posted by

Thx in advance
>

|||that is cruel.|||so, I neither I can't call one script from another (from the batch I run),
right?

:(|||"fireball" <fireball@.onet.kropka.euwrote in message
news:eme57k$mnn$1@.nemesis.news.tpi.pl...

Quote:

Originally Posted by

so, I neither I can't call one script from another (from the batch I run),
right?

No, you can do that.

Quote:

Originally Posted by

>
:(
>

|||fireball (fireball@.onet.kropka.eu) writes:

Quote:

Originally Posted by

please, how to promt user in mu sql 2005 script? For example, I need
user to confirm some operations.. .

The answer is you don't. SQL Server is a server application, and
server applications communicate with client applications, not with
end users.

--
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|||Sql is 4gl and asking it to do 3gl operations like prompts and loops
and if then else logic should be illegal although now allowed.

fireball wrote:

Quote:

Originally Posted by

please, how to promt user in mu sql 2005 script? For example, I need user to
confirm some operations.. .
>
Thx in advance

|||well,
is that possible at least, to exit script on given condition?|||fireball wrote:

Quote:

Originally Posted by

well,
is that possible at least, to exit script on given condition?

Do you want a return code or not? If you don't want a return code
then you can wirite:
If <conditionReturn
If you want a return code then you need to use a stored proc and inside
the procedure you can write:
If <conditionreturn 100
In your application you can check for status of the called procedure
and take action based on that.|||fireball (fireball@.onet.kropka.eu) writes:

Quote:

Originally Posted by

is that possible at least, to exit script on given condition?

Depends on how run it. In SQLCMD or OSQL you can exit immediately
by using a RAISERROR with state 127:

RAISERROR('Got a good reson for taking the easy way out now', 16, 127)

This does not work from Query Analyzer or Mgmt Studio.

--
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|||Uzytkownik "Erland Sommarskog" <esquel@.sommarskog.senapisal w wiadomosci

Quote:

Originally Posted by

RAISERROR('Got a good reson for taking the easy way out now', 16, 127)

Uzytkownik <othellomy@.yahoo.comnapisal w wiadomosci

Quote:

Originally Posted by

If <conditionReturn

all this hepled.

[S0001][unixODBC][Easysoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'test1'

Hello,

I am new to SQL Server, sorry for my ignorance !!!

I have installed SQL Server 2005 Express on my desktop (Win 2000 Pro.)

I have created a new database.

I just created a new login/user (SQL Authentication) and when I try to login, it

errors out:

[S0001][unixODBC][Easysoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'test1'

I believe the error is from SQL Server and not from ODBC driver.

I am also only able to connect by using my own userid (the one that I am connected to my desktop): domain\userid, and then I am only able to connect to 'master' db:

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

/usr/local/etc>isql SQLSERVER_POS -v
++
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
++
SQL> use pcspos
[S0001][unixODBC][Easysoft][ODBC SQL Server Driver][SQL Server]Could not locate entry in sysdatabases for database 'pcspos'. No entry found with that name. Make sure that the name is entered correctly.
[ISQL]ERROR: Could not SQLExecute
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Hello,

While I browse the SSMSE (SQL Server Management Studion Express) , I see that the database state (from the

Database Properties Windows) is NORMAL | SHUTDOWN !!!!!!

Is this OK ?

In the meantime when I login via SSMSE, I can see all the tables and data in the database !!!!!!!!!

Thanks,

Tom

|||

Hello, I was able to change the Database State to NORMAL (I changed the 'Auto Close' option to False).

BUT I am still not able to connect:

/usr/local/etc>isql SQLSERVER_POS -v
[S0001][unixODBC][Easysoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'test1'.
[ISQL]ERROR: Could not SQLConnect

|||If you are using Windows Authentication only, you cannot use SQL Server logins to connect to the server. (see how to switch the authentication mode on my website under the Screencasts). If you want to use WIndows authentication you will have to be aware that you cannot explicitly pass a user name within the connectionstring, the username is passed implicitly through Windows. If you have enabled Mixed authentication already, make sure that you are using the right password.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens, thank you for your reply.

I am using the SQL Authentication mode, see below I am able to connect from the DOS prompt (localy),

but I can NOT connect from a Linux server by using the ODBC driver:

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

C:\>isql -S (local)\SQLEXPRESS -U pos_sql05 -P passwd
1> select db_name()
2> go

--

--

--
POS

(1 row affected)
1> select * from pcs_lookup_active_status;
2> go
ACTIVE_STATUS_KEY ACTIVE_STATUS_DESC
-- --
1 Active
2 Inactive
3 Deleted

(3 rows affected)

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

|||I don′t know how the tool for Linux works but if you do not pass a username using SQL Server authentication there has to be somewhere a setting file where these information is stored.
|||

I am using a config file by the name odbc.ini.

It looks like the following:

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

[SQLSERVER_POS]
Driver = /usr/local/easysoft/sqlserver/lib/libessqlsrv.so
Description = Easysoft SQL Server ODBC driver (unixODBC 2.2.12)
Server = 10.20.30.40\SQLEXPRESS
Port = 1433
Database = POS
User = pos_sql05
Password = passwd
Mars_Connection = No
Logging = 31
LogFile = /tmp/sqlserver.log
QuotedId = Yes
AnsiNPW = Yes
Language =
Version7 = No
ClientLB = No
Failover_Partner =
VarMaxAsLong = No

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

As you cann tell, the user id and password are there.

Thanks again.

Regards,

Tom

|||

Make sure that you are using the right password for the mentioned user. In addition make sure taht the user is priviledged for the POS database, cause authentication or the connection will also fail if the initial database is not one of the allowed ones. if you are not sure, try to connect to the master database. A user which is granted access to the server should have access to the master database.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens, thanks again.

The userid and password are correct (I am able to connect via isql from the DOS prompt).

I think the problem relies on the fact that I am coming remotetly (from a Linux server), BUT I have

enabled the TCP/IP protocol connection properties. What else can I look for ?

Thank you.

Tom

|||

How are you authenticating? Is your server set to authenticate using Windows authentication only or integrated authentication? Perhaps this unix driver is trying to use SQL authentication and your SQL installation only accepts windows authenticated connections. That would explain this problem.

Hope this helps!

John (MSFT)

|||

Hi John & Jens, the issue has been resolved. I contacted the ODBC driver's vendor and they provided me with an updated copy of it.

Thank you for your help and suggestions.

Keep up the GREAT job.

Best regards,

Tom

[OTP] Multi User Application Database Transaction Stability

I am currently developing a multi user VB.NET 2005 application and this is my first time to involve in multi user application. Anyway, i know that multi-user application is not as easy as standalone application. I need to take care of the database stability for each and every user. My concern is may i know how to build up this stability for each and every transaction in terms of Data insert and update? This database resides on server and the user is able to access the data through the client application.

INSERT

The primary key is generated by application when the user enters into the data insert screen and it is done by counting the total amount of records in database. If two users perform insert at the same time, may i know how to deal with these primary keys? I am sure my application must be able to generate two different primary keys for these two users based on the time gap e.g. 0.001 milli second for the first user who enters into the screen and occupy the first primary key than the second user. But i have no idea on how to translate this to code.

UPDATE

If a particular record is viewed by a user and that user peforms an updates on the record while another user updates it at the same time, i may need to figure out a solution for this else the database will really be crashed. Is there any suggestion on it? May i know how to implement lock and unlock the record? Thank you.

For the INSERT part, you can't really generate keys like that from the client side without locking the entire table on the server. You can look at use of IDENTITY column for example which allows you to generate sequential numbers on the server. The server handles the concurrency part. There are other ways to generate keys on the client like using GUID for example. But all of these depends on the type of application you are writing and your data model.

For the UPDATE part, when one connection is updating a particular row or rows then any other connection that tries to update the same row(s) will be blocked. So the concurrent updates will happen in serial fashion depending on which ran first.

My suggestion would be to look at some of the topics on concurrency, locking, transactions in Books Online to get an idea of what the database engine can do. You should also get a book on SQL Server that describes some of these concepts so you don't try to invent or reinvent elaborate techniques on the client which might be suspect at best.

[newbe] SQL number of querries / connections limits ?

Hi all!
Can you tell me if there are any default or manually set limits of
subsequent querries / connections per user or database? I couldnt find
any in Enterprise Manager, but my Perl / ASP aplications seems to behave
as if it were some.
Perl script (DBI + Sybase DBD) is doing some quite massive inserts (a
2000 - 3000 per hour). After doing a couple of thosands it sudenly stops
- querry is sent but no result returns. If I limit the numer of being
sent per minute - the program seems to work propotionally longer, but
finally 'hangs' in the very same well.
Moreover, ASP IIS based cooperating aplication seems to reach the same
limit - after some time querries (actually doing SELECTS on data
previously INSERTED by Perl script) are processed longer than ASP
timeout allows, well the actually hangs, Profiler shows some crazy
processing times. If I reduce the number of queries per transaction -
namely changing a subquerry to join construction - it works ok. It all
makes me think I hit some limits.
I've searched the WEB archives (my MS coleeges' knowledge as well )
but couldn't find much - does any body knows sth about it?
pk
There is no hard limit on the number of connections you can have to a SQL
Server. But every connection takes up about 40KB of server memory, so there
are definitely practical limitations. Make sure that all your code drops the
connections to the server that are no longer needed.
Jacco Schalkwijk
SQL Server MVP
"Piotr Kowalski" <wywaltopkwlski@.oitotez2.pl> wrote in message
news:ctl2ea$oa9$1@.atlantis.news.tpi.pl...
> Hi all!
> Can you tell me if there are any default or manually set limits of
> subsequent querries / connections per user or database? I couldnt find any
> in Enterprise Manager, but my Perl / ASP aplications seems to behave as if
> it were some.
> Perl script (DBI + Sybase DBD) is doing some quite massive inserts (a
> 2000 - 3000 per hour). After doing a couple of thosands it sudenly stops -
> querry is sent but no result returns. If I limit the numer of being sent
> per minute - the program seems to work propotionally longer, but finally
> 'hangs' in the very same well.
> Moreover, ASP IIS based cooperating aplication seems to reach the same
> limit - after some time querries (actually doing SELECTS on data
> previously INSERTED by Perl script) are processed longer than ASP timeout
> allows, well the actually hangs, Profiler shows some crazy processing
> times. If I reduce the number of queries per transaction - namely changing
> a subquerry to join construction - it works ok. It all makes me think I
> hit some limits.
> I've searched the WEB archives (my MS coleeges' knowledge as well ) but
> couldn't find much - does any body knows sth about it?
> pk
|||Jacco Schalkwijk wrote:
> There is no hard limit on the number of connections you can have to a SQL
> Server. But every connection takes up about 40KB of server memory, so there
> are definitely practical limitations. Make sure that all your code drops the
> connections to the server that are no longer needed.
>
yeah, I've considered that.
Sybase DBI library unfortunately uses new connection every query, but
neither client's nor server's network stack reports other TCP
connections than being actually used (some 3 connection in every moment)
so older ones are properly disconnected. Maybe some "residues" still
remains on the SQL box, on the aplication layer, which aren't counted by
netstat but still reserve some resources (RAM). Enterprise manager shows
only as many proceses as really exists (3).
What about query limit? Or other limits that could come in question in
this case?
hk

[newbe] SQL number of querries / connections limits ?

Hi all!
Can you tell me if there are any default or manually set limits of
subsequent querries / connections per user or database? I couldnt find
any in Enterprise Manager, but my Perl / ASP aplications seems to behave
as if it were some.
Perl script (DBI + Sybase DBD) is doing some quite massive inserts (a
2000 - 3000 per hour). After doing a couple of thosands it sudenly stops
- querry is sent but no result returns. If I limit the numer of being
sent per minute - the program seems to work propotionally longer, but
finally 'hangs' in the very same well.
Moreover, ASP IIS based cooperating aplication seems to reach the same
limit - after some time querries (actually doing SELECTS on data
previously INSERTED by Perl script) are processed longer than ASP
timeout allows, well the actually hangs, Profiler shows some crazy
processing times. If I reduce the number of queries per transaction -
namely changing a subquerry to join construction - it works ok. It all
makes me think I hit some limits.
I've searched the WEB archives (my MS coleeges' knowledge as well :))
but couldn't find much - does any body knows sth about it?
pkThere is no hard limit on the number of connections you can have to a SQL
Server. But every connection takes up about 40KB of server memory, so there
are definitely practical limitations. Make sure that all your code drops the
connections to the server that are no longer needed.
--
Jacco Schalkwijk
SQL Server MVP
"Piotr Kowalski" <wywaltopkwlski@.oitotez2.pl> wrote in message
news:ctl2ea$oa9$1@.atlantis.news.tpi.pl...
> Hi all!
> Can you tell me if there are any default or manually set limits of
> subsequent querries / connections per user or database? I couldnt find any
> in Enterprise Manager, but my Perl / ASP aplications seems to behave as if
> it were some.
> Perl script (DBI + Sybase DBD) is doing some quite massive inserts (a
> 2000 - 3000 per hour). After doing a couple of thosands it sudenly stops -
> querry is sent but no result returns. If I limit the numer of being sent
> per minute - the program seems to work propotionally longer, but finally
> 'hangs' in the very same well.
> Moreover, ASP IIS based cooperating aplication seems to reach the same
> limit - after some time querries (actually doing SELECTS on data
> previously INSERTED by Perl script) are processed longer than ASP timeout
> allows, well the actually hangs, Profiler shows some crazy processing
> times. If I reduce the number of queries per transaction - namely changing
> a subquerry to join construction - it works ok. It all makes me think I
> hit some limits.
> I've searched the WEB archives (my MS coleeges' knowledge as well :)) but
> couldn't find much - does any body knows sth about it?
> pk|||Jacco Schalkwijk wrote:
> There is no hard limit on the number of connections you can have to a SQL
> Server. But every connection takes up about 40KB of server memory, so there
> are definitely practical limitations. Make sure that all your code drops the
> connections to the server that are no longer needed.
>
yeah, I've considered that.
Sybase DBI library unfortunately uses new connection every query, but
neither client's nor server's network stack reports other TCP
connections than being actually used (some 3 connection in every moment)
so older ones are properly disconnected. Maybe some "residues" still
remains on the SQL box, on the aplication layer, which aren't counted by
netstat but still reserve some resources (RAM). Enterprise manager shows
only as many proceses as really exists (3).
What about query limit? Or other limits that could come in question in
this case?
hk

[newbe] SQL number of querries / connections limits ?

Hi all!
Can you tell me if there are any default or manually set limits of
subsequent querries / connections per user or database? I couldnt find
any in Enterprise Manager, but my PERL / ASP aplications seems to behave
as if it were some.
Perl script (DBI + Sybase DBD) is doing some quite massive inserts (a
2000 - 3000 per hour). After doing a couple of thosands it sudenly stops
- querry is sent but no result returns. If I limit the numer of being
sent per minute - the program seems to work propotionally longer, but
finally 'hangs' in the very same well.
Moreover, ASP IIS based cooperating aplication seems to reach the same
limit - after some time querries (actually doing SELECTS on data
previously INSERTED by PERL script) are processed longer than ASP
timeout allows, well the actually hangs, Profiler shows some crazy
processing times. If I reduce the number of queries per transaction -
namely changing a subquerry to join construction - it works ok. It all
makes me think I hit some limits.
I've searched the WEB archives (my MS coleeges' knowledge as well )
but couldn't find much - does any body knows sth about it?
pkThere is no hard limit on the number of connections you can have to a SQL
Server. But every connection takes up about 40KB of server memory, so there
are definitely practical limitations. Make sure that all your code drops the
connections to the server that are no longer needed.
Jacco Schalkwijk
SQL Server MVP
"Piotr Kowalski" <wywaltopkwlski@.oitotez2.pl> wrote in message
news:ctl2ea$oa9$1@.atlantis.news.tpi.pl...
> Hi all!
> Can you tell me if there are any default or manually set limits of
> subsequent querries / connections per user or database? I couldnt find any
> in Enterprise Manager, but my PERL / ASP aplications seems to behave as if
> it were some.
> PERL script (DBI + Sybase DBD) is doing some quite massive inserts (a
> 2000 - 3000 per hour). After doing a couple of thosands it sudenly stops -
> querry is sent but no result returns. If I limit the numer of being sent
> per minute - the program seems to work propotionally longer, but finally
> 'hangs' in the very same well.
> Moreover, ASP IIS based cooperating aplication seems to reach the same
> limit - after some time querries (actually doing SELECTS on data
> previously INSERTED by PERL script) are processed longer than ASP timeout
> allows, well the actually hangs, Profiler shows some crazy processing
> times. If I reduce the number of queries per transaction - namely changing
> a subquerry to join construction - it works ok. It all makes me think I
> hit some limits.
> I've searched the WEB archives (my MS coleeges' knowledge as well ) but
> couldn't find much - does any body knows sth about it?
> pk|||Jacco Schalkwijk wrote:
> There is no hard limit on the number of connections you can have to a SQL
> Server. But every connection takes up about 40KB of server memory, so ther
e
> are definitely practical limitations. Make sure that all your code drops t
he
> connections to the server that are no longer needed.
>
yeah, I've considered that.
Sybase DBI library unfortunately uses new connection every query, but
neither client's nor server's network stack reports other TCP
connections than being actually used (some 3 connection in every moment)
so older ones are properly disconnected. Maybe some "residues" still
remains on the SQL box, on the aplication layer, which aren't counted by
netstat but still reserve some resources (RAM). Enterprise manager shows
only as many proceses as really exists (3).
What about query limit? Or other limits that could come in question in
this case?
hk

[MS Sql] Data type nvarchar

Hi everybody,

I'm using SQL Server Management Studio Express.
I'm trying to create a field which contains a text entered by the user. So, it should be able to contain at least 500 characters.

I used the type "nvarchar(MAX)". The problem is that the type contains about 50 characters max!!
I couldn't find out where and how to fix that.
If you have any idea :)

Thanks a lotType over the the MAX with the desired length of 500.|||Hi tmorton
Of course I tried nvarchar(500) (forgot to tell that). But it's not working either.
It sounds like nvarchar(MAX) is the max length type, and limited to about 50...
Thanks for your reply. And if someone has any idea :)|||That's all it takes from the SQL Server end. If you are only managing to store 50 characters in that column, then I would venture a guess that your data is being truncated before it hits the database. What does your code look like that is adding the data to your table?|||I believe that SQL Management Studio truncates text that it displays. Use something else to view/edit the column.|||Hi
It's weird, it's working now. And yet I didn't change anything particular. Maybe a "bug" on SQL Server... weird though. Thanks a lot to you guys :)

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name

From Enterpise Manager I create a user, giving all available rights. I have created a DB and set this user to be everything for it (except deny). I create tables as this user on the above DB. When I try to access these tables with an app using ODBC, the specific error appears.
Please assist fellas - situation critical.
Thanx.Refer to this KBA (http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q218/9/95.asp&NoWebContent=1) to resolve the issue.|||Thanx for the info but it didn't work.|||If you created the objects as the user, then they are owned by the user, and will be named [ownername].[objectname].

If your sql code does not specify a ownername sqlserver assumes dbo ownership, and your objects don't exists as dbo.[objectname].

Either specify the owner name when you reference the objects,
or instead of granting all individual rights to the user just grant it dbo access to the database. Then any objects it creates will be dbo owned and will not need to be fully referenced.

blindman

[Feature Request] Jump to Report (Choice of report to the user)

I am trying to build a report with a "jump to report" (with all the
applicable parameters) effect in a table.
But based on user's choice - I somehow want to let them choose which
report they can jump on.
How may I present them the choices?
One idea I had was to create a dummy report that stores all the
parameters, and then provide different report options...You might want to use jump to url instead. Here is an idea: You have a
report that shows the values you want to have the user select from. Just
show it in a table. Have one of the fields (could be an extra field that
says, Detail ... or something like that. Make it blue and underlined. Then
set the advanced properties and use the jump to URL setting the appropriate
parameters. Depending on how complicated it is you might want to create code
behind that assembles the URL string for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Harsh" <harsh.vb@.gmail.com> wrote in message
news:1e70ecdd.0411130826.3d172549@.posting.google.com...
>I am trying to build a report with a "jump to report" (with all the
> applicable parameters) effect in a table.
> But based on user's choice - I somehow want to let them choose which
> report they can jump on.
> How may I present them the choices?
>
> One idea I had was to create a dummy report that stores all the
> parameters, and then provide different report options...|||In Jump to dialog box, where it asked the name of the report to jump to, do
an
IIF(Fields!xxx.Value = "A", "AReport", IIF(Fields!xxx.Value = "B",
"BReport", "")
"Harsh" wrote:
> I am trying to build a report with a "jump to report" (with all the
> applicable parameters) effect in a table.
> But based on user's choice - I somehow want to let them choose which
> report they can jump on.
> How may I present them the choices?
>
> One idea I had was to create a dummy report that stores all the
> parameters, and then provide different report options...
>

[Fail] Could not add ASPNET user to SQL Server.

Ok here's my setup

1. Installed msde 2000 with sp3.
2. started the SQL Server and SQL Server Agent.
3. Ran IBuySpy Store Installation, when I ran the configuration I get error on bottum.

10:51:55 PM Tuesday, September 23, 2003: [Checking system requirements]
10:51:55 PM Tuesday, September 23, 2003: [Pass] Detected a local instance of SQL Server.
10:51:55 PM Tuesday, September 23, 2003: [Pass] Determined SQL Server version (8.00.194).
10:51:55 PM Tuesday, September 23, 2003: [Pass] Detected .NET Framework.
10:51:55 PM Tuesday, September 23, 2003: [Pass] Detected Internet Information Server (IIS).
10:52:03 PM Tuesday, September 23, 2003: [Begin Sample Configuration]
10:52:05 PM Tuesday, September 23, 2003: [Pass] Created IIS virtual directory.: StoreVBVS
10:52:05 PM Tuesday, September 23, 2003: [Pass] Determined SQL Server version (8.00.194).
10:52:22 PM Tuesday, September 23, 2003: [Fail] Could not add ASPNET user to SQL Server.
SQL Server does not exist or access denied.
10:52:22 PM Tuesday, September 23, 2003: [Done]

Can someone please tell me step by step on what I need to do and where to do it to get this working, thanks !!!did you add it via users in your db? when you xpand our db in sql server xplorer, you can add users using "users"...

HTH

[?] User's global variables

Hello,
Is it possible to define a global variable within a report? It has to be
populated in one DataSet and accessed from other DataSets.
Thanks,
Lucy.Related previous post:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=d0f4d02b-b032-47e8-8318-169052421237.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lusy Crown" <evesq@.uk2.net> wrote in message
news:uad4evlaEHA.716@.TK2MSFTNGP11.phx.gbl...
> Hello,
> Is it possible to define a global variable within a report? It has to be
> populated in one DataSet and accessed from other DataSets.
> Thanks,
> Lucy.
>|||Probably not ;-(
How can I populate such variable (declared in custom code) in a DataSet?
Thanks,
Lucy.
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:%23gh%23zcnaEHA.2056@.TK2MSFTNGP12.phx.gbl...
> Related previous post:
>
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=d0f4d02b-b032-47e8-8318-169052421237.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Lusy Crown" <evesq@.uk2.net> wrote in message
> news:uad4evlaEHA.716@.TK2MSFTNGP11.phx.gbl...
> > Hello,
> >
> > Is it possible to define a global variable within a report? It has to be
> > populated in one DataSet and accessed from other DataSets.
> >
> > Thanks,
> > Lucy.
> >
> >
>

@query error

ODBC error 4604 (42000) There is no such user or group 'sa8'

this is the error message I get when I run my stored procedure that has the @.query parameter and I have been told this is a bug within sql that microsoft doesn't seem to be aware off.

Are you coming across this and what do you do to go around it?

RegardsHi:

I have found this "solution". Please check if you can use it ;)

We had a similar problem, the way we got around it was to add access rights to the TEMP directory in the Document and Settings folder on the account logged into the Server. As we had the NT Administrator account permanatley logged onto the server. If I tried to run a temp query from xp_sendmail it would fail, but if I used the administraor account it worked okay. The query writes the output file it needs to to the temp folder of which we had no access rights. Once I changed the access rights it worked okay. Did find this answer in one of the many SQL forums on the net. Hope this help you.|||I hope I can be more helper ;)