I'm creating a table for maintenance records.
In each record, many of the values are simply checkboxes (on the UI).
In the database table 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,I have written a stored procedure to store values from a report i generated to the DB. Now there is a column PKID which is the primary key but also needs to be repeated at times. I tried to clear the memory that the same PKID has already been entered for which I wrote another SP. SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[spCRMPublisherSummaryUpdate]( @ReportDate smalldatetime, @SiteID int, @DataFeedID int, @FromCode varchar, @Sent int, @Delivered int, @TotalOpens REAL, @UniqueUserOpens REAL, @UniqueUserMessageClicks REAL, @Unsubscribes REAL, @Bounces REAL, @UniqueUserLinkClicks REAL, @TotalLinkClicks REAL, @SpamComplaints int, @Cost int)ASSET NOCOUNT ON DECLARE @PKID INTDECLARE @TagID INTSELECT @TagID=ID FROM Tag WHERE SiteID=@SiteID AND FromCode=@FromCodeSELECT @PKID=PKID FROM DimTag WHERE TagID=@TagID AND StartDate<=@ReportDate AND @ReportDate< ISNULL(EndDate,'12/31/2050')IF @PKID IS NULL BEGIN SELECT TOP 1 @PKID=PKID FROM DimTag WHERE TagID=@TagID AND SiteID=@SiteIDDECLARE @LastReportDate smalldatetime, @LastSent INT, @LastDelivered INT, @LastTotalOpens Real, @LastUniqueUserOpens Real, @LastUniqueUserMessageClicks Real, @LastUniqueUserLinkClicks Real, @LastTotalLinkClicks Real, @LastUnsubscribes Real, @LastBounces Real, @LastSpamComplaints INT, @LastCost INT SELECT @Sent=@Sent-Sent,@Delivered=@Delivered-Delivered,@TotalOpens=@TotalOpens-TotalOpens,@UniqueUserOpens=@UniqueUserOpens-UniqueUserOpens,@UniqueUserMessageClicks=@UniqueUserMessageClicks-UniqueUserMessageClicks,@UniqueUserLinkClicks=@UniqueUserLinkClicks-UniqueUserLinkClicks,@TotalLinkClicks=@TotalLinkClicks-TotalLinkClicks,@Unsubscribes=@Unsubscribes-Unsubscribes,@Bounces=@Bounces-Bounces,@SpamComplaints=@SpamComplaints-SpamComplaints,@Cost=@Cost-Cost FROM CrmPublisherSummary WHERE @LastReportDate < @ReportDate AND SiteID=@SiteID AND TagPKID=@PKIDUPDATE CrmPublisherSummary SET Sent=@Sent, Delivered=@Delivered, TotalOpens=@TotalOpens, UniqueUserOpens=@UniqueUserOpens, UniqueUserMessageClicks=@UniqueUserMessageClicks, UniqueUserLinkClicks=@UniqueUserLinkClicks, TotalLinkClicks=@TotalLinkClicks, Unsubscribes=@Unsubscribes, Bounces=@Bounces, SpamComplaints=@SpamComplaints, Cost=@Cost, TagID=@TagID WHERE ReportDate=@ReportDate AND SiteID=@SiteID AND TagPKID=@PKIDENDELSE INSERT INTO CrmPublisherSummary( ReportDate, SiteID, TagPKID, Sent, Delivered, TotalOpens, UniqueUserOpens, UniqueUserMessageClicks, UniqueUserLinkClicks, TotalLinkClicks, Unsubscribes, Bounces, SpamComplaints, Cost, DataFeedID, TagID) VALUES( @ReportDate, @SiteID, @PKID, @Sent, @Delivered, @TotalOpens, @UniqueUserOpens, @UniqueUserMessageClicks, @UniqueUserLinkClicks, @TotalLinkClicks, @Unsubscribes, @Bounces, @SpamComplaints, @Cost, @DataFeedID, @TagID)SET NOCOUNT OFF this is the one to clear: SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER proc [dbo].[spCRMPublisherSummaryClear]( @SiteID INT, @DataFeedID INT, @ReportDate SMALLDATETIME) AS DELETE LandingSiteSummary WHERE SiteID=@SiteID AND ReportDate=@ReportDatebut it doesnt seem to be working.Please suggest.
View 2 Replies View RelatedThe point is, i want to calculate the max id of a table using Aggregate Transformation, then insert some rows with a OLEDB Command and finally , with another OLEDB Command select those rows with id >(max_id) calculated before.
How can i get a value that was calculated before? Can i store it in a variable?
Many Thanks!
What is the most optimized way to store Hashed values (obtained by Object.GetHashCode() method) in SQL Server?
Thanks.
Hi,
In oracle there is "RETURNING" Clause which stores the values inserted to a variable. Is there any option in sql server. i.e storing and fetching takes place at one shot.
eg:
insert into tab (name) values ('hello') returning name into val;
-cheeku
was hoping someone couild provide some insight into a problem I'm trying to solve.
I have a table called SEARCHCRITERIA. It consists of a USERID column and a CRITERIA column. Users will be able to search for other users based on a set of criteria. There are 5 total criteria a user can choose. They can choose as few as none or all five. I'd like to store the criteria chosen as a single number in the SEARCHCRITERIA table. Then use a function to parse out the criteria. For example:
CRITERIAID CRITERIA CRITERIAVALUE
1 AGE 2
2 SEX 4
3 GRADE 8
4 LOCALE 16
5 REGION 32
A user performs a search based on AGE, SEX, and LOCALE. I would then store the value 22 (the sum of 2, 4, and 16) in the SEARCH table. I would then need a function to pull out the three individual values.
Has anyone done anything like this before?
If so, any help would be appreciated!
Thanks in advance!
I'm looking for a way to store a list of values in a variable. The query user will need to input a list of file numbers, and my query will need to perform a couple operations on that same list of values, which is why it seems a variable would be most appropriate.
I can't obtain the list of values from the database as they will have to be entered by the user. I'm imagining storing these in a table variable.... User just copies/pastes the list of values somewhere into the query code and executes as usual.
I try to store a SSIS-configuration value in the registry.
But it's failed. I get a warning, that the registry-key is not found.
What is the syntax ? I have searched in BO,MSDN and Google/Internet ==> nothing.
There is no example.
To All,
I have a table [multiple entries over the web] where the identity field values do not appear in order using a simple SELECT [no ORDER BY]. Why?
HI
I have a problem related Store Procedure, that i am trying to extact a value from Database (Like FirstName,LastName,Email Address) through Store Procedure and Display it in the DropDownList(Like: FirstName LastName ,(xyz@xyz.com)) , and this is working correctly.
Now i try to check the value at the same time if it is NULL value in the Database then pass EmptyString to the DropDownList Like ("" "" ,(xyz@xyz.com))
how i can do that in the store procedure.
Comments will be appreciated.
I am using the varchar data type in sql to store a comma-delimited string of multiple selections from a checkboxlist.
The string only has about 28-30 characters in it, but it maxes out the sql row length and I get the 8060 error message every time.
Here is some of the code:
"Dim industry1list As String
Dim li As ListItem
industry1list = ""
For Each li In industry1.Items
If li.Selected = True Then
industry1list = industry1list & li.Value & ","
End If
Next
....
MyCommand.Parameters.Add(New SqlParameter("@industry1", SqlDbType.VarChar, 60))
MyCommand.Parameters("@industry1").Value = (industry1list)"
I would appreciate any coaching you have for me to get back on track. Thanks, Bob
What is the type of values I have to use to tell the database to store pics?
View 4 Replies View RelatedHi,
I want to store arabic values and dates in sql server database.
I am using visual basic 2005 with sql server 2005. I entered the the arabic values through visual basic forms. But when i open the database table, i see the values in english format.
I change my regional settings of the operating system is arabic (platform vista).
Can you give how to see arabic values.
Thanks.
Regards
Kashif Chotu
Hallo
is it possible to perform an insert/update query upon the execution of a report, using the report Visual Basic expressions?
I mean, the user enters some values for the report parameters, and i would like to store them, along with the resulting recordset data, into a dedicated table in the database.
hi friends
i am using visual studio 2005. i need one combo box which will display two values. one is "Designation" i.e.("clerk,accountant,manager,officer,shopkeeper) and another "Designation ID" i.e.(1,2,3,4,5 and respectively) i made table of "Designation Master" in sql server 2000 including field of "Designation ID, Designation". manually i am inserting id and designation in that table.
what i want exactly i have one customer form which contain a "combobox" for "Designation" column i achieved "Designation ID" and "Designation" in that combo box also but when i will go for save i want that i will store only "Designation ID" i don't want to store "Designation" in my "Designation" column which is in "Customer table" i am attaching code which store "Designation ID" and "Designation" in customer table but i want only "Designation ID" only
bold line which is difficult for me to inserting only "designationId" in customer table.
Inventory_Master.cntData.Open()
cmdstr = "Select DesigID,Designation from Designation_Master"
cmd = New SqlCommand(cmdstr, Inventory_Master.cntData)
cdrCustomer = cmd.ExecuteReader()
If cdrCustomer.HasRows = True Then
While cdrCustomer.Read()
cmddesig = cdrCustomer.Item(0).ToString.PadLeft(1) + cdrCustomer.Item(1).ToString.PadLeft(25)
cbodesignation.Items.Add(cmddesig)
End While
Else
End If
Inventory_Master.cntData.Close()
Inventory_Master.cntData.Open()
cmd = New SqlCommand("insert CUSTOMER(Membership_No,Member_Initial,Member_Name,Member_Birthdate,Gender,Res_Telno,Mobile_No,Res_Addr1,Res_Addr2,Res_City,Res_Pincode,Landmark,Company_Name,Comp_Addr1,Comp_Addr2,Comp_Telno,Comp_City,Comp_Pincode,Designation,Credit_Limit,Income_Mthly,Email_ID,Note) values (@Membership_No,@Member_Initial,@Member_Name,@Member_Birthdate,@Gender,@Res_Telno,@Mobile_No,@Res_Addr1,@Res_Addr2,@Res_City,@Res_Pincode,@Landmark,@Company_Name,@Comp_Addr1,@Comp_Addr2,@Comp_Telno,@Comp_City,@Comp_Pincode,@Designation,Convert(money,@creditlimit),@Income_Mthly,@Email_ID,@Note)", Inventory_Master.cntData)
cmd.Parameters.AddWithValue("@Membership_No", txtmebno.Text)
cmd.Parameters.AddWithValue("@Member_Initial", cbomebname.Text)
cmd.Parameters.AddWithValue("@Member_Name", txtmebname.Text)
cmd.Parameters.AddWithValue("@Member_Birthdate", dtime.Value)
cmd.Parameters.AddWithValue("@Gender", cbogender.Text)
cmd.Parameters.AddWithValue("@Res_Telno", txttelno.Text)
cmd.Parameters.AddWithValue("@Mobile_No", txtmobileno.Text)
cmd.Parameters.AddWithValue("@Res_Addr1", txtresaddr1.Text)
cmd.Parameters.AddWithValue("@Res_Addr2", txtresaddr2.Text)
cmd.Parameters.AddWithValue("@Res_City", txtrescity.Text)
cmd.Parameters.AddWithValue("@Res_Pincode", txtrespin.Text)
cmd.Parameters.AddWithValue("@Landmark", txtlandmark.Text)
cmd.Parameters.AddWithValue("@Company_Name", txtcompname.Text)
cmd.Parameters.AddWithValue("@Comp_Addr1", txtcompaddr1.Text)
cmd.Parameters.AddWithValue("@Comp_Addr2", txtcompaddr2.Text)
cmd.Parameters.AddWithValue("@Comp_Telno", txtcomptelno.Text)
cmd.Parameters.AddWithValue("@Comp_City", txtcompcity.Text)
cmd.Parameters.AddWithValue("@Comp_Pincode", txtcomppin.Text)
cmd.Parameters.AddWithValue("@Designation", cbodesignation.Text.PadLeft(1, CChar(CStr(1)))) [ help me for inserting designation in this line]
cmd.Parameters.AddWithValue("@creditlimit", txtcreditlimit.Text)
cmd.Parameters.AddWithValue("@Income_Mthly", txtincomemthly.Text)
cmd.Parameters.AddWithValue("@Email_ID", txtemailid.Text)
cmd.Parameters.AddWithValue("@Note", rtxtNote.Text)
cmd.ExecuteNonQuery()
Inventory_Master.cntData.Close()
MsgBox("RECORD SAVED")
Hi All,
I have written a stored procedure that has a return value (OUTPUT Parameter) and was wondering if there is any way to retreive this value in SQL Server Reporting Services 2005? I get the result fine, but cannot figure out how to get the return parameter.
Thanks in advance.
Glenn
i design SP for insert data in 2 tables i need to store list of array in one parameter to complete my query i try the table value but it`s not good for me because table value is readonly and i need to insert data with list of array .....
View 5 Replies View RelatedHello--
For extracting the link structure of a dependency network with a large number of nodes (for problems having a large number of variables), we have been using the stored procedure:
System.Microsoft.AnalysisServices.System.DataMining.DecisionTreesDepNet.DTGetNodeGraph('{model-name}', value)
The stored procedure returns a resultset with columns: [Node_type], [Node_unique_name_1], [Node_unique_name_2], and [MSOLAP_NODE_SCORE]
Are there any pointers, references or descriptions of the values of [MSOLAP_NODE_SCORE]?
Thanks,
- Paul
I'd like to create a table that will store different order items. Several order items make up one single order. Order items can have 0 or more children (max depth will never be deeper than one). Order items can have up to 150 attributes/values. The way I think this should be done is using XML column instead of the EAV type of model. My table structure currently looks like this:
* child_order_item_id (PK)
* parent_order_item_id (FK to child_order_item_id)
* order_id (FK to Order table)
* product_id (FK to Product table)
* price
* attribute_XML
How my attribute_XML should look like or how to validate the xml.
I was working on figuring out where a certain application wasstoring the multiple selection choices I was doing through the app.I finally figured out that they were being store in an IMAGEdata type colum with the variable length of 26 bytes.This is the first time I ran into such way of storing multipleselections in a single Image data type.Is this a better alternative than to store into a One-to-Manytables? If so then I'll have to consider using the Image datatype approach next time I have to do something like storing1 to thousands of selections.Thank you
View 4 Replies View RelatedCould someone please tell me why the following SELECT statement...
SELECT ID, SpecimenNr, ScientificName, Locality, TaxonFROM petrander.QueryViewWHERE (InstitutionCode = 1) AND (Collectioncode = 1) AND (ScientificName LIKE N'%le%') AND (Locality LIKE N'%Fakse%') AND (22 IN (ParentID1, ParentID2, ParentID3, ParentID4, ParentID5, ParentID6, ParentID7, ParentID8))
...gives me 9 rows back, but embedding the exact same statement in the following sproc...
set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [petrander].[DynamicQuery] @taxparent int = NULL, @museum int = NULL, @collection int = NULL, @binomen Nvarchar(254) = NULL, @locality Nvarchar(254) = NULLAS SELECT ID, SpecimenNr, ScientificName, Locality, Taxon FROM QueryView WHERE InstitutionCode = COALESCE(@museum, InstitutionCode) AND CollectionCode = COALESCE(@collection, CollectionCode) AND ScientificName LIKE 'N%' + @binomen + '%' AND Locality LIKE 'N%' + @locality + '%' AND (@taxparent IN (ParentID1, ParentID2, ParentID3, ParentID4, ParentID5, ParentID6, ParentID7, ParentID8))
...and passing the exact same parameter values to with the following execute statement...
USE [Geomusdb]
GO
DECLARE @return_value int
EXEC @return_value = [petrander].[DynamicQuery]
@museum = 1,
@collection = 1,
@binomen = N'le',
@locality = N'Fakse'
SELECT 'Return Value' = @return_value
GO
gives me 0 rows!? What is different!?
Any help is greatly appreciated...
I have several reports that are looking for a code within a certain set of codes or ranges. The specific list of codes to be including is determined by the end user. Currently my "IN" statement can be a hundred lines, listing several ranges, lists of specific codes, etc. I am constantly getting asked what codes does it include, is this code included, etc. Sometimes they'll give me a printed 10 page list of codes and want me to compare to what I have included in the report. Not ideal in the slightest.
What I'd like to do is have a table or a file of some kind somewhere where the end user can view the codes contained, add new ones, and delete ones they no longer want. Then I'd like to be able to just reference that file in my IN statement. Leaving the responsibility of listing the correct codes on them.
Is there a way that I can do this at the table level to automatically handle the rounding of seconds, etc. down to the minute automatically without having to use a trigger?
Here is a very basic example of what I am trying to do:
--example: '09-22-2007 15:07:18.850' this is the value inserted into the table by the code
select getdate()
--example: '2007-09-22 15:07:00.000' this is the value I want to store in the table
select dateadd(mi, datediff(mi, 0, getdate()), 0)
found it
View 3 Replies View RelatedHey,
I have MS SQL database.
I have procedure:
code:--------------------------------------------------------------------------------
CREATE PROCEDURE dbo.Reg_DropTable
@ModuleId varchar(10)
AS
declare @TableName varchar, @kiek numeric
set @TableName = 'reg_'+@ModuleId
begin
DROP TABLE @TableName <- HERE I GOT ERROR
end
GO
--------------------------------------------------------------------------------
I got error when using variable with tables names.
How to do this?
Ps. Number is send to this function and it must drop table with name Reg_[That number]
My 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'.
I am receiving funny results from a query. To simplify, I have 2 tables (todayyesterday). Each tbl has the same 8 columns. My query joins the two tables then looks where either of two columns has changed. What is happening is that when checking one of the columns it seems as though sql is flipping the column, causing it to be returned in error.
result set
colA colB colC colD colE colF colG colG (from yesterday)
1 1 a b c d e m
1 1 a b c d m e
So what's happening is that the record above is actually the same record and should not be returned. There is a daily pmt column that changes but I am not using that in the query. Aside from that the two records are identicle.
Any help is appreciated.
Hi,
I have the following situation (with a site that already works and i cannot modify the database architecture and following CrossRef tables -- you will see what i mean by CrossRef tables below)
I have:
Master table Hotel
table AddressCrossRef (with: RefID = Hotel.ID, RefType = 'Hotel', AddrID)
joins
table Address (key = AddrID)
table MediaCrossRef (with RefID = Hotel.ID, RefType= 'Hotel', MediaID)
joins
table Media (with MediaID,mediaType = 'thumbnail')
foreach hotel, there definitely is a crossRef entry in AddressCrossRef and Address tables respectively (since every hotel has an address)
however not all hotels have thumbnail image
hence i have hotel inner join AddressXReff inner join Address ..... however i must have
left outer join mediaXref left outer join media
the problem is that if there is no entry in Media or mediaXref, I don't get any results
i tried to get over it by using
where (media.mediaTyple like 'thumbnail' or media.mediaType is null)
but then i started getting multiple results for each hotel because media's of type movie or full_image or etc... all got returned
any clue?
thanks
How to count the number of values that exist in a row based on the values from an array of numbers. Basically the the array of numbers I want to look for are in row 1 of table [test 1] and I want to search for them and count the "out of" in table [test 2]. Excuse me for not using the easiest way to convey my question below. I guess in short I have 10 numbers and like to find how many of those numbers exist in each row. short example:
Table Name: test1
Columns: m1 (int), m2 (int), m3 (int) >>> etc
Array/Row1: 1 2 3 4 5 6 7 8 9 10
------
Table Name: test2
Columns: n1 (int), n2 (int), n3 (int), n4 (int), n5 (int)
Row 1: 3, 8, 18, 77, 12
Row 2: 1, 4, 5, 7,18, 21
Row 3: 2, 4, 6, 8, 10
Answer: 2 out of 5
Answer: 4 out of 5
Answer: 5 out of 5
Hello all,
I am trying to think my way through a solution which I believe others have probably come across... I am trying to implement a matching routine wherein I need to match an address against a high value and a low value (or, for that matter an input date vs. a start and end date) to return the desired row ... i.e. if I were to use a straight vb program I would just use the following lookup:
"SELECT DISTINCT fire_id, police_ID, fire_opt_in_out, police_opt_in_out FROM ipt_tbl " & _
" WHERE zip_code = @zip_code AND addr_prim_lo <= @street_number AND addr_prim_hi >= @street_number " & _
" AND addr_prim_oe = @addr_prim_oe AND street_pre = @street_pre AND street_name = @street_name " & _
" AND street_suff = @street_suff AND street_post = @street_post " & _
" AND (expiry_date = '' OR expiry_date = '00000000' OR expiry_date > @expiry_date)" & _
" GROUP BY fire_ID, police_ID, fire_opt_in_out, police_opt_in_out"
My question, then, is how would you perform this type of query using a lookup / merge join or script? I have not found a way to implement a way to set the input columns? I can set the straight matches without a problem, i.e. lookup zip code = input zip code, but can't think of the correct way to set comparisons, i.e. lookup value 1 <= input value AND lookup value 2 >= input value
Any suggestions?
thanks for your time...
I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?
View 12 Replies View RelatedI am working with a data set containing several years' of monetary values. I have entries for past dates and the associated values, and I also have entries for future dates. I need to populate the values of the future date records with the values from the same date the previous year. Is there any way this can be done in Power Pivot?
View 6 Replies View RelatedI have a string variable
string str1="1,2,3,4,5";
I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :
declare @id varchar(50)
set @id= '3,4,6,7'
set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype
select *from ehsservice where id in(@id)
But this query throws following error message:
Conversion failed when converting the varchar value '3,4,6,7' to data type int.