Database

Create trigger that executes on date

15-May-13 18:00

What is it with you people today, the previous bloke wanted to use a trigger, you want to deploy a dtx and schedule it with some tool. USE A SQL JOB. That is precisely what it is designed for! there are a whole bunch of stuff available [^]

Never underestimate the power of human stupidity
RAH

damokk15-May-13 3:54

15-May-13 3:54

Hi. I am creating an application that uses sqlserver and asp.net c#. I need to create a trigger that inserts data into a table 30 days before an expiry date field in another table. I have created the trigger but dont know how to implement the dates? Any suggestions would be appreciated.

Corporal Agarn15-May-13 6:31

Corporal Agarn

15-May-13 6:31

Does the date go in when the "expiry date" column is populate or do you want it to go in 30 days before the date?

Mycroft Holmes15-May-13 17:55

15-May-13 17:55

What you are describing is not a trigger spit but a JOB, the job would call a proc that checks for expiry dates 30 days away and insert the appropriate record. You would schedule the JOB to run daily.

Never underestimate the power of human stupidity
RAH

damokk15-May-13 23:06

15-May-13 23:06

thanks for the reply. I had a feeling a trigger would be difficult or impossible to implement in this way.. Not sure how to create this job tho. Any pointers would be appreciated?

damokk15-May-13 23:08

15-May-13 23:08

I already have a table with an appointment date entered. I need to update another table 30 days before before this appointment date. Thanks

Mycroft Holmes15-May-13 23:33

15-May-13 23:33

Create a stored procedure that does the following
Get the expiring records
insert the relevant data into the target table.

In SSMS create a Job that calls the stored proc
schedule the job to run every day

Never underestimate the power of human stupidity
RAH

damokk15-May-13 23:47

Regarding SSIS (SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel" failed with error code 0xC0202009.)

15-May-13 23:47

thanks I'll give it a try!

Ravikiran72p14-May-13 21:09

Ravikiran72p

14-May-13 21:09

I am new to ssis. I Created on pakcage which loops excel fils(.xls) of one folder and extracts to database.

I placed Excel files in one folder (like C:\Documents and Settings\username\My Documents\Temp). The Sqlserver2005 server located on another machine (where i need to dump the data from excel to this destination server.)

I am running package on my local machine. When i run it is giving below error at Excel Source of Data Flow task.

SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel" failed with error code 0xC0202009.

Please help me out.

Thanks,
Ravikiran

Re: Regarding SSIS (SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel" failed with error code 0xC0202009.)

Mycroft Holmes14-May-13 23:24

Regarding DB2 Rows to Column String Concatenation

14-May-13 23:24

My usual attempt to resolve a sql error is to drop the error message into google[^] - seems like it is a common problem.

Never underestimate the power of human stupidity
RAH

SenthilKumar-8313-May-13 5:05

SenthilKumar-83

13-May-13 5:05

Hi All,

In DB2, i have used XMLAGG built in function in order to convert Rows to Column String Concatenation. Since lot of records are available in the table, performance wise it was too slow. Whether any user defined function is available for DB2 for string concatenation.

Thanks in Advance.

How to Update this request Balance Column

Robymon13-May-13 1:49

Robymon

13-May-13 1:49

EmpID Leave_Year Taken Credit Balance
435 2014 0 22 22
435 2013 10 22 12
435 2012 24 22 0
435 2011 20 22 2
435 2010 23 22 0
435 2009 21 22 1

I want to Update this table Balance column value with the Next row Balance column value

eg: Leave_Year 2011, Balance value (2) Update with Leave_Year 2012 Balance Value (0)

0 - 2 = -2 should get in 2011 Balance column, use with this where condition, where Next row (2012) Taken>0

How to write Update sql for this

Re: How to Update this request Balance Column

Blue_Boy13-May-13 4:14

Blue_Boy

13-May-13 4:14

Can you explain more in details your case?

I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.

Re: How to Update this request Balance Column

gvprabu13-May-13 5:36

gvprabu

13-May-13 5:36

Hi,

Check the Script

SQL

SELECT M.ID,(ROW_NUMBER() OVER(PARTITION BY M.EmpID ORDER BY M.EmpID)) 'RowNumber', M.EmpID, M.Leave_Year, M.Taken, M.Credit, 
(SELECT SUM((ISNULL(Balance,0.0))
 FROM Table_Name WHERE ID<=M.ID AND EmpID=M.EmpID)
FROM Table_Name M
ORDER BY M.EmpID

Re: How to Update this request Balance Column

Jörgen Andersson13-May-13 23:53

Jörgen Andersson

13-May-13 23:53

I wouldn't do that. It's against the "Rules" of normalization. You don't want to make a row dependant on another row in the same table.
Think of all the fuzz to update the table when you find out someone inserted some wrong data last year, or when HR decides to change the rules (quite probable actually).

So you should make a query that gets you the result you want instead.
Hint, checkout ROLLUP.
For example:

SQL

SELECT EmpID,
       CASE WHEN (GROUPING(Leave_Year) = 1) THEN 'Total Balance'
            ELSE ISNULL(Leave_Year, 'UNKNOWN')
       END AS Leave_Year,
       SUM(Balance) AS Balance
FROM   MyTable
GROUP BY EmpID, Leave_Year WITH ROLLUP

I haven't tested this code, but it should give you an idea.
I also assumed SQLServer, Different Databases have different syntax. Use Google.

Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln

Migration process.

nagesh2113-May-13 0:00

nagesh21

13-May-13 0:00

Several screens (or one very functional one) to view the data in a variety of ways like by Phase, by ESXHost, by Application.
I think if we have several drop downs for filtering then that would work; so I can choose Phase 1, to show only the databases that will be migrated in Phase 1, then I can choose some other criteria and the screen shows a subset of data etc.
3. The Pre-requisites and Post-Migration checks should show whether the Phase or database migration is ready to start or is ok to complete.

Need your opinion on querying data for presentation on winforms / web page and for reports

Nayan Ambaliya12-May-13 12:53

Nayan Ambaliya

12-May-13 12:53

Dear all,

I require your opinion on what approach is good and/or practical for the following scenario.

There is a web application which queries data from SQL server.
For example:

Users report to a manager
1. The process by clicking a button is finding who reports to me (manager)
2. So there is a stored procedure written to find the users reporting to the manager

Now, there is a report (rdlc on the web server displayed via reportviewer control) which takes the manager as an input to generate report data for the staff members reporting to that manager.
The stored procedure for this report uses existing stored procedure (as in step 2 above) to find the staff members reporting to the manager and then generate required data.

My question is:
1. Is this the right way of doing this
2. Is there a need to develop a separate SQL view just for this report
3. What would be the best approach on doing this.

Please help.

Thanks

Regards,
Nayan

Re: Need your opinion on querying data for presentation on winforms / web page and for reports

Mycroft Holmes12-May-13 15:05

Re: Need your opinion on querying data for presentation on winforms / web page and for reports

12-May-13 15:05

I don't see any issues with this approach, the dedicated view may be useful if reconciliation is required or you are going to use an OLAP system but not for you current requirements.

Never underestimate the power of human stupidity
RAH

Nayan Ambaliya12-May-13 15:10

Nayan Ambaliya

12-May-13 15:10

Thank you very much.
I just needed a clarification for not under-estimating my decision over my superior.

Thank you very much.

Cannot insert the value NULL into column

Member 991209112-May-13 12:33

Member 9912091

12-May-13 12:33

Hi all. I have run into a problem with inserting values into table.

I have 2 tables: PUBLISHER with fields: PUBLISHER_CODE (Primary Key) and PUBLISHER_NAME. BOOK table fields: PUBLISHER_CODE (Primary Key and Foreign key to PUBLISHER table) and TITLE.

The user enters bookCode and publisherName into 2 textboxes

Here is the code for inserting:

"INSERT INTO BOOK (TITLE) VALUES(@bookCode)";

"INSERT INTO PUBLISHER (PUBLISHER_NAME) VALUES(@publisherName)";

When running programme, the PUBLISHER table generates a new PUBLISHER_CODE (Primary Key) and publisherName inserted.

Now problem is for the BOOK table the following error appears:

"Cannot insert the value NULL into column 'PUBLISHER_CODE'", table BOOK Sigh | :sigh:

Why is this happening? Doesnt BOOK generate same/new PUBLISHER_CODE from PUBLISHER table since its a foreign key?

Re: Cannot insert the value NULL into column

Mycroft Holmes12-May-13 15:02

Re: Cannot insert the value NULL into column

12-May-13 15:02

Member 9912091 wrote:
Doesnt BOOK generate same/new PUBLISHER_CODE from PUBLISHER table since its a foreign key?

No - you have to retrieve it from the inserted publisher record. I use a stored proc for this and return the new record after it is inserted, then you can use that ID to insert the book record.

Or

Wrap the whole thing in a SQL Transaction in a stored proc and do the insert to both table at once.

Never underestimate the power of human stupidity
RAH

Bikash Prakash Dash13-May-13 19:27

Bikash Prakash Dash

13-May-13 19:27

Primary key value cant be null. You have to provide any value for that.

Re: Cannot insert the value NULL into column

Mycroft Holmes13-May-13 20:55