Declaring A Composite Attribute In Sql Server

Mar 28, 2008

hi guys i have a question ..... i would like to know how to delare a compound attribute in this case would be Birthinfo onto a sql server script. from this diagram . thanyou guyt in advance and any suggestion or help would be greatly appreciated .....

View 10 Replies


ADVERTISEMENT

SQL Server 2014 :: How To Check Existing Attribute In Insert Attribute In XML Field

Aug 10, 2015

I want to insert attibute att1 in field F1 . Value for this attribute is content of another field in this table (F2).

My query :
update MyTable
set F1.modify
('insert attribute att1 {sql:column("F2")} into (/ROOT/Node1)[1]')
Where F1 Is not null

But I get this error :
XML well-formedness check: Duplicate attribute 'att1'. Rewrite your XQuery so it returns well-formed XML.

How do I check the douplicate attribute ?

View 0 Replies View Related

Composite Primary Keys Versus Composite Unique Indexes

Feb 20, 2007

Hello,

I have a table which has a composite primary key consisting of four columns, one of them being a datetime called Day.

The nice thing afaik with this composite key is that it prevents duplicate entries in the table for any given day. But the problem is probably two-fold

1. multiple columns need to be used for joins and I think this might degrade performance?
2. in client applications such as asp.net these primary keys must be sent in the query string and the query string becomes long and a little bit unmanagable.

A possible solutions I'm thinking of is dropping the existing primary key and creating a new identity column and a composite unique index on the columns from the existing composite key.

I would like to have some tips, recommendations and alternatives for what I should do in this case.

View 1 Replies View Related

Measure Group Attribute Key Column Does Not Match Source Attribute

May 16, 2008



HI,


I had to change the key columns of a dimension attribute to fix an error. I did this in BIDS. The change was from a single key column to a composite key column. Now I am getting these error when I process the cube:

Measure group attribute key column x does not match source attribute ..

I looked at the cube XMLA definition under mesaure groups and it still shows a single key column with inherited binding. However, the BIDS does not give me an option correct this in any way. I have had to do this once before and the only option seems to be removing the dimension from the cube and add it back in. But that is very error prone since I lose any specific settings at the cube dimension level not to mention aggregations no longer include the dimension, etc.

Not seeing an alternative, I went through each measure group (I have 7) and changed the key columns manually in the XMLA and saved the cube. This worked, but I don't understand why BIDS automatically doesn't do it.

Is this a flaw in the BIDS or I should be missing something.

thanks
MJ

View 3 Replies View Related

SQL Server 2012 :: Behavior Of Brackets In Select Clause When Declaring Variables?

Oct 11, 2014

I can't understand why I get 2 different results on running with a Bracket I get 'NULL' and without a bracket I get the declared variable value which is 'Noname'

Below is Query 1:

Declare @testvar char(20)
Set @testvar = 'noname'
Select @testvar= pub_name
FROM publishers
WHERE pub_id= '999'
Select @testvar

Out put of this query is 'Noname'

BUT when I type the same query in the following manner I get Null-------Please note that the only difference between this query below is I used brackets and Select in the Select@testvar statement

Declare @testvar char(20)
Set @testvar = 'noname'
Select @testvar=(Select pub_name
FROM publishers
WHERE pub_id= '999')
Select @testvar

View 4 Replies View Related

Add Composite Key Thru SQL Server GUI?

May 29, 2008

How can I create a composite key (two primary keys for one table) using SQL Server 2005 Management Studio? Can I do it using the GUI, or do I have to write SQL code to do this?  Thanks

View 7 Replies View Related

Composite Primary Key Using MS SQL Server Enterprise Manager Interface

Jan 24, 2008

I want to created composite primary key using MS SQL Server Enterprise Manager  Interface. I want to use Interface instead of writing it in CREATE Table statement. I was able to create foreign key using this Interface using the "Manage Relationship" option. But cannot find how to add primary key consisting of two fields. Any help regarding this is highly appreciated. 

View 2 Replies View Related

SQL Server 2014 :: Composite PK / Surrogate And Sequence Identity

Jun 21, 2015

So, I have some questions about best practice in SQL Server.

1.) I have PK like this (company TINYINT, store TINYINT, action TINYINT, invoice INT, sn SMALLINT). I know JOINS will work faster with surrogate key but I have only couple of JOINS on that table. I use members of PK in WHERE clause mainly, alone and combined for reporting purpose. Is it always better to have surrogate key because they don't have any meaning and context of data laying in current PK.

2.) In my PK from above I have two candidates for using Sequence object. Invoice start with 1 for every (company,store,action) combination. Sn start with 1 for every (company,store,action,invoice) combination. I would like to know can I implement Sequence object here knowing that Sequence don't support PARTITION BY in OVER clause. From what I red it cannot be done via Sequence but I have to ask.Here is data sample for this PK

company store action invoice sn
----------- ----------- ----------- ----------- -----------
1 1 1 2017 1
1 1 1 2018 1
1 1 1 2019 1
1 1 1 2019 2
1 1 1 2019 3
1 1 2 1 1
1 1 2 2 1
1 1 2 2 2
1 1 2 2 3
1 1 2 3 1
1 1 2 3 2
1 1 2 3 3
1 1 2 3 4
1 1 2 3 5

View 7 Replies View Related

Auto Incrent Attribute SQL Server 2000 Vs Sql Server 2005

May 14, 2007

In our application we use the ADODB.Recordset.



In SQL server 2000



If there was a view that joined 2 tables and I accessed the view the 2 ID fields in the view would still have the AutoIncrement attribute still set to true so that I knew those were Identity fields.



In SQL server 2005

I dont' know why but if you reference a View that has Identiy AutoInc fields in ADO it doesn't keep those properties.



Also for whatever reason we Set the ID field to 0 to let ourselves know its a new Record. SQL 2000 let it happen and assumed it to be null where as By Setting the ID to 0 in SQL 2005 causes it to blow up on me.



Is there some sort of setting in SQL that can make SQL 2005 work like SQL 2000 in these two instances...

View 1 Replies View Related

Declaring A Variable

Feb 6, 2007

I have the below code and have two questions.
1) With the current delete control, the system breaks stating: System.Data.SqlClient.SqlException: Must declare the variable '@doc_area_id'.
2) Currently the Area Type column brings up all records set as '1'. It shows the value '1' for each column. I would like it to state the text 'Shared Area' under the Area Type Column where it is set as '1' in the database.
 
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlManageSharedArea" CellPadding="4" ForeColor="#333333" BorderStyle="Solid" BorderColor="Black" BorderWidth="1px">
<Columns>
<asp:BoundField DataField="doc_area_name" HeaderText="Area Name" SortExpression="doc_area_name" />
<asp:BoundField DataField="doc_area_type" HeaderText="Area Type" SortExpression="doc_area_type" />
<asp:CheckBoxField DataField="doc_area_default" HeaderText="Default" SortExpression="doc_area_default" />
<asp:HyperLinkField HeaderText="Edit" NavigateUrl="~/Admin/EditDocumentArea.aspx" Text="Edit" ><ItemStyle ForeColor="Black" /><ControlStyle ForeColor="Black" /></asp:HyperLinkField>
<asp:CommandField ShowDeleteButton="True" HeaderText="Delete" ><ItemStyle ForeColor="Black" /><ControlStyle ForeColor="Black" /></asp:CommandField>
</Columns>
 
<FooterStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#E3EAEB" />
<EditRowStyle BackColor="#7C6F57" />
<SelectedRowStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#666666" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
<asp:SqlDataSource ID="SqlManageSharedArea" runat="server" ConnectionString="<%$ ConnectionStrings:CPS_docshareConnectionString %>"
SelectCommand="SELECT [doc_area_id], [doc_area_name], [doc_area_type], [doc_area_default] FROM [document_area] WHERE [doc_area_type] = 1"
DeleteCommand="DELETE FROM document_area WHERE [doc_area_id] = @doc_area_id">
</asp:SqlDataSource>
 
Any help would be greatful!

View 1 Replies View Related

Declaring Variables In SQL CE

Dec 3, 2007



Hi, i am trying to run the following query in SQL Server CE:


SELECT @V1=CAST(SCOPE_IDENTITY() AS bigint)

SELECT @V1

INSERT INTO [ResourceItem] ([PackageId],[ResourceXml]) VALUES (@V1,@V6)

It gives me an error "near SELECT".

I replaced SCOPE_IDENTITY() with @@IDENTITY but it didnt help.

I think it is something related to storing values such as @foo = something.

How can i declare variables in SQL CE?

Pls help
Thank You

View 1 Replies View Related

Declaring A Variable

May 9, 2007

I am learning T-SQL syntax and I am very familiar with it, however how would I do the following:



We have a table that actually has a column that contains SQL statements. I want to build a SQL statement in Reporting Services that is going to take that column to build a "dynamic" SQL statment and then I will use the exec sp_executesql statement.



Do I need to declare a parameter, or in SQL is there such thing as a variable?



So if I have:



DECLARE @sql nvarchar(4000)

SELECT AdHocSQL from TheTable

SET @sql=AdHocSQL



Would this work? Is this syntatically correct? Or should I be doing this some other way?



The report is sort of a summary report that has about 250 different items and each item has different data to get from different tables.



Thanks for the information.

View 3 Replies View Related

Select Into / Linked Server / Missing IDENTITY Attribute

Nov 28, 2005

Greetings,If I use a "select into" to clone a table, all attributes are createdcorrectly, however, if I use the same statement across a linked server,my identity column loses its IDENTITY specification.Is this a known issue or basic functionality of using "select into"with linked servers?Kurt

View 2 Replies View Related

The Value Of The PublicKey Attribute In SQL Server 2005 Express Edition

Dec 7, 2007

Hi all

I make a Prerequisites Setup to my application to install SQL Server 2005 Express.

I found that the included file (C:Program FilesMicrosoft Visual Studio 8SDKv2.0BootStrapperPackagesSqlExpressenSQLEXPR32.EXE) version is 9.0.1399.6
there is an xml file with the name package.xml which contain a PublicKey


<PackageFile Name="sqlexpr32.exe" HomeSite="SqlExprExe" PublicKey="3082010A0282010100CF38F7E1A4275EF8F0CCAACEFB3ADE3B6231460546BBF5B6051AD3B3ACC29F0F4C670828C44310F53B75797F6A91F4D633C861BFFA9190007AF0791D5D6870F690B29877B5031D2F9B9D9B75892F4A01417C9E7CCB87439BF49674999E98C1CF40575816F6C0D59216E52485718F9949ED557C65C91F380023C53EAB11D6296CC69EA0705B7DD537D4677720C306CE85F84E3480A035C41C53320157EFB128BD6C01E3AD40BC80A90949DB36E337F41D49AA2AA76BD019D3CC8E9DD686467A134AD64519A553B3E2782F2E35976B4CC6E81AB0D3D1249069ABCEFC696E3E4CFB024162DC07985D7E5CA74C27316B564CE198D8E0D11D718D3D2AC07F714DFFCF0203010001" />
<PackageFile Name="eula.txt" />
</PackageFiles>



later I download a new version 9.0.3042.0. from internet

then I replace the the old file with the new one and after rebuilding Prerequisites Setup, I got this error

Prerequisites Setup
Warning 1 The value of the 'PublicKey' attribute in 'SQL Server 2005 Express Edition' does not match that of file 'C:Program FilesMicrosoft Visual Studio 8SDKv2.0BootstrapperPackagesSqlExpressensqlexpr32.exe'. C: estingPrerequisites SetupPrerequisites Setup.vdproj Prerequisites Setup

When I install my application, Prerequisites fail to install

How can I got out from this bug and keep the new version ??



Khayralla

View 3 Replies View Related

T-SQL (SS2K8) :: Declaring Value To A Variable

Oct 22, 2014

I have a job that runs in sql 2008r2 that declares a value to a variable. This variable is passed to a function. I need to include more values in and am unsure how to do this. @InCo could be 4,5,6,or 7.

See below.

Declare @ReportDateIN [datetime]=GETDATE(),
@InCo varchar(max) = 4,
@OutCo varchar(max) = 8,
@IncludeDetailIN [int]= 1,
@IncludeReleasedIN [int]= 1,
@IncludeHoldingIN [int]= 1

[Code] .....

View 2 Replies View Related

Problem Declaring Cursors

Feb 26, 2008

I am still having problems with my cursors. I am receiving the following errors when I execute my Stored Procedure:
Msg 16915, Level 16, State 1, Procedure BatchNonMons2, Line 27
A cursor with the name 'MyCursor' already exists.
Msg 16905, Level 16, State 1, Procedure BatchNonMons2, Line 31
The cursor is already open.
Msg 16915, Level 16, State 1, Procedure BatchNonMons2, Line 37
A cursor with the name 'vwCursor' already exists.
Msg 16905, Level 16, State 1, Procedure BatchNonMons2, Line 38
The cursor is already open.

Here is the code causing the problem:

CREATE PROCEDURE [dbo].[BatchNonMons2] AS

BEGIN
/* Create a new hdr each time the Sys/Prin changes in the Dtl rec */

/* Define every field you want to output */
DECLARE @Batch_Type int
DECLARE @Batch_Num int
DECLARE @Sys varchar (4)
DECLARE @Prin varchar (4)
DECLARE @Account_Num varchar(16)
DECLARE @Tran_Code varchar(3)
DECLARE @Clerk_Code varchar(3)
DECLARE @Memo_Text varchar(57)
DECLARE @SubTrans varchar(2)
DECLARE @SubTrans_2 varchar(1)
DECLARE @Terminal_Id varchar(3)
DECLARE @Op_Code varchar(2)
DECLARE @Last4 varchar(6)

DECLARE @vwSys varchar (4)
DECLARE @vwPrin varchar (4)

/* Define Cursor */
DECLARE MyCursor cursor
For SELECT Batch_Type, Batch_Number, Sys, Prin, Account_Number, Transaction_Code,
Clerk_Code, Memo_Text, SubTrans, SubTrans2, Term_id, Op_Code
FROM dbo.tblNonMon_Daily_Trans

Open MyCursor; /*Works like an ARRAY*//*holds current record */
FETCH NEXT FROM MyCursor INTO @Batch_Type, @Batch_Num, @Sys, @Prin, @Account_Num,
@Tran_Code, @Clerk_Code, @Memo_Text, @SubTrans, @SubTrans_2,
@Terminal_Id, @Op_Code

Declare vwCursor cursor
FOR SELECT Sys, Prin FROM dbo.vwNonMonSysPrins
open vwCursor;
FETCH next FROM vwcursor INTO @vwSys, @vwPrin

/* When @@Fetch_Status = 1 EOF has been reached */
WHILE @@Fetch_Status = 0
BEGIN
WHILE @vwSys = @Sys
BEGIN

Any input would be appreciated.
Thanx,

View 2 Replies View Related

Declaring USER_NAME() As SQL Variable

Jul 20, 2005

Hi,I have a User-defined function "Concatenate_NoteTexts" which I use in aquery (SQL Server 2000). On my local development machine it is called likethis:SELECTdbo.Concatenate_NoteTexts(Introducers.IntroducerID ) as NoteTextsFROM tblIntroducersI want to run the same code on a shared remote server where I am user "JON"instead of "dbo". I don't want to hard-code the User Name into the SQL, butwhen I tried to put the user name into a variable as here:DECLARE @USER_NAME VarChar(30)SET @USER_NAME = USER_NAME()SELECT@USER_NAME.Concatenate_NoteTexts(Introducers.Intro ducerID) as NoteTextsFROM tblIntroducersI get the following error:Server: Msg 170, Level 15, State 1, Line 4Line 4: Incorrect syntax near '.'Any advice?TIA,JONPS First posted earlier today to AspMessageBoard - no answers yet.http://www.aspmessageboard.com/foru...=626289&F=21&P=1

View 5 Replies View Related

UPDATE CURSOR - Declaring And Use.

Jul 20, 2005

I need to do something relatively simple…I need to update a table using a cursor. (I may have to create astored procedure for doing this…)I need to declare an update cursor, fetch the cursor and update thedata (and presumably close the cursor and de-allocate it…The update query is as follows… Would anyone there know how todeclare the cursor for update and use it?UPDATE ASET A.Field1 =(SELECT B.Field1FROM B INNER JOIN A ON A.id = B.id)I need to know how to declare the cursor and fetch it.Can anyone give me an example of the code I need for the SQL Server?Thanks!

View 4 Replies View Related

Problem Declaring Cursors

May 2, 2008



I am entering the following:


declare @iAbsenceCategoryID int;

declare @dEntitlementRenewal datetime;

declare @iAbsenceUserID int;

declare @iMinutesUsed int;

declare @iMinutesNotUsed int;

declare @iEntitlement int;



declare cr cursor forward_only

for

select iabsencecategoryid,

dentitlementrenewal

from TblTAAbsenceCategories

open cr;

fetch next from cr into

@iAbsenceCategoryID, @dEntitlementRenewal

WHILE @@FETCH_STATUS = 0

BEGIN

fetch next from cr into

@iAbsenceCategoryID, @dEntitlementRenewal

if (DATEPART("Month",(GETDATE()))) = (DATEPART("Month", (@dEntitlementRenewal))) and (DATEPART("Day",(GETDATE()))) = (DATEPART("Day",(GETDATE())))

declare usercr cursor FORWARD_ONLY for

select iAbsenceUserID from TblTAAbsenceUsers

where iAbsenceCategoryID = @iAbsenceCategoryID

fetch next from usercr into

@iAbsenceUserID

WHILE @@FETCH_STATUS = 0

BEGIN

fetch next from usercr into

@iAbsenceUserID

select @iMinutesUsed = sum(iMinutesRequested)

from tblTAAbsenceHolidays

where iAbsenceUserID = 13 and iAbsenceActionID != 2

select @iEntitlement = iHolidayEntitlement from TblTAAbsenceUsers where iAbsenceUserID = 13;

set @iMinutesNotUsed = (@iEntitlement) - (@iMinutesUsed);

update TblTAAbsenceUsers

set iMinutesNotUsed = @iMinutesNotUsed

where iAbsenceUserID = @iAbsenceUserID;

END;

END;

close usercr;

close cr;

DEALLOCATE usercr;

DEALLOCATE cr;


and i get the following error message:


Msg 16916, Level 16, State 1, Line 26

A cursor with the name 'usercr' does not exist.

Msg 16916, Level 16, State 1, Line 57

A cursor with the name 'usercr' does not exist.

Msg 16916, Level 16, State 1, Line 60

A cursor with the name 'usercr' does not exist.


I cant see why its not allowing me to declare the usercr cursor, can anyone tell me where im going wrong? thanks!



View 13 Replies View Related

Declaring Public Variables

May 30, 2007

I am trying to use variables that are declared in a report that I am created in SSRS 2005.



For example:



Declare @Month as int



Set @Month = case when month(getdate()) = 1 then 9 else month(getdate()) - 3



When I do this, I get an error message, 'The declare cursor SQL construct or statement is not supported.'



So how do i properly declare a variable that can be used through all my dataset?



Thanks, Iris

View 10 Replies View Related

Declaring A Sql Query To A Variable...

Sep 10, 2007

Hello all! After I declar a variable how would I set the result of a sql query to the variable so i can utilize it further in my stored procedure?

-Thanks,
Rich

View 5 Replies View Related

DirectReports-- How To Extract This Active Directory Attribute Value In MS SQL 2000 Server

Nov 22, 2007

hi,
I am trying to run a query analyzer via query against ad. Below is the error I am getting. How to extract the directReports attribute values from windows 2003 active directory


SELECT * FROM OPENQUERY( ADSI,
'SELECT directReports FROM ''LDAP://192.168.1.24/dc=xxx,dc=com''
WHERE objectCategory = ''Person'' AND objectClass = ''user'' order by name')

Err: Server: Msg 7346, Level 16, State 2, Line 1
Could not get the data of the row from the OLE DB provider 'ADsDSOObject'. Could not convert the data value due
to reasons other than sign mismatch or overflow.

plz do the need full

thanks & regards
venkey

View 3 Replies View Related

Declaring A Tablename In A Sproc As A Parameter

Oct 2, 2006

HiCan someone please shed some light as to how this can be done. With the below sql statement as you can see I don't want to declare the table name but would rather the table name be passed in via a parameter. I've tried declaring it as a varchar parameter but it doesnt seem to like it if I don't add a valid table name. Any ideas how this can be done. Thanks.select * from @tableName where condition = condition

View 5 Replies View Related

Trouble Declaring Hierarchy Using FOR XML EXPLICIT

Jan 31, 2006

I'm attempting to use FOR XML EXPLICIT in SQLServer to adhere to the following template:
<group>
<sourceid>
<source></source>
<id></id>
<grouptype>
<scheme></scheme>
<typevalue></typevalue>
</grouptype>
<description>
<short></short>
<long></long>
</descrption>
</group>

Here's my code:
SELECT 1 as Tag,
NULL as Parent,
@p_school_desc as [sourceid!1!source!element],
@p_term_code as [sourceid!1!id!element],
NULL as [grouptype!1!scheme!element],
NULL as [grouptype!1!typevalue!element],
NULL as [description!1!short!element],
NULL as [description!1!long!element]
FROM course_main AS sourceid
where sourceid.course_id = @p_course
UNION
SELECT 1 as Tag,
NULL as Parent,
NULL,
NULL,
@p_destination as [grouptype!2!scheme!element],
'Term' as [grouptype!2!typevalue!element],
NULL,
NULL
FROM course_main AS grouptype
where grouptype.course_id = @p_course
UNION
SELECT 1 as Tag,
NULL as Parent,
NULL,
NULL,
NULL,
NULL,
@p_term_code as [description!2!short!element],
@p_term_description as [description!2!long!element]
FROM course_main AS grouptype
where grouptype.course_id = @p_course
FOR XML EXPLICIT

I'm receiving the foollowing error:
Server: Msg 6812, Level 16, State 1, Line 14
XML tag ID 1 that was originally declared as 'sourceid' is being redeclared as 'grouptype'.
Is there anyway to have two different child notes off of the same parent node?

Thanks in advance!

View 1 Replies View Related

Trouble Declaring Hierarchy Using FOR XML EXPLICIT

Jan 31, 2006

I'm attempting to use FOR XML EXPLICIT in SQLServer to adhere to the following template:
<group>
<sourceid>
<source></source>
<id></id>
<grouptype>
<scheme></scheme>
<typevalue></typevalue>
</grouptype>
<description>
<short></short>
<long></long>
</descrption>
</group>

Here's my code:
SELECT 1 as Tag,
NULL as Parent,
@p_school_descas [sourceid!1!source!element],
@p_term_code as [sourceid!1!id!element],
NULLas [grouptype!1!scheme!element],
NULLas [grouptype!1!typevalue!element],
NULLas [description!1!short!element],
NULLas [description!1!long!element]
FROM course_main AS sourceid
where sourceid.course_id = @p_course
UNION
SELECT 1 as Tag,
NULL as Parent,
NULL,
NULL,
@p_destinationas [grouptype!2!scheme!element],
'Term' as [grouptype!2!typevalue!element],
NULL,
NULL
FROM course_main AS grouptype
where grouptype.course_id = @p_course
UNION
SELECT 1 as Tag,
NULL as Parent,
NULL,
NULL,
NULL,
NULL,
@p_term_codeas [description!2!short!element],
@p_term_descriptionas [description!2!long!element]
FROM course_main AS grouptype
where grouptype.course_id = @p_course
FOR XML EXPLICIT

I'm receiving the foollowing error:Server: Msg 6812, Level 16, State 1, Line 14
XML tag ID 1 that was originally declared as 'sourceid' is being redeclared as 'grouptype'.

Is there anyway to have two different child notes off of the same parent node?

Thanks in advance!

View 1 Replies View Related

Declaring A Cursor On A Temporary Table

Jan 27, 2004

How do I declare a cursor on a table like #TempPerson
when thhs table is only created when I do :

Select Name, Age Into #TempPerson From Person

View 7 Replies View Related

How To Call A Procedure While Declaring A Cursor

Apr 30, 2004

HI,
WHILE DECLARING A CURSOR TO SELECT RECORDS FROM A TABLE WE NORMALLY WRITE :-

DECLARE CUR_NAME CURSOR
FOR SELECT * FROM CLEANCUSTOMER

BUT SAY, IF I HAVE WRITTEN A SIMPLE PROCEDURE CALLED AS MY_PROC :-

CREATE PROCEDURE MY_PROC
AS
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CLEANCUSTOMER A
INNER JOIN TRCUSTOMERPREFERENCE03JULY B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
ORDER BY B.INTPREFERENCEID

WHICH IS RUNNING FINE AND GIVING ME THE REQUIRED DATA WHILE EXECUTING THE PROCEDURE :-

EXEC MY_PROC

BUT IF I WANT TO CALL THIS PROCEDURE MY_PROC WHILE DECLARING A CURSOR :-

I AM USING :-

DECLARE CHK_CUR CURSOR
FOR SELECT * FROM MY_PROC

WHICH IS GIVING AN ERROR "Invalid object name 'MY_PROC'."


AND IF I USE :-

DECLARE CHK_CUR CURSOR
FOR EXEC MY_PROC

WHICH IS GIVING AN ERROR "Incorrect syntax near the keyword 'EXEC'".


AND IF I USE :-

DECLARE CHK_CUR CURSOR
FOR CALL MY_PROC

WHICH IS GIVING AN ERROR "Incorrect syntax near 'CALL'. "

IS THERE ANY WAY BY WHICH I CAN FETCH RECORDS FROM THE STORED PROCEDURE?
HOW DO I DECLARE THE PROCEDURE WHILE WRITING THE CURSOR
PLS HELP.

I NEED THIS URGENTLY, I HAVE TO USE THE CURSOR TO FETCH THE RECORDS FROM THE SP,THAT'S HOW THEY WANT IT.I CAN'T HELP IT AND I DON'T KNOW HOW

THANKS

View 14 Replies View Related

Setting/declaring Variables Plus Concatenation

Mar 12, 2008

So I know my code is pretty far off on the variables, but I couldn't find info on how to do this anywhere. Do I need to use sub selects to set the variables? Or something other than variables?


DECLARE@Prefix VARCHAR(10),
@First_Name VARCHAR(50),
@Middle_Name VARCHAR(50),
@Last_Name VARCHAR(50),
@Suffix VARCHAR (10),
@Title VARCHAR (50);

SET@Prefix = ind_prf_code;
SET@First_Name = ind_first_name;
SET@Middle_Name = ind_mid_name;
SET@Last_Name = ind_last_name;
SET@Suffix = ind_sfx_code;
SET@Title = cst_title_dn;

select coalesce((@Prefix + ' '),'') +
ltrim(rtrim(@First_Name)) +
case when ltrim(rtrim(@Middle_Name)) = '' then ' ' else (' ' +
ltrim(rtrim(@Middle_Name)) + ' ') end +
ltrim(rtrim(@Last_Name)) +
coalesce(' ' + @Suffix, '') +
char(13) +
ltrim(rtrim(@Title)) +
char(13) as label

from co_individual (nolock)
join dbo.co_customer (nolock)
on cst_key = ind_cst_key

Thanks!

View 1 Replies View Related

Declaring DataTable In Script Causes Error?

Nov 7, 2006



As discovered when trying to create a custom transformation for this question (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=899895&SiteID=1&mode=1) I tried creating an object of type DataTable and I get this error

dim myTable as DataTable

Reference required to assembly 'System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' containing the implemented interface 'System.Xml.Serialization.IXmlSerializable'. Add one to your project. dts://Scripts/ScriptComponent_3851bc3613714d2d904d79bc006234f9/ScriptMain 19 24 ScriptComponent_3851bc3613714d2d904d79bc006234f9


Even if I add "Imports System.XML" I get the same error. Isn't DataTable part of System.Data (which is imported by default in Script Components)? Everyone else get the same behavior?

View 3 Replies View Related

Transact SQL :: Declaring And Setting Variables

Jul 23, 2015

How do I set a variable to represent all of the data. For example using SELECT * will pull all of the data. Is there any symbol or way to declare and set a variable to do the same exact concept. In my query I have set many different variables which are used later on in my where clause but depending on what information I'm pulling from the data I don't wan the variable to have a specific value and instead pull all the data.

View 2 Replies View Related

Declaring And Setting Vars In A Stored Procedure

Feb 2, 2008

Hello,

I have a stored procedure that prompts the user for a year and
a month. Based on the month selected, I need to determine
the number of days in that month.

I have tried declaring variables to use to calculate number
of days in month and a counter, but they are not
working. When I try to run this it says I have to prompt user for
these as well. How do I declare and set a variable inside
a stored procedure.

Sample of my code is below...

REATE PROCEDURE crm_contact_frequency_report

@TheYear varchar(4),
@TheMonth integer,

@MyCount integer,
@NumDays integer


AS









SELECT

/* EMAILS (B) */
(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Email B ON A.subject = B.subject
WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)
AND (B.directioncode = 1)
) AS Total_EmailOutgoing,

(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Email B ON A.subject = B.subject
WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)
AND (B.directioncode = 0)
) AS Total_EmailImconing,

(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Email B ON A.subject = B.subject
WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)
AND (B.directioncode IS NULL)
) AS Total_EmailNotListed,

(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Email B ON A.subject = B.subject
WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)
) AS Total_All_Emails,


/* PHONE CALLS (C) */
(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN PhoneCall C ON A.subject = C.subject
WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)
AND (C.directioncode = 1)
) AS Total_CallOutgoing,

(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN PhoneCall C ON A.subject = C.subject
WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)
AND (C.directioncode = 0)
) AS Total_CallIncoming,

(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN PhoneCall C ON A.subject = C.subject
WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)
AND (C.directioncode IS NULL)
) AS Total_CallNotListed,

(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN PhoneCall C ON A.subject = C.subject
WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)
) AS Total_All_Calls,

/* FAXES (D) */
(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Fax D ON A.subject = D.subject
WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)
AND (D.directioncode = 1)
) AS Total_FaxOutgoing,

(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Fax D ON A.subject = D.subject
WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)
AND (D.directioncode = 0)
) AS Total_FaxIncoming,

(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Fax D ON A.subject = D.subject
WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)
AND (D.directioncode IS NULL)
) AS Total_FaxNotListed,

(SELECT COUNT(*) FROM dbo.CampaignResponse A
INNER JOIN Fax D ON A.subject = D.subject
WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)
) AS Total_All_Faxes

FROM CampaignResponse A
GO

View 2 Replies View Related

Multi Statement Table-valued UDF Without Declaring Columns?

May 6, 2007

Hey
I have created a multi-statement
table valued function

alter 
function
fn_x(@x int)returns
@tbl table
(
          position int identity primary key,          i
int)
as
begin         
insert
into
@tbl values
(@x)          insert
into
@tbl values
(@x)          insert
into
@tbl values
(@x)          insert
into
@tbl values
(@x)          returnend
 



Is it possible skipping the
definition of the table columns (the light blue
part)?I need to return a different
structure based on a parameter.Dropping those lines throws an
error "incorrect syntax near 'as'" 
The other solution is declaring
each udf separately as one statement udf.Thanks 

View 2 Replies View Related

Transact SQL :: Declaring Cursor Causing Select Statements Included Within A Function Cannot Return Data To Client?

Sep 29, 2015

I cannot find the problem with this function.

ALTER function [Event].[DetermineTrackTime](@TrialID varchar(max)) returns int as
begin
Declare @ret int;
Declare @EnterVolumeTime int;
Declare @ExitVolumeTime int;
Declare @StartTrackTime int;

[code]....

I am getting the following error on line 75:

Select statements included within a function cannot return data to a client.

This is happening when declaring TrackUpdateCursor

The compiler has no problem with the VolumeTimesCursor. What is causing this and what can I do about it?

View 20 Replies View Related







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