Database Journal Forums http://forums.databasejournal.com/ Discuss all things related to the world of databases at the active Database Journal community. Forum topics include news, database design, ASP, MS Access, SQL and more. en Sun, 19 May 2013 02:59:36 GMT vBulletin 60 http://forums.databasejournal.com/images/misc/rss.png Database Journal Forums http://forums.databasejournal.com/ <![CDATA["JOIN OPERATION NOT SUPPORTED" Please Correct this query if possible]]> http://forums.databasejournal.com/showthread.php?53689-quot-JOIN-OPERATION-NOT-SUPPORTED-quot-Please-Correct-this-query-if-possible&goto=newpost Wed, 15 May 2013 21:16:35 GMT MY CODE (Im using MS Access 2000 with JET Database engine)

SELECT Members.First_Name + ' ' + Members.Last_Name AS Member,
iif(NULL,Friends.My_E_Mail, Friends.Friend_E_Mail) AS E_Mail,
Members.First_Name AS Name
FROM ((Members
LEFT OUTER JOIN Friends
ON Members.E_Mail = Friends.My_E_Mail
AND Friends.Friend_E_Mail = ?)
LEFT OUTER JOIN Friends Friends_1
ON Members.E_Mail = Friends.Friend_E_Mail
AND Friends.My_E_Mail = ?)

My Tables
Members(all VARCHAR) SOME DATA
First_Name Alester Jude Carl Jones
Last_Name A B C J

FRIENDS(ALL VARCHAR)
My_E_Mail Alester@lam.com Alester@lam.com Alester@lam.com
Friend_E_Mail jude@lam.com carl@lam.com jones@lam.com

***NOTE Friends Table ALLOWS duplicates so jude can be on my_E_Mail but CAN NOT add alester because they are already friends.


Desired Output if ("?" in above query is: jones@lam.com)

+--------------+-----------+------------+
|Member |E_Mail | Name |
+---------------------------------------+
Alester A Alester@lam.com Alester




Desired Output if("?" in above query is: Alester@lam.com)

+--------------+-----------+------------+
|Member |E_Mail | Name |
+---------------------------------------+
Jude B jude@lam.com Jude
carl C carl@lam.com Carl
Jones J jones@lam.com Jones

PS the "?" are query string parameters that im passing in the "?" i know that works fine.


MY QUESTION IS : I keep getting this error "Join Expression not Supported"

Is there a workaround query I can use without using a stored procedure or using multiple queries since this needs to be ONE SINGLE QUERY!?

THANK YOU! ]]> Microsoft Access jesusw00t http://forums.databasejournal.com/showthread.php?53689-quot-JOIN-OPERATION-NOT-SUPPORTED-quot-Please-Correct-this-query-if-possible Permission by department manager from single report to view. http://forums.databasejournal.com/showthread.php?53687-Permission-by-department-manager-from-single-report-to-view.&goto=newpost Sun, 12 May 2013 03:51:29 GMT Hello,

We are using SSRS 2005 and I have a report I want to give access to 5 different managers the report will be in a separate folders by department this report is a budget report. I don't want to change the sql query for each department individually and copy into the respective folders I would have ssrs 2005 recognize the department by folder and only give the data to the respective department manager. Is this possible? ]]> Reporting Services donalejandro http://forums.databasejournal.com/showthread.php?53687-Permission-by-department-manager-from-single-report-to-view. Illegal reference to correlation name http://forums.databasejournal.com/showthread.php?53685-Illegal-reference-to-correlation-name&goto=newpost Fri, 10 May 2013 16:20:24 GMT I don't know Sybase very well, I'm much more familiar with MySQL. I get the error "Illegal reference to correlation name 'edef'", but I don't know why or how to fix it. Here is my SQL statement. Please help!

Code:
/*
gets employee clock in/clock out times and total hours
*/

select
    edef.emp_seq,
    edef.obj_num,
    edef.payroll_id,
    edef.last_name,
    edef.first_name,

    dtl.clk_in_date_tm,
    dtl.clk_out_date_tm,
    dtl.reg_hrs,

    ot.order_type_seq,
    otdef.name
from
    MICROS.emp_def edef,

    MICROS.time_card_dtl as dtl

left join
(
    select
        MICROS.shift_emp_ot_ttl.emp_seq,
        MICROS.shift_emp_ot_ttl.order_type_seq
    from
        MICROS.shift_emp_ot_ttl
)ot on ot.emp_seq = edef.emp_seq
left join
(
    select
        micros.order_type_def.order_type_seq,
        micros.order_type_def.name
    from
        micros.order_type_def
)otdef on otdef.order_type_seq = ot.order_type_seq
where
    edef.emp_seq = dtl.emp_seq
    and
    dtl.clk_in_date_tm between '2013-05-06 00:07:00' and '2013-05-07 00:04:00'
order by
edef.last_name
]]> Sybase raphael75 http://forums.databasejournal.com/showthread.php?53685-Illegal-reference-to-correlation-name Infopath form data import to Access http://forums.databasejournal.com/showthread.php?53683-Infopath-form-data-import-to-Access&goto=newpost Thu, 09 May 2013 23:22:08 GMT Hi all,

I'm a newbie to this forum so greetings to all :)

I am trying to set up a quote system using an Infopath form which saves the quotes as an xml file, then I want to import the xml into Access in order to keep a track of them, provide reporting functions and also to have a mail merge with a Word doc to then save a pdf quote for the client.

I thought this would be a simple process and it is to a degree but I hit a snag when the xml imports the quote data into separate tables which ruins the mail merge for the word doc. The word doc merge can only be done with one table at a time. This being an important part of the process, I want to make sure the merge can work well.

Another problem is xml not being able to be linked in Access, which leads me to using Excel holding a link to the xml then using Access to hold another linked table to the Excel sheet. Is it me or does this seem silly? (I'm sure a lot of you could go on about this!). This is not an ideal set up but it seems to work ok apart from the element names being imported along with the xml data - "ns1:QuoteNumber" and "ns1:InternalExternal" and so on... Is there a way to avoid importing these element names into Excel?

Please also note that I am not a developer! I am using Office 2010 and am familiar with xml, html css etc... Would anyone have some suggestions? Am I on the right track?:confused: ]]> Microsoft Access Zubanoid http://forums.databasejournal.com/showthread.php?53683-Infopath-form-data-import-to-Access What problem is First Normal Form intended to solve? http://forums.databasejournal.com/showthread.php?53681-What-problem-is-First-Normal-Form-intended-to-solve&goto=newpost Wed, 08 May 2013 01:28:12 GMT In most database design books and articles, a department table (e.g.) that has columns named employee1, employee2, employee3, etc., is said to not be in 1NF.

However, if you read statements of what 1NF is (e.g., Wikipedia article, Chris Date's paper titled "What First Normal Form Really Means," Codd's articles), you will find that 1NF is concerned ONLY with a single column. Some formulations also say that one must not repeat an attribute, but that's impossible in the relational model and in all of the RDBMSes I'm familiar with.

In fact, Chris Date goes so far as to say that no relational database can have a relation that violates 1NF. It is like the sign on an elevator that says: "Out of order. Do not use."

I have come up with an alternative rule that specifically addresses the employee1, employee2, etc., problem. (What it says isn't relevant to this post.)

My question is this: Is my rule, assuming I've put it together well, an alternative to 1NF as the latter is usually expressed? In order to answer this question, I would need to know what problem 1NF was intended to solve. However, in reading the literature extensively, I have never seen this explained, except that in the Date paper I mentioned above he says that the problem it is intended to solve is the system not being relational. That's not what I was looking for.

I would think that if Codd, Date, etc., were concerned about the employee1, employee2, etc., problem, they would have said so. I suspect they were not, because such a table, however awkward, causes no consistency problems at all, and therefore probably is perfectly OK as far as relational database theory is concerned. (Its flaws are that the table definition needs to be altered whenever another employee is needed, and that the SQL becomes horrendous. I doubt that theoreticians would care about either of those very practical concerns.)

Comments? What problem is 1NF as formulated by the database gurus intended to solve? ]]> Database Design Marc Rochkind http://forums.databasejournal.com/showthread.php?53681-What-problem-is-First-Normal-Form-intended-to-solve Easy Database Manager http://forums.databasejournal.com/showthread.php?53679-Easy-Database-Manager&goto=newpost Tue, 07 May 2013 23:05:20 GMT I'm currently using Access 2010.
I want to try something different database but not sure where to look.
I want a db manager that requires very little coding. Something that utilizes wizards.
I don't want anything very expensive. Able to import access data
My access database consist of a backend for the data a front end for the objects.

Thanks,
James ]]> General Database Discussions JamesJoey http://forums.databasejournal.com/showthread.php?53679-Easy-Database-Manager how to make this query? http://forums.databasejournal.com/showthread.php?53677-how-to-make-this-query&goto=newpost Sun, 05 May 2013 20:24:01 GMT Hello all,

I have the following table:

create table Sale(
bankname char(8) null,
controlename char(10) not null,
creditcardnummer numeric(19) null,
username char(10) not null,
accountnummer numeric(7) null,
constraint pk_username primary key(username)
)

I want to make a case/trigger where the following happens:
If the word "Creditcard" has been inserted in controlename then creditnummer must contain numbers else it will be NULL. ]]> Structured Query Language (SQL) Swirl http://forums.databasejournal.com/showthread.php?53677-how-to-make-this-query Network Databases http://forums.databasejournal.com/showthread.php?53675-Network-Databases&goto=newpost Sun, 05 May 2013 00:34:33 GMT I am doing research into network (i.e. IDMS-type) databases. Are there any free/open source network databases for the Windows PC? Thanks I am doing research into network (i.e. IDMS-type) databases. Are there any free/open source network databases for the Windows PC?

Thanks ]]> General Database Discussions Paolo_R http://forums.databasejournal.com/showthread.php?53675-Network-Databases Master database sysusers all have the same sid? http://forums.databasejournal.com/showthread.php?53673-Master-database-sysusers-all-have-the-same-sid&goto=newpost Fri, 03 May 2013 23:08:06 GMT Hi, I noticed that when I do a select * from sysusers in the master database they all show the same sid?

dbo 0x04EB1BD4B662E84EA2F014F4691D18E7
guest 0x04EB1BD4B662E84EA2F014F4691D18E7
BUILTIN\Administrators 0x04EB1BD4B662E84EA2F014F4691D18E7

And so on...

Is there a way to correct this? I'm not sure this is normal or not?

Thanks for your time. ]]> MS SQL Server 7/MS SQL Server 2000 global http://forums.databasejournal.com/showthread.php?53673-Master-database-sysusers-all-have-the-same-sid Run SQL Script File from Access VBA http://forums.databasejournal.com/showthread.php?53671-Run-SQL-Script-File-from-Access-VBA&goto=newpost Fri, 03 May 2013 00:00:47 GMT Hello all, I would like to run a .SQL file from within MS ACCESS VBA. I have VBA code that moves data to a SQL Server database. I also have an advanced SQL Server script that creates GUIDS and places the data into another SQL Server database. Currently, I run the VBA Code in the Access Database and then open a MS SQL Server Management Console to run the .sql script. I would be great to run the .sql script within the Access VBA code. Any advice with sample codes would be appreciated. Thanks, Hello all,

I would like to run a .SQL file from within MS ACCESS VBA. I have VBA code that moves data to a SQL Server database. I also have an advanced SQL Server script that creates GUIDS and places the data into another SQL Server database. Currently, I run the VBA Code in the Access Database and then open a MS SQL Server Management Console to run the .sql script. I would be great to run the .sql script within the Access VBA code.

Any advice with sample codes would be appreciated.

Thanks, ]]> Microsoft Access jcronce http://forums.databasejournal.com/showthread.php?53671-Run-SQL-Script-File-from-Access-VBA Addind days to a date excluding holidays and weekdays in MS Access http://forums.databasejournal.com/showthread.php?53669-Addind-days-to-a-date-excluding-holidays-and-weekdays-in-MS-Access&goto=newpost Mon, 29 Apr 2013 17:55:14 GMT Hello Everyone,

I believe this question has been answered using NetWorkdays and fAddWorkDays using a module called modWorkdays but I could not find the actual code referred to in the article.

I need to add 30 days to a date. If the date falls on a Saturday or Sunday I need the function to return the previous Friday. If it falls on a holiday (from a list in a table) it needs to return the day before the holiday. Could someone direct me to a function that could handle this?

Please be gentle, I'm new here... :)


Eva ]]> Ask an Expert Evangeline http://forums.databasejournal.com/showthread.php?53669-Addind-days-to-a-date-excluding-holidays-and-weekdays-in-MS-Access Microsoft Query + Excel + SQL + CHARINDEX http://forums.databasejournal.com/showthread.php?53667-Microsoft-Query-Excel-SQL-CHARINDEX&goto=newpost Mon, 29 Apr 2013 07:20:07 GMT Is anyone able to tell me why I',m getting this message (See JPG below)

Attachment 647

It's occurring whilst trying to write an "CHARINDEX".

Steps taken:

Microsoft Excel
Data
From Other Sources
From Microsoft Query
Choose the database
Login
Choose the tables & columns
Output it to a table in Excel
Attached Images
]]> Database Lounge BenJ http://forums.databasejournal.com/showthread.php?53667-Microsoft-Query-Excel-SQL-CHARINDEX finding unused tables using monitoring tables http://forums.databasejournal.com/showthread.php?53665-finding-unused-tables-using-monitoring-tables&goto=newpost Sun, 28 Apr 2013 09:03:43 GMT Hello,

This stored proc collects the information from the monopenobjectactivity table and saves the data in TablesUsed table.

CREATE PROCEDURE TablesUsed (
@wait_time char(8) = '00:10:00',
@loop int = -10
)
AS
BEGIN

SELECT * into #temp1
from master..monOpenObjectActivity

SELECT * into #temp2
from master..monOpenObjectActivity


INSERT INTO TablesUsed
(DBName,TableName,IndexID,Operations,RowsInserted, RowsDeleted,RowsUpdated,LockWaits,LogicalReads,Phy sicalReads,UsedCount,LastUsedDate,FromDate,ToDate

SELECT db_name(U2.DBID),object_name(U2.ObjectID, U2.DBID),U2.IndexID,U2.Operations,U2.RowsInserted, U2.RowsDeleted,U2.RowsUpdated,U2.LockWaits,U2.Logi calReads,U2.PhysicalReads,(U1.UsedCount -U2.UsedCount),U2.LastUsedDate,dateadd (mi,@loop,getdate()),getdate()
from #temp1 U1 , #temp2 U2
where Object_name(U1.ObjectID,U1.DBID)=Object_name(U2.Ob jectID,U2.DBID)
and db_name(U1.DBID)=db_name(U2.DBID)
and U1.IndexID=U2.IndexID
and U1.IndexID = 0

END
go

Above is the stored procedure which i wrote to monitor the monopenobjectactivity table. DBA will run this stored proc for about 2 months. then i am thinking to query the "TablesUsed" table to find the unused tables. below is the query

select * from TablesUsed
where RowsInserted = 0 and RowsUpdated = 0 and RowsDeleted = 0
order by LogicalReads asc

My question is, can i consider this result where all the RowsInserted, RowsUpdated and RowsDeleted = 0's as unused tables in the database?

when I am observing the resultset of this above query, i am observing that even though RowsInserted, Deleted or Updated are 0 but we have Operations , Logical and Physical reads on that table...so does it mean that the table is being used?

if so, can anyone please guide me, how to write the query to find the unused tables?

Thanks in advance. ]]> Sybase sanandnas http://forums.databasejournal.com/showthread.php?53665-finding-unused-tables-using-monitoring-tables unused stored procedures using monitoring tables http://forums.databasejournal.com/showthread.php?53663-unused-stored-procedures-using-monitoring-tables&goto=newpost Sun, 28 Apr 2013 08:52:12 GMT Hello Experts,

can anyone please let me know, how to find the unused stored procedures using monitoring tables of master database in sybase.

actually, i already wrote 2 different stored procedures using monitoring tables to find the unused indexes and tables using monopenObjectactivity table. and those stored procedures are running in production to capture the data for around 2 months and then later i will write a query on permanant table which i created as part of stored proc to save the historical data and get the unused objects and indexes.

In the same way i need to create the stored procedure to monitor the historical data for unused stored procedures in the server and monitor the stored procedures for 2 production releases and then if those stored procs are never used from past 4 to 5 months in production then i need to drop them. This is my task.

can anyone please suggest me how do i write a stored procedure to monitor the unused stored procs and save the data to one permanant table and that should stored proc which i write dba's would schedule it to run on daily basis to gather the historical data. then i need to query the permanant table and list all the unused stored procedures. so that the developers would review them and decide whetther to really drop those stored procedure or not......

please experts, give me the query but please don't give me the links, as i have already googled and i'm unable to find a way...

http://www.sybase.com/sb_content/102...MDA-042406.pdf

this above pdf has the query for analyzing the stored procedure performance queries...by considering these information

I thought the below query would give the list of unused stored procedures when i run in the production server. But i observed that, this query is only pulling the stored procedures which are only called by applications in production.

select object_name(ProcedureID,DBID), ProcName = isnull(object_name(ProcedureID, DBID), "UNKNOWN"),
DBName = isnull(db_name(DBID), "UNKNOWN"),SPID, CpuTime, LogicalReads, PhysicalReads, MemUsageKB, StartTime, EndTime
LineNumber,
ElapsedTime = datediff(ms, StartTime, EndTime), KPID, BatchID
from master..monSysStatement
where ProcedureID != 0 and isnull(db_name(DBID), "UNKNOWN") not in ( 'sybsystemprocs ' , 'tempdb')

unable to figureout which monitoring table would help to know the unused stored procedures in the databases of productions server. can anyone please help me?

Thanks alot in advance. ]]> Sybase sanandnas http://forums.databasejournal.com/showthread.php?53663-unused-stored-procedures-using-monitoring-tables New Extended Capabilities in Tools for MySQL Server http://forums.databasejournal.com/showthread.php?53661-New-Extended-Capabilities-in-Tools-for-MySQL-Server&goto=newpost Fri, 26 Apr 2013 12:20:33 GMT Devart Team is glad to announce the release of MySQL Tools for database development and management which introduces new features as well as performance improvements.

New extended capabilities of dbForge Query Builder for MySQL v3.0, dbForge Data Compare for MySQL v4.0, and dbForge Schema Compare for MySQL v3.0 offer users code completion and formatting features. This functionality allows using comprehensive list of abilities for quick and easier creation, and data management, comparing and synchronizing data in MySQL databases.
New enhanced tools offer developers extended suggestion lists during the typing of SQL code and object information on different database objects. Profile formatting capabilities allow users to create new profiles and edit the existing ones easier than ever.

dbForge Query Builder for MySQL
New version of tool for simpler queries creation and editing now offers users such improved features as:
  • Visual building of DML statements
    Now users can design INSERT, UPDATE, DELETE queries in Query Builder. The type of query is stated on the diagram surface, and this label allows selecting tables to use in the query right away.
  • Conversion of SELECT queries into INSERT statements.
  • One-click conversion of SELECT query into UPDATE or DELETE with saving of WHERE condition.
  • Execution of separate subqueries to check their correctness without leaving diagram interface.
  • Ability to export query results as well as data directly from a table.
For more information about dbForge Query Builder for MySQL visit product's page — http://www.devart.com/dbforge/mysql/querybuilder/.

dbForge Data Compare for MySQL
Improvements of this powerful tool for comparing and synchronizing MySQL data include following software features:
  • New data comparison reports now contain table data (not just a summary).
  • Comparison of custom query results (along with tables and views).
  • "One-to-many" objects mapping when addressing columns that were separated as a table in the target database.
  • Possibility to include objects into comparison by mask, which is useful when comparing groups of tables.
  • Full-text data search in comparison results.
  • Analysis of comparison results is improved: 'Hide unmodified columns' option, per-column difference counters are added.

For additional information about Devart, visit http://www.devart.com/. ]]> MySQL Devart http://forums.databasejournal.com/showthread.php?53661-New-Extended-Capabilities-in-Tools-for-MySQL-Server