multiple threads accesing msAccess - Database Discussion Boards

Re: an intersting sql problem

GuyThiebaut10-Oct-07 22:53

GuyThiebaut

10-Oct-07 22:53

richardye wrote:
As there isn't much memory can be used, how to deal with it perfectly (both in time and in space)?

What is going to happen when you delete all of those records is that the transaction log is going to grow.

Regarding disk space(I take it you mean disk space rather than memory?):
Backup the transaction log to clear it out of pending transactions first.
You will then need to shrink the transaction log.
Run your script to delete the records.
Then straightaway backup the transaction log and shrink it again.
This is the only way I can think of to keep disk space usage at a minimum.

Regarding speed:
All I can suggest is that you do all of this out of hours when nobody else is using the database.
If anybody else is accessing the table you are deleting from there is always the chance that you could get a lock, for a period of time, or even a deadlock.

You always pass failure on the way to success.

How to shrink a transaction log - and make sure that it actually shrinks.

GuyThiebaut10-Oct-07 23:04

GuyThiebaut

10-Oct-07 23:04

Shrinking a transaction log is a science in itself, so here is a very useful script that I use(you may have to run the script more than once on the same transaction log for it to recover all the free space).

Click here for the google document.

You always pass failure on the way to success.

SQL Server Replication

Anilkumar K V10-Oct-07 18:10

Anilkumar K V

10-Oct-07 18:10

I am trying Transactional Replication between SQL Server 2005 SE and SQL Server Express installed on a client machine. I am getting "Uninitialized Subscription" status on Replication monitor. The error message is like "The job failed. Unable to determine if the owner (DOMAIN\Joseph.Thomas) of job ACR-MANGO-October10-OCT-10 Pub-ACR-ANJILI\SQLEXPRESS-35 has server access (reason: Could not obtain information about Windows NT group/user 'DOMAIN\Joseph.Thomas', error code 0x5. [SQLSTATE 42000] (Error 15404) The statement has been terminated. [SQLSTATE 01000] (Error 3621))."

The steps i followed are

1. Create a publication in SQL Server 2005 SE.
I could successfully create Publications on Server. The security setting i gave are
SnapShot Agent Process: Run Under SQL Server Agent Service Account
Connect to publisher: By Impersonating the process account

2. Create subscription on SQL Server Express
Distribution Agent Security:Run under SQL Server Agent Service account
Connect To Distributor:By Impersonating the process account
Connect To the Subscriber:By Impersonating the process account
Sync Schedule
Agent Location: Distributor Run Continuously

3. Add a new login for the SQL Server Express user in SQL Server 2005 SE. Give role as sysadmin

Please advise
Thanks

Problem Regarding trigger

Aavesh Agarwal10-Oct-07 17:52

Aavesh Agarwal

10-Oct-07 17:52

I have 4 After triggers and i want to give the order for them in which they fired..
i can do that with the help of sp_setordertrigger but with this i can only specify which one will be fired first and which one will be last but what abut the rest of triggers how can i order them..please help me..in this i m new for trigger...

Sucess need one master Stroke

Thanks and Regards
Aavesh Agarwal

Re: Problem Regarding trigger

joemonvarghese10-Oct-07 19:29

joemonvarghese

10-Oct-07 19:29

hi
you can set the First and Last trigger as you said.Rest of the triggers are fired depending on the trigger creation..So u create the trigger in the order in which you want to fire

regards
JOE

Re: Problem Regarding trigger

Aavesh Agarwal10-Oct-07 20:15

Aavesh Agarwal

10-Oct-07 20:15

Thanks joe,
I got the answer.

Thanks and Regards
Aavesh Agarwal

Trying to figure out how to secure the db better. Starting with:Limit user access.

foolios10-Oct-07 15:46

foolios

10-Oct-07 15:46

Asp.Net/C#/SQL 2005

I created a user and login under security for the database.

In the web.cfg I have:

<connectionstrings>
<add name="ConnectionString1" connectionstring="Data Source=X2\SQLEXPRESS;Initial Catalog=TheDB;Integrated Security=True"
providername="System.Data.SqlClient">
<add name="ReadOnlyConnectionString"
connectionstring="Data Source=localhost\SQLEXPRESS;Initial Catalog=TheDB;
Integrated Security=False;User ID=userReaderOnly;Password=123"
providername="System.Data.SqlClient">

in the codebehind file:

string connectionString = ConfigurationManager.ConnectionStrings["ReadOnlyConnectionString"].ConnectionString;

SqlConnection conn = new SqlConnection(connectionString);

errors at the line:
conn.Open();

The Error:
A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

multiple threads accesing msAccess

dsovino10-Oct-07 14:42

dsovino

10-Oct-07 14:42

I was happily working on my solution with access, everything going sweet.

Until i started to use backgroundworkers doing some inserts to the database, while the main thread was accessing the database calling the tableadapters as well for it's own purposes...

Is there any way MSAcess can manage multiple calls to the database, or should i just forget about it and start working on something different.

I was planning to include in the deploy the mdb file. I tested it, and it worked fine. But with this?

Suggestions PLEASE Confused | :confused:

Thanks for everyones time.

Daniel Sovino

how can i insert mpeg files to DB?

ebtihal10-Oct-07 13:25

ebtihal

10-Oct-07 13:25

Hi,
i'm using sql server 2005 i want to know the way to insert mpeg files???

bleave in your self

Re: how can i insert mpeg files to DB?

Christian Graus10-Oct-07 14:07

Christian Graus

10-Oct-07 14:07

Putting aside the obvious answer ( google can tell you in seconds ), why would you want to ? MPEGs are big, and no media player can play them without a physical file, so storing paths to files seems to make more sense to me.

Christian Graus - Microsoft MVP - C++

"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )

Re: how can i insert mpeg files to DB?

Vidhya Sagar10-Oct-07 16:27

Vidhya Sagar

10-Oct-07 16:27

I agree with Graus, this is the good method. Since inserting huge file into db makes it huge and will overhead to administrative perspective.

In sql2005 it is not possible to insert videos. In SQL2008 (COde Name:katmai) has these facilities.

Vidhya Sagar

Table Column Decimal Value

Brendan Vogt10-Oct-07 5:22

Brendan Vogt

10-Oct-07 5:22

Hi,

I am using SQL Server 2005 and I have a column of type decimal. Default it adds decimal(18, 0). My value that I will have will be 9.99. How would I change decimal(18, 0) to accommodate this?

Please can someone help??

Thanks

Re: Table Column Decimal Value

pmarfleet10-Oct-07 5:36

pmarfleet

10-Oct-07 5:36

decimal(18,2) specifies a column that can hold values with 18 decimal digits in total, including 2 decimal digits to the right of the decimal point.

Paul Marfleet

a problem about m_pConnection->Execute

nibabug10-Oct-07 4:31

nibabug

10-Oct-07 4:31

str+="CREATE TABLE ";
str+=m_TableName;
str+="(ID INTEGER,FangWei TEXT,FuYang TEXT,QingXie TEXT)";
try{
m_pConnection->Execute(str.AllocSysString(),&RecordsAffected,adCmdText);
}
when debug to here, the following appear:
Unhandled exception in SerialRecieve.exe(KERNEL32.DLL):0xE06D7363:Microsoft C++ Exception.

str is a CString variable,which is the table name, the program passed the compile and the build, but when debug, it can not go through.

maybe it is a easy problem, but i can not resolve it

help me please!
thanks a lot!

wuhuaiji

Bulk insert - Fewer fields than in DB

clint198210-Oct-07 4:17

clint1982

10-Oct-07 4:17

Hi I want to use bulk insert in order to insert information from a CSV to the database.
The CSV contains fewer fields than the DB.
I don't want to use a format file, I want to specify all the options within the with clause.
I need to map the fields in the CSV to the fields within the Database table.

Thanks,
Clint

Re: Bulk insert - Fewer fields than in DB

PIEBALDconsult10-Oct-07 6:30

PIEBALDconsult

10-Oct-07 6:30

I can't help with that; I use bcp with a format file to load a table defined specifically to accept the bulk-loaded data, then I move the data to the target tables(s).

Re: Bulk insert - Fewer fields than in DB

GuyThiebaut10-Oct-07 7:01

GuyThiebaut

10-Oct-07 7:01

I can't help with a bulk insert - however would doing an import via Enterprise Manager be an option?

I mention this because you can map fields through the import wizard and it should cover what you want to do.

Of course this is a once off option for ad-hoc jobs, but if doing a bulk insert is not absolutely necessary and this is an ad-hoc job I'd suggest looking at this option.

In Enterprise Manager it is under Tools/Data Transformation Services/Import Data...
You an also get to this via the SQL Server tab on your start menu under Import and Export Data

Hope this helps...

You always pass failure on the way to success.

Calling reports from server logged in user wise

MSinha10-Oct-07 3:50

MSinha

10-Oct-07 3:50

Hi,
I am using Sql server 2005 reporting services. I have created reports on server. I want to connect reportserver from client's PC and the Reports should come with Records as logged in User wise. This I want to place on different panes after calling them, as this happens with Google Analytics and msn sites. To place the records, I will use AJAX controls.
Can any one help me.
With Thanks

Re: Calling reports from server logged in user wise

pmarfleet10-Oct-07 4:49

pmarfleet

10-Oct-07 4:49

Your question, as it stands, is of too general a nature to be answered. Identify specific problems you require help with and post the questions in the relevent forums.

Paul Marfleet

Re: Calling reports from server logged in user wise

MSinha10-Oct-07 5:29

MSinha

10-Oct-07 5:29

I have problem calling reports from client PC, which are generated already and are on server, logged in user wise.

Re: Calling reports from server logged in user wise

pmarfleet10-Oct-07 5:37

pmarfleet

10-Oct-07 5:37

MSinha wrote:
logged in user wise

I don't understand what you mean.

Please post your code. This will make it easier to identify your problem.

Paul Marfleet

CreateQueryDef() pause needed

new_phoenix10-Oct-07 3:40

new_phoenix

10-Oct-07 3:40

I appreciate the assistance that all of you have provided so far. However, I have encountered a slight glitch with the CreateQueryDef() method. The problem is that it seems to take a while for the QueryDef to be actually created and available for use in the following code.

After I create a QueryDef, I need to use it in the very next subroutine, a point in which it might not actually become available in MS Access. Is there a way to pause execution of the subroutine until the QueryDef is actually created and available for use?

Re: CreateQueryDef() pause needed

DQNOK10-Oct-07 5:32

DQNOK

10-Oct-07 5:32

I've not tried this...

Perhaps something like:

CreateQueryDef("mynewquery",...)

while( notexists( "mynewquery" ) )
   doevents
loop

qdf = QueryDefs("mynewquery")

Those are not the right names, but maybe the right idea.

Let us know if you get a solution. I may want to use it myself.

David

There has got to be a more elegant way to do this...

new_phoenix10-Oct-07 10:13

new_phoenix

10-Oct-07 10:13

Here is what I did, and interestingly enough...it works.

I created code that essentially does nothing but execute a loop and pass a counter to a control. Then I added a control to the form, left it visible, but essentially hid it from the form by making the textbox's background color, border color, and fore color the exact same as the underlying form color. This provided something for the processor to do while the QueryDef was being created. I essentially needed to count to 10000.

Here is the code:

'Provides time to create QueryDef qryFinalResult
Dim intCount As Integer
intCount = 0
For intCount = 1 To 10000
    txtCounter.SetFocus
    txtCounter.Text = "Count: " & intCount & "."
    intCount = intCount + 1
Next

Does anybody have a more elegant way to do this? This code prevents that error message from coming up indicating that the QueryDef that I had just created does not exist. When I step through the code myself, the processor has enough time to create the QueryDef object, but when I run it as an application, it does not have time to create it in time to use it.

How to query all of the tab_name in the same database

nibabug10-Oct-07 1:16

nibabug

10-Oct-07 1:16

when my program run, i want to creat a new table, but i do not know if the name is exist.
so, i want to check that if the name can be used or no, but i don't know how to query all the table names

give me a help, please
i am just starting lerning SQL and VC++
thanks a lot

wuhuaiji

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.