|
Use sp_help on the table SerialNumberStaging to show the table definition and post it here.
|
|
|
|
|
Name Owner Type Created_datetime
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- -----------------------
SerialNumberStaging dbo user table 2009-03-18 14:39:18.647
Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- --------------------------------------------------------------------------------------------------------------------------------
snsID int no 4 10 0 no (n/a) (n/a) NULL
SerialNumber nvarchar no 510 no (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
HostID nvarchar no 510 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
AssetTag nvarchar no 510 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
IPAddress nvarchar no 510 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
MacAddress nvarchar no 510 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
NewSerialNumber nvarchar no 510 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
NewMacAddress nvarchar no 510 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
RowIsError nvarchar no 510 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS
Identity Seed Increment Not For Replication
-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------- --------------------------------------- -------------------
snsID 1 1 0
RowGuidCol
--------------------------------------------------------------------------------------------------------------------------------
No rowguidcol column defined.
Data_located_on_filegroup
--------------------------------------------------------------------------------------------------------------------------------
PRIMARY
index_name index_description index_keys
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PK_SerialNumberStaging clustered, unique, primary key located on PRIMARY snsID
constraint_type constraint_name delete_action update_action status_enabled status_for_replication constraint_keys
-------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- -------------- ---------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PRIMARY KEY (clustered) PK_SerialNumberStaging (n/a) (n/a) (n/a) (n/a) snsID
No foreign keys reference table 'SerialNumberStaging', or you do not have permissions on referencing tables.
No views with schema binding reference table 'SerialNumberStaging'.
|
|
|
|
|
The specs for the stored proc were incorrect. With the new specs we've managed to fix the problem. The gritty step that was causing the confusion now looks like this:
UPDATE dbo.SerialNumberStaging.NewSerialNumber
SET SerialNumberStaging.NewSerialNumber = SubString(SerialNumberStaging.NewSerialNumber,2,11) --12char
WHERE (Len(SerialNumberStaging.NewSerialNumber) = 12)
AND SubString(dbo.SerialNumberStaging.NewSerialNumber, 1, 1) = 's' --starts with 's'
AND dbo.SerialNumberStaging.NewSerialNumber -- it exists in lineItem
IN (SELECT dbo.LineItem.SerialNumber
FROM LineItem
WHERE dbo.LineItem.ID = @LineItemID
AND (dbo.LineItem.fkCompanyID -- line item or order???
IN (SELECT dbo.sn_Company.fkCompanyID
FROM dbo.sn_Company
)
)
)
Everything seems to be working now. 
Thanks Guys
|
|
|
|
|
Is there an equivalent '#Define' preprocessor for Microsoft's T-SQL, as in C++ ?
I want to be able to declare two table names, then go on and use these name definition
elsewhere in the SQL statement
eg.
<br />
#DEFINE TableA 'SomeTable'<br />
SELECT * FROM TableA
I Dream of Absolute Zero
|
|
|
|
|
|
RChin wrote: Is there an equivalent '#Define' preprocessor for Microsoft's T-SQL, as in C++ ?
Not that I'm aware of, but you might consider writing a view 
I are troll
|
|
|
|
|
Hi all!!
I need to pull a table from this simple db:
User:
[p_user_id] [int] IDENTITY(1,1) NOT NULL,
[fname] [varchar](50) NOT NULL,
[sname] [varchar](50) NOT NULL,
[email] [varchar](50) NOT NULL,
[accesslevel] [int] NOT NULL,
[password] [varchar](50) NOT NULL,
[lastlogin] [datetime] NULL,
[logincount] [int] NULL,
Event:
[p_event_id] [int] IDENTITY(1,1) NOT NULL,
[eventdate] [datetime] NOT NULL,
[eventtype] [varchar](50) NOT NULL,
[firsthalfcolour] [int] NULL,
[secondhalfcolour] [int] NULL,
[firsthalfscore] [int] NULL,
[secondhalfscore] [int] NULL,
and Link:
[p_link_id] [int] IDENTITY(1,1) NOT NULL,
[f_event_id] [int] NOT NULL,
[f_user_id] [int] NOT NULL,
[f_colour_id] [int] NOT NULL,
And I need to get a list of all the users with their win percentage. firsthalfcolour (winning colour) = f_colour_id (your team colour).
I have this to calculate an individuals percentage but I dont know how to get a 'group' overview?  :
CREATE procedure [dbo].[getwinpercentage_byuserid]
(
@userid int
)
as
declare @First integer
declare @Second integer
declare @Third integer
select @First = count(*) from [event]
left join [link] on f_event_id = p_event_id
left join colour on firsthalfcolour = p_colour_id
where f_user_id = @userid and firsthalfcolour = f_colour_id
select @Second = count(*) from [event]
left join [link] on f_event_id = p_event_id
left join colour on secondhalfcolour = p_colour_id
where f_user_id = @userid and secondhalfcolour = f_colour_id
select @Third = count(*) from [event]
left join [link] on f_event_id = p_event_id
left join colour on firsthalfcolour = p_colour_id
where f_user_id = @userid
select (CONVERT(varchar, ((@First + @Second) * 100) / (@Third * 2)) + '%') as 'Success rate'
If you can help it would be greatly appreciated!
|
|
|
|
|
Hi,
I have a Microsoft Office Excel Comma Separated Values File (.csv)with 200,000 number of rows in excel.
I want to import this to SQL 2008 database.
There is a option to import in SQL but no option to import .csv file. So what would be the best way to import this file.
I have created a table with correct column name, next thing i wanna do is to import all the data.
I have tried below query, but no luck so far...
<br />
BULK <br />
INSERT abc_table<br />
FROM 'C:\xyz303.csv'<br />
WITH<br />
(<br />
FIELDTERMINATOR = ','<br />
)<br />
GO
Thanks
|
|
|
|
|
|
I do not have SQLServer 2008 installed, but I do have SQLServer 2005. Did you see an option for a flat file source? You should be able to import it using that.
Tim
|
|
|
|
|
I want to create a sequence in Oracle, and I want the start value to be the same as the value in another sequence.
This doesn't work:
CREATE SEQUENCE CUSTOMERID_SEQ
MINVALUE 1
MAXVALUE 99999999
INCREMENT BY 1
START WITH (SELECT THAT_OTHER_TABLESPACE.CUSTOMERID_SEQ.NEXTVAL FROM DUAL)
NOCYCLE
NOORDER
NOCACHE As there can be no subqueries in a Create Sequence
Any hints?
|
|
|
|
|
cant be done .. at least, not like this - start has to be an integer (so it doesnt even evaluate your select clause)
I could do it from c++ using oci - I'd do a [SELECT THAT_OTHER_TABLESPACE.CUSTOMERID_SEQ.NEXTVAL FROM DUAL] into an integer variable and then use that variable as a replacement into a create statement and execute the create statement.
Sorry, likely not what you were looking for ...
'g'
|
|
|
|
|
I wonder if it's possible to do that in pl-sql?
Then it could be saved to a script.
Thanks for the idea!
I'll try it this afternoon.
|
|
|
|
|
it should definately be do-able in pl-sql
'g'
|
|
|
|
|
|
For anyone interested, this is one way that works:
DECLARE
SEQ_ID NUMBER;
BEGIN
SELECT AN_OTHER_TABLESPACE.CUSTOMERID_SEQ.NEXTVAL INTO SEQ_ID FROM dual;
EXECUTE IMMEDIATE 'CREATE SEQUENCE CUSTOMERID_SEQ
MINVALUE 1
MAXVALUE 99999999
INCREMENT BY 1
START WITH ' || SEQ_ID || '
NOCYCLE
NOORDER
NOCACHE';
END;
/
|
|
|
|
|
I have one script task. Withing this script task, I need to create and execute Execute SQL Task.
Can be do so?
CodeManiac
xxxxxxxxxx
xxxxxxxxxx
|
|
|
|
|
Hello Friends,
I am in trouble while loading data to SQL Server. i am using bulkcopy code to load datas.
I dont have much knowledge with SQL
While loading, if error occurs (in xtreme cases) loading wont be perfect(ie I having four tables to be loaded. error may occur while loading the fourth table. for me its like corrupting the tables. because for tables are interconnected)
So if error occurs i want to roll back other three tables to last state.
The below code is i used for loading and these function is calling four times for updating four tables
Can Rollback property be applied to the below code?? Please do help
Or any other way to prevent the tables from getting corrupted
------------------------------------------
Public Function rb_BCP(ByVal Server As String, ByVal Database As String, _
ByVal Table As String, ByVal FileName As String)
Dim objServer As SQLDMO.SQLServer
Dim objBCP As SQLDMO.BulkCopy
Dim objDB As SQLDMO.Database
On Error GoTo ErrorHandler
Set objServer = New SQLDMO.SQLServer
Set objBCP = New SQLDMO.BulkCopy
objServer.LoginSecure = True
objServer.EnableBcp = True
objServer.QuotedIdentifier = True
objServer.Connect (Server)
Set objDB = objServer.Databases(Database)
With objBCP
.UseExistingConnection = True
.DataFilePath = FileName
.RowDelimiter = vbCrLf
.IncludeIdentityValues = True
.ServerBCPKeepNulls = True
.DataFileType = SQLDMODataFile_SpecialDelimitedChar
.ColumnDelimiter = "|"
.IncludeIdentityValues = True
.ImportRowsPerBatch = 1000000
'Below speeds things up
'but does not log the bulk copy operation
'comment out if this is not what you
.UseBulkCopyOption = True
End With
objDB.Tables(Table).ImportData objBCP
Set objBCP = Nothing
objServer.Disconnect
Set objServer = Nothing
Exit Function
ErrorHandler:
MsgBox "Error " & Err.Number & vbCrLf & Err.Description
End Function
jishith
|
|
|
|
|
|
Hello.
Im running a webserver with apache and mysql 5.xx
Now I want to connect to the MySQL database on a server outside my own. But it doesnt allow remote connections from my mysql database.
I have port 3306 open and the firewall off.
How can I setup MySQL to accept remote connection?
Best Regard
Mads
|
|
|
|
|
Did you check the "Enable TCP/IP connections" option when you run the instance configuration? If not you can just reconfigure.
He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus
|
|
|
|
|
I installed it together with wamp.
And can't find such just thing in the mysql.ini .. What do I have to change in the mysql.ini to enable TCP/IP connections ?
|
|
|
|
|
Try this one[^] the url looks insultingly explanatory.  lol
He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus
|
|
|
|
|
I use windows for hosting mysql..
I have tried look in my.ini for these bind-adress and the other configs.. But can't find anything at all in my.ini that seems to have something to do with TCP/IP configuration.
|
|
|
|
|
Why SQL "blocks" when it cannot connect to a server? I would want it to return immediately on failure. Connection-timeout attribute looks like useless. I gave it as 5 seconds, but it continues to block for an eternity. Any help?
He never answers anyone who replies to him. I've taken to calling him a retard, which is not fair to retards everywhere.-Christian Graus
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
