how to insert the range of ip address in SQL using stored procedures

hi

i need to insert the list of ipaddress using stored procedures.

the user will give the from and to range of IP ADDRESS.i've to insert all the possible ip address between those values.

how to do this..

LEts say the range is from 172.25.50.1 to 172.25.50.30 you can write a WHILE loop to loop through and insert records. There are other ways too but at this time of the night this is the best I can think of without putting any pressure on my brain...

|||

hi dinakar,

thanks for the help @. the late night,

i even wrote a while loop in stored procedure..

what i did is i converted the ips to bigint and made the loop.

now what i need is to place the dots in the corresponding positions from where it was taken out..

can u give any suggestions for this..

|||

i solved it in this way

-- LOOP START

Declare @.testvarchar(40)declare @.s1int,@.s2int,@.s3int,@.s4intset @.s1 =len(145)set @.s2 =len(145)set @.s3 =len(45)set @.s4 =len(45)set @.test ='1451592633'set @.test=substring(@.test,0,@.s1+1)+'.'+substring(@.test,@.s1+1,@.s2)+'.'+substring(@.test,@.s2+@.s1+1,@.s3)+'.'+substring(@.test,@.s1+@.s2+@.s3+1,@.s4)print @.test

-- END OF LOOPsql

how to insert several insert commands, triggers?

Hello, what i want is simple.

This is a simple forum, it has several topics (that the users can create), when a user create a topic, its stored in forum_topics. The user can then view the topic and post a response that is store in forum_answer, the user can also add this to his favorite list, forum_favorites is simple, contains a TopicID that refers to the topic, a username of the user that has the topic on his favorite list and a auto increment id to be able to delete specified topic favorites.

Now my question is: when a user posts a answer to Topic X, i want a predefined message to be sent to post_inbox for all the users that has Topic X in their favorite list.

How can i get MS SQL 2005 to get all the users from Topic X and then loop thru them and insert a new post into post_inbox?

Patrick

Hello,

to insert multiple rows you can use select instead of values in the insert statement. So there is no need for a loop. It would be something like this:

INSERT INTO post_inbox (TopicID, UserName, Message)SELECT ( TopicId,--Is always the ID of Topic X, because of the WHERE UserName,--Is every user that is subscribed to Topic X'Hey, Topix X has a new answer, check it out!'--Maybe a message in their inbox?)FROM forum_favoritesWHERE TopicId = 123--ID op Topic X

So the select will select all users subscribed to Topic X, and you use that as the values for the insert.

Good luck!

|||

Hi Pafo,

From your description, I understand that you need to give each one, whose favorite list has Topic X, a message when any one replies to Topic X.

This can be done in many ways.

1. In the page code, when some one posts a reply, we can invoke some method to inform the observers. This will be more flexible in the logic layer.
2. If you need to do this in the database, we can use Triggers or we can extend the reply stored procedure to do this directly.

In my opinion, option 1 will be better, since users can customize whether to receive this message on their own.

HTH. If anything is unclear, please feel free to mark it as Not Answered and post your reply. Thanks!

How to insert date in database table in sql server

I have one form with fields name,date of birth , age.
I would like to get the input from user and store it to sql database
table.
After I stored , while viewing the table records, the dateof birth
field is 01.01.1900, how to store the exact date in database.
what datatype i have to store in database table.
Thanx & Regards,
SSGSSG wrote:
> I have one form with fields name,date of birth , age.
> I would like to get the input from user and store it to sql database
> table.
> After I stored , while viewing the table records, the dateof birth
> field is 01.01.1900, how to store the exact date in database.
> what datatype i have to store in database table.
> Thanx & Regards,
> SSG
Use datetime datatype and you can get plenty of information regarding
it in BOL.
Regards
Amish Shah

How to insert date in database table in sql server

I have one form with fields name,date of birth , age.
I would like to get the input from user and store it to sql database
table.
After I stored , while viewing the table records, the dateof birth
field is 01.01.1900, how to store the exact date in database.
what datatype i have to store in database table.
Thanx & Regards,
SSGSSG wrote:

> I have one form with fields name,date of birth , age.
> I would like to get the input from user and store it to sql database
> table.
> After I stored , while viewing the table records, the dateof birth
> field is 01.01.1900, how to store the exact date in database.
> what datatype i have to store in database table.
> Thanx & Regards,
> SSG
Use datetime datatype and you can get plenty of information regarding
it in BOL.
Regards
Amish Shah

How to insert all the values from a listbox into the database?

I want that the user can chose several options from one 'listbox', and to do this, I have created two 'listbox', in the first one there are the options to select, and the second one is empty. Then, in order to select the options I want the user have select one or more options from the first 'listbox' and then click in a link to pass the options selected to the second 'listbox'. Thus, the valid options selected will be the text and values in the second 'listbox'. I have seen this system in some websites, and I think it is very clear.

Well, my question is, Is it possible to insert into the database all the values from a 'listbox' control? In my case from the second 'listbox' with all the values passed from the first one? If yes, in my database table I have to create one column (field) for every possible selected option?

Thank you,
Cesarwhat you should do is have a relational table that only holds, lets say, an ID for the user and a field for the selections that have been made.|||I don' t understand what you mean. If I have a table with two fields, 'User_id' and 'product_quality_num', the 'product_quality_num' field has a foreign key related with the 'Product_Q' table. So, the user only can enter the product_quality_num 1, 2, 3, 4, 5 or 6. Then, what happens if the user have selected product_quality_num 1, 3 and 6? The 'product_quality_num' field only accepts one number.. not three (1, 3 and 6)|||You have to get those values back to the server. There have been posts on how to pass back contents of a listbox that were added client sde, but one way you can do it is by adding the IDs to a hidden form field separated by a delimiter like a pipe while you're adding them to the dropdown. The dropdown really is just a visual. The hidden form field is what you really need.

Your hidden field winds up like 1|2|4| when you post back. On the server, grab that value, parse it into an array based on the pipe. Loop through the array calling an insert for each value. Or, you could pass back the string and parse it in a SQL function that does the same thing, but returns a table as its output.

How to import MySQL data into SQL2005 using wizard?

Hello everybody,

I'm a new SQL2005 user.
I'm

trying to import data from MySQL version 5x into my SQL2005 by wizard.

I created a DNS file and tested successfully using MySQL Connector/ODBC

v5.
Everthing seems fine but at the last step selecting data

source. The SQL2005 wizard forced me to choose using SQL command option

instead of selecting tables/views from a list. Anyone can tell me why?

My collegues faced the same case as they help me to find the reason.

I'm sure that there are a few source objetcts in MySQL source.

Any help will be appreciated!!!

khanhmy

Hi,

Unfortunately this is the limitation of the Import/Export Wizard in SQL 2005. It can't get table metadata for ADO.NET and ODBC sources, so the only way to use it with ADO.NET or ODBC sources is to type the SQL command.

We are looking at fixing this for next version.

Regards,

Michael.

how to implement user function/query

Hi,

In my database I have a table which is some kind of history log. The report is designed to group these entries by date. Showing the history data for the entries works fine, but for any history entry I need to show its previous entry, too (that means the table row with maximum date being smaller than the current selected). But what function/query do I have to implement?

Help would be highly appreciated.

Thank you,
gbruse
Select statement with between, and|||But how can I use the result of a select statement in a function? What I wanted to do is getting all dates and getting the latest which is below the current selected (of the group).

gbr|||send me ur code and table structure|||+------+-------+--+--+---+-------+
| Field | Type | Null | Key | Default | Extra |
+------+-------+--+--+---+-------+
| id | int(10) unsigned | | PRI | NULL | auto_increment |
| id_cust | int(10) unsigned | | | 0 | |
| add_data | varchar(20) | YES | | NULL | |
+------+-------+--+--+---+-------+

Imagine you have a table with the dates a user bought something. What I want to achive is showing all items groupes by dates and the date before the selected date.

The report shall look like:

date customer goods
old 06/02/05 Mr. Brown ...
now 06/03/05 Mr. Brown ...
-------------------
old 07/02/05 Mrs. Green ...
new 07/24/05 Mrs. Green ...

Thanks for your replay,

gbr.|||It would help if somebody could tell how to write a query (select xyz from tab where ...) and how to store the return value into a variable. Giving an example to familiarize with the crystal syntax would be enough for the beginning.

gbr|||Is there anyone who can tell how to store an entire column of a table in a variable?

When I use this function

local datetimevar array dates := {tab.date};
count(dates);

Only 1 is displayed though there are more than 1 columns in this table...

How to implement search function

I have a table search the record. The user can search by name, id, status, address and other information.

I want to make the search function more robust. For example, when the database has a record with name = "Michael Jackson", either typing any subset of the name will show this record.

I would like to know if there is any easy way to implement this with VS2005 and SQL2005 Express.

Thank you

Take a look at the LIKE command in SQL, some examples :http://www.techonthenet.com/sql/like.php ,Hope this helps or gives you some steer.

|||This thread contains some good tips on the SQL searching subject:http://forums.asp.net/thread/1529167.aspx

How to impersonate user for data on another server?

Hello all,

I have a quick question. The sql reporting server is on server A, and the actual data for the report is on a sql server 2005 called server B. When I am actually developing my report and click preview, everything works perfectly using windows authentication. However, whenever I publish and try to view the report, i get this error:

An error has occurred during report processing.

Cannot create a connection to data source 'serverB'.

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.How do I change the login for this datasource while still using windows authentication and not mixed mode?

ThanksGo to the data source properties in the Reporting Services Manager and you can put in a Windows or SQL Server user name and password for the data source.

How to impersonate the user and send those credentials?

I want to create only one user on the domain, and my application should send that user and password to the reportviewer, in that way I wont have to give permissions to every user individually .

Does anyone have the lines of code to do this?

Check the WindowsIdentity class (particularly WindowsIdentity.Impersonate): http://msdn2.microsoft.com/en-us/library/system.security.principal.windowsidentity(VS.80).aspx

-- Robert

How to impersonate the user and send those credentials?

I want to create only one user on the domain, and my application should send that user and password to the reportviewer, in that way I wont have to give permissions to every user individually .

Does anyone have the lines of code to do this?

Try one of these articles:

http://www.odetocode.com/Articles/215.aspx

http://www.odetocode.com/Articles/216.aspx|||Hey I STRUGGLED with this for some time and from grabbing other snippets of code and doing some of my own troubleshooting this is what I came up with and it works for me. Please note that in the second line of code you will need the IMPORTS of YOUR web service. I am using Webhost4life as my provider and this code works for me so I hope it helps someone out there to get started. If anyone has enhancements please let me know.

Imports Microsoft.VisualBasicImports com.mysite4now.sql332reportingImports Microsoft.Reporting.WebFormsImports System.Security.PrincipalPartialClass reportviewerInherits System.Web.UI.PagePublic Class ReportViewerCredentialsImplements IReportServerCredentialsPrivate _userNameAs String Private _passwordAs String Private _domainAs String Public Sub New(ByVal userNameAs String,ByVal passwordAs String,ByVal domainAs String) _userName = userName _password = password _domain = domainEnd Sub Public ReadOnly Property ImpersonationUser()As System.Security.Principal.WindowsIdentityImplements Microsoft.Reporting.WebForms.IReportServerCredentials.ImpersonationUserGet Return Nothing End Get End Property Public ReadOnly Property NetworkCredentials()As System.Net.ICredentialsImplements Microsoft.Reporting.WebForms.IReportServerCredentials.NetworkCredentialsGet Return New Net.NetworkCredential(_userName, _password, _domain)End Get End Property Public Function GetFormsCredentials(ByRef authCookieAs System.Net.Cookie,ByRef userNameAs String,ByRef passwordAs String,ByRef authorityAs String)As Boolean Implements Microsoft.Reporting.WebForms.IReportServerCredentials.GetFormsCredentials userName = _userName password = _password authority = _domainReturn Nothing End Function End Class Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.LoadIf Not IsPostBackThen Dim rptPathAs String rptPath = Request("Path")Dim rsAs New ReportingService ReportViewer1.ServerReport.ReportServerCredentials =New ReportViewerCredentials("username","password","") ReportViewer1.ServerReport.ReportServerUrl =New Uri("yourservername") ReportViewer1.AsyncRendering =False ReportViewer1.ShowDocumentMapButton =True ReportViewer1.DocumentMapCollapsed =False ReportViewer1.ServerReport.ReportPath = rptPathEnd If End SubEnd Class

|||

Hey, can i ask you for this line:
ReportViewer1.ServerReport.ReportServerCredentials = New ReportViewerCredentials("username", "password", "")

How you get the the password variable? if it from your database or from active directory?

Please advice, thank you.

|||

you can put it in, web.config or from a database. don't know abt active directory.

I have worked with this, and we put it in web.config.

i haven't tried for active directory.

How to impersonate known user to AS 2000

I am developing an application in C# 2.0 that will access a AS 2000 cube. I want the application to access the cube with a known account. I do not want to use XMLA.

My ConnectionString looks like this:

Data Source=MyServer; Catalog=GL; User ID=ReportUser; Password=mypass

My code looks like this:

AdomdConnection Connection = new AdomdConnection(ConnectionString);
Connection.Open();

ReportUser is a local account on the server. The cube has a single role where ReportUser is a member.

When trying to open the connection I get an exception that the connection was actively refused by the server.

Help?

Thanks

Sorry - you can't do that unless you set up http authentication and use basic auth for your virtual directory. (please use https)

And that takes Enterprise edition in 2000 (but standard in 2005!).

How to identify User Objects within sysobjects ?

I am querying master..sysobjects table for user defined objects. I have
been relying on category field to show if a given object is a user defined
object or system object. My understanding was that a category value of
zero (0) is a user object. Apparently this is not always true. I have
seen value 16 for some. Where this is documented if any ? BOL does not
talk about the values. Or may be there is a better way to find this out
other than Category field.
I appreciate in advance for any suggestions.
MacYou can use the OBJECTPROPERTY function for this:
SELECT <column list>
FROM sysobjects
WHERE OBJECTPROPERTY(id, 'IsMSShipped') = 0
Jacco Schalkwijk
SQL Server MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:conj65$1o9g$1@.si05.rsvl.unisys.com...
>I am querying master..sysobjects table for user defined objects. I have
> been relying on category field to show if a given object is a user defined
> object or system object. My understanding was that a category value of
> zero (0) is a user object. Apparently this is not always true. I have
> seen value 16 for some. Where this is documented if any ? BOL does not
> talk about the values. Or may be there is a better way to find this out
> other than Category field.
> I appreciate in advance for any suggestions.
> Mac
>|||"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:conj65$1o9g$1@.si05.rsvl.unisys.com...
>I am querying master..sysobjects table for user defined objects. I have
> been relying on category field to show if a given object is a user defined
> object or system object. My understanding was that a category value of
> zero (0) is a user object. Apparently this is not always true. I have
> seen value 16 for some. Where this is documented if any ? BOL does not
> talk about the values. Or may be there is a better way to find this out
> other than Category field.
> I appreciate in advance for any suggestions.
> Mac
>
Are you looking for specific objects?
SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for procs
etc.
I'm not sure if that will help you out or not.
Rick Sawtell
MCT, MCSD, MCDBA|||Excellent! Thanks Jacco for the reply. That must be it. I should check to
see if this is supported in earlier releases as well.
Mac
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23wkkjWJ2EHA.2676@.TK2MSFTNGP12.phx.gbl...
> You can use the OBJECTPROPERTY function for this:
> SELECT <column list>
> FROM sysobjects
> WHERE OBJECTPROPERTY(id, 'IsMSShipped') = 0
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:conj65$1o9g$1@.si05.rsvl.unisys.com...
defined[vbcol=seagreen]
out[vbcol=seagreen]
>|||Thanks Rick. I was looking for user-stored procedures in the master db. I
can use the TYPE to filter the object types but to know whether it is system
defined or user defined object, I received an answer that ObjectProperty
function can be used to find that out.
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:uCHA2XJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:conj65$1o9g$1@.si05.rsvl.unisys.com...
defined[vbcol=seagreen]
out[vbcol=seagreen]
> Are you looking for specific objects?
> SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for procs
> etc.
> I'm not sure if that will help you out or not.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||> I was looking for user-stored procedures in the master db.
Safer to use INFORMATION_SCHEMA in the current DB than to use sysobjects in
the master db (sysobjects is going away eventually, and it is not
recommended to use these tables directly if you can avoid it).
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE'
AND OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+
'.'+ROUTINE_NAME),
'IsMSShipped') = 0
http://www.aspfaq.com/
(Reverse address to reply.)
I
> can use the TYPE to filter the object types but to know whether it is
system
> defined or user defined object, I received an answer that ObjectProperty
> function can be used to find that out.
> "Rick Sawtell" <quickening@.msn.com> wrote in message
> news:uCHA2XJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...
have[vbcol=seagreen]
> defined
of[vbcol=seagreen]
have[vbcol=seagreen]
not[vbcol=seagreen]
> out
>|||Thanks. I should look into that.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uVER44J2EHA.2316@.TK2MSFTNGP15.phx.gbl...
> Safer to use INFORMATION_SCHEMA in the current DB than to use sysobjects
in
> the master db (sysobjects is going away eventually, and it is not
> recommended to use these tables directly if you can avoid it).
> SELECT ROUTINE_NAME
> FROM INFORMATION_SCHEMA.ROUTINES
> WHERE ROUTINE_TYPE='PROCEDURE'
> AND OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+
'.'+ROUTINE_NAME),
> 'IsMSShipped') = 0
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
> I
> system
ObjectProperty[vbcol=seagreen]
> have
> of
> have
> not
this[vbcol=seagreen]
procs[vbcol=seagreen]
>

How to identify User Objects within sysobjects ?

I am querying master..sysobjects table for user defined objects. I have
been relying on category field to show if a given object is a user defined
object or system object. My understanding was that a category value of
zero (0) is a user object. Apparently this is not always true. I have
seen value 16 for some. Where this is documented if any ? BOL does not
talk about the values. Or may be there is a better way to find this out
other than Category field.
I appreciate in advance for any suggestions.
MacYou can use the OBJECTPROPERTY function for this:
SELECT <column list>
FROM sysobjects
WHERE OBJECTPROPERTY(id, 'IsMSShipped') = 0
--
Jacco Schalkwijk
SQL Server MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:conj65$1o9g$1@.si05.rsvl.unisys.com...
>I am querying master..sysobjects table for user defined objects. I have
> been relying on category field to show if a given object is a user defined
> object or system object. My understanding was that a category value of
> zero (0) is a user object. Apparently this is not always true. I have
> seen value 16 for some. Where this is documented if any ? BOL does not
> talk about the values. Or may be there is a better way to find this out
> other than Category field.
> I appreciate in advance for any suggestions.
> Mac
>|||"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:conj65$1o9g$1@.si05.rsvl.unisys.com...
>I am querying master..sysobjects table for user defined objects. I have
> been relying on category field to show if a given object is a user defined
> object or system object. My understanding was that a category value of
> zero (0) is a user object. Apparently this is not always true. I have
> seen value 16 for some. Where this is documented if any ? BOL does not
> talk about the values. Or may be there is a better way to find this out
> other than Category field.
> I appreciate in advance for any suggestions.
> Mac
>
Are you looking for specific objects?
SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for procs
etc.
I'm not sure if that will help you out or not.
Rick Sawtell
MCT, MCSD, MCDBA|||Excellent! Thanks Jacco for the reply. That must be it. I should check to
see if this is supported in earlier releases as well.
Mac
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:%23wkkjWJ2EHA.2676@.TK2MSFTNGP12.phx.gbl...
> You can use the OBJECTPROPERTY function for this:
> SELECT <column list>
> FROM sysobjects
> WHERE OBJECTPROPERTY(id, 'IsMSShipped') = 0
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:conj65$1o9g$1@.si05.rsvl.unisys.com...
> >I am querying master..sysobjects table for user defined objects. I have
> > been relying on category field to show if a given object is a user
defined
> > object or system object. My understanding was that a category value of
> > zero (0) is a user object. Apparently this is not always true. I have
> > seen value 16 for some. Where this is documented if any ? BOL does not
> > talk about the values. Or may be there is a better way to find this
out
> > other than Category field.
> >
> > I appreciate in advance for any suggestions.
> > Mac
> >
> >
>|||Thanks Rick. I was looking for user-stored procedures in the master db. I
can use the TYPE to filter the object types but to know whether it is system
defined or user defined object, I received an answer that ObjectProperty
function can be used to find that out.
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:uCHA2XJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:conj65$1o9g$1@.si05.rsvl.unisys.com...
> >I am querying master..sysobjects table for user defined objects. I have
> > been relying on category field to show if a given object is a user
defined
> > object or system object. My understanding was that a category value of
> > zero (0) is a user object. Apparently this is not always true. I have
> > seen value 16 for some. Where this is documented if any ? BOL does not
> > talk about the values. Or may be there is a better way to find this
out
> > other than Category field.
> >
> > I appreciate in advance for any suggestions.
> > Mac
> >
> >
> Are you looking for specific objects?
> SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for procs
> etc.
> I'm not sure if that will help you out or not.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>|||> I was looking for user-stored procedures in the master db.
Safer to use INFORMATION_SCHEMA in the current DB than to use sysobjects in
the master db (sysobjects is going away eventually, and it is not
recommended to use these tables directly if you can avoid it).
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE'
AND OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+'.'+ROUTINE_NAME),
'IsMSShipped') = 0
--
http://www.aspfaq.com/
(Reverse address to reply.)
I
> can use the TYPE to filter the object types but to know whether it is
system
> defined or user defined object, I received an answer that ObjectProperty
> function can be used to find that out.
> "Rick Sawtell" <quickening@.msn.com> wrote in message
> news:uCHA2XJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...
> >
> > "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> > news:conj65$1o9g$1@.si05.rsvl.unisys.com...
> > >I am querying master..sysobjects table for user defined objects. I
have
> > > been relying on category field to show if a given object is a user
> defined
> > > object or system object. My understanding was that a category value
of
> > > zero (0) is a user object. Apparently this is not always true. I
have
> > > seen value 16 for some. Where this is documented if any ? BOL does
not
> > > talk about the values. Or may be there is a better way to find this
> out
> > > other than Category field.
> > >
> > > I appreciate in advance for any suggestions.
> > > Mac
> > >
> > >
> >
> > Are you looking for specific objects?
> >
> > SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for procs
> > etc.
> >
> > I'm not sure if that will help you out or not.
> >
> > Rick Sawtell
> > MCT, MCSD, MCDBA
> >
> >
> >
>|||Thanks. I should look into that.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uVER44J2EHA.2316@.TK2MSFTNGP15.phx.gbl...
> > I was looking for user-stored procedures in the master db.
> Safer to use INFORMATION_SCHEMA in the current DB than to use sysobjects
in
> the master db (sysobjects is going away eventually, and it is not
> recommended to use these tables directly if you can avoid it).
> SELECT ROUTINE_NAME
> FROM INFORMATION_SCHEMA.ROUTINES
> WHERE ROUTINE_TYPE='PROCEDURE'
> AND OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+'.'+ROUTINE_NAME),
> 'IsMSShipped') = 0
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
> I
> > can use the TYPE to filter the object types but to know whether it is
> system
> > defined or user defined object, I received an answer that
ObjectProperty
> > function can be used to find that out.
> >
> > "Rick Sawtell" <quickening@.msn.com> wrote in message
> > news:uCHA2XJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...
> > >
> > > "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> > > news:conj65$1o9g$1@.si05.rsvl.unisys.com...
> > > >I am querying master..sysobjects table for user defined objects. I
> have
> > > > been relying on category field to show if a given object is a user
> > defined
> > > > object or system object. My understanding was that a category value
> of
> > > > zero (0) is a user object. Apparently this is not always true. I
> have
> > > > seen value 16 for some. Where this is documented if any ? BOL does
> not
> > > > talk about the values. Or may be there is a better way to find
this
> > out
> > > > other than Category field.
> > > >
> > > > I appreciate in advance for any suggestions.
> > > > Mac
> > > >
> > > >
> > >
> > > Are you looking for specific objects?
> > >
> > > SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for
procs
> > > etc.
> > >
> > > I'm not sure if that will help you out or not.
> > >
> > > Rick Sawtell
> > > MCT, MCSD, MCDBA
> > >
> > >
> > >
> >
> >
>

How to identify User Objects within sysobjects ?

I am querying master..sysobjects table for user defined objects. I have
been relying on category field to show if a given object is a user defined
object or system object. My understanding was that a category value of
zero (0) is a user object. Apparently this is not always true. I have
seen value 16 for some. Where this is documented if any ? BOL does not
talk about the values. Or may be there is a better way to find this out
other than Category field.
I appreciate in advance for any suggestions.
Mac
You can use the OBJECTPROPERTY function for this:
SELECT <column list>
FROM sysobjects
WHERE OBJECTPROPERTY(id, 'IsMSShipped') = 0
Jacco Schalkwijk
SQL Server MVP
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:conj65$1o9g$1@.si05.rsvl.unisys.com...
>I am querying master..sysobjects table for user defined objects. I have
> been relying on category field to show if a given object is a user defined
> object or system object. My understanding was that a category value of
> zero (0) is a user object. Apparently this is not always true. I have
> seen value 16 for some. Where this is documented if any ? BOL does not
> talk about the values. Or may be there is a better way to find this out
> other than Category field.
> I appreciate in advance for any suggestions.
> Mac
>
|||"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:conj65$1o9g$1@.si05.rsvl.unisys.com...
>I am querying master..sysobjects table for user defined objects. I have
> been relying on category field to show if a given object is a user defined
> object or system object. My understanding was that a category value of
> zero (0) is a user object. Apparently this is not always true. I have
> seen value 16 for some. Where this is documented if any ? BOL does not
> talk about the values. Or may be there is a better way to find this out
> other than Category field.
> I appreciate in advance for any suggestions.
> Mac
>
Are you looking for specific objects?
SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for procs
etc.
I'm not sure if that will help you out or not.
Rick Sawtell
MCT, MCSD, MCDBA
|||Excellent! Thanks Jacco for the reply. That must be it. I should check to
see if this is supported in earlier releases as well.
Mac
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:%23wkkjWJ2EHA.2676@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> You can use the OBJECTPROPERTY function for this:
> SELECT <column list>
> FROM sysobjects
> WHERE OBJECTPROPERTY(id, 'IsMSShipped') = 0
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:conj65$1o9g$1@.si05.rsvl.unisys.com...
defined[vbcol=seagreen]
out
>
|||Thanks Rick. I was looking for user-stored procedures in the master db. I
can use the TYPE to filter the object types but to know whether it is system
defined or user defined object, I received an answer that ObjectProperty
function can be used to find that out.
"Rick Sawtell" <quickening@.msn.com> wrote in message
news:uCHA2XJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:conj65$1o9g$1@.si05.rsvl.unisys.com...
defined[vbcol=seagreen]
out
> Are you looking for specific objects?
> SELECT Name from sysobjects where TYPE = 'U' for tables, 'P' for procs
> etc.
> I'm not sure if that will help you out or not.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
|||> I was looking for user-stored procedures in the master db.
Safer to use INFORMATION_SCHEMA in the current DB than to use sysobjects in
the master db (sysobjects is going away eventually, and it is not
recommended to use these tables directly if you can avoid it).
SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE'
AND OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+'.'+ROUTIN E_NAME),
'IsMSShipped') = 0
http://www.aspfaq.com/
(Reverse address to reply.)
I
> can use the TYPE to filter the object types but to know whether it is
system[vbcol=seagreen]
> defined or user defined object, I received an answer that ObjectProperty
> function can be used to find that out.
> "Rick Sawtell" <quickening@.msn.com> wrote in message
> news:uCHA2XJ2EHA.2624@.TK2MSFTNGP11.phx.gbl...
have[vbcol=seagreen]
> defined
of[vbcol=seagreen]
have[vbcol=seagreen]
not
> out
>
|||Thanks. I should look into that.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uVER44J2EHA.2316@.TK2MSFTNGP15.phx.gbl...
> Safer to use INFORMATION_SCHEMA in the current DB than to use sysobjects
in[vbcol=seagreen]
> the master db (sysobjects is going away eventually, and it is not
> recommended to use these tables directly if you can avoid it).
> SELECT ROUTINE_NAME
> FROM INFORMATION_SCHEMA.ROUTINES
> WHERE ROUTINE_TYPE='PROCEDURE'
> AND OBJECTPROPERTY(OBJECT_ID(ROUTINE_SCHEMA+'.'+ROUTIN E_NAME),
> 'IsMSShipped') = 0
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
> I
> system
ObjectProperty[vbcol=seagreen]
> have
> of
> have
> not
this[vbcol=seagreen]
procs
>

How to Identify user defined data types

How can you tell which datatypes in a given database are user defined
(with a query, not by looking in Enterprise Manager)? This is for SQL
Server 2000.SELECT domain_name
FROM information_schema.domains

--
David Portas
SQL Server MVP
--|||Bruce (sandell@.pacbell.net) writes:
> How can you tell which datatypes in a given database are user defined
> (with a query, not by looking in Enterprise Manager)? This is for SQL
> Server 2000.

SELECT * FROM systypes WHERE xusertype > 255

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How to identify Memory Leak caused by bad written stored procedures?

Does anyone has experience in monitoring Sql server memory leak? I am suspicious of some of the user stored procedures causing the memory leak. Can anyone who has such experience explaine how to find the offened stored procedures?

Thank you very much for any kind of suggestions and recommendations!

Unless you're talking about extended stored procedures, then stored procs themselves don't have memory leaks as such (like normal code), however, they may be doing things inefficiently, or you may have missing table indexes.

The best way to start is to use the SQL Profiler tool to monitor stored procedure execution and look at the duration field. If you capture results to a table, you can then select the slowest queries by ordering on the duration column descending. You should also group by the procedure name to look at the procedures executed most often.

The combination of the slowest queries, and those executed most often will give you the best place to start. From there it's a case of looking at the execution plan (in query analyser) of the procs and looking for ways to optimise the tsql or add/modify indexes.

How to identify and kill a query

Is there a way to identify a specific query - such as by begin time or user
account - and then be able to kill the query programaticaly. We have a web
based application the issues SQL queries. If a user formulates a query
poorly it can take quite a long time to run (40M+ records for one table). I
would like a way a user can kill their query.
Thanks,Users can issue sp_who2 to find all the active transactions on the server.
This provides login id information aswell.
DBCC INPUTBUFFER (spid) , will give the description of executing statement .
KILL <spid> will terminate the transaction. But the user need to have
sysadmin or processadmin rights. Look at KILL in bol.
Why don't you trace the long running queries and try to fix it, instead of
giving rights to the user to kill the transactions? Giving rights to the
users to kill transaction can be dangerous sometimes.
HTH
GYK
"Gordon" wrote:
> Is there a way to identify a specific query - such as by begin time or user
> account - and then be able to kill the query programaticaly. We have a web
> based application the issues SQL queries. If a user formulates a query
> poorly it can take quite a long time to run (40M+ records for one table). I
> would like a way a user can kill their query.
> Thanks,|||Thanks, the app is a web server based web app written in Java. The users
have no interface to SQL EM or query manager so this would have to be done by
issuing queries/commands to the SQL via a java command. The users have the
ability to run a select query against the database from a query generation
screen which actually formulates and executes the query for them. I would
like them to be able to kill the running query from the status screen for
their query.
We have optimized the queries as much as they can be - sometimes when you
have 40M+ records it just takes a while. It would be nice if we can kill
them so they don't have to wait 30 minutes if they accidently execute the
query.
Also, interesting in that dbcc inputbuffer(spid) did not return the full
text of the query string - it gets truncated. Therefore, I can't really use
that too well to do a string comparison to validate that it is the actual
correct query to kill.
"GYK" wrote:
> Users can issue sp_who2 to find all the active transactions on the server.
> This provides login id information aswell.
> DBCC INPUTBUFFER (spid) , will give the description of executing statement .
> KILL <spid> will terminate the transaction. But the user need to have
> sysadmin or processadmin rights. Look at KILL in bol.
> Why don't you trace the long running queries and try to fix it, instead of
> giving rights to the user to kill the transactions? Giving rights to the
> users to kill transaction can be dangerous sometimes.
> HTH
> GYK
> "Gordon" wrote:
> > Is there a way to identify a specific query - such as by begin time or user
> > account - and then be able to kill the query programaticaly. We have a web
> > based application the issues SQL queries. If a user formulates a query
> > poorly it can take quite a long time to run (40M+ records for one table). I
> > would like a way a user can kill their query.
> >
> > Thanks,|||I would consider enabling the Query Governer either at the server level or
instruct users on how to use it at the session level.
The governer works on the estimated cost of a query execution plan. If this
cost is too high--higher than the threashold--the user will receive an error
and disallow the execution.
Sincerely,
Anthony Thomas
"Gordon" <Gordon@.discussions.microsoft.com> wrote in message
news:CDFD7782-799F-47F0-8C46-678EB2FA32FA@.microsoft.com...
Thanks, the app is a web server based web app written in Java. The users
have no interface to SQL EM or query manager so this would have to be done
by
issuing queries/commands to the SQL via a java command. The users have the
ability to run a select query against the database from a query generation
screen which actually formulates and executes the query for them. I would
like them to be able to kill the running query from the status screen for
their query.
We have optimized the queries as much as they can be - sometimes when you
have 40M+ records it just takes a while. It would be nice if we can kill
them so they don't have to wait 30 minutes if they accidently execute the
query.
Also, interesting in that dbcc inputbuffer(spid) did not return the full
text of the query string - it gets truncated. Therefore, I can't really use
that too well to do a string comparison to validate that it is the actual
correct query to kill.
"GYK" wrote:
> Users can issue sp_who2 to find all the active transactions on the server.
> This provides login id information aswell.
> DBCC INPUTBUFFER (spid) , will give the description of executing statement
.
> KILL <spid> will terminate the transaction. But the user need to have
> sysadmin or processadmin rights. Look at KILL in bol.
> Why don't you trace the long running queries and try to fix it, instead of
> giving rights to the user to kill the transactions? Giving rights to the
> users to kill transaction can be dangerous sometimes.
> HTH
> GYK
> "Gordon" wrote:
> > Is there a way to identify a specific query - such as by begin time or
user
> > account - and then be able to kill the query programaticaly. We have a
web
> > based application the issues SQL queries. If a user formulates a query
> > poorly it can take quite a long time to run (40M+ records for one
table). I
> > would like a way a user can kill their query.
> >
> > Thanks,

How to identify all parameter values selected

I have the SQL query. If the user is selecting all the vendor Numbers available in the vendor number parameter drop down then, I will not include the vendor Number condition in the where portion of the sql query. For that I want to know whether the user has selected all the values available in the drop down. How to identify this?

Have a Default Value in your DropDown, for instance: SELECT ALL with the value of "" VALUE=""

which is basically null. In your where cause use an ISNULL

For instance:

@.COMPANY_ID INT = NULL

Select ID, [NAME], COMPANY_ID FROM EMPLOYEES WHERE COMPANY_ID = ISNULL(@.COMPANY_ID, COMPANY_ID)

Hope this helps.

How to Identify a User's Permissions to SQL Server Tables via VB 6.0 Application

I'm writing an application using VB 6.0 as the front-end GUI, and the
MSDE version of SQL Server as the back-end (it's a program for a
really small # of users -- less then 3-4).
I'm trying to determine, through the Visual Basic interface, the
permissions of each user that's using the application on his/her
machine.
For example, let's say I'm user "Michael" that's sitting down at my
machine using the app. I've written. The security for logging into
SQL Server will be setup using Windows Security (Trusted Connection)
as opposed to Windows & SQL Server security. When Michael accesses a
particular form in the VB 6.0 GUI, I want to run some code that
automatically checks Michael's permission levels on the underlying
table (actually, a stored procedure supplying the data from the table)
that supplies the data to the form he's looking at and then give him
some feedback on the form as to what type of permissions he has while
he's browsing through the data shown in the form.
For example, Michael opens a particular form, code in the background
is run to identify that this is Michael accessing the form, the code
returns a value that identifies what type of permissions he has on the
data in the form, and a text box on the form informs Michael (for
example) that he only has read-only permissions to the data he is
viewing and cannot edit any of the data.
As another example, user Karen sits down at her computer, logs into
the application, opens the same form that Michael just opened, the
code is run in VB to detect the level of permissions she has on the
data being displayed in the form, and the text box on the form informs
her that she has editing permissions on the data in the underlying
table.
Etc...
If anyone can post an example of the code they use in accomplishing
this task in an application they've written, I'd really appreciate a
point in the right direction or a real-world example that's been
implemented by one of you. I've written several apps. thus far using
MSDE as the back-end, but the previous apps. I've written were for
clients that didn't care about restricting access to the
application... everyone could pretty much use the application as they
desired and do anything they desired to the data.
The current client I'm writing the app. mentioned here for wants to
have security in place to where various users access the application
with various levels of permissions to do stuff (or *not* do stuff) to
the data in the application.
Thanks very much in advance for any assistance / code provided!
Sincerely,
Brad McCollum
bmccoll1@.midsouth.rr.com"Brad H McCollum" <bmccoll1@.midsouth.rr.com> wrote in message
news:52031869.0410131014.25af0f1c@.posting.google.com...
> I'm writing an application using VB 6.0 as the front-end GUI, and the
> MSDE version of SQL Server as the back-end (it's a program for a
> really small # of users -- less then 3-4).
> I'm trying to determine, through the Visual Basic interface, the
> permissions of each user that's using the application on his/her
> machine.
> For example, let's say I'm user "Michael" that's sitting down at my
> machine using the app. I've written. The security for logging into
> SQL Server will be setup using Windows Security (Trusted Connection)
> as opposed to Windows & SQL Server security. When Michael accesses a
> particular form in the VB 6.0 GUI, I want to run some code that
> automatically checks Michael's permission levels on the underlying
> table (actually, a stored procedure supplying the data from the table)
> that supplies the data to the form he's looking at and then give him
> some feedback on the form as to what type of permissions he has while
> he's browsing through the data shown in the form.
> For example, Michael opens a particular form, code in the background
> is run to identify that this is Michael accessing the form, the code
> returns a value that identifies what type of permissions he has on the
> data in the form, and a text box on the form informs Michael (for
> example) that he only has read-only permissions to the data he is
> viewing and cannot edit any of the data.
> As another example, user Karen sits down at her computer, logs into
> the application, opens the same form that Michael just opened, the
> code is run in VB to detect the level of permissions she has on the
> data being displayed in the form, and the text box on the form informs
> her that she has editing permissions on the data in the underlying
> table.
> Etc...
> If anyone can post an example of the code they use in accomplishing
> this task in an application they've written, I'd really appreciate a
> point in the right direction or a real-world example that's been
> implemented by one of you. I've written several apps. thus far using
> MSDE as the back-end, but the previous apps. I've written were for
> clients that didn't care about restricting access to the
> application... everyone could pretty much use the application as they
> desired and do anything they desired to the data.
> The current client I'm writing the app. mentioned here for wants to
> have security in place to where various users access the application
> with various levels of permissions to do stuff (or *not* do stuff) to
> the data in the application.
> Thanks very much in advance for any assistance / code provided!
> Sincerely,
> Brad McCollum
> bmccoll1@.midsouth.rr.com
See my response in comp.database.ms-sqlserver - please don't multipost.
Simon