SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 DocID's and Coresponding Reference Keys
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

10Dawgs
Starting Member

USA
5 Posts

Posted - 01/03/2011 :  15:54:19  Show Profile  Reply with Quote
How do i write a script to input Doc ID (unique key) and return Reference key (foreign key) and visa versa?

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/03/2011 :  20:32:34  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Depends on where they are held.
maybe

insert tbl (DOcID, ...) select @DocID, ...
select scope_identity()


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

10Dawgs
Starting Member

USA
5 Posts

Posted - 01/04/2011 :  09:32:38  Show Profile  Reply with Quote
That gives me a start. I need to know the DB? table? where both keys are located? How do i save the script when I get it running as a report on the report services?

quote:
Originally posted by nigelrivett

Depends on where they are held.
maybe

insert tbl (DOcID, ...) select @DocID, ...
select scope_identity()


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/04/2011 :  09:37:41  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
I would put it in a stored procedure and if necessary base a report on that.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

10Dawgs
Starting Member

USA
5 Posts

Posted - 01/04/2011 :  10:39:10  Show Profile  Reply with Quote
I have posed the following questions to the person requesting the table.
1- I need to know where we are pulling this information from (Data Base(s) & table(s)).
2- Is the user going to put in a list of Doc IDs or input one at a time?
3- Do we want to save it as a report on the report server or just save it as a stored proceedure?

Nigel,
In your code above, Can I assume ... means location i.e.(dbo.MediaStatusCatalog)

What other questions do I need to pose (to the requesting party) to get this thing done? (He thinks I just waive a magic wand and it's done.)
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/04/2011 :  10:51:19  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
It means everything that is missing. You haven't given a lot to go on here.

I would assume that the data will be saved in a table or tables but maybe it's just a list that the user wants a report based on - in which case it probably wouldn't be persisted and would be a criteria in a report.
If there is a report involved what form should it take - i.e. what will the user want to do with the result.
And finally what data and structures are available.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

10Dawgs
Starting Member

USA
5 Posts

Posted - 01/04/2011 :  12:01:19  Show Profile  Reply with Quote
"You haven't given a lot to go on here." Right, now you're starting to feel my pain. Plus, I was hired as a DBA, not a programmer. So please pardon the dumb questions, I'm completely out of my element. With that in mind, The answers I got back were:

1- I need to know where we are pulling this information from (Data Base(s) & table(s)). THE VENDOR WILL HAVE TO TELL US (So for our purposes we will say it is dbo.tables)
2- Is the user going to put in a list of Doc IDs or input one at a time? IS A LIST
3- Do we want to save it as a report on the report server or just save it as a stored proceedure? IT IS A DYNAMIC REPORT

Nigel,
What other information do I need to ask?

Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 01/04/2011 :  23:55:07  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Ok so what you want is an SP that takes a list of IDs and returns a resultset.
If you want to use reporting services for the report then that list will probably be a csv list - maybe a selection from a drop down list.

So you need to know
What information does the user require for each ID.
Does the user need any aggregation?
Is there a time range needed
Do they need a search capability to select the IDs. Or maybe they want everything added yesterday - that would be a scheduled report and much simpler.
What will they want to do with the data.
How much data is expected - could effect how it is delivered.

Then you will have to find out whether the data expected is available and how it is stored.

Once you have that you can think about building the SP and report.


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000