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?
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.
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
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.
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.
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.
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)
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
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
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!
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
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?
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?
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 .....
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
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?
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
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
I am not comfortable with DTS 2000 but I need to execute a encapsulated DTS 2000 package from a SSIS package. The real problem is when I need to pass SSIS variables to DTS 2000 package. The DTS 2000 package have 3 global variables that I can identify on " Execute DTS 2000 Package Task Editor - Inner Variables ". I believe the SSIS variables must be mapped on " Execute DTS 2000 Package Task Editor - OuterVariables ". How can I associate the SSIS variables(OuterVariables ) to "Inner Variables"? How can I do it? Much Thanks.
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?
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.Â
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
Anyone know how to write the portion in red in a stored procedure?LEFT OUTER JOIN TITLE AS T ON (POS.TITLE_ID = T.TITLE_ID)JOIN DISTRICT_LOCATIONS AS DL ON (POS.DISTRICT_LOCATION_ID = DL.DISTRICT_LOCATION_ID)WHERE POS.PRIMARY_IND = 1 IF @DISTRICT_LOCATION_ID != 'All' BEGIN and DL.DISTRICT_LOCATION_ID = @DISTRICT_LOCATION_ID ENDIF @ATTENDANCE_STATUS_ID!= 'All' BEGIN and AST.ATTENDANCE_STATUS_ID= @ATTENDANCE_STATUS_ID ENDUNION ALLSELECT DISTINCT 5 AS TAG ,3 AS PARENT ,convert(varchar,getdate(),101) as [ACTIVITY_REPORT!1!REPORT_DATE] ,AX.ACTIVITY_CLASS_ID AS [ACTIVITY!2!ACTIVITY_CLASS_ID] ,NULL AS [ACTIVITY!2!ACTIVITY_NAME]
I am testing SQL Server 7.0. In Stored Proc I try to select a database which name is stored in the variable @databasename. I get the error that it can't open a database @databasename. Can I declare the database object in T-SQL?
/* Get Name of Server & declare variables */ declare @sname varchar(30) declare @db1 varchar(30) declare @db2 varchar(30) declare @db3 varchar(30) declare @dbf1 varchar(30) declare @dbf2 varchar(30) declare @dbf3 varchar(30)
select @sname = rtrim(substring(srvname,5,30)) from sysservers
print 'The name of this server is: ' + @sname
Set @db1 = @sname + 'database1' Set @db2 = @sname + 'database2' Set @db3 = @sname + 'database3' Set @dbf1 = @db1 + 'RL_log' Set @dbf2 = @db2 + 'RL_log' Set @dbf3 = @db3 + 'RL_log'
print @db1 print @dbf1 go
ALTER DATABASE @db1 MODIFY FILE (NAME = @dbf1, MAXSIZE = UNLIMITED) go ALTER DATABASE @db2 MODIFY FILE (NAME = @dbf2, MAXSIZE = UNLIMITED) go ALTER DATABASE @db3 MODIFY FILE (NAME = @dbf3, MAXSIZE = UNLIMITED) go .....
When I run it, I get the following errors: ..... The name of this server is: KANSASCITY KANSASCITYdatabase1 KANSASCITYdatabase1RL_log Server: Msg 170, Level 15, State 1, Line 2 Line 2: Incorrect syntax near '@db1'. Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '@db2'. Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '@db3'. .....
Don't let the line numbers fool you. They refer to the number of lines since the last 'go' in the script. As you can see, the @db1 and @dbf1 variables are evaluating correctly.
WHAT I AM TRYING TO ACCOMPLISH: I am attempting to change the setting of the Transaction Log to grow to fill up the entire disk. I do not wish to limit the space at this time. I have approximately 200 servers to manage and this script would be most useful in managing them, if it only worked.
Should I be using a different function to change the settings on the Transaction Log? Something other than ALTER DATABASE?
