|
BEGIN TRANSACTION
IF NOT EXISTS (SELECT * FROM SomeTable WHERE SomeColumn = @SomeValue)
BEGIN
INSERT ....
END
END TRANSACTION
|
|
|
|
|
Is there anyway to avoid two passes over the table ?
|
|
|
|
|
Yes, but you'll have to catch the exception. The overhead for the exception is probably greater than the overhead for passing over the table twice in a single operation.
|
|
|
|
|
Are you doing from Sql Server itself orfrom a client application? If it's the case wich is the programming language you are using?
Marc.
... she said you are the perfect stranger she said baby let's keep it like this... Dire Straits
|
|
|
|
|
I have a sql statement were I am trying to do string comparison by passing a value.
SELECT CustName, CustAdd, CustCity, CustState, CustZip
FROM CustomerList
WHERE CustType = 1 OR CustType = 6 AND CustName >= @BNAME AND CustName <= @ENAME ORDER BY CustName
When the value passed is "z" and "z" it returns much more than that. Why is that so? Is it because the name is longer than "z"? How could I write this statement to work like I want it?
Thanx in advance
Jude
|
|
|
|
|
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?
|
|
|
|
|
|
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
|
|
|
|
|
That was it! Thanx alot!
Jude
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
I agree. With a small caliber gun, starting at the feet and working your way up.
|
|
|
|
|
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.
|
|
|
|
|
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;
}
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
Hi,
I want the source code to check password,dos etc vulnerabilties in sql server.
Thanks
|
|
|
|
|
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.
|
|
|
|
|
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!
|
|
|
|
|
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;
";
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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 />
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
|
|
|
|