Anyone Else Finding This Painful?

Jan 19, 2006

Although I am sure I will grow to love this new product I can honestly say that everyday has been a painful experience. Most of this, once again, is the learning curve to the new product. Moreover, each accomplishment leaves us feeling as if we are doing things the wrong way.

Here is an example... We are populating lookup tables from a staging environment. This is pretty straight forward, insert records that don't already exist in the destination. Unfortunately, the only way we could get this to work properly, outside of just writing T-SQL for everything, was to use a lookup transformation and redirect the error rows into the table. Although we have ran numerous tests against it with no problems it certainly doesn't let you walk away with a very comfortable feeling and certainly isn't a choice I would recommend. Are others having this same struggle and uncomfortableness?

Select aspirin
into Body
from Ailments
where condition like '%SSIS%'

-Krusty

View 10 Replies


ADVERTISEMENT

Somewhat Painful Join Code

Jul 20, 2005

Hello.Newbie on SQL and suffering through this.I have two tables created as such:drop table table1;godrop table table2;gocreate table table1(name varchar(10),code1 integer,code2 integer,code3 integer,code4 integer,code5 integer);gocreate table table2(code integer,descript varchar(50));goINSERT INTO table1 VALUES ('mary',1,7,8,9,13)INSERT INTO table1 VALUES ('mary',1,7,8,9,13)INSERT INTO table1 VALUES ('mary',1,7,7,7,13)INSERT INTO table1 VALUES ('mary',1,7,8,9,13)INSERT INTO table1 VALUES ('joe',1,7,8,9,3)INSERT INTO table1 VALUES ('bob',1,7,8,9,3)INSERT INTO table1 VALUES ('larry',22,17,18,19,113)INSERT INTO table1 VALUES ('mary',1,3,2,9,13)INSERT INTO table2 VALUES (1,'code1')INSERT INTO table2 VALUES (3,'code3')INSERT INTO table2 VALUES (7,'code7')goTable1 will have duplicate name entries and code1 - code5 couldcontain any range of numeric codes, including dups in the same row orother rows.Table2 sorted and indexed unique by code contains only the codes I'minterested in reporting.I need to produce report:name code count (of names that contain at least 1 code in Table2)I'll go back to suffering through it now, but I thought I'd post sinceI'm not exactly sure where to begin.Thank you for any help or information!My above email is no longer active, so please post to the list so thateveryone can benefit and your contribution lives on.

View 4 Replies View Related

Painful Installation Of RS 2005 On Win2K

Jul 13, 2007

Ok I'm am going through a rather painful experience of installing Reporting Services on:



Windows 2000 SP4

MDAC 2.8

.Net Framework 2.0

IIS 5.0

SQL Server 2005 Developer



I have installed RS 2005 on Windows 2003, and seen it installed on XP no problems. However this is a nightmare.



RS goes ahead and installs - note that there is no Network/Local service option on the service accounts just Local system or the domain account option. I have tried using the Local system and tried using the NT AUTHORITY/SYSTEM.



After the installation I go to the Reporting Services Configuration Tool, where I have crosses on:

- Server Status

- Report Server Virtual Directory

- Report Manager Virtual Directory

- Web Service Identity

- Database Setup

I- nitialization



So I go to report server/manager and click on "New.." on each to create the virtual directories and check "apply default settings" and click "Apply", all goes well



Web Service Identity I cannot change so I go to:

C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServer
sreportserver.config



and change the web service account to:

<WebServiceAccount><<MACHINE NAME>>ASPNET</WebServiceAccount>

When I restart the reporting services service I no longer see a warning in the reporting services log file about the WebServiceAccount



I go back to the Reporting Services Config Tool, where I have crosses on the following:

Web Service Identity

Database Setup

Initialization



The Web Service Identity stays greyed out...



I go to Database Setup:

connect to the server

select the reportingserver database

leave it set as service credentials

Click "Apply"



and I get the most vague of all error messages:

"There was a failure applying your change

A virtual directory must first be created before performing this operation"



What virtual directory, where???



I look in the log files..nothing. I look in the event viewer...and...nothing.



I look on here...

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=628231&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1682492&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=493194&SiteID=1



Either unanswered or there is the one answer but it is not relevant in this case.



Can someone please provide some advice before I lose my sanity?



Thanks in advance

View 1 Replies View Related

Finding Something In SQL

Jun 22, 2007

Hello,

I am very new to SQL and I have an issue. We have a website that has pictures on the site. We want to take them down but the pictures are somehow tied into the database, they aren't on any of the servers. How can I find these pictures that seem to be somehow hard coded into the database?

Any help would be greatly appreciated.

View 2 Replies View Related

Finding Right Row

Mar 11, 2008

i have

create table test

(id_text int
,text_name varchar(10)
,id_author int)

insert into test (id_text, text_name, id_author) values (205, 'name1', 123)
insert into test (id_text, text_name, id_author) values (205, 'name2', 124)
insert into test (id_text, text_name, id_author) values (205, 'name2', 125)
insert into test (id_text, text_name, id_author) values (206, 'name2', 124)
insert into test (id_text, text_name, id_author) values (207, 'name2', 122)
insert into test (id_text, text_name, id_author) values (207, 'name2', 128)

select id_text, id_author from test

and i need to get out only id_text with the first author.
any solutions for sql server 2000?

thank you

View 6 Replies View Related

Finding The Max Value

Aug 16, 2006

I have a report that is grouped by week. The details of the week have the date, time interval (half hour), and the maximum value for that day. So in the example below I am showing the week of May 21st. Then I have all of the days of that week, sorted by date as requested, with the halfhour the maximum value occured in on that day. So Monday the 22nd has the maximum value for the week at 254 (and is highlighted in red). Where the ????? are below, I need to display '1400' which is the interval, but have not yet been able to figure out how to do this.

I tried the following expression:
=iif(max(Fields!MaxValue.Value) = Fields!MaxValue.Value, Fields!Interva.Value, "X")

but this only works if the first day is the maximum value.





5/21/2006



?????
254

Sun, 5/21/2006
1000
147

Mon, 5/22/2006
1400
254

Tue, 5/23/2006
1830
141

Wed, 5/24/2006
1830
107

Thu, 5/25/2006
0830
210

Fri, 5/26/2006
1900
36

Sat, 5/27/2006
1000
149







Any ideas are greatly appreciated!

View 11 Replies View Related

Finding The Next Record

Apr 26, 2007

I have, say 4 rows for 1 client I want to find the date difference between two(or more) of the rows when the first has a true flag and the next does not
Row1  Date1,falseRow2  Date2,trueRow3  Date3,falseRow4  Date4,false
so I want to find the difference between Row2 Date2 and Row3 Date4I know I can loop through the rows but with 000's of clients each with x numbers of rows this will surely be very slow 
 

View 21 Replies View Related

Finding Data

Feb 2, 2008

I have a form where users can enter an appointment on this form some personal details are entered. I want the users to be able to write in a textbox the users surname. they will then press a button next to the textbox that will bring up a smaller screen displaying that users details :
SELECT * FROM Members where surname = (textbox1)
Alternatively the users can click the button and bring up a screen where the users can put the surname into a textbox and then bring up the users details this way.
I am not sure how to do this or how to select from the database where a field in the database is the same as the information put in to a text.
Is this possible? Thanks in advance Mike

View 1 Replies View Related

Finding SQL Triggers

Feb 29, 2008

I have a table TEST.
 I want to find the triggers which use this table test.
For example: If there is a table A, and a insert in A causes a trigger to insert data in TEST,
I want to find this particular trigger.
Any ideas?
I am using SQL server 2000
thank you

View 4 Replies View Related

Finding The Unique....

Aug 28, 2001

hai
I have to delete a single row from a table , where there are multiple rows with the same data and i want to delete only one row and i cannot alter my table
structure.
Thanks in advance
satya

View 1 Replies View Related

Finding Duplicates In SQL

Jul 26, 2002

I have a customer database with the following structure:

Dept ID (int)
Section (varchar)

I need to find only occurrences of a section (eg Admin) where the section name has a record in Dept 1,2 and 3 - only return the result if the record for Admin is associated will these depts.

Example Data:

Dept Section
1 Admin
1 Finance
1 Marketing
2 Admin
2 Sales
2 Marketing
3 Admin
3 Sales
3 Finance

The query would only return 'Admin' since this is the only Section that is represented in Departments 1,2 + 3.

I am not in control of the data design and can't create a bitwise field to achieve this.

tia,
Steve

View 1 Replies View Related

Finding Suid

Oct 18, 1999

How can I find out a user's suid or login name from within a stored procedure?

View 1 Replies View Related

Finding The First Distinct Value

Sep 8, 2004

Hi

My problem is a little tricky. I am trying to get the first distinct row(s) from a table where one column will have only a few differences, while a second is unique.

Now using "distinct" doesnt help as even though distinct(column1) may return only a few rows, because the next column is unique you will still have all rows returned.

How do I return only 1 row per distinct(column1) and drop the rest? What makes this so annoying is that all columns need to be returned, but only in accordance to distinct(column1)

View 1 Replies View Related

Finding Relationships

Sep 23, 2005

Hi, sorry if this comes across as a stupid question, but i'm new to SQL. I have had a look on Google and on these forums but not found the answer..
I am running MS SQL 7 with a database containing around 130 tables.. i want to know if there is a query/command that i can type that will look through all the tables and tell me which ones reference a specified table (through it's key). Hope this makes sense! i've looked at some of the stored procedures but not found any that give me anything useful.

EDIT: Just so you are aware, i did not create this database, hence why i am not aware of the relationships myself, i have only just started working on it.

Thanks
Steve

View 2 Replies View Related

Finding The Numbers

Jul 8, 2005

I have a column of data of type varchar. it contains a mix of text id's and numeric id's.
all i want is the highest purely numeric value
The closest i can get is to use a regex expression, but it don;t work and i just know there is a better way of doing it.

SELECT * FROM customers WHERE custref LIKE 'd*d';

I am using MS SQL 7

thanks so much for any help

View 4 Replies View Related

Finding The Table Name.

Dec 15, 2006

I have a script that creates these spin off tables (used for lightning fast access) on a near daily basis. But it is possible the table does not exist for that day - in which the script need to check one day back - so on and so forth until finding the most recently created table.

The script I have written fails with overflow error.
I was hoping someone could tell me the correct syntax to accomplish this and also why i recieve this overflow error...


Code:


Dim str_SQL_Asset_1
daysBack = 1
tblFound = FALSE
Dim xbl_Holdings_date
Dim xMonth
Dim xDay
xbl_Holdings_date = date()
If Len(Month(xbl_Holdings_date))=1 Then
xMonth = "0" & Month(xbl_Holdings_date)
Else
xMonth = Month(xbl_Holdings_date)
End If
If Len(Day(xbl_Holdings_date))=1 Then
xDay = "0" & Day(xbl_Holdings_date)
Else
xDay = Day(xbl_Holdings_date)
End If

DO WHILE tblFound = FALSE
xbl_Holdings_Name = "xbl_Holdings_" & Year(xbl_Holdings_date) & "_" & xMonth & "_" & xDay
SQL_tblCheck_STR = "SELECT * FROM INFORMATION_SCHEMA.TABLES " & _
"WHERE table_name = '" & xbl_Holdings_Name & "' "

Set MM_checkCmd = Server.CreateObject("ADODB.Recordset")
MM_checkCmd.ActiveConnection = conn_str_ClientDB
MM_checkCmd.Source = SQL_tblCheck_STR
MM_checkCmd.CursorType = 0
MM_checkCmd.CursorLocation = 2
MM_checkCmd.LockType = 3
MM_checkCmd.Open()


IF MM_checkCmd.EOF Then
xbl_Holdings_date = (xbl_Holdings_date - daysBack)
daysBack = daysBack + 1
tblFound = FALSE
ELSE
tblFound = TRUE
MM_checkCmd.CLOSE()
SET MM_checkCmd = Nothing
END IF
loop



The goal of this snippet is simply to identify and set the value for the existing table name.
An example table name would be "xbl_Holdings_2006_12_10".

I could have posted this question in the VB forum but I would like to push as much of the work onto SQL as possible and also - this sort of operation is more likely to be required of a dba imo.

View 1 Replies View Related

Finding Duplicates

Jul 7, 2004

I am trying to complete an insert from query but the problem is I have duplicates, so I'm getting an error message. So to correct it I am creating a Find Duplicates statement in the Query analyzer but Its not working can someone tell me whats wrong with this statement (by the way I'm in SQL 2000 Server)

thank you

SELECT EmployeeGamingLicense [TM#]AS [TM# Field], Count([TM#])AS NumberOfDups
FROM TERMINATION
GROUP BY [TM#]
HAVING Count([TM#])>1;
GO

View 14 Replies View Related

Finding Row Size

Oct 7, 2004

Hi,
Anyone got any ideas how to calculate the occupied row size of a table?

View 1 Replies View Related

Help Finding The Max Total

Dec 3, 2004

Hi,

I have the following code

SELECT
PR.WBS2,
SUM(CASE WHEN LedgerAR.Period = '200408' AND LedgerAR.TransType <> 'CR'
THEN Ledgerar.amount * - 1
ELSE '0' END) AS BillExt
FROM PR
LEFT JOIN Ledgerar ON PR.WBS1 = Ledgerar.WBS1 AND
PR.WBS2 = Ledgerar.WBS2 AND PR.WBS3 = Ledgerar.WBS3
WHERE PR.WBS2 <> '98' AND pr.wbs2 <> '9001'
AND pr.wbs2 <> 'zzz' AND pr.wbs3 <> 'zzz' AND
pr.wbs1 = '001-298'
GROUP BY PR.WBS2



It prints out:
Wbs2 BillExt
0141 0
0143 0
1217 20580

I want the code to return the wbs2 code '1217' because it has the highest amount in BillExt '20580'.

Can someone help me with this?

Thanks.
laura

View 10 Replies View Related

Finding Last Entries

Jan 27, 2005

Hi, I am searching for the most easy SQL solution:

Lets say i have 500000 rows of cars in one table with 30000 different car ID's.
This table contains no keys, no date values, but I need the last row of these 30000 different cars. How do I get them without adapting table structure and without using cursors ?

Thx for reply.

dajm

View 11 Replies View Related

Finding Duplicates - Is This Right?

May 16, 2007

I've done a search and I THINK I've got my head round this, but I'd be very grateful if someone could reassure me:

SELECT Email FROM List1 WHERE EXISTS (
SELECT Email FROM List2 WHERE List2.Email= List1.Email
) AND List1.Email <> '44'

That will give me every email address from list one that (a) appears in list two, and (b) isn't '44'. Right?

And to find all the emails from List1 that DON'T occur in List2 (and aren't '44'), I just put "NOT" in front of "EXISTS". Right?

Sorry for asking an obvious question but I'm having a real mental block here. :o

View 6 Replies View Related

Finding Some Tables

Feb 8, 2008

I have many tables in one database, and some of them are empty. How can I list / find (for eg. names) only non-empty tables?

View 2 Replies View Related

Finding A Set Of Data Not In Another Set

Apr 15, 2013

Our products exist in batches. These batches reference a warehouse.Each warehouse carries a set of 'roles'.Users on our system also carry 'roles'. Comparing user and warehouse roles tells the system what warehouses a user can access.

So, if:

WAREHOUSE1 has roles A + B
WAREHOUSE 2 has role B
WAREHOUSE 3 has roles A + C
and
USER1 has role A
USER2 has roles A + B + C
USER3 has role C
then
USER1 will have access to WAREHOUSE1 and WAREHOUSE3
USER2 will have access to all 3 warehouses
USER3 will have access to WAREHOUSE3.

What I need to do is write some SQL which allows a part number to be entered, and a list of batches for that part to be shown with a list of users who *CANNOT* access that part - because they do not have the relevant 'roles' for the warehouse(s) the part is in.

I can do the *opposite* of this quite easily (ie: show users who HAVE got access):

select distinct sb.batchnumber, ur.userid
from stockbatches sb
inner join warehouseroles wr on sb.warehouseid = wr.warehouseid
inner join user_roles ur on wr.roleid = ur.roleid where sb.partid = @part
order by sb.batchnumber, ur.userid

- it's a straightforward join between the warehouse and user roles, with a 'distinct' to eliminate any duplications.

Fair enough, but it's not what I want.

Using the role data above, with the following batch info:

Batch 1000, warehouse WAREHOUSE1
Batch 1001, warehouse WAREHOUSE2
Batch 1002, warehouse WAREHOUSE3

I actually want to get this results set:

Batch Users who can't use this batch
1000 USER3
1001 USER1
1001 USER3

I'm certain I can write the necessary logic programmatically in T-SQL, looking for single-statement SQL call?

View 2 Replies View Related

Finding Out Indexes

Feb 26, 2004

Hi All,

Sql server 7

Is there anyway of listing the indexes that are present in a database.


TIA
Adil

View 1 Replies View Related

Finding A View Or Sp

Mar 19, 2004

According to a developer that worked on my db, there is a view :
xvw_CheckVoucherReport

I cant see it is there any way i can find out it exsist and if so what database on my server it is in?

View 4 Replies View Related

Help With Finding Out Latest Changes

Apr 8, 2008

So here is my dilemma:

We have licensed software from a 3rd party and we do not have the source code, nor can we do anything to change the database schema, write our own procs, triggers, etc.

With that said, we want to know which records in certain tables have been added or modified since a certain time (for example, in the past 24 hours). However, none of the tables has any datetime or timestamp columns on them for create date, update date, etc.

I tried to look into hacking thru DBCC log (dbname, 2) to see if that would help, what a mess. Using a tool like that from Luminent or Red Gate doesnt help either, because long term this needs to be automated if possible. Does anyone have any ideas on how to do this, or is the answer simply, "Sorry mate, you need to get them to allow you to change the database to add new columns and/or triggers". :)

View 5 Replies View Related

Finding Average

Apr 9, 2008

Hi, I have two tables, Weekly and AverageEngTime. Weekly table has 14 columns, "Mon_Day,Mon_Night,Tue_day,....Sun_Night". AverageEngTime table has 15 columns, "Shifts,Mon_Day,Mon_Night,Tue_day,....Sun_Night". I have inserted "Average" as a value for column "Shifts" in AverageEngTime table. Now how do I find the average for each column in Weekly table and insert into AverageEngTime table in the respective columns, and in the row where Shifts = "Average".

I used the following codes to try but i receive errors at where statement.Please correct me. The expected output as below.

Shifts | Mon_day | Mon_Night | Tue_Day.....
Average | 5.2 | 10.2 | 15.2........

The codes that i tried..

declare @Weekday tinyint, @hour int
select @Weekday = datepart(dw,getdate()),@hour= datepart(hh,getdate())

if (@Weekday= 1 and (@hour>= 7 AND @hour<19))
begin
Insert Into AverageEngTime(Sat_Night) where Shifts = 'Average'
SELECT AVG(Sat_Night)
FROM Weekly
end

if ((@Weekday= 1 and @hour >= 19) OR (@Weekday = 2 and @hour < 7))
begin
Insert Into AverageEngTime(Sun_Day) where Shifts = 'Average'
SELECT AVG(Sun_Day)
FROM Weekly
end

View 7 Replies View Related

Finding Data

Apr 18, 2008

I need to find the object which holds the value(in column)
Whether it is possible?

suppose i need to find the text 'Goal' in stored procedure then i will give:
select * from syscomments where text like '%goal%'

suppose i need to find the text 'Goal' in table level.any system stored procedure?

Txs in Advance..

View 1 Replies View Related

Finding A Column ??????

May 2, 2008

Hi,
How to find if a column exists in Database Table ?

thanx in advance

View 6 Replies View Related

Finding My Views

May 30, 2008

Hi,

I our firm there are 4 people who are working on a database. I need to edit all of my views. Is there any way I can find out which view was created by me?

Please help me.

View 1 Replies View Related

Finding Duplicates

Jun 5, 2008

how can i find duplicates in my database? i want to find them in the website and expressemail field

View 5 Replies View Related

Finding Duplicates Using More That One Key

Jun 16, 2008

Hi guys,

I was just wondering if any one knows how to find duplicate keys using more than one field. I used the below key to find those people who exists in list1 but don't exists in list2. I realized that the results had some duplicates which was expected but how do I then find all those duplicate people. I know how to do it if there was a primary key present I would have done a count (distinct cardnumber) > 1 and i would have done the select statement like this distinct cardnumber, but how do I do it with more that one key??

View 1 Replies View Related

Help In Finding Sign .

Jun 21, 2008

Hello,

Is there any built in function to find out the sign of a value.
For Ex: -225.89 Sign is -
566.987 Sign is +

Thanks
Ganesh Kumar

Solutions are easy. Understanding the problem, now, that's the hard part

View 2 Replies View Related







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