Get all User Databases

When I execute the sp_databases proc all I get are the databases that the
logged in user has rights to. I want a stored procedure that will return
just the names of all the non-system databases that I can populate a
dropdown list with. I really don't want to add the user to every database
or give them an Administrator role just to see the user table names. Any
help on this would be much appreciated.
JohnHi, John
Use something like this:
SELECT name FROM master.dbo.sysdatabases
WHERE HAS_DBACCESS(name) = 1
AND name NOT IN ('master','tempdb','model','msdb')
ORDER BY name
Razvan|||John,
Try:
SELECT NAME FROM MASTER..SYSDATABASES
WHERE NAME NOT IN ('MASTER','MSDB','TEMPDB','MODEL','DISTR
IBUTOR')
HTH
Jerry
"john wright" <riley_wright@.hotmail.com> wrote in message
news:%23jo41JN0FHA.2212@.TK2MSFTNGP15.phx.gbl...
> When I execute the sp_databases proc all I get are the databases that the
> logged in user has rights to. I want a stored procedure that will return
> just the names of all the non-system databases that I can populate a
> dropdown list with. I really don't want to add the user to every database
> or give them an Administrator role just to see the user table names. Any
> help on this would be much appreciated.
>
> John
>|||>> I want a stored procedure that will return just the names of all the
You can query the INFORMATION_SCHEMA.SCHEMATA view or the sysdatabases
system table to the the list of all databases. You can avoid the master,
model, msdb, mssqlweb, tempdb, Pubs & Northwind databases in the WHERE
clause to get the list of non-system databases.
Anith|||Great. This works just fine.
John
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:ulawJVN0FHA.3956@.TK2MSFTNGP09.phx.gbl...
> You can query the INFORMATION_SCHEMA.SCHEMATA view or the sysdatabases
> system table to the the list of all databases. You can avoid the master,
> model, msdb, mssqlweb, tempdb, Pubs & Northwind databases in the WHERE
> clause to get the list of non-system databases.
> --
> Anith
>sql

Get All Servers and Databases from MSSQL Server 2005 Express

Is there any way to get all available servers and databases from Microsoft SQL Server 2005 Express without connecting to it at all? I am trying to write an application that uses SS05E and I would like to have an option to display all available servers and databases.

check SQLCMD. Form command prompt ...

for all the sql server instance in a network - sqlcmd -L

to get all the database name from a sql server instance -- sqlcmd -q"Select *from sys.databases' -SserverName -Ppassword

Madhu

|||There is no way to pull a list of databases from the servers without connecting to the databases. sqlcmd with the -L switch can output all the SQL Servers on your Windows network. You would need to log into each one and pull the database list from the master.dbo.sysdatabases or sys.databases objects to see what databases are available on each.|||That will work fine. Is there any way I can run sqlcmd -L in a vb.net program and get the results put into a string array? I am writing an application that uses SQL Server and I want the servers/databases to be put into combo boxes or list boxes to make it easier to select which server/database to use.|||

I found something google that will work perfectly for what I need. Here is the code:

Code Snippet

Try

Dim myprocess As New Process

Dim StartInfo As New System.Diagnostics.ProcessStartInfo

StartInfo.FileName = "cmd" 'starts cmd window

StartInfo.RedirectStandardInput = True

StartInfo.RedirectStandardOutput = True

StartInfo.UseShellExecute = False 'required to redirect

StartInfo.CreateNoWindow = True 'creates no cmd window

myprocess.StartInfo = StartInfo

myprocess.Start()

Dim SR As System.IO.StreamReader = myprocess.StandardOutput

Dim SW As System.IO.StreamWriter = myprocess.StandardInput

SW.WriteLine("sqlcmd -L") 'the command you wish to run.....

SW.WriteLine("exit") 'exits command prompt window

Me.txtOutput.Text = SR.ReadToEnd 'returns results of the command window

SW.Close()

SR.Close()

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

Thanks for your help in getting me this far! I really appreciate it

Get all databases in a SQL db

Is it possible to programmatically get a list of all the databases in a SQL
server db ?
TIA,
-FatemaSELECT name FROM master..sysdatabases
Jacco Schalkwijk
SQL Server MVP
"Fatema" <Fatema@.discussions.microsoft.com> wrote in message
news:DEFB1153-6989-4EAB-AFF6-08B58B51806B@.microsoft.com...
> Is it possible to programmatically get a list of all the databases in a
> SQL
> server db ?
> TIA,
> -Fatema|||Thanks for the prompt reply.
I also need answers for the following:
- Is there a way to find out all the instances of the SQL server running ?
- Read the alerts generated by SQL server.
- Read the logs generated by SQL server.
Thanks,
-Fatema
"Jacco Schalkwijk" wrote:

> SELECT name FROM master..sysdatabases
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Fatema" <Fatema@.discussions.microsoft.com> wrote in message
> news:DEFB1153-6989-4EAB-AFF6-08B58B51806B@.microsoft.com...
>
>|||Jacco
I can't believe that Jacco has suggested to query a system table :-)
select CATALOG_NAME from INFORMATION_SCHEMA.SCHEMATA
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:u$l6Y7nPFHA.2604@.TK2MSFTNGP10.phx.gbl...
> SELECT name FROM master..sysdatabases
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Fatema" <Fatema@.discussions.microsoft.com> wrote in message
> news:DEFB1153-6989-4EAB-AFF6-08B58B51806B@.microsoft.com...
>|||:-)
Jacco Schalkwijk
SQL Server MVP
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:Olf6sSoPFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Jacco
> I can't believe that Jacco has suggested to query a system table :-)
> select CATALOG_NAME from INFORMATION_SCHEMA.SCHEMATA
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote
> in message news:u$l6Y7nPFHA.2604@.TK2MSFTNGP10.phx.gbl...
>

Generating reports on very large and "live" databases

Hello All,
I am working on a application which has a table with 100 million
records.
A background process keeps inserting 5 rows per second into this
table.
My client is generating reports from that table. They are interested
in seeing reports with real time data. This means that as soon as a
record is inserted into the table, the reports should process that
record as well.
At the max around 20K records are processed to generate a report. When
this happens it takes simply 30 min to generate a report. Upon
analysis I find that since the table is so huge, the indexes on those
table is also very huge.
What can I do to fix this, I have thought about denormalizing the
table. But the some programmers say that picking up data from one
table is better because doing joins on multiple tables will be even
slower.
Another approach is to bring in a data warehouse but I don't know much
about this except what I learnt in MSDN session about creating of data
cubes. But I suppose cube can be created only when data is static. but
in my case new records are inserted every second and they are to be
included in the report.
The 3rd approach is that I create report specific tables and create a
trigger (or a C programm which polls for changes in main table) and
every time new records are inserted into the main table, I preprocess
them. Then when the users make a request for the report I generate my
report from the preprossed table.
But I feel the trigger will be fired to many times and if the number
of reports are significant (> 35) then trigger/C program could become
a bottleneck itself.
What should I do? it is such a tricky problem.
Please help me and give me some advice. Thank you for your help.
regards,
Abhishektable size (or row count) is irrelevent unless a
table/index scan is involved.
5 rows/sec insert is a negligible load on the system.
however, a report that involves 20k rows with good indexes
should not take 30min to run.
1) what is the query,
2) what are the indexes on this table
3) what does the execution plan show?
(indexes used, type of operation, rows involved, costs)
>--Original Message--
>Hello All,
>I am working on a application which has a table with 100
million
>records.
>A background process keeps inserting 5 rows per second
into this
>table.
>My client is generating reports from that table. They are
interested
>in seeing reports with real time data. This means that as
soon as a
>record is inserted into the table, the reports should
process that
>record as well.
>At the max around 20K records are processed to generate a
report. When
>this happens it takes simply 30 min to generate a report.
Upon
>analysis I find that since the table is so huge, the
indexes on those
>table is also very huge.
>What can I do to fix this, I have thought about
denormalizing the
>table. But the some programmers say that picking up data
from one
>table is better because doing joins on multiple tables
will be even
>slower.
>Another approach is to bring in a data warehouse but I
don't know much
>about this except what I learnt in MSDN session about
creating of data
>cubes. But I suppose cube can be created only when data
is static. but
>in my case new records are inserted every second and they
are to be
>included in the report.
>The 3rd approach is that I create report specific tables
and create a
>trigger (or a C programm which polls for changes in main
table) and
>every time new records are inserted into the main table,
I preprocess
>them. Then when the users make a request for the report
I generate my
>report from the preprossed table.
>But I feel the trigger will be fired to many times and if
the number
>of reports are significant (> 35) then trigger/C program
could become
>a bottleneck itself.
>What should I do? it is such a tricky problem.
>Please help me and give me some advice. Thank you for
your help.
>regards,
>Abhishek
>.
>

Generating Foreign Key Script

We sometimes get very large databases that we want to cut down to use for
testing.

The information is all related to a central accounts table.

The way I thought of doing this is to grab all the foreign constraints and
turn them into cascade delete constraints, then delete as many accounts as I
want.

After this I will restore the constraints back to their original state.

I am having a problem doing this as I cannot find a way to programatically
get the add constraint foreign key sql.

For example, I can use sysforeignkeys to list all the foreign keys or
ADO.OpenSchema(adSchemaForeignKeys...) but this doesn't give me the actual
SQL to modify.

My idea was to go through the database saying:

alter table (tablename) drop constraint (foreign key)
alter table (tablename) add constraint (foreign key) on delete cascade

delete various accounts and related data

alter table (tablename) drop constraint (foreign key)
alter table (tablename) add constraint (foreign key)

Can anyone assist?

Thanks[posted and mailed, please reply in news]

Jason Madison (jason.madison@.btinternet.com) writes:
> We sometimes get very large databases that we want to cut down to use for
> testing.
> The information is all related to a central accounts table.
> The way I thought of doing this is to grab all the foreign constraints
> and turn them into cascade delete constraints, then delete as many
> accounts as I want.

Here is some code that I have lying around. It does not exactly do what
you are asking for - it is actually moving the fkeys to refer from a
previous generation a table to a new, but you should be able to work
from it.

It is actually part of a Perl script, which is why all @. are preceded
by \. Just remove these. There are also things that start with $. These
are Perl variables that are set in the beginning of the snippet.

# Determine parameters that depends on the SQL Server version.
if (version_ge($X->{SQL_version}, '7')) {
$vclen = 4000;
$varchar = 'nvarchar';
$maxlen = 127; # Max len for an object name.
$quotef = 'quotename'; # Function to quote a name.
$lenf = 'len'; # Function to get string length.
}
else {
die "The SQL generated by aba_tblfkey2 does not run on SQL 6.5\n";
}

# Stringify table names
$old_table = sql_string($old_table);
$this_table = sql_string($this_table);

$sql = <<SQLEND;
-- Move foreign keys from $old_table to $this_table.
SET NOCOUNT ON

DECLARE \@.dummy tinyint,
\@.fktbl sysname,
\@.fkcol sysname,
\@.refcol sysname,
\@.keyno smallint,
\@.constr sysname,
\@.prev_constr sysname,
\@.drop $varchar($vclen),
\@.add1 $varchar($vclen),
\@.add2 $varchar($vclen),
\@.tmpname sysname,
\@.no smallint,
\@.err int

DECLARE sql_fkey_cur INSENSITIVE CURSOR FOR
SELECT k = 1, constr = object_name(constid), fktbl = object_name(fkeyid),
fkcol = col_name(fkeyid, fkey), refcol = col_name(rkeyid, rkey),
keyno
FROM sysforeignkeys
WHERE rkeyid = object_id($old_table)
UNION
SELECT k = 2, NULL, NULL, NULL, NULL, NULL
ORDER BY k, constr, keyno
OPEN sql_fkey_cur

SELECT \@.no = 0
WHILE 1 = 1
BEGIN
FETCH sql_fkey_cur INTO \@.dummy, \@.constr, \@.fktbl, \@.fkcol, \@.refcol,
\@.keyno
IF \@.\@.fetch_status <> 0
BREAK

IF (\@.constr <> \@.prev_constr OR \@.constr IS NULL) AND
\@.prev_constr IS NOT NULL
BEGIN
-- Add the new foreign constraint.
PRINT \@.add1
PRINT \@.add2
EXECUTE(\@.add1 + \@.add2 + ')')
IF \@.\@.error <> 0 BREAK

-- Drop the old reference, once the new is in place. If we drop first,
-- and something goes wrong, we would lose information.
PRINT \@.drop
EXECUTE(\@.drop)
IF \@.\@.error <> 0 BREAK

-- Rename the new reference to the real name.
EXEC \@.err = sp_rename \@.tmpname, \@.prev_constr
SELECT \@.err = coalesce(nullif(\@.\@.error, 0), \@.err)
IF \@.err <> 0 BREAK
END

IF \@.keyno = 1
BEGIN
-- Set up command to create new reference under a temporary name. Use
-- place holders for the column lists.
SELECT \@.tmpname = 'new_' + ltrim(str(\@.no))
SELECT \@.tmpname = \@.tmpname +
substring(\@.constr, 1, $maxlen - $lenf(\@.tmpname))
SELECT \@.no = \@.no + 1
SELECT \@.add1 = 'ALTER TABLE ' + $quotef(\@.fktbl) +
' ADD CONSTRAINT ' + $quotef(\@.tmpname) +
' FOREIGN KEY (' + $quotef(\@.fkcol),
\@.add2 = ') REFERENCES ' + $quotef($this_table) + '(' +
$quotef(\@.refcol)

-- And set up command to drop current constraint.
SELECT \@.drop = 'ALTER TABLE ' + $quotef(\@.fktbl) +
' DROP CONSTRAINT ' + $quotef(\@.constr)
END
ELSE
BEGIN
-- Add more columns to \@.add command.
SELECT \@.add1 = \@.add1 + ', ' + $quotef(\@.fkcol),
\@.add2 = \@.add2 + ', ' + $quotef(\@.refcol)
END

SELECT \@.prev_constr = \@.constr
END

DEALLOCATE sql_fkey_cur
SQLEND

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

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

generating alter table script

Hello,

I need to change collation in my database (more databases acctualy).
Therefore, I wanted to make a script, which will do it at one more
time.

I already have a cursor, updating collation on all tables (fields) in
database.
The problem is, before I will to update the collations, I need to drop
all constrains and pk's.
But I do not want to erase them. After the collation will be updated,
these should be restored.
Therefore I wanted to additionaly script all cs, pk's for all tables,
drop them, and after updateing the collation, update the tables.

Does enyone have an idea how to do that? Or how can I get (in tsql) an
string containing information like:

ALTER TABLE [dbo].[PrmUserGroup] ADD
CONSTRAINT [FK_PrmUserGroup_PrmGroup] FOREIGN KEY
(
[id_group]
) REFERENCES [dbo].[PrmGroup] (
[id]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_PrmUserGroup_PrmUser] FOREIGN KEY
(
[id_user]
) REFERENCES [dbo].[PrmUser] (
[id]
) ON DELETE CASCADE ON UPDATE CASCADE
GO

If i can have such a part of script (it will complete script of cs and
pk's) i could store it temporary in the table, and after updating a
collation, run them with dynamic sql.

Or maybe there is any other, better way?

Thank you in advance

MateuszMatik (marzec@.sauron.xo.pl) writes:
> I need to change collation in my database (more databases acctualy).
> Therefore, I wanted to make a script, which will do it at one more
> time.
> I already have a cursor, updating collation on all tables (fields) in
> database.
> The problem is, before I will to update the collations, I need to drop
> all constrains and pk's.
> But I do not want to erase them. After the collation will be updated,
> these should be restored.
> Therefore I wanted to additionaly script all cs, pk's for all tables,
> drop them, and after updateing the collation, update the tables.
> Does enyone have an idea how to do that? Or how can I get (in tsql) an
> string containing information like:

I would rather build a new database from scripts, and then use BCP to
bulk data out and in. You can easily script the BCP commands from
sysobjects. Don't forget to include a condition to get -E for tables
with the IDENTITY property.

Best is to apply constraints after the load, but you can load them as
NOCHECK and the enable them with CHECK WITH CHECK. (That's a horrible
syntax, but it's important to actually verify the constraints when you
enable them, or else the optimizer cannot make use of them.)

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

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

Generating a list of databases in SQL2005?

Silly question but... in SQL 2000 it used to be possible to right click on
the "databases" folder in SQL enterprise administrator and export a list of
databases. I can't seem to do the same thing in SQL 2005 Management Studio.
Is there an equivelant way of doing this?
Thanks,
BradSees that feature is missing from the GUI. How about a SQL query instead?
SELECT name FROM sys.databases
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Brad Baker" <brad@.nospam.nospam> wrote in message news:uQp%234sj9GHA.3396@.TK2MSFTNGP04.phx.gbl...
> Silly question but... in SQL 2000 it used to be possible to right click on
> the "databases" folder in SQL enterprise administrator and export a list of
> databases. I can't seem to do the same thing in SQL 2005 Management Studio.
> Is there an equivelant way of doing this?
> Thanks,
> Brad
>|||Thanks! That does the trick.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uBFcnEn9GHA.4224@.TK2MSFTNGP02.phx.gbl...
> Sees that feature is missing from the GUI. How about a SQL query instead?
> SELECT name FROM sys.databases
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Brad Baker" <brad@.nospam.nospam> wrote in message
> news:uQp%234sj9GHA.3396@.TK2MSFTNGP04.phx.gbl...
>> Silly question but... in SQL 2000 it used to be possible to right click
>> on the "databases" folder in SQL enterprise administrator and export a
>> list of databases. I can't seem to do the same thing in SQL 2005
>> Management Studio. Is there an equivelant way of doing this?
>> Thanks,
>> Brad

Generate SQL Script using a DTS

Hi
Each month I back up the structure of our databases by right-clicking
on each database and select Generate SQL Script...

>From the General tab I select script all objects and from the Options
tab I select script database, along with script indexes and script
primary keys.
As we have several databases this can take some time. Is there away to
automate this using a DTS?
Thanks
MattI don't know if DTS is the way to go but you can certainly use DMO to do
this. In fact, the sample code is included with SQL. All you need to do is
modify it to run automatically and then invoke from s DTS package.
Alternatively, you could use the SP_OA family of procedures to do it. At my
last job, we scripted out some objects just this way.
"matt.cottam@.googlemail.com" wrote:

> Hi
> Each month I back up the structure of our databases by right-clicking
> on each database and select Generate SQL Script...
>
> tab I select script database, along with script indexes and script
> primary keys.
> As we have several databases this can take some time. Is there away to
> automate this using a DTS?
>
> Thanks
> Matt
>|||Yes, have a look at SQL DMO library, you could use it within an ActiveX
script in DTS.
"matt.cottam@.googlemail.com" wrote:

> Hi
> Each month I back up the structure of our databases by right-clicking
> on each database and select Generate SQL Script...
>
> tab I select script database, along with script indexes and script
> primary keys.
> As we have several databases this can take some time. Is there away to
> automate this using a DTS?
>
> Thanks
> Matt
>

Generate scripts generates dump (9.0.3152)

When I attempt to generate scripts via SSMS for several databases on this server (x64 Enterprise Windows 2003 Server SP1), I receive a "severe error" message. I have run DBCC commands of various sorts and backed up and restored but the error persists.

I have read other posts on this forum that seem to be asking about related issues and the response appears to be "talk with support" or "post a bug report". What should I do? Any assistance would be appreciated.

Here's the dump:

=====================================================================
BugCheck Dump
=====================================================================

This file is generated by Microsoft SQL Server
version 9.00.3152.00
upon detection of fatal unexpected error. Please return this file,
the query or program that produced the bugcheck, the database and
the error log, and any other pertinent information with a Service Request.


Computer type is AT/AT COMPATIBLE.
Current time is 16:55:02 03/20/07.
4 Unknown CPU 9., 2205 Mhz processor (s).
Windows NT 5.2 Build 3790 CSD Service Pack 1.

Memory
MemoryLoad = 54%
Total Physical = 4951 MB
Available Physical = 2260 MB
Total Page File = 28583 MB
Available Page File = 26144 MB
Total Virtual = 8388607 MB
Available Virtual = 8383329 MB
***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0052.txt
SqlDumpExceptionHandler: Process 55 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is
terminating this process.
* *******************************************************************************
*
* BEGIN STACK DUMP:
* 03/20/07 16:55:02 spid 55
*
*
* Exception Address = 00000000021B4CEF Module(sqlservr+00000000011B4CEF)
* Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION
* Access Violation occurred reading address 000000001DC6BEE4
* Input Buffer 510 bytes -
* SELECT SCHEMA_NAME(v.schema_id) AS [View_Schema], v.name AS [
* View_Name], st.name AS [Name], st.no_recompute AS [NoAutomaticRecomputat
* ion], st.auto_created AS [IsAutoCreated], CAST(1 - INDEXPROPERTY(st.obje
* ct_id,st.name,N'IsStatistics') AS bit) AS [IsFromIndexCreation] FROM sys
* .all_views AS v INNER JOIN sys.stats st ON st.object_id=v.object_id WHER
* E (v.type = 'V') ORDER BY [View_Schema] ASC,[View_Name] ASC,[Name] ASC
*
*
* MODULE BASE END SIZE
* sqlservr 0000000001000000 0000000003523FFF 02524000
* ntdll 0000000078EC0000 0000000078FF8FFF 00139000
* kernel32 0000000077D40000 0000000077EB2FFF 00173000
* MSVCR80 0000000078130000 00000000781FEFFF 000cf000
* msvcrt 000007FF7FC00000 000007FF7FC85FFF 00086000
* MSVCP80 000000007C420000 000000007C530FFF 00111000
* ADVAPI32 000007FF7FEE0000 000007FF7FFE4FFF 00105000
* RPCRT4 000007FF7FD30000 000007FF7FED8FFF 001a9000
* USER32 0000000078C30000 0000000078D3BFFF 0010c000
* GDI32 000007FF7FC90000 000007FF7FD28FFF 00099000
* CRYPT32 000007FF7D180000 000007FF7D2DEFFF 0015f000
* MSASN1 000007FF7D150000 000007FF7D179FFF 0002a000
* Secur32 000007FF7E7F0000 000007FF7E811FFF 00022000
* MSWSOCK 000007FF76FE0000 000007FF7705BFFF 0007c000
* WS2_32 000007FF77150000 000007FF7717FFFF 00030000
* WS2HELP 000007FF77140000 000007FF7714BFFF 0000c000
* USERENV 000007FF7C4A0000 000007FF7C5A8FFF 00109000
* opends60 00000000333E0000 00000000333E7FFF 00008000
* NETAPI32 000007FF77370000 000007FF77407FFF 00098000
* SHELL32 000007FF7F190000 000007FF7FB97FFF 00a08000
* SHLWAPI 000007FF7EF60000 000007FF7EFFAFFF 0009b000
* IMM32 000007FF7D320000 000007FF7D358FFF 00039000
* comctl32 000007FF7F000000 000007FF7F186FFF 00187000
* psapi 000007FF7E1B0000 000007FF7E1BFFFF 00010000
* instapi 0000000048060000 000000004806CFFF 0000d000
* sqlevn70 000000004F610000 000000004F7B8FFF 001a9000
* SQLOS 00000000344D0000 00000000344D5FFF 00006000
* rsaenh 000000000FFB0000 000000000FFEDFFF 0003e000
* AUTHZ 000007FF7E2C0000 000007FF7E2ECFFF 0002d000
* MSCOREE 000006427EE60000 000006427EED3FFF 00074000
* ole32 000007FF7ECE0000 000007FF7EF51FFF 00272000
* msv1_0 000007FF7E330000 000007FF7E373FFF 00044000
* iphlpapi 000007FF57250000 000007FF57280FFF 00031000
* kerberos 000007FF77410000 000007FF774C2FFF 000b3000
* cryptdll 000007FF7DAB0000 000007FF7DABEFFF 0000f000
* schannel 000007FF7DB70000 000007FF7DBB0FFF 00041000
* COMRES 000007FF7E920000 000007FF7E9E5FFF 000c6000
* XOLEHLP 000007FF5C560000 000007FF5C566FFF 00007000
* MSDTCPRX 000007FF67140000 000007FF67210FFF 000d1000
* msvcp60 0000000009460000 0000000009549FFF 000ea000
* MTXCLU 000007FF7B540000 000007FF7B569FFF 0002a000
* VERSION 000007FF7FBF0000 000007FF7FBFAFFF 0000b000
* WSOCK32 000007FF770F0000 000007FF770F9FFF 0000a000
* OLEAUT32 000007FF7E9F0000 000007FF7EB03FFF 00114000
* CLUSAPI 000007FF7B1C0000 000007FF7B1E3FFF 00024000
* RESUTILS 000007FF7B310000 000007FF7B32BFFF 0001c000
* DNSAPI 0000000009560000 00000000095ADFFF 0004e000
* winrnr 000007FF7E820000 000007FF7E82AFFF 0000b000
* WLDAP32 000007FF7E780000 000007FF7E7E5FFF 00066000
* rasadhlp 0000000009650000 0000000009656FFF 00007000
* security 000007FF77530000 000007FF77534FFF 00005000
* msfte 0000000049980000 0000000049D2DFFF 003ae000
* dbghelp 000000000BAA0000 000000000BBFCFFF 0015d000
* WINTRUST 000007FF7E210000 000007FF7E25DFFF 0004e000
* imagehlp 000007FF7E2A0000 000007FF7E2B2FFF 00013000
* dssenh 000000000FF70000 000000000FFABFFF 0003c000
* hnetcfg 000007FF6D1F0000 000007FF6D280FFF 00091000
* wshtcpip 000007FF76FA0000 000007FF76FAAFFF 0000b000
* NTMARTA 000007FF7E2F0000 000007FF7E32BFFF 0003c000
* SAMLIB 000007FF76F80000 000007FF76F95FFF 00016000
* ntdsapi 000007FF7DAD0000 000007FF7DAF3FFF 00024000
* xpsp2res 000007FF5C210000 000007FF5C4D6FFF 002c7000
* CLBCatQ 0000000003530000 000000000360CFFF 000dd000
* sqlncli 00000000337A0000 0000000033A5FFFF 002c0000
* COMCTL32 000007FF76200000 000007FF762EDFFF 000ee000
* comdlg32 000007FF7D360000 000007FF7D3D6FFF 00077000
* SQLNCLIR 0000000035000000 0000000035032FFF 00033000
* msftepxy 0000000003670000 0000000003690FFF 00021000
* xpsqlbot 000000004A7C0000 000000004A7C7FFF 00008000
* xpstar90 0000000053C30000 0000000053CB5FFF 00086000
* SQLSCM90 0000000053AD0000 0000000053ADBFFF 0000c000
* ODBC32 000007FF63EB0000 000007FF63F16FFF 00067000
* BatchParser90 00000000520C0000 00000000520ECFFF 0002d000
* ATL80 000000007C630000 000000007C64DFFF 0001e000
* odbcint 000007FF63DC0000 000007FF63DD7FFF 00018000
* xpstar90 000000000FC60000 000000000FC85FFF 00026000
* xplog70 0000000034730000 000000003473FFFF 00010000
* xplog70 000000000FCA0000 000000000FCA2FFF 00003000
*
* P1Home: FFFFFADF7A660868:
* P2Home: 000000000AC3E180: FFFFFADF7A660868 000000000AC3E180 0000000000000001 000000009EB0A930 00010000
00000000 FFFFFADF878AD010
* P3Home: 0000000000000001:
* P4Home: 000000009EB0A930: 0000000000000000 000000009EC4E1D8 000000009EB8A000 0000006400000018 00000000
9EACE000 FFFFFFFF00000000
* P5Home: 0001000000000000:
* P6Home: FFFFFADF878AD010:
* ContextFlags: 000000000010001F:
* MxCsr: 0000000000001FA0:
* SegCs: 0000000000000033:
* SegDs: 000000000000002B:
* SegEs: 000000000000002B:
* SegFs: 0000000000000053:
* SegGs: 000000000000002B:
* SegSs: 000000000000002B:
* EFlags: 0000000000010246: 005C003A0043003D 0067006F00720050 0020006D00610072 0065006C00690046 00510053
005C0073 004C004D0058004C
* Rax: 000000001DC6BD50:
* Rcx: 0000000000000000:
* Rdx: 0000000000000000:
* Rbx: 0000000000000001:
* Rsp: 000000000AC3E720: 000000000AC3E778 00000000013DA150 0000000000000000 0000000000000000 00000000
00000000 000000009EB9DD40
* Rbp: 000000009EB0A930: 0000000000000000 000000009EC4E1D8 000000009EB8A000 0000006400000018 00000000
9EACE000 FFFFFFFF00000000
* Rsi: 0000000000000000:
* Rdi: 0000000000000000:
* R8: 000000011DC6BD50: 000000001DC60001 0000000000000000 0000000000000000 0000000000000000 00000000
00000031 000000011DC6ACB0
* R9: 000000009EC4E228: 0000000000000000 0000000000000000 000000009EB0A930 0000000000000000 00000000
00000000 0000000000000000
* R10: 0000000001017C00: 2444C74878EC8348 5C8948FFFFFFFE30 4868246C89487024 247C894860247489 894C5024
64894C58 8B48DA8B4848246C
* R11: 000000000AC3E988: 0000000000000000 00000000FFFFFFFF 0000000000000000 0000000000000000 00000000
FFFFFFFF 0000000000000000
* R12: 000000011DC6BD50: 000000001DC60001 0000000000000000 0000000000000000 0000000000000000 00000000
00000031 000000011DC6ACB0
* R13: 000000000AC3EA00: 0000000000000000 0000000000000000 0000000002ECF280 0000000000000000 FFFFFFFF
FFFFFFFE 000000011D9581C0
* R14: 0000000003640580: 0000000200000000 0000000000000008 0000026300000000 0000000003640080 00000001
1DC6C300 000000011DC6A2B0
* R15: 0000000000000002:
* Rip: 00000000021B4CEF: 848900000194808B 548D480000018024 3687E8C88B497824 485024448948FEEE 1374187D
3B66E88B 01DCB58B187D8B66
* *******************************************************************************
* -
* Short Stack Dump
00000000021B4CEF Module(sqlservr+00000000011B4CEF)
00000000021BAD40 Module(sqlservr+00000000011BAD40)
00000000021BAEBA Module(sqlservr+00000000011BAEBA)
00000000021BB029 Module(sqlservr+00000000011BB029)
00000000017B32FA Module(sqlservr+00000000007B32FA)
0000000001343FBC Module(sqlservr+0000000000343FBC)
0000000002527308 Module(sqlservr+0000000001527308)
0000000001CC9A78 Module(sqlservr+0000000000CC9A78)
0000000001391E90 Module(sqlservr+0000000000391E90)
0000000001343ACB Module(sqlservr+0000000000343ACB)
0000000001343B70 Module(sqlservr+0000000000343B70)
0000000001343D4F Module(sqlservr+0000000000343D4F)
0000000001079D91 Module(sqlservr+0000000000079D91)
00000000010796A1 Module(sqlservr+00000000000796A1)
000000000147EE04 Module(sqlservr+000000000047EE04)
000000000147EEBB Module(sqlservr+000000000047EEBB)
0000000001023AA2 Module(sqlservr+0000000000023AA2)
00000000010243A2 Module(sqlservr+00000000000243A2)
0000000001035FDA Module(sqlservr+0000000000035FDA)
00000000010355E5 Module(sqlservr+00000000000355E5)
00000000010366C8 Module(sqlservr+00000000000366C8)
0000000001289FEE Module(sqlservr+0000000000289FEE)
000000000100782E Module(sqlservr+000000000000782E)
0000000001007FB2 Module(sqlservr+0000000000007FB2)
00000000010079D1 Module(sqlservr+00000000000079D1)
000000000146B4D7 Module(sqlservr+000000000046B4D7)
000000000146BC0A Module(sqlservr+000000000046BC0A)
000000000146BB01 Module(sqlservr+000000000046BB01)
000000000146B999 Module(sqlservr+000000000046B999)
00000000781337A7 Module(MSVCR80+00000000000037A7)
0000000078133864 Module(MSVCR80+0000000000003864)
0000000077D6B69A Module(kernel32+000000000002B69A)

PSS @.0x0000000099D9DBF0
--

CSession @.0x0000000099D9C410
-
m_spid = 55 m_cRef = 22 m_rgcRefType[0] = 1
m_rgcRefType[1] = 1 m_rgcRefType[2] = 19 m_rgcRefType[3] = 1
m_rgcRefType[4] = 0 m_rgcRefType[5] = 0 m_pmo = 0x0000000099D9C080
m_pstackBhfPool = 0x0000000000000000 m_dwLoginFlags = 0x83e0 m_fBackground = 0
m_fClientRequestConnReset = 0 m_fUserProc = -1 m_fConnReset = 0
m_fIsConnReset = 0 m_fInLogin = 0 m_fAuditLoginSent = 1
m_fAuditLoginFailedSent = 0 m_fReplRelease = 0 m_fKill = 0
m_ulLoginStamp = 122 m_eclClient = 5 m_protType = 6
m_hHttpToken = FFFFFFFFFFFFFFFF

m_pV7LoginRec

0000000000000000: 5c010000 02000972 00100000 00000006 e8130000 ?\......r............

0000000000000014: 00000000 e0830000 00000000 00000000 5e000f00 ?................^...

0000000000000028: 00000000 00000000 7c002600 c8000f00 e6000000 ?........|.&.........

000000000000003C: e6001c00 1e010000 1e010000 0014c23f 4c1e1e01 ?...............?L...

0000000000000050: 3e005c01 00005c01 00000000 0000???????????????>.\...\.......

CPhysicalConnection @.0x0000000099D9C2E0

m_pPhyConn->m_pmo = 0x0000000099D9C080
m_pPhyConn->m_pNetConn = 0x0000000099D9CB00
m_pPhyConn->m_pConnList = 0x0000000099D9C3E0
m_pPhyConn->m_pSess = 0x0000000099D9C410 m_pPhyConn->m_fTracked = -1
m_pPhyConn->m_cbPacketsize = 4096 m_pPhyConn->m_fMars = 0 m_pPhyConn->m_fKill = 0

CBatch @.0x0000000099D9CFD0
--
m_pSess = 0x0000000099D9C410 m_pConn = 0x0000000099D9CEC0 m_cRef = 3
m_rgcRefType[0] = 1 m_rgcRefType[1] = 1 m_rgcRefType[2] = 1
m_rgcRefType[3] = 0 m_rgcRefType[4] = 0 m_pTask = 0x00000000062C4DA8

EXCEPT (null) @.0x000000000AC3B608

exc_number = 0 exc_severity = 0 exc_func = 0x0000000001891970

Task @.0x00000000062C4DA8

CPU Ticks used (ms) = 159 Task State = 2
WAITINFO_INTERNAL: WaitResource = 0x0000000000000000 WAITINFO_INTERNAL: WaitType = 0x0
WAITINFO_INTERNAL: WaitSpinlock = 0x0000000000000000 SchedulerId = 0x3
ThreadId = 0x1374 m_state = 0 m_eAbortSev = 0

EC @.0x0000000099D9DC00
-
spid = 55 ecid = 0 ec_stat = 0x0
ec_stat2 = 0x0 ec_atomic = 0x0 __fSubProc = 1
ec_dbccContext = 0x0000000000000000 __pSETLS = 0x0000000099D9CF30 __pSEParams = 0x0000000099D9D350
__pDbLocks = 0x0000000099D9DCC0

SEInternalTLS @.0x0000000099D9CF30

m_flags = 0 m_TLSstatus = 3 m_owningTask = 0x00000000062C4DA8
m_activeHeapDatasetList = 0x0000000099D9CF30
m_activeIndexDatasetList = 0x0000000099D9CF40

SEParams @.0x0000000099D9D350
-
m_lockTimeout = 10000 m_isoLevel = 4096 m_logDontReplicate = 0
m_neverReplicate = 0 m_XactWorkspace = 0x0000000091150410
m_pSessionLocks = 0x0000000099D9DD70 m_pDbLocks = 0x0000000099D9DCC0
m_execStats = 0x000000011CADE810 m_pAllocFileLimit = 0x0000000000000000

Hi Bob,

For this one please open a case with product support. They will need to collect some more information from your system to determine the root of the problem.

generate a strong name key file

I am a Newbie to programming and databases and would like to create a simple program for doing full text searching on a SQL database.

I have downloaded and installed Visual Basic 2005 Express, Web Developer 2005 Express, and SQL Server 2005 Express.I have watched several hours of video tutorials and done numerous tutorials. I feel I am making progress and having a lot of fun.My machine is running XP home edition.

I have found the following resource that looks like a good place for me to start with creating a full text search program:

“SQL Server 2005 Books Online

Item Finder Sample

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

However I am stuck at the beginning with trying to “generate a strong name key file.” I don’t know how to “open a command prompt” (I did confess to being a Newbie!!).I searched the database for this forum and found a relevant thread stating that “SQLCMD is the command-line utility for SQL Server and is included with SQL Server 2005 Express Edition.”I found the SQLCMD program on my computer but when I opened it, it does appear to provide the options listed below in the tutorial (i.e., “click start,” “Point to all programs,” etc.).

Initially here is what I need based on the first section of the tutorial:

Building the Sample

If you have not already created a strong name key file, generate the key file using the following instructions.

To generate a strong name key file

Open a Microsoft Visual Studio 2005 command prompt. ClickStart, point toAll Programs, point toMicrosoft .NET Framework SDK 2.0, and then clickSDK Command Prompt.

-- or --

Open a Microsoft .NET Framework command prompt. ClickStart, point toAll Programs, point toMicrosoft .NET Framework SDK 2.0, and then clickSDK Command Prompt.

Use the change directory command (CD) to change the current directory of the command prompt window to the folder where the samples are installed.

Note:

To determine the folder where samples are located, click theStartbutton, point toAll Programs, point toMicrosoft SQL Server 2005, point toDocumentation and Tutorials, and then clickSamples Directory. If the default installation location was used, the samples are located in <system_drive>:\Program Files\Microsoft SQL Server\90\Samples.

At the command prompt, run the following command to generate the key file:

sn -k SampleKey.snk

Important:

For more information about the strong-name key pair, see "Security Briefs: Strong Names and Security in the .NET Framework" in the .NET Development Center on MSDN.

How do I complete this initial step of generating a strong name key file for the tutorial? I guess I am missing something simple and once I get past this I will be able to complete the tutorial. Any help will be greatly appreciated.

Hi,

If you are looking for the place of VS 2005 Command Prompt in menu you can have a look at the picture I have placed at http://www.kodyaz.com/photos/visual_studio_2005/picture347.aspx

After you have run the command promt, then you will go to samples directory using the old dos commands. (cd.. and cd <directory name>)

And then run the command for "sn"

Eralper

|||

Thanks for the picture - it was worth a thousand words. The only problem is that the VS 2005 Command Prompt program is not there on either of my computers that have VS 2005 Express installed.

Perhaps VS 2005 "Express" does not include this feature?

When I use Windows Explorer to try to find the Samples directory in program files, I can't find it either.

If the default installation location was used, the samples are located in <system_drive>:\Program Files\Microsoft SQL Server\90\Samples.

In researching this problem I noticed that SQL Server 2005 "Express" does not support full text search, so I may not be able to do the full text search exercise even if I get past the "generate a strong name key file" issue.

I am really enjoying trying out the "Express" editions of the Microsoft programs and may just have to live with the limitations for now.

|||

With further research I learned that SQL Server 2005 Express "Advanced" does support full text search. So I uninstalled the regular version and installed the "Advanced" version. I am hopeful that I will be able to do full text searches.

But now I am back where I began with trying to do the tutorial with the first step being "generate a strong name key file" as discussed previously.

How can I get the VS 2005 Command Prompt program installed on my machine? I haven't found a download for it and apparently it is not part of the standard install for Visual Studio 2005 Express.

Or, is there another way to "generate a strong name key file"?

Thanks for any help you can provide.

|||It weird that you don't have this, I have nor seen that. However search your machine for SN.exe and then add the path to that .exe as part of your regular windows path. Now go to the directory where the samples are installed and see if sn /? works. If it does then you should be able to build the samples.|||

Thanks Euan.

In working through the various exercises and tutorials in the VB 2005 Express Documentation I have found several instances where the instructions don't quite match up with the program.

As a Newbie I am not familiar with how to add the sn.exe to the regular windows path. I found the sn.exe file in the Program Files\Microsoft Visual Studio 8\SDK\ v2.0\Bin folder. I just don't know what to do with it.

As an immediate solution to being able to work with the sample "ItemFinder" program, I right-clicked the project in Solution Explorer and clicked "Properties" and "Signing" and unchecked "Sign the assembly" which at least allows me to build and run the application. Since it is a sample that will not be distributed, I am not worried about the security. It would be nice to figure out the "strong name key file" issue for future projects.

|||

http://msdn2.microsoft.com/en-us/ms345276.aspx

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

HTH

Generalising Rownum for all the databases

Hi

I have as issue with this rownum.This is supported in different forms in different databases like top in mssql ,limit in mysql and all..i want to genaralise this independent of the type of database because i want to run the query as i wont be knowing the type of the DB used by my user.

can anyone suggest a solution for this ? is there anyway of generating a series of numbers with the query by calling the function in that query repetitively ?

Regards
SreenathA UDF won't help you, because not all databases support User Defined Functions.

I think you will need to use something like this, which requires a primary key or unique sort order on your result set:

select a.Pkey, count(*) as RowNum
from YourTable a
inner join YourTable b on a.Pkey >= b.Pkey
group by a.PKey