Good morning,
What I can do to allow developers have access to view the jobs I created
with out giving them administrator permission?
Thanks.Hi
Doesn't member of db_owner fixed database role have an access to the jobs?
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:4315658A-3079-41F7-B8E9-45225924CDAF@.microsoft.com...
> Good morning,
> What I can do to allow developers have access to view the jobs I created
> with out giving them administrator permission?
> Thanks.|||Thans. but I don't want them to be a member of db_owner.
"Uri Dimant" wrote:
> Hi
> Doesn't member of db_owner fixed database role have an access to the job
s?
>
>
> "Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
> news:4315658A-3079-41F7-B8E9-45225924CDAF@.microsoft.com...
>
>|||There are no special roles for job management in 2000. There are in 2005.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:4315658A-3079-41F7-B8E9-45225924CDAF@.microsoft.com...
> Good morning,
> What I can do to allow developers have access to view the jobs I created
> with out giving them administrator permission?
> Thanks.|||There is a role in msdb called TargetserverRole, which allows members to vie
w
the jobs in EM.
Just keep in mind this is undocumented and subject to change. For instance,
permissions this role had were changed with SP3. So if you make use of it,
test in dev before slapping SP4 on in production (if/when it comes out).
I got above info from following discussion at sqlservercentral:
[url]http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=108953[
/url]
"Matthew Z" wrote:
> Good morning,
> What I can do to allow developers have access to view the jobs I created
> with out giving them administrator permission?
> Thanks.
Showing posts with label access. Show all posts
Showing posts with label access. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
JOB Security
hi @.ll
I have a problem with the security on the JOB.
We have a lot of User (Programmer) this users needs access to the job from
other Users.
If the User has SysAdmin Right everything works fine, without SysAdmin the
User see the Job from the other Job not.
Is there any way to give this kind of User Access to the job from the other
Users?
thanks for any kind of answer
klausThere isn't a supported way to do this as non-sysadmins can
only view or edit jobs they own.
An unsupported method that some have used is to add the user
to the TargetServer role in msdb. The permissions members of
this role has varies depending on what service pack has been
applied.
-Sue
On Wed, 16 Mar 2005 09:36:59 +0100, "Klaus Bilger"
<Klaus.Bilger@.C-S-L.BIZ> wrote:
>hi @.ll
>I have a problem with the security on the JOB.
>We have a lot of User (Programmer) this users needs access to the job from
>other Users.
>If the User has SysAdmin Right everything works fine, without SysAdmin the
>User see the Job from the other Job not.
> Is there any way to give this kind of User Access to the job from the othe
r
>Users?
>
>thanks for any kind of answer
>
>
>klaus
>
I have a problem with the security on the JOB.
We have a lot of User (Programmer) this users needs access to the job from
other Users.
If the User has SysAdmin Right everything works fine, without SysAdmin the
User see the Job from the other Job not.
Is there any way to give this kind of User Access to the job from the other
Users?
thanks for any kind of answer
klausThere isn't a supported way to do this as non-sysadmins can
only view or edit jobs they own.
An unsupported method that some have used is to add the user
to the TargetServer role in msdb. The permissions members of
this role has varies depending on what service pack has been
applied.
-Sue
On Wed, 16 Mar 2005 09:36:59 +0100, "Klaus Bilger"
<Klaus.Bilger@.C-S-L.BIZ> wrote:
>hi @.ll
>I have a problem with the security on the JOB.
>We have a lot of User (Programmer) this users needs access to the job from
>other Users.
>If the User has SysAdmin Right everything works fine, without SysAdmin the
>User see the Job from the other Job not.
> Is there any way to give this kind of User Access to the job from the othe
r
>Users?
>
>thanks for any kind of answer
>
>
>klaus
>
Monday, March 19, 2012
Job Hang when user connected
Hi all,
B.T.W. Sorry for my poor english.
I have database in SQL server 2000 wich user can access with ASP
page. They connect to the database it and return some info.
I got, in SQL Server, a job that run 1 DTS and some stored procedur. It is
running each hour in a day.
I dont know why but when they are web connection to my databse the job
just hang to Step 1 and never end. I have to Stop the Job and restart it.
Again,
if user are connected to my database, the Job just hang. The weird part is
that I can
run the DTS included in the job manually everytime whitout error, even if
user are connected to it ?!
Someone got the same problem
Should I disconnect all user before runnung the job ?
If yes, how can I do that ?
Thanks
Richard
First of all enable DTS package logging as specified in BOL:
To enable package logging
Open the Data Transformation Services (DTS) package for which you want to create a log.
On the Package menu, click Properties to display the DTS Package Properties dialog box.
Do one of the following:
Save package logs to Microsoft? SQL Server? by clicking the Logging tab, selecting the Log package execution to SQLServer check box, and then clicking an available server on which to save the package logs.
Security Note When possible, use Windows Authentication.
Save package logs to SQL Server 2000 Meta Data Services by clicking the Advanced tab, and then selecting the Show lineage variables as source columns and Write lineage to repository check boxes. On the Package menu, click Save As, and then in the Save DTS
Package dialog box, in the Location list, select Meta Data Services.
,... in order to findout the flow of DTS package.
Also refer to SQLAgent log and SQL Server error log for any information on this behaviour.
"Richard Martin" wrote:
> Hi all,
> B.T.W. Sorry for my poor english.
> I have database in SQL server 2000 wich user can access with ASP
> page. They connect to the database it and return some info.
> I got, in SQL Server, a job that run 1 DTS and some stored procedur. It is
> running each hour in a day.
> I dont know why but when they are web connection to my databse the job
> just hang to Step 1 and never end. I have to Stop the Job and restart it.
> Again,
> if user are connected to my database, the Job just hang. The weird part is
> that I can
> run the DTS included in the job manually everytime whitout error, even if
> user are connected to it ?!
> Someone got the same problem
> Should I disconnect all user before runnung the job ?
> If yes, how can I do that ?
>
> Thanks
> Richard
>
>
B.T.W. Sorry for my poor english.
I have database in SQL server 2000 wich user can access with ASP
page. They connect to the database it and return some info.
I got, in SQL Server, a job that run 1 DTS and some stored procedur. It is
running each hour in a day.
I dont know why but when they are web connection to my databse the job
just hang to Step 1 and never end. I have to Stop the Job and restart it.
Again,
if user are connected to my database, the Job just hang. The weird part is
that I can
run the DTS included in the job manually everytime whitout error, even if
user are connected to it ?!
Someone got the same problem
Should I disconnect all user before runnung the job ?
If yes, how can I do that ?
Thanks
Richard
First of all enable DTS package logging as specified in BOL:
To enable package logging
Open the Data Transformation Services (DTS) package for which you want to create a log.
On the Package menu, click Properties to display the DTS Package Properties dialog box.
Do one of the following:
Save package logs to Microsoft? SQL Server? by clicking the Logging tab, selecting the Log package execution to SQLServer check box, and then clicking an available server on which to save the package logs.
Security Note When possible, use Windows Authentication.
Save package logs to SQL Server 2000 Meta Data Services by clicking the Advanced tab, and then selecting the Show lineage variables as source columns and Write lineage to repository check boxes. On the Package menu, click Save As, and then in the Save DTS
Package dialog box, in the Location list, select Meta Data Services.
,... in order to findout the flow of DTS package.
Also refer to SQLAgent log and SQL Server error log for any information on this behaviour.
"Richard Martin" wrote:
> Hi all,
> B.T.W. Sorry for my poor english.
> I have database in SQL server 2000 wich user can access with ASP
> page. They connect to the database it and return some info.
> I got, in SQL Server, a job that run 1 DTS and some stored procedur. It is
> running each hour in a day.
> I dont know why but when they are web connection to my databse the job
> just hang to Step 1 and never end. I have to Stop the Job and restart it.
> Again,
> if user are connected to my database, the Job just hang. The weird part is
> that I can
> run the DTS included in the job manually everytime whitout error, even if
> user are connected to it ?!
> Someone got the same problem
> Should I disconnect all user before runnung the job ?
> If yes, how can I do that ?
>
> Thanks
> Richard
>
>
Job Hang when user connected
Hi all,
B.T.W. Sorry for my poor english.
I have database in SQL server 2000 wich user can access with ASP
page. They connect to the database it and return some info.
I got, in SQL Server, a job that run 1 DTS and some stored procedur. It is
running each hour in a day.
I dont know why but when they are web connection to my databse the job
just hang to Step 1 and never end. I have to Stop the Job and restart it.
Again,
if user are connected to my database, the Job just hang. The weird part is
that I can
run the DTS included in the job manually everytime whitout error, even if
user are connected to it ?!
Someone got the same problem
Should I disconnect all user before runnung the job ?
If yes, how can I do that ?
Thanks
RichardFirst of all enable DTS package logging as specified in BOL:
[i]To enable package logging
Open the Data Transformation Services (DTS) package for which you want to cr
eate a log.
On the Package menu, click Properties to display the DTS Package Properties
dialog box.
Do one of the following:
Save package logs to Microsoft? SQL Server? by clicking the Logging tab,
selecting the Log package execution to SQLServer check box, and then clickin
g an available server on which to save the package logs.
Security Note When possible, use Windows Authentication.
Save package logs to SQL Server 2000 Meta Data Services by clicking the Adva
nced tab, and then selecting the Show lineage variables as source columns an
d Write lineage to repository check boxes. On the Package menu, click Save A
s, and then in the Save DTS
Package dialog box, in the Location list, select Meta Data Services.
[/i]
,... in order to findout the flow of DTS package.
Also refer to SQLAgent log and SQL Server error log for any information on t
his behaviour.
"Richard Martin" wrote:
> Hi all,
> B.T.W. Sorry for my poor english.
> I have database in SQL server 2000 wich user can access with ASP
> page. They connect to the database it and return some info.
> I got, in SQL Server, a job that run 1 DTS and some stored procedur. It i
s
> running each hour in a day.
> I dont know why but when they are web connection to my databse the job
> just hang to Step 1 and never end. I have to Stop the Job and restart it.
> Again,
> if user are connected to my database, the Job just hang. The weird part is
> that I can
> run the DTS included in the job manually everytime whitout error, even if
> user are connected to it ?!
> Someone got the same problem
> Should I disconnect all user before runnung the job ?
> If yes, how can I do that ?
>
> Thanks
> Richard
>
>
B.T.W. Sorry for my poor english.
I have database in SQL server 2000 wich user can access with ASP
page. They connect to the database it and return some info.
I got, in SQL Server, a job that run 1 DTS and some stored procedur. It is
running each hour in a day.
I dont know why but when they are web connection to my databse the job
just hang to Step 1 and never end. I have to Stop the Job and restart it.
Again,
if user are connected to my database, the Job just hang. The weird part is
that I can
run the DTS included in the job manually everytime whitout error, even if
user are connected to it ?!
Someone got the same problem
Should I disconnect all user before runnung the job ?
If yes, how can I do that ?
Thanks
RichardFirst of all enable DTS package logging as specified in BOL:
[i]To enable package logging
Open the Data Transformation Services (DTS) package for which you want to cr
eate a log.
On the Package menu, click Properties to display the DTS Package Properties
dialog box.
Do one of the following:
Save package logs to Microsoft? SQL Server? by clicking the Logging tab,
selecting the Log package execution to SQLServer check box, and then clickin
g an available server on which to save the package logs.
Security Note When possible, use Windows Authentication.
Save package logs to SQL Server 2000 Meta Data Services by clicking the Adva
nced tab, and then selecting the Show lineage variables as source columns an
d Write lineage to repository check boxes. On the Package menu, click Save A
s, and then in the Save DTS
Package dialog box, in the Location list, select Meta Data Services.
[/i]
,... in order to findout the flow of DTS package.
Also refer to SQLAgent log and SQL Server error log for any information on t
his behaviour.
"Richard Martin" wrote:
> Hi all,
> B.T.W. Sorry for my poor english.
> I have database in SQL server 2000 wich user can access with ASP
> page. They connect to the database it and return some info.
> I got, in SQL Server, a job that run 1 DTS and some stored procedur. It i
s
> running each hour in a day.
> I dont know why but when they are web connection to my databse the job
> just hang to Step 1 and never end. I have to Stop the Job and restart it.
> Again,
> if user are connected to my database, the Job just hang. The weird part is
> that I can
> run the DTS included in the job manually everytime whitout error, even if
> user are connected to it ?!
> Someone got the same problem
> Should I disconnect all user before runnung the job ?
> If yes, how can I do that ?
>
> Thanks
> Richard
>
>
Friday, March 9, 2012
job failed to start - access denied
Hi,
the SQL Server Agent job that runs a backup to a network
drive wont start.The syntax is as follows :
BACKUP DATABASE Mydb
TO
DISK='\\ServerName\BACKUP\MyDB.BAK'
WITH INIT
In the error log and in the event viewer I can see the
following error message :operating system error 5 ( access
is denied). I can not figure it out why I can not run this
job since the SQL Server Agent startup account use the
domain user account that has sufficient permissions across
the domain and the job itself is owned by the same account.
Is there something else that I should pay attention to ?
Any view is apreciatedThe backup command runs under the security context of the SQL Server service
account. Ensure that account has the needed permissions.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mirko" <anonymous@.discussions.microsoft.com> wrote in message
news:023c01c3b978$a2a45a80$a001280a@.phx.gbl...
> Hi,
> the SQL Server Agent job that runs a backup to a network
> drive wont start.The syntax is as follows :
> BACKUP DATABASE Mydb
> TO
> DISK='\\ServerName\BACKUP\MyDB.BAK'
> WITH INIT
> In the error log and in the event viewer I can see the
> following error message :operating system error 5 ( access
> is denied). I can not figure it out why I can not run this
> job since the SQL Server Agent startup account use the
> domain user account that has sufficient permissions across
> the domain and the job itself is owned by the same account.
> Is there something else that I should pay attention to ?
> Any view is apreciated
>
the SQL Server Agent job that runs a backup to a network
drive wont start.The syntax is as follows :
BACKUP DATABASE Mydb
TO
DISK='\\ServerName\BACKUP\MyDB.BAK'
WITH INIT
In the error log and in the event viewer I can see the
following error message :operating system error 5 ( access
is denied). I can not figure it out why I can not run this
job since the SQL Server Agent startup account use the
domain user account that has sufficient permissions across
the domain and the job itself is owned by the same account.
Is there something else that I should pay attention to ?
Any view is apreciatedThe backup command runs under the security context of the SQL Server service
account. Ensure that account has the needed permissions.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Mirko" <anonymous@.discussions.microsoft.com> wrote in message
news:023c01c3b978$a2a45a80$a001280a@.phx.gbl...
> Hi,
> the SQL Server Agent job that runs a backup to a network
> drive wont start.The syntax is as follows :
> BACKUP DATABASE Mydb
> TO
> DISK='\\ServerName\BACKUP\MyDB.BAK'
> WITH INIT
> In the error log and in the event viewer I can see the
> following error message :operating system error 5 ( access
> is denied). I can not figure it out why I can not run this
> job since the SQL Server Agent startup account use the
> domain user account that has sufficient permissions across
> the domain and the job itself is owned by the same account.
> Is there something else that I should pay attention to ?
> Any view is apreciated
>
Wednesday, March 7, 2012
job access
I thought I had read that in SQL 2k5 we would be able to give a non-sa user
access to specific jobs, but I can't find how to do this. Is it
possible? If so, can someone tell me how?
Thanks, AndreHi
You have to create proxy account. Lookup BOL
"Andre" <nospam@.spam.com> wrote in message
news:%23zuEqCe2HHA.3900@.TK2MSFTNGP02.phx.gbl...
>I thought I had read that in SQL 2k5 we would be able to give a non-sa user
> access to specific jobs, but I can't find how to do this. Is it
> possible? If so, can someone tell me how?
> Thanks, Andre
>
>|||Yes...different levels of access based on different
configuration options of proxies or using the new database
roles in msdb. Check the following topics in books online:
How to: Configure a User to Create and Manage SQL Server
Agent Jobs (SQL Server Management Studio)
SQL Server Agent Fixed Database Roles
Creating SQL Server Agent Proxies
-Sue
On Wed, 8 Aug 2007 10:22:15 -0700, "Andre" <nospam@.spam.com>
wrote:
>I thought I had read that in SQL 2k5 we would be able to give a non-sa user
>access to specific jobs, but I can't find how to do this. Is it
>possible? If so, can someone tell me how?
>Thanks, Andre
>|||Thanks Sue - I'll check this out.
Andre
access to specific jobs, but I can't find how to do this. Is it
possible? If so, can someone tell me how?
Thanks, AndreHi
You have to create proxy account. Lookup BOL
"Andre" <nospam@.spam.com> wrote in message
news:%23zuEqCe2HHA.3900@.TK2MSFTNGP02.phx.gbl...
>I thought I had read that in SQL 2k5 we would be able to give a non-sa user
> access to specific jobs, but I can't find how to do this. Is it
> possible? If so, can someone tell me how?
> Thanks, Andre
>
>|||Yes...different levels of access based on different
configuration options of proxies or using the new database
roles in msdb. Check the following topics in books online:
How to: Configure a User to Create and Manage SQL Server
Agent Jobs (SQL Server Management Studio)
SQL Server Agent Fixed Database Roles
Creating SQL Server Agent Proxies
-Sue
On Wed, 8 Aug 2007 10:22:15 -0700, "Andre" <nospam@.spam.com>
wrote:
>I thought I had read that in SQL 2k5 we would be able to give a non-sa user
>access to specific jobs, but I can't find how to do this. Is it
>possible? If so, can someone tell me how?
>Thanks, Andre
>|||Thanks Sue - I'll check this out.
Andre
Friday, February 24, 2012
jet to sql
I have an access dabase, and need to do the following:
In access I dim a database and a recordset. I then use seek to find if a
record exist.
If it exist I do an edit, if it doesn't exist, I do an addnew. I think any
programmer here gets the idea.
Now, How do I do this in the sql language used with sql server. Above, this
is all done in background, no user interface.
In other words, put in plain english:
Hey database, does TRIP_ID varid exist (seek varid in other words say varid
might be 55147 or whatever here.)
if so
edit
edit record here in code, no user interface.
else
addnew
add record here no user interface. Above isn't the code, but what I'm after
in english. It all works now using DAO.
NOTE: the data is plucked from an open form and stored into a different
table . It needs done like this because another time another table is
used. Trust me, it has to be seperate, not stored in same table open form
is based on. Key billing info is retrieved into a seperate table from more
than one source table. Please don't respond with using one table only.
I saw no seek or find commands in sql.
thanks, a long time programmer who got stumped.[posted and mailed, please reply in news]
JIMMIE WHITAKER (kpsklab@.worldnet.att.net) writes:
> Hey database, does TRIP_ID varid exist (seek varid in other words say
> varid might be 55147 or whatever here.)
> if so
> edit
> edit record here in code, no user interface.
> else
> addnew
> add record here no user interface. Above isn't the code, but what I'm
> after in english. It all works now using DAO.
IF EXISTS (SELECT * FROM tbl WHERE keycol = @.keyval)
BEGIN
UPDATE tbl
SET col1 = @.newval1,
col2 = @.newval2,
...
FROM tbl
WHERE keycol = @.keyval
END
ELSE
BEGIN
INSERT tbl (keycol, col1, col2, ...)
VALUES (@.keyval, @.newval1, @.newval2, ...)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
jet oledb from tsql
I wish to create a new access database each week whos name includes the
curent date, then to create 7 tables in the database eg '27 Jul 2006.mdb' on
e
for each day
I then wish to insert rows into these tables,
currently I use managed adox & ado to do this, is it possible to use tsql to
get
sql server write the files from its oledb drivers, and would there be any
problems
doing this
in particular I do not know what arguments to pass to opendatasource to
create a new database.Hello Nick,
I understand that you 'd like to create Access database and access it from
SQL Server via TSQL directly. If it is not correct, let me know.
You may want to create linked server to Access database or use open
openquery/openrowset or four-part name syntax to query access database.
Please refer to teh following links for more informaitoin:
http://msdn2.microsoft.com/en-us/library/ms190479.aspx
287414 You cannot link tables into a Microsoft Access project by using an
ODBC Data Source Name (DSN)
http://support.microsoft.com/defaul...kb;EN-US;287414
As for creating database, it is not feasible to create directly from
opendatasource etc from SQL Server. You need to use adox/ado to do this as
you'v done.
317881 How to create an Access database by using ADOX and Visual C# .NET
http://support.microsoft.com/defaul...kb;EN-US;317881
If you have further questions, please let's know.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Nick,
I understand that you 'd like to create Access database and access it from
SQL Server via TSQL directly. If it is not correct, let me know.
You may want to create linked server to Access database or use open
openquery/openrowset or four-part name syntax to query access database.
Please refer to teh following links for more informaitoin:
http://msdn2.microsoft.com/en-us/library/ms190479.aspx
287414 You cannot link tables into a Microsoft Access project by using an
ODBC Data Source Name (DSN)
http://support.microsoft.com/defaul...kb;EN-US;287414
As for creating database, it is not feasible to create directly from
opendatasource etc from SQL Server. You need to use adox/ado to do this as
you'v done.
317881 How to create an Access database by using ADOX and Visual C# .NET
http://support.microsoft.com/defaul...kb;EN-US;317881
If you have further questions, please let's know.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||I have posted following question in sqlprogramming but have
not recieved a response so wondered whether I have posted
it correctly
https://msdn.microsoft.com/newsgrou...59-dfac726d711e
Please help,
since the introduction of SQL2005 the default mode for opening access mdb
databases with Openrowset appears to be in exclusive open mode
I wish to open the database in share mode so the other (non net) ADO threads
can open the database at the same time
I only wish to open the database in read only mode
and so to use the mode param
I have tried the following
SELECT COUNT(*)
FROM
OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0',
'"Database = h:\alarmlogfiles\new folder (3)\alarms 04 feb
2007.mdb; Mode = Share Deny None;"'
, 'SELECT [ID],
[System],
[Group],
[Message],
[Acknowledged],
[AckReq],
[Duplicates],
[Group Name],
[Importance],
[Ms],
[Alarm Date],
[LastDupe],
[Viewers],
[Row_Date],
[Colours],
[Status] From [Alarms 08 Feb 2007] Where 1 = 0' )
but get the following error from sql2005 & sql 2000
Server: Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Could not find installable ISAM.".
SELECT CustomerID, CompanyName
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
'admin';'',Customers)
GO
but there is no opportunity here to add a open mode argument?
please help
best wishes nick
""privatenews"" wrote:
[vbcol=seagreen]
> Hello Nick,
> I understand that you 'd like to create Access database and access it from
> SQL Server via TSQL directly. If it is not correct, let me know.
> You may want to create linked server to Access database or use open
> openquery/openrowset or four-part name syntax to query access database.
> Please refer to teh following links for more informaitoin:
> http://msdn2.microsoft.com/en-us/library/ms190479.aspx
> 287414 You cannot link tables into a Microsoft Access project by using an
> ODBC Data Source Name (DSN)
> http://support.microsoft.com/defaul...kb;EN-US;287414
> As for creating database, it is not feasible to create directly from
> opendatasource etc from SQL Server. You need to use adox/ado to do this as
> you'v done.
> 317881 How to create an Access database by using ADOX and Visual C# .NET
> http://support.microsoft.com/defaul...kb;EN-US;317881
> If you have further questions, please let's know.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
>|||Hello Nick,
Sorry for the late response and it seems there is sync issue in our
internal application for your account. I'v reported this issue to the
related team.
Per your question, I was able to reproduce the issue on my side. It seems
that we are not able to use provider or connection string in the
"datasource" parameter. It also doesn't work if I use provider string
directly:
SELECT CustomerID, CompanyName from
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Data Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data
Source=C:\northwind.mdb;Passord=""',
'select * from customers')
I'v forward your feedback to the product team. In the meantime, I also
encourage you submit via the link below
http://lab.msdn.microsoft.com/produ...ck/default.aspx
Please let's know if you have any further feedback.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============
curent date, then to create 7 tables in the database eg '27 Jul 2006.mdb' on
e
for each day
I then wish to insert rows into these tables,
currently I use managed adox & ado to do this, is it possible to use tsql to
get
sql server write the files from its oledb drivers, and would there be any
problems
doing this
in particular I do not know what arguments to pass to opendatasource to
create a new database.Hello Nick,
I understand that you 'd like to create Access database and access it from
SQL Server via TSQL directly. If it is not correct, let me know.
You may want to create linked server to Access database or use open
openquery/openrowset or four-part name syntax to query access database.
Please refer to teh following links for more informaitoin:
http://msdn2.microsoft.com/en-us/library/ms190479.aspx
287414 You cannot link tables into a Microsoft Access project by using an
ODBC Data Source Name (DSN)
http://support.microsoft.com/defaul...kb;EN-US;287414
As for creating database, it is not feasible to create directly from
opendatasource etc from SQL Server. You need to use adox/ado to do this as
you'v done.
317881 How to create an Access database by using ADOX and Visual C# .NET
http://support.microsoft.com/defaul...kb;EN-US;317881
If you have further questions, please let's know.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Nick,
I understand that you 'd like to create Access database and access it from
SQL Server via TSQL directly. If it is not correct, let me know.
You may want to create linked server to Access database or use open
openquery/openrowset or four-part name syntax to query access database.
Please refer to teh following links for more informaitoin:
http://msdn2.microsoft.com/en-us/library/ms190479.aspx
287414 You cannot link tables into a Microsoft Access project by using an
ODBC Data Source Name (DSN)
http://support.microsoft.com/defaul...kb;EN-US;287414
As for creating database, it is not feasible to create directly from
opendatasource etc from SQL Server. You need to use adox/ado to do this as
you'v done.
317881 How to create an Access database by using ADOX and Visual C# .NET
http://support.microsoft.com/defaul...kb;EN-US;317881
If you have further questions, please let's know.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||I have posted following question in sqlprogramming but have
not recieved a response so wondered whether I have posted
it correctly
https://msdn.microsoft.com/newsgrou...59-dfac726d711e
Please help,
since the introduction of SQL2005 the default mode for opening access mdb
databases with Openrowset appears to be in exclusive open mode
I wish to open the database in share mode so the other (non net) ADO threads
can open the database at the same time
I only wish to open the database in read only mode
and so to use the mode param
I have tried the following
SELECT COUNT(*)
FROM
OPENROWSET ( 'Microsoft.Jet.OLEDB.4.0',
'"Database = h:\alarmlogfiles\new folder (3)\alarms 04 feb
2007.mdb; Mode = Share Deny None;"'
, 'SELECT [ID],
[System],
[Group],
[Message],
[Acknowledged],
[AckReq],
[Duplicates],
[Group Name],
[Importance],
[Ms],
[Alarm Date],
[LastDupe],
[Viewers],
[Row_Date],
[Colours],
[Status] From [Alarms 08 Feb 2007] Where 1 = 0' )
but get the following error from sql2005 & sql 2000
Server: Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Could not find installable ISAM.".
SELECT CustomerID, CompanyName
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
'admin';'',Customers)
GO
but there is no opportunity here to add a open mode argument?
please help
best wishes nick
""privatenews"" wrote:
[vbcol=seagreen]
> Hello Nick,
> I understand that you 'd like to create Access database and access it from
> SQL Server via TSQL directly. If it is not correct, let me know.
> You may want to create linked server to Access database or use open
> openquery/openrowset or four-part name syntax to query access database.
> Please refer to teh following links for more informaitoin:
> http://msdn2.microsoft.com/en-us/library/ms190479.aspx
> 287414 You cannot link tables into a Microsoft Access project by using an
> ODBC Data Source Name (DSN)
> http://support.microsoft.com/defaul...kb;EN-US;287414
> As for creating database, it is not feasible to create directly from
> opendatasource etc from SQL Server. You need to use adox/ado to do this as
> you'v done.
> 317881 How to create an Access database by using ADOX and Visual C# .NET
> http://support.microsoft.com/defaul...kb;EN-US;317881
> If you have further questions, please let's know.
> Best Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
>|||Hello Nick,
Sorry for the late response and it seems there is sync issue in our
internal application for your account. I'v reported this issue to the
related team.
Per your question, I was able to reproduce the issue on my side. It seems
that we are not able to use provider or connection string in the
"datasource" parameter. It also doesn't work if I use provider string
directly:
SELECT CustomerID, CompanyName from
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Data Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data
Source=C:\northwind.mdb;Passord=""',
'select * from customers')
I'v forward your feedback to the product team. In the meantime, I also
encourage you submit via the link below
http://lab.msdn.microsoft.com/produ...ck/default.aspx
Please let's know if you have any further feedback.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============
JET DB initilization problem help!!!
Hi I have set up a access database on a doc library and a infopath form based on this db published to a nearby folder. When I go to fill out the form, and it opens, I hit submit. But then it gives me a JET DB initilization form with login of admin and a blank pass. When I hit ok, it gives me a error saying
infopath The Microsoft Jet database engine cannot open the file '\\207.245.47.37\sites\Safety_and_Training\Experimental\db1.mdb'. It is already opened exclusively by another user, or you need permission to view its data.
For some reason, once or twice its randomly been able to submit succcessfully but I havnt been able to reproduce the situation. Are there files that i am missing? any permissions that I need to check? The access database only has a admin and no password account. PLEASE HELP ME ive been working on this for ages! If you know a solid workaround with little programming let me know.
Hi,
This is a SQL Server forum, i suggest you direct your question to an Access specific forum where you should find someone able to help.
Cheers,
Leigh
Jet datasource on x64
Running SSRS on a 64bit machine. Have a legacy MS Access database that
we need to use as a datasource for select reports. Appears that Jet
was not ported to x64. Is my only recourse for connecting from SSRS to
Jet to uninstall SSRS x64 and install 32bit version?On Aug 22, 9:30 am, Jim <emailje...@.yahoo.com> wrote:
> Running SSRS on a 64bit machine. Have a legacy MS Access database that
> we need to use as a datasource for select reports. Appears that Jet
> was not ported to x64. Is my only recourse for connecting from SSRS to
> Jet to uninstall SSRS x64 and install 32bit version?
Just a quick followup that I decided against the 32bit version of SSRS
and instead installed an instance of SQL Express which runs 32 bit
inside WOW. SQL Express can use Jet as a linked server. and x64 SQL
Server can link to 32bit express. Not ideal, but gets me the data I
need untill we can do away with MS Access/Jet.
Cheers
we need to use as a datasource for select reports. Appears that Jet
was not ported to x64. Is my only recourse for connecting from SSRS to
Jet to uninstall SSRS x64 and install 32bit version?On Aug 22, 9:30 am, Jim <emailje...@.yahoo.com> wrote:
> Running SSRS on a 64bit machine. Have a legacy MS Access database that
> we need to use as a datasource for select reports. Appears that Jet
> was not ported to x64. Is my only recourse for connecting from SSRS to
> Jet to uninstall SSRS x64 and install 32bit version?
Just a quick followup that I decided against the 32bit version of SSRS
and instead installed an instance of SQL Express which runs 32 bit
inside WOW. SQL Express can use Jet as a linked server. and x64 SQL
Server can link to 32bit express. Not ideal, but gets me the data I
need untill we can do away with MS Access/Jet.
Cheers
JET Anonymous Merge: Can It Be Done?
Is it possible to set up
Publisher: SQL Server 2000
Subscriber: Access 97
Merge replication
Anonymous pull subscriptions
Over the Internet via VPN
I've read the BOL and other on-line artices, but don't quite get it.
Links to specific examples?
Thanks
Nope, you might be able to do a push if you could map a drive. The problem
is the linked server definition to enable access as a database you need to
use if you are replicating to a access database. Review the sample chapter
download in the nwsu link for my book for more info on how to replicate to
access.
The problem is in your term anonymous pull. For a pull to work you must have
SQL Server or MSDE installed on the remote machine.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<richerwin@.ubs-europe.org> wrote in message
news:1107726821.588526.272280@.l41g2000cwc.googlegr oups.com...
> Is it possible to set up
> Publisher: SQL Server 2000
> Subscriber: Access 97
> Merge replication
> Anonymous pull subscriptions
> Over the Internet via VPN
> I've read the BOL and other on-line artices, but don't quite get it.
> Links to specific examples?
> Thanks
>
Publisher: SQL Server 2000
Subscriber: Access 97
Merge replication
Anonymous pull subscriptions
Over the Internet via VPN
I've read the BOL and other on-line artices, but don't quite get it.
Links to specific examples?
Thanks
Nope, you might be able to do a push if you could map a drive. The problem
is the linked server definition to enable access as a database you need to
use if you are replicating to a access database. Review the sample chapter
download in the nwsu link for my book for more info on how to replicate to
access.
The problem is in your term anonymous pull. For a pull to work you must have
SQL Server or MSDE installed on the remote machine.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<richerwin@.ubs-europe.org> wrote in message
news:1107726821.588526.272280@.l41g2000cwc.googlegr oups.com...
> Is it possible to set up
> Publisher: SQL Server 2000
> Subscriber: Access 97
> Merge replication
> Anonymous pull subscriptions
> Over the Internet via VPN
> I've read the BOL and other on-line artices, but don't quite get it.
> Links to specific examples?
> Thanks
>
jdbc with datetime field - unwanted timezone conversion
I have a database which contains records each of which has a datetime field.
The database machine is set up as GMT. I access this data from a different
machine which is running in a different timezone (EST for example). The
value returned by the database appears to be offset by some value based on
the local machine's timezone. I just want the raw GMT value regardless of
what timezone the machine running the jdbc driver is running. Any
suggestions how I do this?
I'm using resultset.getTimestamp() and pushing that to a Calendar object if
that helps.
| Thread-Topic: jdbc with datetime field - unwanted timezone conversion
| thread-index: AcSMj5gnHbLf5bAYTqOU3MRZc9R5+Q==
| X-WBNR-Posting-Host: 209.63.42.209
| From: =?Utf-8?B?VmlydHVhbCBQcm9ncmFtbWVy?= <Virtual
Programmer@.discussions.microsoft.com>
| Subject: jdbc with datetime field - unwanted timezone conversion
| Date: Fri, 27 Aug 2004 16:43:03 -0700
| Lines: 10
| Message-ID: <32B0C910-3926-4060-A13F-4014058F720C@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6281
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| I have a database which contains records each of which has a datetime
field.
| The database machine is set up as GMT. I access this data from a
different
| machine which is running in a different timezone (EST for example). The
| value returned by the database appears to be offset by some value based
on
| the local machine's timezone. I just want the raw GMT value regardless
of
| what timezone the machine running the jdbc driver is running. Any
| suggestions how I do this?
|
| I'm using resultset.getTimestamp() and pushing that to a Calendar object
if
| that helps.
|
If you directly output the "resultset.getTimestamp()" before passing it to
the Calendar object, what is the value that is returned? Can you post the
code you are using to demonstrate this problem? If I change the Regional
Settings on my remote SQL Server to use GMT and then insert a date value, I
can query the information from a separate client machine (in a different
time zone) without a problem.
//create table datetest(col1 datetime)
//go
//insert datetest select getdate()
Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver" );
Connection connection =
DriverManager.getConnection("jdbc:microsoft:sqlser ver://remoteSQL:1433;datab
asename=jdbc", "sa", "password");
// Query the database
Statement stmt = connection.createStatement();
ResultSet rs = null;
rs = stmt.executeQuery("select col1 from datetest");
while (rs.next())
{
Timestamp ts = rs.getTimestamp(1);
System.out.println(ts);
}
rs.close();
stmt.close();
connection.close();
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
|||Someone in our local user group has suggested what is going on and based on
the evidence, I think he's right (we just figured it out today). Essentially
the issue is this.
The date information is in a datetime field in a SQL Server 2000 database on
a different machine. As I read the docs on the datetime type, there is no
associated timezone. So, when the jdbc driver on my machine gets the value
from the remote database, it has to guess what the timezone is and makes the
assumption that the datetime field in the database server has the same
timezone as the server running the jdbc driver. Unfortunately, in our case,
that isn't true. I know that the value in the database is GMT. If I offset
the value received from the database by the number of milliseconds to GMT
based on the timezone on my machine (in my case, 6 hours from MDT to GMT),
then java shows the correct value.
Your code below is essentially what I did originally. It probably works
fine when you store the datetime from your machine to the remote server and
then it read it back or when all the machines are on the same timezone.
However, try writing the date from the database machine running GMT and then
it read it back on a different machine running a different timezone. Bet you
don't get the same time back.
Someone suggested I try using the getTimestamp() that has a second parameter
as a Calendar with the timezone of the datetime in the database (in my case,
I'd specify GMT). I haven't tried it yet, but the description in the
javadocs sounds promising. I do know that using a TimeZone object to get the
offset to GMT (don't forget to adjust for DST) and using Calendar.add() to
adjust the time seems to solve the problem though it feels kludgy.
Cheers
""Carb Simien [MSFT]"" wrote:
> --
> | Thread-Topic: jdbc with datetime field - unwanted timezone conversion
> | thread-index: AcSMj5gnHbLf5bAYTqOU3MRZc9R5+Q==
> | X-WBNR-Posting-Host: 209.63.42.209
> | From: =?Utf-8?B?VmlydHVhbCBQcm9ncmFtbWVy?= <Virtual
> Programmer@.discussions.microsoft.com>
> | Subject: jdbc with datetime field - unwanted timezone conversion
> | Date: Fri, 27 Aug 2004 16:43:03 -0700
> | Lines: 10
> | Message-ID: <32B0C910-3926-4060-A13F-4014058F720C@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.jdbcdriver
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6281
> | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
> |
> | I have a database which contains records each of which has a datetime
> field.
> | The database machine is set up as GMT. I access this data from a
> different
> | machine which is running in a different timezone (EST for example). The
> | value returned by the database appears to be offset by some value based
> on
> | the local machine's timezone. I just want the raw GMT value regardless
> of
> | what timezone the machine running the jdbc driver is running. Any
> | suggestions how I do this?
> |
> | I'm using resultset.getTimestamp() and pushing that to a Calendar object
> if
> | that helps.
> |
> If you directly output the "resultset.getTimestamp()" before passing it to
> the Calendar object, what is the value that is returned? Can you post the
> code you are using to demonstrate this problem? If I change the Regional
> Settings on my remote SQL Server to use GMT and then insert a date value, I
> can query the information from a separate client machine (in a different
> time zone) without a problem.
> //create table datetest(col1 datetime)
> //go
> //insert datetest select getdate()
> Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver" );
> Connection connection =
> DriverManager.getConnection("jdbc:microsoft:sqlser ver://remoteSQL:1433;datab
> asename=jdbc", "sa", "password");
> // Query the database
> Statement stmt = connection.createStatement();
> ResultSet rs = null;
> rs = stmt.executeQuery("select col1 from datetest");
> while (rs.next())
> {
> Timestamp ts = rs.getTimestamp(1);
> System.out.println(ts);
> }
> rs.close();
> stmt.close();
> connection.close();
> Carb Simien, MCSE MCDBA MCAD
> Microsoft Developer Support - Web Data
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
>
The database machine is set up as GMT. I access this data from a different
machine which is running in a different timezone (EST for example). The
value returned by the database appears to be offset by some value based on
the local machine's timezone. I just want the raw GMT value regardless of
what timezone the machine running the jdbc driver is running. Any
suggestions how I do this?
I'm using resultset.getTimestamp() and pushing that to a Calendar object if
that helps.
| Thread-Topic: jdbc with datetime field - unwanted timezone conversion
| thread-index: AcSMj5gnHbLf5bAYTqOU3MRZc9R5+Q==
| X-WBNR-Posting-Host: 209.63.42.209
| From: =?Utf-8?B?VmlydHVhbCBQcm9ncmFtbWVy?= <Virtual
Programmer@.discussions.microsoft.com>
| Subject: jdbc with datetime field - unwanted timezone conversion
| Date: Fri, 27 Aug 2004 16:43:03 -0700
| Lines: 10
| Message-ID: <32B0C910-3926-4060-A13F-4014058F720C@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6281
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| I have a database which contains records each of which has a datetime
field.
| The database machine is set up as GMT. I access this data from a
different
| machine which is running in a different timezone (EST for example). The
| value returned by the database appears to be offset by some value based
on
| the local machine's timezone. I just want the raw GMT value regardless
of
| what timezone the machine running the jdbc driver is running. Any
| suggestions how I do this?
|
| I'm using resultset.getTimestamp() and pushing that to a Calendar object
if
| that helps.
|
If you directly output the "resultset.getTimestamp()" before passing it to
the Calendar object, what is the value that is returned? Can you post the
code you are using to demonstrate this problem? If I change the Regional
Settings on my remote SQL Server to use GMT and then insert a date value, I
can query the information from a separate client machine (in a different
time zone) without a problem.
//create table datetest(col1 datetime)
//go
//insert datetest select getdate()
Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver" );
Connection connection =
DriverManager.getConnection("jdbc:microsoft:sqlser ver://remoteSQL:1433;datab
asename=jdbc", "sa", "password");
// Query the database
Statement stmt = connection.createStatement();
ResultSet rs = null;
rs = stmt.executeQuery("select col1 from datetest");
while (rs.next())
{
Timestamp ts = rs.getTimestamp(1);
System.out.println(ts);
}
rs.close();
stmt.close();
connection.close();
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
|||Someone in our local user group has suggested what is going on and based on
the evidence, I think he's right (we just figured it out today). Essentially
the issue is this.
The date information is in a datetime field in a SQL Server 2000 database on
a different machine. As I read the docs on the datetime type, there is no
associated timezone. So, when the jdbc driver on my machine gets the value
from the remote database, it has to guess what the timezone is and makes the
assumption that the datetime field in the database server has the same
timezone as the server running the jdbc driver. Unfortunately, in our case,
that isn't true. I know that the value in the database is GMT. If I offset
the value received from the database by the number of milliseconds to GMT
based on the timezone on my machine (in my case, 6 hours from MDT to GMT),
then java shows the correct value.
Your code below is essentially what I did originally. It probably works
fine when you store the datetime from your machine to the remote server and
then it read it back or when all the machines are on the same timezone.
However, try writing the date from the database machine running GMT and then
it read it back on a different machine running a different timezone. Bet you
don't get the same time back.
Someone suggested I try using the getTimestamp() that has a second parameter
as a Calendar with the timezone of the datetime in the database (in my case,
I'd specify GMT). I haven't tried it yet, but the description in the
javadocs sounds promising. I do know that using a TimeZone object to get the
offset to GMT (don't forget to adjust for DST) and using Calendar.add() to
adjust the time seems to solve the problem though it feels kludgy.
Cheers
""Carb Simien [MSFT]"" wrote:
> --
> | Thread-Topic: jdbc with datetime field - unwanted timezone conversion
> | thread-index: AcSMj5gnHbLf5bAYTqOU3MRZc9R5+Q==
> | X-WBNR-Posting-Host: 209.63.42.209
> | From: =?Utf-8?B?VmlydHVhbCBQcm9ncmFtbWVy?= <Virtual
> Programmer@.discussions.microsoft.com>
> | Subject: jdbc with datetime field - unwanted timezone conversion
> | Date: Fri, 27 Aug 2004 16:43:03 -0700
> | Lines: 10
> | Message-ID: <32B0C910-3926-4060-A13F-4014058F720C@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | charset="Utf-8"
> | Content-Transfer-Encoding: 7bit
> | X-Newsreader: Microsoft CDO for Windows 2000
> | Content-Class: urn:content-classes:message
> | Importance: normal
> | Priority: normal
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | Newsgroups: microsoft.public.sqlserver.jdbcdriver
> | NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
> | Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.jdbcdriver:6281
> | X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
> |
> | I have a database which contains records each of which has a datetime
> field.
> | The database machine is set up as GMT. I access this data from a
> different
> | machine which is running in a different timezone (EST for example). The
> | value returned by the database appears to be offset by some value based
> on
> | the local machine's timezone. I just want the raw GMT value regardless
> of
> | what timezone the machine running the jdbc driver is running. Any
> | suggestions how I do this?
> |
> | I'm using resultset.getTimestamp() and pushing that to a Calendar object
> if
> | that helps.
> |
> If you directly output the "resultset.getTimestamp()" before passing it to
> the Calendar object, what is the value that is returned? Can you post the
> code you are using to demonstrate this problem? If I change the Regional
> Settings on my remote SQL Server to use GMT and then insert a date value, I
> can query the information from a separate client machine (in a different
> time zone) without a problem.
> //create table datetest(col1 datetime)
> //go
> //insert datetest select getdate()
> Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver" );
> Connection connection =
> DriverManager.getConnection("jdbc:microsoft:sqlser ver://remoteSQL:1433;datab
> asename=jdbc", "sa", "password");
> // Query the database
> Statement stmt = connection.createStatement();
> ResultSet rs = null;
> rs = stmt.executeQuery("select col1 from datetest");
> while (rs.next())
> {
> Timestamp ts = rs.getTimestamp(1);
> System.out.println(ts);
> }
> rs.close();
> stmt.close();
> connection.close();
> Carb Simien, MCSE MCDBA MCAD
> Microsoft Developer Support - Web Data
> Please reply only to the newsgroups.
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Are you secure? For information about the Strategic Technology Protection
> Program and to order your FREE Security Tool Kit, please visit
> http://www.microsoft.com/security.
>
Monday, February 20, 2012
JDBC for Sql Server Analysis Services 2005
Is there any way to access Sql Server Analysis Services 2005 via JDBC?I do not know of anyway of doing this with our JDBC driver.
Subscribe to:
Posts (Atom)