Different Access Files DTS To Mssql

Mar 15, 2005

We use a program that creates a access database for each of our payperiods. Its a pain to get a full history of a person from each one. I want to setup a way I can combine all of the access files into one table in mssql. I have a DTS I created into a .bas file. My plan is to make the vb.net code get a directory of the files, then run a function (the .bas file) on each directory appending the access table to the mssql table. The struckture of the access files are that each file is in its own directory, like below:





\servernameTAcompany7-25-04Trans.mdb


\servernameTAcompany8-08-04Trans.mdb


\servernameTAcompany8-22-04Trans.mdb





The table is called "Transactions" and they are the same in each mdb file in terms of fields. Every two weeks there is a new directory that is added to the company directory. So I can either dump all and append all, or only append new data and only use the dump/append when there is a problem.





I am just not sure how to do that. Any ideas?

View 3 Replies


ADVERTISEMENT

How To Convert MSSQL Database Into .sql Files?

Apr 14, 2005

Hello ,
I'd like to convert the database + sp + tables + data into .sql file?
How

View 6 Replies View Related

Update MSSQL Database From .MDX Files

Sep 7, 2004

Hello guys,

Here is the case. I have up and running database on MSSQL server. My client deliver me updates. Since now they were as INSERT statements, but they decided to change some stuff at their side. Now the updates will come as pairs of files (.dbf and .mdx) for each table...

How I can insert them in the running database?

View 1 Replies View Related

Export MSsql Data To Excel Files

May 12, 2007

Hi

i have a MSsql file wich i want to export information from.
Every post in the database should be exportet to a own single file with all the information about the post.
So from a single question to the database i would like to have a lot of files named by a speciel filed i the post.
I also want to be able to design what the excel file should look like.

How can i do this?

Greatful for help.

/Mitmit

View 1 Replies View Related

MSSQL Database Files: Is Licensing Required?

Nov 14, 2007

Hello
I'm developing a simple visual basic.net application and I have a licensing question about database files.

If I were to use a MSSQL database file as a data source (just like you would use a MS Access database file) no server.

Would I have to pay any sort of licensing, and if so what would that be?

View 5 Replies View Related

Feasability Of Storing PDFs And WORD.DOC Files In MSSQL?

Jul 23, 2005

I'm building a system when one can upload a document to the website.I will be storing the document on the hard-drive for quick/easy access,but I was also thinking of storing it in an existing database since mostof the sites information is all stored there.As well there would be only one place to worry about backing up. And ifthe file on the hard-drive was ever missing or became corrupted, I couldrestore it form tha database. Is this feasable? Has anyone ever done this?--* Don VaillancourtDirector of Software Development**WEB IMPACT INC.*phone: 416-815-2000 ext. 245fax: 416-815-2001email: Join Bytes! <mailto:donv@webimpact.com>web: http://www.web-impact.com/ This email message is intended only for the addressee(s)and contains information that may be confidential and/orcopyright. If you are not the intended recipient pleasenotify the sender by reply email and immediately deletethis email. Use, disclosure or reproduction of this emailby anyone other than the intended recipient(s) is strictlyprohibited. No representation is made that this email orany attachments are free of viruses. Virus scanning isrecommended and is the responsibility of the recipient./

View 5 Replies View Related

Access && Mssql

Aug 14, 2003

We developed an application . The application is an ADP with MSSQL.
What i need to do if i want to deploy to
users ? Because all the forms - record source belong to my server.

View 1 Replies View Related

ACCESS To MSSQL -&> Best Way To Go?

Apr 30, 2007

Hi, what is the best way to go to migrate from access to MSSQL on a remote (hosted) server? do I have to create manually de tables and then move the data? or is there a way to create the creation scripts from the ACCESS database?

Thank you
Diego

View 2 Replies View Related

MsSQL / Access On Linux?

May 24, 2005

Is it possible to install MsSQL / Access on Linux? The reason I am asking is I have a linux virtual dedicated server, and develop both php/mysql and asp/mssql/access database, is there another program or something I can run on my server to allow development of programs needs mssql/access?

Thanks in advance for replies,
Jeff

EDIT: I am new to Linux, however have found this: http://www.knoda.org/ Is this what I might need?

View 2 Replies View Related

Subqueries In Access/MSSQL

Jun 7, 2006

Hi,

To make a long story short, I'm trying to correlate the most inner query (two levels deep) with the most outer one, and it just doesn't work.
Is it because the correlation must be back-to-back, without any levels that stand before the two?

If you have no idea what I'm talking about, below is the query that does work, but doesn't produce the right results ("QUOTES" gives the same number for all records):

---------------

select printersGlobal.company, (select count(*) from (select subBidding.bid_company from bidding as subBidding where subBidding.bid_company = 'Printing Company 1' group by subBidding.bid_company, subBidding.project) group by subBidding.bid_company) as QUOTES,

count(date_picked) as wins

from printers as printersGlobal

left outer join bidding as biddingGlobal on printersGlobal.company = biddingGlobal.bid_company
group by printersGlobal.company
order by printersGlobal.company asc

----------------------

When I replace 'Printing Company 1' with printersGlobal.company (to correlate and produce dynamic and correct results), I get errors.

I would appreciate if anybody can point me in the right direction.

Thanks.

View 14 Replies View Related

ACCESS To MSSQL Update Query

Oct 24, 2005

I run the following statement from an update query in access but I can't find the way to run this same query in MSSQL. Please give me some ideas how to modify and run this in MSSQL.

Thank you

"UPDATE DISTINCTROW ZipToTerr, leadsUS SET leadsUS.Terr = [ZipToTerr]![TerrNum] WHERE ((([ZipToTerr].[BU]='W') AND (([ZipToTerr].[ZipFrom])<=[zip]) And (([ZipToTerr].[ZipTo])>=[zip])) And (([leadsUS].[terr]) = 1 ));"

View 8 Replies View Related

Access To MSSql Update Or Insert

Oct 31, 2007

Hi im new to SSIS even i did some basice things in SSIS. Now i run in to the problem, I hawe a access file with arount one milion records and i won to transfare this records in Ms SqlServer . But befora i transfare that i nead to check if that record exsist by ID if exsist i must do update else i must do insert.
Can some one Help me how can i do it..
THX
Sorry for my bad englisht

View 3 Replies View Related

Export Access DB To MSSQL Server 2000

Jun 19, 2004

Hello,

I am working on a couple fo the ASP.NET walkthoughs and I would like to practice with a Database that I created in Access. I realize that I could use the Access DB, but I would like to learn to work with a more industrial strength DB. Is there a method to export to MSSQL server 2000 from Access ?

Regards,

James

View 4 Replies View Related

Access Mssql Databse Which Is Buit In Mcafee

Jul 19, 2007

Hi,
I need to access the mssql database which is inbuilt in Mcafee.
through jdbc i'm trying to conenct mssql but its getting the tables from the default database which in Mcafee not from the mssql.

if any one have any idea please let me know to solve this.

Thanks for your help in advance.

Regs
rbkl.

View 1 Replies View Related

Populating Radio Buttons - Access Vs. MSSQL

Dec 10, 2007

Have recently migrated an Access database to MSSQL.

The following code worked in Access but fails in MSSQL


<input <%If (CStr(Recordset1.Fields.Item("Disclose").Value) = CStr("TRUE")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="Disclose" value="1">

<input <%If (CStr((Recordset1.Fields.Item("Disclose").Value)) = CStr("FALSE")) Then Response.Write("CHECKED") : Response.Write("")%> name="Disclose" type="radio" value="0" >


In Query Analyzer, when I run SELECT Disclose from TABLE, it returns the number 1.

On an ASP page, when I run Response.Write(Recordset1.Fields.Item("Disclose").Value) it returns TRUE.


Have tried changing the = CStr("TRUE") portion to = 1 but it fails to work either way.

Any ideas?

View 1 Replies View Related

Problem After Upgrading Access Database To Mssql

Jan 21, 2004

Oke here is the problem. I hope sombody can help me with it.

After a lot of discussion, I finaly got the people that control the MS SQL server with my appertment as far to allow me to upgrade my access database to the MS SQL server.
After this I've created an access project. Devellopping in access I concluded I made a mistake in the rowsource property of one of the tablefields. The people that control the MS SQL server don'n allow me to make changes to the database structure from within access. So I've to write a SQL statement to change this.

example:

fields table1: id_document, id_document_type, document_name
fields tabel2: id_document_type, document_type_description.

the field id_document_type from table 1 is defined as a combo/list box tha contains the values from the field document_type_description from table 2.

I hope somebody can tell me if it is possible to write an SQL statement for this problem and how it shoud look like ?

thank you

View 1 Replies View Related

Problems Converting Data From MS Access To MSSQL

Oct 15, 2007

Hi All,

I am converting an old MS Access database to MSSQL. While I do get some data into MSSQL, some weird things are happening that are beyond my capabilities.

The database I am trying to convert exists of several tables. The first few are converted perfectly, but one of the last is giving difficulties. What I do is:
SET @SQLQuery = 'INSERT INTO TableName ( ' +
'[field1], ' +
'[field2], ' +
'[field3], ' +
'[field4], ' +
'[Date], ' +
'[field6]) ' +
'SELECT ' +
'[field1], ' +
'[field2], ' +
'[field3], ' +
'[field4], ' +
'[Date], ' +
'[field6]) ' +
' FROM '+ RTRIM(@LinkedServerName) +'...TableName'

EXEC (@SQLQuery)

I do the same for each table, all with their own TableNames and field names. 80% of the tables have a date field, which is of type DBTYPE_DBTIMESTAMP in Access and datetime in MSSQL.

As mentioned: the first tables are converted perfectly, however one fails with error message:
Msg 8114, Level 16, State 8, Server XXXXXX, Line 1
Error converting data type DBTYPE_DBTIMESTAMP to datetime.

If I leave the date field out of the query for that table, it all works (obviously, with an empty date column). The weird thing is that most of the other tables have a date column as well, they are the same data types as in this column and the conversion query is very similar as well.

I have Googled on the full error message and on DBTYPE_DBTIMESTAMP alone, but all results I get seem different to what I have. I have tried using convert to put the date in the correct format (format in the date tables is DD-MM-YYYY), but this doesn't help. Would have been funny if it had helped, as all other tables use the same date format, and according to the specs, DBTYPE_DBTIMESTAMP should be automatically converted to datetime anyway (which in all other tables works)...

Basically, what I am saying is: I have 8 extremely similar tables, which I all try to convert using the exact same method, 7 tables succeed and one fails...

Thanks in advance for your help!
Freddy

View 3 Replies View Related

Restrict User Access In MSSQL 2000

Apr 5, 2006

Hi,

I have few SQL user who has permissions on different databases. When they are accessing MSSQL server from Enterprise Manager they can see all Databases but can not access them and they can change file size allocated to them. I want to restrict this and they can only see database belongs to respective user and restrict user to change allocated size. Please help me with this as I am not able to find the solution.

Thanks & Regards,

Gaurav

View 1 Replies View Related

Access 2002 To MSSQL Upgrade Challenge With ASP Web Application

Mar 6, 2008



To whom it may concern,



I have developed a web application using ASP 3.0 with a Access DB 2002 backend. I have decided to recently to upgrade the database to SQL 2005 but I am having issues that I cannot work out because I am new to MsSql Language. It appears that the FORMAT function in Access does not work in MsSql ( does not recognise the function ) along with some of the PIVOT and TRANSFORM calls (coming up with "Syntax error near "..."") . Please see the below code. The areas that are showing up as errors when the application is run are highlighted in red.



Thanking you in advance

Darren





<!-- #include file="./../../common/includes/i_header_login.asp" -->

<link href="../../common/css/proceduremanual.css" rel="stylesheet" type="text/css">


<%Sub RecordsetToCSV(ByRef RS, ByVal CSVFilePath, ByVal IncludeFieldNames)
Set objCSVFile = CreateObject("ADODB.Stream")
Call objCSVFile.Open

If IncludeFieldNames Then
'string concatenation issues aren't a problem for a small string of field names
For Each Field In RS.Fields
If FieldNames = "" Then
FieldNames = Field.Name
Else
FieldNames = FieldNames & "," & Field.Name
End If

Next
FieldNames = FieldNames & vbCRLF
Call objCSVFile.WriteText(FieldNames, 1)
End If

Call objCSVFile.WriteText(FieldNames & RS.GetString(adClipString, , ",", vbCRLF, ""))
Call objCSVFile.SaveToFile(CSVFilePath, 2)
Set objCSVFile = Nothing
End Sub
%>

<%
function evaluate(pFormula, pUserID, pMonth, pYear)
if isnull(pFormula) then
evaluate = 0
exit function
end if
x = 0
key = ""
zero = "0"
nine = "9"
eFormula = ""
lf = len(pFormula)
for i = 1 to lf
c = mid(pFormula,i,1)
if x = 1 and c >= zero and c <= nine then key = key & c
if x = 1 and (c < zero or c > nine or i = lf) then
set rsd = cn.execute("select sum(total) from Data where UserID = " & pUserID & " and month(weekEnding) = " & pMonth & " and year(weekEnding) = " & pYear & " and GroupTaskID=" & key)
eFormula = eFormula & rsd.fields(0)
rsd.close
key = ""
if c < zero or c > nine then x = 0
end if
if c = "v" then
key = ""
x = 1
end if
if x = 0 then eFormula = eFormula & c
next
on error resume next
evaluate = round(eval(eFormula),0)
'if err then evaluate = err.description & ": " & eformula
if err then evaluate = 0
end function
%>

<%
period = split(request.form("period"),",")
if ubound(period) = 1 then
mmmm = period(0)+0
yyyy = period(1)+0
else
mmmm = 0
yyyy = 0
end if
%>


<div align="left" class="MainBodyText" style="width: 100%; height: 100%">


<div align="left" valign="top" width="200" style="margin:5; padding:3; font-family:arial; font-size:10pt; font-weight:bold; font-color000000;" >

<% if session("groupid") = 1 then%>
<a href="../main/administration.asp"> <<< Administration Home</a><br><br>
<a href="manager_report.asp"> <<< Managers Report Home</a>
<%end if %>
<%if session("groupid") = 2 then%>
<a href="../main/manager.asp"> <<< Manager Home</a><br><br>
<a href="manager_report.asp"> <<< Managers Report Home</a>
<%end if %>
<%if session("groupid") = 3 then%>
<a href="../main/user.asp"> <<< User Home</a>


<%end if %>

<%if session("groupid") = 4 then%>
<a href="../main/general.asp"> <<< Home</a><br><br>
<a href="manager_report.asp"> <<< Managers Report Home</a>


<%end if %>



</div>
<p align="center" class="MainBodyText">
<img src="../../images/si.gif" align="center"><br><b>Month to Date Dashboard</b></p>
<p align="center" class="MainBodyText">
To use the Print Page function please select the period required then select
view report. You can then select Print Page which will print out the rendered
page.</p>
<form name="f1" method="POST" action="dialmtd.asp">
<div id="printReady">
<table align="center" border="0" width="663" style="border-collapse: collapse" bordercolor="#111111" cellpadding="2">
<tr>

<td align="left" width="127"><font face="Arial" size="2">
<select name="benchmarkgroup" style="color: #000000; border: 0px solid #000000; background-color: #FFE737">
<option value="0" <%if request.form("benchmarkgroup") = "" then response.write "selected"%>>Select Report
<% set rs = cn.execute("select TaskGroupID,TaskGroup from BenchmarkGroups where Active = 1 order by TaskGroupID")
do until rs.eof
if request.form("taskgroupid")+0 = rs.fields(0) then selected = "selected" else selected = "" %>
<option value=<%=rs.fields(0) & " " & selected%>><%=rs.fields(1)%>
<% rs.movenext
loop
rs.close %>
</select>
</td>

<td align="left" width="143"><font face="Arial" size="2">
<select name="period">
<option value="" <%if request.form("period") = "" then response.write "selected"%>>Select Period
<% set rs = cn.execute("select year(WeekEnding), month(WeekEnding), format(Weekending,""mmmm yyyy"") from data group by Year(WeekEnding), Month(WeekEnding), format(WeekEnding,""mmmm yyyy"") order by 1,2")
do until rs.eof
x = rs.fields(1) & "," & rs.fields(0)
if request.form("period") = x then selected = "selected" else selected = "" %>
<option value=<%=x & " " & selected%>><%=rs.fields(2)%>
<% rs.movenext
loop
rs.close %>
</select>
</td>

<td align="left" width="117"><font face="Arial" size="2">
<select name="stateid">
<option value="0" <%if request.form("stateid") = "" then response.write "selected"%>>All States
<% set rs = cn.execute("select stateid,state from tblState where benchmarkactive order by state")
do until rs.eof
if request.form("stateid")+0 = rs.fields(0) then selected = "selected" else selected = "" %>
<option value=<%=rs.fields(0) & " " & selected%>><%=rs.fields(1)%>
<% rs.movenext
loop
rs.close %>
</select>
</td>

<td align="left" width="127"><font face="Arial" size="2">
<select name="countryid">
<option value="0" <%if request.form("countryid") = "" then response.write "selected"%>>All Countries
<% set rs = cn.execute("select countryid,country from tblCountry where benchmarkactive order by country")
do until rs.eof
if request.form("countryid")+0 = rs.fields(0) then selected = "selected" else selected = "" %>
<option value=<%=rs.fields(0) & " " & selected%>><%=rs.fields(1)%>
<% rs.movenext
loop
rs.close %>
</select>
</td>
<td align="left" width="129"><input type="image" src="../../Images/view-report.gif" onclick="f1.submit()" name="f1" alt="Click Here to Generate Report"></td>
<td width="127"><input type="image" src="../../Images/print-report.gif" rname="printMe" onClick="printSpecial()" alt="Click Here to Print the Page"></td>


</tr>
</table>

<% if mmmm > 0 then %>
<table border="0" width="450" align="left" style="margin-left:10px; border-collapse: collapse" bordercolor="#111111" cellpadding="2" cellspacing="4">
<thead>
<tr>
<% sql = " where 1 = 1 AND BenchmarkGroup =" & request("benchmarkgroup")
if request.form("stateid")+0 then sql = sql & " and stateid = " & request.form("stateid")
if request.form("countryid")+0 then sql = sql & " and countryid = " & request.form("countryid")
set rs = cn.Execute("TRANSFORM First(UserID) SELECT description, minimum, mintype, formula FROM BenchmarksActiveUsers" & sql & " GROUP BY displayOrder, description, minimum, mintype, formula ORDER BY displayOrder PIVOT Name")
lastfield = rs.fields.count-1
s = 4 %>
<td align="left" bgcolor="#C0C0C0"><font face="Arial" color="#000000" size="2"><b>Benchmark</b></font>&nbsp;</td>
<td align="center" bgcolor="#C0C0C0"><font face="Arial" color="#000000" size="2"><b>Company Average</b></font>&nbsp;</td>
<% for i = s to lastfield %>

<% next %>
</tr>
</thead>
<tbody>
<br>
<% dim v()
do until rs.eof
min = int(rs.fields("minimum"))
mintype = rs.fields("mintype")
sumv = 0
avg = 0
n = 0
for j = s to lastfield
redim preserve v(j)
v(j) = evaluate(rs.fields("formula"),rs.fields(j),mmmm,yyyy)
sumv = sumv + v(j)
n = n + 1
next
if n > 0 then avg = round(sumv / n) %>
<tr><td></td><td align="center" valign="bottom"><font face="Arial" size="2">Target: <%=min%> <%=mintype%></font></td><td></td></tr>
<tr>
<td bgcolor="#FFFFFF">
<p align="right"><font face="Arial" size="2"><%=rs.fields("description")%></font></td>


<td bgcolor="#FFFFFF">
<p align="center"><font face="Arial" size="1"><% If (avg/min)*100 <=12 Then %><IMG SRC="../images/dial-1.gif">
<% ELSEIF (avg/min)*100 <=24 Then%><IMG SRC="../images/dial-2.gif">
<% ELSEIF (avg/min)*100 <=36 Then%><IMG SRC="../images/dial-3.gif">
<% ELSEIF (avg/min)*100 <=48 Then%><IMG SRC="../images/dial-4.gif">
<% ELSEIF (avg/min)*100 <=60 Then%><IMG SRC="../images/dial-5.gif">
<% ELSEIF (avg/min)*100 <=72 Then%><IMG SRC="../images/dial-6.gif">
<% ELSEIF (avg/min)*100 <=86 Then%><IMG SRC="../images/dial-7.gif">
<% ELSEIF (avg/min)*100 <=99 Then%><IMG SRC="../images/dial-8.gif">
<% ELSEIF (avg/min)*100 =100 Then%><IMG SRC="../images/dial-9.gif">
<% ELSEIF (avg/min)*100 <=112 Then%><IMG SRC="../images/dial-10.gif">
<% ELSEIF (avg/min)*100 <=124 Then%><IMG SRC="../images/dial-11.gif">
<% ELSEIF (avg/min)*100 <=136 Then%><IMG SRC="../images/dial-12.gif">
<% ELSEIF (avg/min)*100 <=148 Then%><IMG SRC="../images/dial-13.gif">
<% ELSEIF (avg/min)*100 <=160 Then%><IMG SRC="../images/dial-14.gif">
<% ELSEIF (avg/min)*100 <=172 Then%><IMG SRC="../images/dial-15.gif">
<% ELSEIF (avg/min)*100 <=186 Then%><IMG SRC="../images/dial-16.gif">
<% ELSEIF (avg/min)*100 =>187 Then%><IMG SRC="../images/dial-17.gif">
<%END IF%></font></td><td><img src="../images/under-target.gif"><br><br><img src="../images/over-target.gif"></td>
</tr><tr><td></td><td valign="top" align="center"><font face="Arial" size="2">Actual: <%=avg%> <%=mintype%></font><hr></td><td></td></tr><% for j = s to lastfield
color = "#000000"
if int(v(j)) < min then color="#FF0000" %>

<% next %>
</tr>
<% rs.movenext
loop
rs.close
cn.close
set cn = nothing %>
</tbody>
</table>
<% end if %>
</div>
</form>
</div>
<!-- #include file="./../../common/includes/i_footer.asp" -->

View 2 Replies View Related

Access Rights To Two Mssql Dbs Via Password Protected Role

Jan 30, 2007

I dont know how to arrange situation when application enduser needs to access data in two databases of mssql server concurently in those circumstances that access rights to the data should be restricted by password protected role (whose password is not known to the end user).

Detailed description of problem:

So far there was an application, that manipulated its data, saved in mssql server's database. End user authenticates to application by his (mssql server's) login name and password. The application authenticates the user by connecting to the database with the given name/password credentials, and then the application sets application role with hardcoded name/password. Thus application role sets the access rights for consequent end user's requests, delivered via application to the database server.

The goal is that end user cannot manipulate application database data when connects to the database by other means (e. g. via SQL server Manager), because he does not know the application role's password.

Now suppose that there are two applications (A1, A2), both using the same model for access restrictions. Each of them has its own database (A1DB, A2DB) and its own application role (A1R residing in A1DB, A2R residing in A2DB). End user (login) X can manipulate A1DB data when connects via A1, and A2DB data when connects via A2, and NO data when connects by other means.

Finally suppose that some subset of A2 data (let's say one table) is useful to see also via A1 application. There is no problem to add to A1DB view, that shows data from A2DB table together with A1DB tables. But when the user is connected via A1, he cannot see the data, because query on A1 view fails (user has not access rights on A2 data).

The access rights for A1 enduser cannot be set by no means i know because:

1) I cannot set the rights via public (guest) access because in that case they will be accessible to any users connected by any third party products, which is supposed to be security hole.

2) I cannot set the rights via dbuser or dbrole privileges, because they will not work when connected via A1 application (setting the app role suppresses the db privileges)

3) I cannot set the rights via application role because two application roles cannot be set concurrently.

4) I cannot abandon using application roles mechanism and use database roles mechanism, because db roles cannot be protected by independent password (not known to the enduser).

Please can anybody review my problem and either find the mistake in my approach, or propose other solution? So far I suppose the problem is my ignorance, because I am not great mssql expert.

View 3 Replies View Related

Access To MSSQl 2005 Upgrade Challenge With ASP Web Application

Feb 26, 2008

To whom it may concern,

I have developed a web application using ASP 3.0 with a Access DB 2002 backend. I have decided to recently to upgrade the database to SQL 2005 but I am having issues that I cannot work out because I am new to MsSql Language. It appears that the FORMAT function in Access does not work in MsSql ( does not recognise the function ) along with some of the PIVOT and TRANSFORM calls (coming up with "Syntax error near "..."") . Please see the below code. The areas that are showing up as errors when the application is run are highlighted in red.

Thanking you in advance
Darren


<!-- #include file="./../../common/includes/i_header_login.asp" -->
<link href="../../common/css/proceduremanual.css" rel="stylesheet" type="text/css">

<%Sub RecordsetToCSV(ByRef RS, ByVal CSVFilePath, ByVal IncludeFieldNames)
Set objCSVFile = CreateObject("ADODB.Stream")
Call objCSVFile.Open

If IncludeFieldNames Then
'string concatenation issues aren't a problem for a small string of field names
For Each Field In RS.Fields
If FieldNames = "" Then
FieldNames = Field.Name
Else
FieldNames = FieldNames & "," & Field.Name
End If

Next
FieldNames = FieldNames & vbCRLF
Call objCSVFile.WriteText(FieldNames, 1)
End If

Call objCSVFile.WriteText(FieldNames & RS.GetString(adClipString, , ",", vbCRLF, ""))
Call objCSVFile.SaveToFile(CSVFilePath, 2)
Set objCSVFile = Nothing
End Sub
%>
<%
function evaluate(pFormula, pUserID, pMonth, pYear)
if isnull(pFormula) then
evaluate = 0
exit function
end if
x = 0
key = ""
zero = "0"
nine = "9"
eFormula = ""
lf = len(pFormula)
for i = 1 to lf
c = mid(pFormula,i,1)
if x = 1 and c >= zero and c <= nine then key = key & c
if x = 1 and (c < zero or c > nine or i = lf) then
set rsd = cn.execute("select sum(total) from Data where UserID = " & pUserID & " and month(weekEnding) = " & pMonth & " and year(weekEnding) = " & pYear & " and GroupTaskID=" & key)
eFormula = eFormula & rsd.fields(0)
rsd.close
key = ""
if c < zero or c > nine then x = 0
end if
if c = "v" then
key = ""
x = 1
end if
if x = 0 then eFormula = eFormula & c
next
on error resume next
evaluate = round(eval(eFormula),0)
'if err then evaluate = err.description & ": " & eformula
if err then evaluate = 0
end function
%>
<%
period = split(request.form("period"),",")
if ubound(period) = 1 then
mmmm = period(0)+0
yyyy = period(1)+0
else
mmmm = 0
yyyy = 0
end if
%>

<div align="left" class="MainBodyText" style="width: 100%; height: 100%">

<div align="left" valign="top" width="200" style="margin:5; padding:3; font-family:arial; font-size:10pt; font-weight:bold; font-color000000;" >
<% if session("groupid") = 1 then%>
<a href="../main/administration.asp"> <<< Administration Home</a><br><br>
<a href="manager_report.asp"> <<< Managers Report Home</a>
<%end if %>
<%if session("groupid") = 2 then%>
<a href="../main/manager.asp"> <<< Manager Home</a><br><br>
<a href="manager_report.asp"> <<< Managers Report Home</a>
<%end if %>
<%if session("groupid") = 3 then%>
<a href="../main/user.asp"> <<< User Home</a>

<%end if %>

<%if session("groupid") = 4 then%>
<a href="../main/general.asp"> <<< Home</a><br><br>
<a href="manager_report.asp"> <<< Managers Report Home</a>

<%end if %>

</div>
<p align="center" class="MainBodyText">
<img src="../../images/si.gif" align="center"><br><b>Month to Date Dashboard</b></p>
<p align="center" class="MainBodyText">
To use the Print Page function please select the period required then select
view report. You can then select Print Page which will print out the rendered
page.</p>
<form name="f1" method="POST" action="dialmtd.asp">
<div id="printReady">
<table align="center" border="0" width="663" style="border-collapse: collapse" bordercolor="#111111" cellpadding="2">
<tr>

<td align="left" width="127"><font face="Arial" size="2">
<select name="benchmarkgroup" style="color: #000000; border: 0px solid #000000; background-color: #FFE737">
<option value="0" <%if request.form("benchmarkgroup") = "" then response.write "selected"%>>Select Report
<% set rs = cn.execute("select TaskGroupID,TaskGroup from BenchmarkGroups where Active = 1 order by TaskGroupID")
do until rs.eof
if request.form("taskgroupid")+0 = rs.fields(0) then selected = "selected" else selected = "" %>
<option value=<%=rs.fields(0) & " " & selected%>><%=rs.fields(1)%>
<% rs.movenext
loop
rs.close %>
</select>
</td>

<td align="left" width="143"><font face="Arial" size="2">
<select name="period">
<option value="" <%if request.form("period") = "" then response.write "selected"%>>Select Period
<% set rs = cn.execute("select year(WeekEnding), month(WeekEnding), format(Weekending,""mmmm yyyy"") from data group by Year(WeekEnding), Month(WeekEnding), format(WeekEnding,""mmmm yyyy"") order by 1,2")
do until rs.eof
x = rs.fields(1) & "," & rs.fields(0)
if request.form("period") = x then selected = "selected" else selected = "" %>
<option value=<%=x & " " & selected%>><%=rs.fields(2)%>
<% rs.movenext
loop
rs.close %>
</select>
</td>

<td align="left" width="117"><font face="Arial" size="2">
<select name="stateid">
<option value="0" <%if request.form("stateid") = "" then response.write "selected"%>>All States
<% set rs = cn.execute("select stateid,state from tblState where benchmarkactive order by state")
do until rs.eof
if request.form("stateid")+0 = rs.fields(0) then selected = "selected" else selected = "" %>
<option value=<%=rs.fields(0) & " " & selected%>><%=rs.fields(1)%>
<% rs.movenext
loop
rs.close %>
</select>
</td>
<td align="left" width="127"><font face="Arial" size="2">
<select name="countryid">
<option value="0" <%if request.form("countryid") = "" then response.write "selected"%>>All Countries
<% set rs = cn.execute("select countryid,country from tblCountry where benchmarkactive order by country")
do until rs.eof
if request.form("countryid")+0 = rs.fields(0) then selected = "selected" else selected = "" %>
<option value=<%=rs.fields(0) & " " & selected%>><%=rs.fields(1)%>
<% rs.movenext
loop
rs.close %>
</select>
</td>
<td align="left" width="129"><input type="image" src="../../Images/view-report.gif" onclick="f1.submit()" name="f1" alt="Click Here to Generate Report"></td>
<td width="127"><input type="image" src="../../Images/print-report.gif" rname="printMe" onClick="printSpecial()" alt="Click Here to Print the Page"></td>

</tr>
</table>
<% if mmmm > 0 then %>
<table border="0" width="450" align="left" style="margin-left:10px; border-collapse: collapse" bordercolor="#111111" cellpadding="2" cellspacing="4">
<thead>
<tr>
<% sql = " where 1 = 1 AND BenchmarkGroup =" & request("benchmarkgroup")
if request.form("stateid")+0 then sql = sql & " and stateid = " & request.form("stateid")
if request.form("countryid")+0 then sql = sql & " and countryid = " & request.form("countryid")
set rs = cn.Execute("TRANSFORM First(UserID) SELECT description, minimum, mintype, formula FROM BenchmarksActiveUsers" & sql & " GROUP BY displayOrder, description, minimum, mintype, formula ORDER BY displayOrder PIVOT Name")
lastfield = rs.fields.count-1
s = 4 %>
<td align="left" bgcolor="#C0C0C0"><font face="Arial" color="#000000" size="2"><b>Benchmark</b></font>&nbsp;</td>
<td align="center" bgcolor="#C0C0C0"><font face="Arial" color="#000000" size="2"><b>Company Average</b></font>&nbsp;</td>
<% for i = s to lastfield %>
<% next %>
</tr>
</thead>
<tbody>
<br>
<% dim v()
do until rs.eof
min = int(rs.fields("minimum"))
mintype = rs.fields("mintype")
sumv = 0
avg = 0
n = 0
for j = s to lastfield
redim preserve v(j)
v(j) = evaluate(rs.fields("formula"),rs.fields(j),mmmm,yyyy)
sumv = sumv + v(j)
n = n + 1
next
if n > 0 then avg = round(sumv / n) %>
<tr><td></td><td align="center" valign="bottom"><font face="Arial" size="2">Target: <%=min%> <%=mintype%></font></td><td></td></tr>
<tr>
<td bgcolor="#FFFFFF">
<p align="right"><font face="Arial" size="2"><%=rs.fields("description")%></font></td>

<td bgcolor="#FFFFFF">
<p align="center"><font face="Arial" size="1"><% If (avg/min)*100 <=12 Then %><IMG SRC="../images/dial-1.gif">
<% ELSEIF (avg/min)*100 <=24 Then%><IMG SRC="../images/dial-2.gif">
<% ELSEIF (avg/min)*100 <=36 Then%><IMG SRC="../images/dial-3.gif">
<% ELSEIF (avg/min)*100 <=48 Then%><IMG SRC="../images/dial-4.gif">
<% ELSEIF (avg/min)*100 <=60 Then%><IMG SRC="../images/dial-5.gif">
<% ELSEIF (avg/min)*100 <=72 Then%><IMG SRC="../images/dial-6.gif">
<% ELSEIF (avg/min)*100 <=86 Then%><IMG SRC="../images/dial-7.gif">
<% ELSEIF (avg/min)*100 <=99 Then%><IMG SRC="../images/dial-8.gif">
<% ELSEIF (avg/min)*100 =100 Then%><IMG SRC="../images/dial-9.gif">
<% ELSEIF (avg/min)*100 <=112 Then%><IMG SRC="../images/dial-10.gif">
<% ELSEIF (avg/min)*100 <=124 Then%><IMG SRC="../images/dial-11.gif">
<% ELSEIF (avg/min)*100 <=136 Then%><IMG SRC="../images/dial-12.gif">
<% ELSEIF (avg/min)*100 <=148 Then%><IMG SRC="../images/dial-13.gif">
<% ELSEIF (avg/min)*100 <=160 Then%><IMG SRC="../images/dial-14.gif">
<% ELSEIF (avg/min)*100 <=172 Then%><IMG SRC="../images/dial-15.gif">
<% ELSEIF (avg/min)*100 <=186 Then%><IMG SRC="../images/dial-16.gif">
<% ELSEIF (avg/min)*100 =>187 Then%><IMG SRC="../images/dial-17.gif">
<%END IF%></font></td><td><img src="../images/under-target.gif"><br><br><img src="../images/over-target.gif"></td>
</tr><tr><td></td><td valign="top" align="center"><font face="Arial" size="2">Actual: <%=avg%> <%=mintype%></font><hr></td><td></td></tr><% for j = s to lastfield
color = "#000000"
if int(v(j)) < min then color="#FF0000" %>

<% next %>
</tr>
<% rs.movenext
loop
rs.close
cn.close
set cn = nothing %>
</tbody>
</table>
<% end if %>
</div>
</form>
</div>
<!-- #include file="./../../common/includes/i_footer.asp" -->

View 5 Replies View Related

Advice On Importing Access Data Into MSSQL Table Using Code

Aug 2, 2004

Hi,

I'm about to embark on writing some code in perl or VBscript that automatically synchronises a constantly updated Access database with an MSSQL database.

I know MSSQL has an import tool built into Enterprise manager but I'm wondering if theres a stored procedure that does this?

The way I'm thinking of doing it is to read the all the access tables into separate hash arrays and then INSERTing them into the MSSQL database after checking for any duplicates. This all sounds a bit time consuming (there are a large number of tables) and processor intensive.

If anyones done anything like this before, I'd love to hear their views......!

Thanks!

View 9 Replies View Related

Front/backend Access Application-----conversion To MSSQL--advice

Dec 16, 2005

I have an application that uses Acces as a backend and VBA as front end. Application is secured and is supplied on a CD with setup.exe.

Can I use VB 2005 and MS SQL to achieve the same?

Would I be able to package my application with  all the neccessery files (assuming that client does not have any e.g. SQL server) so that multiple front ends can access data from common source?

Would I be able to secure such an application using only VS 2005?

What would I need to quickly learn  how to achieve the above ( any books you can suggest maybe)?

View 1 Replies View Related

Unable To Insert Records In A Mssql Database With A Access Front End

Nov 18, 2006

I have a database that is in mssql and I'm using an odbc link to an access database where I want to add records to the mssql table. When I open the linked table in access it does not allow me to add a record. I have created a user account in mssql that has ownership to the database and I use this user in setting up the odbc link.

View 3 Replies View Related

MSSQL Mirroring : Unable To Access Mirror After Succes Failover

Oct 16, 2007

Hello everybody,

I'am currently testing a MSSQL 2005 mirroring implementation with automatically failover.

1 principal server ====> Server A
1 mirror server ====> Server B
1 witness server ====> Server C

When server A fails Server B becomes Principal. ( which is the general idea ) This is working perfectly.

The problem is when i try to connect to the mirror Server B ( actually the new principal )

I use the .NET connection string with the failover parameter ( even without the parameter ) the connection fails.
( the general error : unable to connect, or connection property has not been initialized )

Data Source=ServerA; Failover Partner=ServerB;Initial Catalog=DBMTest;uid=dbuser;pwd=dbpassword;

This connection property works fine when server A (original principal) is online.

When i run the SQL profiler with the mirror db as profile, i dont see the traffic.

So you think the mirror server has a network problem or was not connfigured for remote access.
i thought that also, but when i create a 'stand alone - non mirrored' database the same application with the same connetion string ( well.....different db) works fine.

The problem must be in the whole mirroring implementation....... ( my idea )

Does anyone had the same problem, or better, doed anyone know how to solve it?

Hope to hear from you soon!

Thank you in advance!

Regards,
W.schmitt
The netherlands

View 6 Replies View Related

MSSQL Remote Access: Can't Find Solution After 6 Hours Of Searching...

Jan 4, 2007

Hello,
I'm trying to access the MSSQl 'remotey'. I open telnet and go to 127.0.0.1 1433 but can't. It seems I have done everything I can to fix it, but nothing solves the problem.

My firewall is disabled.

I have TCP/IP enabled in "SQL Server Configuration Manager" for both Client Protocol and protocols for MSSSQLSERVER (Shared Memory and NAmed Pipes are also both enabled). VIA is disabled.

In SQL Services, SQL Server Integration Services, Full Text Searc, SQL Server, SQL Server Analysis Services, and Server Browser are running. SQL Server Agent is disabled.

In Server Surface Area configuration, under Database Engine is running (automatic) and "Remote Connections" is enabled to TCP/IP and named pipes. Analysis Service has Local and Remote connections checked and is running (automatic). SQL Server agent is stopped.

Integration Services is running (Automatic) SQL Server Browser is running. (Automatic)

When I look in the error log, it shows...

Date 1/4/2007 3:21:45 AM
Log SQL Server (Current - 1/4/2007 3:21:00 AM)

Source Server

Message
Server is listening on [ 127.0.0.1 <ipv4> 1434].

Date 1/4/2007 3:21:45 AM
Log SQL Server (Current - 1/4/2007 3:21:00 AM)

Source Server

Message
Server is listening on [ 'any' <ipv4> 1433].

I have tried to telnet to port 1434 and 1433 but nothing happens. Does anyone know what else I need to be able to access that port??

Thanks
Foley

View 3 Replies View Related

Access Denied To Files

Sep 12, 2007



Hi,

Im trying to "use" files during my flow in two diferent kind of components (send email task and custom transformation), but the error i get is similar: access denied, file doesnt exists, is locked by another proccess or not enough rigths.

-In custom transofrmation i am trying to write to a file with the next code:




Dim sw As StreamWriter

If (Not File.Exists(". est.txt")) Then

sw = File.CreateText(". est.txt")

Else

sw = File.AppendText(". est.txt")

End If

sw.Write("Numero de Registros en Empresas")

sw.WriteLine(Row.CuentaRegEmpresas)

sw.Write("Numero de Registros en Reporta")

sw.WriteLine(Row.CuentaRegReporta)

sw.Write("Numero de Registros de Facturas ampliadas")

sw.WriteLine(Row.CuentaRegFAmp)

sw.WriteLine()


- In send email tranformation i am trying to attach a different file (the log of the process) , but the error is that i dont have rights to access. If i try to send another file this error disappears...

Both files (test.txt and log.txt) have total control rights to all users, and arent locked or opened by any other process during the execution.

Edit: Thats the error trace i get:


en System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)

en System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)

en System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options)

en System.IO.StreamWriter.CreateFile(String path, Boolean append)

en System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize)

en System.IO.StreamWriter..ctor(String path, Boolean append)

en System.IO.File.AppendText(String path)

en ScriptComponent_14cd101f436a42b18dc68087869033b2.ScriptMain.Entrada0_ProcessInputRow(Entrada0Buffer Row)

en ScriptComponent_14cd101f436a42b18dc68087869033b2.UserComponent.Entrada0_ProcessInput(Entrada0Buffer Buffer)

en ScriptComponent_14cd101f436a42b18dc68087869033b2.UserComponent.ProcessInput(Int32 InputID, PipelineBuffer Buffer)

en Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)

View 5 Replies View Related

How To Access Package Configuration Files

Mar 11, 2008

HI

how to access package configuration files of a particular package during runtime.

View 9 Replies View Related

MSSQL 2005 Inserting Data On Tables Created By Regular Users With Ddl_admin Role, Using Access Or Other Front End Apps

Feb 14, 2008

Writing to tables created by regular users on MSSQL2005

I have users creating tables through an application, I gave them ddl_admin, datareader, datawriter. They can create tables but cannot insert/update data (to their own tables), I cannot insert data either using Access or any other application to those tables created by them (under dbo schema) Is there something I am missing with permissions? Thank you very much

View 3 Replies View Related

SSIS Package Access Files Via Openrowset Strange Problem?

Sep 7, 2007

I have three machine:

S: Running SQL Server Express
V: Running SSIS package in VS.Net
F: Shared folder host excel files

And an openrowset SQL statement: select * from openrowset(..... \Fexcel.xls....). This statement can be run in SS management studio connecting to S using my Windows logon(integration security) without any problem.

However, the same SQL running inside SSIS package (integration security using my Windows account) get the following error:


Error: 0x0 at Check headers: OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine cannot open the file '\Fexcel.xls'. It is already opened exclusively by another user, or you need permission to view its data.".

Error: 0xC002F210 at Check headers, Execute SQL Task: Executing the query "....openrowset....." failed with the following error: "Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.



(My Windows account is administrator of Windows and sysadmin or SQL Sever Express on S)

View 1 Replies View Related

Recovery :: Access To Files On Shared Drive When Cluster Is Shut Down?

Aug 31, 2015

Is there a way to get access to the files on the shared drive from either of the nodes when the SQL Cluster is shut down?

It doesn't look like there is, but hoping there is some trick. I see how it can be done with a Windows Cluster but not a SQL Cluster.

View 4 Replies View Related

How Many Result-rows Does Mssql Return Should Be Used Asynchronous Method To Use Mssql Cursor?

Aug 11, 2004

How many result-rows does mssql return should be used asynchronous method to use mssql cursor, can get the best performance in any time in any result offset?

i want to make the cursor fast in any time whatever how many results returned

View 2 Replies View Related

SQL Server Admin 2014 :: Cannot Access Path When Attaching Database With Data Files On SMB Share

Oct 1, 2014

I have a Windows Server 2012 R2 2 node cluster with SQL Server 2014 FCI installed. Data files are on a separate Windows Server 2012 R2 file server. Data files share has been permissioned to the SQL Server service and SQL Server Agent service accounts as Full Control. NTFS Permissions are Full Control.

When I try to attach a database
CREATE DATABASE AdventureWorksDW2012
ON (FILENAME = 'apricotmssql_VIOLETMSSQL12.MSSQLSERVERMSSQLDATAAdventureWorksDW2012_Data.mdf')
FOR ATTACHI get this error:
Msg 5120, Level 16, State 101, Line 4
Unable to open the physical file "apricotmssql_VIOLETMSSQL12.MSSQLSERVERMSSQLDATAAdventureWorksDW2012_Data.mdf". Operating system error 5: "5(Access is denied.)".

If I log into the file server (called APRICOT) and look at the NTFS permissions they all look good. I have also reapplied the NTFS permissions from the root folder down.

EDIT
If I log on to one of the nodes in the cluster as the SQL Server service account and navigate to apricotmssql_VIOLETMSSQL12.MSSQLSERVERMSSQLDATA and copy and paste the data file, it works fine.

EDIT2:
If I log on to the file server and Enable Inheritance at the root level, then Replace all child objects with inheritable permission entries from this object, I get this error:

User Account Control settings on all nodes and the file server are set to Never notify

View 0 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved