When I use URL Access to render a report with specified parameters and a
format (e.g. PDF), when I choose "Save" (instead of Open) the default
filename is listed as "ThisReportName.pdf". I need to have the "Save As"
name to be very specific (e.g. include the date and the customer ID, like
EN12345-9-27-07.pdf) and ready to save without any typing by the end-user.
I cannot find a URL parameter that will change the default filename (to be
saved) for an exported PDF. Is this possible with SSRS?
Thanks for any help or direction.On Sep 27, 12:10 pm, "Don Miller" <nos...@.nospam.com> wrote:
> When I use URL Access to render a report with specified parameters and a
> format (e.g. PDF), when I choose "Save" (instead of Open) the default
> filename is listed as "ThisReportName.pdf". I need to have the "Save As"
> name to be very specific (e.g. include the date and the customer ID, like
> EN12345-9-27-07.pdf) and ready to save without any typing by the end-user.
> I cannot find a URL parameter that will change the default filename (to be
> saved) for an exported PDF. Is this possible with SSRS?
> Thanks for any help or direction.
I believe that I answered this question in your other posting.
Regards,
Enrique Martinez
Sr. Software Consultant
Showing posts with label access. Show all posts
Showing posts with label access. Show all posts
Thursday, March 22, 2012
Tuesday, March 20, 2012
Change Datasource abuout url access
Hallo,
We have a ASP.Net app and need a way to manage the datasource.
We have one report for 5 Customer. Every customer has his own DB on our
Server. And Every customer has his own Datasource in the Reportmanager.
Now i call my report with url and will tell the reportservices to take
Connecton 1 or Connection 2 usw.
can i do this with a URL ACCESS ?
UweAbout the only way you could do that now, is with a parameter you pass into
the report...
Still there is a problem... Connections are not dynamic... So you would have
to put Stored procedures or dynamic queries which referred to linked servers
or used openquery, openrowset to move between servers...
SQL 2005 has a dynamic connection capability that will help make this
easier.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Uwe Mielisch" <uwe@.mielisch.de> wrote in message
news:%23UE$dvUiFHA.2916@.TK2MSFTNGP14.phx.gbl...
> Hallo,
> We have a ASP.Net app and need a way to manage the datasource.
> We have one report for 5 Customer. Every customer has his own DB on our
> Server. And Every customer has his own Datasource in the Reportmanager.
> Now i call my report with url and will tell the reportservices to take
> Connecton 1 or Connection 2 usw.
> can i do this with a URL ACCESS ?
> Uwe
>|||Hallo Wayne,
Thank you. What do youn mean with
<parameter you pass into the report...>
Uwe
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> schrieb im Newsbeitrag
news:u1K2jlhiFHA.1412@.TK2MSFTNGP09.phx.gbl...
> About the only way you could do that now, is with a parameter you pass
> into the report...
> Still there is a problem... Connections are not dynamic... So you would
> have to put Stored procedures or dynamic queries which referred to linked
> servers or used openquery, openrowset to move between servers...
> SQL 2005 has a dynamic connection capability that will help make this
> easier.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Uwe Mielisch" <uwe@.mielisch.de> wrote in message
> news:%23UE$dvUiFHA.2916@.TK2MSFTNGP14.phx.gbl...
>> Hallo,
>> We have a ASP.Net app and need a way to manage the datasource.
>> We have one report for 5 Customer. Every customer has his own DB on our
>> Server. And Every customer has his own Datasource in the Reportmanager.
>> Now i call my report with url and will tell the reportservices to take
>> Connecton 1 or Connection 2 usw.
>> can i do this with a URL ACCESS ?
>> Uwe
>
We have a ASP.Net app and need a way to manage the datasource.
We have one report for 5 Customer. Every customer has his own DB on our
Server. And Every customer has his own Datasource in the Reportmanager.
Now i call my report with url and will tell the reportservices to take
Connecton 1 or Connection 2 usw.
can i do this with a URL ACCESS ?
UweAbout the only way you could do that now, is with a parameter you pass into
the report...
Still there is a problem... Connections are not dynamic... So you would have
to put Stored procedures or dynamic queries which referred to linked servers
or used openquery, openrowset to move between servers...
SQL 2005 has a dynamic connection capability that will help make this
easier.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Uwe Mielisch" <uwe@.mielisch.de> wrote in message
news:%23UE$dvUiFHA.2916@.TK2MSFTNGP14.phx.gbl...
> Hallo,
> We have a ASP.Net app and need a way to manage the datasource.
> We have one report for 5 Customer. Every customer has his own DB on our
> Server. And Every customer has his own Datasource in the Reportmanager.
> Now i call my report with url and will tell the reportservices to take
> Connecton 1 or Connection 2 usw.
> can i do this with a URL ACCESS ?
> Uwe
>|||Hallo Wayne,
Thank you. What do youn mean with
<parameter you pass into the report...>
Uwe
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> schrieb im Newsbeitrag
news:u1K2jlhiFHA.1412@.TK2MSFTNGP09.phx.gbl...
> About the only way you could do that now, is with a parameter you pass
> into the report...
> Still there is a problem... Connections are not dynamic... So you would
> have to put Stored procedures or dynamic queries which referred to linked
> servers or used openquery, openrowset to move between servers...
> SQL 2005 has a dynamic connection capability that will help make this
> easier.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Uwe Mielisch" <uwe@.mielisch.de> wrote in message
> news:%23UE$dvUiFHA.2916@.TK2MSFTNGP14.phx.gbl...
>> Hallo,
>> We have a ASP.Net app and need a way to manage the datasource.
>> We have one report for 5 Customer. Every customer has his own DB on our
>> Server. And Every customer has his own Datasource in the Reportmanager.
>> Now i call my report with url and will tell the reportservices to take
>> Connecton 1 or Connection 2 usw.
>> can i do this with a URL ACCESS ?
>> Uwe
>
Change database Access - (newbie question)
You should provide the password for the new user.
This article may help with the error message:
http://support.microsoft.com/default.aspx?kbid=826161
Hope this helps.
Tim
>--Original Message--
>I have added a new User Login in Enterprise Manager. I
>then want to give that User access to a database. When I
>tick the appropriate table in the the Database Access
>property of the User, I am prompted to confirm the
>password.
>Which password is the system expecting here? The user's
>password or the SA passsord? But no matter what I type,
I
>get a message: "The confirmation password is not correct"
>
>
>
>.
>Newbie? I don't think so. This has always bugged the heck out of me. Thanks
for asking!
And, even if it is NOT a new user, when you want to add a database from the
Server Security folder, just be sure to reenter the users password before yo
u hit the "permissions" tab. Then, when you add the database access you desi
re, the prompt for the new
password will accept the password.
David
This article may help with the error message:
http://support.microsoft.com/default.aspx?kbid=826161
Hope this helps.
Tim
>--Original Message--
>I have added a new User Login in Enterprise Manager. I
>then want to give that User access to a database. When I
>tick the appropriate table in the the Database Access
>property of the User, I am prompted to confirm the
>password.
>Which password is the system expecting here? The user's
>password or the SA passsord? But no matter what I type,
I
>get a message: "The confirmation password is not correct"
>
>
>
>.
>Newbie? I don't think so. This has always bugged the heck out of me. Thanks
for asking!
And, even if it is NOT a new user, when you want to add a database from the
Server Security folder, just be sure to reenter the users password before yo
u hit the "permissions" tab. Then, when you add the database access you desi
re, the prompt for the new
password will accept the password.
David
Monday, March 19, 2012
Change database
if you have a database in access, isn't there some easy way to convert it
into SQL server like an import or similar?You can use the Database Upsizing Wizard. See this article:
http://support.microsoft.com/?kbid=237980
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"John" <Johna@.nospam.nospam> wrote in message
news:eDPEzvSoGHA.4728@.TK2MSFTNGP05.phx.gbl...
> if you have a database in access, isn't there some easy way to convert it
> into SQL server like an import or similar?
>|||Yes, try the Upsizing Wizard. Go to Tools, Database Utilities, Upzising
Wizard. You can move all your objects and data and still have your Access
application pointing to the new SQL Server database.
Ben Nevarez, MCDBA, OCP
Database Administrator
"John" wrote:
> if you have a database in access, isn't there some easy way to convert it
> into SQL server like an import or similar?
>
>|||Try the SSMA for Access tool.
http://www.microsoft.com/sql/soluti...on/default.mspx
joe.
"John" <Johna@.nospam.nospam> wrote in message
news:eDPEzvSoGHA.4728@.TK2MSFTNGP05.phx.gbl...
> if you have a database in access, isn't there some easy way to convert it
> into SQL server like an import or similar?
>
into SQL server like an import or similar?You can use the Database Upsizing Wizard. See this article:
http://support.microsoft.com/?kbid=237980
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"John" <Johna@.nospam.nospam> wrote in message
news:eDPEzvSoGHA.4728@.TK2MSFTNGP05.phx.gbl...
> if you have a database in access, isn't there some easy way to convert it
> into SQL server like an import or similar?
>|||Yes, try the Upsizing Wizard. Go to Tools, Database Utilities, Upzising
Wizard. You can move all your objects and data and still have your Access
application pointing to the new SQL Server database.
Ben Nevarez, MCDBA, OCP
Database Administrator
"John" wrote:
> if you have a database in access, isn't there some easy way to convert it
> into SQL server like an import or similar?
>
>|||Try the SSMA for Access tool.
http://www.microsoft.com/sql/soluti...on/default.mspx
joe.
"John" <Johna@.nospam.nospam> wrote in message
news:eDPEzvSoGHA.4728@.TK2MSFTNGP05.phx.gbl...
> if you have a database in access, isn't there some easy way to convert it
> into SQL server like an import or similar?
>
Change database
if you have a database in access, isn't there some easy way to convert it
into SQL server like an import or similar?You can use the Database Upsizing Wizard. See this article:
http://support.microsoft.com/?kbid=237980
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"John" <Johna@.nospam.nospam> wrote in message
news:eDPEzvSoGHA.4728@.TK2MSFTNGP05.phx.gbl...
> if you have a database in access, isn't there some easy way to convert it
> into SQL server like an import or similar?
>|||Yes, try the Upsizing Wizard. Go to Tools, Database Utilities, Upzising
Wizard. You can move all your objects and data and still have your Access
application pointing to the new SQL Server database.
Ben Nevarez, MCDBA, OCP
Database Administrator
"John" wrote:
> if you have a database in access, isn't there some easy way to convert it
> into SQL server like an import or similar?
>
>|||Try the SSMA for Access tool.
http://www.microsoft.com/sql/solutions/migration/default.mspx
joe.
"John" <Johna@.nospam.nospam> wrote in message
news:eDPEzvSoGHA.4728@.TK2MSFTNGP05.phx.gbl...
> if you have a database in access, isn't there some easy way to convert it
> into SQL server like an import or similar?
>
into SQL server like an import or similar?You can use the Database Upsizing Wizard. See this article:
http://support.microsoft.com/?kbid=237980
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"John" <Johna@.nospam.nospam> wrote in message
news:eDPEzvSoGHA.4728@.TK2MSFTNGP05.phx.gbl...
> if you have a database in access, isn't there some easy way to convert it
> into SQL server like an import or similar?
>|||Yes, try the Upsizing Wizard. Go to Tools, Database Utilities, Upzising
Wizard. You can move all your objects and data and still have your Access
application pointing to the new SQL Server database.
Ben Nevarez, MCDBA, OCP
Database Administrator
"John" wrote:
> if you have a database in access, isn't there some easy way to convert it
> into SQL server like an import or similar?
>
>|||Try the SSMA for Access tool.
http://www.microsoft.com/sql/solutions/migration/default.mspx
joe.
"John" <Johna@.nospam.nospam> wrote in message
news:eDPEzvSoGHA.4728@.TK2MSFTNGP05.phx.gbl...
> if you have a database in access, isn't there some easy way to convert it
> into SQL server like an import or similar?
>
Change Data Type
I received a db2 data file that I converted to MS-Access. From Access I
ran the upsize wizard to put the tabel in SQL. It put the table in ok
but all the data types are nvarchar. I have a couple of the fields that
are cureny and some that are numeric.
ran the upsize wizard to put the tabel in SQL. It put the table in ok
but all the data types are nvarchar. I have a couple of the fields that
are cureny and some that are numeric.
I need to change the data types from nvarchar to numeric type fields. I
am new to SQL so I do not know all the commands. How do I change the
data type?
Michael Charney
*** Sent via Developersdex http://www.developersdex.com ***ALTER TABLE table_name ALTER COLUMN col_name VARCHAR(10) ;
--
David Portas
SQL Server MVP
--
"Nothing" <me@.you.com> wrote in message
news:1Je1f.13$vU5.1288@.news.uswest.net...
>I received a db2 data file that I converted to MS-Access. From Access I
> ran the upsize wizard to put the tabel in SQL. It put the table in ok
> but all the data types are nvarchar. I have a couple of the fields that
> are cureny and some that are numeric.
> I need to change the data types from nvarchar to numeric type fields. I
> am new to SQL so I do not know all the commands. How do I change the
> data type?
> Michael Charney
> *** Sent via Developersdex http://www.developersdex.com ***
Change connection information Access 2003 with MSDE
I have a copy of an ADP and MSDE database that I tweak occasionally from an
alternate location. I used to be able to do this just fine. When I would
try to open the ADP the connection was broken because the server on this
computer I'm using has a different name than the "live" server.
Since I upgraded to Access 2003, when I open the ADP and try to edith the
ADP, I can't get to it. How can I change the connnection properties?
Linda
hi Linda
Linda Burnside wrote:
> I have a copy of an ADP and MSDE database that I tweak occasionally
> from an alternate location. I used to be able to do this just fine. When
> I would try to open the ADP the connection was broken because
> the server on this computer I'm using has a different name than the
> "live" server.
> Since I upgraded to Access 2003, when I open the ADP and try to edith
> the ADP, I can't get to it. How can I change the connnection
> properties?
in the File menu, you can access the "connection" menu item, which will
open the Data link dialog you can use to select the desired SQL Server
instance (and database)..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Yes, I know where it is. Problem is that nothing happens when I click on
Connection. It won't open.
Linda
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3m7637F156p68U1@.individual.net...
> hi Linda
> Linda Burnside wrote:
> in the File menu, you can access the "connection" menu item, which will
> open the Data link dialog you can use to select the desired SQL Server
> instance (and database)..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Linda,
Linda Burnside wrote:
> Yes, I know where it is. Problem is that nothing happens when I
> click on Connection. It won't open.
does the Data Link dialog not open?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||The box that shows the connection information (if that's what you're calling
the data link dialogue) does not open. When I click on the word
"Connection" from the menu, nothing happens.
Linda
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3m8l81F15g1kqU1@.individual.net...
> hi Linda,
> Linda Burnside wrote:
> does the Data Link dialog not open?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Since "Data Link Properties" dialog box is an independent COM component
comming with Windows, you can test it with a "*.udl" file to make sure the
"Data Link Properties" dialog box itself is OK or not. If it is OK for
"*.udl" file, then the problen could be the installation of Access2003. If
the "Data Link Properties" dialog box does not work even with "*.udl", you
may have to repair your Windows or Windows registry (I am not surewhat to do
or what the cause is).
To test "Data Link Properties" dialog box:
1. Open Notepad;
2. Save a blank Notepad file on desktop as "MyDataLink.udl";
3. Double-click "MyDataLink.udl", which should open "Data Link Properties"
dialog box.
"Linda Burnside" <linda@.nospam_burnsidebiz.com> wrote in message
news:N0ILe.52$Z%6.1@.newssvr17.news.prodigy.com...
> The box that shows the connection information (if that's what you're
calling
> the data link dialogue) does not open. When I click on the word
> "Connection" from the menu, nothing happens.
> Linda
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
> news:3m8l81F15g1kqU1@.individual.net...
>
alternate location. I used to be able to do this just fine. When I would
try to open the ADP the connection was broken because the server on this
computer I'm using has a different name than the "live" server.
Since I upgraded to Access 2003, when I open the ADP and try to edith the
ADP, I can't get to it. How can I change the connnection properties?
Linda
hi Linda
Linda Burnside wrote:
> I have a copy of an ADP and MSDE database that I tweak occasionally
> from an alternate location. I used to be able to do this just fine. When
> I would try to open the ADP the connection was broken because
> the server on this computer I'm using has a different name than the
> "live" server.
> Since I upgraded to Access 2003, when I open the ADP and try to edith
> the ADP, I can't get to it. How can I change the connnection
> properties?
in the File menu, you can access the "connection" menu item, which will
open the Data link dialog you can use to select the desired SQL Server
instance (and database)..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Yes, I know where it is. Problem is that nothing happens when I click on
Connection. It won't open.
Linda
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3m7637F156p68U1@.individual.net...
> hi Linda
> Linda Burnside wrote:
> in the File menu, you can access the "connection" menu item, which will
> open the Data link dialog you can use to select the desired SQL Server
> instance (and database)..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Linda,
Linda Burnside wrote:
> Yes, I know where it is. Problem is that nothing happens when I
> click on Connection. It won't open.
does the Data Link dialog not open?
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||The box that shows the connection information (if that's what you're calling
the data link dialogue) does not open. When I click on the word
"Connection" from the menu, nothing happens.
Linda
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3m8l81F15g1kqU1@.individual.net...
> hi Linda,
> Linda Burnside wrote:
> does the Data Link dialog not open?
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.15.0 - DbaMgr ver 0.60.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||Since "Data Link Properties" dialog box is an independent COM component
comming with Windows, you can test it with a "*.udl" file to make sure the
"Data Link Properties" dialog box itself is OK or not. If it is OK for
"*.udl" file, then the problen could be the installation of Access2003. If
the "Data Link Properties" dialog box does not work even with "*.udl", you
may have to repair your Windows or Windows registry (I am not surewhat to do
or what the cause is).
To test "Data Link Properties" dialog box:
1. Open Notepad;
2. Save a blank Notepad file on desktop as "MyDataLink.udl";
3. Double-click "MyDataLink.udl", which should open "Data Link Properties"
dialog box.
"Linda Burnside" <linda@.nospam_burnsidebiz.com> wrote in message
news:N0ILe.52$Z%6.1@.newssvr17.news.prodigy.com...
> The box that shows the connection information (if that's what you're
calling
> the data link dialogue) does not open. When I click on the word
> "Connection" from the menu, nothing happens.
> Linda
> "Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
> news:3m8l81F15g1kqU1@.individual.net...
>
Thursday, March 8, 2012
Change an Access Database to SQL Server
There's a whitepaper which describes this process in detail. Have a look here:
http://www.microsoft.com/technet/prodtechnol/sql/2000/Deploy/accessmigration.mspx
This is for SQL 2000, but it should be similar for SQL 2005.|||
Hi Chris
Thanks for your help.
I tried this but got errors. I am running SQLServer on the same computer as Access 2003 with the database. The computer is running WindowsXP Pro operating system. Will this work or do I need Windows 2000 server or Windows NT?
Thanks again,
Mark
|||I also use SQL Server 2005 on Windows XP pro, and haven't experienced any limitations (when compared to using Windows 2003). What errors are you getting? Also have a look at the event log, and ensure you resolve any permissions issues first.|||Here is the error message I got:
Connection failed:
SQLState: '01000'
SQL Server Error: 2
[Microsoft][ODBC SQL Server][Shared Memory]ConnectionOpen (Connect()).
Connection failed:
SQLState: '08001'
SQLServer Error: 17
[Microsoft][ODBC SQL Server][Shared Memory]SQL Server does not exist or access denied.
|||This is most likely to be either a security issue (invalid security settings used when trying to connect to SQL Server), or a client/server connectivity issue (related to network connectivity, but probaby not in your case, as you are using Shared Memory - ie. both server/client are on the same box, or a client/server protocol mismatch/misconfiguration).There are detailed step-by-step instructions to follow on the following post for dealing with SQL Server connectivity/connection issues. Have a look at the following thread for details (please read through all details in the thread first before re-posting):
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=302440&SiteID=1|||For some reason, nothing was posted - try again.|||
Chris
I have been writing database application in MS Access for about 10 years and thought I was pretty good. So I figured it was time to try to move up to SQLServer. Boy do I feel stupid!
I tried to read through the thread that you referred me to and I could not make heads or tails out of it. I can't even figure out which program to open (Configuration tools, SQL Server Management Studio Express CTP, etc.) Once I open one of these programs, I can't figure out where to begin. Where is a client? Where is the server?
Access was just - start the program, make some tables, make some queries if needed, make some forms or reports, and your done -EASY.
Do you have any suggestions on where I can go to get basic information on how to get started with SQLServer. The basic information on the Microsoft web site is even too advanced for someone who can't figure out the basics of getting started (step by step - click on this first to do __, click on this next to do the next thing which is __, etc.).
Any suggestions would be greatly appreciated.
Thanks
|||There's no reason to feel stupid. Anything looks super complicated when there are heaps of technical product/domain-specific keywords/jargon, and you've never seen it before. To me, the fact that depsite working on one thing for a long time, the fact that you show the willingness to upgrade/broaden your skills shows that you are willing to learn, and that's commendable.After a quick scout around, I found the following link, which guides you through, step-by-step, the process of setting up either SQL Server 2005 Pro or the Express edition:
http://msdn2.microsoft.com/en-us/library/ms345318(SQL.90).aspx
It's got instructions about how to access different programs (that come with SQL 2005), and by following the instructions step by step, you'll become familiar with which tools are used for which purpose.
Most of what I've learned, I've learned through tutorials, and I find them a great place to start. Once you've got the basics, you can search for specifics in the area you're interested in, or need to work with (ie. once you've got the fundamentals of how to do basic DB management using the tools, then you can concentrate on your DB upgrade).
In most cases, all you need to know will be on msdn, so when you do a google search, if you find you're not getting what you want, then use the site:msdn.microsoft.com keyword to restrict your search to the MSDN site.|||Note also the link to further SQL Server tutorials included on the linked page under the Concepts section, for further tutorials:
SQL Server Tools Tutorials|||
Thanks for your help.
I'll give that a try.
Change Access Db to server on connection on CR9
I am using VB6 and CR9.
I have some CRs using Access as DB and I am converting them to conect to SQL server DB. What I did now is to change their connection to SQL at design time. But it took long time to reinput the fields on the report since DB has been changed. I wonder if there is better way to do that?
Thanks a lot for any input.try "Database" set datasource location (you will build the new connection) click on table names in both windows, click update. If it finds a match it will map your report to the new location and you won't need to change to many formulas.
I have some CRs using Access as DB and I am converting them to conect to SQL server DB. What I did now is to change their connection to SQL at design time. But it took long time to reinput the fields on the report since DB has been changed. I wonder if there is better way to do that?
Thanks a lot for any input.try "Database" set datasource location (you will build the new connection) click on table names in both windows, click update. If it finds a match it will map your report to the new location and you won't need to change to many formulas.
Wednesday, March 7, 2012
Challenge revisisted
Hi,
I'm trying to port som old access code to asp.net and the access solution ha
s a couple of queries to sql server that I'm not sure work as expected. I t
hink the idea of the query is to identify if the same event (slaugher of rei
ndeer) is registered in two
different places (field 5 in my last challenge), but two duplicate rows diff
ering only by place does not get selected by the query.
The query is as follows:
SELECT ImportID FROM Temp t WHERE ( EXISTS (
SELECT Year, Code1, Code2, Date FROM (
SELECT DISTINCT Year, Code1, Code2, Date , Place FROM Temp ) SUBQUERY
WHERE ( t.Year = SUBQUERY.Year )
AND ( t.Code1 = SUBQUERY.Code1 )
AND ( t.Code2 = SUBQUERY.Code2 )
AND ( t.Date = SUBQUERY.Date)
GROUP BY Year, Code1, Code2, Date
HAVING (COUNT(*) > 1) ))
Can anyone see if this query does anything useful?, or perhaps give me a que
ry that selects rows with same Year,Code1,Code2,Date, but with different Pla
ce.
The other query is essentially the same, using Zone instead of Place
Morten WennevikOn Mon, 14 Nov 2005 08:51:43 +0100, Morten Wennevik wrote:
>Hi,
>I'm trying to port som old access code to asp.net and the access solution has a cou
ple of queries to sql server that I'm not sure work as expected. I think the idea o
f the query is to identify if the same event (slaugher of reindeer) is registered in
tw
o different places (field 5 in my last challenge), but two duplicate rows differing only by
place does not get selected by the query.
>The query is as follows:
>SELECT ImportID FROM Temp t WHERE ( EXISTS (
> SELECT Year, Code1, Code2, Date FROM (
> SELECT DISTINCT Year, Code1, Code2, Date , Place FROM Temp ) SUBQUERY
> WHERE ( t.Year = SUBQUERY.Year )
> AND ( t.Code1 = SUBQUERY.Code1 )
> AND ( t.Code2 = SUBQUERY.Code2 )
> AND ( t.Date = SUBQUERY.Date)
> GROUP BY Year, Code1, Code2, Date
> HAVING (COUNT(*) > 1) ))
>Can anyone see if this query does anything useful?, or perhaps give me a qu
ery that selects rows with same Year,Code1,Code2,Date, but with different Pl
ace.
>The other query is essentially the same, using Zone instead of Place
>Morten Wennevik
Hi Morten,
I'm not sure what you want eactly. If you want to find year / code1 /
code2 / date combinations for which more than one row exist, use:
SELECT Year, Code1, Code2, Date, COUNT(*)
FROM Temp
GROUP BY Year, Code1, Code2, Date
HAVING COUNT(*) > 1
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
I'm trying to port som old access code to asp.net and the access solution ha
s a couple of queries to sql server that I'm not sure work as expected. I t
hink the idea of the query is to identify if the same event (slaugher of rei
ndeer) is registered in two
different places (field 5 in my last challenge), but two duplicate rows diff
ering only by place does not get selected by the query.
The query is as follows:
SELECT ImportID FROM Temp t WHERE ( EXISTS (
SELECT Year, Code1, Code2, Date FROM (
SELECT DISTINCT Year, Code1, Code2, Date , Place FROM Temp ) SUBQUERY
WHERE ( t.Year = SUBQUERY.Year )
AND ( t.Code1 = SUBQUERY.Code1 )
AND ( t.Code2 = SUBQUERY.Code2 )
AND ( t.Date = SUBQUERY.Date)
GROUP BY Year, Code1, Code2, Date
HAVING (COUNT(*) > 1) ))
Can anyone see if this query does anything useful?, or perhaps give me a que
ry that selects rows with same Year,Code1,Code2,Date, but with different Pla
ce.
The other query is essentially the same, using Zone instead of Place
Morten WennevikOn Mon, 14 Nov 2005 08:51:43 +0100, Morten Wennevik wrote:
>Hi,
>I'm trying to port som old access code to asp.net and the access solution has a cou
ple of queries to sql server that I'm not sure work as expected. I think the idea o
f the query is to identify if the same event (slaugher of reindeer) is registered in
tw
o different places (field 5 in my last challenge), but two duplicate rows differing only by
place does not get selected by the query.
>The query is as follows:
>SELECT ImportID FROM Temp t WHERE ( EXISTS (
> SELECT Year, Code1, Code2, Date FROM (
> SELECT DISTINCT Year, Code1, Code2, Date , Place FROM Temp ) SUBQUERY
> WHERE ( t.Year = SUBQUERY.Year )
> AND ( t.Code1 = SUBQUERY.Code1 )
> AND ( t.Code2 = SUBQUERY.Code2 )
> AND ( t.Date = SUBQUERY.Date)
> GROUP BY Year, Code1, Code2, Date
> HAVING (COUNT(*) > 1) ))
>Can anyone see if this query does anything useful?, or perhaps give me a qu
ery that selects rows with same Year,Code1,Code2,Date, but with different Pl
ace.
>The other query is essentially the same, using Zone instead of Place
>Morten Wennevik
Hi Morten,
I'm not sure what you want eactly. If you want to find year / code1 /
code2 / date combinations for which more than one row exist, use:
SELECT Year, Code1, Code2, Date, COUNT(*)
FROM Temp
GROUP BY Year, Code1, Code2, Date
HAVING COUNT(*) > 1
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Saturday, February 25, 2012
Chaching Sqldatasource datasets... can it be done? and a performance question.
I'm using visual basic coded datasets right now, and caching them, so as all of my web app users access the cache once the first person's gone to my site and initiallized the page.
If Cache("ds_cache")IsNothingThenDim dsAsNew DataSetblah blah code blah blah blah
Cache.Insert("ds_cache", ds)EndIfWhat I was wondering is, if it's possible to cache the datasets that get created and are used by Sqldatasources. Or if by chance caching is automatically done and controlled for these datasets, as it's the server that makes and handles them.
The reason I wonder about this because I'm not sure how you go about referencing one of those datasets, because you can't give them a name/id. And I've not found mention anywhere of you being able to do so.
The real point or question I'm trying to get at is to see which performs better , or uses less requests back to the server for the data as a lot of my data will be repeatedly used by all of my users and so I'd like to save myself from using as many repeated connections as possibles in regards to using a typed dataset versus filling my controls from a sqldatasource?
Thank you in advance for your response.
You know what, I pretty much found my answer:
http://msdn2.microsoft.com/en-us/library/z56y8ksb.aspx
The question still remains of if a sqldatasource would outperform a typed dataset?
It makes you wonder, as a typed dataset you don't have to worry about time spent on conversions, etc. but you would think that server built and controlled dataset might perform better, or atleast it would probably build faster when it's first created... please do reply if you have insight.
Chaching Sqldatasource datasets... can it be done? and a performance question.
I'm using visual basic coded datasets right now, and caching them, so as all of my web app users access the cache once the first person's gone to my site and initiallized the page.
If Cache("ds_cache") Is Nothing Then
Dim ds As New DataSet
blah blah code blah blah blah
Cache.Insert("ds_cache", ds)End If
What I was wondering is, if it's possible to cache the datasets that get created and are used by Sqldatasources. Or if by chance caching is automatically done and controlled for these datasets, as it's the server that makes and handles them.
The reason I wonder about this because I'm not sure how you go about referencing one of those datasets, because you can't give them a name/id. And I've not found mention anywhere of you being able to do so.
The real point or question I'm trying to get at is to see which performs better , or uses less requests back to the server for the data as a lot of my data will be repeatedly used by all of my users and so I'd like to save myself from using as many repeated connections as possibles in regards to using a typed dataset versus filling my controls from a sqldatasource?
Thank you in advance for your response.
You know what, I pretty much found my answer:
http://msdn2.microsoft.com/en-us/library/z56y8ksb.aspx
The question still remains of if a sqldatasource would outperform a typed dataset?
It makes you wonder, as a typed dataset you don't have to worry about time spent on conversions, etc. but you would think that server built and controlled dataset might perform better, or atleast it would probably build faster when it's first created... please do reply if you have insight.
CFE2SQL Server does not exist or access denied.
Does anyone have any idea what is causing the following error.
"CFE2SQL Server does not exist or access denied."
--
Thanks
JDSJDS wrote:
> Does anyone have any idea what is causing the following error.
> "CFE2SQL Server does not exist or access denied."
>
I'd say the server does not exist, can't be accessed, or you don't have
permission to use it. Without further detail, that's about all I can offer.|||The Server exist and this problem is happen sporadically. I can't find any
information on what is CFE2SQL.
--
Thanks
JDS
"Tracy McKibben" wrote:
> JDS wrote:
> > Does anyone have any idea what is causing the following error.
> >
> > "CFE2SQL Server does not exist or access denied."
> >
> >
> I'd say the server does not exist, can't be accessed, or you don't have
> permission to use it. Without further detail, that's about all I can offer.
>|||JDS wrote:
> The Server exist and this problem is happen sporadically. I can't find any
> information on what is CFE2SQL.
Where are you seeing this message displayed? The actual error message
is "SQL Server does not exist or access denied", the "CFE2" part is
coming from elsewhere.
"CFE2SQL Server does not exist or access denied."
--
Thanks
JDSJDS wrote:
> Does anyone have any idea what is causing the following error.
> "CFE2SQL Server does not exist or access denied."
>
I'd say the server does not exist, can't be accessed, or you don't have
permission to use it. Without further detail, that's about all I can offer.|||The Server exist and this problem is happen sporadically. I can't find any
information on what is CFE2SQL.
--
Thanks
JDS
"Tracy McKibben" wrote:
> JDS wrote:
> > Does anyone have any idea what is causing the following error.
> >
> > "CFE2SQL Server does not exist or access denied."
> >
> >
> I'd say the server does not exist, can't be accessed, or you don't have
> permission to use it. Without further detail, that's about all I can offer.
>|||JDS wrote:
> The Server exist and this problem is happen sporadically. I can't find any
> information on what is CFE2SQL.
Where are you seeing this message displayed? The actual error message
is "SQL Server does not exist or access denied", the "CFE2" part is
coming from elsewhere.
CFE2SQL Server does not exist or access denied.
JDS wrote:
> Does anyone have any idea what is causing the following error.
> "CFE2SQL Server does not exist or access denied."
>
I'd say the server does not exist, can't be accessed, or you don't have
permission to use it. Without further detail, that's about all I can offer.The Server exist and this problem is happen sporadically. I can't find any
information on what is CFE2SQL.
--
Thanks
JDS
"Tracy McKibben" wrote:
> JDS wrote:
> I'd say the server does not exist, can't be accessed, or you don't have
> permission to use it. Without further detail, that's about all I can offe
r.
>|||Does anyone have any idea what is causing the following error.
"CFE2SQL Server does not exist or access denied."
Thanks
JDS|||JDS wrote:
> Does anyone have any idea what is causing the following error.
> "CFE2SQL Server does not exist or access denied."
>
I'd say the server does not exist, can't be accessed, or you don't have
permission to use it. Without further detail, that's about all I can offer.|||The Server exist and this problem is happen sporadically. I can't find any
information on what is CFE2SQL.
--
Thanks
JDS
"Tracy McKibben" wrote:
> JDS wrote:
> I'd say the server does not exist, can't be accessed, or you don't have
> permission to use it. Without further detail, that's about all I can offe
r.
>|||JDS wrote:
> The Server exist and this problem is happen sporadically. I can't find any
> information on what is CFE2SQL.
Where are you seeing this message displayed? The actual error message
is "SQL Server does not exist or access denied", the "CFE2" part is
coming from elsewhere.|||JDS wrote:
> The Server exist and this problem is happen sporadically. I can't find any
> information on what is CFE2SQL.
Where are you seeing this message displayed? The actual error message
is "SQL Server does not exist or access denied", the "CFE2" part is
coming from elsewhere.
> Does anyone have any idea what is causing the following error.
> "CFE2SQL Server does not exist or access denied."
>
I'd say the server does not exist, can't be accessed, or you don't have
permission to use it. Without further detail, that's about all I can offer.The Server exist and this problem is happen sporadically. I can't find any
information on what is CFE2SQL.
--
Thanks
JDS
"Tracy McKibben" wrote:
> JDS wrote:
> I'd say the server does not exist, can't be accessed, or you don't have
> permission to use it. Without further detail, that's about all I can offe
r.
>|||Does anyone have any idea what is causing the following error.
"CFE2SQL Server does not exist or access denied."
Thanks
JDS|||JDS wrote:
> Does anyone have any idea what is causing the following error.
> "CFE2SQL Server does not exist or access denied."
>
I'd say the server does not exist, can't be accessed, or you don't have
permission to use it. Without further detail, that's about all I can offer.|||The Server exist and this problem is happen sporadically. I can't find any
information on what is CFE2SQL.
--
Thanks
JDS
"Tracy McKibben" wrote:
> JDS wrote:
> I'd say the server does not exist, can't be accessed, or you don't have
> permission to use it. Without further detail, that's about all I can offe
r.
>|||JDS wrote:
> The Server exist and this problem is happen sporadically. I can't find any
> information on what is CFE2SQL.
Where are you seeing this message displayed? The actual error message
is "SQL Server does not exist or access denied", the "CFE2" part is
coming from elsewhere.|||JDS wrote:
> The Server exist and this problem is happen sporadically. I can't find any
> information on what is CFE2SQL.
Where are you seeing this message displayed? The actual error message
is "SQL Server does not exist or access denied", the "CFE2" part is
coming from elsewhere.
Certification: Looking for advise
Hi All
I have been working with Ms access Database the last four years and have gained a lot of experience which help me to have extensive knowledge of Jet, Dao and Ado. Since The Begining of this year i hv been migrating to SQL server server and for that i bought two books :Programming Access project file with Microsoft Sql server and Access Developers Guide to Ms sql server : Chipman & Baron ( this oner is simply a great achievement ). They help me out in the very near past to complety migrate a two database approch Jet Application to a reliable (Adp/ SQL Server client server ) where major parts of the code business logic has been transfered to run on the server as stored procedures and all forms re-built as unbound. these merely leads to a great enhancement of the applications.
My goal now is register to MCDBA and get the certification. I am not interested by classroom training from Microsoft Parterns or online training. I am looking for books that can help to get well prepared for the exams.
Have found this two books in Amazon:
1 - MCAD/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server 2000 Database Design and Implementation, Exam 70-229, Second Edition
2- MCSA/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server 2000 System Administration, 70-228, Second Edition
However their rating didn't encourage me to place directly my orders.
Does someone have any experience with them ?
Do you think they will considerably help me to add value to my knowledge of SQL Server and get ready for the exam ?
Do you know some others books you woul rather encourage me to buy ?
Any advice in this regards really appreciated.Transcender
www.transcender.com
Regards,
hmscott|||The MS Press books are not the best for learning. Try the Que or Sybex books for learning the material while preparing for certification. Make sure you get hands-on experience with all the examples and labs as you read the books. Use Transcender after reading the books to assess your readiness to take the test. Good luck!
I have been working with Ms access Database the last four years and have gained a lot of experience which help me to have extensive knowledge of Jet, Dao and Ado. Since The Begining of this year i hv been migrating to SQL server server and for that i bought two books :Programming Access project file with Microsoft Sql server and Access Developers Guide to Ms sql server : Chipman & Baron ( this oner is simply a great achievement ). They help me out in the very near past to complety migrate a two database approch Jet Application to a reliable (Adp/ SQL Server client server ) where major parts of the code business logic has been transfered to run on the server as stored procedures and all forms re-built as unbound. these merely leads to a great enhancement of the applications.
My goal now is register to MCDBA and get the certification. I am not interested by classroom training from Microsoft Parterns or online training. I am looking for books that can help to get well prepared for the exams.
Have found this two books in Amazon:
1 - MCAD/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server 2000 Database Design and Implementation, Exam 70-229, Second Edition
2- MCSA/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server 2000 System Administration, 70-228, Second Edition
However their rating didn't encourage me to place directly my orders.
Does someone have any experience with them ?
Do you think they will considerably help me to add value to my knowledge of SQL Server and get ready for the exam ?
Do you know some others books you woul rather encourage me to buy ?
Any advice in this regards really appreciated.Transcender
www.transcender.com
Regards,
hmscott|||The MS Press books are not the best for learning. Try the Que or Sybex books for learning the material while preparing for certification. Make sure you get hands-on experience with all the examples and labs as you read the books. Use Transcender after reading the books to assess your readiness to take the test. Good luck!
Friday, February 24, 2012
Cell Security : Help
SECURITY USING CELL-SECURITY:
From what i've read cell security s enforced on the client. If someone is able to gain access to a machine running the client (for example an application server or a web server) he is able to get cell values independently of the fact that those values will be defined as #N/A in the secured cell value property. The real value is travelling between theAnalysis Server and the application server. Is this true ? How can we effectively garantee true security ?Did you look in BOL?
Cell Security
In a cube role, you can implement cell security to limit the cube cells that end users in the role can view as they browse cubes. You can also grant read/write access to a write-enabled cube and limit the cells that end users in the role can update. You do this by selecting a policy and by selecting a rule or defining a custom rule for each permission.
Cell security is optional. If you do not specify cell security, end users see all cell values in cubes they are authorized to access. (However, if dimension security is specified, cells for some members might not be viewable.) If a cube is write-enabled, end users cannot update cell values. If one or more of a virtual cube's component cubes are write-enabled, end users cannot update the cell values of virtual cubes.
If a policy or rule permits updates to a cell, it can be updated if it is an atomic cell. If the cell is not atomic, it can be updated only if the client application provides a way of dispersing the update over the subordinate atomic cells. For example, in a client application a write-enabled cube is displayed with the lowest level of every dimension except Time. On the axis for the Time dimension, the nonatomic cells for months are displayed, but the subordinate atomic cells for days are not. (Days is the lowest level in the Time dimension.) A cell for June can be updated by adding $90 if the client application provides a way of dividing the +$90 update into thirty +$3 updates, one to each of the cells for the 30 days in June. Dispersion methods other than simple division can also be used. The UPDATE CUBE statement provides several methods. For more information, see UPDATE CUBE Statement.
From what i've read cell security s enforced on the client. If someone is able to gain access to a machine running the client (for example an application server or a web server) he is able to get cell values independently of the fact that those values will be defined as #N/A in the secured cell value property. The real value is travelling between theAnalysis Server and the application server. Is this true ? How can we effectively garantee true security ?Did you look in BOL?
Cell Security
In a cube role, you can implement cell security to limit the cube cells that end users in the role can view as they browse cubes. You can also grant read/write access to a write-enabled cube and limit the cells that end users in the role can update. You do this by selecting a policy and by selecting a rule or defining a custom rule for each permission.
Cell security is optional. If you do not specify cell security, end users see all cell values in cubes they are authorized to access. (However, if dimension security is specified, cells for some members might not be viewable.) If a cube is write-enabled, end users cannot update cell values. If one or more of a virtual cube's component cubes are write-enabled, end users cannot update the cell values of virtual cubes.
If a policy or rule permits updates to a cell, it can be updated if it is an atomic cell. If the cell is not atomic, it can be updated only if the client application provides a way of dispersing the update over the subordinate atomic cells. For example, in a client application a write-enabled cube is displayed with the lowest level of every dimension except Time. On the axis for the Time dimension, the nonatomic cells for months are displayed, but the subordinate atomic cells for days are not. (Days is the lowest level in the Time dimension.) A cell for June can be updated by adding $90 if the client application provides a way of dividing the +$90 update into thirty +$3 updates, one to each of the cells for the 30 days in June. Dispersion methods other than simple division can also be used. The UPDATE CUBE statement provides several methods. For more information, see UPDATE CUBE Statement.
Thursday, February 16, 2012
Cause SQL Server Restricted Mode (db_owner, dbcreator,)
What would cause SQL Server 2000 Enterprise Edition
databases to be in access restricted mode where only
db_owner, dbcreator, and sysadmin?
SQL Server was not placed in access restricted my any
administrator.
This is an active/active cluster with SQL Server 2000
SP3A with Windows 2000 Advanced Server.
Could the cluster place SQL Server 2000 in a access
restricted mode?
If SQL Server 2000 resources were very low for extended
period would the cluster or SQL Server place SQL Server
in access restricted mode?
Please help me resolve this issue.
Thank You,
Dan
The only thing that can do that is an administrator. If it is a new
database, it will look exactly like that since SQL Server uses the principle
of having to explicitly grant access. But, if it is an existing database,
an administrator had to have changed the security. You are saying that no
one did this. I'd more accurately say that no one is willing to admit to
having done that. How can you find out? Download Log Explorer from
Lumigent Technologies and point it at your tran log + tran log backups. It
will show you pretty explicitly when the security was changed and by whom.
I'd also recommend taking a look at their companion product Entegra which
can notify you immediately when security changes and a whole host of other
things change.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
databases to be in access restricted mode where only
db_owner, dbcreator, and sysadmin?
SQL Server was not placed in access restricted my any
administrator.
This is an active/active cluster with SQL Server 2000
SP3A with Windows 2000 Advanced Server.
Could the cluster place SQL Server 2000 in a access
restricted mode?
If SQL Server 2000 resources were very low for extended
period would the cluster or SQL Server place SQL Server
in access restricted mode?
Please help me resolve this issue.
Thank You,
Dan
The only thing that can do that is an administrator. If it is a new
database, it will look exactly like that since SQL Server uses the principle
of having to explicitly grant access. But, if it is an existing database,
an administrator had to have changed the security. You are saying that no
one did this. I'd more accurately say that no one is willing to admit to
having done that. How can you find out? Download Log Explorer from
Lumigent Technologies and point it at your tran log + tran log backups. It
will show you pretty explicitly when the security was changed and by whom.
I'd also recommend taking a look at their companion product Entegra which
can notify you immediately when security changes and a whole host of other
things change.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
Tuesday, February 14, 2012
catalog views
Is there any way to access catalog views, such as sys.backup_devices, using a select statement from inside a sored procedure?
This works fine for me;
use adventureworks
go
create procedure usp_test as
select * from
sys.backup_devices
go
exec usp_test
go
|||This did not work. Following is the actual code i executed(Dont have adventureworks installed so i executed against my database-EstMaster:
use estmaster
go
create procedure usp_test as
select * from
sys.backup_devices
go
exec usp_test
go
This is the output received::
Changed database context to 'EstMaster'.
Msg 2714, Level 16, State 5, Server HOME\ESTIMATEMASTER, Procedure usp_test, Line 6
There is already an object named 'usp_test' in the database.
Msg 208, Level 16, State 1, Server HOME\ESTIMATEMASTER, Procedure usp_test, Line 2
Invalid object name 'sys.backup_devices'.
Also have tried the following stored procedure:
ALTER PROCEDURE dbo.GetData
AS
SET NOCOUNT ON
SELECT * FROM sys.backup_devices
DECLARE @.var1 nvarchar(30);
SET @.var1 = 'No Name';
SELECT var1=Name FROM sys.backup_devices
Print @.var1
SET @.var1 = 'No Name';
SELECT @.var1=recovery_model_desc FROM sys.databases
Print @.var1
Stored procedure output is almost identical- "Invalid object name 'sys.backup_devices". My entire problem appears to be that the view name "sys.backup_devices" ( or "sys.databases") is not being recognized.
|||I've noticed that
select * from
sys.backup_devices
only seems to work with SQL Server 2005 and not with SQL Server 2000.
Gary
|||
Yes this view only exists in sql2005, is that the version you are running?
In terms of the error you are getting, the first part is because you have created the proc once already, you need to delete it by using the "drop procedure usp_test" command.
I would get this simple version working first as it might be a permissions problem, before going to the more complex one. Can you issue a simple select outside of the stored proc, while in another database?
|||The database in question is an sql server express database(converted from MSDE).|||Hmmm I'm somewhat perplexed.
Can you just run a simple select * from sys.backup_devices in query editor please?
Next can you go into object explorer in SSMS, browse to the database, then views, then system views and look and see if it is there.
|||1 SqlServer Express Is The Version being used. The server/database in question was originally an MSDE database and was converted when SqlServer Express was installed. The Database is being used in a VS2003.Net olution and is, as far as i can tell, running fine. The solution uses stored procedures almost exclusively, with numerous fairly complicated select procedures.
2.The duplication is not of concern. I can delete the stored proc from VS2003 very easily.
3. Since I had no other database on hand to try this, I downloaded the AdventureWorks Sample Db and attached it to the Default instance of SqlServer Express that was created during installation of SqlServerExpress. The first test was to try and run the select statement from a script file. This appeared to work ok. The ouput is shown below:
Changed database context to 'AdventureWorks'.
name type type_desc physical_name
-- - --
(0 rows affected)
In a second test I ran the original script you sent in your first contact. This also Appeared to work fine and the output from that test was identical to that shown above. In a third test, I tried to access sys.databases. That also appears to work fine, the output from that test is shown below:
Changed database context to 'AdventureWorks'.
name database_id source_database_id owner_sid create_date compatibility_level collation_name user_access user_access_desc is_read_only is_auto_close_on is_auto_shrink_on state state_desc is_in_standby is_cleanly_shutdown is_supplemental_logging_enabled snapshot_isolation_state snapshot_isolation_state_desc is_read_committed_snapshot_on recovery_model recovery_model_desc page_verify_option page_verify_option_desc is_auto_create_stats_on is_auto_update_stats_on is_auto_update_stats_async_on is_ansi_null_default_on is_ansi_nulls_on is_ansi_padding_on is_ansi_warnings_on is_arithabort_on is_concat_null_yields_null_on is_numeric_roundabort_on is_quoted_identifier_on is_recursive_triggers_on is_cursor_close_on_commit_on is_local_cursor_default is_fulltext_enabled is_trustworthy_on is_db_chaining_on is_parameterization_forced is_master_key_encrypted_by_server is_published is_subscribed is_merge_published is_distributor is_sync_with_backup service_broker_guid is_broker_enabled log_reuse_wait log_reuse_wait_desc is_date_correlation_on
-- -- - -- - -- -- - -- -- - - - -- -- -- -- -- -- - - - -- -- - -- - -- -- -- - -- - -- -- -
master 1 NULL 0x01 2003-04-08 09:13:36.390 90 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 1 ON 0 3 SIMPLE 2 CHECKSUM 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 00000000-0000-0000-0000-000000000000 0 0 NOTHING 0
tempdb 2 NULL 0x01 2007-05-22 20:00:19.110 90 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 0 OFF 0 3 SIMPLE 0 NONE 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 029F6CF2-9B6D-41CA-ADD1-743BA9B9963F 1 0 NOTHING 0
model 3 NULL 0x01 2003-04-08 09:13:36.390 90 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 0 OFF 0 3 SIMPLE 2 CHECKSUM 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 00000000-0000-0000-0000-000000000000 0 0 NOTHING 0
msdb 4 NULL 0x01 2005-10-14 01:54:05.240 90 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER 0 0 0 0 ONLINE 0 0 0 1 ON 0 3 SIMPLE 2 CHECKSUM 1 1 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 0 0 0 0 0 0 0 F302CF13-753E-4539-8C5A-569F65D00B95 1 0 NOTHING 0
AdventureWorks 5 NULL 0x010500000000000515000000D576CC6AB43555CE66695A87F4010000 2007-05-22 21:26:23.523 90 Latin1_General_CS_AS 0 MULTI_USER 0 1 0 0 ONLINE 0 0 0 0 OFF 0 3 SIMPLE 2 CHECKSUM 1 1 0 0 1 1 1 1 1 0 1 0 0 0 1 0 0 0 0 0 0 0 0 0 93576EF7-2297-4F84-9842-4E6854D3FD10 0 0 NOTHING 0
(5 rows affected)
4.I've tried several other things to see if there is anything wrong with the Estmaster database(where I'm getting these errors), Including a Backup and Restore, but nothing seems to cure the problem.
The database I'm working with, EstMaster, was originally a an MSDE database(SQL2000 Server Desk Top Engine). When I installed SqlServerExpress 2005, The installation procedure converted the database without interaction on my part. After The Express installer completed, MSDE was uninstalled for me. After a lot of work on this problem, I' m convinced that it is a permissions problem. The converted database is OK. I can select, update delete, add entries to all my tables. I just cant access the system views, such as sys.databases and sys.backup devices.. Those views do not exist in the EstMaster database. Have been unsuccessful in determining how to install those catalog views on my database. I also Have AdventureWorks in a separate instance and am considering creating a new database in the default instance identical to the current database. I can then copy the tables over. Not desireable, but it is a last resort option. I'd much rather understand why I can't create those views in the Estmaster database.
|||This should resolve this issue. The reason the sys catalog views are not available for my database is that SqlServer Express does not convert an MSDE database as I originally thought. The sys catlaog views do not exist in an MSDE instance, hence they are not available.The MSDE database was converted to an Sql Server Express Db using the following procedures. These procedures assume a named MSDE instance containing a database, a default Sql Server Express instance and MSSMS=E.
1- Detach the database in the MSDE instance using sp_detach_db.
2- Attach the database in the default Sql Server Express instance using CREATE DATABASE......FOR ATTACH.
3- Move the --.mdf and --.ldf files from the MSDE instance to the DATA folder in the default Sql Server Express instance using the "Planned Relocation Procedure" In the help topic "Moving User Databases". Follow the procedure exactly.
4- Uninstall the MSDE instance using ADD/Remove Programs in Control Panel.
5- Run Sql Server Express setup and create an instance with the same instance name as the old MSDE instance.
6- Back up the data base in the default Sql Server Express instance using BACKUP DATABASE.
7- Copy the --.mdf and --.ldf data files from the backup using Example E in the RESTORE help topic.
8- Create the database in the new instance using using CREATE DATABASE.
9- Delete the database form the default instance using MSSMS-E.
At this point you have an Sql Server Express database created from the data in the MSDE database in an Sql Server Express Instance with the same name as the original MSDE instance. My Visual Studio 2003.Net solution accessing this database required no changes to have full access to the database. All of the sys catalog views and System stored procedures are available. I can manipulate the database from MSSMS-E or from VisualDatabase VisualStudio 2003..
Sunday, February 12, 2012
Cast/Convert value to VB variable
Hi all,
I have a VB 6.0 application that interacts with an MS Access backend. I am in the process of converting it so that it interacts with SQL Server 2000. The current applications uses Cint, CStr, etc. functions which are MS-Access specific, so I am now switching over to Convert (or Cast) function. The problem is that there are certain places wherein I first need to store the value of the resultant CAST/Convert function in a variable and then use that value in a SQL statement. However, I cant seem to figure out a way of storing the results from CAST/CONVERT functions in the VB 6.0 variable. All the examples on Internet show use of these functions directly in an SQL statement e.g. "Select CAST(title as Int) from xyz", etc.
Can anybody tell me how can i get the values to be stored in the variable? I am really stuck here and cant seem to progress.
Thanks in advance for all your help.
Regards:
Prathmeshe.g.
set rs=cmd.execute("select cast(title as int) as title from xyz")
title=rs.fields("title")|||Thanks for the reply oj. However, my requirement is somewhat different. I'll explain the scenario in short. The user chooses a filename to delete, which is stored in the database as a record. It is stored in 2 places in the database. One as a whole filename and in the second place as a breakdown record. The program should delete the filename from the database and also the file from the disk location. The filename in the database is stored as say "XY006CV003A.xls" or "XY005CJ003B.doc" however on the disk they are stored with the above number and the title for the document, concatenated e.g "XY006CV003A test.xls". So I need to extract the file name only which is "XY006CV003A.xls" to match the database record. The last part "003" is sort of a sequence number and is stored in the database. I need to extract the that sequence to match it and delete it from the second place as I have mentioned. '003' when extracted from the filename will be a string and I need to cast it to Integer type to match the record.
e.g.
fname = Split("XY006CV003A.xls",".") gives "XY006CV003A"
seq = Mid(fname, 8, 3) gives '003' which is string format
I now need to use this seq variable in the query
"Select * from XYZ where fileseq=" & seq
fileseq is of integer datatype so I need to cast/convert seq variable to Integer from String.
Can anybody suggest any ideas?
Regards:
Prathmesh
I have a VB 6.0 application that interacts with an MS Access backend. I am in the process of converting it so that it interacts with SQL Server 2000. The current applications uses Cint, CStr, etc. functions which are MS-Access specific, so I am now switching over to Convert (or Cast) function. The problem is that there are certain places wherein I first need to store the value of the resultant CAST/Convert function in a variable and then use that value in a SQL statement. However, I cant seem to figure out a way of storing the results from CAST/CONVERT functions in the VB 6.0 variable. All the examples on Internet show use of these functions directly in an SQL statement e.g. "Select CAST(title as Int) from xyz", etc.
Can anybody tell me how can i get the values to be stored in the variable? I am really stuck here and cant seem to progress.
Thanks in advance for all your help.
Regards:
Prathmeshe.g.
set rs=cmd.execute("select cast(title as int) as title from xyz")
title=rs.fields("title")|||Thanks for the reply oj. However, my requirement is somewhat different. I'll explain the scenario in short. The user chooses a filename to delete, which is stored in the database as a record. It is stored in 2 places in the database. One as a whole filename and in the second place as a breakdown record. The program should delete the filename from the database and also the file from the disk location. The filename in the database is stored as say "XY006CV003A.xls" or "XY005CJ003B.doc" however on the disk they are stored with the above number and the title for the document, concatenated e.g "XY006CV003A test.xls". So I need to extract the file name only which is "XY006CV003A.xls" to match the database record. The last part "003" is sort of a sequence number and is stored in the database. I need to extract the that sequence to match it and delete it from the second place as I have mentioned. '003' when extracted from the filename will be a string and I need to cast it to Integer type to match the record.
e.g.
fname = Split("XY006CV003A.xls",".") gives "XY006CV003A"
seq = Mid(fname, 8, 3) gives '003' which is string format
I now need to use this seq variable in the query
"Select * from XYZ where fileseq=" & seq
fileseq is of integer datatype so I need to cast/convert seq variable to Integer from String.
Can anybody suggest any ideas?
Regards:
Prathmesh
Friday, February 10, 2012
Cast datetime to smalldatetime error
I have a table that I converted from MS Access and one of the colums I use t
o
store time values into has been converted to datetime and the year 1899 was
added to all existing records. I want to just display the time portion and
have tried the following code in the function I use to display the data:
CAST(ESR_CLOSE_TIME AS smalldatetime)
When I try to run the function I get an error saying the conversion resulted
in an overflow error. Any Ideas. Thanksyes - cast as datetime instead
the lowest smalldatetime value possible is Jan 1, 1900, so 1899 is out
of range.
AkAlan wrote:
> I have a table that I converted from MS Access and one of the colums I use
to
> store time values into has been converted to datetime and the year 1899 wa
s
> added to all existing records. I want to just display the time portion and
> have tried the following code in the function I use to display the data:
> CAST(ESR_CLOSE_TIME AS smalldatetime)
> When I try to run the function I get an error saying the conversion result
ed
> in an overflow error. Any Ideas. Thanks|||SELECT CONVERT(char(8), ESR_CLOSE_TIME , 108) --24hr time with seconds
SELECT RIGHT(CONVERT(char(19), ESR_CLOSE_TIME , 0),7) --with AM/ PM
The datetime datatype includes the date (year, month, day) and time with an
accuracy of 3/1000 of a second.
--
"AkAlan" wrote:
> I have a table that I converted from MS Access and one of the colums I use
to
> store time values into has been converted to datetime and the year 1899 wa
s
> added to all existing records. I want to just display the time portion and
> have tried the following code in the function I use to display the data:
> CAST(ESR_CLOSE_TIME AS smalldatetime)
> When I try to run the function I get an error saying the conversion result
ed
> in an overflow error. Any Ideas. Thanks
o
store time values into has been converted to datetime and the year 1899 was
added to all existing records. I want to just display the time portion and
have tried the following code in the function I use to display the data:
CAST(ESR_CLOSE_TIME AS smalldatetime)
When I try to run the function I get an error saying the conversion resulted
in an overflow error. Any Ideas. Thanksyes - cast as datetime instead
the lowest smalldatetime value possible is Jan 1, 1900, so 1899 is out
of range.
AkAlan wrote:
> I have a table that I converted from MS Access and one of the colums I use
to
> store time values into has been converted to datetime and the year 1899 wa
s
> added to all existing records. I want to just display the time portion and
> have tried the following code in the function I use to display the data:
> CAST(ESR_CLOSE_TIME AS smalldatetime)
> When I try to run the function I get an error saying the conversion result
ed
> in an overflow error. Any Ideas. Thanks|||SELECT CONVERT(char(8), ESR_CLOSE_TIME , 108) --24hr time with seconds
SELECT RIGHT(CONVERT(char(19), ESR_CLOSE_TIME , 0),7) --with AM/ PM
The datetime datatype includes the date (year, month, day) and time with an
accuracy of 3/1000 of a second.
--
"AkAlan" wrote:
> I have a table that I converted from MS Access and one of the colums I use
to
> store time values into has been converted to datetime and the year 1899 wa
s
> added to all existing records. I want to just display the time portion and
> have tried the following code in the function I use to display the data:
> CAST(ESR_CLOSE_TIME AS smalldatetime)
> When I try to run the function I get an error saying the conversion result
ed
> in an overflow error. Any Ideas. Thanks
Subscribe to:
Posts (Atom)