|
I am working in a mySQL db and we made a mistake when implementing the tables by having one of the fields in every table being to short. We use this field to catch which form in the applications update each table. The syntax I am finding is to do this...ALTER TABLE `cl_client_common` change `UpdateProgram` char(50). It is currently a char(20) I know that this will work and we should not lose any data however what I am wanting to accomplish is to do this to every table in the db without having to write the code for all 200+ tables. So what I am wondering is could I write something like ALTER TABLE `databaseName`.`*` change `UpdateProgram` char(50).
Any advice would be much appreciated, Please and Thank you.
Humble Programmer
|
|
|
|
|
You can use system tables to generate proper statements. It would be something like following:
SELECT CONCAT( 'ALTER TABLE ', Table_Name, ' CHANGE ...')
FROM Information_Schema.Tables
WHERE Table_Schema = 'YourSchema';
When executing this select, you'll get statements you can again execute to make the actual changes.
Hope this helps,
Mika
|
|
|
|
|
Good advice I don't know what that did not cross my mind.
Humble Programmer
|
|
|
|
|
I used your solution yesterday, it worked perfectly. Thank you very much you are much appreciated.
Humble Programmer
|
|
|
|
|
You're welcome
|
|
|
|
|
Hi,
I have sql server 2005 installed with most of if not all its components, and also have vwd 2008 and VS 2005. Now I am ready to install VS 2008, is it a good method to uninstall sql 2005 and VWD 2008 to start a clean VS 2008 installation?
regards,
hawari
hawari
|
|
|
|
|
You can install SQL Server 2008 and keep SQL Server 2005 if you need both of them. From my experiences there's been no trouble.
I don't know what you're referring to with VWD?
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
09 is referring to Visual Web Developer
The world is a stage and most of us are desperately unrehearsed.
—Sean O’Casey, Playwright
|
|
|
|
|
Ok, thanks for the info! That's something I know nothing about...
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
No reason to uninstall SQL Server 2005. Not sure why you would need VS 2008 and VWD 2008.
The world is a stage and most of us are desperately unrehearsed.
—Sean O’Casey, Playwright
|
|
|
|
|
hi;
i want to write a sql query of different between two dates.
.....................................................
DECLARE @mindays DATETIME
DECLARE @today DATETIME
SET @mindays =select min(daily_date) from r_dailymaterial_entry)
SET @today = getdate()
SELECT DATEDIFF(DAY, @mindays, @today) AS [DateDifference]
.....................................................
i gets a error..
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'select'.
please help me...
|
|
|
|
|
snehasish wrote: SET @mindays =select min(daily_date) from r_dailymaterial_entry)
Try the following. There was an extra parenthesis in the end and when you use select, you don't use set command in the start:
select @mindays = min(daily_date) from r_dailymaterial_entry
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
hi Mika Wendelius,
Many thanks to you for giving this solution.It works fine. Thanks once again.
snehasish
|
|
|
|
|
You're welcome
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Given the T-SQL statments below, it seems that the statements do nothing other than splitting the @MinDate and @MaxDate into day, month and year and then recreating it. Before deleting this portion, can you people verify that I am not missing any perspective.
select @MinDate = min(TheDate) from tDate where TheDate > convert(datetime,'01-01-1980',105)
select @MaxDate = max(TheDate) from tDate
--The following statements are not doing anything meaningful, I believe.
set @Year = datepart(yyyy,@MinDate)
set @Month = datepart(mm,@MinDate)
set @Day = datepart(d,@MinDate)
if len(@Month) = 1
begin
set @Month = '0'+@Month
end
if len(@Day) = 1
begin
set @Day = '0'+@Day
end
set @MinDate = convert(datetime,@Day+'-'+@Month+'-'+@Year,105)
set @Year = datepart(yyyy,@MaxDate)
set @Month = datepart(mm,@MaxDate)
set @Day = datepart(d,@MaxDate)
if len(@Month) = 1
begin
set @Month = '0'+@Month
end
if len(@Day) = 1
begin
set @Day = '0'+@Day
end
set @MaxDate = convert(datetime,@Day+'-'+@Month+'-'+@Year,105)
Regards,
Syed Mehroz Alam
My Blog
My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein
|
|
|
|
|
The original developer, may he suffer a lifetime of minor aches and pains, appears to be preparing for some kind of string-parsing which requires each portion of the date to have two characters. This would be better accomplished by ensuring the format of the incoming date; however, I would say you're more likely to cause harm by taking this out than you are by leaving it in.
If, on the other hand, you want this script to be portable to a server with different internationalization settings (e.g. month/day/year), then nuke this immediately.
|
|
|
|
|
This also has the effect of stripping any time values off of the dates. They are datetime values and the time portion may not be used, but if there is a time portion it is being removed and only the date is used. If for some reason that is the goal, I think it's better done this way:
cast(convert(char(10), @datetime, 101) as datetime)
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Hi, i have error" Data type mismatch in criteria expression" when execute : clsADONET.setConnCommand_Close(sCommand) . Can explain for me what's wrong? here is the code. thanks
public void Update()
{
try
{
string cmd;
string field;
string parameter;
field = "Site,Model”;
parameter = "@Site,@model”;
cmd = "INSERT INTO Run(" + field + ") values (" + parameter + ")";
clsADONET.setConnCommand_Open(sCommand);
sCommand.CommandText = cmd;
sCommand.Parameters.Add("@Site", OleDbType.VarChar, 20);
sCommand.Parameters.Add("@Model", OleDbType.VarChar, 20);
sCommand.Parameters["@Site"].Value = CS.site;
sCommand.Parameters["@Model"].Value = CS.DUTSelected.ToString();
clsADONET.setConnCommand_Close(sCommand);
}
catch(Exception e)
{
MessageBox.Show(e.Message);
return;
}
}
class clsADONET
{
public static OleDbConnection sConnection = new OleDbConnection();
public static OleDbCommand sCommand = new OleDbCommand();
public static OleDbDataAdapter sAdapter = new OleDbDataAdapter();
public static void setConnCommand_Open(OleDbCommand sCommand)
{
setConnection(CommunicationSettings .DatabaseonServer);
sConnection.Open();
sCommand.Connection = sConnection;
}
public static void setConnCommand_Close(OleDbCommand sCommand)
{
try
{
sCommand.ExecuteNonQuery();
sCommand.Parameters.Clear();
sCommand.Connection.Close();
sConnection.Close();
}
catch (Exception e)
{
clsLog.LogEvent("Error", "", "Module:setConnCommand_Close(clsADONET.cs) ", e.Message, CommunicationSettings.EventNo);
}
}
public static void setConnection(string dataBaseLocation)
{
if (sConnection.State == ConnectionState.Open) sConnection.Close();
sConnection.ConnectionString = setConnectionString(dataBaseLocation, "");
}
}
|
|
|
|
|
Could the following be the problem?
sCommand.Parameters["@Site"].Value = CS.site;
The world is a stage and most of us are desperately unrehearsed.
—Sean O’Casey, Playwright
|
|
|
|
|
i don't think so, the type is match.
|
|
|
|
|
Based on your code I'm not sure which sCommand is used at which point. You have a static sCommand, you have defined sCommand somewhere in the class where you have Update-method and you pass sCommand as a parameter for methods in clsADONET. So basically you may be even executing a different command.
The first thing I think you should do is to separate the naming so that class field is for example named _command, parameter is named command and the variable in update method is something else. This would help you when debugging the code.
Also if you want to use a single command all the time (based on the fact that you have a single static field in clsADONET) you could remove the parameters from the methods and use more accurate method calling implementation.
Hope this helps you.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
The following query gives me a concatenation of the name attributes of all sections in an XML column, but I need the values to be delimited. How can I do this?
select ExportAgentID, ExportDef.query('for $nm in /ExportSpecification/Sections/Section/@name return string($nm)') from csExportAgent where ExportAgentID = 30
|
|
|
|
|
Brady Kelly wrote: The following query gives me a concatenation of the name attributes of all sections in an XML column, but I need the values to be delimited.
One way to do this is to use concat function:
...ExportDef.query('for $nm in /ExportSpecification/Sections/Section/@name return concat(string($nm),'', '')')...
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Thanks, I actually just figured that out. It seems to use a single space as a default delimiter, which could present problems in some scenarios. Can you suggest any way of preventing this?
|
|
|
|
|
If you want a simple varchar value out then you could use something like:
...replace(
convert(
varchar(500),
ExportDef.query('for $nm in /ExportSpecification/Sections/Section/@name return concat(string($nm),'', '')'
),
', ',
',')...
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|