Hi,
I'm writing an Access pass-through query against a SQL server backend and I need some advice on passing parameters. Currently I use vba to substitute the literal values for the parameters prior to passing the query to SQL Server. However, I am going through a loop thousands of times with different literals for these parameters which causes the server's cache to fill up. In Oracle, there is a way to use bind variables for the parameters so that only one copy of the query is cached.
Does anyone know how I can do this in SQL Server?
For instance, I have 20,000 employees and I'm pulling info by SS#:
Select * from EmpTable where SS_number = [SSN]
Is there a way I can pass this query to SQL Server and then pass the value of [SSN] as I loop through the dataset?
Thanks.write a stored procedure, and instead of calling the database engine 20,000 times, just call it once and pass it a list of 20,000 numbers
come to think of it, where would you get 20,000 numbers? sounds like you might want to look for a JOIN solution|||SQL Server actually goes you one better, in that its ODBC drivers will automagically parameterize a query for you (unless you get really creative in modifying the query).
As Rudy pointed out though, if you have more than 20 iterations from a given client, you really ought to be thinking about a JOIN based solution... Doing that kind of thing on that scale one row at a time is WAY too much work for me!
-PatP|||Thanks, guys. I'll get write access to the backend and write a stored proc.
I'll have to read up on how to pass values to the proc (I'm guessing it's like a function).
Thanks again.|||How about this?:
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
cnn.Open "DSN=PKRebate2001", "sa", ""
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = cnn
.CommandText = "sp_UpdateCustomerUnique"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@.ImportMonth").Value = IM
.Execute
End With
Set cmd = Nothing
Set cnn = Nothing|||If you are looking to get automatic parameterization, that VBA code is conceptually good.
-PatP
Showing posts with label access. Show all posts
Showing posts with label access. Show all posts
Tuesday, March 27, 2012
Thursday, March 22, 2012
Binary or Byte data linked to Checkbox
Dear Group,
I'm using Access 2003 linked to an SQL Server data table via an ODBC
connection.
On one form I have several check boxes which want data mapped to a
TRUE/FALSE datatype. Is there a direct way to bind a checkbox to a
bit, a binary, or a tinyint? I've only been able to get it to work
with a smallint which is 2 bytes long.
Thanks,
Max
Arg. Access wants to insert -1 for true and 0 for false.
So sorry the type will need to support negative numbers.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"Max Yaffe" <myaffe@.not.gamry.com> wrote in message
news:0f1ji1p22uleclnn5t4lka3vnta48bda3k@.4ax.com...
> Dear Group,
> I'm using Access 2003 linked to an SQL Server data table via an ODBC
> connection.
> On one form I have several check boxes which want data mapped to a
> TRUE/FALSE datatype. Is there a direct way to bind a checkbox to a
> bit, a binary, or a tinyint? I've only been able to get it to work
> with a smallint which is 2 bytes long.
> Thanks,
> Max
I'm using Access 2003 linked to an SQL Server data table via an ODBC
connection.
On one form I have several check boxes which want data mapped to a
TRUE/FALSE datatype. Is there a direct way to bind a checkbox to a
bit, a binary, or a tinyint? I've only been able to get it to work
with a smallint which is 2 bytes long.
Thanks,
Max
Arg. Access wants to insert -1 for true and 0 for false.
So sorry the type will need to support negative numbers.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"Max Yaffe" <myaffe@.not.gamry.com> wrote in message
news:0f1ji1p22uleclnn5t4lka3vnta48bda3k@.4ax.com...
> Dear Group,
> I'm using Access 2003 linked to an SQL Server data table via an ODBC
> connection.
> On one form I have several check boxes which want data mapped to a
> TRUE/FALSE datatype. Is there a direct way to bind a checkbox to a
> bit, a binary, or a tinyint? I've only been able to get it to work
> with a smallint which is 2 bytes long.
> Thanks,
> Max
Binary or Byte data linked to Checkbox
Dear Group,
I'm using Access 2003 linked to an SQL Server data table via an ODBC
connection.
On one form I have several check boxes which want data mapped to a
TRUE/FALSE datatype. Is there a direct way to bind a checkbox to a
bit, a binary, or a tinyint? I've only been able to get it to work
with a smallint which is 2 bytes long.
Thanks,
MaxArg. Access wants to insert -1 for true and 0 for false.
So sorry the type will need to support negative numbers.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"Max Yaffe" <myaffe@.not.gamry.com> wrote in message
news:0f1ji1p22uleclnn5t4lka3vnta48bda3k@.
4ax.com...
> Dear Group,
> I'm using Access 2003 linked to an SQL Server data table via an ODBC
> connection.
> On one form I have several check boxes which want data mapped to a
> TRUE/FALSE datatype. Is there a direct way to bind a checkbox to a
> bit, a binary, or a tinyint? I've only been able to get it to work
> with a smallint which is 2 bytes long.
> Thanks,
> Max
I'm using Access 2003 linked to an SQL Server data table via an ODBC
connection.
On one form I have several check boxes which want data mapped to a
TRUE/FALSE datatype. Is there a direct way to bind a checkbox to a
bit, a binary, or a tinyint? I've only been able to get it to work
with a smallint which is 2 bytes long.
Thanks,
MaxArg. Access wants to insert -1 for true and 0 for false.
So sorry the type will need to support negative numbers.
Matt Neerincx [MSFT]
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.
"Max Yaffe" <myaffe@.not.gamry.com> wrote in message
news:0f1ji1p22uleclnn5t4lka3vnta48bda3k@.
4ax.com...
> Dear Group,
> I'm using Access 2003 linked to an SQL Server data table via an ODBC
> connection.
> On one form I have several check boxes which want data mapped to a
> TRUE/FALSE datatype. Is there a direct way to bind a checkbox to a
> bit, a binary, or a tinyint? I've only been able to get it to work
> with a smallint which is 2 bytes long.
> Thanks,
> Max
Binary Data on Multiple Servers
I'm using EncryptByKey to encrypt data in my SS2005 database. Since our server is really slow to access from home to work on, I used the Database Publishing Wizard and installed the db to work on at home. Then I created the certificate and symmetric key in my home db.
When I pull info using the DecryptByKey on our database at work on Windows 2003 Server, no problem, the data is decrypted. However, the same data does not decrypt at home on my Windows XP computer. I'm using TripleDes on both machines for the symmetric key (AES won't work on XP).
--To create my cert and key:
USE My_DB;
CREATE CERTIFICATE MyCert
ENCRYPTION BY PASSWORD = 'some password'
WITH SUBJECT = My Data',
START_DATE = '01/01/2007',
EXPIRY_DATE = '01/01/2099';
GO
CREATE SYMMETRIC KEY MyKey WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY CERTIFICATE MyCert;
GO
To encrypt:
OPEN SYMMETRIC KEY MyKey
DECRYPTION BY CERTIFICATE MyCert
WITH PASSWORD = 'same password as above';
Insert my record, use scope_identity to return primary key into @.CustomerID.
INSERT INTO [Customers] (EncryptByKey(Key_GUID('MyKey'), @.DataToEncrypt, 1, CONVERT( varbinary, @.CustomerID)))
CLOSE SYMMETRIC KEY MyKey
To decrypt:
SELECT CONVERT(varchar(3925), DecryptByKey(EncryptedField, 1, CONVERT( varbinary, @.CustomerID))) as PlainTextData
FROM Customers
WHERE (CustomerID= @.CustomerID)
Everything works fine when I run the decrypt query on the database on our work server. But I'm not getting decrypted data at home. Is the symmetric key or certificate machine specific? If so, that will cause a huge problem when we deploy to a production server.
Thanks in advance for your help!
Did you up the database master key on the server and restore it to your home machine? If not, then the data will not decrypt on your home machine. The database master key is used to encrypt the symmetric key which would then be used to decrypt your data. If you create the "same" symmetric key, but use a different database master key, then the identity will be different which will prevent decryption. This is by design, because it ensures that someone can fake security credentials and gain access to your data.|||That's one thing I didn't do! Thanks Michael. That's got to be it.
Thank you,
Richard
|||You generated different keys. Have a look at http://blogs.msdn.com/lcris/archive/2006/07/06/658364.aspx for how you can regenerate the same key on different machines.
Thanks
Laurentiu
Laurentiu, great article! I didn't realize the Key_Source and Identity_Value were essential to make the key work in multiple databases. I thought as long as the algorithm and name were identical everything would decrypt properly. Once I created a new key like you mentioned in your article, the decryption worked great across both databases. Thanks again!
Richard
sqlBinary column
We have a binary column that stores web passwords.
What is the proper way to access this column?
The sql below is blowing up!
UPDATE AppServices.dbo.DBUsers
SET Password = 'MyPassword'
WHERE UserId ='MyLogin'Here is one way to accomplish this task.
UPDATE AppServices.dbo.DBUsers
SET Password = pwdencrypt('MyPassword')
WHERE UserId ='MyLogin'
pwdencrypt and pwdcompare are two undocumented SQL functions.
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:E269D86B-E84E-45CE-8E33-81D483391080@.microsoft.com...
> We have a binary column that stores web passwords.
> What is the proper way to access this column?
> The sql below is blowing up!
> UPDATE AppServices.dbo.DBUsers
> SET Password = 'MyPassword'
> WHERE UserId ='MyLogin'
What is the proper way to access this column?
The sql below is blowing up!
UPDATE AppServices.dbo.DBUsers
SET Password = 'MyPassword'
WHERE UserId ='MyLogin'Here is one way to accomplish this task.
UPDATE AppServices.dbo.DBUsers
SET Password = pwdencrypt('MyPassword')
WHERE UserId ='MyLogin'
pwdencrypt and pwdcompare are two undocumented SQL functions.
"Arne" <Arne@.discussions.microsoft.com> wrote in message
news:E269D86B-E84E-45CE-8E33-81D483391080@.microsoft.com...
> We have a binary column that stores web passwords.
> What is the proper way to access this column?
> The sql below is blowing up!
> UPDATE AppServices.dbo.DBUsers
> SET Password = 'MyPassword'
> WHERE UserId ='MyLogin'
Sunday, March 11, 2012
Bidirectional Replication
Hi,
can you pls help me to find the suitable Version of SQL Server?
SQL Server will work on the serverside. MS Access on the clientside.
Between these two DBs i need asynchronous bidirectional replication.
What version of SQL Server would be the best solution for this?
Thanx for ur help
Greetz CreanCan you upgrade the databse engine on the MS-Access side to MSDE instead of Jet? If not, I think you've got a real challenge.
-PatP|||Yes i can upgrade to msde.
So what version of sql server should i take?
Thx
Crean|||Use Windows Explorer to check to see if MSDE is provided on your MS-Access CD. If so, use that version of MSDE, otherwise grab the first version that you find (the average user almost always finds the most stable supported version first if they aren't looking for a specific version).
-PatP|||Ok, thx Pat
Greetz Crean
can you pls help me to find the suitable Version of SQL Server?
SQL Server will work on the serverside. MS Access on the clientside.
Between these two DBs i need asynchronous bidirectional replication.
What version of SQL Server would be the best solution for this?
Thanx for ur help
Greetz CreanCan you upgrade the databse engine on the MS-Access side to MSDE instead of Jet? If not, I think you've got a real challenge.
-PatP|||Yes i can upgrade to msde.
So what version of sql server should i take?
Thx
Crean|||Use Windows Explorer to check to see if MSDE is provided on your MS-Access CD. If so, use that version of MSDE, otherwise grab the first version that you find (the average user almost always finds the most stable supported version first if they aren't looking for a specific version).
-PatP|||Ok, thx Pat
Greetz Crean
Wednesday, March 7, 2012
Bfiles
Hello,
I just want to get some chars from a textfile to be shown via SQL+. My intention is to demonstrate the random access possibility to a LOB in Oracle.
Therefore I put a BFILE into a table and try to read and output a small part from the referenced textfile to the screen.
Here is my code:
CREATE TABLE PLSQL(
text_id NUMBER(5) PRIMARY KEY,
text_file BFILE)
CREATE OR REPLACE directory SAMPLES as 'C:\samples'
INSERT INTO PLSQL(text_id,text_file)
values(1,BFILENAME('samples','erle.txt'))
declare
locator_var BFILE:=BFILENAME('SAMPLES','erle.txt');
amount_var INTEGER;
offset_var INTEGER;
output_var VARCHAR2(10);
begin
amount_var:= 10;
offset_var:= 1;
select text_file into locator_var from PLSQL
where text_id = 1;
DBMS_LOB.OPEN(locator_var, DBMS_LOB.LOB_READONLY);
DBMS_LOB.READ(locator_var, amount_var, offset_var, output_var);
DBMS_OUTPUT.PUT_LINE('Begin of Erle: ' || output_var);
end;
/
COMMIT;
when I run the skript I get the following error message:
"SQL> start demo_plsql;
declare
*
FEHLER in Zeile 1:
ORA-22285: Verzeichnis oder Datei fr FILEOPEN-Vorgang ist nicht vorhanden
ORA-06512: in "SYS.DBMS_LOB", Zeile 672
ORA-06512: in Zeile 11"
The directory exists and the file is also there.
So where could be the problem?
In hope for any suggestions.
MaxSorry, can't read German... yet :)
However, it looks like your problem is here:
INSERT INTO PLSQL(text_id,text_file)
values(1,BFILENAME('samples','erle.txt'))
Input into BFILENAME first parameter needs to be upper case in order to match your specification in the CREATE DIRECTORY command.
JoeB
I just want to get some chars from a textfile to be shown via SQL+. My intention is to demonstrate the random access possibility to a LOB in Oracle.
Therefore I put a BFILE into a table and try to read and output a small part from the referenced textfile to the screen.
Here is my code:
CREATE TABLE PLSQL(
text_id NUMBER(5) PRIMARY KEY,
text_file BFILE)
CREATE OR REPLACE directory SAMPLES as 'C:\samples'
INSERT INTO PLSQL(text_id,text_file)
values(1,BFILENAME('samples','erle.txt'))
declare
locator_var BFILE:=BFILENAME('SAMPLES','erle.txt');
amount_var INTEGER;
offset_var INTEGER;
output_var VARCHAR2(10);
begin
amount_var:= 10;
offset_var:= 1;
select text_file into locator_var from PLSQL
where text_id = 1;
DBMS_LOB.OPEN(locator_var, DBMS_LOB.LOB_READONLY);
DBMS_LOB.READ(locator_var, amount_var, offset_var, output_var);
DBMS_OUTPUT.PUT_LINE('Begin of Erle: ' || output_var);
end;
/
COMMIT;
when I run the skript I get the following error message:
"SQL> start demo_plsql;
declare
*
FEHLER in Zeile 1:
ORA-22285: Verzeichnis oder Datei fr FILEOPEN-Vorgang ist nicht vorhanden
ORA-06512: in "SYS.DBMS_LOB", Zeile 672
ORA-06512: in Zeile 11"
The directory exists and the file is also there.
So where could be the problem?
In hope for any suggestions.
MaxSorry, can't read German... yet :)
However, it looks like your problem is here:
INSERT INTO PLSQL(text_id,text_file)
values(1,BFILENAME('samples','erle.txt'))
Input into BFILENAME first parameter needs to be upper case in order to match your specification in the CREATE DIRECTORY command.
JoeB
Between database servers
Hi all,
I like to access a production database server from my development database,
I need to count how fast one table in the production table grows, is there
any way to do it without actually getting on the production server? linked
server?
Thanks.Yes you can create a linked server
http://sqlservercode.blogspot.com/
"Matthew Z" wrote:
> Hi all,
> I like to access a production database server from my development database,
> I need to count how fast one table in the production table grows, is there
> any way to do it without actually getting on the production server? linked
> server?
> Thanks.|||Thanks. Because the company security reseans, I can't use the linked server,
is there a way to work around?
"SQL" wrote:
> Yes you can create a linked server
> http://sqlservercode.blogspot.com/
>
> "Matthew Z" wrote:
> > Hi all,
> > I like to access a production database server from my development database,
> > I need to count how fast one table in the production table grows, is there
> > any way to do it without actually getting on the production server? linked
> > server?
> >
> > Thanks.|||look up OPENROWSET and OPENDATASOURCE in Books on line
http://sqlservercode.blogspot.com/
"Matthew Z" wrote:
> Thanks. Because the company security reseans, I can't use the linked server,
> is there a way to work around?
>
> "SQL" wrote:
> > Yes you can create a linked server
> >
> > http://sqlservercode.blogspot.com/
> >
> >
> > "Matthew Z" wrote:
> >
> > > Hi all,
> > > I like to access a production database server from my development database,
> > > I need to count how fast one table in the production table grows, is there
> > > any way to do it without actually getting on the production server? linked
> > > server?
> > >
> > > Thanks.|||Thanks for reply.
Even I have sa permission to all sql servers, I still can't have have access
from one server to a different server.
"SQL" wrote:
> look up OPENROWSET and OPENDATASOURCE in Books on line
> http://sqlservercode.blogspot.com/
>
> "Matthew Z" wrote:
> > Thanks. Because the company security reseans, I can't use the linked server,
> > is there a way to work around?
> >
> >
> > "SQL" wrote:
> >
> > > Yes you can create a linked server
> > >
> > > http://sqlservercode.blogspot.com/
> > >
> > >
> > > "Matthew Z" wrote:
> > >
> > > > Hi all,
> > > > I like to access a production database server from my development database,
> > > > I need to count how fast one table in the production table grows, is there
> > > > any way to do it without actually getting on the production server? linked
> > > > server?
> > > >
> > > > Thanks.|||Matthew,
as SQL said:
1. Linked Server
2. OPENROWSET or OPENDATASOURCE - neither requires a linked server to be
created
3. Use EM, QA, OSQL or some other application to connect directly to your
other server.
4. Job or DTS to get the value required to your local db
HTH
Jerry
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:46E45877-8585-4709-AEA8-BDD7886E7FC5@.microsoft.com...
> Thanks for reply.
> Even I have sa permission to all sql servers, I still can't have have
> access
> from one server to a different server.
> "SQL" wrote:
>> look up OPENROWSET and OPENDATASOURCE in Books on line
>> http://sqlservercode.blogspot.com/
>>
>> "Matthew Z" wrote:
>> > Thanks. Because the company security reseans, I can't use the linked
>> > server,
>> > is there a way to work around?
>> >
>> >
>> > "SQL" wrote:
>> >
>> > > Yes you can create a linked server
>> > >
>> > > http://sqlservercode.blogspot.com/
>> > >
>> > >
>> > > "Matthew Z" wrote:
>> > >
>> > > > Hi all,
>> > > > I like to access a production database server from my development
>> > > > database,
>> > > > I need to count how fast one table in the production table grows,
>> > > > is there
>> > > > any way to do it without actually getting on the production server?
>> > > > linked
>> > > > server?
>> > > >
>> > > > Thanks.|||Another option is to create an xp_sendmail proc job on the production DB
that will mail the result to yourself whenever you execute this job
"Matthew Z" wrote:
> Thanks for reply.
> Even I have sa permission to all sql servers, I still can't have have access
> from one server to a different server.
> "SQL" wrote:
> > look up OPENROWSET and OPENDATASOURCE in Books on line
> >
> > http://sqlservercode.blogspot.com/
> >
> >
> >
> > "Matthew Z" wrote:
> >
> > > Thanks. Because the company security reseans, I can't use the linked server,
> > > is there a way to work around?
> > >
> > >
> > > "SQL" wrote:
> > >
> > > > Yes you can create a linked server
> > > >
> > > > http://sqlservercode.blogspot.com/
> > > >
> > > >
> > > > "Matthew Z" wrote:
> > > >
> > > > > Hi all,
> > > > > I like to access a production database server from my development database,
> > > > > I need to count how fast one table in the production table grows, is there
> > > > > any way to do it without actually getting on the production server? linked
> > > > > server?
> > > > >
> > > > > Thanks.|||Thanks guys, based on your replies,I got a idea, I may try DTS job & send
mail functions. This is not a one time process, it runs every week for
reporting Purpose.
"Matthew Z" wrote:
> Hi all,
> I like to access a production database server from my development database,
> I need to count how fast one table in the production table grows, is there
> any way to do it without actually getting on the production server? linked
> server?
> Thanks.
I like to access a production database server from my development database,
I need to count how fast one table in the production table grows, is there
any way to do it without actually getting on the production server? linked
server?
Thanks.Yes you can create a linked server
http://sqlservercode.blogspot.com/
"Matthew Z" wrote:
> Hi all,
> I like to access a production database server from my development database,
> I need to count how fast one table in the production table grows, is there
> any way to do it without actually getting on the production server? linked
> server?
> Thanks.|||Thanks. Because the company security reseans, I can't use the linked server,
is there a way to work around?
"SQL" wrote:
> Yes you can create a linked server
> http://sqlservercode.blogspot.com/
>
> "Matthew Z" wrote:
> > Hi all,
> > I like to access a production database server from my development database,
> > I need to count how fast one table in the production table grows, is there
> > any way to do it without actually getting on the production server? linked
> > server?
> >
> > Thanks.|||look up OPENROWSET and OPENDATASOURCE in Books on line
http://sqlservercode.blogspot.com/
"Matthew Z" wrote:
> Thanks. Because the company security reseans, I can't use the linked server,
> is there a way to work around?
>
> "SQL" wrote:
> > Yes you can create a linked server
> >
> > http://sqlservercode.blogspot.com/
> >
> >
> > "Matthew Z" wrote:
> >
> > > Hi all,
> > > I like to access a production database server from my development database,
> > > I need to count how fast one table in the production table grows, is there
> > > any way to do it without actually getting on the production server? linked
> > > server?
> > >
> > > Thanks.|||Thanks for reply.
Even I have sa permission to all sql servers, I still can't have have access
from one server to a different server.
"SQL" wrote:
> look up OPENROWSET and OPENDATASOURCE in Books on line
> http://sqlservercode.blogspot.com/
>
> "Matthew Z" wrote:
> > Thanks. Because the company security reseans, I can't use the linked server,
> > is there a way to work around?
> >
> >
> > "SQL" wrote:
> >
> > > Yes you can create a linked server
> > >
> > > http://sqlservercode.blogspot.com/
> > >
> > >
> > > "Matthew Z" wrote:
> > >
> > > > Hi all,
> > > > I like to access a production database server from my development database,
> > > > I need to count how fast one table in the production table grows, is there
> > > > any way to do it without actually getting on the production server? linked
> > > > server?
> > > >
> > > > Thanks.|||Matthew,
as SQL said:
1. Linked Server
2. OPENROWSET or OPENDATASOURCE - neither requires a linked server to be
created
3. Use EM, QA, OSQL or some other application to connect directly to your
other server.
4. Job or DTS to get the value required to your local db
HTH
Jerry
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:46E45877-8585-4709-AEA8-BDD7886E7FC5@.microsoft.com...
> Thanks for reply.
> Even I have sa permission to all sql servers, I still can't have have
> access
> from one server to a different server.
> "SQL" wrote:
>> look up OPENROWSET and OPENDATASOURCE in Books on line
>> http://sqlservercode.blogspot.com/
>>
>> "Matthew Z" wrote:
>> > Thanks. Because the company security reseans, I can't use the linked
>> > server,
>> > is there a way to work around?
>> >
>> >
>> > "SQL" wrote:
>> >
>> > > Yes you can create a linked server
>> > >
>> > > http://sqlservercode.blogspot.com/
>> > >
>> > >
>> > > "Matthew Z" wrote:
>> > >
>> > > > Hi all,
>> > > > I like to access a production database server from my development
>> > > > database,
>> > > > I need to count how fast one table in the production table grows,
>> > > > is there
>> > > > any way to do it without actually getting on the production server?
>> > > > linked
>> > > > server?
>> > > >
>> > > > Thanks.|||Another option is to create an xp_sendmail proc job on the production DB
that will mail the result to yourself whenever you execute this job
"Matthew Z" wrote:
> Thanks for reply.
> Even I have sa permission to all sql servers, I still can't have have access
> from one server to a different server.
> "SQL" wrote:
> > look up OPENROWSET and OPENDATASOURCE in Books on line
> >
> > http://sqlservercode.blogspot.com/
> >
> >
> >
> > "Matthew Z" wrote:
> >
> > > Thanks. Because the company security reseans, I can't use the linked server,
> > > is there a way to work around?
> > >
> > >
> > > "SQL" wrote:
> > >
> > > > Yes you can create a linked server
> > > >
> > > > http://sqlservercode.blogspot.com/
> > > >
> > > >
> > > > "Matthew Z" wrote:
> > > >
> > > > > Hi all,
> > > > > I like to access a production database server from my development database,
> > > > > I need to count how fast one table in the production table grows, is there
> > > > > any way to do it without actually getting on the production server? linked
> > > > > server?
> > > > >
> > > > > Thanks.|||Thanks guys, based on your replies,I got a idea, I may try DTS job & send
mail functions. This is not a one time process, it runs every week for
reporting Purpose.
"Matthew Z" wrote:
> Hi all,
> I like to access a production database server from my development database,
> I need to count how fast one table in the production table grows, is there
> any way to do it without actually getting on the production server? linked
> server?
> Thanks.
Between database servers
Hi all,
I like to access a production database server from my development database,
I need to count how fast one table in the production table grows, is there
any way to do it without actually getting on the production server? linked
server?
Thanks.
Yes you can create a linked server
http://sqlservercode.blogspot.com/
"Matthew Z" wrote:
> Hi all,
> I like to access a production database server from my development database,
> I need to count how fast one table in the production table grows, is there
> any way to do it without actually getting on the production server? linked
> server?
> Thanks.
|||Thanks. Because the company security reseans, I can't use the linked server,
is there a way to work around?
"SQL" wrote:
[vbcol=seagreen]
> Yes you can create a linked server
> http://sqlservercode.blogspot.com/
>
> "Matthew Z" wrote:
|||look up OPENROWSET and OPENDATASOURCE in Books on line
http://sqlservercode.blogspot.com/
"Matthew Z" wrote:
[vbcol=seagreen]
> Thanks. Because the company security reseans, I can't use the linked server,
> is there a way to work around?
>
> "SQL" wrote:
|||Thanks for reply.
Even I have sa permission to all sql servers, I still can't have have access
from one server to a different server.
"SQL" wrote:
[vbcol=seagreen]
> look up OPENROWSET and OPENDATASOURCE in Books on line
> http://sqlservercode.blogspot.com/
>
> "Matthew Z" wrote:
|||Matthew,
as SQL said:
1. Linked Server
2. OPENROWSET or OPENDATASOURCE - neither requires a linked server to be
created
3. Use EM, QA, OSQL or some other application to connect directly to your
other server.
4. Job or DTS to get the value required to your local db
HTH
Jerry
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:46E45877-8585-4709-AEA8-BDD7886E7FC5@.microsoft.com...[vbcol=seagreen]
> Thanks for reply.
> Even I have sa permission to all sql servers, I still can't have have
> access
> from one server to a different server.
> "SQL" wrote:
|||Another option is to create an xp_sendmail proc job on the production DB
that will mail the result to yourself whenever you execute this job
"Matthew Z" wrote:
[vbcol=seagreen]
> Thanks for reply.
> Even I have sa permission to all sql servers, I still can't have have access
> from one server to a different server.
> "SQL" wrote:
|||Thanks guys, based on your replies,I got a idea, I may try DTS job & send
mail functions. This is not a one time process, it runs every week for
reporting Purpose.
"Matthew Z" wrote:
> Hi all,
> I like to access a production database server from my development database,
> I need to count how fast one table in the production table grows, is there
> any way to do it without actually getting on the production server? linked
> server?
> Thanks.
I like to access a production database server from my development database,
I need to count how fast one table in the production table grows, is there
any way to do it without actually getting on the production server? linked
server?
Thanks.
Yes you can create a linked server
http://sqlservercode.blogspot.com/
"Matthew Z" wrote:
> Hi all,
> I like to access a production database server from my development database,
> I need to count how fast one table in the production table grows, is there
> any way to do it without actually getting on the production server? linked
> server?
> Thanks.
|||Thanks. Because the company security reseans, I can't use the linked server,
is there a way to work around?
"SQL" wrote:
[vbcol=seagreen]
> Yes you can create a linked server
> http://sqlservercode.blogspot.com/
>
> "Matthew Z" wrote:
|||look up OPENROWSET and OPENDATASOURCE in Books on line
http://sqlservercode.blogspot.com/
"Matthew Z" wrote:
[vbcol=seagreen]
> Thanks. Because the company security reseans, I can't use the linked server,
> is there a way to work around?
>
> "SQL" wrote:
|||Thanks for reply.
Even I have sa permission to all sql servers, I still can't have have access
from one server to a different server.
"SQL" wrote:
[vbcol=seagreen]
> look up OPENROWSET and OPENDATASOURCE in Books on line
> http://sqlservercode.blogspot.com/
>
> "Matthew Z" wrote:
|||Matthew,
as SQL said:
1. Linked Server
2. OPENROWSET or OPENDATASOURCE - neither requires a linked server to be
created
3. Use EM, QA, OSQL or some other application to connect directly to your
other server.
4. Job or DTS to get the value required to your local db
HTH
Jerry
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:46E45877-8585-4709-AEA8-BDD7886E7FC5@.microsoft.com...[vbcol=seagreen]
> Thanks for reply.
> Even I have sa permission to all sql servers, I still can't have have
> access
> from one server to a different server.
> "SQL" wrote:
|||Another option is to create an xp_sendmail proc job on the production DB
that will mail the result to yourself whenever you execute this job
"Matthew Z" wrote:
[vbcol=seagreen]
> Thanks for reply.
> Even I have sa permission to all sql servers, I still can't have have access
> from one server to a different server.
> "SQL" wrote:
|||Thanks guys, based on your replies,I got a idea, I may try DTS job & send
mail functions. This is not a one time process, it runs every week for
reporting Purpose.
"Matthew Z" wrote:
> Hi all,
> I like to access a production database server from my development database,
> I need to count how fast one table in the production table grows, is there
> any way to do it without actually getting on the production server? linked
> server?
> Thanks.
Saturday, February 25, 2012
Between database servers
Hi all,
I like to access a production database server from my development database,
I need to count how fast one table in the production table grows, is there
any way to do it without actually getting on the production server? linked
server?
Thanks.Yes you can create a linked server
http://sqlservercode.blogspot.com/
"Matthew Z" wrote:
> Hi all,
> I like to access a production database server from my development database
,
> I need to count how fast one table in the production table grows, is there
> any way to do it without actually getting on the production server? linked
> server?
> Thanks.|||Thanks. Because the company security reseans, I can't use the linked server,
is there a way to work around?
"SQL" wrote:
[vbcol=seagreen]
> Yes you can create a linked server
> http://sqlservercode.blogspot.com/
>
> "Matthew Z" wrote:
>|||look up OPENROWSET and OPENDATASOURCE in Books on line
http://sqlservercode.blogspot.com/
"Matthew Z" wrote:
[vbcol=seagreen]
> Thanks. Because the company security reseans, I can't use the linked serve
r,
> is there a way to work around?
>
> "SQL" wrote:
>|||Thanks for reply.
Even I have sa permission to all sql servers, I still can't have have access
from one server to a different server.
"SQL" wrote:
[vbcol=seagreen]
> look up OPENROWSET and OPENDATASOURCE in Books on line
> http://sqlservercode.blogspot.com/
>
> "Matthew Z" wrote:
>|||Matthew,
as SQL said:
1. Linked Server
2. OPENROWSET or OPENDATASOURCE - neither requires a linked server to be
created
3. Use EM, QA, OSQL or some other application to connect directly to your
other server.
4. Job or DTS to get the value required to your local db
HTH
Jerry
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:46E45877-8585-4709-AEA8-BDD7886E7FC5@.microsoft.com...[vbcol=seagreen]
> Thanks for reply.
> Even I have sa permission to all sql servers, I still can't have have
> access
> from one server to a different server.
> "SQL" wrote:
>|||Another option is to create an xp_sendmail proc job on the production DB
that will mail the result to yourself whenever you execute this job
"Matthew Z" wrote:
[vbcol=seagreen]
> Thanks for reply.
> Even I have sa permission to all sql servers, I still can't have have acce
ss
> from one server to a different server.
> "SQL" wrote:
>|||Thanks guys, based on your replies,I got a idea, I may try DTS job & send
mail functions. This is not a one time process, it runs every week for
reporting Purpose.
"Matthew Z" wrote:
> Hi all,
> I like to access a production database server from my development database
,
> I need to count how fast one table in the production table grows, is there
> any way to do it without actually getting on the production server? linked
> server?
> Thanks.
I like to access a production database server from my development database,
I need to count how fast one table in the production table grows, is there
any way to do it without actually getting on the production server? linked
server?
Thanks.Yes you can create a linked server
http://sqlservercode.blogspot.com/
"Matthew Z" wrote:
> Hi all,
> I like to access a production database server from my development database
,
> I need to count how fast one table in the production table grows, is there
> any way to do it without actually getting on the production server? linked
> server?
> Thanks.|||Thanks. Because the company security reseans, I can't use the linked server,
is there a way to work around?
"SQL" wrote:
[vbcol=seagreen]
> Yes you can create a linked server
> http://sqlservercode.blogspot.com/
>
> "Matthew Z" wrote:
>|||look up OPENROWSET and OPENDATASOURCE in Books on line
http://sqlservercode.blogspot.com/
"Matthew Z" wrote:
[vbcol=seagreen]
> Thanks. Because the company security reseans, I can't use the linked serve
r,
> is there a way to work around?
>
> "SQL" wrote:
>|||Thanks for reply.
Even I have sa permission to all sql servers, I still can't have have access
from one server to a different server.
"SQL" wrote:
[vbcol=seagreen]
> look up OPENROWSET and OPENDATASOURCE in Books on line
> http://sqlservercode.blogspot.com/
>
> "Matthew Z" wrote:
>|||Matthew,
as SQL said:
1. Linked Server
2. OPENROWSET or OPENDATASOURCE - neither requires a linked server to be
created
3. Use EM, QA, OSQL or some other application to connect directly to your
other server.
4. Job or DTS to get the value required to your local db
HTH
Jerry
"Matthew Z" <MatthewZ@.discussions.microsoft.com> wrote in message
news:46E45877-8585-4709-AEA8-BDD7886E7FC5@.microsoft.com...[vbcol=seagreen]
> Thanks for reply.
> Even I have sa permission to all sql servers, I still can't have have
> access
> from one server to a different server.
> "SQL" wrote:
>|||Another option is to create an xp_sendmail proc job on the production DB
that will mail the result to yourself whenever you execute this job
"Matthew Z" wrote:
[vbcol=seagreen]
> Thanks for reply.
> Even I have sa permission to all sql servers, I still can't have have acce
ss
> from one server to a different server.
> "SQL" wrote:
>|||Thanks guys, based on your replies,I got a idea, I may try DTS job & send
mail functions. This is not a one time process, it runs every week for
reporting Purpose.
"Matthew Z" wrote:
> Hi all,
> I like to access a production database server from my development database
,
> I need to count how fast one table in the production table grows, is there
> any way to do it without actually getting on the production server? linked
> server?
> Thanks.
Friday, February 24, 2012
Best way to track who is accessing a record
I have an application that has a SQL back end, and I want to be able to track who is accessing a record so that no one else can access it at the same time. I was going to do this with a table or application state, but how can I avoid keeping files locked when someone abandons a session? Any Ideas?There are third party tools to do it but try the links below for code to track updates and links to the third party tools. Hope this helps.
http://www.aspfaq.com/show.asp?id=2448
http://www.aspfaq.com/show.asp?id=2448
Best Way to Synch 2 Tables?
All,
Is this something I should use a "tool" (SQL Data Compare, SqlSync,
etc.) to do, or could I pull this off reliably every night just using
SQL from the query I could construct to do the job from inside my
Access DB?
A scheduled job in SQL Server Agent.
I have an Access DB. On a nightly basis, I want to look at an Other
DB (not Access, but SQL) and:
+ Add any new records from Other.Clients into Access.Clients
Quote:
Originally Posted by
Update 9 fields from Other.Clients into Access.Clients where changes have occured in Other.Clients.
Is this something I should use a "tool" (SQL Data Compare, SqlSync,
etc.) to do, or could I pull this off reliably every night just using
SQL from the query I could construct to do the job from inside my
Access DB?
And for question Deux, if I can just use the SQL I will write, what's
the best way to kick off the process at 3:00 am every morning?
Thanks,
PatrickPatrick A (parkins@.stradley.com) writes:
Quote:
Originally Posted by
And for question Deux, if I can just use the SQL I will write, what's
the best way to kick off the process at 3:00 am every morning?
A scheduled job in SQL Server Agent.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Tuesday, February 14, 2012
Best Way to Handle Data Access
This may be a loaded question, but I was wondering if anyone could outline the main options we have available to us for handling data access with ASP.net 2.0 / SQL Server 2005.
I know at one extreme you can simply use the Data Controls like GridView, FormView, and DetailsView, specifying the necessary SQL on a page-by-page basis. At the other extreme, my understanding is that you can basically set up a SQLConnection object, explicitly specify your SQL (or call a stored procedure) and then execute these items from within the code.
Can anyone either outline the main options or point me to a resource that better explains this? Thanks!
Josh
This is an awfully tall order for a forums question. I suggest you take a look at theData Access section of the ASP.NET Developer Center. Or hit upGoogle.|||Thanks for the prompt reply; this looks like exactly the kind of resource I was looking for. My suspicion is that for relatively straightforward data models you can just use the DataControls and write a minimum of code. I'm guessing that for more sophisitcated apps, you'll have to write a data access layer of classes which maybe calls the Stored Procedures so that you wind up using the ObjectDataSourceControl? I may have made some statements here which really don't make sense.
If I come to any reasonably straightforward or simple conclusions, I'll make sure to summarize them here. Thanks again!
Josh
Friday, February 10, 2012
Best way to Connect MS Access to SQL 2000 Tables?
Hello All;
I've about read myself to death, and have yet to find the simple answer to the question of what is the best way to connect my MS Access 2003 front-end to an SQL 2000 back-end?
Can anyone answer that simple question?
Grarful for ANY resonse,
Larry.Hello All;
I've about read myself to death, and have yet to find the simple answer to the question of what is the best way to connect my MS Access 2003 front-end to an SQL 2000 back-end?
Can anyone answer that simple question?
Grarful for ANY resonse,
Larry.
I've not worked with Access in a while, but here is my .02. Your mileage may vary:
1. Linked tables are done through ODBC. ODBC has some limitations. Access has some additional limitations. It's fairly straightfoward to link tables, but you may run into issues down the road. We ran into one recently where MS Access mis-identified the primary key and displayed all data as "#Deleted" for a specific table. This wasn't something I set up, just something I was asked to trouble shoot.
Users can't make schema changes to linked tables/views; however depending on their permissions they can add/update/delete records. MS Access defaults to opening a linked table as an updateable recordset.
2. Access Data Projects (or ADP) eliminate the shortcomings of ODBC, but there may be some limitations on MS Access of which I am unaware. I have resorted to using ADPs only on my local desktop as a quick way to generate reports with better formatting than my limited web skills will permit.
ADPs give you a window directly onto the database server; you need to be cautious about what permissions you assign to users as changes that they make (in views or tables) will affect your database. I AM NOT condoning, recommending or endorsing the ADP route; I have made very limited use of it for writing reports that are for my use only. I don't know how well it behaves "in the wild". ADP does give you the ability to link to objects other than just tables and views (specifically, you can access stored procedures).
Regards,
hmscott|||I do a lot of Access interfaces for SQL Server back-ends, and I choose Access Data Projects every time, hands-down, no contest.
I've about read myself to death, and have yet to find the simple answer to the question of what is the best way to connect my MS Access 2003 front-end to an SQL 2000 back-end?
Can anyone answer that simple question?
Grarful for ANY resonse,
Larry.Hello All;
I've about read myself to death, and have yet to find the simple answer to the question of what is the best way to connect my MS Access 2003 front-end to an SQL 2000 back-end?
Can anyone answer that simple question?
Grarful for ANY resonse,
Larry.
I've not worked with Access in a while, but here is my .02. Your mileage may vary:
1. Linked tables are done through ODBC. ODBC has some limitations. Access has some additional limitations. It's fairly straightfoward to link tables, but you may run into issues down the road. We ran into one recently where MS Access mis-identified the primary key and displayed all data as "#Deleted" for a specific table. This wasn't something I set up, just something I was asked to trouble shoot.
Users can't make schema changes to linked tables/views; however depending on their permissions they can add/update/delete records. MS Access defaults to opening a linked table as an updateable recordset.
2. Access Data Projects (or ADP) eliminate the shortcomings of ODBC, but there may be some limitations on MS Access of which I am unaware. I have resorted to using ADPs only on my local desktop as a quick way to generate reports with better formatting than my limited web skills will permit.
ADPs give you a window directly onto the database server; you need to be cautious about what permissions you assign to users as changes that they make (in views or tables) will affect your database. I AM NOT condoning, recommending or endorsing the ADP route; I have made very limited use of it for writing reports that are for my use only. I don't know how well it behaves "in the wild". ADP does give you the ability to link to objects other than just tables and views (specifically, you can access stored procedures).
Regards,
hmscott|||I do a lot of Access interfaces for SQL Server back-ends, and I choose Access Data Projects every time, hands-down, no contest.
Subscribe to:
Posts (Atom)