Declaring USER_NAME() As SQL Variable

Jul 20, 2005

Hi,

I have a User-defined function "Concatenate_NoteTexts" which I use in a
query (SQL Server 2000). On my local development machine it is called like
this:

SELECT
dbo.Concatenate_NoteTexts(Introducers.IntroducerID ) as NoteTexts
FROM tblIntroducers

I 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, but
when 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 NoteTexts
FROM tblIntroducers

I get the following error:

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '.'

Any advice?

TIA,

JON


PS First posted earlier today to AspMessageBoard - no answers yet.
http://www.aspmessageboard.com/foru...=626289&F=21&P=
1

View 5 Replies


ADVERTISEMENT

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 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

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

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

Declaring A Table Variable Within A Select Table Joined To Other Select Tables In Query

Oct 15, 2007

Hello,

I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.

I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.

This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.

So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?

Thanks in advance,
Andy



Select * from

(

declare @years table (years int);

insert into @years

select

CASE

WHEN month(getdate()) in (1) THEN year(getdate())-1

WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())

END

select

u.fullname

, sum(tx.Dm_Time) LastMonthBillhours

, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

Month(tx.Dm_Date) = Month(getdate())-1

and

year(dm_date) = (select years from @years)

and tx.dm_billable = 1

group by u.fullname

) as A

left outer join

(select

u.FullName

, sum(tx.Dm_Time) Billhours

, ((sum(tx.Dm_Time))

/

((day(getdate()) * ((5.0)/(7.0))) * 8)) perc

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

tx.Dm_Billable = '1'

and

month(tx.Dm_Date) = month(GetDate())

and

year(tx.Dm_Date) = year(GetDate())

group by u.fullname) as B

on

A.Fullname = B.Fullname

Left Outer Join

(

select

u.fullname

, sum(tx.Dm_Time) TwomosagoBillhours

, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

Month(tx.Dm_Date) = Month(getdate())-2

group by u.fullname

) as C

on

A.Fullname = C.Fullname

View 1 Replies View Related

What Is It Mean That User_name Is A Sysname?

Apr 25, 2007

Hi,



In SQL Server 2005 Express BOL,

it says in 'Arguments' description part like this.



user_name

Specifies the name by which the user is identified inside this database. user_name is a sysname.
It can be up to 128 characters long.



What 'user_name is a sysname' imply?

View 2 Replies View Related

How To Get HOST_NAME Insted Of User_name()

Jun 7, 2001

All developers use same login to access database (I know is it is not the best solution)

We need track when user insert value or edit value in specific table
It is done by triger and work fine with USER_ID() or user_name()

But all users (developers) use same login and USER_ID() and user_name()always the same.

Please help me how can get HOST_NAME of user who is trying to modify table and place it into triger insted of user_name()

Thanks a lot

View 2 Replies View Related

Procedure 'JaiDeleteUser' Expects Parameter '@user_name', Which Was Not Supplied

Feb 10, 2008

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server"><title>Untitled Page</title> </head>
<body>
 
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
 
<div>
 
<br />
<asp:Label ID="lblUserInfo" runat="server" Text="Label"></asp:Label><asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:JaiConnectionString %>" DeleteCommand="JaiDeleteUser" DeleteCommandType="StoredProcedure" SelectCommand="SELECT users.user_name, users.user_pass, user_roles.role_name, users.VendorId FROM users INNER JOIN user_roles ON users.user_name = user_roles.user_name"
UpdateCommand="JaiUpdateUser" UpdateCommandType="StoredProcedure">
<DeleteParameters>
<asp:Parameter Name="user_name" Type="String" />
<asp:Parameter Name="user_pass" Type="String" />
<asp:Parameter Name="role_name" Type="String" />
<asp:Parameter Name="VendorId" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="user_name" Type="String" />
<asp:Parameter Name="user_pass" Type="String" />
<asp:Parameter Name="role_name" Type="String" />
<asp:Parameter Name="VendorId" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource><asp:GridView ID="GridView1" runat="server" AllowSorting="True"
AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" /><asp:BoundField DataField="user_name" HeaderText="user_name"
SortExpression="user_name" /><asp:BoundField DataField="user_pass" HeaderText="user_pass"
SortExpression="user_pass" /><asp:BoundField DataField="role_name" HeaderText="role_name"
SortExpression="role_name" /><asp:BoundField DataField="VendorId" HeaderText="VendorId"
SortExpression="VendorId" />
</Columns>
</asp:GridView>
<br />
<br />
</div></form> </body>
</html>
 
 
<<<<<<<<<<<<<Stored Procedure>>>>>>>>>>>>>>>>
CREATE PROCEDURE [dbo].[JaiDeleteUser] @user_name varchar (25), @user_pass varchar (25), @role_name Varchar (15), @VendorId intAS
beginDelete from user_roles where user_name =@user_nameDELETE FROM [users] WHERE user_name =@user_nameendGO
I am getting the error
Procedure 'JaiDeleteUser' expects parameter '@user_name', which was not supplied
whenever I try to delete a record. While Updating works with no problem. Please help.

View 8 Replies View Related

Login Failed For User &&<user_name&&>. (Microsoft SQL Server, Error: 18456)

Aug 25, 2007

Please help I keep getting this message when I log on to my data base. Not sure what state 1 relates too

TITLE: Connect to Server
------------------------------
Cannot connect to <server_name>
------------------------------
ADDITIONAL INFORMATION:
Login failed for user <user_name>. (Microsoft SQL Server, Error: 18456)

------------------------------
BUTTONS:
OK
------------------------------
Server Name: <server~name>Error Number: 18456
Severity: 14
State: 1
Line Number: 65536

View 13 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

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

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 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 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 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

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

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

SQL XML :: Handling Namespace In XQuery - Declaring A Namespace - What Is AWMI

Aug 4, 2015

After I learned the XML Schemas Collection, Using XML Data and 5 XML data type methods : query(), valuse(), exist(), modify(), and node(), I just started to do XQuery by using the Microsoft XQuery Language Reference - SQL Server 2012 Books Online and Handling Namespaces in XQuery of [URL]. I copied the following code:

-- Handlimng Namespace in Xquery (Page 6 of XQuery Languge Reference & Examples of msdn library)

-- Example A. Declaring a namespace

-- saved as MicrosoftXQueryPage6 in C:DocumentsXquery-SQLServer2012

-- 4 Aug 2015 10:55 AM

SELECT Instructions.query('
declare namespace AWMI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";
/AWMI:root/AWMI:Location[1]/AWMI:step
') as Result
FROM Production.ProductModel
WHERE ProductModelID=7

I executed the code in my Microsoft SQL Server 2012 Management Studio (SSMS2012). It worked nicely.

<AWMI:step xmlns:AWMI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions">
Insert <AWMI:material>aluminum sheet MS-2341</AWMI:material> into the <AWMI:tool>T-85A framing tool</AWMI:tool>.
</AWMI:step>
<AWMI:step xmlns:AWMI="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions">

[code]....

But, I don't know what AWMI means and what the results of SELECT Instructions.query(' declare namespace...  are. 

View 3 Replies View Related

SSIS Script Task Alters Package Variable, But Variable Does Not Change.

Oct 25, 2006

I'm working on an SSIS package that uses a vb.net script to grab some XML from a webservice (I'd explain why I'm not using a web service task here, but I'd just get angry), and I wish to then assign the XML string to a package variable which then gets sent along to a DataFlow Task that contains an XML Source that points at said variable. when I copy the XML string into the variable value in the script, if do a quickwatch on the variable (as in Dts.Variable("MyXML").value) it looks as though the new value has been copied to the variable, but when I step out of that task and look at the package explorer the variable is its original value.

I think the problem is that the dataflow XML source has a lock on the variable and so the script task isn't affecting it. Does anyone have any experience with this kind of problem, or know a workaround?

View 1 Replies View Related

Passing A SSIS Global Variable To A Declared Variable In A Query In SQL Task

Mar 6, 2008

I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task:

DECLARE @DV INT;
SET @DV = (SELECT MAX(DateValue) FROM tblTG);
DECLARE @PV INT;
SET @PV = @DV - 1;

I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this:

DECLARE @DV INT;
SET @DV = ?;
DECLARE @PV INT;
SET @PV = @DV - 1;


I have almost 50 references to these parameters in the query so a substitution would be helpful.

Dan

View 4 Replies View Related

SSIS: Problem Mapping Global Variables To Stored Procedure. Can't Pass One Variable To Sp And Return Another Variable From Sp.

Feb 27, 2008

I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database. The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes.

Here are the task steps.


[Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step.

[Execute SQL Task] - Log an entry to a table indicating that the import has started.

[Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step. This works.

[Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks. This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3. The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database. Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile.


If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS.

If IsNewFile is TRUE, proceed with the import. There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post.
Here is the stored procedure that Step 4 is calling. You can see that I experimented with using and not using the OUTPUT option. I really don't care if it returns the value as an OUTPUT or as a field in a recordset. All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction.


CREATE PROCEDURE [dbo].[p_CheckImportFileCreateDate]

/*

The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate.

If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset.

Otherwise it returns a FALSE value in the IsNewFile column.

Example:

exec p_CheckImportFileCreateDate 'GL Account Import', '2/27/2008 9:24 AM', 0

*/

@ProcessName varchar(50)

, @FileCreateDate datetime

, @IsNewFile bit OUTPUT

AS

SET NOCOUNT ON

--DECLARE @IsNewFile bit

DECLARE @CreateDateInTable datetime

SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName

IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName)

BEGIN

-- The process exists in tbl_ImportFileCreateDate. Compare the create dates.

IF (@FileCreateDate > @CreateDateInTable)

BEGIN

-- This is a newer file date. Update the table and set @IsNewFile to TRUE.

UPDATE tbl_ImportFileCreateDate

SET FileCreateDate = @FileCreateDate

WHERE ProcessName = @ProcessName

SET @IsNewFile = 1

END

ELSE

BEGIN

-- The file date is the same or older.

SET @IsNewFile = 0

END

END

ELSE

BEGIN

-- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE.

INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate)

VALUES (@ProcessName, @FileCreateDate)

SET @IsNewFile = 1

END

SELECT @IsNewFile

The relevant Global Variables in the package are defined as follows:
Name : Scope : Date Type : Value
FileCreateDate : (Package Name) : DateType : 1/1/2000
IsNewFile : (Package Name) : Boolean : False

Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this. Here are the settings.

General
Name = Compare Last File Create Date
Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate.
TimeOut = 0
CodePage = 1252
ResultSet = None
ConnectionType = OLE DB
Connection = MyServerDataBase
SQLSourceType = Direct input
IsQueryStoredProcedure = False
BypassPrepare = True

I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate.
SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output

Parameter Mapping
Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1
Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1

Result Set is empty.
Expressions is empty.

When I run this in debug mode with this SQL statement ...
exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the following error message appears.

SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.

Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Compare Last File Create Date

Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "MyPackage.dtsx" finished: Failure.

When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure.

When I run this in debug mode with this SQL statement ...
exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the tbl_ImportFileCreateDate table gets updated. So I know that data piece is working, but then it fails with the following message.

SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.

Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date

Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "MyPackage.dtsx" finished: Failure.

The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit. So what gives?

The "Possible Failure Reasons" message is so generic that it's been useless to me. And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task. My suspicion is that one or more of the settings in that Execute SQL Task node is bad. Or that there is some cryptic, undocumented reason that this is failing.

Thanks for your help.

View 5 Replies View Related

Compare The Value Of A Variable With Previous Variable From A Function ,reset The Counter When Val Changes

Oct 15, 2007

I am in the middle of taking course 2073B €“ Programming a Microsoft SQL Server 2000 Database. I noticed that in Module9: Implementing User-Defined Functions exercise 2, page 25; step 2 is not returning the correct answer.

Select employeeid,name,title,mgremployeeid from dbo.fn_findreports(2)

It returns manager id for both 2 and 5 and I think it should just return the results only for manager id 2. The query results for step 1 is correct but not for step 2.

Somewhere in the code I think it should compare the inemployeeid with the previous inemployeeid, and then add a counter. If the two inemployeeid are not the same then reset the counter. Then maybe add an if statement or a case statement. Can you help with the logic? Thanks!

Here is the code of the function in the book:

/*
** fn_FindReports.sql
**
** This multi-statement table-valued user-defined
** function takes an EmplyeeID number as its parameter
** and provides information about all employees who
** report to that person.
*/
USE ClassNorthwind
GO
/*
** As a multi-statement table-valued user-defined
** function it starts with the function name,
** input parameter definition and defines the output
** table.
*/
CREATE FUNCTION fn_FindReports (@InEmployeeID char(5))
RETURNS @reports TABLE
(EmployeeID char(5) PRIMARY KEY,
Name nvarchar(40) NOT NULL,
Title nvarchar(30),
MgrEmployeeID int,
processed tinyint default 0)
-- Returns a result set that lists all the employees who
-- report to a given employee directly or indirectly
AS
BEGIN
DECLARE @RowsAdded int
-- Initialize @reports with direct reports of the given employee
INSERT @reports
SELECT EmployeeID, Name = FirstName + ' ' + LastName, Title, ReportsTo, 0
FROM EMPLOYEES
WHERE ReportsTo = @InEmployeeID
SET @RowsAdded = @@rowcount
-- While new employees were added in the previous iteration
WHILE @RowsAdded > 0
BEGIN
-- Mark all employee records whose direct reports are going to be
-- found in this iteration
UPDATE @reports
SET processed = 1
WHERE processed = 0

-- Insert employees who report to employees marked 1
INSERT @reports
SELECT e.EmployeeID, Name = FirstName + ' ' + LastName , e.Title, e.ReportsTo, 0
FROM employees e, @reports r
WHERE e.ReportsTo = r.EmployeeID
AND r.processed = 1
SET @RowsAdded = @@rowcount
-- Mark all employee records whose direct reports have been
-- found in this iteration
UPDATE @reports
SET processed = 2
WHERE processed = 1
END
RETURN -- Provides the value of @reports as the result
END
GO

View 1 Replies View Related







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