Database

importing data from access table in a sql server table

26-May-09 20:51

You stated that you want to try something that is prohibited. Ergo, everyone who helps, is helping you in breaking a law Frown | :(

I are troll Smile | :)

laziale25-May-09 23:25

laziale

25-May-09 23:25

hello there!
I have an access DB filled with tables and I want to copy the tables along with the data in it to a sql server db table. Is that possible? Thanks in advance, Laziale

Re: importing data from access table in a sql server table

Giorgi Dalakishvili25-May-09 23:57

Giorgi Dalakishvili

25-May-09 23:57

You can use SQL Server Integration Services[^]

Giorgi Dalakishvili

#region signature
My Articles

Asynchronous Registry Notification Using Strongly-typed WMI Classes in .NET [^]

My blog
#endregion

insert into multiple server table in one transaction

Abdul Rahman Hamidy25-May-09 17:52

Abdul Rahman Hamidy

25-May-09 17:52

Dear All,
I have two Server which exists in different locations with different ip address and instance name.both servers run SQL 2005
as "Server A" IP address is 72.1.1.2 and i can access SQL Server instance as 72.1.1.2 with username and password, and the other server is 192.168.2.3 which i can access sql server instance as 192.168.2.3
I have the same database with same tables in both servers. now i want to insert the same value with a single transaction in both servers.
I mean i have a table called "tblA" with three columns in both servers.
now i want to insert in this table in both server with a single transaction.
how can i insert values into the table with one transaction.
if insertion fails rollback should occur on both servers.

Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan

Re: insert into multiple server table in one transaction

ScottM126-May-09 3:11

ScottM1

26-May-09 3:11

SQL Server replication:
http://msdn.microsoft.com/en-us/library/ms151198.aspx[^]

Re: insert into multiple server table in one transaction

Abdul Rahman Hamidy26-May-09 19:57

Abdul Rahman Hamidy

26-May-09 19:57

thx for answer,
I think you didn't understand my question, i am asking i would like use an insert statement with one transaction like let say
Begin Transaction
try
Insert into "SERVER A"."DATABASEA"."TABlEA" values (...)
Insert into "SERVER B"."DATABASEA"."TABlEA" values (...)
Commit Transaction
catch
Rollback

I want like this, but I need help how to do it?

Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan

Re: insert into multiple server table in one transaction

Eddy Vluggen26-May-09 20:47

how to connect from Ms.Access to Sql server ?

26-May-09 20:47

I believe that transactions are bound to the server, but I could be mistaken.

Are you using MSSQL? If so, you could create a linked server. That way you "link" the tables into the local database, giving you the option to select from both servers;

SELECT * FROM [Server].[Database].[Schema].[Table]

I haven't tried this with transactions, but I guess that it could work, since all transactions can be handled local.

Good luck Smile | :)

I are troll Smile | :)

xingselex25-May-09 4:45

xingselex

25-May-09 4:45

how to connect Access2007 to sql server ? i want to use Access as inter face. whenever i edit record in table in Access, it will affect to sql server directly. because in Access we can see nested table if that tables have relationship. so how to do with this ?

Re: how to connect from Ms.Access to Sql server ?

Eddy Vluggen25-May-09 6:41

Assigning values in an array...

25-May-09 6:41

This[^] article describes the steps both for importing and for linking SQL Server data into Access 2007.

I are troll Smile | :)

new_phoenix25-May-09 3:55

new_phoenix

25-May-09 3:55

I need some assistance with a small application. I need to loop through an existing array and to assign a value to it in a pattern. I thought that I could do it, but I am really at a loss as to how to proceed. I tried a wide variety of approaches but it does not yield the correct results.

As a smaller scale version of the array. Let us say that the array consists of 392 elements. The first element of the array consists of date values from 12/30/2007 and it proceeds up until 1/24/2009. The second element should be number values from 1 to 13.

The pattern is that there should be 28 values with the value 1, and then 28 values with the value 2, and then 28 values with the value of 3. This procedure continues with 28 values of the value 13. After the 28th value of 13, the numbering system should start over again with 28 values with the value of 1. This should populate all of the elements of the 392 element array.

The code below that I have written, for some reason, does not include the 28th value for each of these assignments. Also, it includes a 14th value following the last 13 value assignment. Please provide me with some assistance! Confused | :confused:

Public Function ZodiacPeriod(dteInputDate)
    Dim dteStart As Date
    Dim dteEnd As Date
    Dim intDateCount As Integer
    Dim intRow As Integer
    Dim intX As Integer
    Dim intCounter As Integer
    Dim arrArray(392, 1)
   
    intX = 1
    dteStart = #12/30/2007#
    dteEnd = #1/24/2009#
    intDateCount = DateDiff("d", dteStart, dteEnd)
    
    'Loading the array with dates
    For intRow = 1 To intDateCount
        arrArray(intRow, intColumn) = dteStart
        dteStart = dteStart + 1
    Next

    'The second element value is assigned here
    For intRow = 0 To 392
        If intCounter <= 27 Then
            If intX >= 14 Then
                intX = 1
            End If
            intCounter = intCounter + 1
        Else
            intCounter = 1
            If intX < 14 Then
                intX = intX + 1
            End If
        End If
        arrArray(intRow, 1) = intX
    Next intRow

    'Now to go through the array and return the value in the array
    For intRow = 0 To 392
        If arrArray(intRow, 0) = dteInputDate Then
            ZodiacPeriod = arrArray(intRow, 1)
            Exit For
        End If
    Next intRow
End Function

I have encountered problems in which a value of 14 is assigned. I have encountered problems in which the requirements are not matched. Any assistance would be appreciated.

Re: Assigning values in an array...

riced25-May-09 7:03

riced

25-May-09 7:03

Three points.
1. You fill the array with dates starting at index = 1, but with numbers starting at index = 0. Is this correct?

2. You could try something like

intRow = 0
For j = 1 to 13
   for i = 1 to 28
       intRow = intRow + 1
       arrArray(intRow,1 ) = j
   next i
next j

3. Wrong forum - there is a VB one (though people tend to be VB6 hostile, and this looks like VB6 or VBA to me.) Smile | :)

Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis

Article Status

specialdreamsin25-May-09 2:27

specialdreamsin

25-May-09 2:27

Hi all,

I have written one article. About 3 days back. But my article status is still showing PENDING. What to do next ?

Please any one reply,

Ramesh Sambari

Knowledge is like honey.
Collect it, to make life sweet.

Re: Article Status

Henry Minute25-May-09 4:23

Henry Minute

25-May-09 4:23

Your article is probably waiting moderation from senior members of CodeProject.
Unfortunately you have submitted it at a weekend and a public holiday for large parts of the CP community, so this might make things a little slower.

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

Folder Permissions

jonhbt25-May-09 1:25

Re: Folder Permissions [modified]

25-May-09 1:25

Hi,

I need to find what are the permissions on a specified folder on the computer. I need to do this by using SQL code. Is it possible to create a stored procedure to showthe permissions of a folder? if yes where can I find some examples to follow please or do someone has any examples please?

Thanks

Eddy Vluggen25-May-09 7:03

25-May-09 7:03

That would be done by executing the ATTRIB command, through the xp_cmdshell, a stored procedure that's blocked for safety.

EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
EXEC xp_cmdshell 'attrib "C:\Program Files"'

You can read about safety here[^].

I are troll Smile | :)

modified on Monday, May 25, 2009 2:23 PM

jonhbt25-May-09 19:30

25-May-09 19:30

Thanks for your reply. how can I modify this to pass the path as a parameter? I am trying to do it but there is no output.

Eddy Vluggen26-May-09 0:28

26-May-09 0:28

You can encapsulate the commands in a stored procedure like this;

CREATE PROCEDURE ExecCmd 
	@PATH AS VARCHAR(1024)
AS
BEGIN
	DECLARE @Command AS VARCHAR(1024)
	SET @Command = REPLACE('attrib "<path>"', '<path>', @PATH)
	EXEC xp_cmdshell @Command
END

Calling the stored procedure with a parameter is done like this;

EXEC ExecCmd 'C:\Program Files'

Good luck

I are troll Smile | :)

jonhbt26-May-09 1:46

26-May-09 1:46

Thaks for the answer. It is working fine for the Program Files path. the strange thing is that it is not working for other folders that I am passing. Is only returning the folder path only, without any R or something else. And these folders have full permissions (Everyone has full permissions). Do you have an idea of what is going wrong?

Thanks

Eddy Vluggen26-May-09 6:15

26-May-09 6:15

Sorry, my bad. The ATTRIB command returns the attributes of a file/folder. You don't want the attributes, but the effective permissions per user/windows account. These can be obtained in a similar fashion, using the CACLS command;

CACLS "C:\Program Files"

Hope this helps Smile | :)

I are troll Smile | :)

jonhbt26-May-09 19:54

26-May-09 19:54

Thanks very much. It realy works. Sry for bothering again but if I had to find only the permissions for the current user how could i modify that? only if possible? Thanks again

Eddy Vluggen26-May-09 20:42

26-May-09 20:42

I'm afraid that's somewhat harder to do; the effective permissions aren't set for a user, but for different groups of people. That's one of the reasons why we don't check if we got permission to write to a folder, but simply write, relying on the exception-mechanism to warn when there's insufficient rights.

The only way of getting the permissions for the user, is checking whether he/she is a member of the groups that get listed. Your user may be a member of multiple groups, so that makes it somewhat more complex.

Your user can have read-access for a particular folder, based on the CREATOR OWNER group, but he/she might *also* have write permissions for *that same* folder if he/she's also a member of BUILTIN\Administrators.

That means three steps;
* Get the permissions for each group on a folder
* Check whether the user is part of that group
* Add all permissions, for each group that the user is a member of. That's the effective permission for the folder.

Good hunting Smile | :)

I are troll Smile | :)

jonhbt26-May-09 20:50