Creating users programmatically

Is there a way to programmatically create users for SQL server database access?Hi,
Use the below system procedures
1. sp_addlogin (create a Login)
2. sp_adduser (Create a User)
3. sp_addrolemember (To add a db role to user/group)
4. sp_addsrvrolemember (To add a server role to Login)
THanks
Hari
MCDBA
"Me" <anonymous@.discussions.microsoft.com> wrote in message
news:E59E2633-C1CB-44C2-8859-D0D05FC2CF37@.microsoft.com...
> Is there a way to programmatically create users for SQL server database
access?|||Thanks !

Creating users programmatically

Is there a way to programmatically create users for SQL server database acce
ss?Hi,
Use the below system procedures
1. sp_addlogin (create a Login)
2. sp_adduser (Create a User)
3. sp_addrolemember (To add a db role to user/group)
4. sp_addsrvrolemember (To add a server role to Login)
THanks
Hari
MCDBA
"Me" <anonymous@.discussions.microsoft.com> wrote in message
news:E59E2633-C1CB-44C2-8859-D0D05FC2CF37@.microsoft.com...

quote:

---

> Is there a way to programmatically create users for SQL server database

---

access?|||Thanks !

creating the database tables

Hi,

I need to create a table and a few fields in the SQL database programmatically in asp.net.

I know the tabes name, the server which it resides on, username/password. Would anyone have a small sample of this code?

Thanks

radI meant to say, "I know the database name."|||You might check out this article to get you started:Creating a SQL Server Database Programmatically.|||Thanks|||This is the code that I came up with. When I load the page it tells me that my error is here "Line 22: cmdCreate.ExecuteNonQuery()". What am I doing wrong?

<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SqlClient" %
<Script Runat="Server"
Sub Button_Click( s As Object, e As EventArgs )

Dim conUsers As SqlConnection
Dim strCreate As String
Dim cmdCreate As SqlCommand

conUsers = New SqlConnection( "Server=servername;UID=user;PWD=password;database=databasename" )

strCreate = "CREATE TABLE myTable" + "(Application CHAR(100), Project CHAR(100), EnteredDate CHAR(100), EnteredBy CHAR(100), Descriptions CHAR(100), SNPT CHAR(100), Automation CHAR(100), ContactName CHAR(100), ContactNumber CHAR(100), Priority CHAR(100), Status CHAR(100))"

cmdCreate = New SqlCommand( strCreate, conUsers )
conUsers.Open()
cmdCreate.ExecuteNonQuery()
conUsers.Close()

End Sub

</Script>|||Exactly what is the error message? That will help a great deal.|||I could guestimate that it's probably because you aren't setting the cmdCreate.CommandType, your UID for your server isn't a dbo in that database, you should have a space after myTable (just personal thing, shouldn't generate error), table of that name already exists..

yeah, we need what the error is before we can help you.|||Kragie, I have no idea what most of these things you said mean. I am new at asp.net.

The only error it gives me is, "CREATE TABLE permission denied in database 'databasename'.|||OK, the problem is that the SQL Server user does not have permission to add a table in the database. Give the user permission using Enterprise Manager, or use SQL Script in Query Analyzer.

USE databasename
go

GRANT CREATE TABLE TO username
go|||Thanks for the help guys, although I think the error might be incorrect.

Creating Subscriptions

Does anyone have any good examples of programmatically creating
subscriptions using the SQLRS web service ?
rgds,
JayHere is an example for RS2000.
http://www.odetocode.com/articles/114.aspx
It gets pretty complicated if you're trying to offer all the
functionalitysql

creating SQLDataSource programmatically?

Hello. Im trying to create an SQLDataSource control programmatically. I need to do this because I want to do some stuff on my MasterPage's 'Page_Init' event.

heres my code (Master.master.vb):

Protected Sub Page_Init(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.Init lblUser.Text = Page.User.Identity.NameDim PUserAs New ControlParameter PUser.ControlID ="lblUser" PUser.Name ="LoginName" PUser.PropertyName ="Text" PUser.Type = TypeCode.String PUser.DefaultValue = Page.User.Identity.NameDim SQLDS_LoginAs New SqlDataSource SQLDS_Login.ID ="SQLDS_Login" SQLDS_Login.ConnectionString ="I put conection string here. How do I use the one on my web.config?" SQLDS_Login.SelectCommand ="SELECT [LoginID], [LoginName], [Role], [Status] FROM [myLogin] WHERE ([LoginName] = @.LoginName)" SQLDS_Login.SelectParameters.Add(PUser) SQLDS_Login.SelectCommandType = SqlDataSourceCommandType.Text GridView1.DataSource = SQLDS_Login GridView1.DataBind()End Sub

When i run, i get this error message:

The SqlDataSource control 'SQLDS_Login' does not have a naming container. Ensure that the control is added to the page before calling DataBind.

I never had any problem with Inserts, Updates and Deleting, but I have never made it work for Select when doing it programmatically.

Can you help me with this?

as the error states you need to add it to the page i.e. me.controls.add(SQLDS_Login) [or if you're doing it from a page that uses that for a master then me.master.controls.add(SQLDS_login)

Creating SQL statements programmatically from listbox (ADVANCED)

I am creating a page that creates a report based on a dynamically created SQL statement which is created by user input.

Everything is good except for the WHERE section, which is created from values in a list box.

For Example:
lstCriteria.items(1).value = "COMPANY = 'foo'"
lstCriteria.items(2).value = "DAY= 2"

I build my SQL statement with these values like so:
SELECT * FROM POO WHERE COMPANY = 'foo' AND DAY = 2

The problem I am having is when there are multiple values of the same type in the list box. Say:
lstCriteria.items(1).value = "COMPANY = 'foo'"
lstCriteria.items(2).value = "DAY= 2"
lstCriteria.items(1).value = "COMPANY = 'moo'"

My employer wants this to be valid, but I am having a tough time coming up with a solution.

I know that my SQL statement needs to now read:
SELECT * FROM POO WHERE COMPANY = 'foo' AND DAY = 2 OR COMPANY = 'poo' AND DAY = 2

I have code set up to read the values of each list box item up to the "=". And I know that I need to compair this value with the others in the list box...but I am not running into any good solutions.

Any HELP?How about OR like values together?

SELECT * FROM POO WHERE (COMPANY = 'foo' OR COMPANY = 'poo') AND DAY = 2

|||Yes, that would work. But I am looking more at how to extract this data from the listbox to a usable format. I am working on setting the first value (the left(N) characters of the listbox item, which is also a column name) in an array, then inserting the secondvalue, checking if it is in the array, if it is, adding it to the array. If it is not, then adding it to a new array. This way I could loop thought the arrays and create my where statement...but It's just a thought on a whiteboard now.

Any Other suggestions?

Creating SQL Server 2005 Mobile Edition On Desktop

Hello Everybody

I want to create SQL server 2005 mobile database on desktop programmatically with some inital data for my application and load it into device. We can create database only in VS2005 server explorer or SQL server 2005 application.

Anybody having this solution for this prob.

Regards

Chikuu

Have you had any chance to look at:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=16369&SiteID=1

Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

|||

Hello

My problem is solved using this link. I had copied those dll's into bin folder it woks fine on any windows desktop running VS2005.

Thanks

Chikuu

|||

Glad that I could help you.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Hello

We are trying to install sdf creation code on windows server 2000, but it is giving message that ' sql mobile usage is restricted on this platform to use sql mobile you need dot net 2005,sql server 2005 or Tablet PC sku' i got this message eralier when i am installing it on windows server 2003 that time i had installed sql mobile sdk and then this message is not shown. Is there another solution for windows sever 2000, specifically in destributing these application.

Thanks

Chekku

|||

Chekku,

You really do need to install VS2005 or SS2005 on your Windows 2000 server to use SQL Mobile. It's part of the licensing.

-Darren

|||Hopefully the problems will be sorted with SQL Everywhere. You CAN get around the problem and still stay within Microsoft licencing terms without having to have Visual Studio, etc:

1) Download SQL Express from Microsoft (free download)

2) Extract to a folder using the /x option

3) In the 'Setup' folder there is a file called 'SqlSupport.msi'. Install this on your target PC (only a 9MB file so no big deal).

4) Also on your target PC, deploy the following files along with your app:

sqlceca30.dll

The SQL Server Mobile Client Agent. Required for applications that connect to SQL Server by using replication or remote data access.

sqlcecompact30.dll

Provides the compact database functionality. Required if your application will use compaction.

sqlceer30[language].dll

Contains error strings for SQL Server Mobile-generated errors. Required for all SQL Server Mobile applications.

sqlceme30.dll

Contains code required by the System.Data.SqlServerCe.dll file. Required for all SQL Server Mobile applications.

sqlceoledb30.dll

Provides OLE DB connectivity to SQL Server Mobile databases. Required only if your application uses OLE DB to connect to the SQL Server Mobile database.

sqlceqp30.dll

The SQL Server Mobile Query Processor. Required for all SQL Server Mobile applications.

sqlcese30.dll

The SQL Server Mobile Engine. Required for all SQL Server Mobile applications.

System.Data.SqlServerCe.dll

Seems bizarre how a free download keeps the licence happy !

Creating SQL Server 2005 Mobile Edition On Desktop

Hello Everybody

I want to create SQL server 2005 mobile database on desktop programmatically with some inital data for my application and load it into device. We can create database only in VS2005 server explorer or SQL server 2005 application.

Anybody having this solution for this prob.

Regards

Chikuu

Have you had any chance to look at:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=16369&SiteID=1

Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

|||

Hello

My problem is solved using this link. I had copied those dll's into bin folder it woks fine on any windows desktop running VS2005.

Thanks

Chikuu

|||

Glad that I could help you.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Hello

We are trying to install sdf creation code on windows server 2000, but it is giving message that ' sql mobile usage is restricted on this platform to use sql mobile you need dot net 2005,sql server 2005 or Tablet PC sku' i got this message eralier when i am installing it on windows server 2003 that time i had installed sql mobile sdk and then this message is not shown. Is there another solution for windows sever 2000, specifically in destributing these application.

Thanks

Chekku

|||

Chekku,

You really do need to install VS2005 or SS2005 on your Windows 2000 server to use SQL Mobile. It's part of the licensing.

-Darren

|||Hopefully the problems will be sorted with SQL Everywhere. You CAN get around the problem and still stay within Microsoft licencing terms without having to have Visual Studio, etc:

1) Download SQL Express from Microsoft (free download)

2) Extract to a folder using the /x option

3) In the 'Setup' folder there is a file called 'SqlSupport.msi'. Install this on your target PC (only a 9MB file so no big deal).

4) Also on your target PC, deploy the following files along with your app:

sqlceca30.dll

The SQL Server Mobile Client Agent. Required for applications that connect to SQL Server by using replication or remote data access.

sqlcecompact30.dll

Provides the compact database functionality. Required if your application will use compaction.

sqlceer30[language].dll

Contains error strings for SQL Server Mobile-generated errors. Required for all SQL Server Mobile applications.

sqlceme30.dll

Contains code required by the System.Data.SqlServerCe.dll file. Required for all SQL Server Mobile applications.

sqlceoledb30.dll

Provides OLE DB connectivity to SQL Server Mobile databases. Required only if your application uses OLE DB to connect to the SQL Server Mobile database.

sqlceqp30.dll

The SQL Server Mobile Query Processor. Required for all SQL Server Mobile applications.

sqlcese30.dll

The SQL Server Mobile Engine. Required for all SQL Server Mobile applications.

System.Data.SqlServerCe.dll

Seems bizarre how a free download keeps the licence happy !

Creating SQL Server 2005 Mobile Edition On Desktop

Hello Everybody

I want to create SQL server 2005 mobile database on desktop programmatically with some inital data for my application and load it into device. We can create database only in VS2005 server explorer or SQL server 2005 application.

Anybody having this solution for this prob.

Regards

Chikuu

Have you had any chance to look at:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=16369&SiteID=1

Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

|||

Hello

My problem is solved using this link. I had copied those dll's into bin folder it woks fine on any windows desktop running VS2005.

Thanks

Chikuu

|||

Glad that I could help you.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Hello

We are trying to install sdf creation code on windows server 2000, but it is giving message that ' sql mobile usage is restricted on this platform to use sql mobile you need dot net 2005,sql server 2005 or Tablet PC sku' i got this message eralier when i am installing it on windows server 2003 that time i had installed sql mobile sdk and then this message is not shown. Is there another solution for windows sever 2000, specifically in destributing these application.

Thanks

Chekku

|||

Chekku,

You really do need to install VS2005 or SS2005 on your Windows 2000 server to use SQL Mobile. It's part of the licensing.

-Darren

|||Hopefully the problems will be sorted with SQL Everywhere. You CAN get around the problem and still stay within Microsoft licencing terms without having to have Visual Studio, etc:

1) Download SQL Express from Microsoft (free download)

2) Extract to a folder using the /x option

3) In the 'Setup' folder there is a file called 'SqlSupport.msi'. Install this on your target PC (only a 9MB file so no big deal).

4) Also on your target PC, deploy the following files along with your app:

sqlceca30.dll

The SQL Server Mobile Client Agent. Required for applications that connect to SQL Server by using replication or remote data access.

sqlcecompact30.dll

Provides the compact database functionality. Required if your application will use compaction.

sqlceer30[language].dll

Contains error strings for SQL Server Mobile-generated errors. Required for all SQL Server Mobile applications.

sqlceme30.dll

Contains code required by the System.Data.SqlServerCe.dll file. Required for all SQL Server Mobile applications.

sqlceoledb30.dll

Provides OLE DB connectivity to SQL Server Mobile databases. Required only if your application uses OLE DB to connect to the SQL Server Mobile database.

sqlceqp30.dll

The SQL Server Mobile Query Processor. Required for all SQL Server Mobile applications.

sqlcese30.dll

The SQL Server Mobile Engine. Required for all SQL Server Mobile applications.

System.Data.SqlServerCe.dll

Seems bizarre how a free download keeps the licence happy !

Creating SQL Server 2005 Mobile Edition On Desktop

Hello Everybody

I want to create SQL server 2005 mobile database on desktop programmatically with some inital data for my application and load it into device. We can create database only in VS2005 server explorer or SQL server 2005 application.

Anybody having this solution for this prob.

Regards

Chikuu

Have you had any chance to look at:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=16369&SiteID=1

Thanks,

Laxmi NRO, MSFT, SQL Mobile, Microsoft Corporation

|||

Hello

My problem is solved using this link. I had copied those dll's into bin folder it woks fine on any windows desktop running VS2005.

Thanks

Chikuu

|||

Glad that I could help you.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Hello

We are trying to install sdf creation code on windows server 2000, but it is giving message that ' sql mobile usage is restricted on this platform to use sql mobile you need dot net 2005,sql server 2005 or Tablet PC sku' i got this message eralier when i am installing it on windows server 2003 that time i had installed sql mobile sdk and then this message is not shown. Is there another solution for windows sever 2000, specifically in destributing these application.

Thanks

Chekku

|||

Chekku,

You really do need to install VS2005 or SS2005 on your Windows 2000 server to use SQL Mobile. It's part of the licensing.

-Darren

|||Hopefully the problems will be sorted with SQL Everywhere. You CAN get around the problem and still stay within Microsoft licencing terms without having to have Visual Studio, etc:

1) Download SQL Express from Microsoft (free download)

2) Extract to a folder using the /x option

3) In the 'Setup' folder there is a file called 'SqlSupport.msi'. Install this on your target PC (only a 9MB file so no big deal).

4) Also on your target PC, deploy the following files along with your app:

sqlceca30.dll

The SQL Server Mobile Client Agent. Required for applications that connect to SQL Server by using replication or remote data access.

sqlcecompact30.dll

Provides the compact database functionality. Required if your application will use compaction.

sqlceer30[language].dll

Contains error strings for SQL Server Mobile-generated errors. Required for all SQL Server Mobile applications.

sqlceme30.dll

Contains code required by the System.Data.SqlServerCe.dll file. Required for all SQL Server Mobile applications.

sqlceoledb30.dll

Provides OLE DB connectivity to SQL Server Mobile databases. Required only if your application uses OLE DB to connect to the SQL Server Mobile database.

sqlceqp30.dll

The SQL Server Mobile Query Processor. Required for all SQL Server Mobile applications.

sqlcese30.dll

The SQL Server Mobile Engine. Required for all SQL Server Mobile applications.

System.Data.SqlServerCe.dll

Seems bizarre how a free download keeps the licence happy !

Creating SQL Schema using objects not script?

Hey new in the .NET SQLDMO thing...

Can you in C# programtically create a SQL DB and then create tables, references etc...programmatically without having to use Sql scripting?

What is the root namespace for this anyone?

Thanks ideas on examples much appreciated!No. SQL is a bridge between programming language and Database, so far.|||Does not matter...

I used a combination of XSD and DataSets to come up with a decent enough solution.

Eric|||Yes, you could use XML/XSL to access SQL Server with appropriate setup of SQL Server, but it seems you still need to use a combined SQL script.

Anyway, do you mind to share your sample with us?|||Yep...or use SQLXML3.0 ...

I think you can do that with and OleDb connection objects..create Schema structures in SQL.

I have it working with and AccessDB right now...using T-SQL script source dynamically.

Thanks,

Eric|||I knew and used SQLXML 3.0 and even previous version well in VS 6.0 age, but now it seems the trick has been replaced by Web Service in most scenarios. I am curious of your using XSD associated with DataSet. If with several line code you could demo the sample, would you please show me the code? Otherwise, never mind.|||yeah it is quite large...really...

I only had to do it because the XSD.exe tool barfed on my XSD it was simply too large...So had to do it manually.

But its' simply creating a defintion file for the data structure in an xsd...define tables, rows, columns, types ect...the use the XSD.exe tool and it will generate a strongly typed dataset deriving from your data structure xsd template.

now you can use that strongly typed class as destination source....iterate over your xsd find what you need to define xsd:complexTypes and xsd:elements etc...then use the strongly typed collection ..

myClassTable.Add("xsdElementToBeNamedATable") etc..for type defs column defs etc...

Then you have a complete composite structure of your db in a DataSet....you can create what ever you want from this...out put the xsd again..if you want etc...

Now you can iterate over the DataSet Tables and create your T-SQL script on that structure. Alternatively you can use a straight XmlSchema iteration over the XSD document and then inline your T-SQL script code during the looping over the various types...xsd:attributes, xsd:sequences, xsd:elements etc...

It's kinda hairy at first.

LOL|||It's kinda hairy at first.

Yes, you are right. I believe so. But, what is your reason to do so? Do you try to reach the database through HTTP protocol?|||Customer provided me a schema and nothing else...

Build me a DB...

No ERD nothing just this monster of an XSD. to the tune of like 30 imports and a file size of like 500k...humongous...

Every tool on the market barfed when trying to parse the XSD.

I have 2 Gig on my machine and serious hardware...the problem is that XMLSpyEnterprise is the only tool that I have seen any bright spots with. I wish you could modify the amount of ram that the tool uses...like the java engines allow you to do...it just runs out of memory...the program stack is simply too small.

Eric|||OK, understandable.

Although the XML stuff (including XSD) seems a new and advanced technology, it has never been a normal release as a tool for Microsoft products until the .NET age when the XML technology is still not explicitly used but with the form of Web Service. In fact, Web Service is called XML Web Service, it could let you use XML/SOAP protocol wihtout expert knowledge on it, actually without XML/SOAP knowledge on it.

In your case, you somehow has a legacy product from your customer with it quite troublesome. If this is a small piece or you have no way to control it, you might just keep it and use it. Otherwise, you'd better to upgrade it to Web Service to enjoy the advantage of .NET.

This thread,248757, might help you, too.|||Actually,

They want a DB for all kinds of reasons...I am under NDA so I can't say too much other than it involves InfoPath, Office2003, SharePont Server V2.0, SQLServer and a host of XML technologies...very cool stuff..

LOL|||Web services are fairly trivial...I mean I like then and use them for my own corporate stuff. It's a fantastic technology combine it with the WSDL.exe can you got some seriously cool prodcutvitiy tools for creating strongly typed web serviced classes.

This is different...how is using a web service going to solve my DB problem?

Just curious...

Eric|||You didn't state your DB or other problems clearly, how can I know the way to solve them. I even don't know your structure of the problem, and don't know if your problem need to use either XML or Web Service. Basically, the XML/SOAP technology is used for interplatform/interlanguage communications through HTTP protocol. A lot of people use them just in a not necesary scenatio. But, this might not be your case. You think your job is fantastic, that is fantastic.

Creating Reports at Runtime

Is it possible to create a report programmatically at runtime using Visual Studio 2005 based upon say a report layout specification file?There is a nifty tutorial on this I found.|||

Can you direct me to that tutorial please? It might help me in my situation.

Thanks!

|||

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

It's beautiful!

|||

Hi,

Can you give link for example report preview by RDL-file ?

|||

proki wrote:

Hi,

Can you give link for example report preview by RDL-file ?

You can create any report preview that you would normally create using a report server project. I'm not sure what type of link you are wanting. RDL generator offers full flexibility for creating reports. It's just a different way to go about creating the report. The same reports can be created by using either the RDL generator method or the report server project.

There's nothing new and magical looking if that's what you're asking.

Creating Reports at Runtime

Is it possible to create a report programmatically at runtime using Visual Studio 2005 based upon say a report layout specification file?There is a nifty tutorial on this I found.|||

Can you direct me to that tutorial please? It might help me in my situation.

Thanks!

|||

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

It's beautiful!

|||

Hi,

Can you give link for example report preview by RDL-file ?

|||

proki wrote:

Hi,

Can you give link for example report preview by RDL-file ?

You can create any report preview that you would normally create using a report server project. I'm not sure what type of link you are wanting. RDL generator offers full flexibility for creating reports. It's just a different way to go about creating the report. The same reports can be created by using either the RDL generator method or the report server project.

There's nothing new and magical looking if that's what you're asking.

Creating RDL using information contained in an existing Data Model

This question is in regard to creating RDL programmatically, using information contained in an existing Data Model; basically mimicking what the Report Builder does.

What I need to do is integrate a dummied down version of the Report Builder into an existing web application; where users will have the option to build and save reports using a very simple web interface, 4 step process. This process will only have a very few of the options that the Report Builder has.

So far I have built this process referencing the Microsoft Reporting Services dll's (Microsoft.ReportingServices.Modeling.dll for example), in order to retrieve the entity and attributes information from the Data Model.

** Does anyone know how I could create the RDL, using both the entity and attribute information contained in the Data Model in association with selected fields, filters, etc, that is contained in a database?

I can't find anything about this anywhere on the net; any help would be greatly appreciated!

Thanks,

Dan

There isn't an MS tool to automate this with RS 2005. To meet a similar requirement and speed up the implementation effort, we decided to develop an object wrapper on top of RDL which knows how to serialize itself to RDL using the .NET XmlSerializer. In comparison with the XML DOM alternative, we found this approach to work much better for us. You need to familiriaze yourself with the RDL specification beforehand.|||

Btw, more information about the RDL specification mentioned by Teo is available here: http://www.microsoft.com/sql/technologies/reporting/rdlspec.mspx

-- Robert

|||Well thank you very much for this information, I just wanted to make sure I was not missing out on existing functionality!

Creating new field while app running

Hi
Is it possible to add a field to an sql server 20005 table programmatically
by a vb.net app while the vb.net app has the table being viewed on a vb.net
form?
Thanks
Regardsyou can, if you use dataset for example, then your program retrieve the data
then close the connection. so there is no lock when you'll add a field.
also, adding a field is a transaction like any other transaction in the
database, so when the table become available, the field will be added
"John" <John@.nospam.infovis.co.uk> wrote in message
news:ulQbPTHoFHA.1088@.TK2MSFTNGP14.phx.gbl...
> Hi
> Is it possible to add a field to an sql server 20005 table
> programmatically by a vb.net app while the vb.net app has the table being
> viewed on a vb.net form?
> Thanks
> Regards
>|||John,
You can, however it is probably not a good idea.
You can do that with SQL "Alter" commands and use the Adonet command
(SQLClient.SQLCommand) ExecuteNonQuery
You have crossposted to more newsgroups, did you know that the real
newsgroup for your question is
microsoft.public.dotnet.framework.adonet
I hope this helps,
Cor|||or maybe
microsoft.public.sqlserver.programming ' ( as there are more ways to
perform this as with ADO.Net )
ofcourse it is always the est in a managed situation to let the DBA perform
these tasks , as changing columns might also add the need of performance
analyzing of the database and take apropriate actions that might affect the
database in a possitive way ( adding deleting indexes , constrains etc etc )
if your program is also responsible for the managing task ( like a user
level deployed MSDE database ) it is a good idea in my opinion to do the
following
perform the wanted tasks on a sql server ,with enterprise manager , optimize
the database, now export all your changes as SQL files
you can now import these files in your deployed sql instances either with a
shell call to the command line tools or just read them in and execute the
statements on the connection object ( ofcourse you need to log in with SA ,
administrator rights to do that , or with a pre defined user that was
granted the apropriate rights on the database to perform these actions )
regards
Michel Posseth
"Cor Ligthert [MVP]" <notmyfirstname@.planet.nl> wrote in message
news:enuiN7JoFHA.2904@.TK2MSFTNGP14.phx.gbl...
> John,
> You can, however it is probably not a good idea.
> You can do that with SQL "Alter" commands and use the Adonet command
> (SQLClient.SQLCommand) ExecuteNonQuery
> You have crossposted to more newsgroups, did you know that the real
> newsgroup for your question is
> microsoft.public.dotnet.framework.adonet
> I hope this helps,
> Cor
>|||John (John@.nospam.infovis.co.uk) writes:
> Is it possible to add a field to an sql server 20005 table
> programmatically by a vb.net app while the vb.net app has the table
> being viewed on a vb.net form?
Well, depends on what you mean with possible. But since ADO .Net is
disconnected, there are no locks on the table preventing columns to
be added by you or anyone else.
What you will happen to your VB app next time it access the table
I don't know. Since, a table is intended to be a static entity which
changes only on application upgrades, it's not that your dataset is
going to change automatically. There may be methods to add columns,
though.
Anyway, unless this is a temp table you are working with, you are
probably off-track here. Describe you real business problem, and you
may get better approaches to solve that problem.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

creating jobs programmatically causes deadlocks

I'm using the transactional sproc below to create jobs in an automated
fashion that will run cmdexecs after a configurable pause. This is
potentially HIGH VOLUME! The sproc is run from a VB.NET EXE using the
ADO.NET command.executenonquery method during the processing of text files to
determine what the job should do.
I have two essentially identical servers (4 X 3.2GHz CPU, 3GB RAM). On one
server, dropping 30 trigger files results in ~150 jobs being created in ~30
seconds (I have a thread.sleep(200) between each job creation due to another
issue). On another server dropping just 10 of the trigger files causes
deadlocks consistently. Both servers are running SQL 2000 SP3 on WinServer
2003 Standard edition and
the assembly is written against DNF 1.1.
Is the logic in the sproc faulty? The transaction is necessary since the
job needs all the properties to do it's job.
What server configurations affect deadlock timeouts, etc. Any help
would be appreciated.
Sproc follows:
--creates single use, self deleting job
CREATE PROCEDURE usp_RunCmdJobSoon (
@.JobName as varchar(50),
@.CmdText as varchar(1000),
@.DelaySeconds as int = 30,
@.RunOnIdle as bit = 0,
@.DeleteWhenDone as bit = 0
)
AS
BEGIN TRANSACTION
DECLARE @.JobID BINARY(16)
DECLARE @.ReturnCode INT
SELECT @.ReturnCode = 0
declare @.Date as datetime
declare @.NewDate as int
declare @.NewTime as int
DECLARE @.String char(30)
SET @.String = 'sa'
/*
SELECT @.JobID = job_id
FROM msdb.dbo.sysjobs
WHERE (name = @.JobName)
IF (@.JobID IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF (EXISTS (SELECT *
FROM msdb.dbo.sysjobservers
WHERE (job_id = @.JobID) AND (server_id <> 0)))
BEGIN
-- There is, so abort the script
RAISERROR ('Unable to import job %s since there is already a
multi-server job with this name.', 16, 1,@.JobName) WITH LOG
GOTO QuitWithRollback
END
ELSE
RAISERROR ('Unable to create job %s since it already exists.', 16,
1,@.JobName) WITH LOG
GOTO QuitWithRollback
END
*/
-- Add the job
EXECUTE @.ReturnCode = msdb.dbo.sp_add_job @.job_id = @.JobID OUTPUT ,
@.job_name = @.JobName, @.owner_login_name = @.String, @.description = N'Automated
Job Run', @.category_name = N'ODS Automated', @.enabled = 1,
@.notify_level_email = 0, @.notify_level_page = 0, @.notify_level_netsend = 0,
@.notify_level_eventlog = 3, @.delete_level= @.DeleteWhenDone
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobstep @.job_id = @.JobID, @.step_id =
1, @.step_name = @.JobName, @.command = @.CmdText, @.database_name = N'', @.server
= N'', @.database_user_name = N'', @.subsystem = N'CmdExec',
@.cmdexec_success_code = 0, @.flags = 0, @.retry_attempts = 2, @.retry_interval =
5, @.output_file_name = N'', @.on_success_step_id = 0, @.on_success_action = 1,
@.on_fail_step_id = 0, @.on_fail_action = 2
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @.ReturnCode = msdb.dbo.sp_update_job @.job_id = @.JobID,
@.start_step_id = 1
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
--in case of restart
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name
= N'Run on start', @.enabled = 1, @.freq_type = 64
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
--in case of Idle condition
IF (@.RunOnIdle = 1)
BEGIN
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name
= N'Run on Idle', @.enabled = 1, @.freq_type = 128
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
END
-- Delay start
SET @.String = 'Run job soon'
set @.Date = dateadd(ss,@.DelaySeconds,Getdate())
set @.NewDate = cast(convert(varchar(8), @.Date, 112) as int)
set @.NewTime = cast(replace(convert(varchar(8), @.Date, 108), ':', '') as
int)
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobschedule @.job_id = @.JobID, @.name
= @.String, @.enabled = 1, @.freq_type = 1, @.active_start_date = @.NewDate,
@.active_start_time = @.NewTime
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @.ReturnCode = msdb.dbo.sp_add_jobserver @.job_id = @.JobID,
@.server_name = N'(local)'
IF (@.@.ERROR <> 0 OR @.ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
RAISERROR ('Error %s while creating JobName= %s ', 16, 1,@.@.Error,
@.JobName) WITH LOG
IF (@.@.TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
Hi,
Thanks for your post and I will take ownership of this thread now and help
you with this issue.
From your descriptions, I understood that your stored procedures goes
smoothly in one machine but it will be easily hangs in another machine.
Have I understood you? Correct me if I was wrong.
Generally, performance issues can be caused by various factors, and it is
difficult to locate the root cause in a newsgroup thread. If the issue
still exists after you have used the troubleshooting steps above, to
efficiently troubleshoot a performance issue, we recommend that you contact
Microsoft Product Support Services and open a support incident and work
with a dedicated Support Professional. Please be advised that contacting
phone support will be a charged call. To obtain the phone numbers for
specific technology request please take a look at the web site listed below.
http://support.microsoft.com/default...S;PHONENUMBERS
If you'd still like to continue working via the newsgroup, I want to set
your expectations that the issue might take a long time to narrow down.
During the course of troubleshooting, we may redirect you to PSS if
required.
For now, I would love to collect more information about your issue
1. Any Error Message given by SQL Server 2000?
2. Collect the information from sp_blocker_pss80 as the following KB
insturcted
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/?id=271509
Moreover, here are some documents for your reference to find what is
blocked
Resolving blocking problems that are caused by lock escalation in SQL Server
http://support.microsoft.com/?id=323630
TechNet Support WebCast:Performance troubleshooting and analysis in
Microsoft SQL Server 2000
http://support.microsoft.com/?id=838622
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Mingqing,
I already have a blocker output, but it's pretty straight forward. The ONLY
thing happening on the system is the creation of jobs through this sproc.
The sproc is blocking itself from another instance doing the exact same thing
at the same time. That's why I included the sproc in my first post, it's
blocking itself.
How can I get more blocker output to you? Maybe I can email them to you?
blocker output excerpt:
1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 8 No Waittypes: 2004-09-30 15:39:06.890 0
AAG0J6DW06
8 No Waittypes: 2004-09-30 15:39:21.907 0 AAG0J6DW06
8.2 Start time: 2004-09-30 15:39:36.920 0
SYSPROCESSES AAG0J6DW06 134218488
spid status blocked open_tran waitresource
waittype waittime cmd lastwaittype cpu
physical_io memusage last_batch
login_time net_address net_library dbid ecid kpid
hostname
hostprocess loginame
program_name
nt_domain
nt_username
uid sid
sql_handle
stmt_start stmt_end
-- -- -- --
-----------
-
-- -- -- --
-- -- -- --
-- -- -- -- -- --
------
------
------
------
------
--------
--- -- --
51 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 4547 CONDITIONAL LCK_M_S
0 0 14 2004-09-30 15:39:32.360
2004-09-30 15:39:31.610 000E7FB55C16 Named Pipes 4 0 1008
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
61 sleeping 64 1 KEY: 4:2089058478:1
(bf013f652931)
0x0003 5281 CONDITIONAL LCK_M_S
0 0 18 2004-09-30 15:39:31.623
2004-09-30 15:39:31.623 000E7FB55C16 Named Pipes 4 0 5700
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
64 sleeping 61 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 5406 CONDITIONAL LCK_M_S
0 0 17 2004-09-30 15:39:31.623
2004-09-30 15:39:31.623 000E7FB55C16 Named Pipes 4 0 4136
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
65 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 4672 CONDITIONAL LCK_M_S
0 0 0 2004-09-30 15:39:32.360
2004-09-30 15:39:31.623 000E7FB55C16 Named Pipes 4 0 5616
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
66 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 5390 CONDITIONAL LCK_M_S
0 0 11 2004-09-30 15:39:31.640
2004-09-30 15:39:31.640 000E7FB55C16 Named Pipes 4 0 3308
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
67 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 5390 CONDITIONAL LCK_M_S
0 0 14 2004-09-30 15:39:31.640
2004-09-30 15:39:31.640 000E7FB55C16 Named Pipes 4 0 3284
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
68 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 5390 CONDITIONAL LCK_M_S
0 0 14 2004-09-30 15:39:31.640
2004-09-30 15:39:31.640 000E7FB55C16 Named Pipes 4 0 2064
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
69 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 5390 CONDITIONAL LCK_M_S
0 0 14 2004-09-30 15:39:31.640
2004-09-30 15:39:31.640 000E7FB55C16 Named Pipes 4 0 5780
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
70 sleeping 64 1 KEY: 4:2089058478:1
(cf0177d4204c)
0x0003 5390 CONDITIONAL LCK_M_S
0 0 14 2004-09-30 15:39:31.640
2004-09-30 15:39:31.640 000E7FB55C16 Named Pipes 4 0 1204
AAG0J6DW06
0
AAGIRCENTRAL\SV002SQL
.Net SqlClient Data
Provider
AAGIRCENTRAL
SV002SQL
0
0x010500000000000515000000BD3A7246E3130E198C3B3515 5C050000000000000000000000000000000000000000000000 00000000000000000000000000000000000000000000000000 000000000000000000000000
0x020004009A41FF44000000000100000000000000 6344 6862
ESP 110
SYSPROC FIRST PASS
spid ecid waittype
-- -- --
64 0 0x0003
51 0 0x0003
61 0 0x0003
65 0 0x0003
66 0 0x0003
67 0 0x0003
68 0 0x0003
69 0 0x0003
70 0 0x0003
Blocking via locks at 2004-09-30 15:39:36.920
SPIDs at the head of blocking chains
spid
SYSLOCKINFO
spid ecid dbid ObjId IndId Type Resource Mode
Status TransID TransUOW
-- -- -- -- -- -- -- --
-- -- --
51 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
65 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
70 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
69 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
68 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
67 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
66 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
64 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
61 0 4 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
70 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
69 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
68 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
67 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
66 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
65 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
51 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
64 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
61 0 6 0 0 DB S
GRANT 0 00000000-0000-0000-0000-000000000000
65 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171525 00000000-0000-0000-0000-000000000000
69 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171463 00000000-0000-0000-0000-000000000000
70 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171486 00000000-0000-0000-0000-000000000000
66 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171394 00000000-0000-0000-0000-000000000000
51 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171532 00000000-0000-0000-0000-000000000000
67 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171417 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 1 PAG 1:484 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
68 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171440 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 1 PAG 1:484 IS
GRANT 37171280 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 1 KEY (bf013f652931) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 1 KEY (bf013f652931) S
WAIT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2137058649 1 PAG 1:4701 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 1 PAG 1:4886 IS
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 1 PAG 1:4886 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 5 KEY (d001dca5d2ee) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 4 KEY (2302a3aeaeb3) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 4 PAG 1:1738 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 4 PAG 1:1738 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 3 PAG 1:1736 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 3 PAG 1:1736 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 5 PAG 1:1740 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 5 PAG 1:1740 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
65 0 4 2089058478 0 TAB IS
GRANT 37171525 00000000-0000-0000-0000-000000000000
69 0 4 2089058478 0 TAB IS
GRANT 37171463 00000000-0000-0000-0000-000000000000
70 0 4 2089058478 0 TAB IS
GRANT 37171486 00000000-0000-0000-0000-000000000000
66 0 4 2089058478 0 TAB IS
GRANT 37171394 00000000-0000-0000-0000-000000000000
51 0 4 2089058478 0 TAB IS
GRANT 37171532 00000000-0000-0000-0000-000000000000
67 0 4 2089058478 0 TAB IS
GRANT 37171417 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 0 TAB IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
68 0 4 2089058478 0 TAB IS
GRANT 37171440 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 0 TAB IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 3 KEY (850364070d60) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 2 KEY (a2045bef58ed) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2137058649 1 KEY (d001b54b3a2e) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 4 KEY (3302eb1fa7ce) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 5 KEY (c0019414db93) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2137058649 2 KEY (a90444d60925) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 2 KEY (a904a60ed3b4) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 3 KEY (95032cb6041d) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
61 0 4 2137058649 0 TAB IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2137058649 0 TAB IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
64 0 4 2137058649 2 KEY (a204cbc41ffd) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2137058649 2 PAG 1:11483 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 2 PAG 1:28064 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 2 PAG 1:28064 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
64 0 4 2137058649 1 KEY (c0018819704b) X
GRANT 37171280 00000000-0000-0000-0000-000000000000
61 0 4 2089058478 1 KEY (cf0177d4204c) X
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171280 00000000-0000-0000-0000-000000000000
66 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171394 00000000-0000-0000-0000-000000000000
67 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171417 00000000-0000-0000-0000-000000000000
68 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171440 00000000-0000-0000-0000-000000000000
69 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171463 00000000-0000-0000-0000-000000000000
70 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171486 00000000-0000-0000-0000-000000000000
65 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171525 00000000-0000-0000-0000-000000000000
51 0 4 2089058478 1 KEY (cf0177d4204c) S
WAIT 37171532 00000000-0000-0000-0000-000000000000
61 0 4 2137058649 1 PAG 1:24427 IX
GRANT 37171278 00000000-0000-0000-0000-000000000000
64 0 4 2137058649 2 PAG 1:44674 IX
GRANT 37171280 00000000-0000-0000-0000-000000000000
ESL 0
|||Hi,
Thanks for your prompt updates and information!
Yes, it will be highly appreciated if you could provide me the information
by means of email. Here are some steps to collect the information I want
this time
1. Create a script file named 'queryprocess.sql' with the following query
to run commands in a loop.
WHILE 1=1
BEGIN
Select * from master.dbo.sysprocesses
WAITFOR DELAY '00:00:05'
END
GO
2. Run the script file created in step d from either Isql.exe or the
Osql.exe query tool in a Windows command prompt on the computer that is
running SQL Server. For example,
osql -E -Sserver_name -iqueryprocess.sql -oqueryprocess.out -w2000
3. Send the information to us for reviewing and my email is
v-mingqc@.online.microsoft.com (remove 'online' as it is only for SPAM)
In the meanwhile, from syslockinfo, I found many sp are waiting for an
object 2089058478. Is this the stored procedure itself? Please use the
following command to check the object name: object_name( 2089058478)
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi,
I am just checking on your progress regarding the information that was sent
you! Have you tried the steps I provided to you? I wonder how the testing
is going. If you encounter any difficulty, please do not hesitate to let me
know. Please post here and let me know the status of your issue. Without
your further information, it's very hard for me to continue with the
troubleshooting.
Looking forward to hearing from you soon
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!