Ok guys I have read everything in my previous post but unfortunately can not seem to get it to work properly. Would anyone like to do this 10 minutes work (I am sure). Obviously I dont expect it to be free but if I continue I am going to get a sledge hammer to this now.
Thanks in advance
Is it possible to change table design without running an alter table SQL Script??
For example, change the following :
Table_A ( column1 int identity(1,1) <<----BEFORE
, column2 varchar(10) null
, column3 char(20) not null
)
to
Table_A ( column1 int identity(1,1) <<----AFTER
, column2 char(255) not null
, column3 varchar(20) null
)
Is it possible to change sysobjects and syscolumns or other system objects for this to occur??
I know this is not recommended but if I had to, what are the steps required to do this??
Thanks very much
I would like to create a command that will make a new logical field that is true or false depending on weather field is a certian value for example
col1,col2,col3
col1 is firstname
col2 is lastname
col3 is numeric
SELECT col1,col2,(if col3=6)=true AS Boolean
FROM table
i know this code doesnt work but it is a representation of what i would like to happen
Hello, this probably isnt the best place to ask but I can't find a moresuitable sql newsgroup so I hope y'all dont mind too much.I have 2 tables; Cellar and ColourCELLAR contains the wine name, its year and the no.of bottles.Wine Year BottlesChardonnay 87 4Fume Blanc 87 2Pinot Noir 82 3Zinfandel 84 9COLOUR contains wine name and it's colourWine ColourChardonnay WhiteFume Blanc WhitePinot NoirRedZinfandel RoseThis is from a past exam paper btwOne of the questions was:Write the sql to count how many white wines there are in the table cellar.The solution that the lecturers included is:SELECT count(wine)FROM cellarWHERE colour='White'Now i havent' been able to try out this sql yet but to me that looks wrong.My solution would be:SELECT count(wine)FROM cellarWHERE cellar.wine = colour.wine and colour.colour='White'Can anyone tell me which one is correct, and if mine isn't correct then whyisn't it?Thanks
View 3 Replies View RelatedOne of of server was restarted... In the early hours... And i found this has a resson in event viewer..
Does anybody knows what this means..
The computer has rebooted from a bugcheck. The bugcheck was: 0x0000007f (0x00000008, 0x00000000, 0x00000000, 0x00000000). Microsoft Windows NT [v15.1381]. A dump was saved in: C:WINNTMEMORY.DMP.
Thank you
Jessi.
Hi,
Below is the insert statement... it insert a new record by selecting the max ID and add plus 1 to the existing value.. with in the same statement..
My question is can i get the inserted value of NewID into a VARIABLE with in the same insert statement.. I dont want to pass another sql statement to select the MAX ID..
INSERT INTO tbTest(ID,EName)
SELECT (SELECT MAX(ID)+1 FROM TEST),'BRAIN'
Thanks
Jessi..
I am trying to write a DTS package at work that uses a loop to fire a query off against a different server/database at each loop iteration. Prior to the execute SQL task , I use ActiveX (VBScript) to change the Catalog and DataSource of the connection (created by drag and drop if icon). My Execute SQL task selects @@servername and getdate() just as a dummy query to make sure I am actually pointed at a different server.
While testing the properties of the connection using global variables and msgbox shows that the database (catalog) and server (data source) ip address is being changed, the result returned is always from the first server. I feel like I need to disconnect the connection object, change the parameters and then reconnect to the new server but there doesn't appear to be anyway to do this.
Anyone out there able to successfully change a connection object inthis manner??
I got the following error on my database when I ran DBCC CHECKDB
Server: Msg 8951, Level 16, State 1, Line 1
Table error: Table 'PhoneCall' (ID 254623950). Missing or invalid key in index 'PhoneCall0' (ID 2) for the row:
Server: Msg 8955, Level 16, State 1, Line 1
Data row (1:98383:16) identified by (RID = (1:98383:16) ) has index values (PhoneCallID = 46361).
This error is on a couple of different indexii?? What do I do? Any help will be appreciated. Thank you
My boss asked me to interview 2 people on Monday for a programmer/developer position. They say they are gurus, but my boss wants me to find out for sure. I thought a question about bitwise operations would be good, maybe one about semi-joins. Are there any questions you can think of that will spot the phony pretty quick?
Thanks,
Jim
Hi,
I am trying to import comma delimitted text files which contain data for any particular country.
I have created SSIS package to import data into sql server table. table structure is exactly same as text file except one additional column which is to identify the CountryID. Text files we recieved does not contain CountryID column.
I am using BULK INSERT Task in SSIS to import the data as text files are quite big (up to 1 GB). during processing I do determine the country id for that text file as file name contain countrycode which can be use for lookup and get the countryid.
cyn_bills_20071208_032242.txt
CYN is the country code and country table has got country id for it.
I am using Format file with BULK INSERT task as table has additional column which does not present in source file.
currently after inserting all rows into table i have to run an UPDATE statement to update the value of CountryID column which is very expensive some time taking more then 30 min for (30 million rows)
Can anyone tell me how to insert an expression when using BULK INSERT command / Task in SSIS. there must be a way to insert litterals using Bulk Insert.
Thanks in Advance.
Furrukh Baig
Good afternoon one and all,
I have the following problem :
I have a table which currently has 40+ mill records. Some of the fields are of type nvarchar and these need changing to varchar. I have attempted to change these fields to varchar but the process proceeds to increase the transaction log (to 6 Gigs!) until there is no space left on the server (causing a multitude of other problems with existing programs on the server). Once this happens I have no choice but to kill the change.
Does any1 know how I can make these changes without SQL writing to the transaction log?
Any and all help appreciated,
Thanks
Gurmi
Hello,
I am executing the bcp statement with i a stored procedure, everything works fine. But the question is I want to pass the delimiter as a parameter to the BCP command. I tried but i couldnt make it. I declare a variable and set that variable to a delimiter and try to pass that variable to BCP, but it doesnt accept it, in the export file the delimiter is '@i' instead of ';'
DECLARE
@i CHAR(2)
SELECT @i = ';'
EXEC master..xp_cmdshell 'bcp " select city, name = case whenname like(''%''+char(13)+char(10)+''%'') then replace(name, char(13)+char(10),'''') whenname like(''%''+char(13)+''%'') then replace(name, char(13),'''')whenname like(''%''+char(10)+''%'') then replace(name, char(10),'''')else name end From dbDev..tbtest" queryout d:invoice.txt -c -Sdevelop_server -Uadmin -Padmin -t@i'
THANKS INDEED
JESSI.
Upon running DTS manually to transfer data from Excel into SQL Server, I
get the error:
-----------------------------ERROR OUPTUT ------------------------------------
Error at Source for Row number 264. Errors encountered so far in this task: 1. General error -2147217887 (80040E21).
Data for source column 3 ('Value') is too large for the specified buffer size.
---------------------------END ERROR OUTPUT----------------------------------
*** 'Value' is varchar(4000); largest having length of 1000.
*** The network packet size is 4096.
?? AM I SUPPOSED TO CHANGE THE BUFFER SIZE??
Your kind help is greatly appreciated
Thanks
Ziggy
Hi Guru,,
There are bunch of file on a SQL SERVER installed machine under WINNT floder they are as follows..
OTM1C.tmp
OTM2.tmp
OTM3.tmp
OTM4.tmp
OTM5.tmp
OTM6.tmp
OTMB.tmp
This files occupies 2G.B of disk space... We are in the cleaning process to create some disk space. The thing is no one really understand which application uses this files..
My question is does sql server uses this files... Can we delete this files..
Thank you..
truely
jessi.
Does anyone know if there is an updated Books Online for 2000 published anywhere?? Or even a more recent copy of the 7.0? MS has probably made corrections
to them.
Also, I'm looking for all the free info about DTS for 2000. Not anything that MS publishes, more of a third party evaluation.
Thanks in advance,
Adam
I have a application that uses a connection that is always active until the application is closed, and many other connections that are opened and closed as different data is requested. I DO close and dispose these connections, datasets, and data readers after requesting data to release the resources. BUT they still show in SQL Server 2005 Express Edition. I believe they are NOT active and are part of the Pooling system.
The problem is the connections don't seem to be reused, the Pooling system just makes more, so I end up with hundreds of them until no more connections can be created but SQL Server; hence timing out on some attempt to open a connection.
I have experimented with the 'Min Pool Size' and 'Max Pool Size' values in the connection string but the only change is behavior is that I can make more connection showing in SQL Server but the problem still happens anyway.
I am rewriting this application from VB6 to VB.Net. The VB6 version uses SQL Server 2005 Edition too and the connection problem doesn't exist.
Any ideas would be appreciated.
Hi, does somebody recognize the problem with my code? -Thanks!
Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately. Parser Error Message: This is an unexpected token. The expected token is 'NAME'. Line 58, position 52.Source Error:
<sessionState mode="SQLServer"stateConnectionString="Jensen"sqlConnectionString=
"datasource=Database;userid=username;password=pass"cookieless="false"timeout="60"/>
Always looking for people who are strong with microsoft technology for new oppurtunities in the Seattle area. If you are good, we can help you find your ideal next position.
don't be shy. shoot me an e-mail.
thanks
Pat Copeland
I am looking for strong SQL developers and/or DBA's for some really sweet companies here in Seattle. Anyone interested that is good and wants to hear about new oppurtunities? I would love to help you out.
don't be shy just shoot me an e-mail.
Please help to find an example of the code written on VC++ for an extended stored procedure (dll) for SQL Server 2000.
Thanks
OK...this is driving me nuts....
In the First DELETE and bcp I was getting the thread being launched by xp_cmdshell was being blocked by the parent thread...
put in WAITFOR...sometime it worked...started with an empty table..it worked....left the 28k rows, blocked...
Now, put SELECT COUNT(*)...works each and every g-d damn time...
HUH?
Now I get to the bcp out..
added the same code WAITFOR/SELECT *...
blocks each and ever g-d damn time....
I'm very reticent to COMMIT and start another tranny block...
Anyone have any ideas?
SET NOCOUNT ON
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[wrk_DataHold]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[wrk_DataHold]
GO
CREATE TABLE wrk_DataHold(Col1 varchar(8000))
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[wrk_OldNew]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[wrk_OldNew]
GO
CREATE TABLE wrk_OldNew(Old varchar(255),New varchar(255))
GO
INSERT INTO wrk_OldNew(Old,New)
SELECT 'SEVERAL EE~S', ''
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_ModifyRows]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_ModifyRows]
GO
CREATE PROC usp_ModifyRows
@Path sysname
, @FName sysname
AS
SET NOCOUNT ON
BEGIN TRAN
DECLARE @cmd varchar(8000), @Servername sysname, @rc int, @error int, @rowcount int
, @Old varchar(255), @New varchar(255), @x int
CREATE TABLE #bcpLog(Col1 varchar(8000))
SET @rc = 0
DELETE FROM wrk_DataHold
SELECT @error = @@error, @rowcount = @@ROWCOUNT
IF @error <> 0
BEGIN
SET @rc = -1
GOTO usp_ModifyRows_Error
END
SELECT @x=COUNT(*) FROM wrk_DataHold
WAITFOR DELAY '000:00:10'
SET @cmd = 'bcp wrk_DataHold in ' + @Path + @FName + ' -S ' + @@SERVERNAME + ' -U -P -c'
INSERT INTO #bcpLog(Col1) EXEC master..xp_cmdShell @cmd
DECLARE OldNew CURSOR FOR SELECT Old, New FROM wrk_OldNew
OPEN OldNew
FETCH NEXT FROM OldNew INTO @Old, @New
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE wrk_DataHold
SET Col1 = REPLACE(Col1,@Old,@New)
WHERE Col1 LIKE '%'+@Old+'%'
SELECT @error = @@error, @rowcount = @@ROWCOUNT
IF @error <> 0
BEGIN
SET @rc = -1
GOTO usp_ModifyRows_Error
END
INSERT INTO #bcpLog(Col1)
SELECT 'REPLACE "'+ RTRIM(@Old) + '" With "' + RTRIM(@New)+ '"' UNION ALL
SELECT '('+CONVERT(varchar(25),@rowcount)+' row(s) affected)'
FETCH NEXT FROM OldNew INTO @Old, @New
END
CLOSE OldNew
DEALLOCATE OldNew
SELECT @x=COUNT(*) FROM wrk_DataHold
WAITFOR DELAY '000:00:10'
SELECT @FName = SUBSTRING(@FName,1,CHARINDEX('.',@FName)-1)+'.new'
INSERT INTO #bcpLog(Col1)
SELECT 'Preparing to Write out new file '+ @Path + @FName
/*
SET @cmd = 'bcp wrk_DataHold out ' + @Path + @FName + ' -S ' + @@SERVERNAME + ' -U -P -c'
INSERT INTO #bcpLog(Col1) EXEC master..xp_cmdShell @cmd
SET @cmd = 'bcp #bcpLog out D:cpLog.txt -S ' + @@SERVERNAME + ' -U -P -c'
INSERT INTO #bcpLog(Col1) EXEC master..xp_cmdShell @cmd
*/
COMMIT TRAN
usp_ModifyRows_Exit:
SELECT * FROM #bcpLog
DROP TABLE #bcpLog
SET NOCOUNT OFF
RETURN @rc
usp_ModifyRows_Error:
CLOSE OldNew
DEALLOCATE OldNew
ROLLBACK TRAN
GOTO usp_ModifyRows_Exit
GO
SET NOCOUNT OFF
I have a SQL Server (Express) database that I use in a Visual Studio (Visual Basic) 2005 application.
The application is of the Decision Support type, that routine deletes the contents of some of the database tables and then re-populates them with new data. There is no reason for keeping the "old" contents and no reason to restore the old content.
(It does this using "BULK INSERT" statements, as in:
"BULK INSERT SharePrices FROM "C:SharePrice.txt")
Because of all the data replacing, the log file gets huge quickly. I don't need the log file at all.
(I suspect that the logging is also taking up time unncessarily.)
Is there any way I can set the database not to have a log file at all, or to have a small log file?
(I've tried deleting the log file from SQL Server Management SQL Express, but get a message that there has to be a log file.)
When I restrict the log file size, my application returns error messages that the log file is too small, and the application can't do what it needs to do.
I have already made sure that in the database that the recovery mode is set to "Simple" and that "AutoShrink" is set to true.
I've tried manual "Shrinks". These work. However, as soon as the application accesses the database, the log file gets huge again.
I realise I may have to do (unnecessary) backups before/after my bulk insert statements. If so, I'd appreciate some help on how to do this from within a Visual Basic 2005 application. (I'm actually using the Data Access block from the Enterprise Library for my data access.)
I'd really appreciate your help.
Kind regards
Reg Bust
Dear all,
Can anybody provide sample t-sql procedures coding to do the following tasks?
I want to call a sql stored procedures with parameter passing. I write down the pseudo codes as follows:
In the SQL server side, write a stored procedure which performs the following:
With input parameter "ABCDE";
execute SQL statement "select * from table A where tableA.field B = "ABCDE";
For each record from the result of the above SQL statement,
"select * from tableB where tableB.field C=tableA.fieldX"
if the record can be found in tableB,
"update tableB set tableB.fieldD=something";
if the record cannot be found in tableB,
"insert a new record in tableB"
My actual application requirement is that I have an input file of over hundred thousand records which acts as a primary file to update or to add records to another file. My client side application, which is VB.NET, call a sql procedures once to perform this task. I don't want the VB.NET program to loop through all the input records and call a sql procedure a hundred thousand times!
thanks in advance!
This is an upgrade issue :
I have a SQL server 6.5 with NT users mapped into the SQL Server running in production. At one point of time, this Server was set up for mixed security, which has since been changed to standard ( still with NT users mapped in ).
When upgrading to SQL 7.0, I noticed that it brought in both : Nt USers as well as the mapped login id's as if they were separate entities ( Probably since SQL Server stores mapped login id's in master..syslogins, and mapped NT logins information are stored in the registry ). It also, does not bring in passwords.
I am planning to re-write this part of the upgrade and write my own scripts to transfer the logins ( Unfortunately, I will have to drop them first from the database using sp_dropuser, immediately after the upgrade process ).
Here's how I'm planning to do this : BCP out the contents of the syslogins table from the 6.5, bring it in into, say 'sys_xlogins' ( New table ) in 7.0, update the 'sysxlogins' system table in 7.0 with the passwords from the 'sys_xlogins' table, and add all the users back into the database.
If anyone else has been thru this, or anything like this ( Upgrading from mixed security 6.5 to 7.0 ), I would appreciate their inputs. Any words of wisdom / experience welcome !
Hi,
Till couple of days ago.. I was able to watch different object counters in SQL Server performance monitor..
For some reason now i am unable to see non of the object counters related to sql server in performance monitor..
The error it records in event viewer is as follows..
The description for Event ID[1001] in Source [ SQLCTR70] could not be found.
It contains the following insertion string[s]: SQL Performance DLL Open function failed..
help is greatly appericated...
thanks indeed
jessi...
Hello all,
I have a question on whether the following stored precedure would be open to an SQL Injection attack. Assume that a string query would be passed to the SP.
I am told that because the password parameter is only varchar(8) that it is safe.
Can someone prove this wrong?
Thanks....
I have added sample code below.
CREATE TABLE [dbo].[JB_Test](
[Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_JB_Test_Name] DEFAULT (''),
[Email] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_JB_Test_Email] DEFAULT (''),
[Password] [varchar](8) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_JB_Test_Password] DEFAULT (''),
) ON [PRIMARY]
GO
Insert dbo.JB_Test (Name, Email, Password )
values ('John', 'asdf@asdf.com', '2345')
Insert dbo.JB_Test (Name, Email, Password )
values ('Paul', 'asdf@asdf.com', '2345')
Insert dbo.JB_Test (Name, Email, Password )
values ('Geroge', 'asdf@asdf.com', '2345')
Insert dbo.JB_Test (Name, Email, Password )
values ('Ringo', 'asdf@asdf.com', '2345')
GO
Create procedure dbo.JB_Test_Login
@Username varchar(100),
@Password varchar(8)
AS
select Name
from dbo.JB_Test
where Name = @Username
and Password = @Password
GO
--Clean Up Your Mess
--Drop procedure dbo.JB_Test_Login
--GO
--Drop Table dbo.JB_Test
--GO
JBelthoff
• Hosts Station is a Professional Asp Hosting Provider
• Position SEO can provide your company with SEO Services at an affordable price
› As far as myself... I do this for fun!
Hello,
I've been trying to fill a tree view based on some criteria in another table.
I have the standard table with a ID - ParentID - Text
when I bind the table everything works fine.
What I need to do is display only the tree items based off a username and ID in another table.
Files can be found http://www.cthere.com/treeviewhelp.zip
EXAMPLE:
tblTourgroup(UserName = Jim)
tblTourgroup (GroupID = 110)
tblGroups (GroupID = 110)
Results from tblGroup:
- RESTAURANTS
- Cuisine
- Steakhouse
Query Results should be layed out like tblGroups
Any ideas?
This is what I have so far... But it does not traverse up and get the other nodes..
USE SQL2005_335573_cplanet;
GO
WITH DynamicTree (ParentID, GroupID, GroupName)
AS
(
SELECT e.ParentID, e.GroupID, e.GroupName
FROM tblGroups AS e
INNER JOIN tblTourGroup AS edh
ON e.GroupID = edh.GroupID
)
SELECT e.ParentID, e.GroupID, e.GroupName
FROM DynamicTree as e
INNER JOIN tblTourGroup AS dp
ON e.GroupID = dp.GroupID
WHERE dp.UserName = N'Test'
GO
Hi
I have a table called tblsample, where i have information stored row wise. Ther four quarter information is stored for many years. I want those information column wise for a given year.
say
select col1, col2 from tblsample where rqtr=1 and ryear = 2000
select col1, col2 from tblsample where rqtr=2 and ryear = 2000
select col1, col2 from tblsample where rqtr=3 and ryear = 2000
select col1, col2 from tblsample where rqtr=4 and ryear = 2000
i want information like
for the Year 2000
1 qtr 2 qtr 3 qtr 4 qtr
How to acheive this in MSSQL 2000
hi! I'm still fairly wet behind the ears when it comes to databases, especially sql server 2000. But I just got a copy from my University and I really want to spend some time messing with it. The first thing I want to try is making a shopping cart that stores the items in the database rather than in the session. I'm using asp.net (VB) and I had previously made a simple shopping cart for a SMALL site that stored all the information in a dataset stored in the session... but I've been told that isn't a good idea and reccomended I store the data in the sql database. so that's what I want to do!
I'm looking for resources that can help me get this started. These are the first questions I have about this approach, and any advice from you experts would be most appreciated!
1) do I store the data in a temporary table or do I use the regular table and make temporary rows? if the former how do I transfer the rows from the temp table to the order table? if the latter how do I change it from temporary to peromanent? in either case, how do I eliminate rows if the user abandons their cart?
2) how do I first initialize an order and keep track of the orderID for each user and ensure that no one is assigned a duplicate orderID?
those are my biggest confusions about this approach. I've looked for books on this but most are either too much about sql syntax and big-time server management, or they are too general and not specific to sql server. I've tried looking online, but I can't find much information on doing this using asp.net 2.0. thanks again for your help!
-SelArom
I am looking for sql server certifcation questions any one hasany.like dumps or some thing
View 1 Replies View RelatedHello,
In the ErrorLog of my Sql Server , i found this line :
2007-06-26 05:35:18.37 Serveur The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
Operating System XP Home SP2
SQL Server 2005 Express Edition with Advanced SP1 )
( idem for another workstation with XP Pro SP2 and same version of SQL Server 2005 Express
My problem is :
i want use the windows authentification but my computers are on Worhkgroup linked by a router ( no window server )
i have read that's possible to connect from a remote computer to a computer having a SQL Server 2005 Express through SPN
How can i do it ? ( activating NTLM ? but how ?)
I'm writing a C# program which must be executing on several computers with a SQL Server 2005 Express installed on a particular computer. These computers will belong to a domain of Windows Server 2003.
As i can't connect to this "normal" network, i am trying to simulate this network at home because i want to test this program and especially the possible locks problems.
I don't know whether i'm querying with the "correct" forum.
Sorry for my poor english.
I shall appreciate any help about this problem
Have a nice day
Hi!
I'm new to SSIS (and quite new to SQL Server). I have a process which I'd like to automize via SSIS - just don't know how and couldn't figure it out yet by playing around with the program. Shouldn't be too difficult though.
First of all, that's the process as I do it now:
1) Load several flatfile sources (dumps of SQL tables) into an SQL database.
2) Add identifier rows (to some tables), set the primary and foreign keys so the database is "recreated" and I can work on it.
3) Do several simple transformations, aggregations and selects across tables and finally write a new table containing information for reporting stuff.
I succeded in loading flatfiles within the data flow view, doing some transformations and saving the output to a flatfile. What I didn't find out: how can I "recreate" the database enabling me to perform "SELECT/FROM/WHERE" statements across tables? Will I have to write the imported files to tables within a db (how?) or can I avoid this step?
A little guide (newbie friendly) would be great help!
I created a report which has duplicates on one field. I want the report to display the duplicates, but cannot figure out how. Anywhere I have found where there is a "Hide Duplicates" option I have it turned off (in matrix properties), but the report matrix still hides the duplicates. I don't understand why this would happen since the other fields on the same record are not duplicates. I tried grouping but that was no help.
The query in the dataset DOES return the duplicates as expected.
Anyone know how to fix this, or if it is a bug?
Thanks,
Chris