|
That should be marked as abuse!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
That is why I used the joke icon.
|
|
|
|
|
That's all right. I'm a big boy now.
Not that the joke is funny, my VACUUM command failed and I'm really worrying right now. This is the development database, but I'm really afraid for the production database following any minute.
I did a VACUUM on all tables for production except for one (the largest one) which fails to do anything  .
Anyway, continueing the search, with a positive attitude 
|
|
|
|
|
I meant Tim's response...
I can't help as I have zero experience with MySql. You might check out if SQLServerCentral helps out with MySql or if there is a specialist site for the database. This sounds like a DBA level problem not a developer issue.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: I meant Tim's response...
I know ...
I am the DBA and developer and analyst and tester and ... 
|
|
|
|
|
V. wrote: he DBA and developer and analyst and tester and ...
No you are the poor sod having to look after the database, you may have designed, built, populated the data, tuned the indexes and then broke the bloody thing. You are not a DBA, you just have to do that job.
Sorry I can't help!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I've never used Postgres or I would help.
Can you do a backup and compress? Just grasping for help....
|
|
|
|
|
|
It's in the planning to do something like this, but for the immediate problem it won't help me.
But thanks for the effort.
|
|
|
|
|
Hello Experts,
I have been to most of the links provided by Google for the below question. All I get is difference.
For some reason, there is a table has all the columns as Key columns which is kinda weird but I have to deal with this. Let me put this way. My table contains Data with 6 columns and all are Keys. If I want to edit something then I will have to either delete entire row first and Insert it again or do something. So now if we have sequence object, If I edit the row with a extra Item, wont that be a new row? will that also be existing row with same sequence? Please help me in understanding this.
Your help is much appreciated.
Thanks
Happy Coding!
|
|
|
|
|
I have a SQL query that is returning
modified 3-May-15 13:07pm.
|
|
|
|
|
You probably have an operator-precedence problem due to combining OR with AND -- I can never get it right myself, so I always use parentheses.
I also recommend not using sub-queries, especially with IN -- try a JOIN instead.
|
|
|
|
|
The only problem I see is how do I add the user and rights tables in to the query? There are no keys to access those tables.
|
|
|
|
|
I know there are a lot of posts but I finally understand and did what I was told to do. I broke the query up with parameters and I am still getting the security error. My code is below the with the parameters removed from the hard coded string, the calling code, and the implementing code:
The 3 classes with the SQL w/ with the parameters broken out, the calling code, and the implementing code:
Class with the parameters broken out:
public class MyParam
{
public string name { get; set; }
public string value { get; set; }
}
public class QueryContainer
{
string _query;
public List parameterList = new List();
public QueryContainer(string query) { _query = query; }
public string Query
{
get
{
return _query;
}
set { _query = value; }
}
}<pre>
The calling code:
<pre>
public int GetAccountSortByAccountCode(int account)
{
QueryContainer Instance = new QueryContainer("SELECT ac_sort_order FROM lkup_account_codes where ac_code = <a href="http:
MyParam myParam = new MyParam();
myParam.name = "@account";
myParam.value = account.ToString();
Instance.parameterList.Add(myParam);
return Convert.ToInt32(ExecuteScaler(Instance, 1));
}
<pre>
The implementing code:
<pre>
if (_connection == null || _connection.State == ConnectionState.Closed)
{
OpenConnection();
}
DbCommand command = _provider.CreateCommand();
command.Connection = _connection;
{
command.CommandText = Instance.Query;
command.CommandType = CommandType.Text;
foreach (var p in Instance.parameterList)
{
SqlParameter param = new SqlParameter(p.name, p.value);
command.Parameters.Add(param);
}
if (_useTransaction) { command.Transaction = _transaction; }
try
{
returnValue = command.ExecuteScalar();
}
catch (Exception ex)
{
if (ex is EntryPointNotFoundException)
throw ex;
RollBack();
LogBLL bll = new LogBLL();
bll.WriteErrorLog(ex);
_iserror = true;
}
<pre>
|
|
|
|
|
I know there are a lot of posts but I finally understand and did what I was told to do. I broke the query up with parameters and I am still getting the security error. My code is below the with the parameters removed from the hard coded string, the calling code, and the implementing code:
The 3 classes with the SQL w/ with the parameters broken out, the calling code, and the implementing code:
Class with the parameters broken out:
public class MyParam
{
public string name { get; set; }
public string value { get; set; }
}
public class QueryContainer
{
string _query;
public List<myparam> parameterList = new List<myparam>();
public QueryContainer(string query) { _query = query; }
public string Query
{
get
{
return _query;
}
set { _query = value; }
}
}
The calling code:
<pre>
public int GetAccountSortByAccountCode(int account)
{
QueryContainer Instance = new QueryContainer("SELECT ac_sort_order FROM lkup_account_codes where ac_code = <a href="http:
MyParam myParam = new MyParam();
myParam.name = "@account";
myParam.value = account.ToString();
Instance.parameterList.Add(myParam);
return Convert.ToInt32(ExecuteScaler(Instance, 1));
}
<pre>
The implementing code:
<pre>
if (_connection == null || _connection.State == ConnectionState.Closed)
{
OpenConnection();
}
DbCommand command = _provider.CreateCommand();
command.Connection = _connection;
{
command.CommandText = Instance.Query;
command.CommandType = CommandType.Text;
foreach (var p in Instance.parameterList)
{
SqlParameter param = new SqlParameter(p.name, p.value);
command.Parameters.Add(param);
}
if (_useTransaction) { command.Transaction = _transaction; }
try
{
returnValue = command.ExecuteScalar();
}
catch (Exception ex)
{
if (ex is EntryPointNotFoundException)
throw ex;
RollBack();
LogBLL bll = new LogBLL();
bll.WriteErrorLog(ex);
_iserror = true;
}
<pre>
|
|
|
|
|
Try something like this:
SELECT DISTINCT
dw.we_System_Key,
w.we_System
FROM
dod
INNER JOIN do_we_systems dw on d.dod = dw.dod
INNER JOIN lkup_we_systems w on dw.we_System_Key = w.we_System_Key
WHERE
dw.is_deleted = 0
AND
(
w.we_system != ''
OR
EXISTS
(
SELECT 1
FROM users_rights ur
INNER JOIN rights r ON r.rights_key = ur.rights_key
INNER JOIN users usr ON usr.username = ur.username
WHERE usr.username = 'RHale1'
AND r.rights_code = 'Non-Standard Test'
)
)
ORDER BY
w.we_system
;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
holdorf wrote:
I have a SQL query that is returning
From where?
This looks weird like this; where did the rest of the question go?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi all, I have a function that receives a column name and it will return this column name when the function is called, if the column name is not null.
This function will be called in a stored proc inside of the Select statement. The function will return the name of a column entered as input and it will be used inside of the Select statement.
The following is my function
Create function GetName(@C nvarchar(50))
Returns nvarchar(50)
As
Begin
Declare @Col_Name nvarchar(50)<br />
IF (@Col IS NOT null)
Begin
Set @ColumName = @Col<br />
END
Return @Col_Name
End
I expect this function to return any input a user enters but it doesn't return anything and I'm not getting any errors. Also how do I call this function in my stored proc so that the column name is added to the select statement automatically. Any help is great, thanks.
modified 29-Apr-15 10:34am.
|
|
|
|
|
|
Hi, thank you all for responding. I decided to use a stored proc instead of a function and used Dynamic SQL to write my query.
Create Porocedure GetName
(
@column nvarchar(50)
@columnData nvarchar(50)
)
As
Begin
Declare @MyQuery nvarchar(300)
set @column= IF (@column IS NOT null AND @column ='TableA.SomeColumn')
Begin
Set @MyQuery = N'Select'+ TableA.[@column] + 'from dbo.TableA
(where TableA.Column1 LIKE' + @columnData + '+ ''%'' OR' + @columnData + 'IS null)'
END
End
EXEC sp_executesql;
I get no errors but it does not work. Any help will be greatly appreciated, thanks.
modified 29-Apr-15 19:53pm.
|
|
|
|
|
The statement "EXEC sp_executesql;" should include your variable (@MyQuery)
Mongo: Mongo only pawn... in game of life.
|
|
|
|
|
Try this:
CREATE PROC dbo.GetName
(
@column sysname,
@columnData nvarchar(50)
)
As
BEGIN
DECLARE @MyQuery nvarchar(max), @RealColumnName nvarchar(130);
SET NOCOUNT ON;
SELECT
@RealColumnName = QuoteName(name)
FROM
sys.columns
WHERE
object_id = OBJECT_ID('dbo.TableA')
And
name = @column
;
If @RealColumnName Is Null
BEGIN
RAISERROR('Invalid column name: "%s"', 16, 1, @column);
Return;
END;
SET @MyQuery = N'SELECT ' + @RealColumnName + N' FROM dbo.TableA WHERE (' + @RealColumnName + N' Like @columnData + N''%'' Or @columnData Is Null)';
EXEC sp_executesql @MyQuery, N'@columnData nvarchar(50)', @columnData = @columnData;
END
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Now that is a good explination
Mongo: Mongo only pawn... in game of life.
|
|
|
|
|
You made a typo in your explanation code, at least I hope it is not in your actual function.
@Col_Name is not defined. @ColumName is.
Mongo: Mongo only pawn... in game of life.
|
|
|
|
|
Hi, thanks for replying. I caught that error and my query now runs but it returns nothing
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
