MS SQL Server :: Help With Creating SQL Statement To Get Data From Single Table...

Creating A Stored Procedure That Will Summarize Data In A Table Into A Table Reflecting Period Data Using An Array Type Field
I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.

Any help would be greatly appreciated.

Current Table

Project   | Task      | Category | Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1     | Task 1   | Cat 1      | 2007           | 01                |   40
Proj 1    | Task 1   | Cat 2      | 2007           | 02               |   20
Proj 1    | Task 1   | Cat 3      | 2007           | 03                |   35
Proj 1    | Task 1   | Cat 1      | 2008           | 01                |   40
Proj 1    | Task 1   | Cat 2      | 2008           | 02                |   40
Proj 1    | Task 1   | Cat 3      | 2008           | 03                |   40

Proposed Table

Project   | Task      | Category   | Fiscal Month 01   | Fiscal Month 02   | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1     | Task 1 | Cat 1        |          40              |           0               |         0              |    2007
Proj 1    | Task 1  | Cat 2        |           0               |         20               |         0              |    2007Proj 1    | Task 1  | Cat 3        |           0               |           0               |        35             |    2007
Proj 1    | Task 1  | Cat 1        |         40               |           0               |          0             |    2008

Proj 1    | Task 1  | Cat 2        |           0               |         40               |          0             |    2008
Proj 1    | Task 1  | Cat 3        |           0               |           0               |        40             |    2008

Thanks,
Mike Misera

View Replies !

Creating Table With A Select Statement
Dear folks,

create table temptable(eno, ename) as select eno, ename from emp.

here the problem is it is asking for the datatype for the temporary table.

is it not possible to create the temp table without providing the datatypes?

thank you very much.

Vinod

View Replies !

Retrieving Hierarchical Data From A Single Table
I would like to retrieve a hierarchical list of Product Categories from a single table where the primary key is a ProductCategoryId (int) and there is an index on a ParentProductCategoryId (int) field. In other words, I have a self-referencing table. Categories at the top level of the hierarchy have a ParentProductCategoryId of zero (0). I would like to display the list in a TreeView or similar hierarchical data display control.Is there a way to retrieve the rows in hierarchical order, sorted by CategoryName within level? I would like to do so from a stored procedure. Example data:ProductCategoryID CategoryDescription ParentProductcategoryID ParentCategoryDescription Level------------------------------------------------------------------------------------------------------------------------------------------------1 Custom Furniture 0 02 Boxes 0 03 Toys 0 04 Bedroom 1 Custom Furniture 15 Dining 1 Custom Furniture 16 Accessories 1 Custom Furniture 17 Picture Frames 6 Accessories 28 Serving Trays 6 Accessories 29 Entertainment 1 Custom Furniture 110 Planes 3 Toys 111 Trains 3 Toys 112 Boats 3 Toys 113 Automobiles 3 Toys 114 Jewelry 2 Boxes 115 Keepsake 2 Boxes 116 Specialty 2 Boxes 1Desired output:Custom Furniture Accessories Picture Frames Serving Trays Bedroom Dining EntertainmentBoxes Jewelry Keepsake SpecialtyToys Automobiles Boats Planes Trains

View Replies !

Displaying Data In Hierarchy From Single Table..
Hi,

    I like to get data from a signle table and arranged in hierarchical(hierarchy) order. What will be my sql script to be able to get the desired result shown below? Please include some explanation as too what script is doing..

Table Structure and Sample Data

Id    ParentId    Name                   Code       DisplayOrder
1      null           Group 1                G00001           1
2      null           Group 2                G00002           2
3       1             Sub-Group 1         SG0001          1
4       2             Sub-Group 2         SG0002          1
5       3             Sub-Sub-Group 1 SSG001          1
6     null           Group 3                 G00003          3
7      3            Sub-Sub-Group 2   SSG002          2

Desired Result
Id ParentId   Level Name                    ExtendedName                                                DisplayOrder
1      null         1     Group 1                  Group 1                                                               1
3       1           2     Sub-Group 1           Group 1 -> Sub-Group 1                                        1
5       3           3     Sub-Sub-Group 1    Group 1 -> Sub-Group 1 -> Sub-Sub-Group 1          1
7      3            3     Sub-Sub-Group 2    Group 1 -> Sub-Group 1 -> Sub-Sub-Group 2          2
4       2           2     Sub-Group 2           Group 1 -> Sub-Group 2                                       1
2      null         1      Group 2                 Group 2                                                              2
6     null          1    Group 3                  Group 3                                                               3

View Replies !

Comparing Data In Two Consecutive Rows From A Single Table
I'm trying to come up with an elegant, simple way to compare twoconsecutive values from the same table.For instance:SELECT TOP 2 datavalues FROM myTable ORDER BY timestamp DESCThat gives me the two latest values. I want to test the rate ofchange of these values. If the top row is a 50% increase over the rowbelow it, I'll execute some special logic.What are my options? The only ways I can think of doing this arepretty ugly. Any help is very much appreciated. Thanks!B.

View Replies !

Creating A Table Column That Only Takes Data From Another Table.
I am trying to create a table that holds info about a user; with the usual columns for firstName, lastName, etc.... no problem creating the table or it's columns, but how can I "restrict" the values of my State column in the 'users' table so that it only accepts values from the 'states' table?

View Replies !

Loading The Different Language Data From Excel File To The Single Table
can anyone help me to solve this problem
i have created a ssis package to load the data from excel file to the table, but we are getting the data in different language ie in french,english and in china after loading the data when we view the data it is showing as junk characters for chinese data but we are able to see other language data ie french and english.
so please tell me how to solve that
reply to my mail id(sandeep_shetty@mindtree.com)

View Replies !

Data Flow Task - Multiple Columns From Different Sources To A Single Table
Hi:

I have a data flow task in which there is a OLEDB source, derived column item, and a oledb destination. My source is a SQL command, that returns some values. I have some values, that I define in the derived columns, and set default values under the expression column. My question is, I also have some destination columns which in my OLEDB destination need another SQL command. How would I do that? Can I attach two or more OLEDB sources to one destination? How would I accomplish that? Thanks

MA2005

View Replies !

Creating An MS SQL Data Table Programatically
I use Visual Studio and VB.

If I want to create an SQL data table on the server, I go to Server
Explorer and make a small table with the corerct columns. Then I add
rows programatically as needed from the Application I'm working with.

It would suit me, for a current job, to be able to create the table itself programatically.

I don't know how to do this. Can it be done ? If so, could someone give
me a starter, please ? Four cols with a key in the first.

David Morley

View Replies !

Creating A Table From Data In A SQL Server Db
I have a form with a drop down box so the user can select a quote.. When a quote is selected i need to populate a table of all the records associated with the quote id. I need the table to be created in such a way that the user can add new rows, delete rows and edit the data. Then all of the changes need to be written back to the database. Whats the most efficient/best way of doing this and if you have any ideas can you explain them as thoroughly as possible! I'm currently upgrading an access database to a sql server back end with an asp.net client and it's taking me a while to get to grips with all the changes!Thanks in advance,Chris

View Replies !

Creating A Table Using Data From Another Database
hi, i'm trying to create a table and populate it with data from another database residing on the same server. i've done this on oracle using tables within the same database and am just making a first effort with the added twist of a different database. this is what i've been doing so far...

CREATE TABLE facility_dimension
(
fac_id INT IDENTITY(1,1),
tri_fac_id CHAR(17),
fac_nameVARCHAR(100),
street VARCHAR(100),
city VARCHAR(100),
county VARCHAR(50),
state VARCHAR(4),
longitude REAL,
latitude REAL,
PRIMARY KEY(fac_id)
)
SELECT tri_facility_id, facility_name, street_address,
city_name, county_name, state_abbr, fac_latitude,
fac_longitude
FROM TRI_2004.form_1;

....where TRI_2004 is the other database and form_1 is the table. the result is creation of the new table and then the output of the secondary query. i'm assuming this can even be done but if it can't that would be helpful to know as well. thanks in advance!

View Replies !

Using Result Of A Select Statement From One Table To Add Data To A Different Table.
I have two table with some identical fields and I am trying to populate one of the tables with a row that has been selected from the other table.Is there some standard code that I can use to take the selected row and input the data into the appropriate fields in the other table?

View Replies !

TSQL Statement Extracting Data From One Table Through Another Table
Hi,

I have 2 tables,
MembersTemp and Organisations

I'm trying to extract the organisation Name from the organisations table but am unsure of the sql statement to do this.

Initiallt I only have the ExecID for the MembersTemp table

MembersType table:
ExecID 3013
OrganisationID 4550

Organisation table:
ID 4550 (PK)
Name "Microboff"

Any ideas??

View Replies !

Creating Different Staging Table With Data Extensi
Hi All,

I am extracting source data which is in txt fille to OLE DB destination. But data of each day I want to save in different staging table. For Eg; tblProduct20081206, tblProduct20081207. How can it be done. I have seen lots of posting and script when destination is Txt. I want to use same table for staging but want to create different table for each day with adding date extension.

Please Help

View Replies !

SQL2K SP4 Gives Error 1706 Creating Multi-statement Table-valued Function Names Beginning With &&"sys&&"?
Hi all,

I've created a number of tables, views, sproc, and functions whose names begin with "sys_", but when I tried to create a multi-statement table-valued function with this type of name, I got:

Server: Msg 1706, Level 16, State 2, Procedure sys_tmp, Line 9
System table 'sys_test' was not created, because ad hoc updates to system catalogs are not enabled.

I had a quick look in this forum for 1706 (and on Google) but couldn't find anything. Does anyone know for certain if this is a bug in SQL2K?

Thanks, Jos

Here's a test script:
/*
----------------------------------------------------------------------------------------------------
T-SQL code to test creation of three types of function where the function name begins with "sys_".
Jos Potts, 02-Nov-2006
----------------------------------------------------------------------------------------------------
*/

PRINT @@VERSION
go

PRINT 'Scalar function with name "sys_" creates ok...'
go

CREATE FUNCTION sys_test
       ()
RETURNS       INT
AS
BEGIN
       RETURN 1
END
go

DROP   FUNCTION sys_test
go

PRINT ''
go

PRINT 'In-line table-valued function with name "sys_" creates ok...'
go

CREATE FUNCTION sys_test
       ()
RETURNS       TABLE
AS
RETURN SELECT 1      c
go

DROP   FUNCTION sys_test
go

PRINT ''
go

PRINT 'Multi-statement table-valued function with name "sys_" generates error 1706...'
go

CREATE FUNCTION sys_tmp
       ()
RETURNS       @t     TABLE
       (c     INT)
AS
BEGIN

       INSERT INTO @t       VALUES (1)

       RETURN

END
go

DROP   FUNCTION sys_test
go

PRINT ''
go

/*
----------------------------------------------------------------------------------------------------
*/

And here€™s the output from running the test script in Query Analyser on our server:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
       May 3 2005 23:18:38
       Copyright (c) 1988-2003 Microsoft Corporation
       Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Scalar function with name "sys_" creates ok...

In-line table-valued function with name "sys_" creates ok...

Multi-statement table-valued function with name "sys_" generates error 1706...
Server: Msg 1706, Level 16, State 2, Procedure sys_tmp, Line 11
System table 'sys_tmp' was not created, because ad hoc updates to system catalogs are not enabled.
Server: Msg 3701, Level 11, State 5, Line 2
Cannot drop the function 'sys_test', because it does not exist in the system catalog.

View Replies !

Creating Table With A Date Data Type, Urgent
Hi all,
I'm trying to create a temporary table with one of the columns with a datetime datatype. Sql server 7.0 is giving me an error:
Error 195 date is not a recognized function name.
Example of my create table looks something like this:
create table #temp_table(column_one int, column_two(15,2), Column_three datetime)

View Replies !

Creating Trigger For A Single Column/Field?

Hi all,

My code below creates a trigger that fires whenever a change occurs to a 'myTable' row, but this is not what I want. I only want to log changes made to a single field called 'Charges', when that field is changed I want to log it, can anyone tell me how to modify my code to do this, thanks

Create Trigger dbo.myTrigger
ON dbo.[myTable]
FOR UPDATE
AS
Declare @now DATETIME
Set @now = getdate()

BEGIN TRY
Insert INTO dbo.myAuditTable
(RowImage,Charges,ChangeDate,ChangeUser)
SELECT 'BEFORE',Charges,@now, suser_sname()
FROM DELETED
Insert INTO dbo.myAuditTable
(RowImage,Charges,ChangeDate,ChangeUser)
SELECT 'AFTER',Charges,@now, suser_sname()
FROM INSERTED
END TRY

BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH

View Replies !

Creating A Single View From 2 Existing Views
I have 2 views which contain the following fields:
EVENT,
WEEK,
SUBSCRIPTION,
QTY,
GROSS_AMOUNT,
SEASON

The 2 views differ by SEASON. I'm attempting to combine the 2 views in to a single view or table grouping by EVENT, WEEK and SUBSCRIPTION:

EVENT,
WEEK,
SUBSCRIPTION,
Q6 (qty of season 1),
A6 (gross_amount of season 1),
Q7 (qty of season 2),
A7 (gross_amount of season 2)

Below is my select command:

------

SELECT TOP 100 PERCENT
dbo.vw_SEASON06.EVENT,
SUM(dbo.vw_SEASON06.QTY) AS Q6, SUM(dbo.vw_SEASON06.GROSS_AMOUNT) AS A6,
SUM(dbo.vw_SEASON07.QTY) AS Q7, SUM(dbo.vw_SEASON07.GROSS_AMOUNT) AS A7,
dbo.vw_SEASON06.WEEK,
dbo.vw_SEASON06.SUBSCRIPTION

FROM dbo.vw_SEASON06 FULL OUTER JOIN
dbo.vw_SEASON07 ON dbo.vw_SEASON06.WEEK = dbo.vw_SEASON07.WEEK AND
dbo.vw_SEASON06.SUBSCRIPTION= dbo.vw_SEASON07.SUBSCRIPTION AND
dbo.vw_SEASON06.EVENT = dbo.vw_SEASON07.EVENT

GROUP BY
dbo.vw_SEASON06.EVENT,
dbo.vw_SEASON06.WEEK,
dbo.vw_SEASON06.SUBSCRIPTION

ORDER BY
dbo.vw_SEASON06.EVENT

-----

This creates the view but there are some issues. If an 'EVENT' exists in dbo.vw_SEASON07.EVENT and doesn't exist in dbo.vw_SEASON06.EVENT the value of the field 'EVENT' is set to NULL because the 'EVENT' name is returned from dbo.vw_SEASON06.EVENT. The same issue exists for 'SUBSCRIPTIONS' and 'WEEK'.

How can I create a single view/table that will include all the data from these 2 views without the NULL values in EVENT or SUBSCRIPTION?

Any help is appreciated!

View Replies !

Creating Multiple Databases From A Single Backup F
Hi,
I want to create two databases by restoring from a
single backup file in sql server. I am using 2005-sqlexpress .Is it possible?

Thanx in advance..

View Replies !

Creating Stored Procs That Need To Continusiouly Append To A New Table (this Is To Scrub Data That Is Imported Into DB).
I have 1 table with a huge amount of data that I recive from someone else in a flat file format. I want to be able to filter through that data and scrub it and find out the good data and bad data from it.

I'm scrubbing the data using different stored procs that i've created and through a web interface that the user can pick which records they wish to create.

If I were to create a new table for clean records, what is the syntax to keep Appending to that table through the data that i'm obtainig via the stored procs that i've created.

Any thoughts or suggestions are greatly appriciated in advance

Thanks again in advance
RB

View Replies !

Creating Clustered Index On View With Table Containing XML Data Types Takes Forever And Causes Timeouts
I am trying to create a clustered index on a View of a table that has an xml datatype. This indexing ran for two days and still did not complete. I tried to leave it running while continuing to use the database, but the SELECT statements where executing too slowly and the DML statements where Timing out. I there a way to control the server/cpu resources used by an indexing process. How can I determine the completion percentage or the indexing process. How can I make indexing the view with the xml data type take less time?

The table definition is displayed below.

CREATE TABLE [dbo].[AuditLogDetails](

[ID] [int] IDENTITY(1,1) NOT NULL,

[RecordID] [int] NOT NULL,

[TableName] [varchar](64) NOT NULL,

[Modifications] [xml] NOT NULL,

CONSTRAINT [PK_AuditLogDetails] PRIMARY KEY CLUSTERED

(

[ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

The view definition is displayed below.

ALTER VIEW [dbo].[vwAuditLogDetails] WITH SCHEMABINDING

AS

SELECT P.ID,D.RecordID, dbo.f_GetModification(D.Modifications,P.ID) AS Modifications

FROM dbo.AuditLogParent P

INNER JOIN dbo.AuditLogDetails AS D ON dbo.f_GetIfModificationExist(D.Modifications,P.ID)=1

The definition for UDF f_GetModification

ALTER function [dbo].[f_GetModification]( @Modifications xml,@PID uniqueidentifier )

returns xml

with schemabinding

as

begin

declare @pidstr varchar(100)

SET @pidstr = LOWER(CONVERT(varchar(100), @PID))

return @Modifications.query('/Modifications/modification[@ID eq sql:variable("@pidstr")]')

end

The definition for UDF f_GetIfModificationExist

ALTER function [dbo].[f_GetIfModificationExist]( @Modifications xml,@PID uniqueidentifier )

returns Bit

with schemabinding

as

begin

declare @pidstr varchar(100)

SET @pidstr = LOWER(CONVERT(varchar(100), @PID))

return @Modifications.exist('/Modifications/modification[@ID eq sql:variable("@pidstr")]')

end

The Statement to create the index is below.

CREATE UNIQUE CLUSTERED INDEX [IX_ID_RecordID] ON [dbo].[vwAuditLogDetails]

(

[ID] ASC,

[RecordID] ASC

)WITH (STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

View Replies !

Need A Single Sql Statement
How can I do it by using two subqueries the second of them to be aggregate and have two left joins from the first to the second??

e.g. How can I left join these two queries with the joinfield1,joinfield2 fields??

1st query
Select field1, field2, joinfield1,joinfield2 FROM Table1 INNER JOIN Table2 ON Table1.field3 = Table2.field4 where field5=Value

2nd query
Select sum(agfield1) As f1, sum(agfield2) As f2, joinfield1,joinfield2 FROM Table3 INNER JOIN Table4 ON Table3.agfield3 = Table2.agfield4
where agfield5=Value
Group By joinfield1,joinfield2

View Replies !

Is It Possible In Single SQL Statement
Does anyone know how should I write the sql for getting the following result?

Original Table like below.
-------------------------------
[WorkDay][AgentCode]
06/12/01 3
06/12/02 2
06/12/02 3
06/12/03 2
06/12/03 3
-------------------------------

Curernt SQL:

When I put an "agentcode=2" in 'WHERE' clause, the result does not have '06/12/01' row.

Example,
SELECT DISTINCT WorkDay, AgentCode FROM MasterScheduleTransaction WHERE AgentCode=2
-------------------------------
[WorkDay][AgentCode]
06/12/02 2
06/12/03 2
-------------------------------

I would like to know the agent is in the specified date.
The expected result like below.
-------------------------------
[WorkDay][AgentCode]
06/12/01 NULL
06/12/02 2
06/12/03 2
-------------------------------

Please help its urgent

View Replies !

Multiple Columns Index/Key (does It Free Me From Creating A Single Column Indexes???)
I hope i'm in the right place, but thanks anyway....

Actually i have 2 questions (regarding sql-server Indices/Keys):

1) I have an index, which is consisted of 4 columns.

    I've read elsewhere that this index functions (as well) as an index (single column

    index) on the first column of this multi-column index.

    Does this mean that if i'd like to have (in addition) Indices on all of the 4 columns

    seperately i need to define only 3???

2) I have a unique key consisted of multiple columns.

    I'd like to save an index to this combination of columns as well (to speed up

    things in DB...).

    Does the definition of a multiple-columns key free me from defining the multiple-

    columns index???

    can anyone explain the main diference between Keys and Indices???

View Replies !

Insert Statement That Pulls Column Data From Another Table?
hi.

i cant get this quite right.

i have a table and i need to insert one column with data from another table. it goes something like this (although i know this is wrong, just here for a visual explaination) :

Code:

INSERT INTO List
(list_date, email_address, list_status, list_email)
values
(
GetDate()
, 'name@rice.edu'
, 0
, SELECT emailAddress FROM Users WHERE id = '72'
)

so, list_email needs the email address from the Users
table. i tried messing around with inner joins but, well,
here i am...

thanks in advace.

View Replies !

Single SQL Statement Solutionâ€¦.
What is the single SQL statement to truncate the blank space on either side of data.
Ex.
Table1 has Name as column.
I have records filled with blank space on both side for Name field.
With one query I want to correct (truncate the leading and trailing space) the data.
How?
SQL Server 2005 SP2.
Thank you,
Smith

View Replies !

Single Select Statement
Hi,

i have an input parameter @PageloadYN Bit Null

if @PageloadYN = 1 then Select top 500 Records from the Table
if @PageloadYN = 0 then Select * from the Table

i tried like this

Select top 500.* From Table where @PageloadYN = 1
Select * From Table where @PageloadYN = 0

Is there any way to get details in Single select statement only.

View Replies !

Using Single Quotes In EXEC Statement

View Replies !

Single Statement For Insert Or Update
In VB6 using MDAC 2.8 I could do a single select statement that would act as either an Insert or an update. Is there a way to do this in ADO.net?
My old VB6 code
Dim dbData As New ADODB.Connection
Dim rs1 As New ADODB.Recordset
Dim strParm As String
Dim strCusNo As String
'
strParm = "Provider=SQLOLEDB; Data Source=SQL2000; Initial Catalog=DATA_01; User ID=UserName; Password=password"
dbData.Open strParm
'
strParm = "Select CusNo from CusFil Where CusNo = '" & strCusNo & "'"
rs1.Open strParm, dbData, adOpenStatic, adLockOptimistic, adCmdText
If rs1.BOF And rs1.EOF Then
rs1.AddNew
Else

End If
With rs1
!CusNo = strCusNo
.Update
End With
rs1.Close
'
Set rs1 = Nothing
dbData.Close
Set dbData = Nothing

Is there an ADO.Net equivalent?

thanks,

View Replies !

Update 2 Tables In Single Statement
Is it possible to update 2 tables in a single t-sql statement?

If Yes, whats the syntax?

Update Table1,Table2 ... is not working

View Replies !

Sql Statement With Multiple Single Quotations
>Hi,
>
>Thanks you for quick answer but we still the same problem special with names like the following exmple:

DECLARE @L_SQLCOMM VARCHAR(8000)

SET @L_SQLCOMM = 'SELECT * FROM AMASTER where ACCTNAME = (ala'a)'

EXEC(@L_SQLCOMM

"ala'a " is Arabic name in English characters.

Regards

View Replies !

Single Statement To Delete Record Into More Tables
Hi ,

I little question for you ... is it possibile to write a SQL statement to delete records in several tables at the same time?

For example if I've two tables involved by join

DELETE <...> from Customers A
INNER JOIN CustomerProperties B ON A.CustomerID=B.CustomerID

I Must use two statement to remove records from both the tables?

Thx

View Replies !

To Alter Multiple Column With Single Statement
It is possible to alter multiple columns within a single alter table statement?
I have got the following URL that tells it is not possible to alter multiple columns within in signle alert table statement.
http://www.blogcoward.com/archive/2005/05/09/234.aspx[^]
Does anyone know about that?

Thanks,
Mushq

View Replies !

Can Someone Clarify Why Only A Single-statement Can Be Executed In A Command?
I'm evaluating SQL 2005 Everywhere Edition for use by our desktop application. I'm a traditional SQL Server developer and I rely heavily on stored-procedures to encapsulate basic data manipulations across multiple tables and inside multi-statement transactions.

I was excited to see an in-process version of SQL released and my thought was "this is great... now I can ditch the tediousness of individual OLEDB/.NET commands, and write batches of T-SQL and just focus on the data manipulations". But, alas, it seems I cannot. Why is SQL Everywhere Edition limited to executing a single SQL statement at a time?

For example, my application would like to update mutlipe rows in one table, delete multiple rows from another, and insert multiple rows into a third. I can do that with 3 T-SQL statements in a single small batch in a very readable way with full blown SQL Server. (and I can put that batch in a stored procedure and re-use it efficiently later.) If I contemplate how to do that with OLEDB and the single statement limitation of SQL Everywhere, it's a lot more code and a lot less appealing/maintainable. I want as much of my app to be using declarative code and as little as possible tied up in tedious OLEDB calls. Is this not possible with SQL Everywhere Edition?

View Replies !

Update Multiple Varbinary Records With Single Sql Statement
I am renovating an existing application and am converting the existing passwords into hashed values using SHA1. I know how to compute the hashed values as a byte array for each record. What I don't know how to do easily is update all of the records i a single call to the database. Normally, I would just do the following:UPDATE HashedPassword = someValue WHERE UserID = 101;
UPDATE HashedPassword = someOtherValue WHERE UserID = 102;
...

What I don't know is what someValue and someOtherValue should be. How do I convert my byte array into string representation that SQL will accept? I usually execute multiple statements using Dim oCmd as New SqlCommand(sSQL, MyConn) and then call oCmd.ExecuteNonQuery().
Alternatively, I found the following code that uses the byte array directly but only shows a single statement. How could I use it to execute multiple statements as shown above?'FROM http://aspnet.4guysfromrolla.com/articles/103002-1.2.aspx

'2. Create a command object for the query
Dim strSQL as String = _
"INSERT INTO UserAccount(Username,Password) " & _
"VALUES(@Username, @Password)"
Dim objCmd as New SqlCommand(strSQL, objConn)

'3. Create parameters
Dim paramUsername as SqlParameter
paramUsername = New SqlParameter("@Username", SqlDbType.VarChar, 25)
paramUsername.Value = txtUsername.Text
objCmd.Parameters.Add(paramUsername)

Dim paramPwd as SqlParameter
paramPwd = New SqlParameter("@Password", SqlDbType.Binary, 16)
paramPwd.Value = hashedBytes
objCmd.Parameters.Add(paramPwd)

'Insert the records into the database
objConn.Open()
objCmd.ExecuteNonQuery()
objConn.Close()

View Replies !

How To Identify Special Characters With A Single Case Statement
hi All :I have an insert statement which readsINSERT Into TableA (Col1)SELECT CASE WHEN Col1 LIKE '%[a-z]%' THEN 999999 ELSE Col1 END ASCol1,FROM TableB**********************TableA. Col1 has a datatype of Int.TableB. Col1 has a datatype of varchar.My insert is failing as it is trying to insert special character,identifying a in the input columnHow can i write a similar case statement to filter out specialcharacters and replace with 99999Also can you refer me to a place where i can have a list of specialcharacters and if need be write mulitple case statements to filterthem out.thanksRS

View Replies !

Can A Single Insert Statement Will Update Multiple Tables
Hai Guys:

I have a situation where i need to insert a value called StudentID to several tables with one insert statement. Can you please inform me the SQL statement to acheive the above mentioned task. (by the way iam using SQL Server 2000, i dont need triggers / procedures as they are too tricky and hard to use).

Thanks / Regards,
Dotnet Geek.

View Replies !

Verify Multiple Database Backups Using Single Statement
Hi all,

I have taken more than one database backups and i have an idea that we can verify single backup using "restore verifyonly from disk='<path>'" statement.
Now my question is Could we verify all(More than one backup) backups with single statement?
Any suggestion would be helpful to me:-)

Thanks in advance,

View Replies !

How To Form A Single SQL Statement In A Datalist For A Challenging And Convoluted Problem ?
Hello, I'm really stuck at trying to figure out how to write out the proper SQL statement for my problem.   I'm relatively new to SQL, so this may not be so tough for some of you. Basically, a user logs in (I'm using the default membership provider) and goes to his INBOX to see his list of messages sent to him. The list is presented to him via a datalist. Each item of the datalist contains a table of 2 columns and 1 row as pictured below. The first box contains the user photo and user name of the person who SENT him the message (not the logged in user). The second box contains the subject title of the message.
      FROM        |    SUBJECT | User Photo     |                    ||                       |    Subject     || User Name     |                    |
Here is the list of the relevant 4 tables of my database and the relevant fields.....
aspnet_Users tableUserId (used to link to Member table)UserName
Member tablememberId (int - primary key)UserId (guid - used to link to aspnet_Users table)primaryPhotoId (int - used to link to Photo table)
Photo tablephotoId (int - primary key)photoUrl (string - path to file on local drive)
Message tablemessageId (int - primary key)fromMember (int - connects with memberId from Member table)toMember (int - connects with memberId from Member table)subject (varchar(max))
So basically, from a simplistic high level point of view, the datalist is going to list all of the messages where Message.toMember = the logged in user. The senders will be determined by the Member.fromMember fields. Intuitive enough so far, I guess. This is the SQL statement I have so far.....
SELECT aspnet_Users.UserName, Message.subjectFROM aspnet_Users, Member, MessageWHERE aspnet_Users.UserName = Profile.UserName AND aspnet_Users.UserId = Member.UserId AND Member.memberId = Message.toMember
Note that I'm grabbing the logged in user info from Profile.UserName.  So far, this SQL statement should make the datalist crank out all messages that were sent to the logged in user. HOWEVER, how would I modify this so that the datalist generates the username of the sender, NOT the receiver (aka person who logged in)? Do you see the core of my dilemna here? I'm trying to get a resultset based on the Message.toMember (the logged in user), but also want data on the sender (the Message.fromMember so I can use the username and the photo of the SENDER, not the person logged in aka the RECEIVER). Currently, the aspnet_Users in the SELECT statement gets the username of the logged in person, not the sender.
And once we solve the issue of retrieving the sender's username, I also have to get his MAIN photo (I say "main" since a user can have multiple photos and the main one is determined by the value in a given member's primaryPhotoId field of the Member table) ?? I'm a newbie to ASP.NET and to databases in general so this may not be as tough for most of you and perhaps you're laughing at the simplicity hehe. The SQL statement so far asks to retrieve information based on the logged in user. But how do I also tell it to now go grab the Message.fromMember data, go back to the Member table to A)get the username after going back to the aspnet_Users table and B) to get the Member.primaryPhotoId, and then finally to the Photo table where the Photo.photoUrl string value is obtained..... and still hang on to the results I have up until now? And since I'm using the provided datalist control, I think I need to get all the results I need with just one SQL statement. This is indeed very very complicated for me lol.
This problem has been giving me migraines this whole weekend. Has anyone been through such a problem before? Any help would be greatly appreciated - thanks in advance.

View Replies !

Creating SQL Statement
Alright, so let me explain the details first.I have two tables. One is the default aspnet_users table that themembership class builds. that has GUID, username, lowereduser, and such.then I have another table called "UserSkills". That stores the GUID of the member, then the skills they have. so in that table i have. userID as GUID, then about 12 languages in 'bit' format.. (thats becuase in the webpage when they fill out there profile, all these are checkboxes. Basically all of the info is here http://www.listofcoders.com/profile.aspx?name=fenixsn. so there are a couple of bit fields, 1 text, and couple of varchars.anways, so i wanna build a powerful search thingy. where the users have the option to search a user that only does for ex say php, asp, asp.net. and is from location "Canada". ok so when they fill out the info, I want my SQL statement to do the following search the userskills table for the required fields. there might be more then 1 person that has the same profile, but different GUID. and then maybe using "Join" or another sql statement, grab there username, and last activity date from the users table that memberhship createes. so in short, how do i make a dynamic sql statement.

View Replies !

Creating A SQL Statement
Hello all - newbie post forthcoming....

I have a page that is writing to a database (Access) and I am having problems getting the actual SQL statement to execute properly. The code in question is as follows:

sql = "INSERT INTO article (maincat, subcatid, subject, article)"
sql = sql & " Values ('"
sql = sql & quotes(Request.form("maincat")) & "', '"
sql = sql & quotes(Request.form("subcatid")) & "', '"
sql = sql & quotes(Request.form("subject")) & "', '"
sql = sql & quotes(Request.form("article")) & "')"

The response I get is.... "Data Type Mismatch in Criteria Expression"

The post data does not have quotes around the data - I am thinking this is the problem.

Any help would be appreciated!

View Replies !

Having Problems Creating An SQL Statement
I am having trouble getting the SQL statement to return stats from a survey the way I want them. The table is set up as:
ID Q1 Q2 Q3 Q4
Responses for each question (Columns Q1 â€“ Q4) will be a numerical value between 1-5. I want to count how many 1s, 2s, 3s, etc. I have tried different joins, self joins, unions and sub selections but cannot get the correct output.
I would like to get the output for each question as a single record, and if possible have a final column with an average for the question. But I can do that in the data binding if needed. Qs        Ones    Twos    Threes Fours   Fives
Q1       #of 1s   #of 2s   #of 3s   #of 4s   #of 5s
Q2       #of 1s   #of 2s   #of 3s   #of 4s   #of 5s
Q3       #of 1s   #of 2s   #of 3s   #of 4s   #of 5s
Any tips or SQL sample statements would be greatly appreciated.

View Replies !

Creating Trigger On Creating Table

Hi,

If I want to automatically insert a record which has default value in a table,
how can I create the trigger?

View Replies !

Help Needed Creating Select Statement
Hi,I have a need to create a table detailing the ID of all contacts and thelast time they were contacted. This information is stored in 2 tables,'contact' and 'activity' (ID in the 'contact' table links to 'main_contact'in the 'activity' table).I guess I need some sort if iteration to go through each contact and findfind the last activity that took place against each of them (there many bemore than 1 activity against each contact) and then place the output valuesinto the new table.Can anyone show me how to go about this?Thanks!

View Replies !

Creating A Heirarchical Output From SQL Statement
This may be a basic question, but defining anything other than a cursoris preffered.I have, as an example, 2 tables. One with customer data (addresses,phones, etc), the other is a listing of all 50 states (a cross referencefor short state alias to long state name, i.e. FL - Florida, etc...).I want to sort the out put by state long name, and show each customer inthe state ... BUT ...the output needs to be like so:FloridaABC,Inc Address1 City, State Zip, other InfoDummy Corp Address1 City, State Zip, other Info...GeorgiaXYZ, Inc Address1 City, State Zip, other Info...etc ...This is a basic heirarchical listing. Can this be done with a singleT-SQL statement or are cursors needed?Thanks in advance."Excellence is achieved through 1% inspiration and 99% perspiration." A.Einstein*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View Replies !