How To Combine SQL With Web Site? (was Begginer)
Feb 14, 2007OK, so im getting closer to producing my web site selling t shirts and stickers.
Ive also learnt a bit of SQL- through work.
How do I combine the 2 to enhance my site?
thanks
ADVERTISEMENT
Some Questions For A Begginer
Dec 29, 2005Ok, im a lil confused
I know that SSSME (SQL Server Management Studio Express) CTP is a simple gui that you can use to manage your sql server databases and connections...
In Visual Basic express, you also have the Database Explorer and the option to add a database and create a connection and a gui to edit ur database/tables/fields etc, but it starts another sqlserver.exe service under the user that is currently logged in, so does it create and start its own server or how is that working?....also when i use one, i cant use the other....so which one should i use?
Im going to be using connection strings and creating my own datasets/data adapters so i dont really need the one used inside VS Express....
Still confused bout some things:
In SSSME under Registered Servers, there is currently one server that automatically starts, what if i want to add another server? it lets be add it, but it doesnt let me start the serive or connect to it....is express limited to only one server? or is my understanding of it off?
how/where would i add the database to my project? where should the actually database file be located?
sorry for these q's, moving from access to sql express
Thanks :O
Begginer Confusion
Jul 7, 2007Hey everyone,
I am brand new to SQL and I need some help getting started how to learn about relational databases and how to make the databases via Microsoft SQL(I have visual Studios PRO) and access and use them via C#.
I believe that the program that I am writing will be significantly better with a database. What I am doing is making a program that stores user input. But each user input that a person stores can have a reference to another user input, hence why I think that a relational database to store this information will be work out well. I also just want to learn more about databases because I think that it will be a good step in me learning more about programming.
What I need from you guys is a point into a direction for a book or a specific spot in a website that will introduce and teach me how to design these relational databases via Microsoft SQL. I have programmed alot with Java and recently switched over to C#, using the .net framework and found it to be great. I need to find out how to make these databases but also how to connect and access them through my application that will be coded in C#. I have "Programming in C#" by O'Reilly but it really doesn't go in that much about SQL databases.
The question that I really have come from the limited knowledge that I have gained looking online and talking to my brother (progamming whiz). A database is managed by a DBMS and clients can interact with the DBMS to get to the database. The question to me is how does one interact with a DBMS through a programming language like C#. I guess I am just really confused at the this link. If anyone could point me into the direction of a book to answer these question but help teach me to apply it to my own program, I would really appreciate it. Thank your for time.
P.S.
I have found some books online but I dont really know which one I really need. One that I found is called "Learning SQL on SQL Server 2005". If you have read, I would appreciate your opinion. Thanks
Null Problem(begginer)
Dec 14, 1999I’m working with VB6.0 and SQL Server6.5 using ADO accsesing technology.
I have a silly problem with an If.
I declare a recordset called rstprimas to access to a table called “primas”
I make a “select sum” to this table and if the result is null I have to move
zero to a variable called liquidacion_primas.
I make the select and in the “inmediate window” I can see that the result of the select, that is,
rstprimas(0) is null, but in the if instruction the execution go for the else option. That is, it doesn’t
make the instruction “liquidacion_primas = 0”.
The if must be wrong, but.... why??, do it has someting to do with the “SELECT SUM”???
Can anybody tell me what is the wrong thing?
Thank you very much
MY CODE:
Dim liquidacion_primas as integer
Dim Sql_primas as string
Set rstprimas = New ADODB.Recordset
‘sql for accesing the table “primas”
Sql_primas = "SELECT SUM(prima) FROM primas WHERE codigo_cuenta = '" _
& rstliquidacion!codigo_cuenta & "' AND fecha_sesion = '" _
& fecha_sesion & "'"
‘select sum
rstprimas.Open Sql_primas, cnnoddo, adOpenKeyset, adLockOptimistic
If rstprimas(0) = Null Then
liquidacion_primas = 0
Else
liquidacion_primas = rstprimas(0) ‘the program make that instruction
End If
Help With Error In Log File (Begginer)
Jul 18, 2007I am trying to find out what is the reason for this error on my report server.
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'Reporting_FSSRC'. (rsErrorOpeningConnection)
For more information about this error navigate to the report server on the local
server machine, or enable remote errors
I checked the log and this is what is says.
<Header>
<Product>Microsoft SQL Server Reporting Services Version 9.00.3042.00</Product>
<Locale>en-US</Locale>
<TimeZone>Central Daylight Time</TimeZone>
<Path>C:Program FilesMicrosoft SQL ServerMSSQL.1Reporting ServicesLogFilesReportServer__07_18_2007_16_07_19.log</Path>
<SystemName>BRIARD</SystemName>
<OSName>Microsoft Windows NT 5.2.3790 Service Pack 1</OSName>
<OSVersion>5.2.3790.65536</OSVersion>
</Header>
w3wp!webserver!6!7/18/2007-16:07:19:: i INFO: Reporting Web Server started
w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing ConnectionType to '0' as specified in Configuration file.
w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing IsSchedulingService to 'True' as specified in Configuration file.
w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing IsNotificationService to 'True' as specified in Configuration file.
w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing IsEventService to 'True' as specified in Configuration file.
w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing PollingInterval to '10' second(s) as specified in Configuration file.
w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing WindowsServiceUseFileShareStorage to 'False' as specified in Configuration file.
w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing MemoryLimit to '60' percent as specified in Configuration file.
w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing RecycleTime to '720' minute(s) as specified in Configuration file.
w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing MaximumMemoryLimit to '80' percent as specified in Configuration file.
w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing MaxAppDomainUnloadTime to '30' minute(s) as specified in Configuration file.
w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing MaxQueueThreads to '0' thread(s) as specified in Configuration file.
w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing IsWebServiceEnabled to 'True' as specified in Configuration file.
w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing MaxActiveReqForOneUser to '20' requests(s) as specified in Configuration file.
w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing MaxScheduleWait to '5' second(s) as specified in Configuration file.
w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing DatabaseQueryTimeout to '120' second(s) as specified in Configuration file.
w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing ProcessRecycleOptions to '0' as specified in Configuration file.
w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing RunningRequestsScavengerCycle to '60' second(s) as specified in Configuration file.
w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing RunningRequestsDbCycle to '60' second(s) as specified in Configuration file.
w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing RunningRequestsAge to '30' second(s) as specified in Configuration file.
w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing CleanupCycleMinutes to '10' minute(s) as specified in Configuration file.
w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing DailyCleanupMinuteOfDay to default value of '120' minutes since midnight because it was not specified in Configuration file.
w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing WatsonFlags to '1064' as specified in Configuration file.
w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing WatsonDumpOnExceptions to 'Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException,Microsoft.ReportingServices.Modeling.InternalModelingException' as specified in Configuration file.
w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing WatsonDumpExcludeIfContainsExceptions to 'System.Data.SqlClient.SqlException,System.Threading.ThreadAbortException' as specified in Configuration file.
w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing SecureConnectionLevel to '0' as specified in Configuration file.
w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing DisplayErrorLink to 'True' as specified in Configuration file.
w3wp!library!6!7/18/2007-16:07:19:: i INFO: Initializing WebServiceUseFileShareStorage to 'False' as specified in Configuration file.
w3wp!resourceutilities!6!7/18/2007-16:07:19:: i INFO: Reporting Services starting SKU: Enterprise
w3wp!resourceutilities!6!7/18/2007-16:07:19:: i INFO: Evaluation copy: 0 days left
w3wp!runningjobs!6!7/18/2007-16:07:19:: i INFO: Database Cleanup (Web Service) timer enabled: Next Event: 600 seconds. Cycle: 600 seconds
w3wp!runningjobs!6!7/18/2007-16:07:19:: i INFO: Running Requests Scavenger timer enabled: Next Event: 60 seconds. Cycle: 60 seconds
w3wp!runningjobs!6!7/18/2007-16:07:19:: i INFO: Running Requests DB timer enabled: Next Event: 60 seconds. Cycle: 60 seconds
w3wp!runningjobs!6!7/18/2007-16:07:19:: i INFO: Memory stats update timer enabled: Next Event: 60 seconds. Cycle: 60 seconds
w3wp!library!7!07/18/2007-16:07:21:: Call to GetPermissionsAction(/Versatile).
w3wp!library!7!07/18/2007-16:07:22:: i INFO: Catalog SQL Server Edition = Enterprise
w3wp!library!6!07/18/2007-16:07:22:: Call to GetPropertiesAction(/Versatile, PathBased).
w3wp!library!7!07/18/2007-16:07:22:: Call to GetSystemPermissionsAction().
w3wp!library!6!07/18/2007-16:07:22:: Call to ListChildrenAction(/Versatile, False).
w3wp!library!7!07/18/2007-16:07:22:: Call to GetSystemPropertiesAction().
w3wp!library!6!07/18/2007-16:07:22:: Call to GetSystemPropertiesAction().
w3wp!library!6!07/18/2007-16:07:29:: Call to GetPermissionsAction(/Versatile/Versatile Customer).
w3wp!library!7!07/18/2007-16:07:29:: Call to GetSystemPropertiesAction().
w3wp!library!6!07/18/2007-16:07:29:: Call to GetPropertiesAction(/Versatile/Versatile Customer, PathBased).
w3wp!library!7!07/18/2007-16:07:30:: Call to GetSystemPermissionsAction().
w3wp!library!6!07/18/2007-16:07:30:: Call to GetPropertiesAction(/Versatile/Versatile Customer, PathBased).
w3wp!library!7!07/18/2007-16:07:30:: Call to ListEventsAction().
w3wp!library!6!07/18/2007-16:07:32:: Call to GetSystemPropertiesAction().
w3wp!processing!7!7/18/2007-16:07:33:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'Reporting_FSSRC'., ;
Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'Reporting_FSSRC'. ---> System.Data.SqlClient.SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.ReportingServices.DataExtensions.SqlConnectionWrapper.Open()
at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.ReportRuntimeDataSourceNode.OpenConnection(DataSource dataSourceObj, ReportProcessingContext pc)
--- End of inner exception stack trace ---
w3wp!processing!7!7/18/2007-16:07:33:: e ERROR: Data source 'Reporting_FSSRC': An error has occurred. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'Reporting_FSSRC'. ---> System.Data.SqlClient.SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
w3wp!processing!7!7/18/2007-16:07:33:: e ERROR: An exception has occurred in data source 'Reporting_FSSRC'. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'Reporting_FSSRC'. ---> System.Data.SqlClient.SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
w3wp!processing!7!7/18/2007-16:07:33:: i INFO: Merge abort handler called. Aborting data sources ...
w3wp!processing!7!7/18/2007-16:07:33:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing., ;
Info: Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'Reporting_FSSRC'. ---> System.Data.SqlClient.SqlException: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
--- End of inner exception stack trace ---
Why does it say not associated with trusted sql server connection. I am the admin. How do i fix this, so i can view this report and its datasource correctly on the server.
Begginer In SQL-Foreign KEy To Mulitple Tables
Aug 14, 2007Hey everyone,
I am beggining in SQL and the .NET framework and have been running into some problems trying to design a relational database. I am completely new to it so I bought a book that was recommended in this Forum called "Handbook of Relational Database Design" and it has been pretty usefull so far. RIght now I am trying to make the Logical Data Model before I make the Relational Data Model.
The problem that I am having right now is creating a table that is a derivation from another table. For example, in the book they have a table called Property, and then two other tables called MountainProperty and BeachProperty. MountainProperty and BeachProperty are a type (relationship here) of a property. So basically Property will hold some data in a table, and then MountainProperty and BeachProperty will extend that property to contain more specific data. This is very similar to what I want to do. However I am having a problem understanding how an instance (or row) in Property, will have a link (foreign key) to a piece of data that is in Mountain or BeachProperty. I understand the foreign key in Mountain and BeachProperty and how they will link back to their "parent". But how will Property know its children, where is the link for that, how can one make a link to that. You could make a column with a foreign key, but which table would it point to, can one column point to mulitple tables? That doesn't make very much sense to me.
Basically what I am trying to say is that a row in the Property table can be multiple types, and these types will store more additional data about that row. How can I link to that data from that row in the Table Property.
I am terribly sorry if this is confusing or if it is so appartently easy for you, but this is the first time that I have ever tried to make a relational database and I am really struggling on seeing how to organize these tables properly. Thank yor for your time.
Jeremy
LIKE And IN Can I Combine
Oct 10, 2005I can use the IN with the WHERE clause as example:SELECT * FROM NORTHWIND WHERE LASTNAME IN ('FULLER','KING')I want to use the IN and LIKE at the same time:SELECT * FROM NORTHWIND WHERE LASTNAME LIKE ('A%','B%')I know this is a simplistic example, but the members for the IN will bemany, 5 to 10.I'm trying to avoid:SELECT * FROM NORTHWIND WHERE LASTNAME LIKE 'A%' OR LASTNAME LIKE 'B%'OR LASTNAME LIKE 'FU%' OR LASTNAME LIKE 'JON%' <...>and so forth.Any Ideas?TIARob
View 3 Replies View RelatedHow To Combine IN && LIKE
Aug 23, 2007Hai All,
Could some one help me how to combine IN & LIKE in the query selection,
i'v try many syntac and i just have no find the way out
this is what I would like to write:
Select DSCRIPTN,ORMSTRID,ACTINDX
from T50001
where ACTINDX = 350
and DSCRIPTN Like in '%'+(select distinct rtrim(ORMSTRID) ORMSTRID from L10001)+'%'
Permissions Via ASP.NET Web Site
Jan 31, 2007I have a website with membership system. I've been through a lot implementing the site and I think I'm on the verge of full implementation. I can't get my registration page or my admin page to add, edit or delete users. I'm assuming that asp does not have proper permissions on SQL to do such.
I'm running the site on a provider that has a SQL 2000 server. I access the database through VS2005 and SQL Server Management Studio Express.
Is there a tutorial out there that could help a newbie to SQL understand how to check and correct my permissions issue (or find out that I'm haveing a different issue entirely.
-Bill
Using ASPNETDB For All Site!
May 26, 2008Hi all,
I`m a .NET desktop-app developer and i`m new to ASP.NET and web development, i start developping a web application with SQL Server database and i want to know if i can put all my tables in ASPNETDB?
is any restriction with that?
Regards
SQL 7 And Site Serer 3.0
Feb 3, 1999Hello,
This may be the wrong place to add this but....
I am attempting to setup a membership instance for Site Server 3.0 using SQL 7 and try as I might, Site Server Membership will not see the server at all. I can do anything else I want to to that server and it works great (commerce, etc.). It all works OK under SQL 6.5 SP3 or later, just not SQL 7.
Does anyone have any thoughts or insights?
Thanks
John
Get A File To A FTP Site T-sql
Oct 6, 2003I have to download a file from a ftp site using either SQL/T-Sql or ActiveX script. I'm using SQL Server 2000. Please help
Thanks in advance
SQL Serverback Up To FTP Site
Oct 13, 2004Is there a good back up software that willl back up by SQL server to a FTP site. All of them I have seen either backs up to a disk or tape.
Thanks
Jay
Does Anybody Look At The Jobs On This Site
Nov 17, 2004Poeple are calling me for resume 5 years old. They need DBA's all over the country. Please pass it on to look at the job opportunities on this site.
View 2 Replies View RelatedDeploying A Web Site
Jun 14, 2006I am having problems deploying my web site from my machine to the hosting server.
Does any know of any special configurations that have to be perfomed before copying your web site to a server? All I have is a login in page that loads another page. The login page is launching okay but it does not load the second page.
Should My Site And Sql Be On The Same Server
Apr 11, 2007Hello,
I am upgrading my site from a VPS with a shared SQL server to a dedicated server and my own sql server 2003 standard. My site is an ecommerce site with about 10,000 users a month with between 10-20 people on the site at one time.
It is better to host both my site and the SQL server on the same system or is it better to have them on separate systems? if they are on the same system is 1 gig of ram enough? is will there really be much better performance with 2 gigs?
Any information on the best way to do this would be greatly appreciated!!!
Best regards,
~ Moe
Phishing Web Site
Dec 17, 2007How can a genuine Web Outlook url be classified by filtering software as a known phishing web site?
Thanks.
Accessing FTP Site
May 15, 2008
Hi,
I am trying access a FTP site present from a different server (remote work station). I am getting "The page cannot be displayed" error. I am able to access from my local machine.
Please help me solving this.
Regards,
Sriram.
Backup To FTP Site
Feb 7, 2006I want to backup my database to an ftp site. How can I do that with SQL Server 2005? Thanks.
View 1 Replies View RelatedGetting Files From An FTP Site
Apr 23, 2008Hello:
I have setup a Flat File connection manager and set the location of the file as such:
ftp://10.1.1.1/FileName.txt
When I enter this URL into Start | Run on my machine it opens the txt file fine. Working in BIDS, when I create the flat file connection manager, it doesn't give me a message saying this file does not exist, indicating it is correct. When I create a flat file Source that uses the connection manager, the preview comes up blank. When I try to execute the step it gives an error saying it "cannot open datafile ftp://10.1.1.1/FileName.txt"
Any idea what could be going on?
Thanks,
Kayda
Site Settings
Oct 16, 2006
I need to place some site settings in my Report Manger.But in my Report Manager there is no Site Settings Icon.Is this installation problem please help me.
Using Encryption Via An Asp Site. How Is It Done
Feb 18, 2007ive been playing around with encryption, just encrypting a column with a symmetric key, and having that symmetric key encrypted with a cert, and all that , and it works well through the SSMS, but i want to know how that will be used if im using sql server as a back end database for an asp web site, or an application. does the sql server have to be protected with an SSL cert, and once a user has logged in, what would the steps be to view encrypted data?
View 6 Replies View RelatedBest Way To Combine Columns
Feb 20, 2007Hi all,
I have a table with multiple rows with the same ID.
a) How do I combine all columns into one row with the same ID?
b) Is this better to do the combine in the store procedure/trigger or a sub that involked by some sort of datarepeater, datagrid controls?
Since I am trying to brush up my sql. I appreciate any examples on top of conceptual solution.
Thanks
Combine 3 Databases
Jun 14, 2007Combine 3 Databases, Not tables.
Let me spell this out -- I have 3 databases (they are in isolation when in use, its a field app) that need to be merged into 1 "masterDB" database. I've discovered I can use something like this to get to each DB in a query...
1 USE [database1]2 SELECT table1.Name, table1.Location, table1.Date, table2.Blog3 FROM table2 INNER JOIN4 table1 ON table2.ID = table1.ID5 ORDER BY table1.Date
and then just repeat for database2 and database3. Ok, fine, rah rah. My question is how do I "merge" all of these into 1. No data on each db will be identical, at all, ever so that is not a concern. I just need all the data from db1, 2 and 3 into masterDB.
Ideas? Direction?
Combine Sql Queries
Feb 19, 2008Hello, I have these variables on my page:
userid = "10101"
RequestHost = "example.com"
What would be the best way performace wise to first check if the userid 10101 exists in my sql server db. If it does exist I would then need to check if "example.com" exists for the userid in the userdomains table. If these both exist I would then like to query some additional data. I was hoping its possible to combine this into one query somehow. I dont think this is the best solution:
sqlcommand.CommandText = "SELECT UserId From Users Where UserID = '10101'"
Conn.Open()
dr = sqlcommand.ExecuteReader
if dr.hasrows then
sqlcommand2.CommandText = "SELECT UserDomain From UserDomains Where UserID = 'example.com'"
dr2 = sqlcommand2.ExecuteReader
if dr2.hasrows then
sqlcommand3.CommandText = 'Select Additional Data
dr3 = sqlcommand3.ExecuteReader
'read values
conn.close
else
conn.close
'do something
end if
else
conn.close
'do something
end if Thanks Very Much!
How To Combine Two Columns Into One In An Sql
Dec 7, 2005I have an stored procedure that returns 3 columns. Month, Date, and Total Number of Calls.
Here is the stored Proc:
SELECT DATEPART(mm, CALLSTARTTIME) , DATEPART(dd, CALLSTARTTIME), COUNT(*)
FROM CALL_LOG_MASTER
WHERE (COMMERCIALS = '1') AND (PINCODE IS NOT NULL)
GROUP BY DATEPART(mm, CALLSTARTTIME), DATEPART(dd, CALLSTARTTIME)
ORDER BY DATEPART(mm, CALLSTARTTIME), DATEPART(dd, CALLSTARTTIME)
It returns a table:
MONTH DATE TOTAL NUMBER OF CALLS======= ===== ===========1 1 10
1 2 15
My question is: is it possible to combine the Month and Date column into one column. e.g.
Date Total Number of Calls==== ==============1/1 101/2 15
Please Help, Thanks in advance :)
Combine SQL Select With SP
Mar 27, 2002I am looking for the correct T-sql statement. I want to put parameters in a SP from a select statement. And make the SP exec for each records that the select statement returns. The following is the SP that I want to pass the parameters.
CREATE PROCEDURE sp_sendSMTPmail (@To varchar(8000),
@Subject varchar(255),
@Body text = null,
@Importance int = 1, -- 0=low, 1=normal, 2=high
@Cc varchar(8000) = null,
@Bcc varchar(8000) = null,
@Attachments varchar(8000) = null, -- delimeter is ;
@HTMLFormat int = 0,
@From varchar(255) = null)
/* Name: sp_sendSMTPmail
Purpose: Send an SMTP mail using CDONTS object.
Requirements: SMTP server (comes with IIS but doesn't require IIS) must be loaded.
Returns: 0 if successful, 1 if any errors
Sample Usage:
sp_sendSMTPmail 'vince.iacoboni@db.com', 'Testing', 'testing sp_sendSMTPmail, please reply if you receive this',
@cc='irmsqlmail@db.com',
@Importance=1,
@Attachments='c:oot.ini;c:autoexec.bat'
History:
02/07/2001 VRI Created.
*/
AS
SET NOCOUNT ON
DECLARE @object int,
@hr int,
@StrEnd int,
@Attachment varchar(255),
@return int,
@Msg varchar(255)
SELECT @From = isnull(@From, @@SERVERNAME)
-- Create the CDONTS NewMail object.
EXEC @hr = sp_OACreate 'CDONTS.NewMail', @object OUT
IF @hr <> 0 GOTO ObjectError
-- Add the optional properties if they are specified
IF @Body IS NOT NULL
BEGIN
EXEC @hr = sp_OASetProperty @object, 'Body', @Body
IF @hr <> 0 GOTO ObjectError
END
IF @Cc IS NOT NULL
BEGIN
EXEC @hr = sp_OASetProperty @object, 'Cc', @Cc
IF @hr <> 0 GOTO ObjectError
END
IF @Bcc IS NOT NULL
BEGIN
EXEC @hr = sp_OASetProperty @object, 'Bcc', @Bcc
IF @hr <> 0 GOTO ObjectError
END
IF @HTMLFormat <> 0
BEGIN
EXEC @hr = sp_OASetProperty @object, 'MailFormat', 0
IF @hr <> 0 GOTO ObjectError
END
-- Loop through the ; delimited files to attach
CREATE TABLE #FileExists (FileExists int, FileIsDir int, ParentDirExists int)
WHILE isnull(len(@Attachments),0) > 0
BEGIN
SELECT @StrEnd = CASE charindex(';', @Attachments)
WHEN 0 THEN len(@Attachments)
ELSE charindex(';', @Attachments) - 1
END
SELECT @Attachment = substring(@Attachments, 1, @StrEnd)
SELECT @Attachments = substring(@Attachments, @StrEnd+2, len(@Attachments))
-- Ensure we can find the file we want to send.
DELETE #FileExists
INSERT #FileExists
EXEC master..xp_fileexist @Attachment
IF NOT EXISTS (SELECT * FROM #FileExists WHERE FileExists = 1)
BEGIN
RAISERROR ('File %s does not exist. Message not sent.', 16, 1, @Attachment)
RETURN 1
END
EXEC @hr = sp_OAMethod @object, 'AttachFile', NULL, @Attachment
IF @hr <> 0 GOTO ObjectError
SELECT @Msg = 'File ' + @Attachment + ' attached.'
PRINT @Msg
END
-- Call the Send method with parms for standard properties
EXEC @hr = sp_OAMethod @object, 'Send', NULL, @From, @To, @Subject, @Importance=@Importance
IF @hr <> 0 GOTO ObjectError
-- Destroy the NewMail object.
EXEC @hr = sp_OADestroy @object
IF @hr <> 0 GOTO ObjectError
PRINT 'Message sent.'
RETURN 0
ObjectError:
BEGIN
EXEC sp_displayoaerrorinfo @object, @hr
RETURN 1
END
GO
Combine 2 Tables
Sep 20, 2007I have two tables in MS SQL 2000 that I need to combine into one. they will share 3 columns and the rest will stay the same. the existing tables are very large and I REALLY don't want to plug in all the data by hand...Any nifty tricks??? I found software but dont want to spend $$ on it.
View 5 Replies View RelatedCombine Two Tables Into One RS
Mar 5, 2005Here is my dilemma, i'm trying to combine the results of two different tables. Both tables are very similar but the data returned must be sorted before they are combined because I'm only returning the top xx records based on a hits column.
Here is a sample of the two databases:
Table 1
ID - SONG - HITS
1 - tb1SONG 1 - 356
2 - tb1SONG 2 - 1459
3 - tb1SONG 3 - 278
4 - tb1SONG 4 - 965
5 - tb1SONG 5 - 124
Table 2
ID - tb2SONG - HITS
1 - tb2SONG 1 - 412
2 - tb2SONG 2 - 85
3 - tb2SONG 3 - 2035
4 - tb2SONG 4 - 693
5 - tb2SONG 5 - 745
I have tried the following union query which combines the two RS's then sorts the data:
SELECT Top 2 ID, Song, Hits FROM Table1
UNION SELECT Top 2 ID, Song, Hits from Table2
Which would return the first two records from each then sort them like this:
2 - tb1SONG 2 - 1459
1 - tb2SONG 1 - 412
1 - tb1SONG 1 - 356
2 - tb2SONG 2 - 85
I would like to sort based on the hits column then combine the RS producing this:
3 - tb2SONG 3 - 2035
2 - tb1SONG 2 - 1459
4 - tb1SONG 4 - 965
5 - tb2SONG 5 - 745
Any ideas or solutions will be greatly appreciated.
Thanks
Combine Fields
Oct 30, 2006I have two fields that I would like to combine into 1 field is this possible.
Example: Document # 555, Doc Description ABCD in the 3 field I would like 555-ABCD.
Is that possible in SQL Server thanks
Combine These Two Sql Statements
Jun 2, 2004SELECT bms_id,email_address,COUNT(*)
INTO #temp
FROM emp_db
WHERE email_address IS NOT NULL
GROUP BY bms_id,email_address
ORDER BY bms_id DESC,COUNT(*) DESC
SELECT bms_id COUNT(*)
FROM #TEMP
GROUP BY bms_id
ORDER BY COUNT(*) DESC
How can i put these two statements into a single sql statement.
Thanks.
Combine Two Queries Into One
Jun 16, 2008I need to combine to sql queries. Separately they work fine, but I need the "total qty" from second query put into the first query
Query 1
SELECT dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Region.CompanyRegionID, dbo.Job.Active,
dbo.Job.ChangeDate
FROM dbo.Job
LEFT OUTER JOIN dbo.Division ON dbo.Job.DivisionGuid = dbo.Division.DivisionGuid
LEFT OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid
LEFT OUTER JOIN dbo.JobType ON dbo.Job.JobTypeGuid = dbo.JobType.JobTypeGuid
WHERE dbo.job.CompanyJobId = 3505048
ORDER BY dbo.Job.CompanyJobId
Query 2
SELECT case dbo.SourceType.CompanySourceTypeId
when 'PR' then SUM(dbo.ProductionEvent.Quantity)
end
AS Ttl_Qty
FROM dbo.Batch INNER JOIN
dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN
dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN
dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN
dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid INNER JOIN
dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid INNER JOIN
dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid LEFT OUTER JOIN
dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid left outer join
dbo.JobNoteEvent on Event.EventGuid = dbo.JobNoteEvent.EventGuid
WHERE dbo.Job.CompanyJobId = 3505048 and dbo.SourceType.CompanySourceTypeId = 'PR'
GROUP BY dbo.SourceType.CompanySourceTypeId
I have tried this but it doe not work:
SELECT dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Region.CompanyRegionID, dbo.Job.Active,
dbo.Job.ChangeDate, Ttl_Qty
FROM dbo.Job
LEFT OUTER JOIN dbo.Division ON dbo.Job.DivisionGuid = dbo.Division.DivisionGuid
LEFT OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid
LEFT OUTER JOIN dbo.JobType ON dbo.Job.JobTypeGuid = dbo.JobType.JobTypeGuid
WHERE dbo.job.CompanyJobId = 3505048 and where Ttl_Qty =
(SELECT case dbo.SourceType.CompanySourceTypeId
when 'PR' then SUM(dbo.ProductionEvent.Quantity)
end
AS Ttl_Qty
FROM dbo.Batch INNER JOIN
dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN
dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN
dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN
dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid INNER JOIN
dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid INNER JOIN
dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid LEFT OUTER JOIN
dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid left outer join
dbo.JobNoteEvent on Event.EventGuid = dbo.JobNoteEvent.EventGuid
WHERE dbo.Job.CompanyJobId = 3505048 and dbo.SourceType.CompanySourceTypeId = 'PR'
GROUP BY dbo.SourceType.CompanySourceTypeId)
ORDER BY dbo.Job.CompanyJobId
Combine To 1 New Column
Dec 18, 2013I have data as below:
IDJourneySegmentType Depart Arrive 1st2nd
1234511A UK AUS UKNULL
1234512A AUS US NULLUS
How can i make it to 1 row for 1st and 2nd column?
ID1st2nd
12345UKUS