User Defined SQL Data Pull With ASP

Dec 19, 2007

Good Afternoon,

I have recently been tasked to come up with a means by which to create SQL data pulls based on user entries.  What I mean by this is, I will have a pre defined SQL data pull set up and the user can then select the criteria upon which to pull said data.

So, say I want to display a table where one of the colums has is the "Year".  I dont however want all years of data pulled, only 2005 - 2006.  So, in form field 1, I could select start year as 2005 and in form field 2, I could select end year as 2006.  This would then give me all data from this table based on that year range, or whatever year range I select and submit.

Is there a way to do this in ASP?  I have been reading up on UDF with SQL, but I cant find anything about linking it through the use of web forms or through ASP.  Or if there is another way to accomplish this?  Any assistance is very much appreciated!

View 5 Replies


ADVERTISEMENT

RDA Pull Problem: Command=PULL Hr=80040E4D Login Failed For User 'test'

Apr 25, 2007

Hi all,

I have following problem:

I'm developing a Windows Mobile application, which is using RDA Pull for retrieving data from SQL Server 2005 database to PDA. Please, see the example:






Code Snippet

using (SqlCeEngine engine = new SqlCeEngine(connStr))

{

engine.CreateDatabase();

}

serverConnStr="Provider=SQLOLEDB;Data Source=.;User ID=sa;Initial Catalog=Demo;Password=xxx";

using (SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess(

Configuration.Default.SyncServerAddress, "", "", connStr))

{

rda.Pull("MyTable", "SELECT * FROM mytable", serverConnStr, RdaTrackOption.TrackingOffWithIndexes, "ErrorTable");

}





Everythink works fine, when I use 'sa' user account in serverConnStr.

But, when I change conn string to:

"Provider=SQLOLEDB;Data Source=.;User ID=test;Initial Catalog=Demo;Password=test"

the sqlcesa30.dll cannot connect to SQL Server database.

In the sqlcesa30.log then I found following line:




Code Snippet

2007/04/17 10:43:31 Thread=1EE30 RSCB=16 Command=PULL Hr=80040E4D Login failed for user 'test'. 18456



The user 'test' is member of db_owner, db_datareader and public roles for the Demo database and in SQL Server Management Studio I'm able to login to the Demo database with using the 'test' users credentials and I'm able to run the select command on 'mytable'.



So, what's wrong? Why the sqlcesa30.dll process cannot login to the Demo database, and from another application with using the SAME connection string it works?



Please help.



Thank you.

Fipil.



View 10 Replies View Related

Why User Defined Data Type?

Aug 4, 2003

what are the advantages of using user-defined data types in SQL Server?

for example,
you may have Customer Number Cust_Num varchar(10)
you can create a user-defined data type like this:
udt_CustNum

so now your table creation script for Custome table become:
Cust_Num udt_CustNum

however, once the user-defined data type is referred by other tables.
it cannot be changed/deleted.

So, i wonder what are the good reason of using udt instead of fundamental data types. (built-in datatypes)

the only good thing i think is about:
the abstract naming of the data type.
you may have other key fields like Supplier Number which is varchar(12)
instead of u have to remember varchar(10) for customer and varchar(12) for supplier
u just to remember udt_CustNum for customer and udt_SuppNum for supplier.

what do u think?

View 2 Replies View Related

Can We've User Defined Data Types???

Nov 14, 2006

Hi there

Can we have user defined data types in SSIS???

Thanks and Regards

Rahul Kumar

View 1 Replies View Related

Changing User Defined Data Types

Aug 24, 2000

Is there a way to change the physical mapping of a user defined data type to the physical SQL Server datatype. It seems that once you create them, it is just about impossible to change it.

Thanks,
Andrew Abrams

View 3 Replies View Related

How To Identify User Defined Data Types

Jul 23, 2005

How can you tell which datatypes in a given database are user defined(with a query, not by looking in Enterprise Manager)? This is for SQLServer 2000.

View 2 Replies View Related

User Defined Data Types Problem

Jul 8, 2006

Hi all,I defined unsigned_int in my database, which uses unsigned_int_rangerule. The unsigned_int_range rule is defined as follows:@unsigned_int >=0 and @unsigned_int <=4294967295(4294967295 = 0xFFFFFFFF)The storage size is 4 bytes.One of the tables in the database contains a field that is of typeunsigned_int.When I try to add a new record into the table (ex: using the EnterpriseManager), it always fails in the unsigned_int field if I enter a valuegreater than 2147483647 (which is 0x7FFFFFFF) all the way to 4294967295(0xFFFFFFFF). The Enterprise Manager shows the following message:"The value you entered is not consistent with the data type orlength of the column, or over grid buffer limit."What is wrong here? The 4-byte storage should be good for any valuefrom 0 to 4294967295.Any help is appreciated.Thanks,Ken

View 2 Replies View Related

User-defined Function In Data Flow

May 11, 2006

I want to use a scalar user-defined function (udf) in a data flow with an OLE DB Source and an OLE DB Destination. The udf parameters are 2 columns from the source and the udf result is mapped to a destination column.

I've tried using an OLE DB Command component but I'm not getting very far. This statement works in Management Studio:

SELECT dbo.objectIDFromSourceKey('person','-128283')

I was expecting to just substitute ? for each of the function values 'person','-128283' but SSIS doesn't like the syntax (apparently it needs a FROM clause). Am I on the right track here or...?

View 2 Replies View Related

User Defined Data Types - Manipulate

Mar 4, 2008

I have somw tables like Product, Sales, Customer.

I used UDTs like ProductCode = nvarchar(30)
CustomerCode= nvarchar(15)
How can I modify my UDTs ?Is there any quick way for that ?


****

Thanks.

View 3 Replies View Related

User Defined Data Types And Stored Procedures

Mar 13, 2001

I have defined a user defined data type. When I try to create a stored procedure specifying the column and user define data tpye I receive message

Server: Msg 2715, Level 16, State 3, Procedure spStoredproc, Line 0
Column or parameter #1: Cannot find data type udtcol1.
Server: Msg 2715, Level 16, State 1, Procedure spStoredproc, Line 0
Column or parameter #2: Cannot find data type udtcol2.
Server: Msg 2715, Level 16, State 1, Procedure spStoredproc, Line 0
Column or parameter #3: Cannot find data type udtcol3

Can you have user defined data types in stored procedures.

Store Procedure creation text

CREATE PROCEDURE spStoredproc
@col1 udtcol1,
@col2 udtcol2,
@col3 udtcol3
AS
INSERT INTO tblTempEmployee
(col1 , col2 , Col3)
VALUES (@col1 , @col2, @col3)
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS OFF
GO

Dave

View 3 Replies View Related

Change Owner Of User Defined Data Types?

Jan 11, 2006

Hello is there a way to change the owner of a user defined data type in sql2000? If so help is appreciated

View 1 Replies View Related

Change Ownership For User Defined Data Types

May 10, 2006

Hello,

Does anybody know an easy way to change User Defined Data Types ownership to “dbo”, without dropping dependent objects?

I know that there is a st. proc sp_changeobject owner, but it does not deal with this particular db objects. Is there a similar st.procedure or script that would do the same operation?

Thank you

View 2 Replies View Related

How To Share User-defined Data Type In Different Database?

Nov 6, 2006

Hi,everyone.

I have defined a data type "OC_BUN_NAME" in database "CVPRO". I want to create a table tempdb.dbo.CVPRO in SQL SERVER 2005 system Database tempdb. But SQL SERVER 2005 DBMS gives a Error Messages:"Can not find the data type OC_BUN_NAME".

How can I do? How to use data types in the other database?

Please give me some advice. Thank you in advance.

View 7 Replies View Related

Changing Ownership Of User Defined Data Types?

May 14, 2008

We have a database that originally had all objects owned by a vendor id. We are in the process of changing the ownership to 'dbo' and wish to remove the vendor id but have found there are a few user defined datatypes that are also owned by this vendor id. How can I change the ownership of them to 'dbo'? They are currently being used in some of the tables. I tried the sp_changeobjectowner but that doesn't work for these.

thanks for any advice!

View 3 Replies View Related

Changing The Owner Of A User-defined Data Type

Jul 20, 2005

Hi GuysWonder if you could help me.Basically I produce an accounts package that uses a SQL 2000 DB as theRDBMS. I always instruct users to login as 'sa' and the relevantpassword when doing an update to my program, as sometimes I need to dodatabase changes for new stuff.Found that one of my users has not only logged in with their loginname (in this case Edward), but have also made this login a 'db owner'so that when I created 2 new user-defined data types they belong toEdward rather than dbo.This must have happened a long time ago, but now that they want tomove Edward round the roles and/or delete him from a copy of thedatabase that they have, they can't because he's the owner of theseuser-defined types.This brings me to the reason for my post, how can I change the ownerfrom Edward to dbo for these data types? I found an article ontechnet of how to do this, but when it suggests changing myuser-defined type to standard format it doesn't seem to work.Any ideas?RgdsRobbie

View 1 Replies View Related

Using IPAddress Object In A User Defined Data Type

Sep 29, 2006

Im trying to use a .NET IPAddress in my UDT. i create the assembly, but i get this error when i try to create the type in SQL Server:



Type "IPAddress.IPAddressUDTType" is marked for native serialization, but field "address" of type "IPAddress.IPAddressUDTType" is not valid for native serialization.





From what i understand about the IPAddress object in .NET it is serializable. what am i doing wrong



( note, im just doing this for research purposes and not planing to use the UDT)





View 3 Replies View Related

Transact SQL :: Getting NULL For User Defined Data Types

Nov 27, 2015

I have a table RequestUtility with 3 columns Vendor, name, system. I want to call this table from BizTalk so that I send multiple vendors and will get corresponding Vendor, name and system for that. As I need to send multiple vendors at the same time I have created a User-Defined Table type VendorNames with a single column Names. and have written a SP :

ALTER PROCEDURE [dbo].[GetName]
 -- Add the parameters for the stored procedure here
 @vendorN VendorNames ReadOnly
AS
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
    -- Select statements for procedure here
 SELECT * from dbo.RequestUtility where Vendor IN (SELECT Names from @vendorN)

This is working fine when the value for vendor is present in the table. But if the value is not present it is not returning any thing. But, my requirement is that for example vendor is Dummy which is not present in the table so it should return

Vendor   Name  System
Dummy  NULL   NULL

But currently it is not returning anything.

View 7 Replies View Related

Edit User-defined Data Type In Microsoft SQL Server

Mar 20, 2006

Hi,

I created a user-defined datatype in Microsoft SQL server using the Enterprise Manager. But, I am not able to find options to edit this data type. There are options to delete but not for editing an existing user defined data type.

Can any one help me how to edit this user defined datatype ?

Also, are there any better ways to create and manage user defined data types in MS SQL ?


Thanks in advance ..

-Sudhakar

View 5 Replies View Related

Question About User Defined Data Type And 'COLLATE SQL_Latin1_General_CP1_CI_AS'

Apr 8, 2004

What is 'COLLATE SQL_Latin1_General_CP1_CI_AS'? I am new to
SQLServer, and couldn't find much information on the Web. Also,
I am trying to understand user defined data types. For example,

In the following example, what is '[Price_DT]' data type? and how would
it be referenced at the time of 'INSERT'.

CREATE TABLE [dbo].[Dist_Orders_Master_Index] (
[SubTotal] [Price_DT] NOT NULL ,
[Tax] [Price_DT] NOT NULL
) ON [PRIMARY]

View 1 Replies View Related

SQL 2012 :: Nested Tables And User Defined Data Types?

Jun 5, 2015

I have a requirement of creating nested tables in SQL server. how to create them. Just to give a background I am trying to move the RDBMS from oracle to SQL server.

Structure of tables is as follows. I have table 'Employees' with address as one of the column. I have one more table with columns Street, Town, Dist, State. When I query the table 'Employees' I should see the attribute name and values of all the columns in address table in address column.

Employees: with columns: ID, FirstName, LastName, dept, gender, dob, address

Address (Nested table): with columns : Street, Town, Dist, State

This was done in oracle using Nested tables and user defined data types. what is alternative for this in SQL server. How can I achive this requirement in SQL server.

View 2 Replies View Related

User Defined Data Type Used In Stored Procedure Parameters

Jul 23, 2005

I have several stored procedures with parameters that are defined withuser defined data types. The time it takes to run the procedures cantake 10 - 50 seconds depending on the procedure.If I change the parameter data types to the actual data type such asvarchar(10), etc., the stored procedure takes less that a second toreturn records. The user defined types are mostly varchar, but someothers such as int. They are all input type parameters.Any ideas on why the stored procedure would run much faster if notusing user defined types?Using SQL Server 2000.Thanks,DW

View 13 Replies View Related

User Defined Data Type Issues After Upgrade To 2005

Mar 13, 2008

Hi,

After upgrading to MSSQL 2005 we have stored procedures which get this error:

Msg 2715, Level 16, State 7, Procedure prc_sel_accruals, Line 37
Column, parameter, or variable #15: Cannot find data type umoney.

The user data type is defined in the database. I tried recompiling the SP, which suucceeds but then fails on execution.

The SP works under SQL2000. Is there anything we need to do after the conversion?

View 6 Replies View Related

Question For Creation The User Defined Data Type Easily Like Mysql

Sep 29, 2006

Hello, I cannot find out to create enum data type in SQL Server 2005 Express. Can I easily create the enum type just like the MySQL does.(please the MySQL example below)

CREATE TABLE myTable
(
myid INT UNSIGNED NOT NULL,
myclass ENUM('FIRST','SECOND','THIRD') DEFAULT 'FIRST'
PRIMARY KEY(myid)
);

View 1 Replies View Related

Copy View And User Defined Functions To Another Database Without Replacing Data

Mar 5, 2008

I work on a copy of SQL Server Express on my desktop. After modifying and creating views and user defined functions, I would like to copy and paste them into the working database. Is there a method programmatically of doing this or must I copy and paste the t-sql language from the existing view to the new database--then save the new view on the working database?

View 6 Replies View Related

Script Task User Defined Variable And Data Reader Source SQL Statement

Jan 12, 2007

Hello,

Please can anyone tell me if or how I have to cross reference to a user defined variable from a script task in the SQL of a Data Reader Source?



The script task creates a variable for the last Sales Ledger Session. The SQL in the data reader source that updates the DW sales invoice lines file based upon those invoice lines where the session number is greater than the value from the script task.



The SQL command in the custom properties doesn't cross reference back to the variable.



If anyone can help or needs further detail to help please post a resonse.

Thanks,

Marcus Simpson

View 4 Replies View Related

Using User Defined Data Type (UDT) In A Sql Server 2005 Database + Window Application.

Feb 18, 2006

Hi,

I am using VS2005 C# + sql server 2005 Express edition.

I need to using a database that uses my own defined data types to define its tables columns.

I already have designed a Database projact and create the new UDT as follows:



Create a new Database project in the Visual C# language nodes.


Add a reference to the SQL Server 2005 database that will contain the UDT.


Add a User-Defined Type class.


Write code to implement the UDT.


Select Deploy from the Build menu.

Now I need to ask some quistions:

1- When I try to add a new query to a table that contains my new data type in its columns,if I try to exexute the query the next message appears:

'Execution of user code in the .Net framework is disabled. Enable "clr enabled" configuration option'.

How can I doing that??

2- I need to use that database - which has the new data type - in a traditional ' Visual C# Windows Application' instead of 'Database', but:

when I try to add a new Data Source that contains the tables that have the new data types in its definitions, the next message appears:

'<AyaDatabase.dbo.MyNewUDTTable>

User-defined types(UDTs)are not supported in the Dataset Designer.'

So, how can I resolve that problem??

please help me.

Thanks in advance for any help.

Aya.



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

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

Changing User Defined Data Type's Data Type

Sep 12, 2006

Hi,



I have a user defined data type which is called DEmployeeName,

it's length is varchar(20), how do i change it into varchar(30) without droping it?

I'm using SQL server 2005.

Thanks in advance..

View 1 Replies View Related

Pull Between 2 Dates From User Input

Feb 14, 2007

 I want to pull dates from my database that are between to set dates i have written a query that does this which looks like this:
"SELECT OCH_ID, empno, Selected_OCD, Start_Time, End_Time, Selected_OCDay, Selected_DOM, Selected_Month, Selected_Year FROM dbo.ICT_On_Call_Hours                           WHERE (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) >= CONVERT (datetime, LEFT ('12/02/2007', 2) + '/' + SUBSTRING('12/02/2007', 4, 2) + '/' + RIGHT ('12/02/2007', 4))) AND (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) < CONVERT (datetime, LEFT ('14/02/2007', 2) + '/' + SUBSTRING('14/02/2007', 4, 2) + '/' + RIGHT ('14/02/2007', 4)))"
This works when the dates are included in the statement but when i try and use parameters to pull them in like this:
"SELECT OCH_ID, empno, Selected_OCD, Start_Time, End_Time, Selected_OCDay, Selected_DOM, Selected_Month, Selected_Year FROM dbo.ICT_On_Call_Hours                           WHERE (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) >= CONVERT (datetime, LEFT ('@Choice1', 2) + '/' + SUBSTRING('@Choice1', 4, 2) + '/' + RIGHT ('@Choice1', 4))) AND (CONVERT (datetime, LEFT (Selected_OCD, 2) + '/' + SUBSTRING(Selected_OCD, 4, 2) + '/' + RIGHT (Selected_OCD, 4)) < CONVERT (datetime, LEFT ('@Choice2', 2) + '/' + SUBSTRING('@Choice2', 4, 2) + '/' + RIGHT ('@Choice2', 4)))"
I then recieve the following error message why is this?
ERROR ------------------->  Syntax error converting datetime from character string.
Any Help would be greatly appreciated thanks

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







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