ODBC and MSDE

Hello,
I'm not sure if it is right group for this question, but I hope that
I'll get help here...
I'm using MSDE to store data for my application. And I'm having such
problem. I'm creating a table in DB for string connection information:
CREATE TABLE sql (
action_id int NOT NULL default '0',
database_name varchar(20) NOT NULL default '',
username varchar(15) NOT NULL default '',
password varchar(15) NOT NULL default '',
PRIMARY KEY (action_id)
);
Field 'database_name' stands for the DB name used by ODBC (In my Java
application I'm using ODBC, to connect to particular DB). So when I'm
adding a record to this table, for instance:
1, "MySQL", "root", "password"
ODBC || MSDE || JDBC changes the values by adding free space to fill all
the field's spaces (Instead of "MySQL" it adds "MySQL "). It
seems like some component doesn't understand that I'm using varchar(20),
not char.
For instance, when I'm using MySQL DB server, such problem doesn't
introduce itself... Does anyone know how to manage this?
Thank you,
Audrius
Audrius wrote:
> Hello,
> I'm not sure if it is right group for this question, but I hope that
> I'll get help here...
> I'm using MSDE to store data for my application. And I'm having such
> problem. I'm creating a table in DB for string connection information:
> CREATE TABLE sql (
> action_id int NOT NULL default '0',
> database_name varchar(20) NOT NULL default '',
> username varchar(15) NOT NULL default '',
> password varchar(15) NOT NULL default '',
> PRIMARY KEY (action_id)
> );
> Field 'database_name' stands for the DB name used by ODBC (In my Java
> application I'm using ODBC, to connect to particular DB). So when I'm
> adding a record to this table, for instance:
> 1, "MySQL", "root", "password"
> ODBC || MSDE || JDBC changes the values by adding free space to fill all
> the field's spaces (Instead of "MySQL" it adds "MySQL "). It
> seems like some component doesn't understand that I'm using varchar(20),
> not char.
> For instance, when I'm using MySQL DB server, such problem doesn't
> introduce itself... Does anyone know how to manage this?
> Thank you,
> Audrius
When I tried to insert data into MSDE table using console, everything
worked fine. The data occupies exactly as it should ("MySQL" - 5
symbols). But when I do this using ODBC from my application, it doesn't
work right. Can anyone suggest me a solution?
|||Audrius wrote:
> Audrius wrote:
>
> When I tried to insert data into MSDE table using console, everything
> worked fine. The data occupies exactly as it should ("MySQL" - 5
> symbols). But when I do this using ODBC from my application, it doesn't
> work right. Can anyone suggest me a solution?
The problem was solved.

ODBC and MSDE

Hello,
I'm not sure if it is right group for this question, but I hope that
I'll get help here...
I'm using MSDE to store data for my application. And I'm having such
problem. I'm creating a table in DB for string connection information:
CREATE TABLE sql (
action_id int NOT NULL default '0',
database_name varchar(20) NOT NULL default '',
username varchar(15) NOT NULL default '',
password varchar(15) NOT NULL default '',
PRIMARY KEY (action_id)
);
Field 'database_name' stands for the DB name used by ODBC (In my Java
application I'm using ODBC, to connect to particular DB). So when I'm
adding a record to this table, for instance:
1, "MySQL", "root", "password"
ODBC || MSDE || JDBC changes the values by adding free space to fill all
the field's spaces (Instead of "MySQL" it adds "MySQL "). It
seems like some component doesn't understand that I'm using varchar(20),
not char.
For instance, when I'm using MySQL DB server, such problem doesn't
introduce itself... Does anyone know how to manage this?
Thank you,
Audrius
Audrius wrote:
> Hello,
> I'm not sure if it is right group for this question, but I hope that
> I'll get help here...
> I'm using MSDE to store data for my application. And I'm having such
> problem. I'm creating a table in DB for string connection information:
> CREATE TABLE sql (
> action_id int NOT NULL default '0',
> database_name varchar(20) NOT NULL default '',
> username varchar(15) NOT NULL default '',
> password varchar(15) NOT NULL default '',
> PRIMARY KEY (action_id)
> );
> Field 'database_name' stands for the DB name used by ODBC (In my Java
> application I'm using ODBC, to connect to particular DB). So when I'm
> adding a record to this table, for instance:
> 1, "MySQL", "root", "password"
> ODBC || MSDE || JDBC changes the values by adding free space to fill all
> the field's spaces (Instead of "MySQL" it adds "MySQL "). It
> seems like some component doesn't understand that I'm using varchar(20),
> not char.
> For instance, when I'm using MySQL DB server, such problem doesn't
> introduce itself... Does anyone know how to manage this?
> Thank you,
> Audrius
When I tried to insert data into MSDE table using console, everything
worked fine. The data occupies exactly as it should ("MySQL" - 5
symbols). But when I do this using ODBC from my application, it doesn't
work right. Can anyone suggest me a solution?
|||Audrius wrote:
> Audrius wrote:
>
> When I tried to insert data into MSDE table using console, everything
> worked fine. The data occupies exactly as it should ("MySQL" - 5
> symbols). But when I do this using ODBC from my application, it doesn't
> work right. Can anyone suggest me a solution?
The problem was solved.

ODBC and MSDE

Hello,

I'm not sure if it is right group for this question, but I hope that
I'll get help here...

I'm using MSDE to store data for my application. And I'm having such
problem. I'm creating a table in DB for string connection information:

CREATE TABLE sql (
action_id int NOT NULL default '0',
database_name varchar(20) NOT NULL default '',
username varchar(15) NOT NULL default '',
password varchar(15) NOT NULL default '',
PRIMARY KEY (action_id)
);

Field 'database_name' stands for the DB name used by ODBC (In my Java
application I'm using ODBC, to connect to particular DB). So when I'm
adding a record to this table, for instance:
1, "MySQL", "root", "password"
ODBC || MSDE || JDBC changes the values by adding free space to fill
all the field's spaces (Instead of "MySQL" it adds "MySQL
"). It seems like some component doesn't understand that I'm using
varchar(20), not char.

For instance, when I'm using MySQL DB server, such problem doesn't
introduce itself... Does anyone know how to manage this?

Thank you,
AudriusWhen I tried to insert data into MSDE table using console, everything
worked fine. The data occupies exactly as it should ("MySQL" - 5
symbols). But when I do this using ODBC from my application, it doesn't
work right. Can anyone suggest me a solution?|||Audrius (audrius.peseckis@.gmail.com) writes:
> I'm not sure if it is right group for this question, but I hope that
> I'll get help here...
> I'm using MSDE to store data for my application. And I'm having such
> problem. I'm creating a table in DB for string connection information:
> CREATE TABLE sql (
> action_id int NOT NULL default '0',
> database_name varchar(20) NOT NULL default '',
> username varchar(15) NOT NULL default '',
> password varchar(15) NOT NULL default '',
> PRIMARY KEY (action_id)
> );
> Field 'database_name' stands for the DB name used by ODBC (In my Java
> application I'm using ODBC, to connect to particular DB). So when I'm
> adding a record to this table, for instance:
> 1, "MySQL", "root", "password"
> ODBC || MSDE || JDBC changes the values by adding free space to fill
> all the field's spaces (Instead of "MySQL" it adds "MySQL
> "). It seems like some component doesn't understand that I'm using
> varchar(20), not char.

It appears that your app passes "MySQL ". In such case,
SQL Server will store the trailing spaces when the ANSI_PADDING
option is in effect, which it is by default when you use ODBC. You
need to trim the trailing spaces somewhere on the line.

There are a few more possibilities, but I can't really speculate
about them, as you did not provide any code.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Yes, it solved the problem. ODBC was set to use ANSI padding by
default. Your reply saved me a lot of time, thank you.

Audrius|||Audrius (audrius.peseckis@.gmail.com) writes:
> Yes, it solved the problem. ODBC was set to use ANSI padding by
> default. Your reply saved me a lot of time, thank you.

While turning of ANSI_PADDING may solve the problem, be aware of
that there are features in SQL Server that require ANSI_PADDING to
be on. In SQL 2000 that is indexed views and indexes on computed
columns. I believe there are more features in SQL 2005.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

ODBC and MSDE

Hello,
I'm not sure if it is right group for this question, but I hope that
I'll get help here...
I'm using MSDE to store data for my application. And I'm having such
problem. I'm creating a table in DB for string connection information:
CREATE TABLE sql (
action_id int NOT NULL default '0',
database_name varchar(20) NOT NULL default '',
username varchar(15) NOT NULL default '',
password varchar(15) NOT NULL default '',
PRIMARY KEY (action_id)
);
Field 'database_name' stands for the DB name used by ODBC (In my Java
application I'm using ODBC, to connect to particular DB). So when I'm
adding a record to this table, for instance:
1, "MySQL", "root", "password"
ODBC || MSDE || JDBC changes the values by adding free space to fill all
the field's spaces (Instead of "MySQL" it adds "MySQL "). It
seems like some component doesn't understand that I'm using varchar(20),
not char.
For instance, when I'm using mysql DB server, such problem doesn't
introduce itself... Does anyone know how to manage this?
Thank you,
AudriusAudrius wrote:
> Hello,
> I'm not sure if it is right group for this question, but I hope that
> I'll get help here...
> I'm using MSDE to store data for my application. And I'm having such
> problem. I'm creating a table in DB for string connection information:
> CREATE TABLE sql (
> action_id int NOT NULL default '0',
> database_name varchar(20) NOT NULL default '',
> username varchar(15) NOT NULL default '',
> password varchar(15) NOT NULL default '',
> PRIMARY KEY (action_id)
> );
> Field 'database_name' stands for the DB name used by ODBC (In my Java
> application I'm using ODBC, to connect to particular DB). So when I'm
> adding a record to this table, for instance:
> 1, "MySQL", "root", "password"
> ODBC || MSDE || JDBC changes the values by adding free space to fill all
> the field's spaces (Instead of "MySQL" it adds "MySQL "). It
> seems like some component doesn't understand that I'm using varchar(20),
> not char.
> For instance, when I'm using mysql DB server, such problem doesn't
> introduce itself... Does anyone know how to manage this?
> Thank you,
> Audrius
When I tried to insert data into MSDE table using console, everything
worked fine. The data occupies exactly as it should ("MySQL" - 5
symbols). But when I do this using ODBC from my application, it doesn't
work right. Can anyone suggest me a solution?|||Audrius wrote:
> Audrius wrote:
>
>
> When I tried to insert data into MSDE table using console, everything
> worked fine. The data occupies exactly as it should ("MySQL" - 5
> symbols). But when I do this using ODBC from my application, it doesn't
> work right. Can anyone suggest me a solution?
The problem was solved.

Obtaining statistical data in MSDB db

Dear folks,
I would like have available statistical data concerning our scheduled DTS.
Up to date I am be able to store and to see some data using sysjobs and
sysjobhistory tables in MSDB database, i.e, I obtain variables such as last
execution, time inverted and owner, as well as owner or version id. That's
fine and very useful but I would need more accurate information in order to
create statistics with the total of time inverted per each DTS during the
last two ws (for instance) or be able to desing a "production map" and
therefore, to schedule better our DTS, covering spaces in terms of time and
avoiding bottlenecks or issues which sometimes are causing headaches to all
the developers.
How could I do such thing? I am seeing these aforementioned tables but the
info stored is the last day.
select run_date,run_duration,run_status,step_na
me from sysjobhistory where
step_name
in('__ABS_PasarDiarioaHistorico')
I see this:
20050805 2855 1 __ABS_PasarDiarioaHistor
ico
20050806 2518 1 __ABS_PasarDiarioaHistor
ico
20050807 148 1 __ABS_PasarDiarioaHistori
co
20050808 6 1 __ABS_PasarDiarioaHistorico
20050809 2113 1 __ABS_PasarDiarioaHistor
ico
20050810 3205 1 __ABS_PasarDiarioaHistor
ico
20050811 3355 1 __ABS_PasarDiarioaHistor
ico
But, July executions where on earth are?
Any though/comment/advice will be welcomed.
Regards,I've done this query which is solving any doubts:
SELECT run_date, run_duration, case MSDB..sysjobschedules.freq_type
when 4 then 'Daily'
when 8 then 'Wly'
when 16 then 'Monthly'
when 32 then 'once'
end
FROM MSDB..sysjobhistory inner join MSDB..sysjobschedules
on MSDB..sysjobhistory.step_name = MSDB..sysjobschedules.name
WHERE STEP_NAME IN('__ABS_EliminarHistorico') ORDER BY 1
*** __ABS_EliminarHistorico is one of our DTS.
"Enric" wrote:

> Dear folks,
> I would like have available statistical data concerning our scheduled DTS.
> Up to date I am be able to store and to see some data using sysjobs and
> sysjobhistory tables in MSDB database, i.e, I obtain variables such as las
t
> execution, time inverted and owner, as well as owner or version id. That's
> fine and very useful but I would need more accurate information in order t
o
> create statistics with the total of time inverted per each DTS during the
> last two ws (for instance) or be able to desing a "production map" and
> therefore, to schedule better our DTS, covering spaces in terms of time an
d
> avoiding bottlenecks or issues which sometimes are causing headaches to al
l
> the developers.
> How could I do such thing? I am seeing these aforementioned tables but the
> info stored is the last day.
> select run_date,run_duration,run_status,step_na
me from sysjobhistory where
> step_name
> in('__ABS_PasarDiarioaHistorico')
> I see this:
> 20050805 2855 1 __ABS_PasarDiarioaHistor
ico
> 20050806 2518 1 __ABS_PasarDiarioaHistor
ico
> 20050807 148 1 __ABS_PasarDiarioaHistori
co
> 20050808 6 1 __ABS_PasarDiarioaHistorico
> 20050809 2113 1 __ABS_PasarDiarioaHistor
ico
> 20050810 3205 1 __ABS_PasarDiarioaHistor
ico
> 20050811 3355 1 __ABS_PasarDiarioaHistor
ico
>
> But, July executions where on earth are?
>
> Any though/comment/advice will be welcomed.
> Regards,|||Enric
select convert(varchar(22), j.name) as job_name
, case freq_type -- Daily, wly, Monthly
when 1 then 'Once'
when 4 then 'Daily'
when 8 then 'Wk ' -- For wly, add in the days of the w
+ case freq_interval & 2 when 2 then 'M' else '' end -- Monday
+ case freq_interval & 4 when 4 then 'Tu' else '' end -- Tuesday
+ case freq_interval & 8 when 8 then 'W' else '' end -- etc
+ case freq_interval & 16 when 16 then 'Th' else '' end
+ case freq_interval & 32 when 32 then 'F' else '' end
+ case freq_interval & 64 when 64 then 'Sa' else '' end
+ case freq_interval & 1 when 1 then 'Su' else '' end
when 16 then 'Mthly on day ' + convert(varchar(2), freq_interval) -- Monthly
on a particular day
when 32 then 'Mthly ' -- The most complicated one, "every third Friday of
the month" for example
+ case freq_relative_interval
when 1 then 'Every First '
when 2 then 'Every Second '
when 4 then 'Every Third '
when 8 then 'Every Fourth '
when 16 then 'Every Last '
end
+ case freq_interval
when 1 then 'Sunday'
when 2 then 'Monday'
when 3 then 'Tuesday'
when 4 then 'Wednesday'
when 5 then 'Thursday'
when 6 then 'Friday'
when 7 then 'Saturday'
when 8 then 'Day'
when 9 then 'W day'
when 10 then 'Wend day'
end
when 64 then 'Startup' -- When SQL Server starts
when 128 then 'Idle' -- Whenever SQL Server gets bored
else 'Err' -- This should never happen
end as schedule
, case freq_subday_type -- FOr when a job funs every few seconds, minutes or
hours
when 1 then 'Runs once at:'
when 2 then 'every ' + convert(varchar(3), freq_subday_interval) + '
seconds'
when 4 then 'every ' + convert(varchar(3), freq_subday_interval) + '
minutes'
when 8 then 'every ' + convert(varchar(3), freq_subday_interval) + ' hours'
end as frequency
-- All the subsrings are because the times are stored as an integer with no
leading zeroes
-- i.e. 0 means midnight, 13000 means half past one in the morning
(01:30:00)
, substring (right (stuff (' ', 1, 1, '000000') +
convert(varchar(6),active_start_time), 6), 1, 2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_start_time),
6) ,3 ,2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time),
6) ,5 ,2) as start_at
,case freq_subday_type
when 1 then NULL -- Ignore the end time if not a recurring job
else substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),
active_end_time), 6), 1, 2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time),
6) ,3 ,2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time),
6) ,5 ,2) end as end_at
from sysjobs j
join sysJobSchedules s
on j.job_id = s.job_id
order by j.name, start_at
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:E8350DF7-6624-465B-888A-78FDE5899FBB@.microsoft.com...
> I've done this query which is solving any doubts:
> SELECT run_date, run_duration, case MSDB..sysjobschedules.freq_type
> when 4 then 'Daily'
> when 8 then 'Wly'
> when 16 then 'Monthly'
> when 32 then 'once'
> end
> FROM MSDB..sysjobhistory inner join MSDB..sysjobschedules
> on MSDB..sysjobhistory.step_name = MSDB..sysjobschedules.name
> WHERE STEP_NAME IN('__ABS_EliminarHistorico') ORDER BY 1
> *** __ABS_EliminarHistorico is one of our DTS.
> "Enric" wrote:
>|||Thanks so much Uri, it's amazing, just a quick and powerful response.
"Uri Dimant" wrote:

> Enric
> select convert(varchar(22), j.name) as job_name
> , case freq_type -- Daily, wly, Monthly
> when 1 then 'Once'
> when 4 then 'Daily'
> when 8 then 'Wk ' -- For wly, add in the days of the w
> + case freq_interval & 2 when 2 then 'M' else '' end -- Monday
> + case freq_interval & 4 when 4 then 'Tu' else '' end -- Tuesday
> + case freq_interval & 8 when 8 then 'W' else '' end -- etc
> + case freq_interval & 16 when 16 then 'Th' else '' end
> + case freq_interval & 32 when 32 then 'F' else '' end
> + case freq_interval & 64 when 64 then 'Sa' else '' end
> + case freq_interval & 1 when 1 then 'Su' else '' end
> when 16 then 'Mthly on day ' + convert(varchar(2), freq_interval) -- Month
ly
> on a particular day
> when 32 then 'Mthly ' -- The most complicated one, "every third Friday of
> the month" for example
> + case freq_relative_interval
> when 1 then 'Every First '
> when 2 then 'Every Second '
> when 4 then 'Every Third '
> when 8 then 'Every Fourth '
> when 16 then 'Every Last '
> end
> + case freq_interval
> when 1 then 'Sunday'
> when 2 then 'Monday'
> when 3 then 'Tuesday'
> when 4 then 'Wednesday'
> when 5 then 'Thursday'
> when 6 then 'Friday'
> when 7 then 'Saturday'
> when 8 then 'Day'
> when 9 then 'W day'
> when 10 then 'Wend day'
> end
> when 64 then 'Startup' -- When SQL Server starts
> when 128 then 'Idle' -- Whenever SQL Server gets bored
> else 'Err' -- This should never happen
> end as schedule
> , case freq_subday_type -- FOr when a job funs every few seconds, minutes
or
> hours
> when 1 then 'Runs once at:'
> when 2 then 'every ' + convert(varchar(3), freq_subday_interval) + '
> seconds'
> when 4 then 'every ' + convert(varchar(3), freq_subday_interval) + '
> minutes'
> when 8 then 'every ' + convert(varchar(3), freq_subday_interval) + ' hours
'
> end as frequency
> -- All the subsrings are because the times are stored as an integer with n
o
> leading zeroes
> -- i.e. 0 means midnight, 13000 means half past one in the morning
> (01:30:00)
> , substring (right (stuff (' ', 1, 1, '000000') +
> convert(varchar(6),active_start_time), 6), 1, 2)
> + ':'
> + substring (
> right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_start_time
),
> 6) ,3 ,2)
> + ':'
> + substring (
> right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time)
,
> 6) ,5 ,2) as start_at
> ,case freq_subday_type
> when 1 then NULL -- Ignore the end time if not a recurring job
> else substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),
> active_end_time), 6), 1, 2)
> + ':'
> + substring (
> right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time),
> 6) ,3 ,2)
> + ':'
> + substring (
> right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time),
> 6) ,5 ,2) end as end_at
> from sysjobs j
> join sysJobSchedules s
> on j.job_id = s.job_id
> order by j.name, start_at
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:E8350DF7-6624-465B-888A-78FDE5899FBB@.microsoft.com...
>
>

Obtaining Procedure Declaration

Where does SQL Server 2000 store the definition of stored procedures and
views? Is there a system table that I can query and retrieve the definition
,
like it shows up when scripting from the QA Object Browser?Kevin Bowker wrote:
> Where does SQL Server 2000 store the definition of stored procedures and
> views? Is there a system table that I can query and retrieve the definiti
on,
> like it shows up when scripting from the QA Object Browser?
syscomments table contains full text of views, procedures and other objects.|||One of my colleagues apparently does better footwork than I do and came up
with this for those who may be interested:
select routine_definition
from information_schema.routines
where routine_type='Procedure'
"Kevin Bowker" wrote:

> Where does SQL Server 2000 store the definition of stored procedures and
> views? Is there a system table that I can query and retrieve the definiti
on,
> like it shows up when scripting from the QA Object Browser?|||I would use sp_helptext in 2000 and previous. This will account for
procedures larger than 8K that span multiple rows in syscomments and
INFORMATION_SCHEMA.ROUTINES.
In SQL Server 2005, you can use the new OBJECT_DEFINITION() function, or the
new catalog view sys.procedures
"Kevin Bowker" <KevinBowker@.discussions.microsoft.com> wrote in message
news:73D65E14-A411-4FD7-96FB-A85CAC02EF5F@.microsoft.com...
> Where does SQL Server 2000 store the definition of stored procedures and
> views? Is there a system table that I can query and retrieve the
> definition,
> like it shows up when scripting from the QA Object Browser?