Selecting Tables According To Sql Version Fails At Precompile

Oct 6, 2006

I am pulling info out of MSDB to report on job schedules. As we have a mixture of 2005 and 2000 servers, I am varying my select statement according to the result of
[Code]

Set @Version = SubString(Convert(VarChar(10), (Select ServerProperty('ProductVersion'))),1,1)

[/Code]

I have to vary the tables for each condition. Not a problem,
[Code]
if @Version = 9
begin
Select whatever from table1 join table2
end
Else
begin
Select whatever from table1 join table3
end

Problem is that SQL 2005 is trying to verify the existence of the fields in the tables on compile (it knows that the table, for example, sysjobschedules exists in 2005, but the field "Name" doesn't - although it does in 2000) , even after the if statement checking if the version is 8, and SQL 2000 uses 2 tables whilst 2005 needs 3, and the fields I need are on one table in 2000, but in a different table in 2005.
SQL 2000 is fine with it - it just does it.
Syntax checking is fine.
Just to clarify, here is my code:
[Code]


Declare @Version Char(1), @CurrentDate datetime

Set @CurrentDate = GetDate()

Create Table #TempSchedDetails

( Server VarChar(30)

,CurrentDate VarChar(19)

,JobName VarChar(80)

,ScheduleName VarChar(80)

,Freq_Type Char(1)

,Freq_Interval VarChar(2)

,JobEnabled Bit

,Freq_Subday_Type VarChar(25)

,ScheduleEnabled Bit

,StartTime VarChar(25)

,EndTime VarChar(25)

)

Set @Version = SubString(Convert(VarChar(10), (Select ServerProperty('ProductVersion'))),1,1)

If @Version = '9'

Begin

Insert Into #TempSchedDetails

Select left(@@SERVERNAME,30)

,GetDate()--@CurrentDate

,J.Name

,SS.Name

,SS.Freq_Type

,SS.Freq_Interval

,J.Enabled

,SS.Freq_SubDay_Type

,SS.Enabled

,SS.Active_Start_Time

,SS.Active_End_Time

From msdb..SysJobs J

Left Outer Join msdb..SysJobSchedules JS on J.Job_Id = JS.Job_Id

Join msdb.dbo.SysSchedules SS ON JS.schedule_id = SS.Schedule_Id

Order By J.Name, Active_Start_Time

End

Else

Begin

Insert Into #TempSchedDetails

Select left(@@SERVERNAME,30)

,@CurrentDate

,J.Name

,S.Name

,S.Freq_Type

,S.Freq_Interval

,J.Enabled

,S.Freq_SubDay_Type

,S.Enabled

,S.Active_Start_Time

,S.Active_End_Time

From MSDB..SysJobs J

Left Outer Join MSDB..SysJobSchedules S On J.Job_Id = S.Job_Id

Order by J.name, Active_Start_Time



End
[/Code]
Any ideas as to how to turn the precompile checking off in 2005?

View 3 Replies


ADVERTISEMENT

Selecting Latest Version.

Feb 11, 2008

Hi everybody!

I am developing realese system. It contains application, application releases and changes that have been made in a particular release.

What i need to do is: I need to retrieve the latest version for a particular application and the latest change so as a result I would see in a table:
application, latest version and latest change for the latest version.

I tried to querry that using Max function but it does not seem to work as I get e.g. 2 rows when there are 2 records for the same version.

Could anyone help me?

View 13 Replies View Related

Selecting Version Of Remote SQL Instance Into Local Variable?

Feb 28, 2007

Hello all,
I'm a bit new to SQL Server and T-SQL (my background is in DB2), so hoping you can help me with this. I'm writing a T-SQL script that's hopefully going to scan a bunch of SQL server instances and record the results. We've got a mix of SQL2K and SQL2K5 instances, and for part of my script I only want to run something if the remote instance is SQL2K.

I'd tested this on my local PC with several instances created and it worked fine. When I try and run it for instances located on other servers it barfs :eek: . From what I've found from looking up the error message it looks like I've got myself a loopback -- but I don't know how to get around it - any ideas please?


DECLARE @tempdata TABLE (scratch sql_variant)
DECLARE @debug tinyint
DECLARE @server sysname
DECLARE @version varchar(20)

SET @debug=1
SET @server='SQLSERVER_INSTANCE'

INSERT INTO @tempdata (scratch) EXEC ('[' + @server + '].master.dbo.sp_executesql N''SELECT SERVERPROPERTY(''''ProductVersion'''')''')
SELECT @version = convert(varchar(100),scratch) from @tempdata
DELETE FROM @tempdata
IF @debug>0 BEGIN PRINT @server + ' is running SQL Server version: ' + @VERSION END
IF charindex('8.00',@version) > 0 BEGIN
PRINT 'SQL2K-only code goes here'
END


The error I get is:
OLE DB provider "SQLNCLI" for linked server "SQLSERVER_INSTANCE" returned message "The transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "SQLSERVER_INSTANCE" was unable to begin a distributed transaction.


I get this error running from a SQL2K5 instance against both SQL2K or SQL2K5

TIA, moff.

View 3 Replies View Related

Can't Precompile Script

Feb 9, 2006

Hi,

with a script task I get an error "This task is configured to pre-compile the script but the binary code is not found. Please visit the IDE in Script Task editor by clicking Desing Script button to cause binary code to be generated".

Well, if I do so the error doesn't disappear. The only chance I have is to switch of precopilation, which is quite a performance issue...

I have no idea where this error is comming from... The script is quite easy, just some string and file operations (find out file change date using system.io)...

Any idea?

View 8 Replies View Related

How To Precompile A Script Task Through The API?

Nov 27, 2007

I've been working on adding "copy a script task from one package to another package" functionality to my SSIS Package Manager (PacMan - http://www.codeplex.com/pacman) utility in order to ease some of the pain I'm feeling in my main SSIS dev project these days. The code I have today looks something like this:




Code Block
public void CopyScriptTaskPrototype(PackageUtil sourcePackage)
{
// Basic logic taken from https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=941508&SiteID=1
const string CODE_MONIKER_TEMPLATE = "dts://Scripts/{0}/ScriptMain.vsaitem";
const string PROJ_MONIKER_TEMPLATE = "dts://Scripts/{0}/{0}.vsaproj";
const string BINN_MONIKER_TEMPLATE = "dts://Scripts/{0}/{0}.dll";
try
{
// Get the source task
TaskHost sourceScriptTaskHost = sourcePackage.ssisPackage.EventHandlers["OnPreExecute"].Executables[0] as TaskHost;
ScriptTask sourceScriptTask = sourceScriptTaskHost.InnerObject as ScriptTask;
// Get the target container
if (!this.ssisPackage.EventHandlers.Contains("OnPreExecute"))
{
this.ssisPackage.EventHandlers.Add("OnPreExecute");
}
DtsEventHandler targetContainer = this.ssisPackage.EventHandlers["OnPreExecute"];
// Delete the target task, if it exists
if (targetContainer.Executables.Contains(sourceScriptTaskHost.Name))
{
targetContainer.Executables.Remove(sourceScriptTaskHost.Name);
}
TaskHost targetScriptTaskHost = targetContainer.Executables.Add("STOCK:SCRIPTTASK") as TaskHost;
targetScriptTaskHost.Name = sourceScriptTaskHost.Name;
targetScriptTaskHost.Description = sourceScriptTaskHost.Description;
ScriptTask targetScriptTask = targetScriptTaskHost.InnerObject as ScriptTask;
targetScriptTask.SetUniqueVsaProjectName();
string sourceCodeMoniker = string.Format(CODE_MONIKER_TEMPLATE, sourceScriptTask.VsaProjectName);
string targetCodeMoniker = string.Format(CODE_MONIKER_TEMPLATE, targetScriptTask.VsaProjectName);

string sourceProjectMoniker = string.Format(PROJ_MONIKER_TEMPLATE, sourceScriptTask.VsaProjectName);
string targetProjectMoniker = string.Format(PROJ_MONIKER_TEMPLATE, targetScriptTask.VsaProjectName);
string sourceBinaryMoniker = string.Format(BINN_MONIKER_TEMPLATE, sourceScriptTask.VsaProjectName);
string targetBinaryMoniker = string.Format(BINN_MONIKER_TEMPLATE, targetScriptTask.VsaProjectName);
targetScriptTask.CodeProvider.PutSourceCode(targetCodeMoniker,
sourceScriptTask.CodeProvider.GetSourceCode(sourceCodeMoniker));
targetScriptTask.CodeProvider.PutSourceCode(targetProjectMoniker,
sourceScriptTask.CodeProvider.GetSourceCode(sourceProjectMoniker));
// We've commented this out due to errors at package runtime
//targetScriptTask.CodeProvider.PutBinaryCode(targetBinaryMoniker,
// sourceScriptTask.CodeProvider.GetBinaryCode(sourceBinaryMoniker));
targetScriptTask.PreCompile = false; // We want to be able to say "true" here
}
catch (Exception ex)
{
throw new ApplicationException("Could not copy script task - oh no!", ex);
}
}

This method exists within a PackageUtil class that has a private member variable named ssisPackage of type Microsoft.SqlServer.Dts.Runtime.Package, and for my current purposes I only need to worry about copying the first task from the OnPreExecute event handler for the package, so some things are hard-coded now that won't be later on.

Now, with that said, here is the question: How, through the .NET API can I force the Script Task code to be compiled so that I can set the PreCompile flag to true and have it work as if I'd done the work through VSA in the designer.


Thanks in advance to anyone who can help out here. This is "icing on the cake" to some extent, as the core functionality I need is currently in place, but it would be excellent to have this 100% complete.

View 14 Replies View Related

Precompile Script Task Programatically

May 11, 2006



I have code which generates packages programatically, and script task is a part of the control flow. I've succeded to set source code programatically, but I do not know how to put binary code, because I need to have my script task precompiled.



Just setting PreCompile = true does not solve this problem



Thanks in advance.



Borko

View 1 Replies View Related

Selecting From Different Tables

Jul 23, 2005

Using SQL2000. How do I format my select statement to choose one outof 24 different tables? Each table is slightly different and I washoping I could use one select statement and format it on-the-flyinstead of using 24 different ones. I had in mind using a casestatement, something like this:select * fromcase when <input parameter> = 'something1' then tblSomething1case when <input parameter> = 'something2' then tblSomething2...and so on...Thanks for any help.

View 4 Replies View Related

Selecting From A Tables

Nov 1, 2006

Hello I have two tables:

Package

PakageID Destination Source

1 xyz abc



And

PendingPackages

ClientID PackageID Status

1 1 0

How would I create a select statement to select all rows from Package (PackageID, Destination, Source) where the CleintID=1 and the PackageID exists in pending packages with a status of 0?

Can this be done using a select statement or would I have to create a view? I'm not too sure!

Thanks :)

View 3 Replies View Related

Set PreCompile Property To False For RSExecutionLog_Update.dtsx

Nov 9, 2007

I have installed the Report Execution Sample reports and with it came the RSExecutionLog_Update.dtsx and instructions to enable it in a SQL Agent job. I have followed the instructions, however, where do I set the PreCompile property to false? Is there a way to pull in a dtsx file into BIDS?

View 7 Replies View Related

Selecting Data From 3 Tables...

Feb 12, 2006

Plugging away at my little message board that I am working on, I have hit another SQL snag. I am trying to select a list of all posts in a thread. I have to get each post, the name of the poster (which comes from a different table - referenced by userid), as well as some profil information, from a 3rd table (also referenced by the userid).

View 2 Replies View Related

Selecting Only Specific Tables.

Jul 30, 2001

Hi all,
I have a query similar to this:
select "bcp databasename.."+name+"
from sysobjects where type = 'U'
order by name

What I need to know is that, I need to unselect some of the tables that starts with name cj_. I don't want the tables that starts with a name cj_.
Can someone help me on this.
Thank you

View 3 Replies View Related

Selecting From Multiple Tables

Apr 2, 2007

Hi all.

I'm trying to select from multiple table in one select statement but i'm having problems. Here is the code i'm trying:



if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[snow_ors_additionalInfoRead]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[snow_ors_additionalInfoRead]
GO

CREATE PROCEDURE dbo.snow_ors_additionalInfoRead
@Reference int
AS
SELECT
Account.CanTravel,
Account.SEEmployee,
Account.WorkHours,
Account.DrivingLicence,
Account.CriminalConvictions,
Account.CriminalConvictionsDetails1,
Account.CriminalConvictionsDate1,
Account.CriminalConvictionsDetails2,
Account.CriminalConvictionsDate2,
Account.CriminalConvictionsDetails3,
Account.CriminalConvictionsDate3,
Application.VacancyMonitoring,
Application.VacancyMonitoringDetails
FROM Account, Application
WHERE Account.Reference = @Reference AND
Application.reference = @Reference


GO


Any help at all would be great.

thanks people.

View 5 Replies View Related

Selecting On Multiple Tables

Aug 13, 2006

Hi there,I'm new to sql and thus I'm having problems with a specific query which Ihope you guys can help me with.Basicly I have a few tables which I'm trying to do a query on:Table groups contains information about specific groups e.g. "Windows" or"Unix".Table users contains information about specific users e.g. "a", "b" or "c".Table users_groups contain information about group relationship (a user canbe in multiple groups) e.g. (a, Windows), (b, Unix), (a, Unix).In this case user c is ungrouped.Now I'd like to find the users which does belong to group Windows and thosewho do not:select distinct username from users_groups where groupname = "Windows" orderby username asc;This works pretty well for finding users in the specific group. In this casethe result is a.However I'd like to get the opposite result (b and c) but I'm stuck.The problem is that I'd like a list of all users excluding those which arein "Windows"Here is a partial query:select distinct users.username from users left join users_groups onusers.username = users_groups.username where users_groups.username is nullorder by users.username asc;This only gives me those users who are not grouped at all. This mean thatuser b is not in those results.Please advise.Thanks in advance.-- Henrik

View 2 Replies View Related

Search Multiple Keywords Stored Procedure With Precompile

Jun 8, 2007

Hi,
I'm working on a new site with a big number of future concurrent visitors so performance is very important. We're working on a search function with which users can search for multiple keywords in a single table. My .NET application consults a SQL Server 2005 Stored Procedure to lookup the information. The stored procedure builds up a dynamic SQL string with which the table is queried.
 An example:
User searches for 'car airco'. Alle records with the words car and/or airco in specified columns should show up. This works. The query would be
SELECT Col1, Col2 FROM Table1 WHERE (Col1 LIKE '%car%' OR Col2 LIKE '%car%')OR (Col1 LIKE '%airco%' OR Col2 LIKE '%airco%')
As I mentioned before performance is a hot issue in this project. The problem with the stored procedure is that it can't be precompiled by SQL Server (dynamic SQL string). Is there a way to search for multiple keywords without losing the precompile behaviour of SQL Server Stored Procedures?
Kind regards,
ThaYoung1!

View 11 Replies View Related

Selecting Tables That Meet Certain Condition

Aug 5, 2004

Hi there,

I have the following script that selects tables from my database with the same column name and then I delete data that falls within a specified condition. However what I need to be able to do is just select these tables that meet the condition and then just delete the data because at the moment it's also returning tables that I don't need.

So I just want to use a cursor on a table list that meet the criteria:

1) have qid column name
2) qid >= 5000000 and qid < 1500000000 '


Example

declare @strqry varchar(1000)

declare dailyYear cursor
for SELECT TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE 'qid' = COLUMN_NAME order by table_name asc
open dailyYear
fetch next from dailyYear into @DelTable

while @@FETCH_STATUS = 0

begin

Set @strqry = 'Delete from '+@DelTable+' where qid >= 5000000 and qid < 1500000000 '

exec(@strqry)

fetch next from dailyYear into @DelTable

end

close dailyYear
deallocate dailyYear

Any help would be greatly appreciated!!

Thanks

S

View 3 Replies View Related

Selecting Tables That Meet Certain Criteria

Sep 2, 2004

Hi there,

Is there a quick way to select all the tables in the DB that don't have certain column in the table, so for example getting a list of all tables that don't have columns: A, B or C?

Thanks

S

View 4 Replies View Related

Selecting A Column From Multiple Tables

Feb 19, 2004

Hello,

Is it possible to get a list of rows from Multiple tables which have the same Column Name. I have 3 tables which comtain similar info
and I want to get a list of Names
the structure is

ID;Name;Address;Phone No.


I was thinking something along the lines of SELECT Name FROM TABLE1,TABLE2, TABLE3

But this does not work.

Is there a nice way of doing this with SQL or should I do code outside the SQL DB

View 4 Replies View Related

Selecting Procedure Structure From Sys Tables

Apr 18, 2008

Hi

Is there a way to select out the contents of sys.comments for an object (in this case, a procedure) so that it is nicely formatted?

The reason being, is that I want to update source control with all objects from the live environment (as it has become out of date.... long story). Currently, we have a single script for each object, split in directories for each object type. Anyway, I also want to wrap the objects in our standards (check for object existence, print out success/fail statements, etc).

Any suggestions to make the process more automated and less painfull? I don't want to have to script each object out, manually wrap it in standards and save as that would be perhaps the most tedious task in the world!!!!

Thanks!

Hearty head pats

View 2 Replies View Related

Difference Between Selecting Into And Temp Tables

Aug 27, 2007

Hello Tsql experts,
Can you please explain to me what the differnce is between selecting into a table and creating a new tabel and inerting in to it like so:

First way:
select names
into #ancestors
from names

and

second way

create table #names(
name varchar(10)
)

insert into #names (name) values(name)
select names from names

is it just personal choice or performance in one method is better than the other.Can you please explain.
Thanks

View 3 Replies View Related

Selecting From Two Tables With Different Data - Presenting In One Datalist

Oct 6, 2006

Hello!I have two tables:Pressreleases:| Pressrelease_ID | PressDate | FilePath | Title |PressLinks:| PressLink_ID | PressLinkDate | PressLinkUrl | PressText |I would like to select the the TOP 3 with the most recent dates from either Pressreleases or PressLinks, and present them in a DataList. How do I do that? Selecting from one table is no problem:SELECT TOP 3 Pressreleases.PressDate, Pressreleases.Filepath, PressReleases.Title FROM Pressreleases ORDER BY PressDate DESCHow do I select from both tables and rename the columns to Date, Path, Text so that I can use it in a Datalist?Thank you in advance!

View 4 Replies View Related

Selecting Columns From Different Tables Into A Single Table

Jan 5, 2008

I want to select columns from different tables into a single table...Not sure if a temp table is suited for this and if so how I should implement it (NOTE: this query will be executed many times by different users at the same time, so I'd rather avoid temp tables!)I have:TABLE1idfirstnamedescriptioncreatedateTABLE2idcarnamespecificationsimportdateNow, I want a resultset that has the columns (columns from other tables from which the values should be retreived are behind the desired columns):id  (TABLE1.id, TABLE2.id)title (TABLE1.firstname , TABLE2.carname)description (TABLE1.description , TABLE2.sepcifications)date (TABLE1.createdate , TABLE2.importdate)Thanks!

View 1 Replies View Related

Selecting From Two Tables, And Sorting By Common Field.

Feb 10, 2002

This is feeling very hard for me, but is surely very easy for many of you.
I have 2 Tables. "Events" and "Meals". Both have a columns named "EventDate" and "EventTime". I need to be able to compile a list of both and sort by event date and time. For example, a Meal @ 5:30 would place itself between a 4:00 Event, and a 6:30 Event.

PLEASE HELP ME!!!
Pending deadline doom :(

Thanks,
kaskenasy@publishingconcepts.com

View 1 Replies View Related

T-SQL (SS2K8) :: Selecting Distinct From Multiple Tables

Jun 4, 2014

I have 3 tables:

News:

bigint NewId
nvarchar NewTitle
datetime NewDate
nvarchar NewBrief
--------------------------
Category:

int CatId
nvarchar CatName
--------------------------
NewsRelCategory:

bigint Id
int CategoryIdFk
bigint NewsIdFk
--------------------------

I want to select NewId, NewDate and Distinct NewTitle

I tried this but NewTitle doesn't distinct:

SELECT
FROM dbo.Category INNER JOIN
NewsRelCategory ON dbo.Category.CatId = NewsRelCategory.NrcCategoryIdFk INNER JOIN
dbo.News ON NewsRelCategory.NrcNewsIdFk = dbo.News.NewId

View 9 Replies View Related

Selecting From Multiple Tables Avoiding Duplicates

Sep 6, 2006

Hi

I currently have two tables called Book and JournalPaper, both of which have a column called Publisher. Currently the data in the Publisher column is the Publisher name that is entered straight into either table and has been duplicated in many cases. To tidy this up I have created a new table called Publisher where each entry will have a unique ID.

I now want to remove the Publisher columns from Book and JournalPaper, replace it with an ID foreign key column and move the Publisher name data into the Publisher table. Is there a way I can do this without duplicating the data as some publishers appear several times on both tables?

Any help with this will be greatly appreciated as my limited SQL is not up to this particular challenge!!!
Thanks!

View 7 Replies View Related

A Deadlock When Selecting From A View With Replicated Tables.

Dec 15, 2006

I moved this from another forum because it seems more related to replication the longer I look into it!
The following code often dies on a deadlock. I have also seen "Spid x blocked by Spid x", where x is the spid of this connection. The view selects from several tables and some other views as well, and many of the underlying tables are being updated by replication. I have seen cases where the replication Insert proc participated in the deadlock, and the table being inserted into is joined twice in the view, suggesting that somehow this
causes the deadlock or makes a deadlock more likely?

SELECT * INTO dbo.tbl_stg_LoansMarketingFirstBorrower
FROM view_loans_marketing_first_borrower OPTION (MAXDOP 1)

I do not understand how selecting from a view can cause a deadlock with a
proc which does not reference tbl_stg_LoansMarketingFirstBorrower. Any
suggestions on how to diagnose this? I used (1204) to identify the proc and underlying table.

Thanks a bunch.

View 1 Replies View Related

Dynamically Selecting Tables In OLEDB Source

Mar 17, 2008

hello guys,

I have 10 tables, table1, table2, table3, table4.......table10. all these tables have different structure.
From each of these tables I want extract data and dump into flat file csv.

So i have OLEDB source and FlatFile Destination.

If i write seperate data flow task for each of the tables, then there is no issue.

But i want to use a single data flow task for all these tables. So for this, i use a variable @SQLStr . And i dynamically set the value of this variable to select * from table1, select * from table2.........selct * from table10.

So in the OLEDB source I select Data Access Mode as : SQL Command from variable. And I use @SQLStr for this varible.

For Destination, i dynamically generate the flat file for each of the table.

But this doesnt work, i get validation errors.

So, first can i use a single data flow task to dynamically change the source(different source tables) and destination. If so, what i am missing in the above process?

any help appriciated.

Thanks

View 8 Replies View Related

Creating A Left Join When Selecting From 3 Tables

Nov 30, 2007



Hi,
I was wanting to know if it is possible to create a left join when selecting from 3 seperate tables.

Select p.Project_name, p.project_id, cp.email_display_name, te.Mon
FROM tblProject p, tblCorpPerson cp, tblTimeEntry te
WHERE p.Project_ID = te.Project_ID
AND p.Person_ID = @PersonID
AND cp.Person_ID = p.Person_ID


I need to return all rows from tblProject, and any matching project_id's from tblTimeEntry.

Any ideas or suggestions?

Thanks

View 24 Replies View Related

How To Manage The Version Of Tables,View, And So On

Jan 12, 2004

Hi.
I am managing the SQL script files for constructing tables,views,and so on with Visual Source Sefe.

But once I run these scripts with a tool like Query Analyzer
for the database in SQL Server,
I do not know How to get the versions of the scripts which were installed now, with using Enterprise Manager or any tool associated with SQL Server2000.

Please tell me How are you managing and
know which version of scripts have been installed on database in SQL Server.

Sorry for my poor English.

Thanks for reading.

View 10 Replies View Related

Database Design Selecting Multiple Different Child Tables

Sep 19, 2000

I want to have a linking table say for example we call this a claim. Based on the claim number you need to relate to one of say 6 different types of claims. The types of claims related to their own individual parent table. (individual because each type of claim tracks completely different information) does anyone have an idea on how to set this up?

Sample Structure

table = Claim
Field 1 = ClaimTypeA_ID
Field 2 = ClaimTypeB_ID
Field 3 = ClaimTypeC_ID
Field 4 = ClaimTypeD_ID
Field 5 = ClaimTypeE_ID
Field 6 = ClaimTypeF_ID

The six field relate to the 6 different tables ID.

If I do this how do I store the data? put 0's in each of the claim types that are not used???

Any suggestions would be appreciated.

View 2 Replies View Related

SQL Server 2012 :: Selecting Records From Multiple Tables?

Jul 1, 2015

i have this query in a proc

declare @bu_id INT,
@CurCaptureDate DATETIME,
@user_id INT,
@col_name VARCHAR(100),
@sort_order VARCHAR(4),
@CityPair_ID INT=NULL,

[code]....

where @reasons and @departure_code can be multiple.

View 2 Replies View Related

Selecting Rows From Two Tables With JOIN And Ordering Problem

Dec 8, 2007



Hi,

First the environment: two tables A and B.

Table A: ID (unique-identifier)
Table B: ID_A (unique-identifier to A.ID, relation)

DTime (datetime)

Rows (id1 and id2 are Id examples):
A: id1
id2
B: id1 and 12:00:00 (date not important)
id1 and 13:00:00
id2 and 12:00:00

Example:
SELECT A.ID, B.DTIME
FROM A
LEFT JOIN B ON B.ID_A = A.ID
WHERE B.DTime < '14:00:00'
ORDER BY NEWID()

When I run this, I get the three rows of table B. But what I want is to get each table A row once, and get the nearest datetime of WHERE expression from the relation of table B.
So, the result must been two rows, id1 and id2, and id1 with '13:00:00' row because this is the nearest value of '14:00:00'.

How can I do this? DISTINCT trying by A.ID of SELECT, but doesn't work. Also ORDER BY B.DTime will work, but not random by NEWID() anymore.

Thank you.

View 3 Replies View Related

Microsoft SQL Server 2005. Selecting Multple Columns From Multiple Tables

Mar 23, 2008

Im just curious how i would take multiple columns from multiple tables.... would it be something like this ???
table: Products COLUMNS ProductName, ProductID
table: Categorys COLUMNS CategoryName, CategoryID,ProductID
SELECT Products.ProductName, Categorys.CategoryName,Products.ProductID,Categorys.CategoryID,Categorys.ProductID
FROM Categorys, Tables
WHERE Products.ProductID = Categorys.ProductID
 

View 1 Replies View Related

SSIS Transfer Object Task Error - Selecting Tables To Copy

May 4, 2007

I'm using a Business Intelligence project to copy stored procedures and tables from one database to another across servers. I'm having trouble copying tables or stored procedures using the Management.SMO.Transfer class.



I tried copying stored procedure with the property transfer.CopyAllStoredProcedures = true. This didn't work. As a workaround, I used the StringCollection property and executed every string as sql.



Now I'm having trouble copying tables. I don't want to copy all the tables in the database. How do I go about selecting what tables to copy. I tried using ObjectList property and provided the names of the tables in an ArrayList. I get the error

"Transfer cannot process System.String. You need to pass an instance class object."



How can I pass an "instance class object" for something that's in the database? The ScriptTransfer method fails so I can't even see the script that is being generated. There is virtually no documentation for this class.



Any help will be appreciated.



Transfer transfer = new Transfer();

transfer.Database = sourceDB;

transfer.DestinationDatabase = DestinationDatabase;

transfer.DestinationServer = DestinationServer;

transfer.DropDestinationObjectsFirst = false;

transfer.CopyData = true;

transfer.CopySchema = true;

transfer.CopyAllObjects = false;

transfer.CopyAllTables = false;

transfer.CopyAllStoredProcedures = false;

transfer.Options.WithDependencies = false;

GetTablesToBeCopied();

transfer.ObjectList = tablesList;



transfer.Options.FileName = "C:\TransferScriptTables.sql";



StringCollection coll = transfer.ScriptTransfer();

View 3 Replies View Related







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