Silly Query Question
Sep 3, 2006
I have been rewritting one of our applications to use SQL Express instead of Access. I am having a small issue that I cannot seem to resolve and have searched the forum thoroughly. My code is as follows:
SearchPONo = Me.txtSearch.Text
Set rs55 = New Recordset
rs55.CursorLocation = adUseClient
'SQLSearchQuery = "SELECT * FROM POInfo WHERE PONumber LIKE '%SearchPONo%' "
rs55.Open SQLSearchQuery, cn55, adOpenForwardOnly, adLockOptimistic
Goto GetRecord
GetRecord:
If rs55.RecordCount() > 0 Then
Me.DataGrid55.Enabled = True
Set DataGrid55.DataSource = rs55
I am trying to get a PONumber from a user input textbox and if I replace the %SearchPoNo% with a partial of a PO Number I can see my result. However, if I use the user input, then of course I get no results.
What am I doing wrong?
Thanks,
Lennie
View 3 Replies
ADVERTISEMENT
Aug 26, 2004
hi ...
i m a new member
i want a help on this query(new user of sql :D )
yaa and the query is
create a rule and attach it to the service_code(column) of airline_service(Table) alow only the values 'cc','n' and 'wc' to be entered into the column
plz tell mee... :confused: ..i wlll be gr8 fulllllllllllllllllllllllllllllllll ;)
View 6 Replies
View Related
Jul 1, 2004
I Imported a table over to sql 2000 server(through the DTS), then I exported the table to access 2000. After creating my forms my users were trying ot add data but couldnt, they kept getting an error message saying these records are not updateable. After some research I found that I never appointed a primary key, so after doing so the records became updateable. constant learning process adn I love it :)
View 7 Replies
View Related
Jun 11, 2001
Is there a way to restore just the data.mdf file and accept an existing log?
Current state: A client has a backup where the data file is 400mb and the log is 4.99gb. The avaiable hhd space is 6gb.
Restoring the backup to a test database fails.
Objective: Check the calendar file for missing data, and if found, update the live calendar with the missing items.
We don't need the database to be fully operational, we only need a particular table. I'll write a cursor if needed to update the live cal from the restored backup.
I suspect that the available diskspace is causing the retore to fail.
TIA
View 3 Replies
View Related
Mar 25, 2003
After being put in a position where I had to deal with a SQL problem without very little SQL knowledge I have screwed a clients main database up.
I backed up the transaction log and then deleted the log. The database is shown as suspect and won't let me restore.
Am I F&%ked?
Any help in sorting this out will be rewarded with much kudos and thanks
View 12 Replies
View Related
Jul 8, 2004
I wanted to create a table with an exsisting table, then create a relationship between the two. The table being created needs a DocID autonumbered primary key, is that possible to create through sql. An autonumber like access has, through a query or something. should I just have a insert into query or something?? how would I go about doing that.
Goodmorning Guys :)
View 3 Replies
View Related
Jul 22, 2004
I can execute a stored procedure I created in the Enterprise manager cant I??
View 8 Replies
View Related
Jan 23, 2006
Yes this is a silly question, but I don't know the answer!
I have developed a database using SQL Server 2K. I am now upgrading to SQL Server 2005. Can I still use my current database files in SQL Server 2005? If I can, do u have any idea how I can make SQL Server 2005 load up the old files and start working?
Also my hosting provider has NOT upgraded to SQL Server 2005. He will only accept the old SQL Server 2K files. Can SQL Server 2005 save files that will work on a SQL Server 2K server?
Thank you!!!
View 7 Replies
View Related
Mar 16, 2007
Hi all. I think its a silly question to ask. What is better to use? I mean in terms of performance.
c.* or c.field1, c.field2, c.field3, c.lastfield..... ?
thanks
-Ron-
View 2 Replies
View Related
Jul 20, 2005
HiI've not used SQL Server for a while, and I've forgotten how you hide allthose system procedures (beginning with dt_) in Enterprise Manager.Could some kind person please refresh my memory?ThanksCaptain Nemo
View 2 Replies
View Related
Mar 3, 2008
In a situation when you have a power cut, and then sometime later 'most' of your sql servers come back on line, is it better to leave them all down unless they all come back online, or is it better to let some of them come back up knowing that the ones that do come up will have job failure issues with the ones that are down. I pose this question purely from the perspective of scheduled job problem as we do not have people on site when we have intermittent power cuts at weekends. What would scheduled jobs which are due to run , but miss their run time as we leave the servers down after a power cut till we get back in on Mondays do when we do actually re-power them up, would they just resume from their next scheduled point, or would they try to run as often as they should have run?
Dave
View 3 Replies
View Related
Apr 24, 2007
Hi everyone,
Inside a Script Task I€™ve got this line:
Dts.Variables("Var1").Value = Dts.Variables("Var2").ToString
After that, I get this value for Var1:
"Microsoft.SqlServer.Dts.Runtime.Variable" {String}
String: "Microsoft.SqlServer.Dts.Runtime.Variable"
Does anyone have any idea about the hell is happening here?
Both of them has been defined at the same scope and own String as data type
Thanks in advance and regards,
View 3 Replies
View Related
Oct 11, 2007
I will give you the simplest version of this I know if.
I have 3 tables.
Person Table
PersonID, Forename, Surname
Event Table
EventID, EventName
Involvment Table
PersonID, EventID
In this, the Person table's primary key is PersonID, the Event table's primary key is EventID and the Involvment table's primary key is PersonID, EventID.
There is also a foreign key constrant between Person.PersonID and Involvment.PersonID and a foreign key constraint between Event.EventID and Involvment.EventID.
The sql to create this would be
CREATE TABLE [dbo].[Person](
[PersonID] [int] NOT NULL,
[Forename] [nchar](30) NOT NULL,
[Surname] [nchar](30) NOT NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[PersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Event](
[EventID] [int] NOT NULL,
[EventName] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Event] PRIMARY KEY CLUSTERED
(
[EventID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Involvment](
[PersonID] [int] NOT NULL,
[EventID] [int] NOT NULL,
CONSTRAINT [PK_Involvment] PRIMARY KEY CLUSTERED
(
[PersonID] ASC,
[EventID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Involvment] WITH CHECK ADD CONSTRAINT [FK_Involvment_Event] FOREIGN KEY([EventID])
REFERENCES [dbo].[Event] ([EventID])
GO
ALTER TABLE [dbo].[Involvment] CHECK CONSTRAINT [FK_Involvment_Event]
GO
ALTER TABLE [dbo].[Involvment] WITH CHECK ADD CONSTRAINT [FK_Involvment_Person] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Person] ([PersonID])
GO
ALTER TABLE [dbo].[Involvment] CHECK CONSTRAINT [FK_Involvment_Person]
As so obviously stolen from SSMS.
Now what I am having problems with is if someone is involved with more than one event, then I only want them to get listed once.
If we have the following in the Person table.
0
John
Doe
1
Jane
Doe
the following in the events table
0
This
1
That
and this in the Involvment table
0
0
0
1
1
1
then when doing any select using the Involvment to get name and event information from their respective tables, there will be two entries for John Doe. I don't want this if it is possible. Although the same event multiple times is ok in this case.
So, if someone could help with this then it will be greatly appreciated. I'm still not that great with SQL so this is a problem which has been annoying me.
View 4 Replies
View Related
Mar 1, 2006
I have typically done any ETL style manipulations I needed to do to data stored in SQL Server in VB.NET. I would use the IMPORT?EXPORT DTS wizard to import flat files, or mabe something from ACCESS every now and then.
I am looking at a situation in my current contract where I will be pulling flat files from a mainframe and quasi relational stuff from a DB2 instance via an ODBC connection. I will be using this stuff to build a datawarehouse for a manufacturing client.
My question is this. Is there really enough good stuff in SSIS for what I will be doing to justify my learning it or if I'm comfortable doing the manipulations in VB.NET will that work just as well for my client? After all, I can schedule VB.NET apps to write results to log files and to run at specified times, etc. It just sort of always seemed to me that DTS was for people that needed to manipulate data without necessarily having to know a lot about programming per se.
I'm looking for opinions from people that know SSIS well. Is there enough meat to make cooking the SSIS meal worth the trouble?
Thanks in advance for any info.
View 4 Replies
View Related
May 7, 2007
As i am inserting data from textfile to sql server database i happened to have space Like a square appears in front of data for certain columns.How do i remove that????I use Trim(column) for all incoming columns but it does not work.
View 3 Replies
View Related
Oct 19, 2007
What is the function of SQL Server Manager Studio Express?
Create Database and tables?
It seems that Visual Studio is already including the functions.
We can create database and table in Visual Studio 2005.
I am a new to SQL, sorry for the silly question.
View 10 Replies
View Related
Nov 25, 2004
Hi,
I have come across this term (YUKON or something like that). Is this the 'code name' for the next version of SQL server which is yet to be launced??? Can someone help me provide links to some articles on it.
Many TIA.
View 3 Replies
View Related
Nov 25, 2004
Hi,
I have come across this term (YUKON) or something like that and assume that this is the "code name" of thenext version of SQL server (i.e. 2005) thatis yet to belaunched. Isthis correct? If not, can someone please explain what YUKON means/stands for.
Thanks.
View 2 Replies
View Related
Mar 31, 2004
Hello SQL Gurus,
I want to dynamically add a column to a select statement who must contain the line number.
Example :
Some table has 3 columns (id,field1,field2)
SELECT top 3 *,[lineNumber] as lineOrder FROM someTable
must produce
id | field1 | field 2 | lineOrder
-----------------------------------
1 abc def 1
23 def ghi 2
7 ghi jkl 3
and so on... Possible ?
View 5 Replies
View Related
Nov 27, 2007
Hi all
As far as i know there is no diffrence between the two followings
Sql Statements:
1) Select * from Pubs.dbo.authors
2) Select * From Pubs..authors
i just want to be extra sure about this issue that whan we omit
the OWNER section, sqlserver use the CURRENT USER? am i right?
Thanks in advance.
Regards.
View 1 Replies
View Related
Jul 23, 2005
Gurus,Here is what I ma trying to do. I have numeric expression stored in atable column. for e.g. @a + @b + @c. I supply values to the variablesat run time and want them to be computed at run time as per theexpression in the column.the stored procedure works fine but it gives a silly error.Any help greatly appreciated. Below is the code.--drop procedure proc_bkrcreate procedure proc_bkr ASdeclare @expr nvarchar(2000)declare @sql nvarchar(2000)declare @temp_exp nvarchar(3000)declare @ans integerdeclare @QFAAPAC02_1 integerdeclare @QFAAPAC02_2 integerdeclare @QFAAPAC02_3 integerdeclare @QFAAPAC02_4 integer-- Assigning values to variables -- Startset @QFAAPAC02_1 = (Select QFAAPAC02_1 from fa_ap_stage where recordid= 3)set @QFAAPAC02_2 = (Select QFAAPAC02_2 from fa_ap_stage where recordid= 3)set @QFAAPAC02_3 = (Select QFAAPAC02_3 from fa_ap_stage where recordid= 3)set @QFAAPAC02_4 = (Select QFAAPAC02_4 from fa_ap_stage where recordid= 3)-- Assigning values to variables -- Endset @temp_exp = (select num from translation where processid = 'AP' andlabel = 'C1')-- This is how num looks: @QFAAPAC02_1 + @QFAAPAC02_2 + @QFAAPAC02_3 +@QFAAPAC02_4--select @expr = '@QFAAPAC02_1 + @QFAAPAC02_2 + @QFAAPAC02_3 +@QFAAPAC02_4'-- Above line works fine but below one does not. though both are same.select @expr = @temp_expselect @sql = 'select @ans = ' + @exprexec sp_executesql @sql, N'@QFAAPAC02_1 integer, @QFAAPAC02_2 integer,@QFAAPAC02_3 integer, @QFAAPAC02_4 integer, @ans integer OUTPUT',@QFAAPAC02_1,@QFAAPAC02_2,@QFAAPAC02_3,@QFAAPAC02_ 4,@ans OUTPUTset @cc = @ansError Message: Server: Msg 137, Level 15, State 2, Line 1[Microsoft][ODBC SQL Server Driver][SQL Server]Must declare thevariable '@QFAAPAC02_'.Thanks in Advance!Bkr
View 1 Replies
View Related
Sep 29, 2006
Hi guys,
When you get an warning from, i.e, OleDb Destination you see an exclamation mark in yellow and then, when you move the mouse over you can't see never the whole message (If long)
Is there any way to see the entire tooltiptext?
Thanks in advance and have a good weekend!
View 3 Replies
View Related
Jun 28, 2007
Dear world,
In my new installation of MS SQL Server 2005 Management Studio, i open the DB i want, then i go to Programmability/Stored Procedures i click right button "New Stored Procedure".
I can write a stored procedure but when i save it does not record the sp in the dababase but in the file system without any relation with the database!
The database was originally created with SQL Server 2000 and now attached to a 2005 version (may be is something to do with it)
What am i doing wrong???
Thank you in advance
David
View 2 Replies
View Related
Oct 6, 2005
is it ms sql stand for microsoft access???
i am currently do a project.
i am headaching .about the database adn the asp.
so far all i knw is only asp, other no .. any suggestion ??
asp to MySQL ? will it work ????
View 1 Replies
View Related
Nov 23, 2004
I am setting up a development server with SQL 2005 Dev Ed beta 2. I have been going around in circles trying to find out how to create a database diagram. Microsoft really knows how to make things difficult! Would someone mind telling me how to build a diagram of my new database?
Thanks!
Dan
View 9 Replies
View Related
Apr 19, 2006
I've previously used version 7 and 2000.I've recently started to learn ASP.net. I've installedVisual Web Developer 2005 Express and this includesSQLServer 2005 in the installation. The task managerand and the list of services definitely show I've gotSQLServer running on my computer.Here's the problem: how do I actually use it? I can'tfind any tools to create and access databases.I've tried installing the Enterprise Manager from 2000but this gives me an error message saying I must use the 2005tools to connect.So, is there an Enterprise Manager (or similar) for 2005?Where do I find it?
View 2 Replies
View Related
Feb 13, 2006
I must be missing something very simple here but how do you set an inner variable with a value stored in an outer variable, or any other variable?
I've tried various syntaxes used elsewhere to refer to variables without any success, and the help topics have been less than useful for this task.
Thanks,
John
View 1 Replies
View Related
Mar 2, 2007
Hi all,
I need few clarifications from you experts regarding SQL server 2005 & CLR integration ( my questions might be simple and silly, please bear with me).
A web service should be invoked from the SQL Server , Is CLR stored procedure only way to do that ?
Does SQL Server uses the CLR only when the CLR support is enabled ? OR SQL Server itself runs on top of the CLR no matter it is enabled or not ?
What are the major disadvantages of using CLR stored procedures instead of T-SQL?
Thanks in advance,
DBLearner
View 3 Replies
View Related
Mar 10, 2007
I thought I would delve into index fragmentation and I found somegreat sql from many posters (thanks Erland!).My question is how bad is bad? I know this is very subjective.Some scripts I found would reindex if the LogicalFragmenation is over30%.I have some tables that are 98% (I'm guessing really bad). I know itall depends..more as a learning point: I found a table that had over 30%logicalfragmentation, I dropped the indexes, created then ran thescript that used type code segment:'DBCC SHOWCONTIG(' + @TableName + ') WITH TABLERESULTS, ALL_INDEXES,NO_INFOMSGS')In one case, the indexes for the table dropped below 30%, in anothercase the index was still fragmented ever after I dropped and re-created index.SQL Server 2005 x64 SP2This is the script I am running (I found this in another thread thatErland posted):SET NOCOUNT ONUSE ds_v6_sourceDECLARE @TableName VARCHAR(100)-- Create a table to hold the results of DBCC SHOWCONTIGIF OBJECT_ID('Tempdb.dbo.#Contig') IS NOT NULLDROP TABLE #ContigCREATE TABLE #Contig ([ObjectName] VARCHAR(100), [ObjectId] INT,[IndexName]VARCHAR(200),[IndexId] INT, [Level] INT, [Pages] INT , [Rows] INT ,[MinimumRecordSize] INT,[MaximumRecordSize] INT , [AverageRecordSize] INT,[ForwardedRecords] INT ,[Extents] INT, [ExtentSwitches] INT, [AverageFreeBytes]NUMERIC(6,2),[AveragePageDensity] NUMERIC(6,2), [ScanDensity]NUMERIC(6,2) ,[BestCount] INT ,[ActualCount] INT , [LogicalFragmentation] NUMERIC(6,2) ,[ExtentFragmentation] NUMERIC(6,2) )DECLARE curTables CURSOR STATIC LOCALFORSELECT Table_NameFROM Information_Schema.TablesWHERE Table_Type = 'BASE TABLE'OPEN curTablesFETCH NEXT FROM curTables INTO @TableNameSET @TableName = RTRIM(@TableName)WHILE @@FETCH_STATUS = 0BEGININSERT INTO #Contig EXEC('DBCC SHOWCONTIG(' + @TableName + ') WITHTABLERESULTS, ALL_INDEXES, NO_INFOMSGS')FETCH NEXT FROM curTables INTO @TableNameENDCLOSE curTablesDEALLOCATE curTables
View 8 Replies
View Related
Sep 14, 2004
Minimum Date in DateStamp field is not 1-Jan-100. Wouldn't we expect that.
In SQLServer2000 is it "1-Jan-100", if not WHY ??,
In the previous versions it is "01-Jan-1753"
bikramjeet
View 3 Replies
View Related
Nov 3, 2000
Probably this will be a silly question?
1. How can I Put all the Q documents from Microsoft that are related to SQL server? So that I can search fast.
2. How can I search SWYNK for all the ANSWERS that MAK/CRAIG/RAY replied to the threads. Its so weird that I answered someone and now I forgot the answer what I posted. Now I am having the same problem. I cant search.
3. I wanna put all SWYNK's Questions responses in one database say SQLSERVER, so that I can search all the threads.
I had sent many emails to SWYNK. No reply so far.
Any inputs r well appreciated!!!!!!
-MAK
View 2 Replies
View Related
Dec 19, 2003
I'm running a query, actually its an insert that works when using the TSQL below.
However when I try to use the debugger to step through and using the exact same values as those below I get the following error:
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
Its Killing me because everything else works, but this. Can somebody help.
DECLARE @NoteID INT,-- NULL OUTPUT,
@Note_Description NVARCHAR(3000),-- = NULL,
@Date DateTime,-- = NULL OUTPUT,
@ByWho NVARCHAR(30),-- = NULL,
@FK_Action_Performed NVARCHAR(40),-- = NULL,
@FK_UserID INT,-- = NULL,
@FK_JobID INT,-- = NULL,
@Job_Date DateTime,-- = NULL,
@Start DateTime,-- = NULL,
@Finish DateTime,-- = NULL,
@BeenRead NVARCHAR(10),-- = NULL
@FK_UserIDList NVARCHAR(4000)-- = NULL
--SET @NoteID = 409 --NULL OUTPUT,
SET @Note_Description = 'Tetsing'
--SET @Date DateTime = NULL OUTPUT,
SET @ByWho = 'GeorgeAgaian'
SET @FK_Action_Performed = 'Worked hard'
SET @FK_UserID = 5
SET @FK_JobID = 29
SET @Job_Date = 28/01/03
SET @Start = '1:00:20 PM'
SET @Finish = '1:00:20 PM'
SET @BeenRead = 'UnRead'
SET @FK_UserIDList = '1,2,3'
--AS
--SET NOCOUNT ON
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRANSACTION
SET @Date = GETDATE()
-- Insert Values into the customer table
INSERT Note (Note_Description,
Date,
ByWho,
FK_Action_Performed,
FK_UserID,
FK_JobID,
Job_Date,
Start,
Finish)
SELECT --@NoteID,
@Note_Description,
@Date,
@ByWho,
@FK_Action_Performed,
@FK_UserID,
@FK_JobID,
@Job_Date,
@Start,
@Finish
-- Get the new Customer Identifier, return as OUTPUT param
SELECT @NoteID = @@IDENTITY
-- Insert new notes for all the users that the note pertains to, in this case this will be by the assigned
-- users.
IF @FK_UserIDList IS NOT NULL
EXECUTE spInsertNotesByAssignedUsers @NoteID, @FK_UserIDList
-- Insert New Address record
-- Retrieve Address reference into @AddressId
-- EXEC spInsertForUserNote
-- @FK_UserID,
--@NoteID,
-- @BeenRead
-- @Fax,
-- @PKId,
-- @AddressId OUTPUT
COMMIT TRANSACTION
--------------------------------------------------
GO
View 1 Replies
View Related
May 28, 2008
ok can someone tell me why i get two different answers for the same query. (looking for last day of month for a given date)
SELECT DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, CAST('12/20/2006' AS datetime)) + 1, 0)) AS Expr1
FROM testsupplierSCNCR
I am getting the result of 01/01/2007
but in query analizer I get the result of
12/31/2006
Why the different dates
View 4 Replies
View Related
