Named queries ?

Hi,
Is there anyway i can use 2 databases in one dataset using named
queries ?
here is what i want to do
select name from database1.dbo.employees
union
select name from dabaase2.dbo.employees
i dont have reportmodel veiew etc. I am using plaing old reporting
services 2005.
ThanksAny valid SQL that you can run against SQL Server can be used. You'll want
to use the generic query designer (there is a button to switch from
graphical to generic).
So, yes, you can easily do this. Heck, I have cross database joins.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Prash" <PrashantiKoti@.gmail.com> wrote in message
news:1187030848.237751.264950@.w3g2000hsg.googlegroups.com...
> Hi,
> Is there anyway i can use 2 databases in one dataset using named
> queries ?
> here is what i want to do
> select name from database1.dbo.employees
> union
> select name from dabaase2.dbo.employees
> i dont have reportmodel veiew etc. I am using plaing old reporting
> services 2005.
> Thanks
>

Mysterious DB Owner Changed

We are running a SQL 2000 server on Windows 2003 server. Somehow the db
owner changed on some of the databases. It's interesting that it changed to
the first login account listed on the server, not sure if that means anythin
g
or not. Has anyone seen the db owner mysteriously change and if so what
caused this?"Brenda" <Brenda@.discussions.microsoft.com> wrote in message
news:CAC1D3F3-225C-42AD-A992-0594561F997B@.microsoft.com...
> We are running a SQL 2000 server on Windows 2003 server. Somehow the db
> owner changed on some of the databases. It's interesting that it changed
> to
> the first login account listed on the server, not sure if that means
> anything
> or not. Has anyone seen the db owner mysteriously change and if so what
> caused this?
Did someone detach, then re-attach the databases in question using EM (as
opposed to manually executing sp_attach_db)? The attach UI allows you to
select the owner from sorted a combobox; which effectively makes the first
user the default owner. (sp_attach_db sets the owner to the current user.)
I'd think the old owner could be inferred from the orphaned dbo alias when
the db is attached, and that best-case behavior would be for previously
established ownership to be retained... but maybe not quite so simple.
In any case, sp_changedbowner is the easiest remedy.
-Mark|||Mark,
No the db's in question haven't been re-attached...I already changed the
owner, but I was just concerned that the owner changed mysteriously...Just
trying to see if anyone else has seen this happen...
Thanks!
"Mark J. McGinty" wrote:

> "Brenda" <Brenda@.discussions.microsoft.com> wrote in message
> news:CAC1D3F3-225C-42AD-A992-0594561F997B@.microsoft.com...
> Did someone detach, then re-attach the databases in question using EM (as
> opposed to manually executing sp_attach_db)? The attach UI allows you to
> select the owner from sorted a combobox; which effectively makes the first
> user the default owner. (sp_attach_db sets the owner to the current user.
)
> I'd think the old owner could be inferred from the orphaned dbo alias when
> the db is attached, and that best-case behavior would be for previously
> established ownership to be retained... but maybe not quite so simple.
> In any case, sp_changedbowner is the easiest remedy.
> -Mark
>
>|||I've never seen an unexplained database ownership change. Mark mentioned to
usual reasons. Another possibility is that a utility script was run that
changed the owners, such as one intended to correct invalid database owners
.
Hope this helps.
Dan Guzman
SQL Server MVP
"Brenda" <Brenda@.discussions.microsoft.com> wrote in message
news:CAC1D3F3-225C-42AD-A992-0594561F997B@.microsoft.com...
> We are running a SQL 2000 server on Windows 2003 server. Somehow the db
> owner changed on some of the databases. It's interesting that it changed
> to
> the first login account listed on the server, not sure if that means
> anything
> or not. Has anyone seen the db owner mysteriously change and if so what
> caused this?

MySQL vs. MS SQL Server. What's the difference?

I'm a beginner programmer and databases are still a bit of a mystery to me. I have some basic questions that I can't find direct answers for anywhere but which I think should be fairly simple for someone acquainted with using databases in programming.

1. MySQL & MS SQL Server (or Express)...what's the difference? Are they mutually compatible? Are all SQL files written in the same file format?

2. If I write create a MySQL database on my website to store customer data, can my application which was written with VB 2005 Express (and which uses MS SQL Server Express) read those database files? Vice-versa?

3. If I create a MS SQL database in VB express for my application, is that database on some huge central server somewhere or is my computer used as the server, or what? (yeah, I know that's probably a really stupid question but I can find the answer in any of the three programming books I've bought in recent months or online either.)

4. I've read the MySQL is free unless I use the databases within an actual application from which I'm making money, in which case I need to buy a license. What about MS SQL Server or MS SQL Express? Microsoft can be so withholding about what costs money, what is free, and what is temporarily free.

That's all for now...I hope someone can answer my stupid questions.

1. No the file types are not compatible, they are from different vendors. Even the SQL symtax differ but shares a common dictionary the SQL Ansi standard.

2. That depends on how you did your database layer implementation. Some developers use the generic adapter approach making database capable for many database whereas only the adapter using to connect to the database has to be exchange. if you are using the SQL* classes in youa application and not the e.g. OLEDb like olecbcommand you cannot switch to another database within your application.

3. The SQL Server editions (beside the compact edition) run as services. They do not have to run on a "central computer" and can be based on normal pcs (depending on the edition you need a server OS to install the services like for enterprise edition) The SQL Server express editions were designed to run on normal worksatation having a local database stored on the computer.

4. All edition beside the SQL Server Express and the Compact editions have to be licensed. they either go by the Server/cal or the processor licence. SQL Server Express and Comnpact edition are free not matter if you shipping them with your applications or using them privately.

See the frature comparisons on this site for more information wheter SQL Server Express fits your needs, as it has some limitations:

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

No stupid question at all :-)

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Hey Jens, thank you so much for answering my questions.

mysql to ms sql

Need a little help. I am a newbie with both databases (mysql and mssql). I
am transferring from mysql to ms sql. There are very few tables and data.
I am basically transferring structure by hand.
I am finding the syntax to be difficult to deal with. I have a txt file
with several tables and their attributes from mysql but I am not finding the
exact same thing in ms sql. I wonder if someone can look at the code and
give me a hand. I am using the EM to put this on ms sql online.
Here is an example of one table and the trouble I am having with it. This
is the mysql code to be changed. Please see my comments under this code.
CREATE TABLE room_action (
id varchar(100) NOT NULL default '',
room tinyint(3) unsigned NOT NULL default '0',
user tinyint(3) unsigned NOT NULL default '0',
name varchar(50) NOT NULL default '',
cmd varchar(10) NOT NULL default '',
x mediumint(9) defaulnamet '0',
y mediumint(9) default '0',
msg varchar(255) default NULL,
avatar tinyint(3) unsigned default NULL,
viewed tinyint(3) unsigned NOT NULL default '0',
PRIMARY KEY (id)
) TYPE=MyISAM;
I am creating the table in the EM. I am assuming that the numbers are the
field length. For example:
room tinyint(3) unsigned NOT NULL default '0'
I am attempting to put 3 in the length but it will only use 1.
I also am confused about unsigned. Also what is TYPE=MyISAM
Can anyone give me a hand?
Thanks
Laura KLaura,
Try this:
CREATE TABLE room_action (
id varchar(100) NOT NULL default '',
room smallint NOT NULL default 0,
userID smallint NOT NULL default 0, --user is a keyword, change to
userID
name varchar(50) NOT NULL default '',
cmd varchar(10) NOT NULL default '',
x integer default 0,
y integer default 0,
msg varchar(255) default NULL,
avatar smallint default NULL, --use a default of 0 instead of null if
possible, will prevent aggreatation warnings
viewed smallint NOT NULL default 0,
PRIMARY KEY (id)
)
Also, please consider removing the default '' on your not null columns. It
is contradictory and a pain in the rear to maintain.
Take some time to review Books On Line, especially data types, as many of
your conversion questions will be answered.
--Morgan
"Laura K" <klkazanAT@.ATcharter.net> wrote in message
news:%23Gz90CleDHA.1824@.TK2MSFTNGP10.phx.gbl...
> Need a little help. I am a newbie with both databases (mysql and mssql).
I
> am transferring from mysql to ms sql. There are very few tables and data.
> I am basically transferring structure by hand.
> I am finding the syntax to be difficult to deal with. I have a txt file
> with several tables and their attributes from mysql but I am not finding
the
> exact same thing in ms sql. I wonder if someone can look at the code and
> give me a hand. I am using the EM to put this on ms sql online.
> Here is an example of one table and the trouble I am having with it. This
> is the mysql code to be changed. Please see my comments under this code.
> CREATE TABLE room_action (
> id varchar(100) NOT NULL default '',
> room tinyint(3) unsigned NOT NULL default '0',
> user tinyint(3) unsigned NOT NULL default '0',
> name varchar(50) NOT NULL default '',
> cmd varchar(10) NOT NULL default '',
> x mediumint(9) defaulnamet '0',
> y mediumint(9) default '0',
> msg varchar(255) default NULL,
> avatar tinyint(3) unsigned default NULL,
> viewed tinyint(3) unsigned NOT NULL default '0',
> PRIMARY KEY (id)
> ) TYPE=MyISAM;
>
> I am creating the table in the EM. I am assuming that the numbers are the
> field length. For example:
> room tinyint(3) unsigned NOT NULL default '0'
> I am attempting to put 3 in the length but it will only use 1.
> I also am confused about unsigned. Also what is TYPE=MyISAM
> Can anyone give me a hand?
> Thanks
> Laura K
>|||Thanks but I don't want to do mySQL. The original program was written for a
mysql database but we want to run it from MS sql. I am writing the tables
by hand in ms sql but I do not know much about the differences.
Laura
"Groucho" <rog11228@.aol.com> wrote in message
news:eFKpYqleDHA.4024@.TK2MSFTNGP11.phx.gbl...
> "Laura K" wrote in message
> > Need a little help. I am a newbie with both databases (mysql and
mssql).
> .
> .
> Don't expect to much help in the way of MySql here.
> It's not considered a *real* rdbms in these parts:)
> There are many differences/similarities between the two.
> You'll get Mysql help on google at:
> mailing.database.mysql
> mailing.database.mysql-win32
> You can also let MySql utilities help you,especially the ones that
> import and export data.Check out:
> SQLyog @.
> www.webyog.com
> and
> MySql GUI tool @.
> www.mysqlgui.net
> Install the MySql odbc driver and you can use the
> Server dts utility to transfer data and see how the
> table structure(s) turn out.
> A type MyISAM is a particular type of MySql table.
> In MySql there are different types of tables you can use.
> This concept does not exist in Server.See the MySql
> docs for details.
> It also matters what version of MySql your working with.
> Check their website for details.(You should be using 4.0.xx).
> If you want subqueries,derived tables and some other
> fancy server like constructs check out 4.1 alpha.
> Using both Server and MySql will probably become
> pretty common as time goes on.You can pick the one
> you want to pay for:).
> RAC v2.2 and QALite for Sql Server released.
> www.rac4sql.net
>
>|||Thanks so much. I am beginning the understand the differences.
I did a search for conversion but all I got all kinds of non helpful things
instead from the search engines. I will take a look at books online.
Thanks again for the detailed help.
Laura
"Morgan" <mfears@.spamcop.net> wrote in message
news:OaAQiGmeDHA.3528@.tk2msftngp13.phx.gbl...
> Laura,
> Try this:
> CREATE TABLE room_action (
> id varchar(100) NOT NULL default '',
> room smallint NOT NULL default 0,
> userID smallint NOT NULL default 0, --user is a keyword, change to
> userID
> name varchar(50) NOT NULL default '',
> cmd varchar(10) NOT NULL default '',
> x integer default 0,
> y integer default 0,
> msg varchar(255) default NULL,
> avatar smallint default NULL, --use a default of 0 instead of null if
> possible, will prevent aggreatation warnings
> viewed smallint NOT NULL default 0,
> PRIMARY KEY (id)
> )
> Also, please consider removing the default '' on your not null columns. It
> is contradictory and a pain in the rear to maintain.
> Take some time to review Books On Line, especially data types, as many of
> your conversion questions will be answered.
> --Morgan
>
> "Laura K" <klkazanAT@.ATcharter.net> wrote in message
> news:%23Gz90CleDHA.1824@.TK2MSFTNGP10.phx.gbl...
> > Need a little help. I am a newbie with both databases (mysql and
mssql).
> I
> > am transferring from mysql to ms sql. There are very few tables and
data.
> > I am basically transferring structure by hand.
> >
> > I am finding the syntax to be difficult to deal with. I have a txt file
> > with several tables and their attributes from mysql but I am not finding
> the
> > exact same thing in ms sql. I wonder if someone can look at the code
and
> > give me a hand. I am using the EM to put this on ms sql online.
> >
> > Here is an example of one table and the trouble I am having with it.
This
> > is the mysql code to be changed. Please see my comments under this code.
> >
> > CREATE TABLE room_action (
> > id varchar(100) NOT NULL default '',
> > room tinyint(3) unsigned NOT NULL default '0',
> > user tinyint(3) unsigned NOT NULL default '0',
> > name varchar(50) NOT NULL default '',
> > cmd varchar(10) NOT NULL default '',
> > x mediumint(9) defaulnamet '0',
> > y mediumint(9) default '0',
> > msg varchar(255) default NULL,
> > avatar tinyint(3) unsigned default NULL,
> > viewed tinyint(3) unsigned NOT NULL default '0',
> > PRIMARY KEY (id)
> > ) TYPE=MyISAM;
> >
> >
> > I am creating the table in the EM. I am assuming that the numbers are
the
> > field length. For example:
> > room tinyint(3) unsigned NOT NULL default '0'
> >
> > I am attempting to put 3 in the length but it will only use 1.
> >
> > I also am confused about unsigned. Also what is TYPE=MyISAM
> >
> > Can anyone give me a hand?
> >
> > Thanks
> >
> > Laura K
> >
> >
>

Mysql on different servers

Is it possible to connect to different databases on different servers
so i can merge them as one.

I heard there is a tool that does this but still to come accross it
any help appreciated.[posted and mailed]

kev (bairdoid@.hotmail.com) writes:
> Is it possible to connect to different databases on different servers
> so i can merge them as one.
> I heard there is a tool that does this but still to come accross it
> any help appreciated.

In MS SQL Server you would set up a linked server and use four-part notation
for your queries:

SELECT * FROM SERVER.db.dbo.tbl

But what MySQL offers, I have no idea. You need to find a forum devoted
to MySQL.

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

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

my script not working when the name of a DB has - (the dash character)

Hi, this script uses MSforEachDB to check all the SPs in all the databases and look for a keyword LockCookie.

The script runs well except when there is a database with a dash - in ots name.
ie: When ? in the script is replaced by a database whose name contains a dash - the dash and the rest of the database name after the dash is ignored.

And I get the message for example:
Could not locate entry in sysdatabases for database 'SharePoint_AdminContent_f5c0f71f'. No entry found with that name. Make sure that the name is entered correctly.

Here is the script:
exec sp_MSforeachDB
'
use ?
select ''LockCookie'' as searchedTxt, o.name AS ProcName ,Len(SubString(object_definition(o.object_id),1, PatIndex(''%LockCookie%'', object_definition(o.object_id))))-Len(Replace(SubString(object_definition(o.object_id),1, PatIndex
(''%LockCookie%'', object_definition(o.object_id))),char(13),''''))+1 AS Line,
PatIndex(''%LockCookie%'', object_definition(o.object_id)) AS Position, ''?'' as dbName
from ?.sys.objects as o
where o.type=''P'' and object_definition(o.object_id) like ''%LockCookie%''
ORDER BY searchedTxt,ProcName, Line, position'

You can run it and if u have a DB named: sgfgdffgdfd-jjjjj-hhhhh for example you will see the error
How can I fix my script to consider databases with - as well in sys.objects

Thanks a lot for your help.

You might want to try enclosing the database name in square bracked so that

sgfgdffgdfd-jjjjj-hhhhh

becomes

[sgfgdffgdfd-jjjjj-hhhhh]

Dave

|||

yeah man. but what do i do in my script

the script is dynamic:

try jus this little script and it gives the error:

exec sp_MSforeachDB
'
use ?
select o.name,
''?'' as dbName
from ?.sys.objects as o
where o.type=''P'''

Thanks a lot

|||

I changed it to:

exec sp_MSforeachDB
'
use ?
select o.name,
''?'' as dbName
from [?].dbo.sysobjects as o
where o.type=''P'''

and it works fine against all of my databases -- even against my [test-hyphen] database.

Dave

|||

same problem man. did u try it?

thank thee

|||

I got the same error you did with your original query; this version eliminates the execution errors.

exec sp_MSforeachDB
'
use [?]
select ''LockCookie'' as searchedTxt, o.name AS ProcName ,Len(SubString(object_definition(o.object_id),1, PatIndex(''%LockCookie%'', object_definition(o.object_id))))-Len(Replace(SubString(object_definition(o.object_id),1, PatIndex
(''%LockCookie%'', object_definition(o.object_id))),char(13),''''))+1 AS Line,
PatIndex(''%LockCookie%'', object_definition(o.object_id)) AS Position, ''?'' as dbName
from [?].sys.objects as o
where o.type=''P'' and object_definition(o.object_id) like ''%LockCookie%''
ORDER BY searchedTxt,ProcName, Line, position'

Dave

|||

amazing what did u change amigo how many "?"

u re d man

|||never mind amigo thanks a lot

My MDF Files currupted

My company have server with loads of test databases. last saturday its
hard disk crashed all the partitions were lost . i tried to recover
data from that disk and found my database folder. I took backup on
another harddisk and tried to attache my databases but sql server says
it not avalid database file. i tried to repaire those files with
"recovery for sql server" but no use please help.
Tough thing...
If the database file is corrupt, and you cannot restore from a good backup,
reinstalling the software cannot help. also, re-attaching the database files
will not (cannot) solve the corruption.
Try =>
sp_attach_single_file_db
Attaches a database having only one data file to the current server.
if in suspect state => to reset the suspect status:
http://msdn.microsoft.com/library/de...tabse_494j.asp
once you can reaccess the db, try DBCC CHECKDB:
http://msdn.microsoft.com/library/de..._dbcc_00gy.asp
http://www.experts-exchange.com/Data..._21648997.html
Thanks,
Sree
"khandelwal.deep@.gmail.com" wrote:

> My company have server with loads of test databases. last saturday its
> hard disk crashed all the partitions were lost . i tried to recover
> data from that disk and found my database folder. I took backup on
> another harddisk and tried to attache my databases but sql server says
> it not avalid database file. i tried to repaire those files with
> "recovery for sql server" but no use please help.
>

My MDF Files currupted

My company have server with loads of test databases. last saturday its
hard disk crashed all the partitions were lost . i tried to recover
data from that disk and found my database folder. I took backup on
another harddisk and tried to attache my databases but sql server says
it not avalid database file. i tried to repaire those files with
"recovery for sql server" but no use please help.Tough thing...
If the database file is corrupt, and you cannot restore from a good backup,
reinstalling the software cannot help. also, re-attaching the database files
will not (cannot) solve the corruption.
Try =>
sp_attach_single_file_db
Attaches a database having only one data file to the current server.
if in suspect state => to reset the suspect status:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_494j.asp
once you can reaccess the db, try DBCC CHECKDB:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_00gy.asp
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21648997.html
Thanks,
Sree
"khandelwal.deep@.gmail.com" wrote:
> My company have server with loads of test databases. last saturday its
> hard disk crashed all the partitions were lost . i tried to recover
> data from that disk and found my database folder. I took backup on
> another harddisk and tried to attache my databases but sql server says
> it not avalid database file. i tried to repaire those files with
> "recovery for sql server" but no use please help.
>

My MDF Files currupted

My company have server with loads of test databases. last saturday its
hard disk crashed all the partitions were lost . i tried to recover
data from that disk and found my database folder. I took backup on
another harddisk and tried to attache my databases but sql server says
it not avalid database file. i tried to repaire those files with
"recovery for sql server" but no use please help.Tough thing...
If the database file is corrupt, and you cannot restore from a good backup,
reinstalling the software cannot help. also, re-attaching the database files
will not (cannot) solve the corruption.
Try =>
sp_attach_single_file_db
Attaches a database having only one data file to the current server.
if in suspect state => to reset the suspect status:
tabse_494j.asp" target="_blank">http://msdn.microsoft.com/library/d...
tabse_494j.asp
once you can reaccess the db, try DBCC CHECKDB:
0gy.asp" target="_blank">http://msdn.microsoft.com/library/d...
0gy.asp
[url]http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21648997.html[/ur
l]
Thanks,
Sree
"khandelwal.deep@.gmail.com" wrote:

> My company have server with loads of test databases. last saturday its
> hard disk crashed all the partitions were lost . i tried to recover
> data from that disk and found my database folder. I took backup on
> another harddisk and tried to attache my databases but sql server says
> it not avalid database file. i tried to repaire those files with
> "recovery for sql server" but no use please help.
>