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.
I created a maintenance plan for my 2005 server which included deleting old backups...or so I thought. The backups are stored in subdirectories, and the maintenance plan is not looking in subdirectories for old files.
So, I check the sql that is being executed, and it runs a command called xp_delete_file. No problem, I'll just look up that parameters and see if there is an option for including subdirectories.
NO CAN DO! XP_DELETE_FILE is an undocumented extended stored procedure. http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124708
Thanks for making it impossible to research my error, MicroSquash!
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 RelatedIs 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
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
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 :)
I can execute a stored procedure I created in the Enterprise manager cant I??
View 8 Replies View RelatedYes 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!!!
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-
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 RelatedIn 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
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,
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.
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.
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 RelatedWhat 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.
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.
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 ;)
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.
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 ?
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 RelatedHi 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!
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
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
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 ????
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
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 RelatedI 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
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
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 RelatedMinimum 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
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