Database Discussion Boards

Re: String Comparison in a Sql Statement

Paddy Boyd27-Jul-06 5:20

27-Jul-06 5:20

I'm not entirely sure what you want, is it something like this:

For table:

Alice
Bob
Charlie
Dave

Pass in 'B' and 'D', do you want to get everybody but Alice?

Re: String Comparison in a Sql Statement

TheJudeDude27-Jul-06 6:30

TheJudeDude

27-Jul-06 6:30

Correct

Jude

Re: String Comparison in a Sql Statement

Mike Dimmick28-Jul-06 1:50

Mike Dimmick

28-Jul-06 1:50

I suspect operator precedence is biting you. Put brackets around the 'OR' part, or use 'IN':

WHERE (CustType = 1 OR CustType = 6)
 AND CustName >= @BNAME AND CustName <= @ENAME

WHERE CustType IN ( 1, 6 )
 AND CustName >= @BNAME AND CustName <= @ENAME

Stability. What an interesting concept. -- Chris Maunder

Re: String Comparison in a Sql Statement

TheJudeDude28-Jul-06 18:34

TheJudeDude

28-Jul-06 18:34

That was it! Thanx alot!

Jude

Re: String Comparison in a Sql Statement

TheJudeDude28-Jul-06 22:39

TheJudeDude

28-Jul-06 22:39

Well, it works...to a point. Let's say that ENAME = "z" or "Z", "Zelda" is not included, but it is not included when ENAME = "ZZZZZZ" or "zzzzzz". That boggles my mind

Jude

Need to programmatically discover name of relationship table

LongRange.Shooter27-Jul-06 3:53

LongRange.Shooter

27-Jul-06 3:53

We are just starting the use of BindingSource between two datagrids so that when a record is selected in table A, the detail is shown from table B. Works just great!!

The problem is that our dba runs mini-builds that reconstruct our schema every week in all environments except for production. (through a tool called Erwin(?) ) This, of course, immediately breaks our BindingSource since all of the relationship tables are renamed.

How do I programmatically discover the name of the relationship table that binds tableA to tableB??

Any info -- even an article -- would be a life saver.

Thanks.

Re: Need to programmatically discover name of relationship table

Rob Graham27-Jul-06 5:06

Rob Graham

27-Jul-06 5:06

Shoot the dba.

We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest.
Patricia Aburdene

Re: Need to programmatically discover name of relationship table

Paddy Boyd27-Jul-06 5:11

Paddy Boyd

27-Jul-06 5:11

I agree. With a small caliber gun, starting at the feet and working your way up.

Re: Need to programmatically discover name of relationship table

Paddy Boyd27-Jul-06 5:17

Paddy Boyd

27-Jul-06 5:17

But more seriously:

SELECT so.name AS 'Constraint Name', so1.name AS 'Parent Table'
,so2.name AS 'Child Table'
FROM sysobjects so
INNER JOIN sysforeignkeys sf
ON so.id = sf.constid
INNER JOIN sysobjects so1
ON so1.id = sf.rkeyid
INNER JOIN sysobjects so2
ON so2.id = sf.fkeyid
WHERE so1.name = 'MyTableName'

This returns all the foreign keys for a particular table, you could probably use this.

Re: Need to programmatically discover name of relationship table

LongRange.Shooter27-Jul-06 7:11

LongRange.Shooter

27-Jul-06 7:11

Thanks for the feedback.

While waiting for an answer we dug into our strong-typed dataset and found that we could implement the following code as well:

public static string GetRelationshipName( string ParentTable, string ChildTable )
{
    foreach ( System.Data.DataRelation relationship in dataset.Relations)
    {
        if ( relationship.ParentTable.TableName == ParentTable
            && relationship.ChildTable.TableName == ChildTable )
            return relationship.RelationName;
    }
    return string.Empty;
}

OSQL (ODBC) taking longer than (ISQL) DB Library

benylegg227-Jul-06 2:56

benylegg2

27-Jul-06 2:56

Hello All,

Does anyone know of a reason that why the same statement would take longer using OSQL versus using ISQL/ISQLW? The time difference I am seeing is pretty significant; in the order of seconds versus minutes.

I am aware of the different connection libraries that they use (hence; my application based on ODBC is taking a long time to execute as well!!).

Any ideas?

Thanks in advance.

Ben

Re: OSQL (ODBC) taking longer than (ISQL) DB Library

Mike Dimmick28-Jul-06 1:53

Mike Dimmick

28-Jul-06 1:53

The two libraries have different defaults for the SET options, perhaps the value of an option is causing an index to be rejected?

I thought Query Analyzer (ISQLW.EXE) used ODBC but I could be mistaken.

Stability. What an interesting concept. -- Chris Maunder

sql reporting services connection error

TintinV3ck27-Jul-06 2:42

TintinV3ck

27-Jul-06 2:42

Hi
We are running windows server 2003 with SQL 2005 and SQL Reporting Services 2005 - > It worked fine until we then installed SharePoint Portal Services 2003.

It gives the following error when trying to connect

http://localhost/reportserver. ->Unable to connect to database. Check database connection information and make sure the database server is running.

As far as we know the error started occuring after we installed sharepoint portal services.

Any assistance would be appreciated.

Regards,

Tintin

Code to find sql server vulnerabilties

sfernandes27-Jul-06 0:57

sfernandes

27-Jul-06 0:57

Hi,
I want the source code to check password,dos etc vulnerabilties in sql server.

Thanks

Re: Code to find sql server vulnerabilties

LongRange.Shooter27-Jul-06 3:58

LongRange.Shooter

27-Jul-06 3:58

I am amazed at all of the amateurs like you that come here to codeproject expecting us to do your freaking job! We are professional developers that volunteer our free time to help other developers. That means that a developer (which you are not) would write code and then ask for help when they get in trouble.

Secondly, what you are asking for is basically source code to build a tool to attack SQL Servers. No way would anyone with a brain provide someone that kind of source code in a forum like this.

Asking user for Database name

Thanat0s426-Jul-06 18:35

Thanat0s4

26-Jul-06 18:35

Hello there, hope you're all well. I am working on this project using ADO .NET. I'm suppose to prompt the user for the database name, table name, and record values, etc... I have everything working except getting the value for the database name and setting it in the connString.

Console.WriteLine(@"Please enter database name.");
string line = Console.ReadLine();
Regex r = new Regex("[ ]+");
string[]inputs = r.Split(line);

string connString = @"
server = .\sqlexpress;
integrated security = true;
database = inputs[0];
MultipleActiveResultSets = true;
";

Is this suppose to be right ? The program compiles but after inputting the database name, the program throws an exception which I catch and let the user know that is a bad database name. Any suggestions ? Any help will be greatly appreciated. Thank you in advance.

The key to inmortality is to live a life worth remembering!

Re: Asking user for Database name

Colin Angus Mackay27-Jul-06 2:18

Colin Angus Mackay

27-Jul-06 2:18

Thanat0s4 wrote:
Is this suppose to be right ?

Nope.

You have specified a variable name inside a string and somehow expect the system to expand that out into the contents of the variable. That isn't going to happen because it doesn't know that's what you want to do. It has just been told to assign a string to a value. Nothing more.

string connString = @"
server = .\sqlexpress;
integrated security = true;
database = "+inputs[0]+";
MultipleActiveResultSets = true;
";

Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

SQL Server, Performance and Implementation

clint198226-Jul-06 11:10

clint1982

26-Jul-06 11:10

I have lots of files and for each file I will have a lot of strings that I have to save, about 1000000 strings. I want somehow to be able to save the information of each file. To be able to search for a string in the table, and to get the information of the row. The problem is that I am afraid that it will have problems making queries on very large tables. The number of records in this table can arrive easyly to 100 million. Is there anyway, to configure SQLServer to handle large tables or any other way. Any suggestion could be great since I don't have too much experience with the abilities of SQL Server.

Thanks,
Clint

Re: SQL Server, Performance and Implementation

Mike Dimmick27-Jul-06 1:02

Mike Dimmick

27-Jul-06 1:02

SQL Server can easily handle hundreds of millions of rows in one table. I have a table here with 25 million rows in it. The key to SQL Server performance is in ensuring that you have suitable indexes on the tables and ensuring that you write your queries in such a way that the indexes get used.

Stability. What an interesting concept. -- Chris Maunder

SP returns multiple results, can't get them all into datatables

eappell26-Jul-06 5:11

eappell

26-Jul-06 5:11

I've got a stored procedure that calls other stored procedures so that I end up with 8 sets of results when it is run. I'm trying to figure out how to get all 8 of those result sets into one dataset, and 8 seperate datatables within the dataset. I've got code that does run the sp and I'm attempting to put the results into seperate tables in the dataset, but I end up with the same results in all 8 datatables. Here's my code (just trying to get first four results here to test):

The code from my data class:

  public static DataSet getDataSet(string xref)<br />
  {<br />
    SqlConnection con = new SqlConnection(getConnectionString());<br />
<br />
    try<br />
    {<br />
      SqlCommand kpfCmd = new SqlCommand(kpfSP, con);<br />
      kpfCmd.CommandType = CommandType.StoredProcedure;<br />
<br />
      // send input parameters to the stored procedure<br />
      SqlParameter paramXREF = kpfCmd.Parameters.Add("@XREF", SqlDbType.Int);<br />
      paramXREF.Direction = ParameterDirection.Input;<br />
      paramXREF.Value = xref;<br />
<br />
      SqlParameter paramIP = kpfCmd.Parameters.Add("@ADDR", SqlDbType.VarChar);<br />
      paramIP.Direction = ParameterDirection.Input;<br />
      paramIP.Value = null;<br />
<br />
      SqlParameter paramNetname = kpfCmd.Parameters.Add("@NetName", SqlDbType.VarChar);<br />
      paramNetname.Direction = ParameterDirection.Input;<br />
      paramNetname.Value = "eappell";<br />
<br />
      SqlDataAdapter ad = new SqlDataAdapter(kpfCmd);<br />
      DataSet ds = new DataSet();<br />
<br />
      ad.Fill(ds, "Auth");<br />
      ad.Fill(ds, "Results2");<br />
      ad.Fill(ds, "Results3");<br />
      ad.Fill(ds, "Results4");<br />
      return ds;<br />
    }<br />
    finally<br />
    {<br />
      con.Close();<br />
    }<br />
  }<br />

Here's my code-behind for the page:

  protected void Page_Load(object sender, EventArgs e)<br />
  {<br />
    DataSet ds = KPFData.getDataSet("602");<br />
    GridView1.DataSource = ds.Tables["Results2"];<br />
    GridView1.DataBind();<br />
<br />
    GridView2.DataSource = ds.Tables["Results3"];<br />
    GridView2.DataBind();<br />
<br />
    GridView3.DataSource = ds.Tables["Results4"];<br />
    GridView3.DataBind();<br />
  }<br />

I've been working on this for a few days, and have posted messages on a couple other message boards but no one seems to know the answer to this one. I'm really hoping code project users are the key... Please help!

Thanks,

eddie

Re: SP returns multiple results, can't get them all into datatables

Rob Graham26-Jul-06 7:19

Rob Graham

26-Jul-06 7:19

from the help:

If the DataAdapter encounters multiple result sets, it creates multiple tables in the DataSet.
 The tables are given an incremental default name of TableN, starting with "Table" for Table0.
 If a table name is passed as an argument to the Fill method,
 the tables are given an incremental default name of TableNameN, starting with "TableName" for TableName0.

It would seem to me that you only need one ad.Fill call, say ad.Fill(ds,"Results");
Then bind to the tables like so:

 GridView1.DataSource = ds.Tables["Results"];<br />
GridView1.DataBind();<br />
<br />
GridView2.DataSource = ds.Tables["Results1"];<br />
GridView2.DataBind();<br />
<br />
GridView3.DataSource = ds.Tables["Results2"];<br />
GridView3.DataBind();

How : automatted .rdl uploaded

karthic_mohan26-Jul-06 2:27

karthic_mohan

26-Jul-06 2:27

i have created .rdl file programmaticaly in c#.
and i want to upload the rdl file to the location in report server progrmatically so that the report automatically reflects. hence i dont need to upload the rdl manually. has any one tried this?
below details i Need:
1.how this rdl file saved in report server?
2.how i have to save the rdl file programmatically?

SqlServer evaluation period expired

lehya26-Jul-06 2:22

lehya

26-Jul-06 2:22

Hello friends..
Is there any other alternative to start SqlServer..I have MS Sql Server 2000 installed on a XP machine. I downloaded it from someone's cd..now it says the time is expired..is there anyway to make it work once again..

Re: SqlServer evaluation period expired

Rob Graham26-Jul-06 2:49

Rob Graham

26-Jul-06 2:49

You installed from a trial CD. The trial has expired, so you will need to install a licensed version. One option may be to uninstall what is there now (databases you have created will be left behind), then install MSDE 2000 which is available free (seethis[^] )
then attach your existing databases to the new MSDE server.

Re: SqlServer evaluation period expired

Jerry Hammond26-Jul-06 5:02

Jerry Hammond

26-Jul-06 5:02

Forget MSDE. Go with SQL Server 2005 Exress. You'll be saving yourself a whole bunch of grief down the road if you go with SQL Server 2005 Express now.

http://msdn.microsoft.com/sql/express/default.aspx[^]

Jerry

"I'm happier than a tornado in a trailer park"--Cars

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.