Just getting started with SQL Server CLR integration. Any suggestions on how to organize code? Should I keep my CLR Stored Procedures in a separate project than the code that will call them? Should each DB have its own project containing all the CLR code?
Maybe there is no right way...I'm just looking for a good way.
Thanks!
Hello Frdz, I have doubt regarding the datatypes fields used in SQL SERVER 2005.I want to use the field to store the mobile number of the user .Which datatype should i used ?? The value for bigint Int64 is 18The value of int Int32 is 9/10Now,if in int i write : 1234567890 (accepted)This gives error : 9874565656 (not accepted..........why is it so ??? )Why is it so ??I want to know the perfect size of all the datatypes used in SQLSERVER 2005.There are also smallint,tinyint.....What's the main difference with all of them ??Can anyone provide me the nice links which can explain me what m i asking in this post...Please help me....I want to know all the datatypes used differences...
View 1 Replies View RelatedDoes anyone know how to call a SQL store procedure that return a value to the page?
I've a simple data entry aspx page with several textboxes and a save button. When user fill out the form and click save/submit, it calls a store procedure to insert a row into a SQL table and automatically generate an ID that need to return the the page to display for the user.
Are there a similar article somewhere?
Thank you all!
The problem is as subject line shown.
Please Help.
Thanks for all.
J.H.
Hi,
Is anyone has store procedure that find city and zipcode by radius (10 miles, 30 miles, 50 miles.......)
and also table that has all zipcodes. I finded one, but it's look like very old one, not updated since 2005
Thanks
Kate
hi
I am trying to store the output of sp-executesql into a variable to implement it as a user defined function later
The function is
ALTER function [dbo].[UnitsAvailable] (@id int)
returns int
as
begin
declare @sql nvarchar(100)
declare @params nvarchar(500)
declare @count nvarchar(10)
set @sql = N'Select count(*) from units where projectid=' + convert(varchar,@id) + 'and sold=0 and displayunit=1'
set @params = N'@countOUT nvarchar(10) OUTPUT';
exec sp_executesql @sql, @params, @countOUT=@count OUTPUT;
return @count
end
The result is that I am able to parameterize the sql end execute with the right result. The only problem is that the value is not stored in the variable @count. I could get to the same result using managed code in sql 2005 but still I am curious to find out where the problem is ....
Can you please help?
Thanks Alex
I have a code that is working, and want to create a store procedure in order to put the information of the running code into a new table.
View 7 Replies View RelatedHi all,
I spent about an hour searching the forums and the web but could not find a solution. Bob Bojanic, if you are around can you answer?
I have a DB that stores WW data for company names in a varchar
I don't have control over this DB, other than to pull data from it
I have an SSIS package that grabs WW data in a single pull using a system account
I have an Execute SQL task that runs a sproc to stage the data
I have a Data Flow Task that then copies the data to another server (where I need it)
The destination columns are nvarchar. The source columns are varchar.
Some countries (such as Korea and China) end up with ASCII gibberish (because of code page issues).
I have a solution that involves pulling the data, BCP the pulled data to a text file, then re-import with the correct code page, and delete the gibberish. BUT, I'd like to do this as part of the pull if possible (without any data duplication).
I've tried modifying the CodePage properties for both the Staging step (Execute SQL task running a sproc) and the Source & Dest columns for the Data Flow Task with no luck. Can anyone assist? Thanks much!
Brian
Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here:
Function Main()
on error resume next
dim cn, i, rs, sSQL
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
set rs = DTSGlobalVariables("SQLstring").value
for i = 1 to rs.RecordCount
sSQL = rs.Fields(0).value
cn.Execute sSQL, , 128 'adExecuteNoRecords option for faster execution
rs.MoveNext
Next
Main = DTSTaskExecResult_Success
End Function
This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)
Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:
public Sub Main()
...
Dts.TaskResult = Dts.Results.Success
End Class
I get the following error when I attempt to compile this:
Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.
I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.
- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).
Given this statement:
dim cn, i, rs, sSQL
I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"
set rs = CreateObject("ADODB.Recordset")
This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!
Dear Friends,
I am having 2 Tables.
Table 1: AddressBook
Fields --> User Name, Address, CountryCode
Table 2: Country
Fields --> Country Code, Country Name
Step 1 : I have created a Cube with these two tables using SSAS.
Step 2 : I have created a report in SSRS showing Address list.
The Column in the report are User Name, Address, Country Name
But I have no idea, how to convert this Country Code to Country name.
I am generating the report using the Layout tab. ( Data | Layout | Preview ) Report1.rdl [Design]
Anyone help me to solve this issue. Because, in our project most of the transaction tables have Code and Code description in master table. I need to convert all code into corresponding description in all my reports.
Thanks in advance.
Regards
Ramakrishnan
Singapore
28 March 2007
Hello,
I'm using ASP.Net to update a table which include a lot of fields may be around 30 fields, I used stored procedure to update these fields. Unfortunatily I had to use a FormView to handle some TextBoxes and RadioButtonLists which are about 30 web controls.
I 've built and tested my stored procedure, and it worked successfully thru the SQL Builder.The problem I faced that I have to define the variable in the stored procedure and define it again the code behind againALTER PROCEDURE dbo.UpdateItems
(
@eName nvarchar, @ePRN nvarchar, @cID nvarchar, @eCC nvarchar,@sDate nvarchar,@eLOC nvarchar, @eTEL nvarchar, @ePhone nvarchar,
@eMobile nvarchar, @q1 bit, @inMDDmn nvarchar, @inMDDyr nvarchar, @inMDDRetIns nvarchar,
@outMDDmn nvarchar, @outMDDyr nvarchar, @outMDDRetIns nvarchar, @insNo nvarchar,@q2 bit, @qper2 nvarchar, @qplc2 nvarchar, @q3 bit, @qper3 nvarchar, @qplc3 nvarchar,
@q4 bit, @qper4 nvarchar, @pic1 nvarchar, @pic2 nvarchar, @pic3 nvarchar, @esigdt nvarchar, @CCHName nvarchar, @CCHTitle nvarchar, @CCHsigdt nvarchar, @username nvarchar,
@levent nvarchar, @eventdate nvarchar, @eventtime nvarchar
)
AS
UPDATE iTrnsSET eName = @eName, cID = @cID, eCC = @eCC, sDate = @sDate, eLOC = @eLOC, eTel = @eTEL, ePhone = @ePhone, eMobile = @eMobile,
q1 = @q1, inMDDmn = @inMDDmn, inMDDyr = @inMDDyr, inMDDRetIns = @inMDDRetIns, outMDDmn = @outMDDmn,
outMDDyr = @outMDDyr, outMDDRetIns = @outMDDRetIns, insNo = @insNo, q2 = @q2, qper2 = @qper2, qplc2 = @qplc2, q3 = @q3, qper3 = @qper3,
qplc3 = @qplc3, q4 = @q4, qper4 = @qper4, pic1 = @pic1, pic2 = @pic2, pic3 = @pic3, esigdt = @esigdt, CCHName = @CCHName,
CCHTitle = @CCHTitle, CCHsigdt = @CCHsigdt, username = @username, levent = @levent, eventdate = @eventdate, eventtime = @eventtime
WHERE (ePRN = @ePRN)
and the code behind which i have to write will be something like thiscmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@eName", ((TextBox)FormView1.FindControl("TextBox1")).Text);cmd.Parameters.AddWithValue("@ePRN", ((TextBox)FormView1.FindControl("TextBox2")).Text);
cmd.Parameters.AddWithValue("@cID", ((TextBox)FormView1.FindControl("TextBox3")).Text);cmd.Parameters.AddWithValue("@eCC", ((TextBox)FormView1.FindControl("TextBox4")).Text);
((TextBox)FormView1.FindControl("TextBox7")).Text = ((TextBox)FormView1.FindControl("TextBox7")).Text + ((TextBox)FormView1.FindControl("TextBox6")).Text + ((TextBox)FormView1.FindControl("TextBox5")).Text;cmd.Parameters.AddWithValue("@sDate", ((TextBox)FormView1.FindControl("TextBox7")).Text);
cmd.Parameters.AddWithValue("@eLOC", ((TextBox)FormView1.FindControl("TextBox8")).Text);cmd.Parameters.AddWithValue("@eTel", ((TextBox)FormView1.FindControl("TextBox9")).Text);
cmd.Parameters.AddWithValue("@ePhone", ((TextBox)FormView1.FindControl("TextBox10")).Text);
cmd.Parameters.AddWithValue("@eMobile", ((TextBox)FormView1.FindControl("TextBox11")).Text);
So is there any way to do it better than this way ??
Thank you
Hi all,
Could someone tell me if custom code function can capture the event caused by a user? For example, onclick event on the rendered report?
Also, can custom code function alter the parameters of the report, or refresh the report?
Thanks.
Hi,I need some help here. I have a SELECT sql statement that will query the table. How do I get the return value from the sql statement to be assigned to a label. Any article talk about this? Thanks geniuses.
View 2 Replies View RelatedMy store Procedure is not save in Strore Procedure folder at the time of saving it give me option to save in Project folder and file name default is SQLQuery6.sql when i save it after saving when i run my store procedure
exec [dbo].[SP_GetOrdersForCustomer] 'ALFKI'
I am getting below error :
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.SP_GetOrdersForCustomer'.
Hi:
My service broker is working with 2 different instances in local server.But could not able to get working on 2 different servers because of Conversation ID cannot be associated with an active conversation error which I have posted.
After I receive the message successfully...in the end I get this message sent...
<Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error">
<Code>-8462</Code>
<Description>The remote conversation endpoint is either in a state where no more messages can be exchanged, or it has been dropped.</Description>
</Error>
Why am i gettting this error after the conversation.
Thanks,
Pramod
Hi All,
Recently in an SSIS package I am getting the following error for a particular Data flow task.
Error: 2008-01-25 12:01:48.58
Code: 0xC0202009
Source: Import Datasynapse Data User Events Source [3017]
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.
End Error
Error: 2008-01-25 12:01:48.73
Code: 0xC004701A
Source: Import Datasynapse Data DTS.Pipeline
Description: component "User Events Source" (3017) failed the pre-execute phase and returned error code 0xC0202009.
End Error
Our guess is when the data size of User Events table is more it throws this error. If we try to transfer small subset of data it succeeds. What could be reason for this error?
Since this is very urgent, immediate response would be very much appreciated.
Thanks & Regards,
Prakash Srinivasan
I know I can call store procedure from store procedure but i want to take the value that the store procedure returned and to use it:
I want to create a table and to insert the result of the store procedure to it.
This is the code: Pay attention to the underlined sentence!
ALTER PROCEDURE [dbo].[test]
AS
BEGIN
SET NOCOUNT ON;
DROP TABLE tbl1
CREATE TABLE tbl1 (first_name int ,last_name nvarchar(10) )
INSERT INTO tbl1 (first_name,last_name)
VALUES (exec total_cash '8/12/2006 12:00:00 AM' '8/12/2006 12:00:00 AM' 'gilad' ,'cohen')
END
PLEASE HELP!!!! and God will repay you in kind!
Thanks!
Hi allI am using .jpg images in my application, I am currently storing all my images in a file location, and placing their file path in the database. The problem with this is that I cannot display the images in crystal reports (version with vs.net 2005). I can however display the images if I store them as bytes in an image data type in my db.So the question is should I store either the file path of the bytes of the image in the database table? What are the contraints, I know that size is one, but will speed & performance be affected?Mike55.
View 2 Replies View RelatedI want to store only dd-MMM part of date in a DateTime field. Is it possible? If I take a varchar field, how to query while converting it to date.
View 4 Replies View RelatedHi everybody.
I am using SQL Server 2000.
My users table looks lie this:
userID (primary key)
firstName
lastName
userName
userCode
Industry
Company
Office
dateCreated
Password
…
Every time that I create a new user I have to create userCode (for internal use).
userCode should be created from first letter of the FirstName, first letter from LastName and a number.
Example:
firstName: lastName: userCode:
Joe Blow jb1
Jake Brinks jb2
Ann Smith as1
…
If same userCode already exist the in the same Industry and in the same Company and in the same Office then createCode function should increment the number (the third character of the userCode) up to 9
I never did this before.
Any help would be highly appreciated.
Alex.
I need some help writing my request, I tried both of the following but it does'st seem to work. Your sugestion would be appreciated.SELECT count(LeadId) FROM dbo.Cl_Leads Where AccntMng=@AccntMng and (Status = 'Won' or Status = 'Lost') and InsertDate BETWEEN @dtStart AND @dtEnd
I also tried
SELECT count(LeadId) FROM (SELECT * FROM dbo.Cl_Leads WHERE InsertDate BETWEEN @dtStart AND @dtEnd)Where AccntMng=@AccntMng and (Status = 'Won' or Status = 'Lost') This return and error: Incorrect syntax near the keyword 'Where'.
Hi guysi would really appreciate your help here. what am i doing wrong with this stored procedure ALTER PROCEDURE usp_CreateNewCustomer( @LName as varchar(50), @FName as varchar(50), @Dept as varchar(50)=NULL, @PhoneType as varchar(50)=NULL, @Complete as Bit=NULL, @CustomerID as Int OUTPUT, @FaxModel as varchar(50)=NULL, @FaxNumber as varchar(50)=NULL, )ASINSERT INTO [CustomerInfo] ([LName], [FName], [Dept], [PhoneType], [Complete]) VALUES (@LName, @FName, @Dept, @PhoneType, @Complete)SELECT SCOPE_IDENTITY()INSERT INTO Extras (CustomerID, FaxModel, FaxNumber) VALUES (@CustomerID, @FaxModel, @FaxNumber)RETURN It keeps on complaning "'usp_CreateNewCustomer' expects parameter '@CustomerID', which was not supplied."thanks
View 4 Replies View RelatedHi all,
I have a few question regarding SPROC. Firstly, I want to create a sp, that will return a multiple column of data, how do you achieve that. Secondly, I want to create a store procedure that will return a multiple columns in a cursor as an output variable. Any help will be much appreciated. Can you have more then 1 return parameters???
Thanks.
Kabir
In an ASP.NET app, where is the preferred place (or your preferred place, I should say) to store any SQL Statements (updates, selects, stored procedure calls, etc.) you have? I have been using a key/value pair in AppSettings in my web.config but just feel like that isn't the best way. What say you?
View 4 Replies View RelatedI have asp.net web application which interface with SQL server,
I want to run store procedure query of SQL using my asp.net application.
How to declare connectons strings, dataset, adapter etc to run my store procedure resides in sql server.
for Instance Dim connections as string,
Dim da as dataset, Dim adpt as dataadapter. etc
if possible , then show me completely code so I can run store procedure using my button click event in my asp.net application
thank you
maxmax
hi everyone :)
I've got a store procedure(sp) on the database side ( MS SQL 2000 )
and want to access the sp via ASP.net
so simply I have got few textboxs, which I am getting the values from and I need to pass this data to my sp and get an output of a string from my sp.
I've tested the SP and it does work fine
can you just give me a template of how to access the sp from ASP.net
mant thanks indeed
M
I'm creating a table for maintenance records.
In each record many of the values are simply checkboxes.
In the database for these attributes, is a good way to store the state of these checkboxes as simple as 0 for false, 1 for true?
-David
Hi All,
I want to store files (any kind of file) in DB and retrieve them.What is the best method to do it and also please post sample code as I have never done that.
Thanks,
Kumar.
I want to learn Store procedure whats the best way to start!
Any idea!
Thx
Hi,
I'm developing a site in asp.net and one of the main parts is to manage a timesheet.
Now the problem i'm facing is; how do I save this timesheet in a database in a way that it's easy to save and easy to query from.
I'm thinking about problems as i.e. 1st of a month is middle of the week, so I can't save it like this:
Month Week Mon Tue Wed Thu Fri Sat Sun
1 5 .. .. .. .. .. .. ..
2 1 .. .. .. .. .. .. ..
Anyway, I got stuck to find the best method, maybe someone has made something like this?
Thanks in advance!
Pieje
I am doing an insert and I get the error "Cast from type 'DBNull' to type 'Integer' is not valid." if the contact name already exist.
I dont see my error so if someone could enlight me I would appreciate.
Thanks
"****************STORE PROC***********************
ALTER procedure dbo.Add_Cl_Contact
(
@ContactNamenvarchar(75),
@Departmentnvarchar (50)=null,
@Titlenvarchar(50)=null,
@Phone1char(20)=null,
@Phone2char(20)=null,
@Phone3char(20)=null,
@Ext1char(10)=null,
@Ext2char(10)=null,
@Ext3char(10)=null,
@Faxnvarchar(50)=null,
@Emailnvarchar(50)=null,
@ContactTypeIDint=null,
@Resultinteger OUTPUT
AS
if Exists
(
Select *
From Cl_Contacts
Where ContactName = @ContactName
)
Return 1
Else
insert into Cl_Contacts (
ContactName,
Department,
Title,
Phone1,
Phone2,
Phone3,
Ext1,
Ext2,
Ext3,
Fax,
ContactTypeID,
Email
)
values (
@ContactName,
@Department,
@Title,
@Phone1,
@Phone2,
@Phone3,
@Ext1,
@Ext2,
@Ext3,
@Fax,
@ContactTypeID,
@Email
)
Select @Result = SCOPE_IDENTITY()
return 0
"****************END STORE PROC***********************
'******************************************************************
'******************************************************************
Public Function Add_Cl_Contact(ByVal strContactName As String, _
ByVal strDep As String, ByVal strEmail As String, ByVal strExt1 As String, ByVal strExt2 As String, _
ByVal strExt3 As String, ByVal strFax As String, ByVal strPhone1 As String, ByVal strPhone2 As String, _
ByVal strPhone3 As String, ByVal strTitle As String, ByVal iContactTypeId As Integer, _
ByRef iResult As Integer, ByRef strError As String) As Boolean
'******************************************************************
Dim bSuccess As Boolean = True
Dim connect As New SqlConnection(strConnection)
Dim cmdSelect As New SqlCommand("Add_Cl_Contact", connect)
Dim paramReturnValue As SqlParameter
cmdSelect.CommandType = CommandType.StoredProcedure
'PARAM
cmdSelect.Parameters.Add("@ContactName", strContactName)
cmdSelect.Parameters.Add("@Department", strDep)
cmdSelect.Parameters.Add("@Title", strTitle)
cmdSelect.Parameters.Add("@Phone1", strPhone1)
cmdSelect.Parameters.Add("@Phone2", strPhone2)
cmdSelect.Parameters.Add("@Phone3", strPhone3)
cmdSelect.Parameters.Add("@Ext1", strExt1)
cmdSelect.Parameters.Add("@Ext2", strExt2)
cmdSelect.Parameters.Add("@Ext3", strExt3)
cmdSelect.Parameters.Add("@Fax", strFax)
cmdSelect.Parameters.Add("@Email", strEmail)
cmdSelect.Parameters.Add("@ContactTypeID", iContactTypeId)
paramReturnValue = cmdSelect.Parameters.Add("@Result", SqlDbType.Int)
paramReturnValue.Direction = ParameterDirection.Output
paramReturnValue = cmdSelect.Parameters.Add("ReturnValue", SqlDbType.Int)
paramReturnValue.Direction = ParameterDirection.ReturnValue
Try
connect.Open()
cmdSelect.ExecuteNonQuery()
iResult = cmdSelect.Parameters("@Result").Value
connect.Close()
If cmdSelect.Parameters("ReturnValue").Value = 0 Then
strError = "Contact has been added"
Else
strError = strContactName & " already exist!"
bSuccess = False
End If
Catch ex As Exception
bSuccess = False
strError = ex.Message
Finally
If connect.State = ConnectionState.Open Then
connect.Close()
End If
End Try
Return bSuccess
End Function
I am not sure if it is right place to ask the question. I have a store procedure in which funcitons are called and several temp tables are created. When I use sql analyzer, it runs fast. But when I called it from asp.net app, it runs slow and it waits and waits to get data and insert into temp tables and return. Though I have used with (nolock), it still not imprvoed. Any suggestions? Thanks in advance.
NetAdventure
For a reason that I dont see my store proc is always
returning 0 records but if I use a commandType.text instead of
StoredProcedure then I get my results. So I must have some kind
of error somewhere.
Store Proc
ALTER PROCEDURE dbo.Get_Cl_IssuesBySystemID
@SystemId nvarchar(255)
AS
SET NOCOUNT ON
SELECT t1.issueId, t1.IssueTitle,t1.DateCreated,t2.CustomFieldValue
FROM dbo.IssueTracker_Issues t1
LEFT OUTER JOIN dbo.IssueTracker_ProjectCustomFieldValues t2
ON t1.IssueId = t2.IssueId
Where t2.CustomFieldId=5 and t2.CustomFieldValue like '%@SystemId%'
order by t1.DateCreated
SET NOCOUNT OFF
Function
Public Function Get_Cl_IssuesBySystemID(ByVal strSystemId As String) As DataView
Dim objDs As New DataSet
Dim objDv As New DataView
Dim connect As New SqlConnection(strConnection)
'***For CommanType.Text***
Dim strSelect As String
strSelect = "SELECT t1.issueId, t1.IssueTitle,t1.DateCreated,t2.CustomFieldValue" & _
" FROM dbo.IssueTracker_Issues t1" & _
" LEFT OUTER JOIN dbo.IssueTracker_ProjectCustomFieldValues t2" & _
" ON t1.IssueId = t2.IssueId" & _
" Where t2.CustomFieldId=5 and t2.CustomFieldValue like '%" &
strSystemId & "%'" & _
" order by t1.DateCreated"
Dim cmdSelect As New SqlCommand(strSelect, connect)
cmdSelect.CommandType = CommandType.Text
'***End For CommanType.Text***
'***For CommanType.StoredProcedure***
'Dim cmdSelect As New SqlCommand("Get_Cl_IssuesBySystemID", connect)
'cmdSelect.CommandType = CommandType.StoredProcedure
'PARAM
'cmdSelect.Parameters.Add("@SystemId", strSystemId)
'***End For CommanType.StoredProcedure***
Dim objAdapter As New SqlDataAdapter(cmdSelect)
Try
objAdapter.Fill(objDs)
objDv = objDs.Tables(0).DefaultView
Catch objErr As Exception
Throw New Exception(objErr.Message)
End Try
Return objDv
End Function