Creating Table Based On A User Defined Tabletype

Mar 13, 2014

I have my defined table type created with

CREATE TYPE dbo.MyTableType AS TABLE
(
Name varchar(10) NOT NULL,
ValueDate date NOT NULL,
TenorSize smallint NOT NULL,
TenorUnit char(1) NOT NULL,
Rate float NOT NULL
PRIMARY KEY (Name, ValueDate, TenorSize, TenorUnit)
);

and I would like to create a table of this type. From this answer [URL] .... the suggestion was to try

CREATE TABLE dbo.MyNewTable AS dbo.MyTableType

which produced the following error message in my SQL Server Express 2012:

> Incorrect syntax near the keyword 'OF'.

Is this not supported by SQL Server Express? If so, could I create it some other way, for example using `DECLARE`?

View 1 Replies


ADVERTISEMENT

Automatically Populate Application User Defined Field Based On Other Fields In Table

Nov 22, 2015

I am working with a vendor application called Cisco Unified Attendant Console - it operates on a Windows server with a SQL express database. The CUPs function of the application needs to reference a "contact" field with only the user portion of the contact's email address - generally, the contact's User ID will match the user portion of their email address, however, for this customer it does not (they use the employee number as the User ID and firstname.lastname as user portion of the email address.

Writing a script to accomplish the following:

The dbo.Contact_Properties table of the ATTCFG database has the following fields that we can work with:
 
- First_Name
 - Last_Name
 - Email
 - User_Field_2
 - Contact_Unique_Ref (appears to be the field that ties all other contact tables together ?)

Is it possible to create a script that could run daily to either, combine the First_Name and Last_Name fields (with a period between) and populate the User_Field_2 field for each user, or populate the User_Field_2 field with everything before the @ symbol in the Email field for each user?

Also, by default the servers that this application is installed on does not have SQL Server Management Studio installed - is it possible to accomplish with PowerShell script triggered from the Windows Scheduler?

View 5 Replies View Related

Creating A New User For Sp Execution Say Sa (to Whom I Am In Need Of Creating Under My User Defined Db )

Jul 11, 2007

I am in need of creating a new user for stored procedures execution say sa (to whom i am in need of creating under my user defined db)

View 1 Replies View Related

Creating A Table In SQL Server With Fields From Other Tables And Some Fields User Defined

Feb 20, 2008

How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.

View 8 Replies View Related

User Defined Function: Convert String Value Of Table To Table Object

Jul 20, 2005

Does anyone know where to find or how to write a quick user defined fucntionthat will return a table object when passed the string name of the tableobject. The reason why I want dynamicallly set the table name in a storedprocudue WITHOUT using concatination and exec a SQL String.HenceIf @small_int_parameter_previous = 1 then@vchar_tablename = "sales_previous"else@vchar_tablename = "sales"Endselect * from udf_TableLookup(@vchar_tablename )So if I pass 1, that means I want all records from "sales_previous"otherwise give me all records from "sales" (Sales_Previous would last yearssales data for example).udf_TableLookup would I guess lookup in sysobjects for the table name andreturn the table object? I don't know how to do this.I want to do this to avoid having 2 stored procedures..one for current andone for previous year.Please respond to group so others may benfiit from you knowledge.ThanksErik

View 2 Replies View Related

Table Of User Defined Types

Apr 13, 2006

Hi!

I have a question about creating a user defined type: I'd like to create a table of employee objects which include objects of the type employee_t. I used this structure before in Oracle 9i and would like to know how it can be done with MS SQL Server 2000 or 2005, preferably with Enteprise Manager/Management Studio. Below is an example.

CREATE TYPE employee_t AS OBJECT (
name VARCHAR(10)
jobDesc VARCHAR(15)
...
)

CREATE TABLE Employee of employee_t

Regards,

Timo

View 3 Replies View Related

A .NET Framework Error Occurred During Execution Of User-defined Routine Or Aggregate -While Creating A SQL SERVER 2005 Stored Prodecure In VS.NET 2005

Aug 15, 2007

 Running [dbo].[insertlogin] ( @log = hiten, @pas = hiten ).A .NET Framework error occurred during execution of user-defined routine or aggregate "insertlogin": System.Data.SqlClient.SqlException: Must declare the scalar variable "@Log".System.Data.SqlClient.SqlException:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)   at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)   at Microsoft.SqlServer.Server.SmiEventSink_Default.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages)   at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe)   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()   at SqlServerProject1.StoredProcedures.insertlogin(SqlString log, SqlString pas).No rows affected.(0 row(s) returned)@RETURN_VALUE = Finished running [dbo].[insertlogin]. ***************************************************************all i am trying to do is :  creating a SP in VS using managed code and then trying to execute it. But every time i get the above error. If you can tell me how to edit connection string in this that would be very helpful. At present i am using :   Using conn As New SqlConnection("context connection=true") I tried to do "" ALTER ASSEMBLY SqlServerProject1 WITH PERMISSION_SET=EXTERNAL_ACCESS""but i get this error  ""  Msg 10327, Level 14, State 1, Line 1ALTER ASSEMBLY for assembly 'SqlServerProject1' failed because assembly 'SqlServerProject1' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS.  The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem.""" *********************************************************************Plz help 

View 13 Replies View Related

Table Variable And User Defined Types

Oct 30, 2003

This is part of the codes of a stored procedure:

-- Create tables variables
DECLARE @_tab_PRNumList TABLE(
PRNum udt_PRNum
)

However, it cannot be compiled coz of this error:
Error 2715: Column or parameter #1: Cannot find data type udt_PRNum.

This user-defined data type is created in a user database only.

I tried to created the user-defined type into BOTH tempdb and master but same error.

How can i resolove it?
Or it is impossible to use user-defined datatype for table variable?

In the past,
i use this method:
CREATE TABLE #PRNumList (
PRNum udt_PRNum
)

and cerate udt_PRNum in tempdb only could solve it.

But now i want to use table variable.
It seems NOT work for my situation.

Any idea?

View 1 Replies View Related

Establish User Defined Table Type

Aug 18, 2015

INSERT INTO @TESTTAB SELECT * FROM dbo.UTIL_TABLE_FROM_STRING(@szDelimit)
Note....The stored function returns a table.

Why doesn't this work ?:
SET @TESTTAB = (SELECT * FROM dbo.UTIL_TABLE_FROM_STRING(@szDelimit))

I wonder if I need to establish a user-defined table type ?I really just want a pointer to the table, and not to have to create a new copy.

View 8 Replies View Related

Order By User Defined Table Type

Dec 12, 2014

I've got a user defined table type called StringDictionaryTVP:

CREATE TYPE [dbo].[StringDictionaryTVP] AS TABLE(
[key] [varchar](500) NULL,
[value] [varchar](500) NULL
)

Ideally I would like to be able to have a # of columns and directions in this table like so:

DECLARE @OrderByClause StringDictionaryTVP

INSERT INTO @OrderByClause([key], [value])
values('gender','desc')
INSERT INTO @OrderByClause([key], [value])
values('name','asc')

Since our database can be a bit sizable, I'd also like to use Common Table Expressions so I can page through them fairly easy.So my standard cte is something like this:

DECLARE @PageIndex INT = 0
DECLARE @PageSize INT = 20
;WITH results_cte AS ( SELECT U.*, ROW_NUMBER() over ( ORDER BY name ) RowNum
from Users U)
SELECT * FROM results_cte
WHERE RowNum > @Offset AND RowNum <= @Offset + @PageSize

So where 'ORDER BY name' is I'd like to use the @OrderByClause in some sort of dynamic way. I've tried all kinds of stuff but even something like this doesn't get the actual column name I need

;WITH results_cte AS ( SELECT U.*, ROW_NUMBER() over ( ORDER BY (select top 1 [key] +' '+ [value] from @OrderByClause) ) RowNum
from Users U)

I may be chasing the wrong stick, but outside of dynamic sql, is something like this possible?

View 2 Replies View Related

User-Defined Func Syntax For Table Return

Oct 30, 2006

Hi all!

I'm trying to use a UDF that returns a table, but I'm not sure of the syntax to invoke it. I've found examples in BOL and on-line like the following:

SELECT * FROM dbo.fn_MyTableFunc( 123.09, 'MyID' )

But I need the input parameter to be obtained from another table. For a very simplistic example, I've got 4 tables (and yes, I know that I can get the results I want for this example without using a UDF, but humor me):

CREATE TABLE tUser (UserID int PRIMARY KEY, UserName varchar(50))
CREATE TABLE tAcctGroup (AcctGroupID int PRIMARY KEY, AcctGroupName varchar(50))
CREATE TABLE tAcct (AcctID int PRIMARY KEY, AcctGroupID int, AcctName varchar(50))
CREATE TABLE tMapUserToGroup (UserID int, AcctGroupID int)
GO

INSERT INTO tUser VALUES (111, 'Me')

INSERT INTO tAcctGroup VALUES (1, 'NY')
INSERT INTO tAcct VALUES (11, 1, 'New York City')
INSERT INTO tAcct VALUES (12, 1, 'Syracuse')

INSERT INTO tAcctGroup VALUES (2, 'GA')
INSERT INTO tAcct VALUES (21, 2, 'Atlanta')
INSERT INTO tAcct VALUES (22, 2, 'Savannah')
INSERT INTO tAcct VALUES (23, 2, 'Augusta')

INSERT INTO tAcctGroup VALUES (3, 'TX')
INSERT INTO tAcct VALUES (31, 3, 'Dallas')
INSERT INTO tAcct VALUES (32, 3, 'Houston')
INSERT INTO tAcct VALUES (33, 3, 'El Paso')
INSERT INTO tAcct VALUES (34, 3, 'San Antonio')

INSERT INTO tAcctGroup VALUES (4, 'CA')
INSERT INTO tAcct VALUES (41, 4, 'Los Angeles')
INSERT INTO tAcct VALUES (42, 4, 'San Francisco')

INSERT INTO tMapUserToGroup VALUES (111,2)
INSERT INTO tMapUserToGroup VALUES (111,4)
GO

CREATE FUNCTION dbo.ufnGetAcctList(@AcctGroupID int) RETURNS @tAcct table (AcctID int, AcctName varchar(50))
AS
BEGIN
INSERT INTO @tAcct
SELECT AcctID, AcctName FROM tAcct WHERE AcctGroupID = @AcctGroupID
RETURN
END
GO

I know that I can do:
SELECT * FROM TestDB.dbo.ufnGetAcctList(4)

But I want the equivalent of:
SELECT AcctID, AcctName FROM tAcct
WHERE AcctGroupID IN (SELECT AcctGroupID FROM tMapUserToGroup WHERE UserID = 111)

Which uses tMapUserToGroup to obtain the AcctGroupID to pass into the function. The results would be:
AcctID AcctName
-----------------------------
21 Atlanta
22 Savannah
23 Augusta
41 Los Angeles
42 San Francisco

Any thoughts?
Thanks in advance for your help.
Cat

View 7 Replies View Related

Order By With Columns Of User Defined Table Type

Sep 28, 2014

I have a user defined table type with two columns: ID: int, Value: float.Also, I have a table with different columns.I have a stored procedure:

ALTER PROCEDURE [dbo].[MyProcedure]
@List AS dbo.MyUserDefinedTableType READONLY
AS
BEGIN
SET NOCOUNT ON;

[code]....

I want to add "order by Value" to this stored procedure. Like below:

ALTER PROCEDURE [dbo].[MyProcedure]
@List AS dbo.MyUserDefinedTableType READONLY
AS
BEGIN
SET NOCOUNT ON;

[code]....

But this way is not true, and I get error when i debug my application.I fill this user defined table type in c# with data of a DataTable.

View 4 Replies View Related

How To Use User Defined Variables As Table Names In SSIS

Dec 4, 2007

I am a SSIS novice, and need help to do the following:

Every day a Table is generated by the system of format XR+present Year + present month + present day. My task is pretty simple. I am to devise a SSIS package that will import this table on to a different server.

I have defined the variable as user defined variable. I am also able to specify the variable in OLE db source editor -> Data Access Mode : Table name or view name variable. When I am click the 'Columns' feature, I get the following error

TITLE: Microsoft Visual Studio
------------------------------
Error at Data Flow Task [OLE DB Source [1]]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E37.
Error at Data Flow Task [OLE DB Source [1]]: Opening a rowset for "xr+ convert(char(8),getdate(), 112)" failed. Check that the object exists in the database.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC02020E8 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------

In short can any one tell me a correct way of using a table name as variable in
Data Access Mode : Table name or view name variable ?

One other question: can I make use of a dynamic SQL in Execute SQL Task? If yes, how do I write the query

exec( 'select * from ' + @table)

or just

select * from @table

Any suggestions will be appreciated.






View 10 Replies View Related

How To Get Permissions On A SQL Databse Table For A User Group Defined In AD

Jun 6, 2007

Hi, i am trying to find permissions on SQL server database tables for a usergroup defined in Active Directory.



there is one function in SQL : €œSELECT * FROM fn_my_permissions('TableName', 'OBJECT')€?



This function get me the permission on TableName table for the current user. but i want that inforamtion for a user group defined in AD.

Is tehre any way to acheive that?



-Mani

View 1 Replies View Related

Can We Pass Filed Name And Table Name As Parameter To User Defined Functions

Dec 29, 2005

Hi,
Following is the user defined function I want to get maximum value. It gives man an error "@strTableName must declare"


CREATE FUNCTION dbo.GetMaximum
(
@strFieldNamenvarchar(255),
@strTableName nvarchar(255)

)
RETURNS nvarchar(255)
AS
BEGIN

DECLARE @maxID int


SELECT @maxID=(SELECT IsNull(MAX(@strFieldName),0)+1 FROM @strTableName )

RETURN (@maxID)

END

View 5 Replies View Related

SQL Server 2014 :: User Defined Function - Using Table Name As Variable

Aug 9, 2014

I'm trying to create a simple function that will do a count on a table. I want to pass the table name in form of a parameter to the variable and this function will return the count as an int. See my function below...

CREATE FUNCTION count_rows (@tablename varchar(100)
RETURNS int AS
BEGIN
DECLARE @emp_count AS int
declare @declaration varchar(100)

[Code] ....

The errors I am getting are as follows:

Msg 102, Level 15, State 1, Procedure count_rows, Line 3
Incorrect syntax near 'RETURNS'.
Msg 102, Level 15, State 1, Procedure count_rows, Line 10
Incorrect syntax near '@declaration'.
Msg 178, Level 15, State 1, Procedure count_rows, Line 14

A RETURN statement with a return value cannot be used in this context.

View 9 Replies View Related

Transact SQL :: Change Structure Of User Defined Table Types

Nov 4, 2015

Change structure of a User Defined Table Types

      field
([idConteudo] [int]
NULL)      to     
 ([idConteudo] [BigInt]
NULL)

Or really should drop and create again?

CREATE TYPE [dbo].[tbProjeto] AS TABLE(
[dsConteudo] [varchar](64) NOT NULL,
[idConteudo] [int] NULL,
[dtConteudo] [datetime] NULL DEFAULT (getdate())
)
GO

View 4 Replies View Related

Handling Large Queries In A Table Valued User Defined Function

May 20, 2008

Hello,

We have created several Table Valued User Defined Functions in a Production SQL Server 2005 DB that are returning large (tens of thousands of) rows obtained through a web service. Our code is based on the MSDN article Extending SQL Server Reporting Services with SQL CLR Table-Valued Functions .

What we have found in our implementations of variations of this code on three seperate servers is that as the rowset grows, the length of time required to return the rows grows exponentially. With 10 columns, we have maxed out at approximately 2 500 rows. Once our rowset hit that size, no rows were being returned and the queries were timing out.

Here is a chart comparing the time elapsed to the rows returned at that time for a sample trial i ran:

Sec / Actual Rows Returned
0 0
10 237
20 447
30 481
40 585
50 655
60 725
70 793
80 860
90 940
100 1013
110 1081
120 1115
130 1151
140 1217
150 1250
160 1325
170 1325
180 1430
190 1467
200 1502
210 1539
220 1574
230 1610
240 1645
250 1679
260 1715
270 1750
280 1787
290 1822
300 1857
310 1892
320 1923
330 1956
340 1988
350 1988
360 2022
370 2060
380 2094
390 2094
400 2130
410 2160
420 2209
430 2237
440 2237
450 2274
460 2274
470 2308
480 2342
490 2380
500 2380
510 2418
520 2418
530 2451
540 2480
550 2493
560 2531
570 2566

It took 570 seconds (just over 9 1/2 minutes to return 2566 rows).

The minute breakdown during my trial is as follows:
1 = 655 (+ 655)
2 = 1081 (+ 426)
3 = 1325 (+244)
4 = 1610 (+285)
5 = 1822 (+212)
6 = 1988 (+166)
7 = 2160 (+172)
8 = 2308 (+148)
9 = 2451 (+143)

As you can tell, except for a few discrepancies to the resulting row count at minutes 4 and 7 (I will attribute these to timing as the results grid in SQL Management Studio was being updated once every 5 seconds or so), as time went on, fewer and fewer rows were being returned in a given time period. This was a "successful" run as the entire rowset was returned but on more than several occasions, we have reached the limit and have had 0 new rows per minute towards the end of execution.

Allow me to explain the code in further detail:

[SqlFunction(FillRowMethodName = "FillListItem")]
public static IEnumerable DiscoverListItems(...)
{

ArrayList listItems = new ArrayList();

SPToSQLService service = new SPToSQLService();

[...]

DataSet itemQueryResult = service.DoItemQuery(...); // This is a synchronous call returning a DataSet from the Web Service

//Load the DS to the ArrayList


return listItems;
}


public static void FillListItem(object obj, out string col1, out string col2, out string col3, ...)
{

ArrayList item = (ArrayList) obj;


col1 = item.Count > 0 ? (string) item[0] : "";
col2 = item.Count > 0 ? (string) item[1] : "";
col3 = item.Count > 0 ? (string) item[2] : "";
[...]
}

As you will notice, the web service is called, and the DataSet is loaded to an ArrayList object (containing ArrayList objects), before the main ArrayList is returned by the UDF method. There are 237 rows returned within 10 seconds, which leads me to believe that all of this has occured within 10 seconds. The method GetListItems has executed completely and the ArrayList is now being iterated through by the code calling the FillListItem method. I believe that this code is causing the result set to be returned at a decreasing rate. I know that the GetListItems code is only being executed once and that the WebService is only being called once.


Now alot of my larger queries ( > 20 000 rows) have timed out because of this behaviour, and my workaround was to customize my web service to page the data in reasonable chunks and call my UDF's in a loop using T-SQL. This means calling the Web Service up to 50 times per query in order to return the result set.

Surely someone else who has used Table Valued UDFs has come accross this problem. I would appreciate some feedback from someone in the know, as to whether I'm doing something wrong in my code, or how to optimize an SQL Server properly to allow for better performance with CLR functions.

Thanks,

Dragan Radovic

View 7 Replies View Related

T-SQL (SS2K8) :: Generate Script For View Dependencies Of User Defined Table Types?

Aug 25, 2014

T-SQL script for the following request ?

Generate script for view dependencies of user defined table types ?

View 2 Replies View Related

SQL 2012 :: Creating A Table Based On Non-Join

Oct 21, 2014

I have 2 tables that I need to merge let me explain. I have a range of product ID's that have a product grouping of * meaning all product groups. So I have a table with products and one with around 100 groups

ProdID ProdGrp
-------- ---------
11 *
12 *

ProdGrp ProdGrpDesc
--------- ---------------
A Prod Group A
B Prod Group B
C Prod Group C

I need a table which looks like the below but I have no joining mechanism

ProdID ProdGrp
-------- ---------
11 A
11 B
11 C
12 A
12 B
12 C

View 3 Replies View Related

CREATING TABLE USING SQL BASED ON SEQUENTIAL DATES

Jul 20, 2005

I would like to reopen this discussion. I need to create this tableusing SQL that will return this dataset.BRAID RATEDEFID MIN(BRADATE) MAX(BRADATE RATECODE PRODNAME614 14 12/1/2002 12/4/2004 Rack BeachTower Terrace618 14 12/8/2002 12/11/2004 Rack BeachTower Terrace622 14 12/15/2002 12/19/2004 Rack BeachTower TerraceI have provided the test data below and the SP I have been working with.The SP works fine as long as there are 4 seqential dates. I getunpredictable results when the count <> 4. I am also concerned on whathappens at the end of the month. For example, 1/31/2004, 2/1/2004.Does this affect the SP? I appreciate your continued support.MichaelCREATE TABLE [dbo].[BASERATEAVAIL_Tmp] ([BRAID] [decimal](18, 0) NOT NULL ,[RATEDEFID] [decimal](18, 0) NOT NULL ,[BRADATE] [datetime] NOT NULL ,[RATECODE] [varchar] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL,[PRODNAME] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL) ON [PRIMARY]GOINSERT INTO dbo.BASERATEAVAIL_Tmp(BRAID, RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (614, 14, '12/1/2002', 'Rack', 'Beach Tower Terrace');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (615, 14, '12/2/2002', 'Rack', 'Beach Tower Terrace');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (616, 14, '12/3/2002', 'Rack', 'Beach Tower Terrace');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (617, 14, '12/4/2002', 'Rack', 'Beach Tower Terrace');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (618, 14, '12/8/2002', 'Rack', 'Beach Tower Terrace');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (619, 14, '12/9/2002 ', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (620, 14, '12/10/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (621, 14, '12/11/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (622, 14, '12/15/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (623, 14, '12/16/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (624, 14, '12/17/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (625, 14, '12/18/2002', ' Rack ', ' Beach Tower Terrace ');INSERTINTO dbo.BASERATEAVAIL_Tmp(BRAID,RATEDEFID, BRADATE, RATECODE, PRODNAME)VALUES (626, 14, '12/19/2002 ', ' Rack ', ' Beach Tower Terrace ');CREATE PROCEDURE usp @n INTASSET ANSI_WARNINGS OFFSELECT MIN( braid) AS "braid",ratedefid, ratecode, prodname,MIN( CASE n WHEN 1 THEN bradate END ) AS "min_date",MAX( CASE n WHEN 0 THEN bradate END ) AS "max_date"FROM ( SELECT t1.braid, t1.ratedefid, t1.ratecode, t1.prodname,t1.bradate,COUNT( * ) % @n AS "n",( COUNT( * ) - 1 ) / @n AS "r"FROM BASERATEAVAIL_Tmp t1INNER JOIN BASERATEAVAIL_Tmp t2ON t2.bradate <= t1.bradateGROUP BY t1.braid, t1.ratedefid, t1.ratecode, t1.prodname,t1.bradate) DGROUP BY r, ratedefid, ratecode, prodnameHAVING MAX( CASE n WHEN 0 THEN bradate END ) IS NOT NULL ;Usage:EXEC usp @n = 4--Anith*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Creating Rows Based On Date Range From Another Table

Aug 20, 2006

I wish to build a table based on values from another table.I need to populate a table between two dates from another table. Usingthe START_DT and END_DT, create records between those dates.I need a new column that is the days between the date and the MID_DTThe data I wish to end with would look something like this:PERIOD DATE DAY_NO200602 2005-07-06 -89200602 2005-07-07 -88200602 2005-07-08 -87<...>200602 2005-10-02 -2200602 2005-10-03 -1200602 2005-10-04 0200602 2005-10-05 1<...>200602 2005-12-18 75CREATE TABLE "dbo"."tblDates"("PERIOD" CHAR(6) NOT NULL,"START_DT" DATETIME NULL,"MID_DT" DATETIME NULL,"END_DT" DATETIME NOT NULL)INSERT INTO tblDates VALUES('200505',2005-04-12,2005-07-05,2005-09-12)INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)INSERT INTO tblDates VALUES('200603',2005-10-04,2006-01-17,2006-03-27)INSERT INTO tblDates VALUES('200604',2006-01-18,2006-04-10,2006-06-19)INSERT INTO tblDates VALUES('200605',2006-04-11,2006-07-04,2006-09-11)INSERT INTO tblDates VALUES('200702',2006-07-05,2006-10-02,2006-12-18)

View 7 Replies View Related

User-defined Fun Or The System-defined Fun

Apr 2, 2008

hai,

how can i identify the function is user defined or the system defined function....................

View 1 Replies View Related

SQL Server 2012 :: Error Creating Temp Table Based On IF Logic

Nov 13, 2014

I could deploy across my environment, which is a mix of 2008R2/2012 servers, to give some information on log files. Running into a silly issue right off the bat. The table that DBCC LogInfo() conjures out of magic is different between the two. In 2012 it gained the RecoveryUnitID column. So I'm trying to write some logic to create a temp table based on which version is running. I would like to avoid a global temp table if possible. Here's what I've tried:

sp_executesql creates a table outside of the scope of my session:
DECLARE @PrVers NVARCHAR(128)
, @PrVersNum DECIMAL(10,2)
, @StageTable NVARCHAR(1024) = N''

[code]....

View 9 Replies View Related

Login Failed: Reason: User &#39;_&#39; Not Defined As A Valid User Of Trusted

Jan 26, 2000

Our server has integrated security setup, upon startup of the server,
we have a continuous flow of error msg:
Login Failed: Reason: User '_' not defined as a valid user of trusted
connection.
The timing of these messages only around 30 seconds apart
The only incident from Technet I can find is Q186314, but we don't have replication setup, anyone knows where I can look into ?
I recycle the server but didn't help.
I appreciate any help I can get ..
Thanks.

View 1 Replies View Related

Login Failed- User: Reason: Not Defined As A Valid User Of A Trusted SQL Server Connection.

Apr 5, 1999

Hi,

i'm a newbie in SQL. I can't connect to SQL 6.5 server using ASP ADO. Below is the program code

<%
set conn=server.createobject("adodb.connection")
conn.open "dsn=Central;uid=sa;pwd="
%>

An error message appears
Login failed- User: Reason: Not defined as a valid user of a trusted SQL Server connection.

I've already created a ODBC System DSN named Central
parameter Server Local and used Trusted connection checked.

Then under sql Manager there is a sa with a blank password.

Please help.

View 1 Replies View Related

Transact SQL :: Can Invoke Stored Procedure Stored Inside From User Defined Table Column?

Nov 5, 2015

Can I invoke stored procedure stored inside from a user defined table column?

View 5 Replies View Related

Table-valued User-defined Function: Commands Completed Successfully, Where Is The Result? How Can I See Output Of The Result?

Dec 11, 2007

Hi all,

I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:

USE AdventureWorks;

GO

IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL

DROP FUNCTION dbo.ufnGetContactInformation;

GO

CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)

RETURNS @retContactInformation TABLE

(

-- Columns returned by the function

ContactID int PRIMARY KEY NOT NULL,

FirstName nvarchar(50) NULL,

LastName nvarchar(50) NULL,

JobTitle nvarchar(50) NULL,

ContactType nvarchar(50) NULL

)

AS

-- Returns the first name, last name, job title, and contact type for the specified contact.

BEGIN

DECLARE

@FirstName nvarchar(50),

@LastName nvarchar(50),

@JobTitle nvarchar(50),

@ContactType nvarchar(50);

-- Get common contact information

SELECT

@ContactID = ContactID,

@FirstName = FirstName,

@LastName = LastName

FROM Person.Contact

WHERE ContactID = @ContactID;

SELECT @JobTitle =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)

THEN (SELECT Title

FROM HumanResources.Employee

WHERE ContactID = @ContactID)

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE ContactID = @ContactID)

ELSE NULL

END;

SET @ContactType =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)

THEN 'Employee'

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN 'Vendor Contact'

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN 'Store Contact'

-- Check for individual consumer

WHEN EXISTS(SELECT * FROM Sales.Individual i

WHERE i.ContactID = @ContactID)

THEN 'Consumer'

END;

-- Return the information to the caller

IF @ContactID IS NOT NULL

BEGIN

INSERT @retContactInformation

SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;

END;

RETURN;

END;

GO

----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.

Thanks in advance,
Scott Chang

View 1 Replies View Related

Select Based On User Identity Name Included In Insert Into Second Table

Aug 25, 2006

I am having issues getting this to work.  I have the user login to a page to
put in a request for vacation.  When they login, I have a label that isn't
visible that is equal to their User.Identity.Name.
I select the user from the employee table where the username = the label User
Identity Name and pull in the emp_id which is the primary key that identifies
the user. 
I need to insert the request into the request table with the emp_id from the
select statement, without showing the em_id on the screen.  I tried using a
hidden field and assigning the emp_id as the value, but it isn't working. 
Not sure if this is the best way to do this.  Really new to ASP.NET 2.0 so I
really appreciate any help.
Thank you!

View 12 Replies View Related

Help With User Defined Function

Jan 10, 2007

I have a UDF that takes my input and returns the next valid business day date. My valid date excludes weekends and holidays.
It works perfect except for one issue. It doesn't check to see if today's date  is a holiday.
I pass a query to sql server like so " select dbo.getstartdate('01/ 10/2007',2)"
It then moves ahead two business days and returns that date.
Here is the current code. Hopefully someone can tell me how to do the holiday check on the current date.
I really don't want to rewrite the whole script .
Code---------------------------------------------------------
SET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS OFF GO
--DROP FUNCTION GetStartDate
--declare function receiving two parameters ---the date we start counting and the number of business days
CREATE  FUNCTION GetStartDate (@startdate datetime, @days int)   RETURNS datetimeASBEGIN
--declare a counter to keep track of how many days are passingdeclare @counter int
/*Check your business rules.  If 4 business days means you count starting tomorrow, set counter to 0.  If you start counting today, set counter to 1*/set @counter = 1
--declare a variable to hold the ending datedeclare @enddate datetime
--set the end date to the start date.  we'll be -- incrementing it for each passing business dayset @enddate = @startdate
/*Start your loop.While your counter (which was set to 1), is less than or equal to the number of business days increment your end date*/WHILE @counter <= @days
BEGIN
--for each day, we'll add one to the end dateset @enddate = DATEADD(dd, 1, @enddate)
   --If the day is between 2 and 6 (meaning it's a week   --day and the day is not in the holiday table, we'll    --increment the counter   IF (DATEPART(dw, @enddate) between 2 and 6) AND       (@enddate not in           (           select HolidayDate            from tFederalHoliday            where [HolidayYear] = datepart(yyyy,@enddate)         )       )   BEGIN      set @counter = @counter + 1   END
--end the while loopEND
--return the end dateRETURN @enddate
--end the functionEND
GOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO
---------------------------------------------------------------------------------------------

View 1 Replies View Related

SQL User Defined View

Aug 22, 2007

I've a view in which one of the fields were  DATEDIFF(day,contract date,received) AS AgeOfAccount. I changed it to DATEDIFF(day, m.received, CASE WHEN m.clidlp > m.clidlc THEN m.clidlp ELSE m.clidlc END) * - 1 AS AgeOfAccount. When I execute the view  I'm getting an error. But the results are getting displayed properly. That's what's puzzling me. Could someone tell me what the error is.
 
The following errors were encountered while parsing the contents of the sql pane. 
Your entry cannot be converted to a valid date time value.

View 4 Replies View Related

User-defined Functions - Help!

Sep 3, 2004

Hi,

I have a search returning many 'car' records (regno, mileage, color, etc)

In a seperate table I have two columns: accessory and regno. This can have many rows for each regnumber.

Within the storedprocedure that returns the 'car' records I need to also return the relevant accessories for that car as one column.

I have written a function as such (with some help!):

ALTER Function getAccs(@regNo varChar(20))
RETURNS varChar
AS
BEGIN
Declare @List varchar(1000)
SELECT @List = COALESCE(@List + ', ', '') + accessory
FROM frxrep2.dbo.usedaccessories WHERE regnumber = @regno
return @List
END

I was hoping that I could simply use this in the 'car' SELECT statement.

ie:

SELECT regNo, color, mileage, dob.getAccs(regno) as AccessoryList
FROM tableBla etc


I'm not even sure if the function works - How can I test in SQL analyzer?

any help much appreciated,

Pete

View 3 Replies View Related

User Defined Function

Aug 17, 2005

Hi everyone,
    I am tring to pass acomma delimited string to a function  and this function is parsing the string so that I can see individual values so for example I am passing 1,2,3,4,5 as a parameter to my function and I am parsing this string so that I can write something like this
Select * from tableA where userID in(1,2,3,4)
It is working fine. Only problem is if the user passes word 'all' instead of 1,2,3,4 then I have to doSelect * from tableA
My function looks like this.  How can I modify this function if I pass 'all' as a paramater. Any help will be appreciated.CREATE FUNCTION [dbo].[ParseText2File] (@p_text varchar(4000), @p_Delimeter char(1))
RETURNS @results TABLE (id varchar(100))
AS
BEGIN
declare @i1 varchar(200)
declare @i2 varchar(200)
declare @tempResults Table (id varchar(100))
while len(@p_text) > 0 and charindex
(@p_Delimeter, @p_text) <> 0
begin
select @i1 = left(@p_text, charindex(@p_Delimeter, @p_text) - 1)
insert @tempResults select @i1
select @p_text = right(@p_text, len(@p_text) - charindex(@p_Delimeter,@p_text))
end
insert @tempResults select @p_text
insert @results
select *
from @tempResults
return
END
Thanks

View 1 Replies View Related







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