Easy way to view DB

Hi,
I have several DB with 80+ tables in each. I would like to find out what is
the best method to learn and view the relationships of these tables. I made
a SQL diagram of all the tables but since there are so many tables, it is
not easy to view the 'big picture'. Does anyone have any suggestions or may
be a tool that shows DBs in a better view?
Thank you.Greg has a nice product that would serve you well here.
http://www.ag-software.com/ags_scribe_index.aspx
--
-oj
RAC & QALite!
http://www.rac4sql.net
"Dragon" <NoSpam_Baadil@.hotmail.com> wrote in message
news:%23doHPiayDHA.3772@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I have several DB with 80+ tables in each. I would like to find out what
is
> the best method to learn and view the relationships of these tables. I
made
> a SQL diagram of all the tables but since there are so many tables, it is
> not easy to view the 'big picture'. Does anyone have any suggestions or
may
> be a tool that shows DBs in a better view?
> Thank you.
>

Easy Question on inequality

:rolleyes:

In T-SQL I could use <> or != to do an inequality comparison.

However which method would be most suited?

I and my co-workers use C# so I think I'll use !=. :cool:

This fits in with our dev language pilosophy.

Are there any reasons why I shouldn't?

Opinions welcome.

Shaun McGuile
"...you think the worms of Dune are big, wait till you see the sparrows!"Afaik there is absolutely no difference. As you, i strongly preffer using !=|||Old style syntax such as "!=" is supported by MSSQL, but not encouraged.
The familiar "<>" is more universally recognized.|||I find it strange that its old style

I would have said that <> is BASIC programming style

and

!= is more C# and Javascript style.

(from my perspective more modern).

Regards

(3 Card Brag - Rules - you can't see a blindman!). :cool:|||I think what the BM is saying is that one is MS specific and the other is in the ANSI-92 specification. If you ever need to port your app to another DB platform the less MS specific code you have the better off you will be.

who cares what is more in line with app code?

BTW, you spell sean the wrong way.|||He is using the old Ansii-standard "Shaun", while you are using the MS-specific "Sean".|||BTW, you spell sean the wrong way.

Hmm try telling that to my Welsh co-worker Sion. :D|||Or my sister-in-law, Cion

Easy question

Hi,

I have a query that I need to pass as a string as the second parameter of the OpenQuery method. Here's my query:

SELECT * FROM mytable WHERE last_name = 'DOE'

Thing is that a string is set with apostrophies, so I don't know how to set my string since apostrophies are alse in my query. Normally, it would look like this:

DECLARE @.CQUERY VARCHAR(100)
SET @.CQUERY = 'SELECT * FROM mytable WHERE last_name = 'DOE''

But of course this fails. How can I do it then?

Thanks,

Skip.I always do it thid way

DECLARE @.CQUERY VARCHAR(100)
SET @.CQUERY = 'SELECT * FROM mytable WHERE last_name = '+ '''' + 'DOE' + ''''
SELECT @.cquery

Only cause it's easier for me to read.....|||SET @.CQUERY = "SELECT * FROM mytable WHERE last_name = 'DOE'"|||What's the order of the characters (I can't see them correctly with these fonts)?

Is it 2 double quotes, 3 apostrophies, etc.

Thanks again,

Skip|||I do

1 quote text message 1 quoye + 4 quotes + 1 qoute value 1 quote + 4 quotes...

but you should be able to cut and paste the code into QA...|||OK, I've tried a couple of solutions and I can see that something like this works fine:

DECLARE @.CQUERY VARCHAR(100)
SET @.CQUERY = 'SELECT * FROM mytable WHERE last_name = ' + '''' + 'DOE' + ''''

Here, '''' = 4 apostrophies.

Now, I find it wierd that this worked because shoudn't 4 apostrophies open-close empty strings twice (hence creating an error because the + sign is not between them)?

Is this a special case programmed for text appending in SQL Server?

Thanks,

Skip.

Easy method of Forms Authentication

Does anybody know if it is possible to create an ASP.NET webform (Form1)
that simply recieves a request from a client browser, and then sends that
request (changing a few bits as necessary) on to another page (Form2). The
response from Form2 will then be digested by Form1 and returned to client
browser.
I know that Server.Transfer has limitations, in that it only works between
ASPX forms, but this would be a great way of getting around all of the
Authentication problems in Report Services and sounds really easy.
You would just have to add some credentials, parameters if required and hey
presto, your in.
There may be a bit of a complicated conversation on the way through - but
this cant be as difficult as all the stuff that is going on here for
integeration.
Please somebody either add there support for this issue, or answer it as it
would solve loads of peoples issues.
Thanks in advance.
--
Message posted via http://www.sqlmonster.comThe Forms Authentication sample code provided by Microsoft is about as easy
as it can get for integrating with Reporting Services. We have used this as
a starting point to integrate with our own ASP.NET applications and
single-sign-on solution. If you have questions about this, please feel free
to post back.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Tom Robson via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:47c0624d6e254541b1877708761a9ea9@.SQLMonster.com...
> Does anybody know if it is possible to create an ASP.NET webform (Form1)
> that simply recieves a request from a client browser, and then sends that
> request (changing a few bits as necessary) on to another page (Form2). The
> response from Form2 will then be digested by Form1 and returned to client
> browser.
> I know that Server.Transfer has limitations, in that it only works between
> ASPX forms, but this would be a great way of getting around all of the
> Authentication problems in Report Services and sounds really easy.
> You would just have to add some credentials, parameters if required and
> hey
> presto, your in.
> There may be a bit of a complicated conversation on the way through - but
> this cant be as difficult as all the stuff that is going on here for
> integeration.
> Please somebody either add there support for this issue, or answer it as
> it
> would solve loads of peoples issues.
> Thanks in advance.
> --
> Message posted via http://www.sqlmonster.com

Easiest method for moving databases from one partition to another

The first SQL install went on the smaller partition and we need to
move it from C: to D:. It seems to me there should be a startup
parameter we can change, stop the server, move the databases, and
restart the server. Our DB admin is saying we need a full reinstall.
It seems to me this should be easiert. I am seeing pathing
information in the database parameters in the server properties tabs
in enterprize manager. Can I just change those, do my move, and
restart?
suggestions greatly appreciated
Hal
Hi
If you want to move the databases, look at sp_attachdb and sp_detachdb in BOL.
No need for re-install. (The location of master DB is in the registry so
moving that takes a bit more effort).
If you want to move the SQL EXE's, then un-install and re-install is required.
Regards
Mike
"hal@.nospam.com" wrote:

> The first SQL install went on the smaller partition and we need to
> move it from C: to D:. It seems to me there should be a startup
> parameter we can change, stop the server, move the databases, and
> restart the server. Our DB admin is saying we need a full reinstall.
> It seems to me this should be easiert. I am seeing pathing
> information in the database parameters in the server properties tabs
> in enterprize manager. Can I just change those, do my move, and
> restart?
> suggestions greatly appreciated
> Hal
>
|||If you want to move the master database, you can add some options to the
sqlservr.exe program that is started as a service, you should use something
like
sqlservr -d<new masterdatafilepath> -l<new master log path>
Marc
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:21A185C6-89AD-4DC6-901F-B770BD9CA72D@.microsoft.com...
> Hi
> If you want to move the databases, look at sp_attachdb and sp_detachdb in
BOL.
> No need for re-install. (The location of master DB is in the registry so
> moving that takes a bit more effort).
> If you want to move the SQL EXE's, then un-install and re-install is
required.[vbcol=seagreen]
> Regards
> Mike
> "hal@.nospam.com" wrote:

Easiest method for moving databases from one partition to another

The first SQL install went on the smaller partition and we need to
move it from C: to D:. It seems to me there should be a startup
parameter we can change, stop the server, move the databases, and
restart the server. Our DB admin is saying we need a full reinstall.
It seems to me this should be easiert. I am seeing pathing
information in the database parameters in the server properties tabs
in enterprize manager. Can I just change those, do my move, and
restart?
suggestions greatly appreciated
HalHi
If you want to move the databases, look at sp_attachdb and sp_detachdb in BO
L.
No need for re-install. (The location of master DB is in the registry so
moving that takes a bit more effort).
If you want to move the SQL EXE's, then un-install and re-install is require
d.
Regards
Mike
"hal@.nospam.com" wrote:

> The first SQL install went on the smaller partition and we need to
> move it from C: to D:. It seems to me there should be a startup
> parameter we can change, stop the server, move the databases, and
> restart the server. Our DB admin is saying we need a full reinstall.
> It seems to me this should be easiert. I am seeing pathing
> information in the database parameters in the server properties tabs
> in enterprize manager. Can I just change those, do my move, and
> restart?
> suggestions greatly appreciated
> Hal
>|||If you want to move the master database, you can add some options to the
sqlservr.exe program that is started as a service, you should use something
like
sqlservr -d<new masterdatafilepath> -l<new master log path>
Marc
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:21A185C6-89AD-4DC6-901F-B770BD9CA72D@.microsoft.com...
> Hi
> If you want to move the databases, look at sp_attachdb and sp_detachdb in
BOL.
> No need for re-install. (The location of master DB is in the registry so
> moving that takes a bit more effort).
> If you want to move the SQL EXE's, then un-install and re-install is
required.[vbcol=seagreen]
> Regards
> Mike
> "hal@.nospam.com" wrote:
>sql

Easiest method for moving databases from one partition to another

The first SQL install went on the smaller partition and we need to
move it from C: to D:. It seems to me there should be a startup
parameter we can change, stop the server, move the databases, and
restart the server. Our DB admin is saying we need a full reinstall.
It seems to me this should be easiert. I am seeing pathing
information in the database parameters in the server properties tabs
in enterprize manager. Can I just change those, do my move, and
restart?
suggestions greatly appreciated
HalHi
If you want to move the databases, look at sp_attachdb and sp_detachdb in BOL.
No need for re-install. (The location of master DB is in the registry so
moving that takes a bit more effort).
If you want to move the SQL EXE's, then un-install and re-install is required.
Regards
Mike
"hal@.nospam.com" wrote:
> The first SQL install went on the smaller partition and we need to
> move it from C: to D:. It seems to me there should be a startup
> parameter we can change, stop the server, move the databases, and
> restart the server. Our DB admin is saying we need a full reinstall.
> It seems to me this should be easiert. I am seeing pathing
> information in the database parameters in the server properties tabs
> in enterprize manager. Can I just change those, do my move, and
> restart?
> suggestions greatly appreciated
> Hal
>|||If you want to move the master database, you can add some options to the
sqlservr.exe program that is started as a service, you should use something
like
sqlservr -d<new masterdatafilepath> -l<new master log path>
Marc
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:21A185C6-89AD-4DC6-901F-B770BD9CA72D@.microsoft.com...
> Hi
> If you want to move the databases, look at sp_attachdb and sp_detachdb in
BOL.
> No need for re-install. (The location of master DB is in the registry so
> moving that takes a bit more effort).
> If you want to move the SQL EXE's, then un-install and re-install is
required.
> Regards
> Mike
> "hal@.nospam.com" wrote:
> > The first SQL install went on the smaller partition and we need to
> > move it from C: to D:. It seems to me there should be a startup
> > parameter we can change, stop the server, move the databases, and
> > restart the server. Our DB admin is saying we need a full reinstall.
> > It seems to me this should be easiert. I am seeing pathing
> > information in the database parameters in the server properties tabs
> > in enterprize manager. Can I just change those, do my move, and
> > restart?
> >
> > suggestions greatly appreciated
> >
> > Hal
> >

Dynamically Generating SMDL (Report Model Definitions) With C#

My problem is this: How do I dynamically generate a Report Model Definition with c#?

Is there some sort of method I could call from the ReportingService2005 web service? Or some sort of APIs I could use?

If I didn't have a dynamic database structure, I would just create a Report Model Definition with BIS and then deploy the same model to each customer. However, our product creates additional tables in the database, depending on what data users wish to collect.

There are currently 2 solutions for this problem. First, I can manually create a Report Model Definition through the Buisness Intelligence Studio (BIS). However, I wish to be able to dynamically generate the report model without having to go through BIS. Second, I could use C# and manually write the XML of the SMDL. However this seems problematic.

I'm really hoping for some MS API that I'm missing out on here. Thanks for the help.

- Sean

I asked MS about this and they said they currently do not have any APIs to accomplish this at this time.

Still searching for ideas.

|||

I was also looking for similar functionality. Let me know in case you find a solution.

Thanks,

-Suri

|||

Suri,

I was unable to find an API or anything definitive to fix this. However, what I am doing is creating a class to populate with information from my database and then serialize it into XML to insert into the SMDL.

I'm actually just designing the class right now. I'd recommend this page in MSDN

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

I hope this helps.

|||

There is API method GenerateModel

|||

Excellent.

You saved me a lot of time. I'll use the similiar RegenerateModel method:

//Get the webservice
wsRS2005.ReportingService2005 r = new TNTSMDL.wsRS2005.ReportingService2005();

//Set the Credentials
r.Credentials = System.Net.CredentialCache.DefaultCredentials;

//Regenerate the Model in the database
r.RegenerateModel(@."/Models/NewModel");

POOF. It is generated. Thanks Again!

|||is there a sample available?

Dynamically Generating SMDL (Report Model Definitions) With C#

My problem is this: How do I dynamically generate a Report Model Definition with c#?

Is there some sort of method I could call from the ReportingService2005 web service? Or some sort of APIs I could use?

If I didn't have a dynamic database structure, I would just create a Report Model Definition with BIS and then deploy the same model to each customer. However, our product creates additional tables in the database, depending on what data users wish to collect.

There are currently 2 solutions for this problem. First, I can manually create a Report Model Definition through the Buisness Intelligence Studio (BIS). However, I wish to be able to dynamically generate the report model without having to go through BIS. Second, I could use C# and manually write the XML of the SMDL. However this seems problematic.

I'm really hoping for some MS API that I'm missing out on here. Thanks for the help.

- Sean

I asked MS about this and they said they currently do not have any APIs to accomplish this at this time.

Still searching for ideas.

|||

I was also looking for similar functionality. Let me know in case you find a solution.

Thanks,

-Suri

|||

Suri,

I was unable to find an API or anything definitive to fix this. However, what I am doing is creating a class to populate with information from my database and then serialize it into XML to insert into the SMDL.

I'm actually just designing the class right now. I'd recommend this page in MSDN

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

I hope this helps.

|||

There is API method GenerateModel

|||

Excellent.

You saved me a lot of time. I'll use the similiar RegenerateModel method:

//Get the webservice
wsRS2005.ReportingService2005 r = new TNTSMDL.wsRS2005.ReportingService2005();

//Set the Credentials
r.Credentials = System.Net.CredentialCache.DefaultCredentials;

//Regenerate the Model in the database
r.RegenerateModel(@."/Models/NewModel");

POOF. It is generated. Thanks Again!

|||is there a sample available?|||

Hello Sean,

Can you post the complete code for "RegenerateModel" method?

It would save lot of development time.

Thanks

Dynamically Generating SMDL (Report Model Definitions) With C#

My problem is this: How do I dynamically generate a Report Model Definition with c#?

Is there some sort of method I could call from the ReportingService2005 web service? Or some sort of APIs I could use?

If I didn't have a dynamic database structure, I would just create a Report Model Definition with BIS and then deploy the same model to each customer. However, our product creates additional tables in the database, depending on what data users wish to collect.

There are currently 2 solutions for this problem. First, I can manually create a Report Model Definition through the Buisness Intelligence Studio (BIS). However, I wish to be able to dynamically generate the report model without having to go through BIS. Second, I could use C# and manually write the XML of the SMDL. However this seems problematic.

I'm really hoping for some MS API that I'm missing out on here. Thanks for the help.

- Sean

I asked MS about this and they said they currently do not have any APIs to accomplish this at this time.

Still searching for ideas.

|||

I was also looking for similar functionality. Let me know in case you find a solution.

Thanks,

-Suri

|||

Suri,

I was unable to find an API or anything definitive to fix this. However, what I am doing is creating a class to populate with information from my database and then serialize it into XML to insert into the SMDL.

I'm actually just designing the class right now. I'd recommend this page in MSDN

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

I hope this helps.

|||

There is API method GenerateModel

|||

Excellent.

You saved me a lot of time. I'll use the similiar RegenerateModel method:

//Get the webservice
wsRS2005.ReportingService2005 r = new TNTSMDL.wsRS2005.ReportingService2005();

//Set the Credentials
r.Credentials = System.Net.CredentialCache.DefaultCredentials;

//Regenerate the Model in the database
r.RegenerateModel(@."/Models/NewModel");

POOF. It is generated. Thanks Again!

|||is there a sample available?|||

Hello Sean,

Can you post the complete code for "RegenerateModel" method?

It would save lot of development time.

Thanks

Dynamically creating SSIS package for each flat file

Trying to figure out the best method of reading in a number of flat files, all with different number of columns and data types and outputting them to a database.

Here's the problem: They are EBCDIC encoded and some of the columns are packed decimal. I've set up one package that takes the flat file, unpacks the decimal (Using UnpackDecimal component) and then sending the rest through a second component to go from EBCDIC -> ASCII.

What I need is a way to do this for every flat file based on the schema for that flat file. One current solution is to write a script/app to create the .dtsx XML file and then execute that for each flat file. It appears like this may be possible, but I haven't gotten far enough to know for sure. So my questions are this:

1) Is there an easier way to do this (ie somehow feed the schema to the package and use it to dynamically set up the column makers and determine which columns get fed to the unpack decimal component.

2) If there isn't a better way, will dynamically creating the .dtsx XML file based on the necessary input/output columns for each flat file work? If so, what is a good source of information on this (information about how the .dtsx XML file is set up, what needs to be changed/what doesn't, etc).

Thanks,

Travis

Trav2003 wrote:

1) Is there an easier way to do this (ie somehow feed the schema to the package and use it to dynamically set up the column makers and determine which columns get fed to the unpack decimal component.

2) If there isn't a better way, will dynamically creating the .dtsx XML file based on the necessary input/output columns for each flat file work? If so, what is a good source of information on this (information about how the .dtsx XML file is set up, what needs to be changed/what doesn't, etc).

Thanks,

Travis

1) No. SSIS can't handle dynamic columns. The best it can do it dynamically create a child package, which is no different than #2.
2) Yes, it will work. You probably don't want to create XML directly, but instead use the API to generate the package. The updated samples contain one showing how to create a basic package. You may also find this tool helpful for reverse engineering packages.
|||you might also check out http://www.aminosoftware.com they have a custom source component that will read in many forms of ebcdic (including packed, zoned, etc) and output it into ASCII with only a single pass through the data file.

dynamically columns

Hi guys,
Is there any method to add/delete dynamically columns in a table, not in a
matrix?
thanks,
RaduYou can not really Dynamically add columns to a table..
But you can pre-create a number of extra columns, show/hide and assign their
values on the fly...
That's about the best we can do now.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Radu" <Radu@.discussions.microsoft.com> wrote in message
news:FEA44080-476E-46AC-B2AC-7599FB6A6C06@.microsoft.com...
> Hi guys,
> Is there any method to add/delete dynamically columns in a table, not in a
> matrix?
> thanks,
> Radu|||Just another thought... ONe of the things we do that 'sort of' simluates
dynamically adding columns is that we will create a single column, then
populate it with varying multiple pieces of information concatenated
together as a single sql column...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Radu" <Radu@.discussions.microsoft.com> wrote in message
news:FEA44080-476E-46AC-B2AC-7599FB6A6C06@.microsoft.com...
> Hi guys,
> Is there any method to add/delete dynamically columns in a table, not in a
> matrix?
> thanks,
> Radu|||You could build up the XML for the report programmatically. This would allow
you complete control over what columns get placed in your table. A possible
disadvantage is that you would have to deploy the report programmatically
once it had been built up.|||snyder,
i want to hide a whole column in a table if there is no data to be
displayed in the entire column, if there exists atleast one record in the
column then we need to show the column else hide it.
"Wayne Snyder" wrote:
> You can not really Dynamically add columns to a table..
> But you can pre-create a number of extra columns, show/hide and assign their
> values on the fly...
> That's about the best we can do now.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Radu" <Radu@.discussions.microsoft.com> wrote in message
> news:FEA44080-476E-46AC-B2AC-7599FB6A6C06@.microsoft.com...
> > Hi guys,
> >
> > Is there any method to add/delete dynamically columns in a table, not in a
> > matrix?
> >
> > thanks,
> > Radu
>
>|||how do you create a single column in the matrix control and then populate it
with multiple pieces of information
I amtrying to add a Percent Change Col to Pivoted colmns in Matrix
"Wayne Snyder" wrote:
> Just another thought... ONe of the things we do that 'sort of' simluates
> dynamically adding columns is that we will create a single column, then
> populate it with varying multiple pieces of information concatenated
> together as a single sql column...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Radu" <Radu@.discussions.microsoft.com> wrote in message
> news:FEA44080-476E-46AC-B2AC-7599FB6A6C06@.microsoft.com...
> > Hi guys,
> >
> > Is there any method to add/delete dynamically columns in a table, not in a
> > matrix?
> >
> > thanks,
> > Radu
>
>